SQL Server 2008 r2--Find the minimum nindex,nindex existence and nindex+1 does not exist the maximum value in the minimum contiguous array _mssql2008

In fact, we move the brain slightly, the problem can be converted to, is to find the smallest contiguous array of the maximum value, the size of the array can be 1.

=======================================================================

Play a full set, send the Buddha to the west.

In order to facilitate learning research, it is necessary to write a full set of sample code.

---------------------------------------------------------------------------------------by WLS-non-professional SQL not efficient but can run Use the 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 starting value DECLARE @TestScale Integ ER SET @TestScale =+@i--Data scale DECLARE @t datetime, @t datetime, @dd int, @dayadd int, @tRes DATE Time SET @t = '--:: ' Set @t = '--:: ' Set @dd = DATEDIFF (dd, @t, @t) while @i < @TestScale--data scale BEGIN S ET @dayadd = @dd * rand () SET @tRes = DATEADD (dd, @dayadd, @t) + RAND () INSERT into T_maxinmincontinuousarr VA Lues (@i, @tRes) SET @i = @i + END Go--select top * from T_mAxinmincontinuousarr--go--------------------------------------------------------------------------------------  -delete some snid randomly DECLARE @TestScale INTEGER SET @TestScale =--Data size Delete from T_maxinmincontinuousarr WHERE snid=--(Select ABS (Checksum (NEWID ())% @TestScale +) DELETE from T_maxinmincontinuousarr WHERE snid=--(select ABS (check SUM (NEWID ())% @TestScale +) go--select top * T_maxinmincontinuousarr--go-------------------------------------
 --------------------------------------------------now find the Snid this snid+ is missing.  With Tminandmaxsnid as (SELECT min (Snid) as Minsnid,max (Snid) as Maxsnid from T_maxinmincontinuousarr--the min and 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 to Tcontinuousid EXCEPT select Snid from T_maxiNmincontinuousarr) as Res go 

Attached Implementation plan

=======================================================================

I do not know whether this code can be used, the first post the follow-up slowly change it.

Network code is risky copy and paste should be cautious

Execute the two statements to clear the cache.

DBCC FREEPROCCACHE 
go
dbcc dropcleanbuffers
go

=======================================================================

20151103-01

There's a problem with the code.

=======================================================================

20151103-02

I tried it again (about dozens of guesses) and found that I could only handle the missing search within 2048. This is a sensitive figure that has to be studied.

Of course, it may be that I am not professional, writing code has problems.

Fortunately, I am not in the development, production problems encountered, but also leisurely analysis to find the problem.

The revelation of this is: you annoying little crawler sites, the error code is here I still do not change.

Your behavior is illegal, not to say that the notice and then delete is OK.

I reserve all the rights conferred upon me by law.