February 24, 2009 at 9:15 am
For some reason, which I am now unable to remember, I am of the belief that it is "better" (in terms of disk space efficiency) to put NOT NULL columns prior to nullable columns within a table. A co-worker of mine is adamant that the order of columns based on nullability is a total non-issue. Which one of us is correct? Is there a rule of thumb or is it always very much dependent on a given situation?
Ciao.
February 24, 2009 at 9:51 am
It doesn't matter so far as I know. Try a test like this:
create table dbo.NullTest1 (
ID int identity primary key,
Col1 int null,
Col2 int null,
Col3 int null,
Col4 int null,
Col5 int not null,
Col6 int not null,
Col7 int not null,
Col8 int not null);
go
create table dbo.NullTest2 (
ID int identity primary key,
Col1 int null,
Col5 int not null,
Col2 int null,
Col6 int not null,
Col3 int null,
Col7 int not null,
Col4 int null,
Col8 int not null);
go
insert into dbo.NullTest1 (Col5, Col6, Col7, Col8)
select 1, 1, 1, 1
from dbo.Numbers
where number between 1 and 10000;
insert into dbo.NullTest2 (Col5, Col6, Col7, Col8)
select 1, 1, 1, 1
from dbo.Numbers
where number between 1 and 10000;
I did that, and ended up with 0.445 Meg per table.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
February 24, 2009 at 9:52 am
Your co-worker is correct - column order has no bearing on how SQL Server places the columns in the b-tree.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
February 24, 2009 at 10:08 am
developer6 (2/24/2009)
A co-worker of mine is adamant that the order of columns based on nullability is a total non-issue. Which one of us is correct?
Your colleague is correct. The order of columns defined in the table doesn't affect the order that SQL stores them on the page.
On the page, fixed length columns are stored first, variable length after and there's a null bitmap to determine if columns are null.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply