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 table
variable 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, atable
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
Page 1 of 2