1

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?

EDIT:

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 SELECT or 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 IMAGE or 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?

  • 3
    SQL orders the columns on the page as defined by their ordinal position in sys.columns (so as defined in the tables create statement). There is benefit to putting variable width data at the end and putting fixed width data at the start. But variable width large enough (or large static) will be stored off-page with pointers which will impact performance regardless of where they are located. But these benefits are small numbers and worrying about column order should be one of the last performance levers you reach for. Are you experiencing performance issues? – Jonathan Fite Jun 6 at 14:41
  • We are not running into performance issues. This application is still in the initial design phases. This was just something one of the architects asked for and before we took the effort or reorder the columns on the table I wanted to make sure there was something to support that it wasn't just a futile exercise. – Kirk Saunders Jun 6 at 14:45
  • I would definitely consider it pre-mature optimization. – Jonathan Fite Jun 6 at 14:49
  • 5
    I think we'd all like to see what he read that explained this! – Tony Hinkle Jun 6 at 14:57
  • I have modified the original question with some more information after some more discussions over here. It sounds like this nullable vs non-nullable is an over simplification of some table structure design theories. The question may very well be answered with a clarification of my understanding. – Kirk Saunders Jun 6 at 16:14
1

I am posting this here as an answer so that I have more room to post resources and links, as well as explain a bit.

To answer the specific question about NULLABLE vs NON-NULLABLE columns and their relative cardinality, Kimberly Tripp has an article here: https://www.sqlskills.com/blogs/kimberly/column-order-doesnt-matter-generally-but-it-depends/

Unfortunately I cannot find the reference that supports my statement about having variable width columns at the end of the column order list, but the reasoning was that updating a variable width column at the end of a row would be less likely to cause fragmentation (and off-page spill) than one in the middle of the column order.

But as I've previously said, these are hard to get right and unless you know exactly your data access patterns you will probably get it wrong. There are many other low hanging fruit to tune over worrying about column order. Switching to properly sized static column lengths would be an easier way to get better memory estimates and avoid fragmentation.

Indexes with proper fill factors, statistic updates, stored procedures for data access (so you can more tightly control execution plans) and more. If you are on enterprise edition then you have more options, table compression will allow you to fit more data into memory and that's a far easier (and more widely applicable), partitioning will allow you to do really cool things on management side (although using it to accelerate your reads is a challenge (partition elimination is another hard thing to get right)).

Note, when I say hard to get right, I usually mean that it's hard to get SQL to use them to best advantage, so getting ROI for time spent optimizing is usually much better spent elsewhere.

To answer the last section of the question in the EDIT section. Yes, pre-loading the column with primary keys, foreign keys and the other join columns would probably help. But if you have indexes on those then it shouldn't matter as the index will be used to determine which rows to retrieve and then a keylookup performed. I just don't think spending architecture time on it is worth the impact. You are probably going to have an index on those columns in any case.

  • Thank you Jon for these details and this link. I believe I have gotten the information I am looking for. 1) Nullable columns last can save space but only if they are variable length and never populated. 2) The Physical structure of the table is primarily based on data type not on null vs non-null 3) Time and effort is better spent on things like proper data types, indexes and query design, not trying to cram as many columns on the page as possible by manipulating the logical order in SSMS. – Kirk Saunders Jun 10 at 15:55
0

Case 1 : Not null First

 CREATE TABLE testspace 
                 ( 
                              id       INT IDENTITY PRIMARY KEY , 
                              code     CHAR(20) NOT NULL, 
                              NAME     VARCHAR(100) NOT NULL, 
                              address1 VARCHAR(200), 
                              address2 VARCHAR(200) 
                 )
CREATE NONCLUSTERED INDEX ix_code_testspce ON testspace 
                              ( 
                                                        code 
                              ) 
                              include 
                              ( 
                                                        NAME 
                              )

DECLARE @i INT=0

WHILE(@i<=500000) 
    BEGIN 
      IF(@i%10=0) 
      INSERT INTO testspace VALUES 
                  ( 
                              Replicate('code',5), 
                              Replicate('name',25), 
                              Replicate('add1',50), 
                              Replicate('add2',50) 
                  ) 
      ELSE 
      INSERT INTO testspace VALUES 
                  ( 
                              Replicate('code',5), 
                              Replicate('name',25), 
                              NULL, 
                              NULL 
                  ) 
      SET @i=@i+1 
    ENDsp_spaceused N'TestSpace' 
    --select * from sys.dm_db_database_page_allocations(db_id('DbName'),OBJECT_ID('TestSpace'),1,null,'DETAILED')

SELECT *
    FROM   sys.Dm_db_index_physical_stats(Db_id('DBName'),Object_id('TestSpace'),1,NULL,'DETAILED')

Case 2 : Null column First

        CREATE TABLE testspace1 
  ( 
     address1 VARCHAR(200), 
     address2 VARCHAR(200), 
     NAME     VARCHAR(100) NOT NULL, 
     id       INT IDENTITY PRIMARY KEY, 
     code     CHAR(20) NOT NULL 
  ) 

CREATE NONCLUSTERED INDEX ix_code_testspce1 
  ON testspace1(code) 
  include(NAME) 

DECLARE @i INT=0 

WHILE( @i <= 500000 ) 
  BEGIN 
      IF( @i%10 = 0 ) 
        INSERT INTO testspace1 
                    (address1, 
                     address2, 
                     NAME, 
                     code) 
        VALUES     (Replicate('add1', 50), 
                    Replicate('add2', 50), 
                    Replicate('name', 25), 
                    Replicate('code', 5)) 
      ELSE 
        INSERT INTO testspace1 
                    (address1, 
                     address2, 
                     NAME, 
                     code) 
        VALUES     (NULL, 
                    NULL, 
                    Replicate('name', 25), 
                    Replicate('code', 5)) 

      SET @i=@i + 1 
  END 

SELECT * 
FROM   sys.Dm_db_index_physical_stats(Db_id('Fixture20180417'), 
       Object_id('TestSpace1') 
       , -1, NULL, 'DETAILED') 

Clustered index and Non Clustered Index are created intentionally to check Page Count,fragmentation , Record count on each page etc. of each type of index.

I found that Fragment_Count,Page_Count,record_count are little more in Case 2.

So Case 1 is expected to perform BETTER.

Like I told you I do not have permission to execute DBCC Page , DBCC Show_Statistics etc.

You can perform something similar experiment and check yourself and post your experience here.You take your real and important table example .

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.