1

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 ?

  • If field4 is not part of the key according to your business, you should not include it in the primary key. You can add other indexes for performance reasons – Lennart Jul 8 at 5:52
2

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.

  • Good catch Erik! I'll edit my note to also point to this one.., – Tibor Karaszi Jul 8 at 6:52
3

No, it won't help. If Field4 were the first column, then it would have helped. Like asking if going through a telephone directory/book will help you sort peoples names by firstname. :-)

Edit: Erik posted one example where this index can help. Requires you to return all of the rows for a certain Field4 value (i.e., equal-comparison on all but Field4).

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.