September 10, 2010 at 6:27 am
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).
September 10, 2010 at 6:31 am
Thanks finally u got me...
September 10, 2010 at 7:27 am
Great Question!!
September 10, 2010 at 7:52 am
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.
September 10, 2010 at 7:54 am
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.
September 10, 2010 at 8:40 am
Oops. I figured it out.
September 10, 2010 at 9:08 am
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.
September 10, 2010 at 10:44 am
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
September 10, 2010 at 11:26 am
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
September 11, 2010 at 10:00 pm
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.
September 13, 2010 at 12:10 am
Thanks for the nice question.
It would have been tough if you didn't have "-- Drop the associated constraints" in the statements.
September 13, 2010 at 12:43 am
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
September 13, 2010 at 12:52 am
Yes nakul...
September 13, 2010 at 1:29 am
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.
September 13, 2010 at 2:15 am
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 15 posts - 16 through 30 (of 34 total)
You must be logged in to reply to this topic. Login to reply