Disabling Indexes

Question

Post reply

Disabling Indexes

Viewing 15 posts - 16 through 30 (of 34 total)

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

Hugo Kornelis SSC Guru Points: 64790 More actions · Answer 1

mukeshkane (9/10/2010)
Is I am still missing something??

Yes.

The line

ALTER TABLE MyTable DROP CONSTRAINT PK_MyIdGO

should read

ALTER TABLE MyTable DROP CONSTRAINT PK_MyId

(That is, remove the GO at the end).

When I wrote that you were missing a GO, I had not seen that the GO was there, but in the wrong place. You added the GO, but kept the extraneous one at the end of the line. Those two extra letters invalidated the DROP CONSTRAINT (because there is no constraint named PK_MyIDGO; the proper constraint name is PK_MyID).

Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
Visit my SQL Server blog: https://sqlserverfast.com/blog/
SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

mukeshkane SSC-Addicted Points: 449 More actions · Answer 2

mukeshkane

SSC-Addicted

Points: 449

September 10, 2010 at 6:31 am

#1219125

Thanks finally u got me...

daveb87 Ten Centuries Points: 1025 More actions · Answer 3

daveb87

Ten Centuries

Points: 1025

September 10, 2010 at 7:27 am

#1219144

Great Question!!

SanjayAttray SSChampion Points: 13157 More actions · Answer 4

Good question. I had not disabled index as of now or never tried. Thought that it would fetch data but would take time, period.

But, never knew that disabling index would throw error and would not return any thing.

Msg 8655, Level 16, State 1, Line 5

The query processor is unable to produce a plan because the index 'PK_MyId' on table or view 'MyTable' is disabled.

SQL DBA.

Cliff Jones SSChampion Points: 10547 More actions · Answer 5

vk-kirov (9/10/2010)
The question would be more educational if it had no 'DROP CONSTRAINT' statement. In this case I would probably have answered wrong 🙂

Yes, I agree.

croberts 36762 SSC Eights! Points: 972 More actions · Answer 6

croberts 36762

SSC Eights!

Points: 972

September 10, 2010 at 8:40 am

#1219223

Oops. I figured it out.

marklegosz SSCrazy Points: 2816 More actions · Answer 7

The explaination was very interesting (which I learned from) but I felt the question didn't really illistrate the point very well, i.e. the net result was drop an index and run a select on the table. Who would have thought select * from table returns all rows.

SQLRNNR SSC Guru Points: 281334 More actions · Answer 8

Thanks for the question

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Nakul Vachhrajani SSChampion Points: 10294 More actions · Answer 9

Hello!

Thank-you all for your kind comments and feedback.

I am sorry that a few of you did have some trouble trying to get the code running on your servers. I also apologize for not having the time during the day to reply to all who took the time out to comment (had a busy day at home and at office today).

What fascinated me the most about this was that it taught me a little something about constraints and how SQL Server uses them (i.e. the use of the constraint made SQL look for the index, which was disabled).

To all those who read the question and took the time to attempt it - a very big thank-you from me. To all those who learnt something new, SQL is magical and this question is even more motivation to learn more about it!

Have a wonderful weekend ahead!

Thanks & Regards,
Nakul Vachhrajani.
http://nakulvachhrajani.com

Follow me on
Twitter: @sqltwins

Nadrek SSC-Insane Points: 20039 More actions · Answer 10

SanjayAttray (9/10/2010)
Good question. I had not disabled index as of now or never tried. Thought that it would fetch data but would take time, period.

Same here; but I've seen countless disabled constraints, as well as enabled but Untrusted constraints.

ralm Hall of Fame Points: 3156 More actions · Answer 11

Thanks for the nice question.

It would have been tough if you didn't have "-- Drop the associated constraints" in the statements.

^{[font="Verdana"]Regards,
Rals[/font].}

Nakul Vachhrajani SSChampion Points: 10294 More actions · Answer 12

Hello!

The goal of this question was to have a deeper understanding of how SQL Server behaves, and not making the questions harder. Emphasis is therefore on the fact that if we have the constraints enabled, SQL does try to use the index which it cannot do because it's disabled. If one knows this concept, the answer is "easy" - otherwise it's "difficult"/"harder".

Thanks & Regards,
Nakul Vachhrajani.
http://nakulvachhrajani.com

Follow me on
Twitter: @sqltwins

ralm Hall of Fame Points: 3156 More actions · Answer 13

ralm

Hall of Fame

Points: 3156

September 13, 2010 at 12:52 am

#1219862

Yes nakul...

^{[font="Verdana"]Regards,
Rals[/font].}

Hugo Kornelis SSC Guru Points: 64790 More actions · Answer 14

Nakul Vachhrajani (9/13/2010)
Emphasis is therefore on the fact that if we have the constraints enabled, SQL does try to use the index which it cannot do because it's disabled.

Actually, the reason you get an error if the index is disabled is that, for a clustered index, the index IS the table. As a result, disabling the clustered index equates to disabling the table.

If you use a nonclustered rather than a clustered index, then table is still accessible even with the index disabled. You can try this for yourself, by simply changing the "CLUSTERED" to "NONCLUSTERED" in the constraint declaration for the primary key.

Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
Visit my SQL Server blog: https://sqlserverfast.com/blog/
SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

Koen Verbeeck SSC Guru Points: 259115 More actions · Answer 15

Nice question. Thanks...

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP