Working with Temporary Data in SQL Server

Introduction

SQL Server often requires you to store intermediate results for use in subsequent operations within a stored procedure or script. While you can use variables to hold single values, dealing with multiple values or entire result sets requires a more robust approach. This tutorial explores how to effectively use table variables and temporary tables in SQL Server to store and reuse data efficiently.

Table Variables

Table variables are in-memory table-like structures that you can declare and use within a batch or stored procedure. They are particularly useful for small to medium-sized datasets.

Declaration:

To declare a table variable, use the DECLARE statement with a table structure:

DECLARE @MyTableVariable TABLE
(
    Column1DataType datatype1,
    Column2DataType datatype2,
    ...
);

For example:

DECLARE @Customers TABLE
(
    CustomerID uniqueidentifier,
    FirstName nvarchar(100),
    LastName nvarchar(100),
    Email nvarchar(100)
);

Populating Table Variables:

You can populate a table variable using an INSERT INTO statement combined with a SELECT statement. You cannot directly use SELECT ... INTO to create and populate a table variable in a single step.

INSERT INTO @Customers
SELECT CustomerID, FirstName, LastName, Email
FROM Customers
WHERE City = 'New York';

Querying Table Variables:

Once populated, you can query the table variable like any other table:

SELECT * FROM @Customers;

Scope and Lifetime:

Table variables have a limited scope. They are only visible within the batch or stored procedure in which they are declared. They are automatically dropped when the batch or stored procedure completes execution.

Temporary Tables

Temporary tables are also in-memory tables, but they are created as actual tables within the tempdb database. This offers different characteristics compared to table variables.

Creation:

Temporary tables are created using either a single # or double ## prefix to the table name.

Local Temporary Table (#TableName): Visible only to the current connection. It is automatically dropped when the connection is closed or the session ends.
Global Temporary Table (##TableName): Visible to all connections until the server is restarted or the table is explicitly dropped.

Example:

CREATE TABLE #MyTemporaryTable
(
    ID int,
    Name nvarchar(100)
);

Or for a global temp table:

CREATE TABLE ##GlobalTempTable
(
    ID int,
    Name nvarchar(100)
);

Populating and Querying:

Like table variables, you can use INSERT INTO to populate and SELECT to query a temporary table:

INSERT INTO #MyTemporaryTable (ID, Name)
VALUES (1, 'Example');

SELECT * FROM #MyTemporaryTable;

Dropping Temporary Tables:

While temporary tables are automatically dropped when the connection closes (local) or server restarts (global), it’s good practice to explicitly drop them when you’re finished using them:

DROP TABLE #MyTemporaryTable;
DROP TABLE ##GlobalTempTable;

Choosing Between Table Variables and Temporary Tables

Here’s a summary of when to use each:

Guidelines:

For small datasets (a few hundred rows or less) and simple operations, table variables generally offer better performance due to their in-memory nature.
For larger datasets or when you need to create indexes or statistics on the data for performance optimization, temporary tables are often a better choice.
Consider the scope requirements. If the data needs to be accessible only within a single batch or stored procedure, a table variable is sufficient. If data needs to be shared across multiple connections, a global temporary table might be appropriate (but use with caution).

Example Scenario

Let’s illustrate how to use a table variable to store and reuse customer data:

DECLARE @ActiveCustomers TABLE
(
    CustomerID uniqueidentifier,
    FirstName nvarchar(100),
    LastName nvarchar(100),
    Email nvarchar(100)
);

INSERT INTO @ActiveCustomers
SELECT CustomerID, FirstName, LastName, Email
FROM Customers
WHERE IsActive = 1;

-- Now you can reuse the data in other queries
SELECT * FROM @ActiveCustomers WHERE LastName LIKE '%Smith%';

-- Example: join with another table
SELECT c.*, o.OrderID
FROM @ActiveCustomers c
INNER JOIN Orders o ON c.CustomerID = o.CustomerID;

This demonstrates how a table variable can streamline data processing within a stored procedure by allowing you to store and reuse data without repeatedly querying the base tables.

Introduction

Table Variables

Temporary Tables

Choosing Between Table Variables and Temporary Tables

Example Scenario

Leave a Reply Cancel reply