SQL Server:表中的行更改了它们的顺序 [英] SQL Server : rows in table changed their order

问题描述

我用这样的数字创建了表:如何找到数据的间隙并插入NULL数据点而不是有间隙

I've created table with number like this: 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 服务重新启动等后，我的数据库恢复了几分钟.

It worked like a charm, but after months of running SQL Server, some SQL service restarts etc., my database was recovering for few minutes.

我不确定我的表是否在恢复时间后坏了，但是我在某些时候注意到我的行没有按顺序排列.

I am not sure if my table broke after restore time, however I've noticed at some point that my rows are not in order.

MIN is 1,
MAX is 1000000,
AVG is 500000

所以看起来所有行都在原位，但最后一行不是 1000000.

So it looks like still all rows are in place, but the last row is not the 1000000.

同样在前 3332 行之后它会丢失一个订单，下一行的编号是 49029.

Also after first 3332 rows it loses an order and the next row Number is 49029.

我使用该表来查找数据中的空白并插入 NULL.

I use that table to find gaps in data and insert NULLs.

我基于该表的应用程序失败.如果该表按顺序排列，我不会在应用程序端进行任何检查，但这是我的假设.我正在使用 SQL 引擎，所以我应该得到保证.

My application that's based on that table failed. I don't have any checks on application side if that table is in order, but that was my assumption. I am using SQL engine, so I should be guaranteed.

知道会发生什么吗?

推荐答案

表中的行无法更改其顺序，因为..... 它们没有顺序.集合的顺序没有定义.除非你的选择有一个 ORDER BY 子句，否则它返回的顺序是随机的，甚至可以在两次调用之间改变.

Rows in a table can not change their order because..... they do not have an order. The order of a set is not defined. Unless your select has an ORDER BY clause the order it is returned is random and can change even between two calls.

这是基本的 SQL.

想要订单，在SELECT语句中放入订单.

You want an order, put an order into the SELECT statement.

这篇关于SQL Server:表中的行更改了它们的顺序的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！