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

Page 1 of 2