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.