Is it better to involve more columns to make an index unique, or just index the nonunique column?

Question

Is it better to involve more columns to make an index unique, or just index the nonunique column?

High Plains Grifter

Ten Centuries

Points: 1191
More actions
January 24, 2014 at 3:45 am

#396156

Hi all,
If I have a table that looks something like this:
CREATE TABLE MyTable
( ReferenceIdINT NOT NULL
,ReferenceTypeINT NOT NULL
,ClientIdINT NULL
CONSTRAINT PK_ReferenceId_Type PRIMARY KEY CLUSTERED (ReferenceId DESC, ReferenceType ASC)
)
Is it better in general to make a simpler, but non unique index on the third column:
CREATE INDEX ix_ClientId ON MyTable(ClientId ASC)
or is it better to make a unique index, even if this requires that I repeat the logic of the previous index to make it unique...
CREATE UNIQUE INDEX ux_ClientId_ReferenceId_ReferenceType ON MyTable(ClientId ASC,ReferenceId DESC,ReferenceType ASC)
Is it overkill to make both? Is there any advantage? I sometimes make tables with loads of non-unique columns that I want to index up; should each column have an index that only covers that column, or is it better to involve unique columns to keep all the idexes unique (for instance by involving an Identity column and using that). On this table:
CREATE TABLE MyTable
( IdINT IDENTITY(1,1)
,ClientTitleIdINT NULL
,ClientIdINT NULL
,SupplierIdINT NOT NULL
CONSTRAINT PK_ReferenceId_Type PRIMARY KEY CLUSTERED (Id DESC))
Which of these options is better - This:
CREATE UNIQUE INDEX ux_ClientId_IdON MyTable(ClientId ASC,Id DESC)
CREATE UNIQUE INDEX ux_SupplierId_IdON MyTable(SupplierId ASC,Id DESC)
CREATE UNIQUE INDEX ux_ClientTitleId_IdON MyTable(ClientTitleId ASC,Id DESC)
or this:
CREATE INDEX ix_ClientIdON MyTable(ClientId ASC)
CREATE INDEX ix_SupplierIdON MyTable(SupplierId ASC)
CREATE INDEX ix_ClientTitleIdON MyTable(ClientTitleId ASC)
Thanks for your time!
Mark

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Koen Verbeeck SSC Guru Points: 259115 More actions · Answer 1

It depends on which queries are used against the table.

The unique index is useful as a check constraint to make sure the natural key (the combination of those three columns) is respected.

It will also most likely be used in joins in lookups, as that is what makes a row unique.

If you have a lot of queries focusing solely on client_id, that index might be useful as well.

However, since your PK is (ReferenceId,ReferenceType) and is thus by definition unique, it seems strange to define a unique index with a column extra.

(Which is basically the same as your question for the second table, it makes no sense to define a unique index containing the PK and some extra columns)

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

High Plains Grifter Ten Centuries Points: 1191 More actions · Answer 2

Ok - no point in repeating the logic in separate indexes; got it.

thanks!

Mark

Grant Fritchey SSC Guru Points: 398908 More actions · Answer 3

Another consideration is the width of the key. The wider it is, the fewer key values are stored per page, which means possibly deeper B-Trees on the index and more pages to read to retrieve the information. That's offset by the index being more selective because it's unique, which increases the likelihood that it'll get used by the optimizer.

Yay!

Index design is a blast!

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

Generally... Index what you need to index to support the queries that are running against the table. If the index happens to be unique, mark it as so.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

ScottPletcher SSC Guru Points: 101118 More actions · Answer 5

Define the index based on how you will query it, and the cardinality of the keys.

If you will always specify the second column in the conditions for the query, you should include that column in the index key. If you will never specify a second column in the query conditions, don't include the column in the index key.

If sometimes you will and sometimes you won't, consider how often you will and how much uniqueness the second value adds to the key. For example, if the first column in the nonclus index might be repeated thousands of times, but the combination of both columns will never appear more than, say, 5 times, you'd strongly consider adding the second column, since that would reduce the number of index rows that must be read to find the matching rows.

Keep in mind that all columns in the clustered index key will automatically appear in every nonclustered index. But, as you're noted, you still have to consider whether or not they should appear in the key in the nonclus index or just be included automatically by SQL.

Edit: Re-worded for clarity.

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".