Gaps in identites

Holes in the values of an identity column can cause data discrepencies in come cases as i have found in the past. Currently I am working on an issue with a 20,000 record table, with a current identity value above 180,000. To resolve this issue, I first need to know where the gaps in my data are coming from. The script below will display all of the gaps in my data to allow me to pinpoint when and where the problem may exist. I thought I would share this with the world.

WITH Gaps (ID,RecordNumber)
AS ( SELECT IdentityColumn AS ID,
ROW_NUMBER() OVER(ORDER BY IdentityColumn) AS RecordNumber
FROM dbo.Table )

SELECT g2.ID, g2.ID - g1.ID Gap_Afterwards
FROM Gaps g1
JOIN Gaps g2
ON g2.RecordNumber - 1 = g1.RecordNumber
WHERE g2.ID - g1.ID > 1
ORDER BY g2.ID - g1.ID

Today I ran into an issue with my automated index maintenance. It was trying to rebuild an index that I had recently removed. This prompted me to add a job to update the list of indexes that I have i my database. This job would do three tasks. The first would be to add any new indexes that I had created. It will look at the indexes i am currently maintaining (by referencing dbo.IndexMaintenance) and compare that to a list of indexes from a set of the currently existing indexes in my database. It will add a record into dbo.IndexMaintenance for each new index. After this, It will disable the maintenance for any removed indexes, as well as re-enable the maintenance on any indexes that are currently present, yet have been previously disabled. Please check it out and comment with any suggestions or thoughts.


CREATE PROC dbo.Job_UpdateIndexMaintenance
AS

-- same script we used to populate the IndexMaintenance table to begin with.
-- We will use this to snapshot what we should be maintaining now
SELECT DISTINCT schema_name(st.schema_id) SchemaName, -- schema name
object_name(si.object_id) [TableName], --Table Name
si.name IndexName, -- Index Name
0 OptionStatisticsNorecompute, -- Statistics Norecompute
1 OptionAllowRowLocks, -- Allow Row Locks
1 OptionAllowPageLocks, -- Allow Page Locks
0 OptionSortInTempDB, -- Sort In TempDB
CASE sum(CASE WHEN sc.system_type_id = 35 THEN 1 -- text
WHEN sc.system_type_id = 99 THEN 1 -- ntext
WHEN sc.system_type_id = 34 THEN 1 -- image
WHEN sc.system_type_id = 241 THEN 1 -- xml
WHEN sc.system_type_id = 165 and sc.max_length = -1 THEN 1 -- varbinary
WHEN sc.system_type_id = 167 and sc.max_length = -1 THEN 1 -- varchar
WHEN sc.system_type_id = 231 and sc.max_length = -1 THEN 1 -- nvarchar
ELSE 0
END) WHEN 0 THEN 1 else 0 end OptionOnlineRebuild, -- Online Rebuilds
1 KeepMaintained, -- Keep Maintained
CASE si.fill_factor WHEN 0 THEN 100 ELSE si.fill_factor END Fill_Factor-- Fill Factor
into #IndexMaintenance
FROM sys.indexes si
JOIN sys.tables st
ON st.object_id = si.object_id
JOIN sys.columns sc
ON si.object_id = sc.object_id
WHERE si.type_desc <> 'HEAP' -- we do not want heap data
GROUP BY st.schema_id, si.name,si.object_id,si.fill_factor

GO
-- Locate new indexes and insert them
INSERT INTO [IndexMaintenance].[dbo].[IndexMaintenance]
([SchemaName]
,[TableName]
,[IndexName]
,OptionStatisticsNorecompute
,[OptionAllowRowLocks]
,[OptionAllowPageLocks]
,OptionSortInTempdb
,[OptionOnlineRebuild]
,[KeepMaintained]
,[Fill_Factor])
SELECT im.SchemaName
,im.TableName
,im.IndexName
,im.OptionStatisticsNorecompute
,im.OptionAllowRowLocks
,im.OptionAllowPageLocks
,im.OptionSortInTempdb
,im.OptionOnlineRebuild
,im.KeepMaintained
,im.Fill_Factor
FROM #IndexMaintenance im
WHERE NOT EXISTS (SELECT 1
FROM dbo.IndexMaintenance im2
WHERE im2.SchemaName= im.SchemaName
AND im2.TableName = im.TableName
AND im2.IndexName = im.IndexName)

--Disable Old Indexes
UPDATE im
SET KeepMaintained = 0
FROM dbo.IndexMaintenance im
WHERE NOT EXISTS (SELECT 1
FROM #IndexMaintenance im2
WHERE im2.SchemaName= im.SchemaName
AND im2.TableName = im.TableName
AND im2.IndexName = im.IndexName)
AND im.KeepMaintained = 1

--Enable Old Indexes
UPDATE im
SET KeepMaintained = 1
FROM dbo.IndexMaintenance im
WHERE EXISTS (SELECT 1
FROM #IndexMaintenance im2
WHERE im2.SchemaName= im.SchemaName
AND im2.TableName = im.TableName
AND im2.IndexName = im.IndexName)
AND im.KeepMaintained = 0