datetime tune date into pad dynamic cursor money percent sp job isnumeric while over update
FREE TRIAL  SQL 2012 PROGRAMMING  SEARCH
SQL Server Scripts SQL 2005 SQL 2008 Articles
SQL JOBS NEWS FORMAT DEV JOBS
How to find missing numbers in a sequence?

Execute the following SQL Server T-SQL script in Management Studio Query Editor to find the missing sequence ranges in SalesOrderID which is an identity sequence Primary Key.

-- SQL missing ranges - SQL misssing records - SQL missing rows - sequence missing values

-- SQL gaps - data gaps - range gaps - SQL select into sql server

USE AdventureWorks;

USE tempdb;

SELECT * INTO SOH

FROM AdventureWorks.Sales.SalesOrderHeader

GO

 

DELETE SOH WHERE MONTH(OrderDate) = 11

GO

 

SELECT   MissingRangeStart,

         MissingRangeEnd

FROM     (SELECT soh1.SalesOrderID + 1 AS MissingRangeStart,

                 (SELECT min(SalesOrderID) - 1

                  FROM   SOH AS soh

                  WHERE  soh.SalesOrderID > soh1.SalesOrderID) AS MissingRangeEnd

          FROM   SOH AS soh1

                 LEFT OUTER JOIN SOH AS soh2

                   ON soh1.SalesOrderID = soh2.SalesOrderID - 1

          WHERE  soh2.SalesOrderID IS NULL) AS a

WHERE    MissingRangeEnd IS NOT NULL

ORDER BY MissingRangeStart;

GO

 

/* Results

 

MissingRangeStart MissingRangeEnd

44481             44739

47960             48285

57012             58900

*/

Exam Prep 70-461
Exam 70-461