June 8, 2015 at 5:42 am
Good question. The answer made sense and seemed obvious to me, but I've had to restructure a few poorly-designed tables before.
June 8, 2015 at 5:50 am
What is the advantage to disable indexes ?
I 've never used that.
Or I make and use an index,
or I do not make an index,
or I drop an index if I don't need it anymore.
But why disabling it ? 😀
Why should one use that ?
June 8, 2015 at 6:32 am
TomThomson (6/6/2015)
Nice question.To me it seems rather obvious that if you disable the clustered index then no other index on the table will work because all the other indexes identify rows the row by reference to the clustered index key (including any hidden uniquifier) for the row. So two seems a lot of points for this question. But as two of the first five answers are wrong maybe it isn't quite as obvious as it seems.
Also interesting is that the sequence implies:
1. The PK is first built using RIDs as references since there is no CI
2. The CI is built, causing some if not all the RIDs in the PK index to be invalid
3. The PK is not just updated, but rebuilt using the CI column(s) as the reference.
At first I did not expect the PK rebuild in this fashion, though of course it makes sense. Just glad SQL also sees the sense in the rebuild.
Gerald Britton, Pluralsight courses
June 8, 2015 at 6:53 am
Hany Helmy (6/8/2015)
ArnoKwetters (6/8/2015) How can you see then that the primairy key index has been disabled ?
In the Message pane when you execute the Index disable code.
I got confused too so I ran it and saw the odd named index.
June 8, 2015 at 9:17 am
Thanks for the question. I learned something.
- webrunner
-------------------
A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html
June 8, 2015 at 10:19 am
Now that's a comment I'm interested in. When a database is in an unhealthy state, the 'icon' is gray. Geeze, if you disable an index maybe Microsoft could do the same with the key icon?
June 8, 2015 at 10:55 am
That's really interesting. I did not realize there is a difference between having no clustered index, and having a disabled clustered index.
Don Simpson
June 8, 2015 at 12:21 pm
mhynds (6/8/2015)
Now that's a comment I'm interested in. When a database is in an unhealthy state, the 'icon' is gray. Geeze, if you disable an index maybe Microsoft could do the same with the key icon?
Good idea. Why don't you log it at http://connect.microsoft.com/SQLServer? I will kick it for you.
June 8, 2015 at 2:09 pm
Revenant (6/8/2015)
mhynds (6/8/2015)
Now that's a comment I'm interested in. When a database is in an unhealthy state, the 'icon' is gray. Geeze, if you disable an index maybe Microsoft could do the same with the key icon?Good idea. Why don't you log it at http://connect.microsoft.com/SQLServer? I will kick it for you.
+1 Revenant , but it is difficult to obtain something with connect.Microsoft . Usually , the answer is : maybe for the next version , even if a MSFT or known MVP approves the request ...
June 8, 2015 at 2:12 pm
patricklambin (6/8/2015)
Revenant (6/8/2015)
mhynds (6/8/2015)
Now that's a comment I'm interested in. When a database is in an unhealthy state, the 'icon' is gray. Geeze, if you disable an index maybe Microsoft could do the same with the key icon?Good idea. Why don't you log it at http://connect.microsoft.com/SQLServer? I will kick it for you.
+1 Revenant , but it is difficult to obtain something with connect.Microsoft . Usually , the answer is : maybe for the next version , even if a MSFT or known MVP approves the request ...
I am sitting in Studio D.
June 8, 2015 at 4:16 pm
ArnoKwetters (6/8/2015)
What is the advantage to disable indexes ?I 've never used that.
Or I make and use an index,
or I do not make an index,
or I drop an index if I don't need it anymore.
But why disabling it ? 😀
Why should one use that ?
For nonclustered indexes, a valid scenario might be a huge data load where updating the index while loading slows down the operation. You can drop the index, then load the data and then create the index again, but that requires repeating the full CREATE INDEX statement. It's easier to disable / enable, because then the definition is saved by SQL Server.
I don't think I have ever come across a reasonable use case for disabling a clustered index.
June 8, 2015 at 11:59 pm
ArnoKwetters (6/8/2015)
Thanks PMA...That is a nice script.
It is still strange that when I scripted the Indexed, it was not obvious that the primairy key was disabled.
This script does.
Thanks again
When you run the script given in QOD, you get the below warnings (please observe system generated name for primary key which starts like "PK__". or else give a proper name for the primary key in the script to get the exact name in the warning. )
Warning: Index 'PK__SalesArc__C953FF0B2706E424' on table 'SalesArchive' was disabled as a result of disabling the clustered index on the table.
Warning: Index 'SalesArchive_NCI_SalesDate' on table 'SalesArchive' was disabled as a result of disabling the clustered index on the table.
June 9, 2015 at 12:37 pm
Nice question, thanks.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
Viewing 13 posts - 16 through 27 (of 27 total)
You must be logged in to reply to this topic. Login to reply