Using the Table Data Type in SQL Server 2000, Page 2
So What's the Difference?
Obviously, converting from temporary tables to
science. So when should you do it? Consider these factors when making the
- Stored procedures that use
tablevariables won't need to be recompiled as often as stored procedures that use temporary tables. This speeds up your stored procedures.
- If you need to work with a large amount of data with many indexes, use a
temporary table instead of a table variable. You can't apply the
CREATE INDEXstatement to a
tablevariable. SQL Server will still automatically create indexes when you use a
PRIMARY KEYconstraint on a
tablevariable, so if you only need unique indexes you need not worry about this issue.
- You can't apply the
ALTER TABLEstatement to a
tablevariable. If your working table needs to change its schema in the course of a batch, use a temporary table instead.
- You can't insert the results of a stored procedure into a
tablevariable, or make one the target of a
As a general rule of thumb, you should use
table variables any
time that your temporary data is of reasonable size and is only used a few
times. As your temporary data grows in size, complexity, and reuse, a temporary
table will be more appropriate. Of course, you don't have to make this decision
blindly. With execution plans, SQL Profiler, and performance counters available
to monitor what's going on inside of your stored procedures, you can code the
alternatives and test them when there's any doubt.
One factor that you should not take into account is the tradeoff
between disk space and RAM usage. Many references on the Web claim that
table variables are implemented purely in memory, as opposed to
temporary tables which are actually written to a physical database. In fact,
table variables and temporary tables are created in the tempdb
database. After all, SQL Server doesn't know how much data you're going to try
to put into a
table variable; what if you want to put in more than
you have RAM available? In any case, SQL Server will cache small amounts of data
in RAM no matter which means you use to declare the table.
There, that was easy, wasn't it? If you've been writing SQL for a while,
you're probably very familiar with the use of temporary tables to solve knotty
problems. Now you can consider using
table variables instead. Having
more tools at hand gives you a better chance of coming up with an optimum
solution, and that's what this business is all about.
About the Author
Mike Gunderloy is the author of over 20 books and numerous articles on development topics, and the lead developer for Larkware. Check out his latest book, Coder to Developer from Sybex. When he's not writing code, Mike putters in the garden on his farm in eastern Washington state.