clustered index and non clustered index on the same table.

amitabh04 used Ask the Experts™
   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?

Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®


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.
How to Generate Services Revenue the Easiest Way

This Tuesday! Learn key insights about modern cyber protection services & gain practical strategies to skyrocket business:

- What it takes to build a cloud service portfolio
- How to determine which services will help your unique business grow
- Various use-cases and examples

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.
Alpesh PatelSolution Architect at TCS (SAFe®Agilist, TOGAF® Foundation, PSM I®, MCP, MCSD)

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?    
Only thing I disagree is - I'd suggest to create unique indexes instead of unique constraints because unique constraints does not allow you to add extra "included" columns and create covered index.

E.g: alter table Agents add constraint UQ_Agents_AgentName unique(AgentName) - will force SQL Server to create unique non-clustered index on AgentName column. Although if you define it as unique non-clustered index in the first place, you will be able to add INCLUDE columns and illuminate expensive key lookups if needed. E.g. create unique non-clustered index IDX_Agents_AgentName(AgentName) INCLUDE(...).

I understand the difference between logical and physical db design. But this is the case where I'd cross the boundaries and use the implementation that can give me some potential benefits at the future.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial