September 22, 2011 at 6:22 am
Hi
I realise that this might not be the brightest of questions, but a while back I remember hearing that if you place Nullable columns at the end of a table the table will perform better if those columns are not frequently used.
I just want to know if there is any truth to this?
Thanks in advance 😀
The Fastest Methods aren't always the Quickest Methods
September 22, 2011 at 6:34 am
Nope.
Internally, on the page, SQL stored the variable length columns after the fixed length, but that's the internal row structure which does not correspond to the table design.
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
September 22, 2011 at 8:40 am
Thanks 🙂
The Fastest Methods aren't always the Quickest Methods
September 22, 2011 at 9:44 am
A side effect of the order you have defined the nullable columns can have an effect on the size of the table, and you may want to take this into consideration. For a complete explanation of how row size if effected read this blog post by Kimberly Trip.
http://sqlskills.com/BLOGS/KIMBERLY/category/SQL-Server-2005.aspx. It may take a good bit of scrolling down the link to get to the specifics.
A short summary:
For years I've been saying that SQL Server doesn't care about the order in which you define the columns of your table because internally SQL Server will re-arrange your columns to store all of the fixed width columns first and the variable columns last. In both the fixed-width portion of the row as well as the variable-width portion of the row, the columns are defined in the order in which they are declared. So, what does matter? It's all in the cost of the variable array's offset values. If the large majority of your NULLable records are at the end, then SQL Server doesn't need to completely populate the variable block array (which saves 2 bytes per column). If you have a table where 36 columns are NULLable and generally they are NULL, then defining those columns at the end of the row can save you space.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply