Columns that contain both clustered and unclustered

Question

Columns that contain both clustered and unclustered

Viewing 6 posts - 16 through 20 (of 20 total)

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

jabadwy SSC Enthusiast Points: 126 More actions · Answer 1

Ok thanks - in test system i have started to drop and edit indexes where they are covered/included with clustered index. I was wondering should i then run dbcc freeproccache or is there is no need because the index has been altered/dropped then execution will use a diff. plan anyway?

Thanks again

Paul White SSC Guru Points: 150468 More actions · Answer 2

jabadwy (4/20/2010)
Ok thanks - in test system i have started to drop and edit indexes where they are covered/included with clustered index. I was wondering should i then run dbcc freeproccache or is there is no need because the index has been altered/dropped then execution will use a diff. plan anyway?

Dropping an index forces any cached plan that referenced it to be recompiled (correctness).

A changed index forces any cached plan that references the table to be recompiled (optimality).

So, no, absolutely no reason to dump the whole plan cache (for the entire server)!

Paul White
All articles available on SQL.kiwi
@SQL_Kiwi

Paul White SSC Guru Points: 150468 More actions · Answer 3

Roy Ernest (4/15/2010)
I have never heard of anyone recommending to have both clustered and non clustered on the same column. This is the first time.

It's not all that uncommon. I use this technique in an article, which will be published next Monday (26^th April).

Paul White
All articles available on SQL.kiwi
@SQL_Kiwi

Grant Fritchey SSC Guru Points: 398916 More actions · Answer 4

Paul White NZ (4/20/2010)
Roy Ernest (4/15/2010)
I have never heard of anyone recommending to have both clustered and non clustered on the same column. This is the first time.
It's not all that uncommon. I use this technique in an article, which will be published next Monday (26^th April).

But, you don't simply have an ID column and apply a cluster and a noncluster to it, right? There's either additional columns in the key or additional columns in the INCLUDE that would modify the behavior of certain queries such that the two indexes that are keyed on the same column, but structured differently, would behave differently enough that the optimizer can pick them up and use them appropriately for benefit.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Paul White SSC Guru Points: 150468 More actions · Answer 5

Grant Fritchey (4/20/2010)
But, you don't simply have an ID column and apply a cluster and a noncluster to it, right?

That is exactly what I do 😉

Heh. I feel evil for not sharing, but wait until Monday :w00t:

Paul White
All articles available on SQL.kiwi
@SQL_Kiwi

Grant Fritchey SSC Guru Points: 398916 More actions · Answer 6

Paul White NZ (4/20/2010)
Grant Fritchey (4/20/2010)
But, you don't simply have an ID column and apply a cluster and a noncluster to it, right?
That is exactly what I do 😉
Heh. I feel evil for not sharing, but wait until Monday :w00t:

But if there's even a slight difference, one with a different fill factor, one is unique where the other isn't, any other type of difference other than simply one is clustered and one is not, then we're still saying you make a change, but I'll wait til Monday to see.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning