-------------------------------------------------------------------------------------
 --by wls
 --非专业SQL 不求高效但求能跑
 USE tempdb
 GO
 -------------------------------------------------------------------------------------
 IF OBJECT_ID (N't_MaxInMinContinuousArr', N'U') IS NOT NULL
 DROP TABLE t_MaxInMinContinuousArr;
 GO
 CREATE TABLE t_MaxInMinContinuousArr(SNId INTEGER PRIMARY KEY,SomeDate DATETIME)
 GO
 -------------------------------------------------------------------------------------
 DECLARE @i INT
 SET @i =    --SNId起始值
 DECLARE @TestScale INTEGER
 SET @TestScale=+@i --数据规模
 DECLARE @t DATETIME ,
   @t DATETIME ,
   @dd INT ,
   @dayadd INT ,
   @tRes DATETIME
 SET @t = '-- ::'
 SET @t = '-- ::'
 SET @dd = DATEDIFF(dd, @t, @t)
 WHILE @i < @TestScale --数据规模
  BEGIN 
  SET @dayadd = @dd * RAND() 
  SET @tRes = DATEADD(dd, @dayadd, @t) + RAND() 
  INSERT INTO t_MaxInMinContinuousArr VALUES(@i , @tRes)
  SET @i = @i + 
  END
 GO
 --SELECT TOP * FROM t_MaxInMinContinuousArr
 --GO
 -------------------------------------------------------------------------------------
 --Delete some SNId randomly
 DECLARE @TestScale INTEGER
 SET @TestScale= --数据规模
 DELETE FROM t_MaxInMinContinuousArr WHERE SNId=--(SELECT abs(checksum(newid()))%@TestScale + )
 DELETE FROM t_MaxInMinContinuousArr WHERE SNId=--(SELECT abs(checksum(newid()))%@TestScale + )
 GO
 --SELECT TOP * FROM t_MaxInMinContinuousArr
 --GO
 -------------------------------------------------------------------------------------
 --now find the SNId that SNId+ is missing.
 WITH TMinAndMaxSNId
 AS(
 SELECT MIN(SNId) AS MinSNId,MAX(SNId) AS MaxSNId FROM t_MaxInMinContinuousArr --The minand max SNId
 ),
 TContinuousId
 AS
 (
 SELECT number AS SNIdCmped FROM master..spt_values,TMinAndMaxSNId WHERE type='p' AND number >=TMinAndMaxSNId.MinSNId AND number <=TMinAndMaxSNId.MaxSNId
 )
 SELECT MIN(res.SNIdCmped)- FROM
 (
 SELECT SNIdCmped FROM TContinuousId
 EXCEPT 
 SELECT SNId FROM t_MaxInMinContinuousArr) AS res 
 GO

DBCC FREEPROCCACHE 
GO
DBCC DROPCLEANBUFFERS
GO