Showing posts from 2015

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…

Database Size Script

This is just a fancier version of sp_spaceused which writes the data to a temp table which you can sort on use ManGroup set nocount on go declare @TableName varchar(128), @RID int, @MaxRID int, @SQL nvarchar(max) if object_id('tempdb.dbo.#LoopSrc') is not null drop table #LoopSrc create table #LoopSrc ( RID int identity(1,1) primary key clustered, TableName varchar(128) ) if object_id('tempdb.dbo.#Tabs') is not null drop table #Tabs create table #Tabs ( TableName varchar(128), nRows int, nReserved as cast(replace(sReserved, ' KB', '') as int), nData as cast(replace(sData, ' KB', '') as int), nIndexSize as cast(replace(sIndexSize, ' KB', '') as int), nUnused as cast(replace(sUnused, ' KB', '') as int), sReserved varchar(30), sData varchar(30), sIndexSize varchar(30), sUnused varchar(30) ) /***************************** *** INSERT LOOP ITE…

Script Out SQL Row Constructors

A while back I got tired of having to type out the long strings to script out rows from a table, so I built a snippet which does it for you. It’s almost illegible to read, but it determines the columns of the table you choose and builds a string to output each row. I'll admit that this could be further refined to add in some more precise data type handling (perhaps casting date fields to a certain varchar format, or handling more obscure data types), but for most every day purposes, this does just fine. Also, when you copy/paste the results into a query window, you'll have to manually cut out the first "union all" to get it to run. These omissions are just me being lazy, but there's no reason they can't be done, and if you want to make them or wrap this in a procedure, I encourage you to do so. use go set nocount on go declare @string nvarchar(max) = ( --This creates a framework for each column in the chosen table, which then gets serialized and ap…

Ambiguous IN functionality

Here is an interesting item that ended up biting me last week. This is definitely unexpected behavior from my understanding, but I wanted to make sure others were aware to avoid the problems. This code assumes you have a tally table called dbo.numbers with a single integer column named [Num].
--Set up a Temp Table so that we can limit the information we are getting out If OBJECT_ID('tempdb..#NUMCheck') IS NOT NULL DROP TABLE #NUMCheck Create Table #NUMCheck (Number Int) --a Simple IN using a Subquery Select top 100 * from dbo.Numbers where Num in (select Num From #NUMCheck ) --Oops, i forgot to load anything into the Table Insert Into #NUMCheck (Number) Select 2 Union all Select 3 Union all Select 5 Union all select 7 Union all Select 11 --OK, try it again Select top 100 * from dbo.Numbers where Num in (select Num From #NUMCheck ) --?? Oh wait, the Temp Table uses Number not Num --and how it should be written anyway Select top 100 * from dbo.Numbers where Num in (select …