Tags
26 performance tuning questions and solutions, Advanced SQL interview questions, Advanced SQL Queries, Advanced SQL tutorial, Advanced SQL tutorial pdf, Difficult SQL Interview Questions, Download SQL Questions, Download SQL Server Interview Question in PDF, Download SQL SERVER Interview questions, Download SQL Server Interview questions and answers, download sql server interview questions and answers pdf, download sql server interview questions by Pawan Khowal, download sql server interview questions by Pawan Kumar, download sql server interview questions by Pawan Kumar Khowal, Download T-SQL Interview Questions, Duplicate Insertion in Primary Key Column, Free Download SQL SERVER Interview questions, How SQL SERVER handles duplicate values in an index?, How to tune SQL queries, Insert duplicate in an index, Interview questions for SQL Server Performance Tuning, Looking for SQL Optimization Interview Questions, performance sql server, Performance tips for faster SQL queries, Performance Tuning, Performance Tuning for SQL Server, Query Optimization, Query Performance Tuning, SQL, SQL Common Interview Questions, SQL Common Interview Questions and answers, SQL Complex Queries, SQL FAQ, SQL FAQs, SQL Indexes, SQL Interview Q & A, SQL Interview Questions, SQL Optimization Interview Questions, sql performance, sql performance and tuning, sql performance explained pdf, sql performance tips, SQL Performance Tuning, sql performance tuning and optimization, sql performance tuning interview questions, sql performance tuning tips, SQL Queries asked in interviews, SQL Query Optimizer, SQL Query Tuning or Query Optimization, SQL Questions, SQL Server, SQL Server - General Interview Questions and Answers, SQL Server developer Interview questions and answers, SQL Server developer Interview questions with answers, SQL SERVER Interview questions, SQL SERVER Interview questions & Answers, SQL Server Interview questions and answers, SQL Server Interview Questions and Answers - Free PDF, SQL SERVER Interview questions and answers for experienced, sql server interview questions and answers for net developers, SQL SERVER Interview questions for experienced, SQL SERVER Interview questions pdf, SQL server optimization interview questions and answers, sql server performance query, sql server performance slow, SQL Server Performance Tuning, SQL Server Performance Tuning Tips, SQL SERVER Tips, SQL SERVER Tricky questions, SQL Tips & Tricks, SQL Tips and Tricks, SQL Tricks, SQL Tricky question, SQL Tuning Overview, SQL Tutorial, T-SQL, T-SQL Interview questions, T-SQL Programming, T-SQL Server Interview Questions, T-SQL Tutorial, Tips for SQL Database Tuning and Performance, Top 10 performance tuning tips for relational databases, TOP 100 SQL SERVER INTERVIEW QUESTIONS, Top 50 SQL Server Questions & Answers, Tough PL SQL Interview Questions, Tough SQL Interview Questions, Tough SQL Queries, Tough SQL Queries Interview Questions, Tough SQL Questions, Tricky SQL Interview Questions, Tricky SQL Interview Questions and answers, Tricky SQL Queries for Interview, Tricky SQL SERVER Interview Questions and answers
How SQL SERVER handles duplicate values in an index?
Today let’s talk about how SQL Server handles duplicate values in a clustered index and unique constraints. In which cases it allows insertion of duplicate values and in which cases it throws an error. So let’s directly jumps on the demos-
CASE 1 – Duplicate Insertion in Primary Key Column
Here we are creating a simple table with 2 columns. Only point to be noted here is we have mentioned Primary Key on ID column.
-- CREATE TABLE testDuplicateIndexesPrimaryKey ( ID INT PRIMARY KEY ,NAME VARCHAR(10) ) GO --
Now check what indexes and constraints created on our table. There are some cases where SQL Server internally creates indexes or constraints.
-- EXEC sp_helpindex 'testDuplicateIndexesPrimaryKey' EXEC sp_helpconstraint 'testDuplicateIndexesPrimaryKey'; --
Output
So in table creation we mentioned primary key. Now internally SQL Server created 2 objects.
• A Unique Clustered Index – PK__testDupl__3214EC27CB282EB4(clustered, unique, primary key located on PRIMARY)
• A Constraint – PK__testDupl__3214EC27CB282EB4(PRIMARY KEY (clustered))
Note here that whenever you create a primary key SQL Server internally creates an index and a constraint. Now here unique clustered index is used to sort the data at leaf level (Physical sorting) using a B+ tree structure and constraint is used to handle the duplicate values. The Type of constraint here is primary key clustered.
Let’s prove it by running the below statement.
-- INSERT INTO testDuplicateIndexesPrimaryKey(ID,NAME) VALUES (1,'Pawan'),(2,'Avika'),(1,'Aisha') --
Output
Msg 2627, Level 14, State 1, Line 11
Violation of PRIMARY KEY constraint ‘PK__testDupl__3214EC27CB282EB4’. Cannot insert duplicate key in object ‘dbo.testDuplicateIndexesPrimaryKey’. The duplicate key value is (1). The statement has been terminated.
Now the message we are getting is that we are violating primary key constraint (PK__testDupl__3214EC27CB282EB4). Here It does not provide us any information about the clustered index we have.
CASE 2 –Duplicate Insertion in a Column having unique constraint
Here we are creating a simple table with 2 columns. Only point to be noted here is we have unique constraint on name column.
-- CREATE TABLE testDuplicateIndexesConstraint ( ID INT ,NAME VARCHAR(10) CONSTRAINT Ak_Name UNIQUE (Name) ) GO /* ---Alternate syntax to create Constraint CREATE TABLE testDuplicateIndexesConstraint ( ID INT ,NAME VARCHAR(10) ) GO ALTER TABLE testDuplicateIndexesConstraint ADD CONSTRAINT Ak_Name UNIQUE (Name); */ --
Now check what indexes and constraints created on our table.
-- EXEC sp_helpindex 'testDuplicateIndexesConstraint' EXEC sp_helpconstraint 'testDuplicateIndexesConstraint'; --
So by mentioning a unique constraint SQL Server creates 2 objects
1. A Non Clustered Index (Index Description – nonclustered, unique, unique key located on PRIMARY)
2. A Constraint (Constraint_ type – UNIQUE (non-clustered))
Please note both index and constraint are of type NonClustered. Check out the constraint type and index description for details. Now let’s execute the below query and see what error we will get and the object mentioned in there.
-- INSERT INTO testDuplicateIndexesConstraint(ID,NAME) VALUES (1,'Pawan'),(2,'Pawan') --
Output
Msg 2627, Level 14, State 1, Line 30
Violation of UNIQUE KEY constraint ‘Ak_Name’. Cannot insert duplicate key in object ‘dbo.testDuplicateIndexesConstraint’. The duplicate key value is (Pawan). The statement has been terminated.
Now the message we are getting is that we cannot insert duplicate values in Unique key constraint which we mentioned while creating the table. Here It does not provide us any information about the nonclustered index we have.
CASE 3 –Duplicate Insertion in Column having Unique Clustered Index
-- CREATE TABLE testDuplicateIndexes ( ID INT ,NAME VARCHAR(10) ) GO CREATE UNIQUE CLUSTERED INDEX Ix_ID ON testDuplicateIndexes(ID) GO --
Now check what indexes and constraints created on our table.
EXEC sp_helpindex ‘testDuplicateIndexes’
EXEC sp_helpconstraint ‘testDuplicateIndexes’;
Okay, in this SQL Server only creates a unique clustered index. Now let’s execute the below statement and see what error we will get.
-- INSERT INTO testDuplicateIndexes(ID,NAME) VALUES (1,'Pawan') ,(2,'Avika') ,(1,'Aisha') --
Output
Msg 2601, Level 14, State 1, Line 13
Cannot insert duplicate key row in object ‘dbo.testDuplicateIndexes’ with unique index ‘Ix_ID’. The duplicate key value is (1). The statement has been terminated.
In this case it is saying that it cannot insert duplicate values with index Ix_Id. Here it is using clustered index to handle duplicate values. There are not constraints involved here.
So far we have seen cases where indexes or constraints are used to restrict the insertion of duplicate values. Now let’s see cases where we can successfully insert the duplicate values and how SQL Server handles duplicate values in an index
CASE 4 –Duplicate Insertion in Column having Non-Unique Clustered Index
Here we are creating a simple table with 2 columns. Only point to be noted here is we have mentioned non unique clustered index on ID column.
-- CREATE TABLE testDuplicateIndexesNonUnique ( ID INT ,NAME VARCHAR(10) ) GO CREATE CLUSTERED INDEX Ix_ID ON testDuplicateIndexesNonUnique(ID) GO --
Now check what indexes and constraints created on our table.
-- EXEC sp_helpindex 'testDuplicateIndexesNonUnique' EXEC sp_helpconstraint 'testDuplicateIndexesNonUnique'; --
In this case SQL Server only creates a non-unique clustered index. Now let’s see if we can insert duplicate values or not.
-- INSERT INTO testDuplicateIndexesNonUnique(ID,NAME) VALUES (1,'Pawan'),(2,'Avika'),(1,'Aisha') --
It allows us to insert duplicate values. Now the question is how SQL Server internally handles this.
Okay so the point to be noted here is that we can create a clustered index on columns that contains duplicate values. Clustered index in this case should be Non Unique in this case. Now in this case SQL Server internally adds a four digit unique identifier after the column value to maintain the uniqueness internally.
In the above example we have a non-unique clustered index on Name column. Now for example Manoj, For manoj we have 2 values. For the first value the data will be inserted as it is. For the second manoj value SQL internally adds a 4 digit like shown in the above example. So first value will be manoj and second value will be manoj7654. User will not be affected by this.
So internally it will increase the space taken by the index. Hence always try to create unique clustered indexes on less percentage of duplicate values.
Summary
If you create a PRIMARY KEY SQL Server internally creates 2 objects
- A Unique Clustered Index (clustered, unique, primary key located on PRIMARY)
- A Constraint – (PRIMARY KEY (clustered))
- If you try to insert duplicate values this type of error will be thrown – Violation of PRIMARY KEY constraint
If you create Unique Constraint SQL Server internally creates 2 objects
- A non clustered index – (Index Description – nonclustered, unique, unique key located on PRIMARY)
- A Constraint (Constraint_ type – UNIQUE (non-clustered))
- If you try to insert duplicate values this type of error will be thrown – Violation of UNIQUE KEY constraint
If you create a Unique Clustered Index SQL Server internally create single object
- If you try to insert duplicate values this type of error will be thrown – Cannot insert duplicate key row in object ‘dbo.testDuplicateIndexes’ with unique index ‘Ix_ID’
If you create a NON-Unique Clustered Index SQL Server internally create single object
- Here duplicate and non duplicate data will be inserted successfully. SQL Server here internally add a 4 digit integer value to the original value to maintain uniqueness, but this will increase the index size.
I hope you have enjoyed the article. Thanks for reading
-Pawan Khowal
MSBISkills.com
You must be logged in to post a comment.