DATETIME SELECT SELECT INTO DATE PAD STRING DYNAMIC SQL CURSOR MONEY FORMAT PERCENT STORED PROCEDURE SQL SERVER AGENT JOB OPTIMIZATION WHILE LOOP OVER PARTITION BY UPDATE
SITE SEARCH SQLUSA.com HEADLINES NEWS
SQL E/BOOKS  SQL 2012 PROGRAMMING  DOWNLOADS
SCRIPTS SQL 2005 SQL 2008 ARTICLES
SQL JOBS TWITTER FORMAT VIDEOS
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
DATETIME SELECT SELECT INTO DATE PAD STRING DYNAMIC SQL CURSOR MONEY FORMAT PERCENT STORED PROCEDURE SQL SERVER AGENT JOB OPTIMIZATION WHILE LOOP OVER PARTITION BY UPDATE