Table Creation and Data Storage

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.

I performed the following tests:

set nocount on

create table a ( a varchar(10) not null, b int null, c varchar(10) null )
declare @c int = 0
while (@c < 1000000 ) begin
    insert into a(a) values ( 'abcdefghij' )
    select @c = @c + 1
end
exec sp_spaceused 'a' --a   1000000    	28104 KB	28072 KB	8 KB	24 KB
update a set b = 999999999
exec sp_spaceused 'a' --a   1000000    	28104 KB	28072 KB	8 KB	24 KB
update a set c = '999999999'
exec sp_spaceused 'a' --a   1000000    	46728 KB	46664 KB	8 KB	56 KB
Go

create table b ( c varchar(10) null, b int null, a varchar(10) not null )
declare @c int = 0
while (@c < 1000000 ) begin
    insert into b(a) values ( 'abcdefghij' )
    select @c = @c + 1
end
exec sp_spaceused 'b' --b   1000000    	30152 KB	30080 KB	8 KB	64 KB
update b set b = 999999999
exec sp_spaceused 'b' --b   1000000    	30152 KB	30080 KB	8 KB	64 KB
update b set c = '999999999'
exec sp_spaceused 'b' --b   1000000    	44744 KB	44728 KB	8 KB	8 KB
go

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...

I think I understand what Paul meant to say... if a NULL variable length column is at the end of the row, it won't have an entry in the column offset array. So if the last three columns (col7, col8, col9) are all nullable, but the last one (col9) has a value and the two previous ones don't, then there will be two bytes offset info for each of the three. There will be zero bytes for the actual NULL data, but we need the offsets for any columns occurring before the last non-null variable length column. But if col7 has a value (i.e. is non-null), and col8 and col9 are null, there will not be offsets for col8 and col9, saving 4 bytes.

So as Paul says, the order could save a bit of space, but the cost might not be worth the savings. If you are adding or dropping columns in a table with ALTER TABLE, you might not know which columns are actually physically stored last.

HTH ~Kalen