pkasemsant (8/8/2011)
As developer and still got lots to learn in SQL Server administration, can anyone tell me, which is better using temp table(s) or creating table variable(s) within these conditions,i. Small or relatively medium size return rows
ii. Only used once to process within the working SP.
I alway thought that using variable is better, or is it? This always in back of my mind, would be great if any experience SQL Admins can reply for me.
Thanks in advance for your help. 😎
In true "IT" fashion, "It Depends". Please see the following article which I consider to be one of the definitive articles on the subject...
http://www.sqlservercentral.com/articles/Temporary+Tables/66720/
My personal opinion and practice is... the only three places I use Table Variables are
1. In UDF's (and only because UDF's really don't like Temp Tables although there is a trick to using them there),
2. Inn places where I don't want something to be rolled back if a failure occurs such as process audit logs.
3. In places where I need to read from temporary structures using BCP or similar external programs.
If Temp Tables could be used for those three things, I'd never use a Table Variable for one and only one reason... they don't persist in SSMS which can be a real PITA when it comes to troubleshooting code especially if it's lengthy code.
--Jeff Moden
Change is inevitable... Change for the better is not.