Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
shiyam198
Yak Posting Veteran
94 Posts |
 Posted - 2010-04-30 : 02:20:32
|
| Test 011. I create an index.create clustered index myindxon adventureworks.dbo.DatabaseLog(PostTime)(This table already has a non-clustered index, which is the primary key – different column of course.)2. Doing a select on this table.select * from adventureworks.dbo.DatabaseLogNo problem. It displays all the rows of data.3. I disable the index.alter index myindxon adventureworks.dbo.DatabaseLogdisableIt displays the following warning, when I ran this.Warning: Index 'PK_DatabaseLog_DatabaseLogID' on table 'DatabaseLog' was disabled as a result of disabling the clustered index on the table.Q1. Why did it disable the other non-clustered index (primary key) when I disabled only the clustered index I created – “myindx”?Q2. When I run a select statement on this table“select * from adventureworks.dbo.DatabaseLog”It gives me the following error – The query processor is unable to produce a plan because the index 'myindx' on table or view 'DatabaseLog' is disabled.Why can’t I select data in a table just because an index is disabled? I thought the only reason we use index is to make the Database function faster by arranging the data in heirarchical structure. |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2010-04-30 : 02:38:34
|
| i think reason is because the nonclustered index points to clustered index once it was created on table. so when it was disabled the dependent non clustered index also got disabled and as there's no clustered index now it was not able to create a plan for query.------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
 |
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2010-04-30 : 02:51:36
|
If you disable the clustered index, the entire table is inaccessible. That's because the clustered index is the table, it has the actual data pages at the leaf level.From Books Online:quote:
DISABLE Marks the index as disabled and unavailable for use by the Database Engine. Any index can be disabled. The index definition of a disabled index remains in the system catalog with no underlying index data. Disabling a clustered index prevents user access to the underlying table data. To enable an index, use ALTER INDEX REBUILD or CREATE INDEX WITH DROP_EXISTING. For more information, see Disabling Indexes.
--Gail ShawSQL Server MVP |
|
|
|
shiyam198
Yak Posting Veteran
94 Posts |
Posted - 2010-05-06 : 23:22:20
|
| Thanks a lot guys.- Shiyam |
|
|
|
|
|
|
Index Question