Hello friends, In previous article we were learn How to remove identity column in SQL server ? and SQL Server Replace Multiple Spaces in String with Single Space In this article We are going to get query to find missing indexes in SQL Server. First of all we needs to know What is Missing Indexes in Sql Server and how it generates.
When we creates a table with Identity primary key column, it generates auto sequence number for primary key column when data is inserted. But if we delete data from table and insert new data and when it take place frequently (Delete/Insert). The sequence of primary key column skips these numbers as shown in following SQL Queries.
CREATE DATABASE SWCLASSDB
Go
Use SWCLASSDB
Go
CREATE SCHEMA SWC
Go
CREATE TABLE SWC.tbStudentsRollNo
(
StudentId INT IDENTITY(1,1) CONSTRAINT PK_StudentId PRIMARY KEY,
RollNo INT
)
Go
DECLARE @Cnt int = 1
SET NOCOUNT ON;
WHILE @Cnt <= 20
BEGIN
INSERT INTO SWC.tbStudentsRollNo(RollNo) VALUES(@Cnt)
SET @Cnt +=1
END
Go
DELETE FROM SWC.tbStudentsRollNo WHERE StudentId%2=0
Go
DECLARE @Cnt int = 21
SET NOCOUNT ON;
WHILE @Cnt <= 30
BEGIN
INSERT INTO SWC.tbStudentsRollNo(RollNo) VALUES(@Cnt)
SET @Cnt +=1
END
Go
DELETE FROM SWC.tbStudentsRollNo WHERE StudentId%2<>0
Go
DECLARE @Cnt int = 31
SET NOCOUNT ON;
WHILE @Cnt <= 40
BEGIN
INSERT INTO SWC.tbStudentsRollNo(RollNo) VALUES(@Cnt)
SET @Cnt +=1
END
Go
DELETE FROM SWC.tbStudentsRollNo WHERE StudentId>30
Go
DECLARE @Cnt int = 1101
SET NOCOUNT ON;
WHILE @Cnt <= 1110
BEGIN
INSERT INTO SWC.tbStudentsRollNo(RollNo) VALUES(@Cnt)
SET @Cnt +=1
END
Go
SELECT * FROM SWC.tbStudentsRollNo
Go
OUTPUT: StudentId RollNo ----------- ----------- 22 22 24 24 26 26 28 28 30 30 41 1101 42 1102 43 1103 44 1104 45 1105 46 1106 47 1107 48 1108 49 1109 50 1110
So How we can get missing indexes for StudentId column ? And insert data with these numbers.
Step 1: First we needs to remove identity for StudentId Column. We can achieve it by Sql Server Management Studio Design User Interface. To do this
1). Open Tools Menu and select Options command. Click on Designers and Disable following option. Prevent saving changes that require table re-creation.
2). Open object explorer (by press F7 if not shown), Expand DATABASE, Expand Tables, Right Click on tbStudentsRollNo table and click Design command. Select StudentId, Goto Column Properties at bottom panel. Set (Is Identity) under Identity specification to No. and close window with save.
or by T-Sql as explained in my previous article How to remove identity column in SQL server ?
Step 2: After Removing identity from StudentId. Find Min index value for StudentId column for SWC.tbStudentsRollNo table.
SELECT Min(StudentId) MinStudentId FROM SWC.tbStudentsRollNo
Go
OUTPUT: MinStudentId ------------ 22
Step 3: Update it with Starting Index say 1.
UPDATE SWC.tbStudentsRollNo SET StudentId=1 WHERE StudentId=22
Go
SELECT * FROM SWC.tbStudentsRollNo
Go
OUTPUT: StudentId RollNo ----------- ----------- 1 22 24 24 26 26 28 28 30 30 41 1101 42 1102 43 1103 44 1104 45 1105 46 1106 47 1107 48 1108 49 1109 50 1110
Step 4: Query to get Missing Indexes in SWC.tbStudentsRollNo
SELECT DISTINCT StudentId+1 AS MissingStudentId FROM SWC.tbStudentsRollNo
WHERE StudentId+1 NOT IN (SELECT DISTINCT StudentId FROM SWC.tbStudentsRollNo)
Go
OUTPUT: MissingStudentId ---------------- 2 25 27 29 31 51
Step 5: Getting first missing index for StudentId
DECLARE @nextStudentId int
;WITH CTE AS
(SELECT DISTINCT StudentId+1 AS MissingStudentId FROM SWC.tbStudentsRollNo
WHERE StudentId+1 NOT IN (SELECT DISTINCT StudentId FROM SWC.tbStudentsRollNo))
SELECT TOP 1 @nextStudentId = MissingStudentId FROM CTE ORDER BY MissingStudentId
SELECT @nextStudentId nextStudentId
Go
OUTPUT: nextStudentId ------------- 2
Query to Insert Data With Missing Indexes
DECLARE @nextStudentId int
DECLARE @tmpTable TABLE (StudentId int,RollNo int)
DECLARE @cnt int=11000
PRINT 'Missing Indexes......'
;WITH CTE AS
(SELECT DISTINCT StudentId+1 AS MissingStudentId FROM SWC.tbStudentsRollNo
WHERE StudentId+1 NOT IN (SELECT DISTINCT StudentId FROM SWC.tbStudentsRollNo))
SELECT * FROM CTE ORDER BY MissingStudentId
WHILE @cnt <= 11050
BEGIN
;WITH CTE AS
(SELECT DISTINCT (StudentId+1) AS MissingStudentId FROM SWC.tbStudentsRollNo
WHERE (StudentId+1) NOT IN (SELECT DISTINCT StudentId FROM SWC.tbStudentsRollNo))
SELECT TOP 1 @nextStudentId = MissingStudentId FROM CTE ORDER BY MissingStudentId
INSERT INTO SWC.tbStudentsRollNo(StudentId,RollNo) VALUES(@nextStudentId,@cnt)
INSERT INTO @tmpTable VALUES(@nextStudentId,@cnt)
SET @cnt+=1
END
PRINT 'New Inserted Data......'
SELECT * FROM @tmpTable
PRINT 'Old + New Data........'
SELECT * FROM SWC.tbStudentsRollNo
Go
OUTPUT: Missing Indexes...... MissingStudentId ---------------- 2 25 27 29 31 51 New Inserted Data...... StudentId RollNo ----------- ----------- 2 11000 3 11001 4 11002 5 11003 6 11004 7 11005 8 11006 9 11007 10 11008 11 11009 12 11010 13 11011 14 11012 15 11013 16 11014 17 11015 18 11016 19 11017 20 11018 21 11019 22 11020 23 11021 25 11022 27 11023 29 11024 31 11025 32 11026 33 11027 34 11028 35 11029 36 11030 37 11031 38 11032 39 11033 40 11034 51 11035 52 11036 53 11037 54 11038 55 11039 56 11040 57 11041 58 11042 59 11043 60 11044 61 11045 62 11046 63 11047 64 11048 65 11049 66 11050 Old + New Data........ StudentId RollNo ----------- ----------- 1 22 2 11000 3 11001 4 11002 5 11003 6 11004 7 11005 8 11006 9 11007 10 11008 11 11009 12 11010 13 11011 14 11012 15 11013 16 11014 17 11015 18 11016 19 11017 20 11018 21 11019 22 11020 23 11021 24 24 25 11022 26 26 27 11023 28 28 29 11024 30 30 31 11025 32 11026 33 11027 34 11028 35 11029 36 11030 37 11031 38 11032 39 11033 40 11034 41 1101 42 1102 43 1103 44 1104 45 1105 46 1106 47 1107 48 1108 49 1109 50 1110 51 11035 52 11036 53 11037 54 11038 55 11039 56 11040 57 11041 58 11042 59 11043 60 11044 61 11045 62 11046 63 11047 64 11048 65 11049 66 11050
If you have any query or question or topic on which, we might have to write an article for your interest or any kind of suggestion regarding this post, Just feel free to write us, by hit add comment button below or contact via Contact Us form.
Your feedback and suggestions will be highly appreciated. Also try to leave comments from your valid verified email account, so that we can respond you quickly.