When to use a covering index, a composite index, and unique columnar indexes
Let's say I have the following table in SQL Server 2008:
ProfileID int //identity; index: unique, primary key, clustered
ClientID int
RegionID int
ProfileName nvarchar(50)
Columns 2 and 3 link to their respective tables via foreign relationships.
Let's say my most common query is this:
SELECT ProfileID, ProfileName
FROM Profiles
WHERE ClientID = ? AND RegionID = ?
ORDER BY ProfileName
What indexing system is best suited?
If I put a covering index on (ProfileID, ProfileName), then that kills the default clustered index since covering indexes have to be non-clustered, but satisfies at least the return part of the query.
If I leave the primary key as-is, and independently index ClientID and RegionID, that gives me 3 indexes that have to be maintained by the 开发者_StackOverflowRDBMS, PLUS a table scan would still be needed to return ProfileName as it isn't covered. This seems heavy.
A simple case-study of how intricate indexing planning can be.
For this query:
SELECT ProfileID, ProfileName
FROM Profiles
WHERE ClientID = ? AND RegionID = ?
ORDER BY
ProfileName
you should create this index:
CREATE INDEX ix_profiles_region_client_name ON (RegionID, ClientID, ProfileName)
Within a single value of (RegionID, ClientID)
, the records are sorted on ProfileName
, so that the records will go sorted and no additional sorting will be needed.
Since ProfileID
is a PRIMARY KEY CLUSTERED
, it is implicitly included into each index record, so there is no need to specify it in the index definition explicitly.
If it were not a clustered key, you would need to add it to the index for it to cover this query:
CREATE INDEX ix_profiles_region_client_name__id ON (RegionID, ClientID, ProfileName) INCLUDE (ProfileID)
精彩评论