Using the Table Data Type in SQL Server 2000
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 atable
variable. SQL Server will still automatically create indexes when you use aUNIQUE
orPRIMARY KEY
constraint on atable
variable, so if you only need unique indexes you need not worry about this issue. - You can't apply the
ALTER TABLE
statement to atable
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 aSELECT 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.
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.
Page 2 of 2