SQL Variables as Temp Tables
How many times have you written a script which makes use of temp tables and then had something go wrong, and when you re-run your script you get this wonderful error.
1 2 | Server: Msg 2714, Level 16, State 6, Line 1 There is already an object named '#MyTempTableName' in the database. |
A table variable is created in memory, and so it performs slightly better than #temp tables.
Table variables might still perform I/O to tempdb but there is not proof and the documentation is not very explicit about this.
Table variables are automatically cleared when the procedure or function goes out of scope, so you don't have to remember to drop the temp table and the table variable log activity is truncated immediately, while #temp table log activity persists until the log hits a checkpoint, is manually truncated, or when the server restarts.
So here is an Example of using a Variable Table
1 2 3 4 5 6 7 8 9 10 | DECLARE @MyTable AS Table ( ID INT IDENTITY, MyText VARCHAR(50) ) INSERT INTO @MyTable (MyText) Values ('One') INSERT INTO @MyTable (MyText) Values ('Two') INSERT INTO @MyTable (MyText) Values ('Three') INSERT INTO @MyTable (MyText) Values ('Four') INSERT INTO @MyTable (MyText) Values ('Five') SELECT * FROM @MyTable |
And the final result:
Sponsors
Categories
- CakePHP (2)
- Classic ASP (VB Script) (14)
- CSS (2)
- HTML5 (1)
- Javascript Libraries/Frameworks (2)
- SmarterTrack (1)
- Software (3)
- T-SQL (4)
- VB.Net (1)
- WordPress (3)
Site Links
Blogroll
- Codango Web Development Reviews, Resources, and Inspiration.
- tuts+ From graphics to web development, audio to video and more, get the skills you want from our family of tutorial and resource sites.
- W3Avenue advice & resources for rapid web development
- WebAppers Hunting the Best Open Source Resources for Web Developers