CODEWITHSUNDEEP
sqlperformancedatabase-designdatabase-performance
dzonek94
dzonek94

Reputation: 11

SQL: Multiple columns in primary key - how to achieve correct order?

In my solution I have multiple queries for my table. Let's assume that it's a table with multiple columns, but columns a and b form the PrimaryKey. Sometimes I query by a values, sometimes by b values. Currently I have PRIMARY KEY CLUSTERED ([a] ASC, [b] ASC). When I'm trying to query by column b it is very slow and I constantly get a timeout from the database.

Having two clustered primary keys would be great... What shoud I do? Will creating a new index on column b make b queries more efficient?

Upvotes: 1

Views: 2736

Answers (1)

Branko Dimitrijevic
Branko Dimitrijevic

Reputation: 52117

Primary key is a logical concept. Performance, on the other hand, depends on the physical organization of data. The reason why WHERE a = ? is fast is not that PK exists, but that an index was automatically created together with the PK.

So, if you want to make another query fast, just add an appropriate index.

Assuming your table looks similar to this...

CREATE TABLE T (
    a int,
    b int,
    c int,
    PRIMARY KEY (a, b)
);

You have more-less the following options for creating an index to speed-up WHERE b = ?:

CREATE INDEX T_I1 ON T(b);
CREATE UNIQUE INDEX T_I2 ON T(b, a);
CREATE UNIQUE INDEX T_I3 ON T(b, a, c);
   -- or: CREATE UNIQUE INDEX T_I2 ON T(b, a) INCLUDE (c);

Which of these options you choose, depends largely on how much of your query you want to cover.

Also, beware that...

CREATE INDEX T_I1 ON T(b);

...might actually be equivalent to...

CREATE INDEX T_I1 ON T(b) INCLUDE (a);

...if your table is clustered (which it is in your case).

Upvotes: 1

Related Questions