en-UShe-IL
You are here:   Blog
Register   |  Login

Blog Archive:

Maximize
* Can be used in order to search for older blogs Entries

Search in blogs


Blog Categories:

Maximize
* Can be used in order to search for blogs Entries by Categories

Blog Tags:

Maximize
* Can be used in order to search for blogs by keywords

TNWikiSummit


Awared MVP 


 


Microsoft® Community Contributor 


Microsoft® Community Contributor


 Read first, before you you use the blog! Maximize
נוב28

Written by: ronen ariely
28/11/2018 19:44 RssIcon

Hi guys,

This post based on my answer at the MSDN forums several days ago. 

You can (and I HIGHLY recommend to do so) check the original thread in the following link and read more:
https://social.msdn.microsoft.com/Forums/en-US/dfcc13cf-d852-4908-90ad-da24dfb5179b/order-of-columns?forum=sqlgetstarted

This question is VRY common in all forums probably and unfortunately you can find wrong responds coming from experts. Therefore, in addition to the link above, i did a short search on stackoverflow in order to have another sample of discussion:
https://stackoverflow.com/questions/1605144/how-to-change-column-order-in-a-table-using-sql-query-in-sql-server-2005/160

Is Order Important?

The short answer is YES! The order of columns can significantly affect performance and even be related to errors which raise only in specific order.

This is advance topic which require understanding of the internals of SQL Server :-)




DEMO 1

I will proof it in a simple example but there are a lot of different cases where it might come in practical cases. This is only the first that came to my mind which is simple to be proven.

The procedure will be like this: (1) I will create new table (2) I will drop specific column by name (3) I will add new column

This procedure I will do twice where the only different is the order of the columns in step 1

First I will create new database and a SP which will help me to examine the internals

create database RonenArielyDB
GO
 
use RonenArielyDB
GO
 
create procedure p
    @Table sysname
as
    select p.object_id, p.index_id,
        pc.leaf_null_bit,
        coalesce(cx.name, c.name) as column_name,
        pc.leaf_offset,
        pc.partition_column_id,
        pc.max_inrow_length,
        pc.max_length,
        pc.key_ordinal,
        pc.is_nullable,
        pc.is_dropped,
        pc.is_uniqueifier,
        pc.is_sparse,
        pc.is_anti_matter
    from sys.system_internals_partitions p
    join sys.system_internals_partition_columns pc on p.partition_id = pc.partition_id
    left join sys.index_columns ic on p.object_id = ic.object_id and ic.index_id = p.index_id and ic.index_column_id = pc.partition_column_id
    left join sys.columns c on p.object_id = c.object_id and ic.column_id = c.column_id
    left join sys.columns cx on p.object_id = cx.object_id and p.index_id in (0,1) and pc.partition_column_id = cx.column_id
    where p.object_id = object_id(@Table)
GO

Now we can move to the demo:

Step 1: We create two tables with the same columns in different order

-- We create two table with the same columns in different order
DROP TABLE IF EXISTS T1;
CREATE TABLE T1(
    FixedDataTypeIdentity int identity(1,1),
    FixedDataType1 char(10) null,
    FixedDataType2 char(10) null
)
GO
DROP TABLE IF EXISTS T2;
CREATE TABLE T2(
    FixedDataTypeIdentity int identity(1,1),
    FixedDataType2 char(10) null,
    FixedDataType1 char(10) null
)
GO

Let's examine the internals of the tables:

execute p @Table = 'T1'
GO
/*
object_id   index_id    leaf_null_bit   column_name leaf_offset partition_column_id max_inrow_length    max_length  key_ordinal is_nullable is_dropped  is_uniqueifier  is_sparse   is_anti_matter
709577566   0   1   FixedDataTypeIdentity   4   1   4   4   0   0   0   0   0   0
709577566   0   2   FixedDataType1  8   2   10  10  0   1   0   0   0   0
709577566   0   3   FixedDataType2  18  3   10  10  0   1   0   0   0   0
*/
execute p @Table = 'T2'
GO
/*
object_id   index_id    leaf_null_bit   column_name leaf_offset partition_column_id max_inrow_length    max_length  key_ordinal is_nullable is_dropped  is_uniqueifier  is_sparse   is_anti_matter
725577623   0   1   FixedDataTypeIdentity   4   1   4   4   0   0   0   0   0   0
725577623   0   2   FixedDataType2  8   2   10  10  0   1   0   0   0   0
725577623   0   3   FixedDataType1  18  3   10  10  0   1   0   0   0   0
*/

You can notice that these tables has different structure behind the scene!

In a second or two, I will show you that ORDER IS IMPORTANT!

Focus on the value of the leaf_offset which tell us where the column is physically in the row structure. You can see that the second column is on leaf_offset 8 and the third one on leaf_offset 18 which make sense since the second column has 10 bytes so it start in leaf_offset 8 the next fixed length column will start at 8+10

Step 2: Now let's drop the column FixedDataType1 in both tables

-- now we remove column FixedDataType1 from both tables
ALTER TABLE T1
    DROP COLUMN FixedDataType1
GO
ALTER TABLE T2
    DROP COLUMN FixedDataType1
GO
 
