Sql-server – primary key constraint Sorting

indexoptimizationprimary-keysortingsql server

Is having a field in primary key constraint optimizes sorting of the table ?

Here's the schema of the table:

CREATE TABLE Persons (
Field1 long NOT NULL,
Field2 long NOT NULL,
Field3 long NOT NULL,
Field4  long NOT NULL,
Filed5 long NOT NULL,
CONSTRAINT PK_Person PRIMARY KEY (Field1 Asc,Field2 Asc,Field3 Asc,Field4 Asc));

The sorting query: Select * from Persons order by Field4

The field4 in primary key constraint doesn't provide any uniqueness with respect to values.

The only reason it's added is to optimize the sorting on field4, since sorting on a column that does have an clustered index helps in optimization.

In short, Is having the field4 in primary key constraint helping with the optimization of the above sorting query, even though the primary key constraint is created using ascending order of field1 then field2 then field3 and the field4 ?

Best Answer

It could help some variations of your query. Take this setup for instance:

CREATE TABLE dbo.Persons
     (
         Field1 INTEGER NOT NULL,
         Field2 INTEGER NOT NULL,
         Field3 INTEGER NOT NULL,
         Field4 INTEGER NOT NULL,
         Field5 INTEGER NOT NULL,
         CONSTRAINT PK_Person
             PRIMARY KEY ( Field1 ASC, Field2 ASC, Field3 ASC, Field4 ASC )
     );


INSERT dbo.Persons ( Field1, Field2, Field3, Field4, Field5 )
SELECT x.n, x.n % 2, x.n % 3, x.n % 4, x.n % 5
FROM   (   SELECT TOP ( 1000 * 1000 )
                  ROW_NUMBER() OVER ( ORDER BY 1 / 0 ) AS n
           FROM   sys.messages AS m 
           CROSS JOIN sys.messages AS m2 ) AS x;

The query you gave as example:

SELECT *
FROM dbo.Persons AS p
ORDER BY p.Field4

NUTS

There's a Sort operation to put things in the order you requested.

But this query has a different plan:

SELECT *
FROM dbo.Persons AS p
WHERE p.Field1 = 1000000
AND   p.Field2 = 0
AND   p.Field3 = 1
ORDER BY p.Field4

NUTS

With equality predicates across the columns leading up to your chosen Sort order, the order of data in your index is supported.

I'm not sure how often you're truly selecting every column with no filter, but if you have more restrictive queries that index may work out for your needs.

Related Question