Using the Table Data Type in SQL Server 2000

SQL Server 2000 has hundreds of advances over SQL Server 7.0. Though it's been out for a few years now, developers can be pardoned if they still haven't found time to dig into all of the changed areas; there are just so many of them, and we've all got work to do. But it's worth finding time now and then to learn something new. Besides, with SQL Server 2005 looming, you don't want to get too far behind. So today, I'm going to fill you in on the table data type and show you some of its uses. Tuck this away in your SQL Server bag of tricks, because it will probably come in useful in the future.

Getting Started: Table Data Type Syntax

Let's start with the syntax that you can use to declare a variable using the table data type. These variables are designed for use in stored procedures and functions, so you use the DECLARE statement:

DECLARE @local_variable table_type_definition

table_type_definition ::=
    TABLE ( { column_definition | table_constraint } [ ,...n ] )

column_definition ::=
    column_name scalar_data_type
    [ COLLATE collation_definition ]
    [ [ DEFAULT constant_expression ] | 
      IDENTITY [ ( seed , increment ) ]
    ]
    [ ROWGUIDCOL ] 
    [ column_constraint ] [ ...n ]

column_constraint ::=
    { [ NULL | NOT NULL ] 
    | [ PRIMARY KEY | UNIQUE ] 
    | CHECK ( logical_expression )
    } 

table_constraint ::=
    { { PRIMARY KEY | UNIQUE } ( column_name [ ,...n ] )
    | CHECK ( search_condition )
    }

As with all SQL syntax diagrams, square brackets indicate optional items, ::= indicates an expansion, and italics indicate variables that you need to replace. As you can see, the syntax for declaring a tablevariable is a fair subset of the CREATE TABLE statement that you use for declaring a permanent table.

But there are substantial differences between table variables and either permanent or temporary tables. Here are the highlights:

• table variables can only be used in functions, stored procedures, and batches. They have no permanent life outside of the batch that contains them. They're cleaned up automatically at the end of the batch.
• A table variable has a well-defined scope, which is the function, stored procedure, or batch that contains it. You don't need to worry about name conflicts with anything outside of this scope. Within its scope, a table variable behaves like a permanent table.
• table variables require less locking and logging resources, and have less overhead during transactions, because SQL Server knows that they're going to vanish at the end of the batch.

Let's See an Example

Generally, you can use a table variable anywhere that you might have used a temporary table in previous versions on SQL Server. For example, consider this stored procedure in the Northwind sample database, which shows the distribution of products across orders:

CREATE PROC procGetProductStats AS
CREATE TABLE #OrderCounts(ProductID int, OrderCount int)
INSERT INTO #OrderCounts
SELECT ProductID, COUNT(DISTINCT OrderID)
FROM [Order Details]
GROUP BY ProductID
ORDER BY COUNT(DISTINCT OrderID) DESC

SELECT COUNT(ProductID), OrderCount
FROM #OrderCounts
GROUP BY OrderCount
ORDER BY OrderCount DESC

Figure 1 shows the result of running this stored procedure. This tells us that one product appears on 54 distinct orders, three products on 51 orders each, and so on.

To get the same results with a table variable only takes a slight rewrite:

CREATE PROC procGetProductStats2 AS
DECLARE @OrderCounts TABLE(ProductID int, OrderCount int)
INSERT INTO @OrderCounts
SELECT ProductID, COUNT(DISTINCT OrderID)
FROM [Order Details]
GROUP BY ProductID
ORDER BY COUNT(DISTINCT OrderID) DESC

SELECT COUNT(ProductID), OrderCount
FROM @OrderCounts
GROUP BY OrderCount
ORDER BY OrderCount DESC

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.