This post includes two simple demo code to demonstrate (1) how order of columns can impact the size of the table and the database, and (2) That order of columns can be related to errors in the execution.
For the sake of the demo, we will create two databases:
use master
GO
ALTER DATABASE [InternalsTableStructure01] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
ALTER DATABASE [InternalsTableStructure02] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO
DROP DATABASE IF EXISTS InternalsTableStructure01
DROP DATABASE IF EXISTS InternalsTableStructure02
GO
CREATE DATABASE InternalsTableStructure01
CREATE DATABASE InternalsTableStructure02
GO
Now let's execute the next script:
use InternalsTableStructure01
GO
CREATE TABLE T(-- ,
Column_Two char(1) null,
Column_One char(4000) null
)
GO
INSERT T(Column_One,Column_Two)
SELECT REPLICATE('b',4000), 'a'
GO
ALTER TABLE T DROP COLUMN Column_One
GO
ALTER TABLE T ADD Column_Three char(3000) null
GO
INSERT T(Column_Two,Column_Three)
SELECT TOP 10000 'a', REPLICATE('b',3000)
FROM sys.all_objects t1
CROSS JOIN sys.all_objects t2
GO
-- number of pages
SELECT type_desc, SUM(total_pages) total_pages, SUM(used_pages) used_pages,SUM(data_pages) data_pages
FROM sys.allocation_units
GROUP BY type_desc
GO
-- Size of databases
SELECT sys.databases.name,
CONVERT(VARCHAR,SUM(size)*8/1024)+' MB' AS [Total disk space]
FROM sys.databases
JOIN sys.master_files
ON sys.databases.database_id=sys.master_files.database_id
where sys.databases.name = 'InternalsTableStructure01' or sys.databases.name = 'InternalsTableStructure02'
GROUP BY sys.databases.name
ORDER BY sys.databases.name
GO
Note! The result might be different in your server according to the configuration of parameters like table DATA_COMPRESSION and FILLFACTOR, or database SIZE and FILEGROWTH.
Now go back to the script and change the database name from InternalsTableStructure01 to InternalsTableStructure02.
Change the order of the columns so Column_One will be the first.
Execute the query above after the changes made and compare the results.
Notice that the number of pages for the second table and the size of the database is about 2 times the result we got for the first table, and the only difference is the order of columns!
Demo 2: errors
/***********************************************
************************************************
*** Case Study: Is Order of Column Important?!?
***
*** Execute the following script twice!
*** 1. First time in the CREATE TABLE stetement use Column_One first:
*** -- CREATE TABLE T(Column_One char(4000) null,Column_Two char(4000) null)
*** 2. Second time ONLY CHANGE THE ORDER OF COLUMNS! use Column_Two first:
*** -- CREATE TABLE T(Column_Two char(4000) null,Column_One char(4000) null)
***
***********************************************/
/******************************************* */
use InternalsTableStructure01
GO
DROP TABLE IF EXISTS T
GO
CREATE TABLE T(-- ,
Column_Two char(4000) null,
Column_One char(4000) null
)
GO
INSERT T(Column_One,Column_Two)
SELECT TOP 1000 'a', REPLICATE('b',4000)
GO
/********************************************************** */
/**************************************** Playing Start Here */
---------------------------- DROP Column_One and Create Column_One
-- Droping column does not remove the column behind the scenes!
ALTER TABLE T DROP COLUMN Column_One
GO
-- If Column_One 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 Column_One 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 Column_One char(1) null
GO
-----------------------------------------------------------------------
-- If Column_One 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 Column_One 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 */
/********************************************************** */
PRINT 'END!'
GO
Note! It is very common mistake to think that the order of columns is not important.
https://stackoverflow.com/questions/1605144/how-to-change-column-order-in-a-table-using-sql-query-in-sql-server-2005/1605167
https://social.msdn.microsoft.com/Forums/en-US/dfcc13cf-d852-4908-90ad-da24dfb5179b/order-of-columns?forum=sqlgetstarted