0

I add a column to a table with data type / length varchar(500). I then add 3 records to this table. Two records have a value of length 5 in the new column and the third has a value of length 500. Assuming all other columns have the same values for the three records, do they now all occupy the same space on disc or is the third row occupying more space?

If the former, what's the harm in setting N in varchar(N) to a very high number across all string field types where at some point there might be a need to utilise all of N? I could understand being conservative with setting N if one rogue value used N characters and the effect was a significant increase in storage allocation for every other record in the table. But if it only affects the one record, what's the harm in setting N very high?

Everything I read online says to be conservative when setting N, but I'm struggling to see why.

hwilson1
  • 489
  • 1
  • 6
  • 17
  • [this answer](http://dba.stackexchange.com/a/162117/361) might help you – Lamak Feb 01 '17 at 12:23
  • Possible duplicate of [varchar(max) everywhere?](http://stackoverflow.com/q/2091284/1048425) or [is there an advantage to varchar(500) over varchar(8000)?](http://stackoverflow.com/q/2009694/1048425), or [SQL Server VARCHAR Column Width](http://dba.stackexchange.com/q/11614/7257), or [Difference between varchar(5) and varchar(5000)?](http://stackoverflow.com/q/9033000/1048425) or [Would using varchar(5000) be bad compared to varchar(255)?](http://dba.stackexchange.com/q/162113/7257) – GarethD Feb 01 '17 at 12:27

1 Answers1

0

Varchar is ideal for saving space because it doesn't store trailing empty spaces.

It's downfall comes when you start performing lots of UPDATEs on the varchar field. The updates can trigger page splits and result in less efficient storage due to a low page fullness.

So the bigger the variation in size then the greater the potential impact of an UPDATE transaction to multiple records.

pacreely
  • 1,881
  • 2
  • 10
  • 16