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

05 Monday Mar 2018

Posted by Pawan Kumar Khowal in Download SQL Interview Q's, SQL Concepts, SQL Performance Tuning, SQL Puzzles, SQL SERVER, SQL Server Interview Questions, SQL SERVER Puzzles, T SQL Puzzles, Tricky SQL Queries

≈ Leave a comment

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

SQL SERVER | Enable or Disable CONSTRAINTs? | SQL Interview Question

05 Monday Mar 2018

≈ Leave a comment

Tags

SQL SERVER| SQL SERVER – Enable or Disable CONSTRAINTs? | SQL Interview Question

In this post we shall understand how we can disable or enable single or all the constraints belongs to a single table with their side effects.

Notes

We cannot disable/enable unique constraints. We can only do drop and recreate unique constraints. We can only do constraint disable for Foreign Key Constraint and CHECK constraints

Lets first go through the syntaxes.

Syntax to DISABLE a single CONSTRAINT.

--

ALTER TABLE TableName 
	NOCHECK CONSTRAINT ConstraintName

--

Syntax to ENABLE a single CONSTRAINT.

--

ALTER TABLE TableName 
	CHECK CONSTRAINT ConstraintName

--

Example of CHECK CONSTRAINT enabling and disabling

Let’s create a table called testConstraints and insert fews rows into it.

--

--Create a new table
CREATE TABLE testConstraints
(
	 Id INT 
	,Val VARCHAR(10)
)
GO

--Insert Some ROWS in the table
INSERT INTO testConstraints VALUES ( 1, 'Pawan' ) , (2, 'Avtaar')
GO

--

Lets add a UNIQUE CONSTRAINT to restrict the Id less than 10.

--

ALTER TABLE testConstraints
ADD CONSTRAINT Ix_Dups CHECK ( Id < 10 )
GO


--

Now lets try to insert data which fails the check constraint.

--

INSERT INTO testConstraints VALUES ( 11 , 'Pawan' ) 
GO


--

OUTPUT

Msg 547, Level 16, State 0, Line 39
The INSERT statement conflicted with the CHECK constraint "Ix_Dups". 
The conflict occurred in database "master", table "dbo.testConstraints", column 'Id'.
The statement has been terminated.


--

Note that now since we have the check CONSTRAINT in place SQL Server does not allows us to insert the above value. Now lets select the data from the table to see if anything got inserted into the table.

--

SELECT * FROM testConstraints
GO

OUTPUT

Id          Val
----------- ----------
1           Pawan
2           Avtaar

(2 rows affected)

--

Clearly nothing got inserted. Now lets disable the unique constraint and see the effects.

--

ALTER TABLE testConstraints 
	NOCHECK CONSTRAINT Ix_Dups
GO
--Commands completed successfully.

--

Now lets try to insert the data again.

--

INSERT INTO testConstraints VALUES ( 11 , 'Pawan' ) 
GO
--(1 row affected)

--

Now let’s select the data from the table to see the data.


--

SELECT * FROM testConstraints
GO

OUTPUT

Id          Val
----------- ----------
1           Pawan
2           Avtaar
11          Pawan

(3 rows affected)


--

We can also enable or disable all the CONSTRAINTs from the table.

Syntax to DISABLE all the CONSTRAINT from a table

--

ALTER TABLE tableName
	NOCHECK CONSTRAINT ALL

--

Syntax to ENABLE all the CONSTRAINT from a table.

--

ALTER TABLE tableName
	CHECK CONSTRAINT ALL

--

Related SQL SERVER Article – For verifying existing and future data.

1	https://msbiskills.com/2018/02/14/alter-table-with-check-nocheck-check-nocheck-constraint-a-deep-dive/