If you create a table that will allow NULL columns do you achieve better storage results if the NULL columns are the last fields or does it matter?
asked Oct 22 '09 at 11:26 AM in Default
The position of the nullable columns should make no difference. NULLS are indicated not by the presence or absence of any data in a column, but by a bitmap appended to the end of the row on each page. One bit per nullable column means that you get up to eight nullable columns for only one extra byte added to the row length. If you go to nine nullable columns, then a second byte is added, and the next 7 will be "free".
Varchar and Nvarchar columns of course differ in size depending on the length of the string being stored, but for char, int, numeric, and other fixed-length datatypes, when a column goes from NULL to having data, there is no change to the row size. A bit is simply flipped from 0 to 1.
I was pinged to help answer this in email. The order does matter if the final columns in the table are nullable variable-length columns. When NULL, they won't have an entry in the variable-length column offset array, saving two bytes per column. This is why the first table in the example above is slightly smaller.
I illustrate this in the blog post at: Search Engine Q&A #27: How does the storage engine find variable-length columns?
So theoretically you can save a small amount of space through careful column ordering, but in real-life I'd say the chances of you saving much space are pretty small.
Hope this helps.
answered Oct 29 '09 at 06:09 PM
I performed the following tests:
Table A was smaller with no data and bigger with data. 28104 KB -> 46728 KB Table B was the oposite. 30152 KB -> 44744 KB
So unused varchar columns at the end use less space, but it seems that used columns require less space when not at the end.
In both cases we had no difference on INT columns, they always reserve all space available.
Anyway, they have a difference, but its not a HUGE difference but they are not that big. On SQL 2000, text columns must be at the end, but it's not a must these days.
Let me know if this information was useful...