Background

Wanted to put together a short post on things to consider when utilizing temporary tables.

Specifically, indexing options.

SQL

Outline

1. Table
   • Applicant Table
     • Structure
       • ssn char(11)
     • Index Options
       • Clustered
       • Heap
2. Review
   • Index Metadata
   • Table Size
   • Populate Duration

Code

use [tempdb]
go

set XACT_ABORT on
go
set nocount on
go

declare @tableSignature sysname
declare @id int
declare @idMax int

declare @dtHeapStart datetime
declare @dtHeapEnd   datetime

declare @dtClusteredStart datetime
declare @dtClusteredEnd   datetime

declare @dtStart         datetime
declare @dtEnd           datetime
declare @iDuration  	 int

--set @idMax = 1E5
set @idMax = 5E5
set @tableSignature = '#applicant'

if object_id('#applicantClustered') is not null
begin

    drop table #applicantClustered

end


if object_id('#applicantHeap') is not null
begin

    drop table #applicantHeap

end


CREATE TABLE #applicantHeap
(
      [ssn] char(11) NOT NULL
    
    , index [INDX_SSN]
        (
            [ssn]
        ) 
);

CREATE TABLE #applicantClustered
(
      [ssn] char(11) NOT NULL
    
    , index [INDX_SSN] clustered
        (
            [ssn]
        ) 
);

set @id =1

set @dtStart = getdate()
set @dtHeapStart = getdate();

while (@id <= @idMax)
begin

    insert into #applicantHeap
    ([ssn])
    values(@id);

    set @id= @id+ 1

end

set @dtHeapEnd = getdate();

set @id =1

set @dtClusteredStart = getdate()

while (@id <= @idMax)
begin

    insert into #applicantClustered
    ([ssn])
    values(@id);

    set @id= @id+ 1

end

set @dtClusteredEnd = getdate()
set @dtEnd = getdate()
set @iDuration = datediff(second, @dtStart, @dtEnd)

select 
          [object] = tblSO.[name]
        , [indexID] = tblSI.[index_id]
        , [index] = tblSI.[name]
        , [indexType] = tblSI.[type_desc]

from   sys.indexes tblSI

inner join sys.objects tblSO
        on tblSO.object_id = tblSI.object_id

where tblSO.[type] = 'U'

and  tblSO.[name] like @tableSignature + '%'

exec sp_spaceused '#applicantHeap'
exec sp_spaceused '#applicantClustered'

select	
	   = 'Heap'
	, [start] = @dtHeapStart
	, [end] = @dtHeapEnd
	, [duration] = datediff(second,@dtHeapStart, @dtHeapEnd) 
	, [%]
		= cast
			(
				(
					  ( datediff(second,  @dtHeapStart, @dtHeapEnd) * 100.00 )
					/ ( NULLIF(@iDuration, 0) )
				)
				as decimal(10, 2)
			)


union
select
	   = 'Clustered'
	, [start] = @dtClusteredStart
	, [end] = @dtClusteredEnd
	, [duration] = datediff(second, @dtClusteredStart, @dtClusteredEnd) 
	, [%]
		= cast
			(
				(
					  ( datediff(second,  @dtClusteredStart, @dtClusteredEnd) * 100.00 )
					/ ( NULLIF(@iDuration, 0) )
				)
				as decimal(10, 2)
			)


if object_id('#applicantClustered') is not null
begin

    drop table #applicantClustered

end


if object_id('#applicantHeap') is not null
begin

    drop table #applicantHeap

end
go

Output

Explanation

1. Metadata ( sys.indexes )
   • Heap
     • Index Type = Heap
     • Index Type = Non-Clustered
   • Clustered
2. Table Size ( sp_spaceused )
   • Heap
     • 31176 KB ( 32 MB )
   • Clustered
     • 16448 KB ( 16 MB )
3. Duration
   • Heap
     • 29 seconds ( 56.86% of Total )
   • Clustered
     • 22 seconds ( 43.14% of Total )

Summary

It seems that Clustered Index should be considered over non-clustered indexes.

Having having said that there is a cost to Clustered Index:

1. Update Locks
   • When searching for Clustered Index location
2. HotSpots
   • HotSpots at insert location
   • Contention should not matter as only the current session has access to temp table