Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««1234»»

Disabling Indexes Expand / Collapse
Author
Message
Posted Friday, September 10, 2010 8:40 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Monday, April 30, 2012 12:36 PM
Points: 404, Visits: 442
Oops. I figured it out.
Post #983823
Posted Friday, September 10, 2010 9:08 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, October 23, 2013 7:57 AM
Points: 1,110, Visits: 1,148
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.
Post #983867
Posted Friday, September 10, 2010 10:44 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 6:02 PM
Points: 21,657, Visits: 15,326
Thanks for the question



Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp
Post #983961
Posted Friday, September 10, 2010 11:26 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Yesterday @ 10:32 PM
Points: 1,415, Visits: 1,832
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
Be courteous. Drive responsibly.

Follow me on
Twitter: @sqltwins
Google Plus: +Nakul
Post #983994
Posted Saturday, September 11, 2010 10:00 PM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Yesterday @ 11:36 AM
Points: 868, Visits: 2,384
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.
Post #984343
Posted Monday, September 13, 2010 12:10 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Friday, August 22, 2014 10:32 AM
Points: 1,892, Visits: 791
Thanks for the nice question.

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




Regards,
Rals
.
Post #984542
Posted Monday, September 13, 2010 12:43 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Yesterday @ 10:32 PM
Points: 1,415, Visits: 1,832
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
Be courteous. Drive responsibly.

Follow me on
Twitter: @sqltwins
Google Plus: +Nakul
Post #984546
Posted Monday, September 13, 2010 12:52 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Friday, August 22, 2014 10:32 AM
Points: 1,892, Visits: 791
Yes nakul...



Regards,
Rals
.
Post #984547
Posted Monday, September 13, 2010 1:29 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 4:28 PM
Points: 5,984, Visits: 8,242
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 MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Post #984558
Posted Monday, September 13, 2010 2:15 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 1:47 AM
Points: 13,644, Visits: 10,537
Nice question. Thanks...



How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #984574
« Prev Topic | Next Topic »

Add to briefcase «««1234»»

Permissions Expand / Collapse