0

If I have a column in SQL Server that I know will be about 10 characters usually. Is there any disadvantage in making it varchar(50) to cover all my cases? Any loss in efficiency, storage, computation / query pull between varchar(n) and varchar(n+x)?

Note: this is not a comparison between varchar(n) and varchar(MAX). There is another question already asked about that.

Related but different question: difference between varchar(500) vs varchar(max) in sql server

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Sir Fartsalot
  • 1
  • 2

1 Answers1

1

Yes, oversizing "just in case" can lead to performance issues, namely memory grants that are too high because they are based on the assumption that every value is half-populated.

But it's up to you to balance that impact with other potential issues:

  • Making it "just right" can lead to memory grants that are too low if most/all values are more than half full.
  • Making it 5X just in case seems like a lot, but think about how likely it is that the business requirement will evolve over time. Changing the column later could be more disruptive; consider not just the column itself but all the parameter and variable definitions, indexes, constraints, etc. that might also be affected, as well as application code that would have to change to accommodate a larger string.

What you should do depends on how confident you are that this will always be varchar(10). If you're not very confident, you might consider making it varchar(20) or varchar(32) but constraining it to the current requirement of 10 (with params to insert procedures and an actual constraint). If the requirement changes later, you only have to remove the constraint, change the parameter definitions, and then the apps can change at their own pace to start supporting the longer value.

Aaron Bertrand
  • 272,866
  • 37
  • 466
  • 490
  • You said something about memory grants and half full and I don't understand all that. You seem to be implying that if you know its always 10, you might make it 15, and it could improve performance. Did I catch that right? – Sir Fartsalot Jun 24 '20 at 15:29
  • Did you [read the link](https://sqlperformance.com/2017/06/sql-plan/performance-myths-oversizing-strings)? I can't force you to understand things if you don't read them. SQL Server gives every query a memory grant based on how much data it _thinks_ it will have to process (and what type of processing it has to do, say, sorting). With variable length data types like `varchar`, it doesn't *know* if the values are full, empty, or somewhere in between. So it makes a guess that they are all half full (for varchar(10), it assumes every value is 5 characters). It grants memory based on that. – Aaron Bertrand Jun 24 '20 at 15:32
  • If that's not enough memory to process the data (say, every value is 10 characters), then it will be forced to dump some of the data to tempdb in order to process it all, since it can't all fit in the memory that's been granted (and in most scenarios that grant can't be altered after the query starts). This can hurt query performance. Making it 15 might help that, sure, but that's still going to make SQL Server assume that every value is 7.5 characters, and could still lead to the same issue. Making it 20 sounds like the right thing to do, but granting _too much_ memory can also be an issue. – Aaron Bertrand Jun 24 '20 at 15:35
  • Very helpful! Thanks so much! – Sir Fartsalot Jun 24 '20 at 15:38
  • If I have a ssn field that is 9, and I allocate 9, yes, its just right, and yes it won't grow, but I might still be fine, because I could have a name field that is max 50, and typically, I only 10 characters for it. My overall allocation will be fine. One makes up for the other. Is this a valid point? – Sir Fartsalot Jun 24 '20 at 16:25
  • That's assuming both names are in your query, though (above comment. Sorry, I wan't able to edit the comment). – Sir Fartsalot Jun 24 '20 at 16:43