22

Someone was reviewing my DDL code for creating tables and suggested, when they saw I saw using VARCHAR(256) fields for text I expect to be pretty small, like a first name or whatever, that I should always just use VARCHAR(MAX) and linked Why use anything but varchar(max). I read it but it seemed dated, as it was focusing on 2005, and didn't seem to offer any real justification to allocate potentially up to 2 GB per row on all text fields.

From a performance, storage, etc. standpoint, how should one go about deciding whether to use VARCHAR(MAX) or a smaller more specific type for modern versions of SQL Server? (e.g., 2008, 2012, 2014)

31

Should I always use (n)varchar(max) for text columns?

No.

For SQL Server, the max data types should only be specified when there is no alternative. One should instead choose the correct base type (varchar or nvarchar) and specify an explicit maximum length that is appropriate to the data to be stored.

Physical storage is identical whether the column is typed as varchar(n) or varchar(max), so that is not the concern.

The reasons to not choose (n)varchar(max) everywhere revolve around features, plan quality, and performance.

An exhaustive list is probably not practical, but among other things, max columns:

Features

  • Require a separate constraint to enforce a maximum length
  • Cannot be a key in an index (so no unique constraints either)
  • May prevent online DDL (including index rebuilds and adding a new non-null column)
  • Are generally not supported for 'newer' features e.g. columnstore
  • See the product documentation for more specific features and limitations. The general pattern is that there are awkward limitations and restrictions around max data types. Not all limitations and side-effects are documented.

Performance

  • Require special handling in the execution engine, to account for the potentially very large size. Typically, this involves using a less efficient code path, with a streaming interface
  • May have similar unanticipated consequences for external code (and other SQL Server components like SSIS), which must also be prepared to handle data up to 2GB in size
  • Are assumed to be 4000 bytes wide in memory grant calculations. This is likely to lead to excessive memory reservation, which limits concurrency, and pushes valuable index and data pages out of cache memory
  • Disable several important performance optimizations
  • May extend lock duration
  • May prevent the optimizer choosing a (non-dynamic) seek plan
  • Prevent filters being pushed into scans and seeks as a residual
  • May increase tempdb pressure and contention (version dependant), since variables and parameters are also likely to be typed as max to match column definitions

In summary, there are so many subtle (and undesirable) side effects of unnecessarily using the max specifier that it makes no sense to do this. The minor 'convenience' of using a single declaration is no sort of compensation.

Evaluate each type in context, use the correct base type (varchar or nvarchar), and a sensible explicit length.

Further reading:

8

This is going to read like a paranoid's answer, but there aren't only storage and performance considerations.

The database itself doesn't control its clients, and clients can't be assumed to always securely insert user input - even if a database is designed to be used only with a .net application that uses Entity Framework to encapsulate transactions and ensure parameterized queries are systematically used, you cannot know that it's always going to be the case.

I wouldn't know exactly how to do this, but by making all text fields varchar(max), if a client has Bobby Tables issues and/or your stored procedures' parameters are also varchar(max), then you're making it easier for an attacker to come up with a valid but cleverly evil parameter value that can do things that clients aren't supposed to be doing - whatever that is.

By limiting the length to what you actually need, you're not shielding yourself from these clever attacks (I'm not even sure what it's actually called, I just remember reading about it a while back), but you're not saying "go ahead, try to give me a 2GB script to run" either.

  • What it's called is probably "injection" ("SQL injection", more specifically). – Andriy M Jun 9 '16 at 6:59
  • @AndriyM yeah, for some reason I was thinking of SQL truncation attacks (MS seems to have taken down the link I had bookmarked), but that's basically exploiting varchar(not-max) parameters, so I kinda stuck a foot in my mouth here. But yeah SQL injection would be applicable here. Perhaps I should rephrase this answer a bit. – Mathieu Guindon Jun 9 '16 at 21:34

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.