[ACCEPTED]-How are varchar values stored in a SQL Server database?-sqldatatypes
Completely pointless restriction as far 17 as I can see. Assuming standard FixedVar format 16 (as opposed to the formats used with row/page 15 compression or sparse columns) and assuming 14 you are talking about varchar(1-8000) columns
All varchar data 13 is stored at the end of the row in a variable 12 length section (or in offrow pages if it 11 can't fit in row). The amount of space it 10 consumes in that section (and whether or 9 not it ends up off row) is entirely dependant 8 upon the length of the actual data not the 7 column declaration.
SQL Server will use the 6 length declared in the column declaration 5 when allocating memory (e.g. for sort operations). The 4 assumption it makes in that instance is 3 that varchar columns will be filled to 50% of their declared size on average so this might be 2 a better thing to look at when choosing 1 a size.
I have heard of this practice before, but 22 after researching this question a bit I 21 don't think there is a practical reason 20 for having varchar values in multiples of 19 16. I think this requirement probably comes 18 from trying to optimize the space used on 17 each page. In SQL Server, pages are set 16 at 8 KB per page. Rows are stored in pages, so 15 perhaps the thinking is that you could conserve 14 space on the pages if the size of each row 13 divided evenly into 8 KB (a more detailed 12 description of how SQL Server stores data 11 can be found here). However, since the amount 10 of space used by a varchar field is determined 9 by its actual content, I don't see how using 8 lengths in multiples of 16 or any other 7 scheme could help you optimize the amount 6 of space used by each row on the page. The 5 length of the varchar fields should just 4 be set to whatever the business requirements 3 dictate.
Additionally, this question covers 2 similar ground and the conclusion also seems 1 to be the same:
Database column sizes for character based data
You should always store the data in the 22 data size that matches the data being stored. It 21 is part of how the database can maintain 20 integrity. For instance suppose you are 19 storing email addresses. If your data size 18 is the size of the maximum allowable emailaddress, then 17 you will not be able to store bad data that 16 is larger than that. That is a good thing. Some 15 people want to make everything nvarchar(max) or 14 varchar(max). However, this causes only 13 indexing problems.
Personally I would have 12 gone back to the person who make this requirement 11 and asked for a reason. Then I would have 10 presented my reasons as to why it might 9 not be a good idea. I woul never just blindly 8 implement something like this. In pushing 7 back on a requirement like this, I would 6 first do some research into how SQL Server 5 organizes data on the disk, so I could show 4 the impact of the requirement is likely 3 to have on performance. I might even be 2 surprised to find out the requirement made 1 sense, but I doubt it at this point.
More Related questions
We use cookies to improve the performance of the site. By staying on our site, you agree to the terms of use of cookies.