I agree that when working with large data sets temp tables are the way to go.  I think it should be explained to readers that when working with small data sets that are changing there is a good chance that the temp table will decide to update statistics wich will force recompiles.  This can be a significant performance issue.

 Also I think it should be noted that temp table transactions are logged and so they we be rolled back.  There may be a scenario when jusers would not want tansactions rolled out if a procedure fails, maybe audit records for example.  In this case a table variable would be the way to go because its transactions are at the batch level only and would not be roll back.

 JDub

This is a great write up on the relationship between temp tables and recompiles

MSDN Blog 

<p>The below two statements are incorrect as specified by u. I have tested two points in sql server 2005 express edition by creating a table variable.

• You can not create constraints in table variables
• You can not create default values on table variable columns
  

i am able to create a primary key constraint for a table variable and i am able to set a default value for a column in table variable.

can u pls reply me for this comment. is this artical specific for sql server 2000? I didnt tried this in sql server 2000?

Waiting for ur reply.

 Thanks & regards

Naveen

<p>

You can even index a table variable, if it's part of the primary key constraint, see http://msdn.microsoft.com/en-us/library/aa175774(SQL.80).aspx for more info.

 Followup after chatting with some folks, from Gail Shaw (blog):

"you can create primary key and unique constraints on a table variable, if they're done as part of the declaration. Alter table and create index statements fail."

eg:

declare @tbl table (
 id int identity primary key,
 Col1 varchar(20) unique,
 Col2 varchar(30),
 Col3 datetime,
 unique nonclustered (Col2, Col3, id)
)

And from Dave Ballentyne (blog):

"Remember that the optimizer uses statistics to decide to use an index. Table variables have none, it is assumed that they will have 1 row. So if you want to use an index you generally have to use a hint."

followup from Gail again "Unless the index is covering, in which case it will be used without a hint. "

Dear sir, Recently I have gone through your article.  The same topic was given by the link is http://www.sql-server-performance.com/articles/per/temp_tables_vs_variables_p1.aspx

According to you Both temp tables & table variables are logged in the transaction log. But according to the other site The first difference is that transaction logs are not recorded for the table variables. Can you please clarify which one to be considered. Thanks & Regards, HemaBhushan

Can someone explain what is differences between Temp tables and Physical tables? It is usually seen that some temp tables are created in a SP and then will be droped later in the same SP. If we need to run this SP prequently like once a day, should we create physical tables instead of cerating temp tables during SP running time? If we do so, does the SP run faster?

Sorry for this basic questions

Thanks

Very good articile, easy to understand the differences. Keep writing

declare @temp table
(id int primary key,
name varchar(10) default getdate() )

"Yes I have tested and i  a Table Variable can have Constraint."

thanks you

and good night

• You can not create constraints in table variables

Actually, you can with some severe limits.  You can declare a column to be UNIQUE at Table Variable creatio time.

Tim Ford is right on this, Constraint can be created during declartion of table variable but not after it has been declared.

I stumbled across this very good article in my search to an explanation for a weird behaviour between two identical SQL servers (2008 R2, Dell PowereEdge 210R, 4GByte RAM). I run a view on which I group later and in total after grouping, there are never more than 30-50 rows max returned. That's why I've decided to do the post processing by means of storing the result (after grouped) in a table variable and do further processings with it.

However, the one server gives me a time out, the other one returns a result set within 2s. Before grouping, we're talking about 1500 - 3500 raw data rows. I'm running the grouping statement using the view with an insert transact on the table variable. Probably the wrong approach, ist it ?

1. VIEW

CREATE VIEW [dbo].[BookPerf]

AS

SELECT DISTINCT 

c.Jobnumber, 

s.[Description] AS Shift, 

c.ReelStand, 

c.Batch, 

c.DateStamp, 

c.bDate AS [Day], 

o.FirstName + ' ' + o.Surname AS UserName, 

c.oASC,

c.oDESC, 

t.MakeReadyDateTime AS mReady,

