We are in the process of building out a database for a new application. I got some feedback from the architect that he read somewhere there is a memory utilization benefit for having all of the non-nullable columns in a table first, then all the nullable columns. If there are nullable columns in-between non-nullable columns that some kind of memory or storage benefit is forfeited because those nullable columns are between non-nullable columns.
The columns were ordered in a slightly ambiguous order but in general it was prioritized for columns which were more likely to be used first.
I was under the impression that SSMS orders the columns on the page file by its own logic instead of the specific order they are created on the table. That the order I put the columns in on the table was totally independent from how the data was stored in the page file.
Is there any documentation somewhere that would support this finding/understanding?
Having some more conversations around figuring out where this idea came from. The request was an over simplification of having high density of data, or columns used most frequently in
JOIN operations in the front of the table to prevent them from moving to the Overflow section of the page file.
That the order of the columns assigned to the table is the same order the data is added onto the page file. For a given row, the data is stored on the page file in the order the columns are set on the table. If the row holds more data than the page file allows (assuming we are not using anything that would go into a LOB
VARCHAR(MAX) or something like that) then the remainder is put onto the overflow file. If that overflow data is needed than there is extra effort and time spent doing the lookup to that overflow file. That can result in noticeable impacts to performance in high row environments (10's or 100's of millions of rows +). So we would want to prioritize the column order in how often we think that column will be accessed, not necessarily if it is
NULL or not.
Does that understanding/analysis sound right?