sql varchar(max) vs varchar(fix)

by

MSDN

  • Use varchar when the sizes of the column data entries vary
    considerably.
  • Use varchar(max) when the sizes of the column data entries vary
    considerably, and the size might exceed 8,000 bytes.

When the the length is specified in declaring a VARCHAR variable or column, the maximum length allowed is 8000. If the length is greater than 8000, you have to use the MAX specifier as the length. If a length greater than 8000 is specified, the following error will be encountered (assuming that the length specified is 10000):

The size (10000) given to the type ‘varchar’ exceeds the maximum allowed for any data type (8000).

UPDATE :-
I found a link which I would like to share:-

Here

There is not much performance difference between Varchar[(n)] and Varchar(Max). Varchar[(n)] provides better performance results compared to Varchar(Max). If we know that data to be stored in the column or variable is less than or equal to 8000 characters, then using this Varchar[(n)] data type provides better performance compared to Varchar(Max).Example: When I ran the below script by changing the variable @FirstName type to Varchar(Max) then for 1 million assignments it is consistently taking double time than when we used data type as Varchar(50) for variable @FirstName.

DECLARE @FirstName VARCHAR(50), @COUNT INT=0, @StartTime DATETIME = GETDATE()
WHILE(@COUNT < 1000000)
BEGIN
SELECT @FirstName="Suraj", @COUNT = @COUNT +1
END
SELECT DATEDIFF(ms,@StartTime,GETDATE()) 'Time Taken in ms'
GO

More Related Contents:

  1. Unpivot with column name
  2. How can I delete using INNER JOIN with SQL Server?
  3. SQL NVARCHAR and VARCHAR Limits
  4. Convert varchar into datetime in SQL Server
  5. sql query to return differences between two tables
  6. Check if a row exists, otherwise insert
  7. T-SQL datetime rounded to nearest minute and nearest hours with using functions
  8. SQL Server : dynamic pivot over 5 columns
  9. Script to kill all connections to a database (More than RESTRICTED_USER ROLLBACK)
  10. How to write UPDATE SQL with Table alias in SQL Server 2008?
  11. SQL Server 2008- Get table constraints
  12. SQL SERVER: Get total days between two dates
  13. Restrict an SQL Server connection to a specific IP address
  14. Execute Dynamic Query with go in sql
  15. TransactSQL to run another TransactSQL script
  16. How can I get a list of element names from an XML value in SQL Server
  17. Why are logical reads for windowed aggregate functions so high?
  18. Store multiple bit values in a single table column
  19. SQL Server equivalent to MySQL enum data type?
  20. UPDATE and REPLACE part of a string
  21. Using IF ELSE statement based on Count to execute different Insert statements
  22. SQL Server SELECT INTO @variable?
  23. How do I change db schema to dbo
  24. How to subtract 30 days from the current date using SQL Server
  25. Count records for every month in a year
  26. SQL Server: +(unary) operator on non-numeric Strings
  27. Use one CTE many times
  28. How to Select Every Row Where Column Value is NOT Distinct
  29. How to do a case sensitive GROUP BY?
  30. How to automatically generate unique id in SQL like UID12345678?

Leave a Comment