Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Can composite indexes be used as an index on a single field? Expand / Collapse
Author
Message
Posted Thursday, April 3, 2014 10:32 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, July 1, 2014 1:59 PM
Points: 67, Visits: 133
I've got a table that has a composite index on three of its fields. I don't want to create another index if I don't have to, but I'm trying to speed up some views that are taking a while.

I'm doing a rollup on PartNumber, which is the first field in the composite index.

Will it use that index to accomplish a search on PartNumber alone if needed? Will it do it for any field in the composite index?
Post #1558096
Posted Thursday, April 3, 2014 11:36 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, July 16, 2014 7:25 PM
Points: 16, Visits: 143
It should use part number if you search for part number alone. I'm not sure about the rest. You could test by viewing the execution plans to be certain.
Post #1558125
Posted Thursday, April 3, 2014 11:45 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 12:02 PM
Points: 12,890, Visits: 31,853
since you said the PartNumber is the first field, then yes; but it also depends on how selective that single column is.
if there's a lot of common values, SQL might just decide to do a clustered index scan instead.

so to confirm, if PartNumber is the leading column in your index that has three fields, would it be posible for a query to use the index.

so if the index is (PartNumber,SecondKey,ThirdKey) then yes.
if the index was created as (FirstKey,SecondKey,PartNumber) then no, you'd need an extra idnex.


Lowell

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1558128
Posted Thursday, April 3, 2014 11:49 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: 2 days ago @ 2:51 PM
Points: 1,194, Visits: 2,213
It's better to test by viewing the execution plans.

--
SQLBuddy
Post #1558131
Posted Thursday, April 3, 2014 4:56 PM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Yesterday @ 12:07 PM
Points: 15,562, Visits: 27,940
It goes back to the histogram on the statistics. That is only build on the first column in the index key. The others are used to help establish the density (how selective the entire key is), but the histogram is only on that first one.

----------------------------------------------------
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood..." Theodore Roosevelt
The Scary DBA
Author of: SQL Server 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #1558310
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse