x

Table Creation and Data Storage

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?

Thanks; Greg

more ▼

asked Oct 22, 2009 at 11:26 AM in Default

Blackhawk-17 gravatar image

Blackhawk-17
11.9k 28 31 36

(comments are locked)
10|1200 characters needed characters left

4 answers: sort voted first

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.

more ▼

answered Oct 22, 2009 at 10:12 PM

Bob Hovious gravatar image

Bob Hovious
1.6k 5 6 9

Obviously, I need to do more reading on internals, since I can't argue with the numbers. Although I got slightly different results when I ran Gustavo's code, they were still consistent with one taking more to store once all columns were loaded with data, and that loading data into the fixed length fields made no difference.
Oct 22, 2009 at 11:53 PM Bob Hovious
I had the same results but it makes you scratch your head that almost identucal data would end up utilizing storage differently. There is an algorithm at work somewhere that causes this discrepancy. Guess it's a question for Ms Delaney :)
Oct 23, 2009 at 09:17 AM Blackhawk-17
(comments are locked)
10|1200 characters needed characters left

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.

more ▼

answered Oct 29, 2009 at 06:09 PM

Paul Randal gravatar image

Paul Randal
51

(comments are locked)
10|1200 characters needed characters left

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

more ▼

answered Oct 22, 2009 at 02:47 PM

Gustavo gravatar image

Gustavo
592 4 4 7

(comments are locked)
10|1200 characters needed characters left

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

more ▼

answered Nov 09, 2009 at 08:57 PM

Kalen Delaney gravatar image

Kalen Delaney
21

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x108
x30

asked: Oct 22, 2009 at 11:26 AM

Seen: 1152 times

Last Updated: Oct 22, 2009 at 11:26 AM