נוב28
Written by:
ronen ariely
28/11/2018 19:44 
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