varchar(max) everywhere?

by

By using VARCHAR(MAX) you are basically telling SQL Server “store the values in this field how you see best”, SQL Server will then choose whether to store values as a regular VARCHAR or as a LOB (Large object). In general if the values stored are less than 8,000 bytes SQL Server will treat values as a regular VARCHAR type.

If the values stored are too large then the column is allowed to spill off the page in to LOB pages, exactly as they do for other LOB types (text, ntext and image) – if this happens then additional page reads are required to read the data stored in the additional pages (i.e. there is a performance penatly), however this only happens if the values stored are too large.

In fact under SQL Server 2008 or later data can overflow onto additional pages even with the fixed length data types (e.g. VARCHAR(3,000)), however these pages are called row overflow data pages and are treated slightly differently.

Short version: from a storage perspective there is no disadvantage of using VARCHAR(MAX) over VARCHAR(N) for some N.

(Note that this also applies to the other variable-length field types NVARCHAR and VARBINARY)

FYI – You can’t create indexes on VARCHAR(MAX) columns

More Related Contents:

  1. SQL group_concat function in SQL Server [duplicate]
  2. Pivot Dynamic Columns, no Aggregation
  3. How to connect to local instance of SQL Server 2008 Express
  4. Export query result to .csv file in SQL Server 2008
  5. Enable ‘xp_cmdshell’ SQL Server
  6. Sql Server ‘Saving changes is not permitted’ error ► Prevent saving changes that require table re-creation
  7. SQL MERGE statement to update data
  8. How to convert float to varchar in SQL Server
  9. A transport-level error has occurred when receiving results from the server [closed]
  10. How can I load a large flat file into a database table using SSIS?
  11. How to convert datetime in Persian in SQL Server
  12. storing passwords in SQL Server
  13. How does sql server sort your data?
  14. How to get the size of a varchar[n] field in one SQL statement?
  15. SQL Server: Split operation
  16. How to get week number of the month from the date in sql server 2008
  17. How to deploy SQL Server Compact Edition 4.0?
  18. Optional parameters in SQL Server stored procedure
  19. Entity Framework Database.SetInitializer simply not working
  20. Cannot open backup device. Operating System error 5
  21. Restoring database from .mdf and .ldf files of SQL Server 2008
  22. Restrict SQL Server Login access to only one database
  23. T-SQL strip all non-alpha and non-numeric characters
  24. How can I get a trigger to fire on each inserted row during an INSERT INTO Table (etc) SELECT * FROM Table2?
  25. Where are SQL Server connection attempts logged?
  26. Can I pass variable to select statement as column name in SQL Server [duplicate]
  27. SQL Server 2008 Connection Error “No process is on the other end of the pipe”
  28. SSIS LookUp is not dealing with NULLs like the docs say it should
  29. Recursive Child/Parent queries in T/SQL
  30. Forcing code-first to always initialize a non-existent database?

Leave a Comment