{"id":530,"date":"2009-08-10T07:10:00","date_gmt":"2009-08-10T07:10:00","guid":{"rendered":"\/blogs\/kimberly\/post\/Column-order-doesnt-matter-generally-but-IT-DEPENDS!.aspx"},"modified":"2013-01-11T23:07:56","modified_gmt":"2013-01-12T07:07:56","slug":"column-order-doesnt-matter-generally-but-it-depends","status":"publish","type":"post","link":"https:\/\/www.sqlskills.com\/blogs\/kimberly\/column-order-doesnt-matter-generally-but-it-depends\/","title":{"rendered":"Column order doesn’t matter… generally, but – IT DEPENDS!"},"content":{"rendered":"

\nOK, 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?\n<\/p>\n

\nIt'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.\n<\/p>\n

\nThe following script will show you how the maximum length of the row changes based on whether or not a later column in the variable block is NOT NULL – even when most\/all of the prior columns are!\n<\/p>\n

\n\tCREATE TABLE RowSizeVariableBlock
\n\t(
\n\tID int NOT NULL identity,
\n\tc01 char(10) NOT NULL default 'test',
\n\tc02 datetime2(7) NOT NULL default sysdatetime(),
\n\tc03 char(80) NOT NULL default 'junk',
\n\tc04 varchar(100) NULL,
\n\tc05 varchar(100) NULL,
\n\tc06 varchar(100) NULL,
\n\tc07 varchar(100) NULL,
\n\tc08 varchar(100) NULL,
\n\tc09 varchar(100) NULL,
\n\tc10 varchar(100) NULL,
\n\tc11 varchar(100) NULL,
\n\tc12 varchar(100) NULL,
\n\tc13 varchar(100) NULL,
\n\tc14 varchar(100) NULL,
\n\tc15 varchar(100) NULL,
\n\tc16 varchar(100) NULL,
\n\tc17 varchar(100) NULL,
\n\tc18 varchar(100) NULL,
\n\tc19 varchar(100) NULL,
\n\tc20 varchar(100) NULL,
\n\tc21 varchar(100) NULL,
\n\tc22 varchar(100) NULL,
\n\tc23 varchar(100) NULL,
\n\tc24 varchar(100) NULL,
\n\tc25 varchar(100) NULL,
\n\tc26 varchar(100) NULL,
\n\tc27 varchar(100) NULL,
\n\tc28 varchar(100) NULL,
\n\tc29 varchar(100) NULL,
\n\tc30 varchar(100) NULL,
\n\tc31 varchar(100) NULL,
\n\tc32 varchar(100) NULL,
\n\tc33 varchar(100) NULL,
\n\tc34 varchar(100) NULL,
\n\tc35 varchar(100) NULL,
\n\tc36 varchar(100) NULL,
\n\tc37 varchar(100) NULL,
\n\tc38 varchar(100) NULL,
\n\tc39 varchar(100) NULL,
\n\tc40 varchar(100) NULL
\n\t)
\n\tgo <\/p>\n

\n\tinsert RowSizeVariableBlock DEFAULT VALUES
\n\tgo\n\t<\/p>\n

\n\tselect * from RowSizeVariableBlock
\n\tgo\n\t<\/p>\n

\n\tselect * from sys.dm_db_index_physical_stats
\n\t(db_id(), object_id('RowSizeVariableBlock'), null, null, 'detailed')
\n\t— review "max" record size = 114
\n\tgo\n\t<\/p>\n

\n\tinsert RowSizeVariableBlock (c01, c03, c20)
\n\tvalues ('med row', 'up to c20', 'test')
\n\tgo\n\t<\/p>\n

\n\tselect * from RowSizeVariableBlock
\n\tgo\n\t<\/p>\n

\n\tselect * from sys.dm_db_index_physical_stats
\n\t(db_id(), object_id('RowSizeVariableBlock'), null, null, 'detailed')
\n\t— review "max" record size = 154
\n\tgo\n\t<\/p>\n

\n\tinsert RowSizeVariableBlock (c01, c03, c30)
\n\tvalues ('med+ row', 'up to c30', 'test')
\n\tgo\n\t<\/p>\n

\n\tselect * from RowSizeVariableBlock
\n\tgo\n\t<\/p>\n

\n\tselect * from sys.dm_db_index_physical_stats
\n\t(db_id(), object_id('RowSizeVariableBlock'), null, null, 'detailed')
\n\t— review "max" record size = 174
\n\tgo\n\t<\/p>\n

\n\tinsert RowSizeVariableBlock (c01, c03, c40)
\n\tvalues ('large row', 'up to c40', 'test')
\n\tgo\n\t<\/p>\n

\n\tselect * from RowSizeVariableBlock
\n\tgo\n\t<\/p>\n

\n\tselect * from sys.dm_db_index_physical_stats
\n\t(db_id(), object_id('RowSizeVariableBlock'), null, null, 'detailed')
\n\t— review "max" record size = 194
\n\tgo\n\t<\/p>\n

\n\t<\/span>\n\t<\/p>\n

\n\tWhile there are some other optimizations at this level, <\/span>most<\/span><\/strong> tables cannot benefit from this as the data populations aren't as predictable nor are most tables filled with so many variable-width and NULLable columns. However, if you do have this data pattern, defining these columns at the end of your table's definition – MIGHT save a tremendous amount of space, especially when this table is very large!<\/span>\n\t<\/p>\n

\t<\/span><\/span>Paul's blogged more on these structures as well as the NULL bitmap here: https:\/\/www.sqlskills.com\/blogs\/PAUL\/post\/Misconceptions-around-null-bitmap-size.aspx<\/a>. <\/span><\/span><\/p>\n

\n\tEnjoy! And, thanks for reading,
\n\tkt<\/span>\n\t<\/p>\n<\/blockquote>\n","protected":false},"excerpt":{"rendered":"

OK, 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 […]<\/p>\n","protected":false},"author":7,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[25,37,65,66],"tags":[],"class_list":["post-530","post","type-post","status-publish","format-standard","hentry","category-design","category-inside-the-storage-engine","category-sql-server-2005","category-sql-server-2008"],"_links":{"self":[{"href":"https:\/\/www.sqlskills.com\/blogs\/kimberly\/wp-json\/wp\/v2\/posts\/530","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.sqlskills.com\/blogs\/kimberly\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.sqlskills.com\/blogs\/kimberly\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/kimberly\/wp-json\/wp\/v2\/users\/7"}],"replies":[{"embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/kimberly\/wp-json\/wp\/v2\/comments?post=530"}],"version-history":[{"count":0,"href":"https:\/\/www.sqlskills.com\/blogs\/kimberly\/wp-json\/wp\/v2\/posts\/530\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.sqlskills.com\/blogs\/kimberly\/wp-json\/wp\/v2\/media?parent=530"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/kimberly\/wp-json\/wp\/v2\/categories?post=530"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/kimberly\/wp-json\/wp\/v2\/tags?post=530"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}