3 ms·

This is why I like VARCHAR(MAX) in SQL Server if the row is < 8192 chars it's a VARCHAR if it's > 8192 it's TEXT. You only pay the penalty on rows that exceed t

by fleitz 16y ago

This is why I like VARCHAR(MAX) in SQL Server if the row is < 8192 chars it's a VARCHAR if it's > 8192 it's TEXT. You only pay the penalty on rows that exceed that threshold. Best of all you can store 2GB of text in a VARCHAR(MAX) http://msdn.microsoft.com/en-us/library/ms176089.aspx http://msdn.microsoft.com/en-us/library/ms176089.aspx

singular 16y ago
Using VARCHAR(MAX) still seems to have a significant performance impact, however - http://richardlees.blogspot.com/2010/07/varcharmax-performance-in-sql-server.html http://richardlees.blogspot.com/2010/07/varcharmax-performan... - perhaps the fact it has to check whether the varchar exceeds 8192?
- dspillett 16y ago
  It doesn't just have to check the length of that one field - it needs to check that the whole row wouldn't pass the per-row length limit even if the field itself doesn't. [N]VARCHAR(MAX) columns can lead to greater page tearing and other performance issues over time if their value is changed between a size that would fit in-row and a size that won't too - so as well as the hit on INSERT/UPDATE performance there may be a (less significant) hit on read performance over time too, on top of the slight hit from simply needing to check where the data is and go find another page if it isn't in-row. For the most part [N]VARCHAR(MAX) should be used as a more efficient (and far more convenient) replacement for [N]TEXT. It should only be used as a replacement for [N]VARCHAR(<something>) when the data in the column may need to break the ~4000 characters mark (for NVARCHAR, the limit is ~8000 characters in the case of VARCHAR) or when the total bytes for a row will.
rapind 16y ago
This is where I must have picked up the habit of always limiting my varchars to 8,000 chars. I'm in the habit of always using text for anything larger and I couldn't remember why.

by fleitz 16y ago

singular 16y ago

Using VARCHAR(MAX) still seems to have a significant performance impact, however - http://richardlees.blogspot.com/2010/07/varcharmax-performance-in-sql-server.html http://richardlees.blogspot.com/2010/07/varcharmax-performan... - perhaps the fact it has to check whether the varchar exceeds 8192?

dspillett 16y ago
It doesn't just have to check the length of that one field - it needs to check that the whole row wouldn't pass the per-row length limit even if the field itself doesn't. [N]VARCHAR(MAX) columns can lead to greater page tearing and other performance issues over time if their value is changed between a size that would fit in-row and a size that won't too - so as well as the hit on INSERT/UPDATE performance there may be a (less significant) hit on read performance over time too, on top of the slight hit from simply needing to check where the data is and go find another page if it isn't in-row. For the most part [N]VARCHAR(MAX) should be used as a more efficient (and far more convenient) replacement for [N]TEXT. It should only be used as a replacement for [N]VARCHAR(<something>) when the data in the column may need to break the ~4000 characters mark (for NVARCHAR, the limit is ~8000 characters in the case of VARCHAR) or when the total bytes for a row will.

rapind 16y ago

This is where I must have picked up the habit of always limiting my varchars to 8,000 chars. I'm in the habit of always using text for anything larger and I couldn't remember why.