Tuning Tip: Always create the index after dumping the data in to temporary tables. Loading the data after index creation takes more time and causes index fragmentation

Demo:

Let’s create two similar temporary tables.

IF OBJECT_ID('tempdb..#Temp1') IS NOT NULL
DROP TABLE #Temp1

CREATE TABLE #Temp1 (
ID VARCHAR(30)
,VAL VARCHAR(50)
)

IF OBJECT_ID('tempdb..#Temp2') IS NOT NULL
DROP TABLE #Temp1

CREATE TABLE #Temp2 (
ID VARCHAR(30)
,VAL VARCHAR(50)
)

For #Temp1 : we will first create the index and then load the table with data

For #Temp2 : we will first load the data, then create the Index.

I am also going to run profiler to capture the stats.

CREATE INDEX IX_Temp ON #Temp1 (
ID
,VAL
)

DECLARE @ID INT = 1

WHILE @ID <= 10000
BEGIN
INSERT INTO #Temp1
VALUES (
RAND()
,RAND()
)

SET @ID = @ID + 1
END


GO

DECLARE @ID INT = 1

WHILE @ID <= 10000
BEGIN
INSERT INTO #Temp2
VALUES (
RAND()
,RAND()
)

SET @ID = @ID + 1
END

CREATE INDEX IX_Temp ON #Temp2 (
ID
,VAL
)

Let’s look at the profiler data.

It is clear from profiler that when we are creating the index after loading the data in to temporary table, then it is taking less time in comparison to first creating the index then load the data.

Also, let’s check the Index fragmentation.

SELECT OBJECT_NAME(ips.OBJECT_ID)
,i.NAME
,ips.index_id
,avg_fragmentation_in_percent
,page_count
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'SAMPLED') ips
INNER JOIN sys.indexes i ON (ips.object_id = i.object_id)
AND (ips.index_id = i.index_id)
WHERE i.NAME = 'IX_Temp'
ORDER BY avg_fragmentation_in_percent DESC

The index on #Temp1 table is completely fragmented as data were loaded after index creation, so the index created on temp1 will not perform efficiently .

We should always create index after loading the data into temp table . It will make the insert faster and fragmentation on temporary table index low