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
אוק13

Written by: ronen ariely
13/10/2019 10:00 RssIcon

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.

Demo 1: Size

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