我创建了带有这样的数字的表:如何找到数据间隙并插入 NULL 数据点而不是有间隙 https://stackoverflow.com/questions/20911946/sql-how-to-find-gaps-of-data-and-insert-null-data-points-instead-having-gap
;WITH
Pass0 as (select 1 as C union all select 1), --2 rows
Pass1 as (select 1 as C from Pass0 as A, Pass0 as B),--4 rows
Pass2 as (select 1 as C from Pass1 as A, Pass1 as B),--16 rows
Pass3 as (select 1 as C from Pass2 as A, Pass2 as B),--256 rows
Pass4 as (select 1 as C from Pass3 as A, Pass3 as B),--65536 rows
Pass5 as (select 1 as C from Pass4 as A, Pass4 as B),--4,294,967,296 rows
Tally as (select row_number() over(order by C) as Number from Pass5)
select Number INTO dbo.Numbers from Tally where Number <= 1000000
它的工作就像一个魅力,但在运行 SQL Server 几个月、一些 SQL 服务重新启动等之后,我的数据库恢复了几分钟。
我不确定我的表在恢复时间后是否损坏,但是我注意到在某些时候我的行不按顺序排列。
MIN is 1,
MAX is 1000000,
AVG is 500000
所以看起来所有行仍然就位,但最后一行不是 1000000。
此外,在前 3332 行之后,它会丢失订单,下一行编号为 49029。
我使用该表来查找数据中的间隙并插入 NULL。
我基于该表的应用程序失败了。如果该表有序,我不会在应用程序方面进行任何检查,但这是我的假设。我使用的是SQL引擎,所以我应该得到保证。
有什么想法会发生什么吗?