4

Having read a great deal about the differences between temporary tables and table variables in SQL Server, I am experimenting with switching from mostly using temporary tables to mostly using table variables. (They seem to be a better fit for the types of queries I usually work with.)

In these queries, the tables hold unique identifiers that drive the lookup process. It's been my habit, when working with temporary tables, to include a PRIMARY KEY constraint so that the query optimizer is aware that it won't see any duplicates. However, given that the optimizer (in most circumstances, and for my queries) assumes that a table variable only holds a single row*, which is unique by definition, is the query optimizer going to make choices any differently if there's a PRIMARY KEY constraint?

* Technically, it assumes there are no rows, but replaces the zero with a one. (Because the zero interacts very poorly with the rest of the estimation process.) But it also depends on whether the table variable is populated or not when the query is compiled. There is some background information here: What's the difference between a temp table and table variable in SQL Server?.

I'm currently using SQL Server 2014, but I would be curious if the behavior changes in newer versions.


As has been pointed out, a PRIMARY KEY constraint comes with a clustered index that gives the query optimizer more choices on how to get data out of the table variable. I was aware of this and thinking about the rest of the query plan. But after attempting to clarify my question, I've decided that the question I was attempting to ask was too broad and probably particular to my extreme situation. (Nothing but navigational-type queries into half-a-trillion-row tables with an expectation of sub-second performance.) So I am going to leave my question as-is.

6

Since declaring a PRIMARY KEY on a table variable implicitly creates an index for the key columns (and in fact is the only way to index a table variable prior to SQL Server 2014), its presence will definitely have an effect on the resulting query plans. The optimizer will make use of that primary key index where appropriate. You can see that in action by running this short script with the execution plan enabled - the table scan will change to a clustered index seek:

--No primary key/index
DECLARE @t1 TABLE (
    id int NOT NULL,
    data varchar(50) NOT NULL
)

INSERT INTO @t1 VALUES
(1, 'aaaaa'),
(2, 'bbbbb'),
(3, 'ccccc'),
(4, 'ddddd'),
(5, 'eeeee')

SELECT * FROM @t1 WHERE id = 4

--With primary key/index
DECLARE @t2 TABLE (
    id int NOT NULL PRIMARY KEY CLUSTERED,
    data varchar(50) NOT NULL
)

INSERT INTO @t2 VALUES
(1, 'aaaaa'),
(2, 'bbbbb'),
(3, 'ccccc'),
(4, 'ddddd'),
(5, 'eeeee')

SELECT * FROM @t2 WHERE id = 4

Now, as for whether declaring a PRIMARY KEY instead of a plain CLUSTERED INDEX (which 2014 lets you do) will result in different query plans? That I can't say authoritatively. This contrived test was still a clustered index seek:

DECLARE @t3 TABLE (
    id int NOT NULL,
    data varchar(50) NOT NULL,
    INDEX IX1 CLUSTERED (id)
)

INSERT INTO @t3 VALUES
(1, 'aaaaa'),
(2, 'bbbbb'),
(3, 'ccccc'),
(4, 'ddddd'),
(5, 'eeeee')

SELECT * FROM @t3 WHERE id = 4

I suspect things get a little more iffy when using nonclustered indexes on table variables, where the optimizer needs to estimate the cost of potential RID lookups and weigh them against a table scan.

7

...is the query optimizer going to make choices any differently if there's a PRIMARY KEY constraint?

Yes it might do. Estimating one row (with the understanding that estimates can be incorrect) is different from knowing that the table contains only unique values. Certain plan space explorations require a key, for example.

A good general rule of thumb is to provide as much information about the data and query task as you can to the optimizer. If there is a key, say so explicitly. It's not as if declaring the key will add much cost (beyond a little keyboard work) in most cases.

I personally rarely use table variables. The lack of statistics (including distribution and density) and cardinality information (all separate considerations) provides less information to the optimizer than an equivalent temporary table. My experience has very much been that table variable plans do not adapt as well to changing circumstances over time.

I only use a table variable when there are special reasons to be sure that it will always be adequate from a query optimization point of view. Only you have enough information about your databases and queries to say whether that is true in your case or not.

The question is rather broad (without a specific example), and so is this answer.

1

The question asks only about uniqueness, but a clustered index (created due to a PRIMARY KEY constraint) also does something else that can be quite beneficial, which seems worth mentioning in order to make these answers widely applicable.

A clustered index imposes an order on its keys. So even if the table variable is only read from, the query optimizer may decide to preserve that order if it allows SQL Server to later skip work it might otherwise need to do. Consider this simple scenario, for instance:

DECLARE @Driver TABLE (id int NOT NULL PRIMARY KEY CLUSTERED)
INSERT INTO @Driver VALUES
    (1), (2), (3)
DECLARE @DataTable TABLE (id int NOT NULL PRIMARY KEY, data varchar(50))
INSERT INTO @DataTable VALUES
    (1, 'partridge in a pear tree'),
    (2, 'turtle doves'),
    (3, 'french hens'),
    (4, 'calling birds'),
    (5, 'golden rings')
SELECT dt.*
    FROM @Driver AS d
    JOIN @DataTable AS dt ON dt.id = d.id
    ORDER BY dt.id DESC

In this case, SQL Server is able to use the order of the clustered index to get the desired output:

id          data
----------- --------------------------------------------------
3           french hens
2           turtle doves
1           partridge in a pear tree

Without requiring an explicit sort:

  |--Nested Loops(Inner Join, OUTER REFERENCES:([d].[id]))
       |--Clustered Index Scan(OBJECT:(@Driver AS [d]), ORDERED BACKWARD)
       |--Clustered Index Seek(OBJECT:(@DataTable AS [dt]), SEEK:([dt].[id]=@Driver.[id] as [d].[id]) ORDERED FORWARD)

Note that SQL Server is able to take advantage of the clustered-index order despite the fact that it's the opposite of the order requested!

In more complicated scenarios, the order provided by the clustered index could permit the use of a Stream Aggregate operator instead of a Hash Aggregate operator. (The former uses less memory and starts returning data sooner.) Disk I/O might be more efficient if reads are done in a particular order as well. In short, there may be a number of benefits due to ordering, depending on the query.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.