execute p @Table = 'T1'
GO
/*
object_id   index_id    leaf_null_bit   column_name leaf_offset partition_column_id max_inrow_length    max_length  key_ordinal is_nullable is_dropped  is_uniqueifier  is_sparse   is_anti_matter
709577566   0   1   FixedDataTypeIdentity   4   1   4   4   0   0   0   0   0   0
709577566   0   2   NULL    8   67108865    10  10  0   1   1   0   0   0
709577566   0   3   FixedDataType2  18  3   10  10  0   1   0   0   0   0
*/
execute p @Table = 'T2'
GO
/*
object_id   index_id    leaf_null_bit   column_name leaf_offset partition_column_id max_inrow_length    max_length  key_ordinal is_nullable is_dropped  is_uniqueifier  is_sparse   is_anti_matter
725577623   0   1   FixedDataTypeIdentity   4   1   4   4   0   0   0   0   0   0
725577623   0   2   FixedDataType2  8   2   10  10  0   1   0   0   0   0
725577623   0   3   NULL    18  67108865    10  10  0   1   1   0   0   0
*/

you can notice that the column was only deleted in the metadata but it is still in the row's structure (simply marked as null). This mean that any row that you add to the table will still use this space. Did you know this ? :-)

You can notice that in table T1 the NULL space is at the middle of the row's structure while in table T2 it is at the end, and this will make all the difference!

Step 3: We will add new column to both tables

-- let's add new column length 10.
ALTER TABLE T1
    ADD FixedDataType3 char(20)
GO
ALTER TABLE T2
    ADD FixedDataType3 char(20)
GO
 
execute p @Table = 'T1'
GO
/*
object_id   index_id    leaf_null_bit   column_name leaf_offset partition_column_id max_inrow_length    max_length  key_ordinal is_nullable is_dropped  is_uniqueifier  is_sparse   is_anti_matter
709577566   0   1   FixedDataTypeIdentity   4   1   4   4   0   0   0   0   0   0
709577566   0   2   NULL    8   67108865    10  10  0   1   1   0   0   0
709577566   0   3   FixedDataType2  18  3   10  10  0   1   0   0   0   0
709577566   0   4   FixedDataType3  28  4   20  20  0   1   0   0   0   0
*/
execute p @Table = 'T2'
GO
/*
object_id   index_id    leaf_null_bit   column_name leaf_offset partition_column_id max_inrow_length    max_length  key_ordinal is_nullable is_dropped  is_uniqueifier  is_sparse   is_anti_matter
725577623   0   1   FixedDataTypeIdentity   4   1   4   4   0   0   0   0   0   0
725577623   0   2   FixedDataType2  8   2   10  10  0   1   0   0   0   0
725577623   0   3   NULL    18  67108865    10  10  0   1   1   0   0   0
725577623   0   4   FixedDataType3  18  4   20  20  0   1   0   0   0   0
*/

WALLA!!!

Check the leaf_offset of the last column in each table :-)

In table T1 it is 28 and in table T2 it is only 18, which mean that for each row you will insert the tables, table T2 will need much less space and the performance will be much better behind the scene.

All the different was in the first step where we created the tables using columns in different order

ORDER CAN BE EXTREMELY IMPORTANT in some cases ;-)

I hope this was clear, and again this is only one simple example where order of columns can make a difference





Demo 2

Update 2019-03-21: adding example from my last lecture.

This demo shows how the order of columns can impact errors as well as the size of the table

/***********************************************
************************************************
*** Case Study: Is Order of Column Important?!?
***
*** Execute the following script twice!
*** 1. First time in the CREATE TABLE stetement use Col1 first:
***    -- CREATE TABLE T(Col1 char(4000) null,Col2 char(4000) null)
*** 2. Second time ONLY CHANGE THE ORDER OF COLUMNS! use Col2 first:
***    -- CREATE TABLE T(Col2 char(4000) null,Col1 char(4000) null)
***
***********************************************/
/*******************************************  */
-- Create the databaseifdoes not exists:
-- CREATE DATABASE InternalsTableStructure01
-- GO
use InternalsTableStructure01
GO
 
DROP TABLE IF EXISTS T
GO
CREATE TABLE T(-- ,
    Col1 char(4000) null,
    Col2 char(4000) null
)
GO
 
 
 
INSERT T(Col1,Col2)
SELECT TOP 1000 'a', REPLICATE('b',4000)
GO
 
 
/**********************************************************  */
/**************************************** Playing Start Here */
---------------------------- DROP Col1 Create Col1
-- Droping column does not remove the column behind the scenes!
ALTER TABLE T DROP COLUMN Col1
GO
-- If Col1 is not the last column, 
--    then the new column is added after the old column in addition to the old column!
--    This mean that the PHYSICAL length of table's row behind the scenes is 4000+4000+1 = 8001
-- If Col1 is the last column, 
--    then the new column is added in-place!
--    This mean that the PHYSICAL length of table's row behind the scenes is only 4000+1 = 4001
ALTER TABLE T ADD Col1 char(1) null
GO
 
-----------------------------------------------------------------------
-- If Col1 is not the last column, 
--    The PHYSICAL length of table's row behind the scenes is 8001
--    THEREFORE, adding another column length 3000 raise an error!
-- If Col1 is the last column, 
--    The PHYSICAL length of table's row behind the scenes is 4001
--    THEREFORE, adding another column length 3000 is OK :-)
ALTER TABLE T ADD Col3 char(3000) null
GO
 
/****************************************** Playing End Here */
/**********************************************************  */
 
 
 
-->> "The column order is just a "cosmetic" thing we humans care about" ?!?
PRINT 'END!'
GO