Link to home
Start Free TrialLog in
Avatar of amitabh04
amitabh04

asked on

clustered index and non clustered index on the same table.

Hi,
   I would like to create clustered index on columns agent #, game number, date of a table. What would be the advantage of also having non clustered index on agent # column of the same table?

Thanks.
Avatar of dqmq
dqmq
Flag of United States of America image

None.
Generally speaking no. If you have an index on (A,B,C), SQL Server would be able to use it for index seek for the predicates on (A), (A,B) and (A,B,C).  So generally you don't want to create extra indexes on (A) or (A,B) because index maintenance cost would be higher than minor performance benefits you can get with some selects on extra indexes.

There are a few cases, though, when extra indexes could be useful:
1. Uniqueness of (A). This is not your case (AgentCode) is not unique, but in some cases you have to add additional index to force uniqueness
2. You'll get better performance on selects like that: select AgentCode, Count(*) from Table group by AgentCode. It happens because index row size on (AgentCode) could be much smaller than CI row size and at the end this select would produce less IO

But again, those are very specific cases and generally you don't want to have performance penalty of extra index maintenance.
>extra index useful for uniqueness.
Please DO NOT get in the habit of using indexes for uniqueness; use unique not null keys instead.  I realize they generally result in the creation of a supporting index, but uniqueness is a logical constraint and it's best practice to specify it with a logical key, not a physical artifact like an index.

>You'll get better performance on selects like that: select AgentCode, Count(*)
OK, there may be some queries that peform slightly better against the narrower index. However, balance that against the extra disk space and insert overhead.  In practice, I wouldn't declare the second index except to address a specific performance issue.
 
       
I would give the opposite advice. Even if dgmg is right and unique constraint is the logical concept vs. unique index is the physical concept, end result is the same - sql creates unique index internally. But unique constraint does not allow you to create covered index which is the great optimization technique.
Avatar of Alpesh Patel
Cluster Index is default on primary key. And you can create more index on other columns or (Composite index also)
>I would give the opposite advice.

To clarify my advice:
  1. Declare primary and unique keys to enforce uniqueness at the logical level. There will be some physical indexes as a byproduct
  2. Create additional indexes (one of your tools) for addressing performance issues at the physical level
  3. From among the indexes, use performance/space considerations and some DBA smarts to choose the best candidate for clustered index, covering columns, and other physical attributes of the index.

What is the opposite of that?    
ASKER CERTIFIED SOLUTION
Avatar of dwkor
dwkor
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial