Rank over with Clustered Index

Question

Post reply

Rank over with Clustered Index

lianvh 89542

SSCommitted

Points: 1561
More actions
January 21, 2014 at 10:31 pm

#287537

Good Day,
We are running SQL server 2008R2 64 Bit Ent. on our Production System. On our main tables we did define Clustered indexes spanning over multiple columns, but there was a spec to do ordering more refined on some of these columns and it was decided to use the Rank Over feature . It sounds a bit of an overkill. Any ideas ?

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Ville-Pekka Vahteala SSCrazy Points: 2458 More actions · Answer 1

If I understood your question correctly, I think this blog post will have answers.

http://www.brentozar.com/archive/2014/01/dynamic-sorting/

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 2

Clustered indexes over multiple columns isn't usually a great idea. What are you trying to achieve?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

lianvh 89542 SSCommitted Points: 1561 More actions · Answer 3

Thank you for the feedback. There was a business requirement for the concatenated clustered index . The rank over was then needed to order the rows more accurately on one of the columns of the concatenated clustered index .

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 4

lianvh 89542 (1/23/2014)
Thank you for the feedback. There was a business requirement for the concatenated clustered index . The rank over was then needed to order the rows more accurately on one of the columns of the concatenated clustered index .

You want to reorder the clustered index - is this correct?

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

lianvh 89542 (1/23/2014)
Thank you for the feedback. There was a business requirement for the concatenated clustered index.

Why? Business requirements shouldn't specify the indexing locations and types, that's a technical detail, not something the business users should care in the slightest about.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

lianvh 89542 SSCommitted Points: 1561 More actions · Answer 6

Thank you for the feedback. I am sorry for expressing myself incorrectly. Business wanted the data on screen more precisely ordered. It was up to Dev and the Dbas to achieve the ordering.

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 7

So the answer to your original question is no, it's not overkill. You can't rely on the clustered index for sorting query results. Parallelism, advanced scans and other factors mean results can be returned in any order. That's not to mention that the way the database engine is coded internally could change. And even if you could rely on the clustered index, what would happen if, for performance reasons, you changed the clustered index? You'd then need to go back and recode all your queries.

John

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 8

lianvh 89542 (1/23/2014)
Thank you for the feedback. I am sorry for expressing myself incorrectly. Business wanted the data on screen more precisely ordered. It was up to Dev and the Dbas to achieve the ordering.

Then you need to rely on order by, on a set of columns. The clustered index does not enforce order, if you want a particular order, you need an Order By on the outer query.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 9

lianvh 89542 (1/23/2014)
Thank you for the feedback. I am sorry for expressing myself incorrectly. Business wanted the data on screen more precisely ordered. It was up to Dev and the Dbas to achieve the ordering.

Now you are aware that the clustered index order is irrelevant to output sort order, you may wish to revisit your choice of cluster columns.

GilaMonster (1/22/2014)
Clustered indexes over multiple columns isn't usually a great idea. What are you trying to achieve?

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden