Tags

, , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , ,


SQL PUZZLE | SQL SERVER – Disabling INDEXES : Clustered Index – A Good Idea ? | SQL Interview Question

In this post we shall understand how we can disable or enable indexes for a table in SQL Server

Let’s create table called UnderstandIndexes and insert some data for our examples

--

--Let's create table called UnderstandIndexes

CREATE TABLE UnderstandIndexes
(
	 Id INT
	,Nm VARCHAR(10)
)
GO

INSERT INTO UnderstandIndexes VALUES ( 1, 'Pawan' ) , ( 2, 'Avtaar' )
GO

CREATE UNIQUE CLUSTERED INDEX Ix_Id ON UnderstandIndexes(Id)
GO
--Commands completed successfully.

CREATE UNIQUE NONCLUSTERED INDEX Ix_Name ON UnderstandIndexes(Nm)
GO
--Commands completed successfully.

--

Now Lets see how many indexes are their on this table and their status

--

SELECT s.name tableName , x.object_id , x.name , x.type_desc , is_disabled FROM Sys.indexes x
INNER JOIN sys.objects s ON x.object_id = s.object_id
WHERE s.type = 'U' and s.name = 'UnderstandIndexes'

--

OUTPUT

--

tableName            object_id   name        type_desc            is_disabled
-------------------- ----------- ----------- -------------------- -----------
UnderstandIndexes    128719511   Ix_Id       CLUSTERED            0
UnderstandIndexes    128719511   Ix_Name     NONCLUSTERED         0

(2 rows affected)

--

Now lets write a query where Name = ‘Avtaar’

--

SELECT Id,Nm FROM UnderstandIndexes
WHERE Nm = 'Avtaar'

--

The above query is using NONCLUSTERED INDEX Ix_Name. Now Lets disable the NONCLUSTERED INDEX Ix_Name and see how SQL Server get the data.

--

ALTER INDEX Ix_Name ON UnderstandIndexes DISABLE
GO
--Commands completed successfully.

Now lets check how many indexes are there and their status on the table.

--

SELECT s.name tableName , x.object_id , x.name , x.type_desc , is_disabled FROM Sys.indexes x
INNER JOIN sys.objects s ON x.object_id = s.object_id
WHERE s.type = 'U' and s.name = 'UnderstandIndexes'

OUTPUT

tableName                 object_id   name       type_desc         is_disabled
------------------------- ----------- ---------- ----------------- -----------
UnderstandIndexes         128719511   Ix_Id      CLUSTERED         0
UnderstandIndexes         128719511   Ix_Name    NONCLUSTERED      1

(2 rows affected)

--

See the is_disabled column for NONCLUSTERED = Ix_Name is 1. This means this Index is disabled. Now lets run the query again
and see how SQL Server is getting the data.

--

SELECT Id,Nm FROM UnderstandIndexes
WHERE Nm = 'Avtaar'

--

Now the SQL Server is using Clustered Index Scan as it is the only Index available on the table. Lets enable the
NONCLUSTERED = Ix_Name again.

--

ALTER INDEX Ix_Name ON UnderstandIndexes REBUILD
GO

--

Now lets see the Indexes with the their enable/disable status. 


--

SELECT s.name tableName , x.object_id , x.name , x.type_desc , is_disabled FROM Sys.indexes x
INNER JOIN sys.objects s ON x.object_id = s.object_id
WHERE s.type = 'U' and s.name = 'UnderstandIndexes'

OUTPUT

tableName                object_id   name         type_desc       is_disabled
------------------------ ----------- ------------ --------------- -----------
UnderstandIndexes        128719511   Ix_Id        CLUSTERED       0
UnderstandIndexes        128719511   Ix_Name      NONCLUSTERED    0

(2 rows affected)


--

Now lets disable the CLUSTERED INDEX..

--

ALTER INDEX Ix_Id ON UnderstandIndexes DISABLE
GO

OUTPUT
Warning: Index 'Ix_Name' on table 'UnderstandIndexes' was disabled as a result of disabling the clustered index on the table.

--

Now lets see the Indexes with the their enable/disable status. 

