Disabling Indexes

Question

Disabling Indexes

Steve Jones - SSC Editor

SSC Guru

Points: 734449
More actions
June 6, 2015 at 3:19 pm

#74275

Comments posted to this topic are about the item Disabling Indexes
June 8, 2015 at 2:20 am

This was removed by the editor as SPAM

Viewing 15 posts - 1 through 15 (of 27 total)

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

TomThomson SSC Guru Points: 104773 More actions · Answer 1

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.

Tom

Hany Helmy SSChampion Points: 13921 More actions · Answer 2

Good question & good information, thanx.

Thanks & Best Regards,
Hany Helmy
SQL Server Database Consultant

SQL_Hunt SSC-Dedicated Points: 33518 More actions · Answer 3

Nice one. As soon as I saw option #3, I selected and scored 2 points.

Thanks.

Igor Micev SSC-Dedicated Points: 33109 More actions · Answer 4

Great question! Thanks.

Igor Micev,My blog: www.igormicev.com

palotaiarpad SSCertifiable Points: 5617 More actions · Answer 5

In my opinion it's unimportant, if the other indexes are disabled or not. You can't reach the table data with a disabled clustered index.:-D

pmadhavapeddi22 SSCertifiable Points: 5306 More actions · Answer 6

pmadhavapeddi22

SSCertifiable

Points: 5306

June 8, 2015 at 12:42 am

#1803580

good question, Thanks

Arno Kwetters Hall of Fame Points: 3704 More actions · Answer 7

Strange. It looks to me that the primairy key index isn't disabled.

I scripted all 3 keys and noticed that only at the 2 non-primairy indexescript had an extra line inserted like :

ALTER INDEX [SalesArchive_NCI_SalesDate] ON [dbo].[SalesArchive] DISABLE

How can you see then that the primairy key index has been disabled ?

Hany Helmy SSChampion Points: 13921 More actions · Answer 8

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.

Thanks & Best Regards,
Hany Helmy
SQL Server Database Consultant

Arno Kwetters Hall of Fame Points: 3704 More actions · Answer 9

Hello Hany,

Yes you are right.

I was already a step forward.

I didn't look well in the Message pane.

Thanks.

pmadhavapeddi22 SSCertifiable Points: 5306 More actions · Answer 10

ArnoKwetters (6/8/2015)
Strange. It looks to me that the primairy key index isn't disabled.
I scripted all 3 keys and noticed that only at the 2 non-primairy indexescript had an extra line inserted like :
ALTER INDEX [SalesArchive_NCI_SalesDate] ON [dbo].[SalesArchive] DISABLE
How can you see then that the primairy key index has been disabled ?

You can also check the disabled/enabled indexes with the below query

select sys.objects.name as 'table',

sys.indexes.name as 'index',

is_disabled = case is_disabled

when '1' then 'disabled'

when '0' then 'enabled'

end

from sys.objects join sys.indexes

on sys.objects.object_id = sys.indexes.object_id

where sys.objects.name = 'SalesArchive'

Arno Kwetters Hall of Fame Points: 3704 More actions · Answer 11

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

manik_anu SSCrazy Points: 2377 More actions · Answer 12

nice question...

Manik
You cannot get to the top by sitting on your bottom.

patricklambin SSCrazy Eights Points: 9994 More actions · Answer 13

Good question.

Last month , I have done some tests to compare both versions 2008 R2 and 2014 and I have some problems.

2008 R2 accepts to script everything even disabled indexes , but it seems that SQL Server 2014 is more constraining as it is unable to provide the full script for all the objects of a database having disabled indexes on a table.

I have done this test with the same values for the Scripting options for 2008 R2 and 2014 with both versions 2008 R2 and 2014 of the SSMS ( Right-cclick on the database name , Tasks , Generate Scripts ) .I have done these tests to see the différences of behaviour between these both versions.

Any comment on these tests would be appreciated as I have found nothing about this difference in the forums/blogs MSDN.