Showing posts from June, 2017

Temp Tables vs Table Varibles: The Great Debate

There seems to be a lot of confusion around the differences between  temp tables and table variables (in the classic sense; I'm not talking about in memory table types or anything here). Some people say you should only use table variables. Some people say you should only use temp tables. Most people caution a more nuanced approach. Here I want to cover what I think are the biggest issues in an attempt to shed some light on this surprisingly tricky topic.

Statistics This is usually the most important factor when considering which one to use. It boils down to two facts, and everything else flows from these: Temp Tables maintain statistics, Table Variables do not.

Table Variables Because table variables maintain no statistics, the Query Optimizer always assumes the contain exactly one row. As a result, any joins against a table variable, unless explicitly told to behave otherwise will (probably) alwaysbe a nested loop join.

As long as the number of rows in the table variable are small…

Primary Key columns in INCLUDE list

I was reviewing someone's code the other day and I saw they had put primary key columns in the INCLUDE list of a non-clustered index

-- Create a table if object_id('tempdb.dbo.#PhoneBook') is not null drop table #PhoneBook create table #PhoneBook ( FirstName nvarchar(500), LastName nvarchar(500), PhoneNumber varchar(20), DOB datetime ) create clustered index #IXC__#PhoneBook__LastName on #PhoneBook (LastName) create nonclustered index #IXN__#PhoneBook__PhoneNumber on #PhoneBook (PhoneNumber)
A little background on the physical structure of non-clustered indexes for those who might not know. A non-clustered index consists of at least two, and up to three components. All non-clustered indexes store the index key (in my example above, this would be [PhoneNumber] in the nonclustered index) as well as a pointer back to the clustered index (in this case, the columns [LastName]).

The Phone Book example is the classic indexing example thrown around. The clustered …