--

SELECT s.name tableName , x.object_id , x.name , x.type_desc , is_disabled FROM Sys.indexes x
INNER JOIN sys.objects s ON x.object_id = s.object_id
WHERE s.type = 'U' and s.name = 'UnderstandIndexes'

OUTPUT

tableName              object_id   name      type_desc       is_disabled
---------------------- ----------- --------- --------------- -----------
UnderstandIndexes      128719511   Ix_Id     CLUSTERED       1
UnderstandIndexes      128719511   Ix_Name   NONCLUSTERED    1

(2 rows affected)

--

See once we disable the CLUSTERED Index SQL SERVER also disables the nonclustered indexes. ( is_disabled = 1 for both the rows )
Now lets see how SQL Server now will get the data for us.

--

SELECT * FROM UnderstandIndexes
GO

OUTPUT

Msg 8655, Level 16, State 1, Line 116
The query processor is unable to produce a plan because the index 'Ix_Id' on table or view 'UnderstandIndexes' is disabled.

--

SQL Server here says that it cannnot generate the execution plan as the clustered index is disabled.

Note – Once you disable the clustered index our table will of no USE.

Final Notes-

1. If we disable the Clustered Index, then data will exists, but will not be accessible for anything other than
Drop or REBUILD operations. The data rows of the disabled clustered index cannot be accessed except to drop or rebuild the clustered index.

2. All related Non-clustered Indexes and views will be unavailable as well as Foreign Keys referencing the table will be disabled
and there by leading the FAILURE for all the queries that are referencing the table.

3. There is no option to ENABLE the Index. You have to REBUILD it.

4. If we disable the Clustered Index, SQL Server will also disable the NonClustered Indexes with out any rows.
Note this is because disabled non-clustered indexes just keep the metadata, which along with the clustered index data is
adequate to REBUILD the index. See in the last column i.e row_count we have NULL in the below output.

--

SELECT s.name TableName 
, x.object_id , x.name , x.type_desc , is_disabled , p.row_count FROM Sys.indexes x
INNER JOIN sys.objects s ON x.object_id = s.object_id
LEFT JOIN sys.dm_db_partition_stats p ON p.object_id = x.object_id AND p.index_id = x.index_id
WHERE s.type = 'U' and s.name = 'UnderstandIndexes'

TableName                object_id   name          type_desc         is_disabled row_count
------------------------ ----------- ------------- ----------------- ----------- --------------------
UnderstandIndexes        128719511   Ix_Id         CLUSTERED         1           2
UnderstandIndexes        128719511   Ix_Name       NONCLUSTERED      1           NULL

(2 rows affected)

--

References

1 https://docs.microsoft.com/en-us/sql/relational-databases/indexes/disable-indexes-and-constraints
2 https://dba.stackexchange.com/questions/41519/why-disabling-a-clustered-index-makes-the-table-inaccessible

Pawan Khowal

Pawan is a SQL Server Developer. If you need any help in writing code/puzzle or training please email at – pawankkmr”AT”gmail.com. Meanwhile please go throgh the top pages from his blog.

Page Detail URL
☛ SQL Advance Puzzles https://msbiskills.com/tsql-puzzles-asked-in-interview-over-the-years/
☛ SQL Tricky Queries https://msbiskills.com/sql-puzzles-finding-outputs/
☛ SQL Server Perfomance tuning Articles https://msbiskills.com/sql-performance-tuning/
☛ SQL Server Articles https://msbiskills.com/t-sql/
☛ SQL Interview Questions & Answers https://msbiskills.com/sql-server-interview-questions/

My SQL Groups on Facebook:

1. If you like this post, you may want to join my SQL SERVER Interview Puzzles/Interview Questions on Facebook: https://www.facebook.com/groups/1430882120556342/

2. If you like this post, you may want to join my SQL Server Puzzles on Facebook:
https://www.facebook.com/groups/206594023090781/

My SQL Page on Facebook:

2. For all the updates you may follow my page -> https://www.facebook.com/MSBISkillscom-1602779883299222/

Enjoy !!! Keep Learning

Http://MsbiSkills.com