ramlicious Blogs by Tina & Prabhu

September 24, 2010

Finding gaps in SQL numeric columns

Filed under: SQL Server — Prabhuram @ 6:39 am

Here is a cool way to find gaps in SQL numeric column. At times you would have wondered how to find if there are any numbers missing in a identity column field. This should help you.

DECLARE @db as VARCHAR(100)
DECLARE @TableName as VARCHAR(100)
DECLARE @ColumnName as VARCHAR(100)
DECLARE @command NVARCHAR(max)
SET @db = 'Northwind'
SET @TableName = 'Orders'
SET @ColumnName = 'OrderId'
SET @command = '
Select
  LastSeqNumber [Last Sequence Number],
  NextSeqNumber [Next Sequence Number],
  [First Number in Gap] = LastSeqNumber + 1,
  [Last Number in Gap] = NextSeqNumber – 1,
  [Total numbers in Gap] = NextSeqNumber – (LastSeqNumber + 1)
from (
  Select LastSeqNumber = (Select isnull(Max(Seq2.' + @ColumnName + '),0) as SeqNumber
  from [' + @db + ']..[' + @TableName + '] Seq2
  where Seq2.' + @ColumnName + ' < Seq1.' + @ColumnName + ')
  , NextSeqNumber = ' + @ColumnName + '
from [' + @db + ']..[' + @TableName + '] Seq1
) as A
  where
    NextSeqNumber – LastSeqNumber > 1
  order by
    LastSeqNumber
'
exec (@command)

No Comments »

No comments yet.

RSS feed for comments on this post. TrackBack URL

Leave a comment

You must be logged in to post a comment.

Powered by WordPress