Using the Table Data Type in SQL Server 2000, Page 2

So What's the Difference?

Obviously, converting from temporary tables to table variables isn't rocket science. So when should you do it? Consider these factors when making the decision:

• Stored procedures that use table variables 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 INDEX statement to a table variable. SQL Server will still automatically create indexes when you use a UNIQUE or PRIMARY KEY constraint on a table variable, so if you only need unique indexes you need not worry about this issue.
• You can't apply the ALTER TABLE statement to a table variable. 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 table variable, or make one the target of a SELECT INTO statement.

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, both 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.