sql - 一张表中的行是随机排序的

Question

我目前正在开发一个在 Microsoft SQL Server 上保存数据的应用程序。在测试时，我注意到在一个表中，行似乎是随机排序的，而在所有其他表中，它们是按主键(一个自动递增整数)排序​​的。

示例:

有序表:

随机顺序:

我知道我可以在 SQL 查询中对它们进行排序，所以这不是我的问题。

我的问题:它们的顺序不同是否有原因？

编辑:有人想查看CREATE TABLE语句

有序表:

CREATE TABLE [dbo].[Label](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [Name] [nvarchar](255) NOT NULL,
    [XmlTemplatePath] [nvarchar](255) NOT NULL,
 CONSTRAINT [PK_Label] PRIMARY KEY CLUSTERED 
(
    [ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY],
 CONSTRAINT [UNIQUE_Label_XmlTemplatePath] UNIQUE NONCLUSTERED 
(
    [XmlTemplatePath] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

无序表:

CREATE TABLE [dbo].[Parameter](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [Name] [nvarchar](255) NOT NULL,
 CONSTRAINT [PK_Parameter] PRIMARY KEY CLUSTERED 
(
    [ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY],
 CONSTRAINT [UNIQUE_Parameter_Name] UNIQUE NONCLUSTERED 
(
    [Name] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

Answer 1

SQL Server 通常会努力尽可能高效地返回数据，这些数据可能按聚集索引的顺序排列，也可能不按聚集索引的顺序排列。检查您的执行计划以查看使用了什么索引以及如何扫描它。

考虑这个例子:

USE tempdb;
GO

CREATE DATABASE Demo
    ON
          (NAME='Demo_Primary1', FILENAME='C:\SqlDataFiles\Demo_Primary', SIZE=10MB)
        , (NAME='Demo_Primary2', FILENAME='D:\SqlDataFiles\Demo_Primary2', SIZE=10MB)
    LOG ON
          (NAME='Demo_Log', FILENAME='D:\SqlLogFiles\Demo_Log.ldf', SIZE=1MB);
GO

USE Demo;
GO

CREATE TABLE [dbo].[Parameter]
    (
      [ID] [int] IDENTITY(1, 1)
                 NOT NULL
    , [Name] [nvarchar](255) NOT NULL
    , CONSTRAINT [PK_Parameter] PRIMARY KEY CLUSTERED ( [ID] ASC )
        WITH ( PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF,
               IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON,
               ALLOW_PAGE_LOCKS = ON ) ON [PRIMARY]
    , CONSTRAINT [UNIQUE_Parameter_Name] UNIQUE NONCLUSTERED ( [Name] ASC )
        WITH ( PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF,
               IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON,
               ALLOW_PAGE_LOCKS = ON ) ON [PRIMARY]
    )
ON  [PRIMARY];
GO

WITH 
     t4 AS (SELECT n FROM (VALUES(0),(0),(0),(0)) t(n))
    ,t1K AS (SELECT ROW_NUMBER() OVER (ORDER BY (a.n)) AS num
        FROM t4 AS a CROSS JOIN t4 AS b CROSS JOIN t4 AS c CROSS JOIN t4 AS d CROSS JOIN t4 AS e)
INSERT INTO dbo.Parameter WITH(TABLOCKX)
SELECT CAST(num AS char(255))
FROM t1K
WHERE num BETWEEN 1 AND 256;
GO
WITH 
     t4 AS (SELECT n FROM (VALUES(0),(0),(0),(0)) t(n))
    ,t1K AS (SELECT ROW_NUMBER() OVER (ORDER BY (a.n)) AS num
        FROM t4 AS a CROSS JOIN t4 AS b CROSS JOIN t4 AS c CROSS JOIN t4 AS d CROSS JOIN t4 AS e)
INSERT INTO dbo.Parameter WITH(TABLOCKX)
SELECT CAST(num AS char(255))
FROM t1K
WHERE num BETWEEN 257 AND 512;
GO
WITH 
     t4 AS (SELECT n FROM (VALUES(0),(0),(0),(0)) t(n))
    ,t1K AS (SELECT ROW_NUMBER() OVER (ORDER BY (a.n)) AS num
        FROM t4 AS a CROSS JOIN t4 AS b CROSS JOIN t4 AS c CROSS JOIN t4 AS d CROSS JOIN t4 AS e)
INSERT INTO dbo.Parameter WITH(TABLOCKX)
SELECT CAST(num AS char(255))
FROM t1K
WHERE num BETWEEN 513 AND 768;
GO
WITH 
     t4 AS (SELECT n FROM (VALUES(0),(0),(0),(0)) t(n))
    ,t1K AS (SELECT ROW_NUMBER() OVER (ORDER BY (a.n)) AS num
        FROM t4 AS a CROSS JOIN t4 AS b CROSS JOIN t4 AS c CROSS JOIN t4 AS d CROSS JOIN t4 AS e)
INSERT INTO dbo.Parameter WITH(TABLOCKX)
SELECT CAST(num AS char(255))
FROM t1K
WHERE num BETWEEN 769 AND 1024;
GO

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
GO
SELECT * FROM dbo.Parameter;
GO

在我的 SQL 2014 系统上，我发现即使扫描了聚集索引，结果也没有按标识值的顺序返回。执行计划中的聚集索引扫描运算符显示Ordered False。在这种情况下，SQL Server 选择按分配顺序扫描表，而不是遵循页面链接的逻辑顺序，因为在 READ UNCOMMITTED 隔离级别中不需要读取完整性。

即使在其他隔离级别下，SQL Server 也可能选择扫描不同的索引，这也会导致与标识值不同的顺序。