Effect on execution plans when a table variable has a primary key

Question

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.

Answer 1

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.

Answer 2

...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.

Answer 3

revival

I forget what I was doing in 2017 when this was originally asked, but I figured I'd throw a couple examples in of when indexing a table variable may provide some benefit. There are many reasons to avoid them outlined in posts you've linked to, and existing answers, but while I wait for my phone to charge and the nasal spray, magnesium, red wine, and Robitussin to kick in, here's what I got.

Join Elimination

This isn't special or specific to table variables, but it is special and specific to outer joins on unique keys, where row duplication is guaranteed to not happen. The query can take place in part of the simplification process that allows for unnecessary joins to be ignored in the final query plan.

DECLARE 
    @UserIds table
(
    UserId integer NOT NULL --PRIMARY KEY
);

INSERT
    @UserIds
(
    UserId
)
SELECT
    u.Id
FROM dbo.Users AS u
WHERE u.Reputation > 10000;

SELECT
    p.OwnerUserId,
    c = COUNT_BIG(*)
FROM dbo.Posts AS p
LEFT JOIN @UserIds AS ui
  ON ui.UserId = p.OwnerUserId
GROUP BY
    p.OwnerUserId
HAVING 
    COUNT_BIG(*) > 999
ORDER BY
    c DESC;
GO

Here's the plan with the PRIMARY KEY attribute quoted out:

Here's the plan with the PRIMARY KEY attribute unquoted:

Sort like Amazon asking you to prove a package didn't arrive, it's a little hard to prove that the join isn't there, but really it isn't.

Eager Index Spools

Sometimes SQL Server will decide to build an index for you in the middle of an execution plan. Having an index can avoid that misfortune. Again, not specific to table variables, but apparently we're stuck with them for some reason.

DECLARE 
    @waypops TABLE 
(
  UserId integer NOT NULL,
  Date datetime NOT NULL--, INDEX c CLUSTERED (UserId, Date DESC)
);

INSERT 
    @waypops 
(
    UserId, 
    Date
)
SELECT 
    b.UserId, 
    b.Date
FROM dbo.Badges AS b
WHERE b.Name = N'Popular Question'
  UNION ALL 
SELECT 
    b.UserId, 
    b.Date
FROM dbo.Badges AS b
WHERE b.Name = N'Notable Question';

SELECT 
    u.DisplayName, 
    b.Date
FROM dbo.Users AS u
CROSS APPLY
(
    SELECT TOP (1) 
        w.*
    FROM @waypops AS w
    WHERE u.Id = w.UserId
    ORDER BY 
        w.Date DESC
) AS b
WHERE u.Reputation >= 100000
ORDER BY
    u.Reputation DESC;

Plan with the clustered index definition quoted out:

Plan with the clustered index definition unquoted:

reasonable

If you've got no alternative for some weird reason, you should consider indexing table variables for the same reasons you'd considering indexing any other table. But there are very few reasons to prefer them generally.

• Faster data access
• Supported join algorithms
• Avoiding sorts

Answer 4

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.