ISDATE(lm.Datestamp) AS LM, 

lm.Datestamp AS dLM,

NULLIF(COALESCE(lm.Username,c.Username),c.Username) AS uLM, 

t.ProductionStartDateTime AS pStart, 

ISDATE(fb.DateStamp) AS LS, 

fb.DateStamp AS dLS, 

ISDATE(db.DateStamp) AS DB, 

db.DateStamp AS dDB, 

t.ProductionFinishDateTime AS pEnd, 

ISDATE(lr.DateStamp) AS LR, 

lr.DateStamp AS dLR, 

COALESCE(lr.Username,c.Username) AS uLR

FROM ( 

SELECT 

Jobnumber, 

ReelStand, 

Batch, 

DateStamp, 

CAST(DateStamp AS DATE) AS bDate,

ROW_NUMBER() OVER (PARTITION BY Jobnumber, ReelStand ORDER BY DateStamp ASC) AS oASC, 

ROW_NUMBER() OVER (PARTITION BY Jobnumber, ReelStand ORDER BY DateStamp DESC) AS oDESC, 

ROW_NUMBER() OVER (PARTITION BY Batch ORDER BY DateStamp ASC) AS cPiece, 

Username 

FROM StockControl.dbo.Consume

) c   

INNER JOIN StockControl.dbo.Batch AS b ON (b.Batch = c.Batch) 

INNER JOIN StockControl.dbo.Tickets t ON (c.Jobnumber = t.TicketID)

LEFT OUTER JOIN StockControl.dbo.Operator o ON c.Username = o.UserName 

LEFT OUTER JOIN StockControl.dbo.Shifts s ON t.EndShift = s.Shift

-- late start bookings

LEFT OUTER JOIN Consume fb ON

(fb.Jobnumber = c.Jobnumber) AND 

(fb.Batch = c.Batch) AND 

(c.oASC = 1) AND 

(fb.DateStamp > t.ProductionStartDateTime) 

-- late return bookings 

LEFT OUTER JOIN ( 

SELECT *, ROW_NUMBER() OVER (PARTITION BY Batch ORDER BY DateStamp ASC) AS rPiece 

FROM StockControl.dbo.[Return]) lr ON

(c.Batch = lr.Batch) AND 

(c.cPiece = lr.rPiece) AND 

(c.oDESC = 1) AND 

(lr.DateStamp > t.ProductionFinishDateTime) 

-- double bookings 

LEFT OUTER JOIN ( 

SELECT *, ROW_NUMBER() OVER (PARTITION BY Jobnumber, ReelStand ORDER BY DateStamp) AS oASC 

FROM StockControl.dbo.Consume) dB ON 

(c.oASC = db.oASC + 1) AND 

(c.DateStamp = db.DateStamp) AND 

(c.Jobnumber = db.Jobnumber) AND 

(c.ReelStand = db.ReelStand) 

-- late moves 

LEFT OUTER JOIN StockControl.dbo.[Move] lm ON 

(c.Batch = lm.Batch) AND (

(c.oASC = 1 AND lm.Datestamp BETWEEN t.MakeReadyDateTime AND c.DateStamp) 

OR 

(c.oASC > 1 AND DATEDIFF(mi,lm.Datestamp,c.DateStamp)<5) 

) 

WHERE 

(t.[Status] IN (3,4)) AND 

(t.GrossWeight > 1) 

GO

Hello - I just found this article - quite informative as I am reasonably new to SQL (previously used Visual Foxpro).  I do have a question about temp tables.  Is it possible to add a field with auto_increment functionality.  I need this for an application i am working on.  I tried playing with row numbers - got no where!

I just found a solution

SELECT ROW_NUMBER() OVER (ORDER BY ID) AS ROW, ID FROM #TEMPGLBANKLIST

where #tempgl contains gl accounts in id field

Thank you so much Sir.. Ite really nice.. Keep writing.. 

Is there any performance difference between Temp Table vs Table valued functions?