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 VIDEOS
 
 
SQL E/BOOKS   WORLD, USA & SQL NEWS   FORMAT
SCRIPTS SQL 2005 SQL 2008 ARTICLES
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
SQL 2016 DESIGN & PROGRAMMING
 
 
 
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