SQL SERVER – Disabled Index and Index Levels and B-Tree

This blog post is written in response to T-SQL Tuesday hosted by Michael J. Swart.

Recently, I presented a session at the Microsoft Bangalore office. Everybody eagerly wanted to learn more, to the extent that they wanted a mentor to train each of them in order to move on to the next level. I have many mentors worldwide as I keep on traveling, in addition to being already a part of Solid Quality Mentors. However, if I have to take one name in India, I will take the name of Vinod Kumar, who has given me many insights and helped me to become a better presenter of my sessions. I recently met him at his office at Microsoft Bangalore. He was very kind and helped several attendees to think about the very subject I was discussing with them. During the discussion, he has asked a single interesting question:

What will be the status of the B-Tree structure when index is disabled?

He also gave a hint: use the script which I have used earlier over SQL SERVER – Index Levels, Page Count, Record Count and DMV – sys.dm_db_index_physical_stats.

Well, let us follow the same advice and hint given by Vinod Kumar so we can try to answer his question.

In the following script, the following operations are to be done:

• Create Table
• Create Clustered Index
• Check the Index Levels
• Disable Index
• Check the Index Levels

USE tempdb
GO
-- Create Table IndexTable
CREATE TABLE IndexTable (ID CHAR(800),
FirstName CHAR(2000),
LastName CHAR(3000),
City CHAR(2200))
GO
-- Create Clustered Index
CREATE CLUSTERED INDEX [IX_IndexTable_ID] ON IndexTable
(
[ID] ASC
) ON [PRIMARY] GO
-- Insert one Million Records
INSERT INTO IndexTable (ID,FirstName,LastName,City)
SELECT TOP 100 ROW_NUMBER() OVER (ORDER BY a.name) RowID,
'Bob',
CASE WHEN ROW_NUMBER() OVER (ORDER BY a.name)%2 = 1 THEN 'Smith'
ELSE 'Brown' END,
CASE WHEN ROW_NUMBER() OVER (ORDER BY a.name)%10 = 1 THEN 'New York'
WHEN ROW_NUMBER() OVER (ORDER BY a.name)%10 = 5 THEN 'San Marino'
WHEN ROW_NUMBER() OVER (ORDER BY a.name)%10 = 3 THEN 'Los Angeles'
ELSE 'Houston' END
FROM sys.all_objects a
CROSS JOIN sys.all_objects b
GO
-- Check the Index Level
SELECT avg_page_space_used_in_percent
,avg_fragmentation_in_percent
,index_level
,record_count
,page_count
,fragment_count
,avg_record_size_in_bytes
FROM sys.dm_db_index_physical_stats(DB_ID('TempDb'),OBJECT_ID('IndexTable'),NULL,NULL,'DETAILED')
GO
-- Disable the Index
ALTER INDEX [IX_IndexTable_ID] ON [dbo].[IndexTable] DISABLE
GO
-- Check the Index Level
SELECT avg_page_space_used_in_percent
,avg_fragmentation_in_percent
,index_level
,record_count
,page_count
,fragment_count
,avg_record_size_in_bytes
FROM sys.dm_db_index_physical_stats(DB_ID('TempDb'),OBJECT_ID('IndexTable'),NULL,NULL,'DETAILED')
GO
-- Clean up
DROP TABLE IndexTable
GO

Let us now observe the output now.

We can clearly see from the output that B-Tree structure is no more maintained after disabling the index. This means that the index is no more applicable.

I want to thank Vinod for bringing up a very interesting subject. He made me realize I had not blogged about it yet before.

I suggest that you also read my other articles in the series by searching ‘Disabled Index’ at Search@SQLAuthority.com.

Reference: Pinal Dave (https://blog.sqlauthority.com)