CODEWITHSUNDEEP
sql-serverindexing
YvesR
YvesR

Reputation: 6222

MS SQL Index column sort order matters?

I have a database where I run profiler and database advisor to improve the performance of the database. I did and so far so good, the advisor presented me lots of indexes and statistics that can be created to get better performance. But reading the statements I ran into this:

CREATE NONCLUSTERED INDEX [_dta_index_xinfobaseacl_11_397426127__K3_K7_K1_K5] ON [dbo].[xinfobaseacl] 
(
    [lviewid] ASC,
    [lparentid] ASC,
    [lid] ASC,
    [xlactor] ASC
)WITH (SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF) ON [PRIMARY]
go

CREATE NONCLUSTERED INDEX [_dta_index_xinfobaseacl_11_397426127__K7_K5_K1_K3] ON [dbo].[xinfobaseacl] 
(
    [lparentid] ASC,
    [xlactor] ASC,
    [lid] ASC,
    [lviewid] ASC
)WITH (SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF) ON [PRIMARY]
go

Same table, same columns, same parameters, just other sort order of the columns...

So I am confused, does the sort order really matters in index creation???

Upvotes: 4

Views: 1771

Answers (2)

Martin Smith
Martin Smith

Reputation: 453037

Yes the order of the columns in a composite index matters.

Both of them will work equally well for an equality predicate on all 4 columns.

But the first one will additionally support queries like

WHERE lviewid =  1 AND lparentid 1 AND lid = 1
ORDER BY xlactor

And the second one will support queries such as 

WHERE parentid =  1 AND xlactor = 1
ORDER BY lid, xlactor

Whether or not you should create both indexes depends on your workload. Did you feed the DTA a representative workload including data modification statements?

Upvotes: 3

podiluska
podiluska

Reputation: 51494

You're confusing terminologies. The sort order doesn't have a great, if any, affect, but the order of the columns in the index can be important.

The correct order of columns for a compound index depends on your query, but as a rule of thumb, the column with the fewest different values should be first.

Upvotes: 1

Related Questions