Skip to main content
modified the example to indicate that the order of the columns that make up the key matters more than the ascending/descending order of the columns themselves
Source Link
sam.bishop
  • 494
  • 3
  • 16

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 (id DESC))
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 FORWARDBACKWARD)
       |--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.

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 (id DESC))
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 FORWARD)
       |--Clustered Index Seek(OBJECT:(@DataTable AS [dt]), SEEK:([dt].[id]=@Driver.[id] as [d].[id]) ORDERED FORWARD)

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.

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.

Source Link
sam.bishop
  • 494
  • 3
  • 16

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 (id DESC))
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 FORWARD)
       |--Clustered Index Seek(OBJECT:(@DataTable AS [dt]), SEEK:([dt].[id]=@Driver.[id] as [d].[id]) ORDERED FORWARD)

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.