Jez's questions

我一直在阅读 SQLite 中的全文搜索文档，这一切都是有道理的，只是似乎没有为实际能够将 FTS 行与其他表中的行链接起来做出任何调整。我猜我不想将所有实体的列都放入我的 FTS 表中，因为我无法为其指定数据类型（或 STRICT），并且我不需要为 FTS 索引所有数据。所以我想使用 FK 关系从我的主实体表到其 FTS 表进行 1:1 链接，如下所示：

CREATE VIRTUAL TABLE usersFTS USING fts4(
    keywords,
    nicknames
);

-- Create the users table with an explicit foreign key reference to usersFTS
CREATE TABLE users(
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL,
    email TEXT NOT NULL UNIQUE,
    usersFTS_id INTEGER NOT NULL,
    FOREIGN KEY (usersFTS_id) REFERENCES usersFTS (rowid)
);

-- ... then:

-- Insert into usersFTS and capture the generated rowid for Alice's meta
INSERT INTO usersFTS (keywords, nicknames) VALUES ('software developer linux open-source', 'Ally Alice');

-- Capture the generated rowid for Alice
SELECT last_insert_rowid();  -- Assume it returns 1

-- Insert into users using the captured rowid
INSERT INTO users (name, email, usersFTS_id) VALUES ('Alice', '[email protected]', 1);

-- ... so that:

SELECT usr.id, usr.name, usr.email, fts.keywords, fts.nicknames
FROM usersFTS fts
INNER JOIN users usr ON usr.usersFTS_id = fts.rowid
WHERE fts.keywords MATCH '"software developer"';

但这失败于以下INSERT声明：

SQL 逻辑错误：外键不匹配 - “users”引用“usersFTS”

似乎虚拟表不能在正常的 FK 关系中引用其 rowid。那么，如何将我的全文搜索表中的一行链接到另一个包含该实体更多数据的表中的一行？有没有办法在 SQLite 中做到这一点并保持引用完整性？

我有一个在 Linux 上的 Docker 容器中运行的 SQL Server 2022 实例。我的理解是 Docker 容器可以简单地扩展它们的磁盘使用量以填满整个可用的卷空间。卷上的空间约为 10GB：

$ sudo df -h /var/lib/docker/
Filesystem      Size  Used Avail Use% Mounted on
/dev/xvda1       47G   34G   10G  78% /

然而，当我进入 SSMS 并查看数据库属性时，它显示“数据库大小 80.00 MB，可用空间 1.78MB”。为什么它说只有 1.78MB 可用？我的数据库是否仍能使用分区上的所有 10GB 空间？

我正在提交一个（可能很长）“匹配实体”列表，每个列表都包含要匹配的用户数据，以及该匹配信息的唯一 ID。来自匹配用户的实际用户数据，以及该匹配的唯一 ID，需要从我的 SQL 查询中返回。所以，假设我有 2 个实体来尝试匹配用户，这两个实体都试图将电话号码与用户的电话号码匹配；client_handle我可以使用这样的联合（是提交的唯一 ID）来匹配与提交的实体关联的任何用户以及“匹配实体”的唯一ID：

SELECT
    [client_handle] = 'axtwe-wasst',
    [user_id],
    [email],
    [mobile_no],
    [firstname],
    [surname]
FROM
    [dbo].[vAPP_UsersActive]
WHERE
    [mobile_no] in ('+44 7747 122123', '+44 7904 223323')

UNION

SELECT
    [client_handle] = 'zjfft-albwq',
    [user_id],
    [email],
    [mobile_no],
    [firstname],
    [surname]
FROM
    [dbo].[vAPP_UsersActive]
WHERE
    [mobile_no] in ('+44 7758 444111', '+44 7758 444222', '+44 7758 444333')

UNION这种方法的问题在于，如果向我提交了大量的匹配实体，它可能会导致非常多的s。1000 个提交的匹配实体将导致 999UNION秒。这实际上是性能方面的问题，是否有更好的方法来实现我想要的结果？或者，我可以遍历每个提交的匹配实体并运行一个查询来匹配每个，但是如果提交了 1000 个匹配实体，我将有 1000 个单独的查询，这似乎更糟。

我有下表：

CREATE TABLE [dbo].[MP_Notification_Audit](
    [id] [bigint] IDENTITY(1,1) NOT NULL,
    [type] [int] NOT NULL,
    [source_user_id] [bigint] NOT NULL,
    [target_user_id] [bigint] NOT NULL,
    [discussion_id] [bigint] NULL,
    [discussion_comment_id] [bigint] NULL,
    [discussion_media_id] [bigint] NULL,
    [patient_id] [bigint] NULL,
    [task_id] [bigint] NULL,
    [date_created] [datetimeoffset](7) NOT NULL,
    [clicked] [bit] NULL,
    [date_clicked] [datetimeoffset](7) NULL,
    [title] [nvarchar](max) NULL,
    [body] [nvarchar](max) NULL,
 CONSTRAINT [PK_MP_Notification_Audit] PRIMARY KEY CLUSTERED 
(
    [id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

ALTER TABLE [dbo].[MP_Notification_Audit] ADD  CONSTRAINT [DF_MP_Notification_Audit_date_created]  DEFAULT (sysdatetimeoffset()) FOR [date_created]
GO

CREATE NONCLUSTERED INDEX [IX_MP_Notification_Audit_TargetUserDateCreated] ON [dbo].[MP_Notification_Audit]
(
    [target_user_id] ASC,
    [date_created] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
GO

表中有超过 10000 行，其中 a[target_user_id]为100017.

当我执行以下查询时：

SELECT
    [target_user_id], [patient_id]
FROM
    [dbo].[MP_Notification_Audit]
WHERE
    [target_user_id] = 100017
ORDER BY
    [date_created] ASC
OFFSET 9200 ROWS
FETCH NEXT 10 ROWS ONLY

...我得到以下实际执行计划：

为什么 SQL Server 需要进行 9210 次而不是 10 次聚集键查找？索引[IX_MP_Notification_Audit_TargetUserDateCreated]应该允许它找出它需要检索到的 10 个 RID [patient_id]，并且只进行 10 个聚集键查找，对吗？

我还发现了一些更奇怪的行为——看起来 SQL Server 会因为你没有选择不可索引的列而“惩罚”你。如果我改为OFFSET10000 行，我会得到以下执行计划：

SELECT
    [target_user_id], [patient_id]
FROM
    [dbo].[MP_Notification_Audit]
WHERE
    [target_user_id] = 100017
ORDER BY
    [date_created] ASC
OFFSET 10000 ROWS
FETCH NEXT 10 ROWS ONLY

...建议创建一个包含 的索引[patient_id]，并对整个表进行低效的聚集索引扫描。花费的时间是 0.126 秒，但这显然会好很多，因为当我将不可索引的列添加[title]到查询中时，我得到了这个：

SELECT
    [target_user_id], [patient_id], [title]
FROM
    [dbo].[MP_Notification_Audit]
WHERE
    [target_user_id] = 100017
ORDER BY
    [date_created] ASC
OFFSET 10000 ROWS
FETCH NEXT 10 ROWS ONLY

...并且仍然使用非聚集索引，所用时间仅为0.032s。SQL Server 是否基本上说“您本可以创建一个索引来更有效地执行此操作，所以我们甚至不会使用您拥有的索引，我们会低效地进行查找来惩罚您”，或者我我错过了什么？

我创建了一个带有非聚集 PK 的表（这是设计使然），并在我使用WHERE子句 ( [target_user_id]) 过滤的列上创建了一个额外的非聚集索引：

CREATE TABLE [dbo].[MP_Notification_Audit] (
    [id]                    BIGINT             IDENTITY (1, 1) NOT NULL,
    [type]                  INT                NOT NULL,
    [source_user_id]        BIGINT             NOT NULL,
    [target_user_id]        BIGINT             NOT NULL,
    [discussion_id]         BIGINT             NULL,
    [discussion_comment_id] BIGINT             NULL,
    [discussion_media_id]   BIGINT             NULL,
    [patient_id]            BIGINT             NULL,
    [task_id]               BIGINT             NULL,
    [date_created]          DATETIMEOFFSET (7) CONSTRAINT [DF_MP_Notification_Audit_date_created] DEFAULT (sysdatetimeoffset()) NOT NULL,
    [clicked]               BIT                NULL,
    [date_clicked]          DATETIMEOFFSET (7) NULL,
    [title]                 NVARCHAR (MAX)     NULL,
    [body]                  NVARCHAR (MAX)     NULL,
    CONSTRAINT [PK_MP_Notification_Audit1] PRIMARY KEY NONCLUSTERED ([id] ASC)
);

[...]

CREATE NONCLUSTERED INDEX [IX_MP_Notification_Audit_TargetUser] ON [dbo].[MP_Notification_Audit]
(
    [target_user_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
GO

该表大约有 11,700 行数据，因此应该足以触发对WHERE子句的索引的使用。如果我SELECT只是要过滤的列，则仅使用索引并读取 133 个匹配行 - 仅索引扫描：

SELECT [target_user_id]
  FROM [TestDb].[dbo].[MP_Notification_Audit]
  WHERE [target_user_id] = 100017

但是，一旦我向 中添加额外的列SELECT，索引就会被忽略，并使用谓词进行表扫描以获得结果，读取超过 11,700 行：

SELECT [target_user_id], [patient_id]
  FROM [TestDb].[dbo].[MP_Notification_Audit]
  WHERE [target_user_id] = 100017

为什么它在第二个查询中忽略了我的索引？我原以为使用索引降低到 133 个 RID，然后查询所需的额外行数据，比使用谓词遍历表的每一行更有效？我知道我可以使用子句中INCLUDE需要的额外字段将列添加到索引中，SELECT以使其再次使用索引，但我对为什么在这种情况下它仍然不使用索引感兴趣。

我有一个自引用的表：

CREATE TABLE [dbo].[TestTable] (
    [id] [bigint] IDENTITY(100000,1) NOT NULL,
    [referenced_id] [bigint] NULL,
    CONSTRAINT [PK_TestTable] PRIMARY KEY CLUSTERED (
        [id] ASC
    ) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

ALTER TABLE [dbo].[TestTable] WITH NOCHECK ADD CONSTRAINT [FK_ReferencedId] FOREIGN KEY ([referenced_id])
    REFERENCES [dbo].[TestTable] ([id])
GO

ALTER TABLE [dbo].[TestTable] CHECK CONSTRAINT [FK_ReferencedId]
GO

假设我有几行相互引用：

id     | referenced_id
-------|--------------
100023 | 100024
100024 | 100023
100025 | 100026
100026 | 100023

如果我尝试DELETE使用该行WHERE [id] = 100023，则 FK 将被违反，因为100024并100026引用该行并且该DELETE行将失败。但是，如果我只是DELETE FROM [dbo].[TestTable]，它似乎可以工作并成功删除所有行。因此，SQL Server 似乎只在单个DELETE语句中要删除的所有行都被删除之后才检查 FK 约束，而不是在每行删除之间。

我可以依靠这种行为，还是DELETE有时会失败？

我有 3 个表：Room、Conference和Participant。 Room有很多Conferences，Conference有很多Participants。我需要我的查询来显示来自的字段Room，以及它具有的关联数量，以及每个具有Conferences的关联数量的总和。这是我为获取此信息而编写的查询的简化版本；首先，我刚刚选择了房间 ID：ParticipantConferenceSELECT

SELECT TOP(1000)
  rm.[Id]
FROM
  [Room] rm
LEFT JOIN (
  SELECT
    conf.[Id] AS [ConferenceId],
    MIN(conf.[Name]) AS [ConferenceName],
    MIN(conf.[RoomId]) AS [RoomId],
    COUNT(part.[Id]) AS CalcConferenceParticipantCount
  FROM
    [Conference] conf
  LEFT JOIN
    [Participant] part on part.[ConferenceId] = conf.[Id]
  GROUP BY
    conf.[Id]
  ) confData ON confData.[RoomId] = rm.[Id]
GROUP BY
  rm.[Id]

这是非常快的，因为 SQL Server 能够仅从子查询中提取数据Room并且几乎忽略了子查询（请参见下图中的试验 1 - 试验 4）。然后我在ConferenceName子查询的字段中添加，以及每个房间的会议数量计数：

SELECT TOP(1000)
  rm.[Id],
  COUNT(confData.[ConferenceId]) AS CalcRoomConferenceCount,
  MIN(confData.[ConferenceName])
FROM
  [Room] rm
LEFT JOIN (
  SELECT
    conf.[Id] AS [ConferenceId],
    MIN(conf.[Name]) AS [ConferenceName],
    MIN(conf.[RoomId]) AS [RoomId],
    COUNT(part.[Id]) AS CalcConferenceParticipantCount
  FROM
    [Conference] conf
  LEFT JOIN
    [Participant] part on part.[ConferenceId] = conf.[Id]
  GROUP BY
    conf.[Id]
  ) confData ON confData.[RoomId] = rm.[Id]
GROUP BY
  rm.[Id]

这大大降低了查询速度，大约降低了 100 倍（请参见下图中的试验 5 - 试验 7）。然后我从子查询中添加了参与者计数，这意味着使用了 2 个级别的聚合函数：

SELECT TOP(1000)
  rm.[Id],
  COUNT(confData.[ConferenceId]) AS CalcRoomConferenceCount,
  MIN(confData.[ConferenceName]),
  SUM(confData.[CalcConferenceParticipantCount]) AS CalcRoomParticipantCount
FROM
  [Room] rm
LEFT JOIN (
  SELECT
    conf.[Id] AS [ConferenceId],
    MIN(conf.[Name]) AS [ConferenceName],
    MIN(conf.[RoomId]) AS [RoomId],
    COUNT(part.[Id]) AS CalcConferenceParticipantCount
  FROM
    [Conference] conf
  LEFT JOIN
    [Participant] part on part.[ConferenceId] = conf.[Id]
  GROUP BY
    conf.[Id]
  ) confData ON confData.[RoomId] = rm.[Id]
GROUP BY
  rm.[Id]

这进一步将查询速度减慢了大约 4 倍（参见下图中的试验 8 - 试验 10）。以下是包含 10 次试验数据的客户统计数据：

下面是慢查询的查询计划：https ://www.brentozar.com/pastetheplan/?id=SJpyeec5Q

有没有一种方法可以使这种查询——我计算子查询聚合的聚合——更有效？

SQL Server 中是否可以在不登录 SQL Server 的情况下确定是否启用混合模式身份验证？

有时查询会花费很长时间，而您不想运行整个查询。这正是您可能希望查看实际执行计划以找出查询效率低下原因的时间。但是我注意到，当您告诉 SSMS 在结果中包含实际执行计划时，它只会在查询成功完成且未取消时这样做！为什么是这样？大概必须首先生成计划，因此它可以首先由 SQL Server 发送，甚至对于已取消的查询也可以显示。我知道估计的执行计划，但不一样。

我正在PIVOT创建一个表，以便最终表中的列名基于源表中的“组织名称”。问题是，“组织名称”是数据驱动的，可以是任何一组值；我使用动态 SQL 来构建PIVOT'IN子句。这意味着该值可以匹配我拥有的另一个列名，就像RecordId我正在旋转的那样。所以我有这样的事情：

.----------------------------------------------------------------------.
| RecordId | OrganizationFoo | OrganizationBar | OrganizationBaz | ... |
|----------|-----------------|-----------------|-----------------|-----|
| 123      | 182             | 76              | 56              | ... |
| 234      | 846             | 0               | 182             | ... |
| 345      | 46              | 2               | 951             | ... |
...

问题是，这都是数据驱动的，所以组织名称可以是RecordId，这会导致由于重复列而生成无效的 SQL 查询。有没有办法可以自动为PIVOT输出中的每一列添加前缀？

如果没有，我能想到的唯一三种方法是：

• 而是选择CONCAT('Prefix_', [OrgName])，但这可能会降低效率，因为[OrgName]是索引的。
• 为持有者创建一个重复表[OrgName]，每次运行此查询时，清除它并插入原始表的内容，但使用[OrgName]前缀代替。
• 总是[OrgName]首先存储前缀。

任何更清洁/更好的选择？

我有一个操作进行得相当缓慢，可能是因为我CROSS JOIN首先创建了大量的行供 SQL Server 处理，这些行后来被GROUP编辑并PIVOT编辑成更少的行和一堆列。目的是获取每个“唯一组织名称”的“每条记录的下载次数”，其中唯一组织名称是OrganizationName表中的唯一条目，AdamUser下载次数是表中关联条目的数量MaintenanceJobHistory。最后，Record连接到每一行以输出有关该记录的更多信息。这是查询：

SELECT *
FROM (
    SELECT
        rec.[Id]                                      AS RecordId,
        CONCAT('Downloads_', usr.[OrganizationName])  AS OrganizationName,
        COUNT(hist.[Id])                              AS TimesDownloaded  -- To be aggregated by PIVOT
    FROM (
        SELECT
            innerRec.[Id]
        FROM
            [dbo].[Record] innerRec
        INNER JOIN
            [dbo].[RecordClassificationLink] innerLnk ON innerLnk.[RecordId] = innerRec.[Id]
        -- WHERE (classification ID is foo or bar), for optional classification filtering
        GROUP BY
            innerRec.[Id]
        -- HAVING COUNT(innerLnk.ClassificationId) = (number of specified classifications), for optional classification filtering
    ) rec
    CROSS JOIN [dbo].[AdamUser] usr
    LEFT JOIN (
        SELECT * FROM [dbo].[MaintenanceJobHistory] WHERE [CreatedOn] > '2016-01-01 12:00:00' AND [CreatedOn] < '2016-12-01 12:00:00'
    ) hist ON hist.[AccessingUser] = usr.[Name] AND hist.[RecordId] = rec.[Id]
    GROUP BY
        rec.[Id], usr.[OrganizationName]
) srcTable
PIVOT  -- Pivot around columns outside aggregation fn, eg. heading column [OrganizationName] & all other columns: [RecordId]
(
    MIN(srcTable.[TimesDownloaded]) FOR [OrganizationName] IN (...~200 columns...)
) pivotTable
INNER JOIN [dbo].[Record] outerRec ON outerRec.[Id] = pivotTable.[RecordId]

这是执行计划：

https://www.brentozar.com/pastetheplan/?id=ry8tXM3mg

它确实给了我正确的输出，但我注意到流聚合之前的排序操作需要很长时间，并溢出到tempdb. 这可能就是为什么查询需要大约 5 分钟的时间，我的正常数据集有 200 列，而我的测试数据集只有 6 列，PIVOT可能只需要半分钟。就它处理的行数而言，被CROSS JOIN编辑的两个表 (Record和AdamUser) 分别有 38397 行和 1017 行。

有没有办法可以加快速度，或者是否有必要为我拥有的行数和列数花费这么长时间？

我想建立一个PIVOT表，但我可能不得不使用动态 SQL 除非该IN子句可以在常规 SQL 中构建。我想做这样的事情：

SELECT *
FROM
(
    SELECT x, y, z...
) srcTable
PIVOT
(
    MIN(srcTable.TimesDownloaded) FOR OrganizationName IN (SELECT CONCAT('Download_', OrganizationName) FROM AdamUser GROUP BY OrganizationName)
) pivotTable

因此，当我从SELECTunique OrganizationNamesAdamUser和 prefixDownload_时，这给了我一个表，其行值等于我想要的列的名称。虽然这是无效的语法。我是否总是必须对PIVOT列名进行硬编码？

我有一个查询在我的机器上需要很长时间（7 分钟）才能执行，我想知道我是否可以（显着）更快地完成它：

SELECT
    rec.[Id] AS RecordId,
    MIN(rec.[CreationDate]) AS RecordCreationDate,
    MIN(rec.[LastModified]) AS RecordLastModified,
    MIN(rec.[AssetType]) AS RecordAssetType,
    MIN(rec.[MasterFilename]) AS RecordMasterFilename,
    MIN(rec.[GameName]) AS RecordGameName,
    usr.[OrganizationName],
    COUNT(hist.[Id]) AS TimesDownloaded
FROM
(
    SELECT
        innerRec.Id,
        MIN(innerRec.CreationDate) AS CreationDate,
        MIN(innerRec.LastModified) AS LastModified,
        MIN(innerRec.AssetType) AS AssetType,
        MIN(innerRec.MasterFilename) AS MasterFilename,
        MIN(innerRec.GameName) AS GameName
    FROM
        [dbo].[Record] innerRec INNER JOIN [dbo].[RecordClassificationLink] innerLnk ON innerRec.Id = innerLnk.RecordId
    -- WHERE (classification ID is foo or bar)
    GROUP BY
        innerRec.Id
    -- HAVING COUNT(innerLnk.ClassificationId) = (number of specified classifications)
) rec
CROSS JOIN
    [dbo].[AdamUser] usr
LEFT JOIN
    (SELECT * FROM [dbo].[MaintenanceJobHistory] WHERE [CreatedOn] > '2016-01-01 00:00:00' AND [CreatedOn] < '2016-12-01 00:00:00') hist ON usr.Name = hist.AccessingUser AND rec.Id = hist.RecordId
GROUP BY
    rec.Id, usr.OrganizationName

它正在做的是提取要放入 Excel 电子表格报告中的数据（电子表格是否可以很好地表示这些数据不在这个问题的范围内:-)）

第一个子查询提取按分类 ID 列表选择性过滤的记录。然后将它们与用户表交叉连接，因为每个用户表行实际上包含我们真正需要的信息：用户的组织名称。然后我离开加入维护作业历史表（为每个记录下载存储一个条目）以便在记录被多次访问时创建多行，然后按记录 ID 和组织名称分组以获得“每个记录下载数”组织”算作TimesDownloaded.

读取此输出的代码然后填充一个关联数组，其键为OrganizationName，其值为TimesDownloaded，创建一个动态的等价物，PIVOT其中每个记录行包含每个组织的一列，每个包含记录下载次数的计数。

就像你想象的那样，它在大型数据集上运行得相当慢，正如我上面所说的；我正在使用的那个有 ~38000 Records 和 ~1000 个用户，这意味着交叉连接导致 ~38,000,000 行，但这在概念上似乎是必要的。

这可以显着提高效率吗？如果我PIVOT改用动态 SQL 会更好吗？

我使用的 DBMS 是 SQL Server 2014。

以下是表的模式定义：

CREATE TABLE [dbo].[AdamUser](
    [Id] [uniqueidentifier] NOT NULL,
    [Name] [nvarchar](200) NOT NULL,
    [UserGroupName] [nvarchar](50) NOT NULL,
    [OrganizationName] [nvarchar](50) NOT NULL,
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]
) ON [PRIMARY]

GO

CREATE UNIQUE NONCLUSTERED INDEX [UIX_AdamUser_Name] ON [dbo].[AdamUser]
(
    [Name] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
GO

CREATE TABLE [dbo].[MaintenanceJobHistory](
    [Id] [uniqueidentifier] NOT NULL,
    [Data] [xml] NOT NULL,
    [CreatedOn] [datetime] NOT NULL,
    [Type] [nvarchar](512) NOT NULL,
    [RecordId] [uniqueidentifier] NOT NULL,
    [AccessingUser] [nvarchar](200) NOT NULL,
 CONSTRAINT [PK_MaintenanceJobHistory] 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]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO

CREATE NONCLUSTERED INDEX [IX_MaintenanceJobHistory_CreatedOn] ON [dbo].[MaintenanceJobHistory]
(
    [CreatedOn] ASC
)
INCLUDE (     [Id],
    [RecordId],
    [AccessingUser]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
GO

CREATE TABLE [dbo].[Record](
    [Id] [uniqueidentifier] NOT NULL,
    [CreationDate] [datetime] NOT NULL,
    [LastModified] [datetime] NOT NULL,
    [AssetType] [nvarchar](max) NULL,
    [MasterFilename] [nvarchar](max) NULL,
    [GameName] [nvarchar](max) NULL,
 CONSTRAINT [PK_Record] 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]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO

CREATE TABLE [dbo].[RecordClassificationLink](
    [Id] [uniqueidentifier] NOT NULL,
    [RecordId] [uniqueidentifier] NOT NULL,
    [ClassificationId] [uniqueidentifier] NOT NULL,
 CONSTRAINT [PK_RecordClassificationLink] 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]
) ON [PRIMARY]

GO

这是执行计划：https ://www.brentozar.com/pastetheplan/?id=Sy6LlXDXg

典型输出：

然而，这个输出被调用程序按照以下几行转换成 Excel 电子表格（因此它就像一个PIVOT操作）：

.----------------------------------------------------------------------.
| Filename | Creation Date | #times downloaded by: CompanyA | CompanyB | ...
| foo.png  | 1/2/3         |                       0        | 2        |
| bar.png  | 1/3/4         |                       3        | 1        |
...

更新：

PIVOT通过将操作移至 SQL 查询本身，我最终大大提高了效率；这样，SQL Server 只需要输出Record表中的行数而不是乘以组织的数量（在你达到数百个组织之前还不错，此时它是一个巨大的数字）。手术仍然需要几分钟，但已经可以忍受多了。这是我最终决定使用的查询：

SELECT *
FROM (
    SELECT
        rec.[Id]                         AS RecordId,
        'Org_' + usr.[OrganizationName]  AS OrganizationNamePrefixed,
        COUNT(hist.[Id])                 AS TimesDownloaded  -- To be aggregated by PIVOT
    FROM (
        SELECT
            innerRec.[Id]
        FROM
            [dbo].[Record] innerRec
        INNER JOIN
            [dbo].[RecordClassificationLink] innerLnk ON innerLnk.[RecordId] = innerRec.[Id]
        -- WHERE (classification ID is foo or bar), for optional classification filtering
        GROUP BY
            innerRec.[Id]
        -- HAVING COUNT(innerLnk.ClassificationId) = (number of specified classifications), for optional classification filtering
    ) rec
    CROSS JOIN [dbo].[AdamUser] usr
    LEFT JOIN (
        SELECT * FROM [dbo].[MaintenanceJobHistory] WHERE [CreatedOn] > 'eg. 2016-01-01 12:00:00' AND [CreatedOn] < 'eg. 2016-12-01 12:00:00'
    ) hist ON hist.[AccessingUser] = usr.[Name] AND hist.[RecordId] = rec.[Id]
    GROUP BY
        rec.[Id], usr.[OrganizationName]
) srcTable
PIVOT  -- Pivot around columns outside aggregation fn, eg. heading column [OrganizationNamePrefixed] & all other columns: [RecordId]
(
    MIN(srcTable.[TimesDownloaded]) FOR [OrganizationNamePrefixed] IN (...list of ~200 columns dynamically generated...)
) pivotTable
INNER JOIN [dbo].[Record] outerRec ON outerRec.[Id] = pivotTable.[RecordId]

我添加了各种索引，并PIVOT通过仅选择聚合列、标题列和必要的其他列来尽可能地提高效率。最后，我重新JOIN使用PKRecord表RecordId来获取每行的额外记录信息。

此查询有效：

DECLARE @Ordering NVARCHAR(50)
SET @Ordering = 'aggregateid'

DECLARE @OrderDirectionInt INT
SET @OrderDirectionInt = 0 --1

SELECT * FROM (
    SELECT
        ROW_NUMBER() OVER (ORDER BY
            CASE WHEN LOWER(@Ordering) = 'aggregateid' AND @OrderDirectionInt = 1 THEN AggregateId END DESC,
            CASE WHEN LOWER(@Ordering) = 'aggregateid' AND @OrderDirectionInt = 0 THEN AggregateId END ASC
        ) AS RowNumber,
        MyField
    FROM
        MyTable
) AS TempResults
WHERE TempResults.RowNumber BETWEEN 12 AND 23

我不明白为什么ASCandDESC在语句之后（或之外） 。CASE看起来ASCandDESC被无条件地放入，这没有任何意义......它可能会解决：

ROW_NUMBER() OVER (ORDER BY
    DESC,
    AggregateId ASC
) AS RowNumber

为什么该CASE语句不包含ASCor DESC（如果您尝试这样做，则会出现语法错误）？

我试过用谷歌搜索这个问题的答案，但一无所获。

我有一个 SQL Server 实例，其复制设置为FooDatabase. 我有第二个实例，其复制设置为FooDatabase第一个实例的订阅者。复制更新工作。我想要做的是备份订阅FooDatabase，在订阅者实例上删除它，然后恢复备份。但是，当我这样做并尝试恢复备份时，我收到以下错误：

服务器“DAVEG1525-162”的恢复失败。(Microsoft.SqlServer.SmoExtended)

System.Data.SqlClient.SqlError：对象名称“master.dbo.MSreplication_options”无效。(Microsoft.SqlServer.Smo)

我已经检查并dbo.MSreplication_options确实作为系统表存在于生产实例的master数据库中，但不存在于订阅实例上。

我的问题是：如果订阅实例上不存在它，为什么它会包含在从 SQL Server 订阅实例制作的备份中？我该如何解决这个问题？

我正在使用 SQL Server 2008 r2 的复制功能通过事务请求订阅更新我的订阅者数据库。当我将其标记为重新初始化时，它确实修复了发布快照中存在的任何已修改本地表的架构和数据，但它不会删除任何已添加的新表（可能还有 SP、触发器等）。除了更新和添加现有对象之外，是否有任何方法可以同步删除发布快照中不存在的表等多余对象？

我注意到，当您设置事务复制时，SQL Server 会将身份范围管理设置为手动。这意味着在我的订阅数据库中，当我尝试将新记录插入其 PK 是标识列的表中时，它会给我一个错误并说它试图插入一个 PK 为“1”、“2 "、"3" 等。这是因为订阅者上所有身份列的当前身份值都被重置为种子值（通常为 1），而不是保持在发布者上的值。

我理解 SQL Server 这样做的原因——您应该将订阅者表保留为只读。但是，我的情况有点不正统 - 我不时通过复制更新我的订阅者，立即备份该数据库，然后我想对订阅者进行一些不会被推回发布者的更新，然后当我再次更新订阅者时，我从早期的备份中恢复其数据库并提取最新更新。因为我想在这些更新之间对订阅者进行更新（“临时增量”，如果你愿意的话），我需要标识列正常工作，而不是在复制时重置为 1。

我在设置我的出版物时尝试打开自动身份范围管理，但是当我尝试向出版物添加表时，这只会给我以下错误：

消息 21231，级别 16，状态 1，过程 sp_MSrepl_addarticle，第 2243 行
自动标识范围支持仅对允许更新订阅者的发布有用。

有什么办法可以解决这个问题吗？我确实希望将此复制呈现给 SQL Server，就好像它在订阅者端是只读的一样，因为我不打算进行将推送回发布者的更新，但我确实想做临时更新将在下一次复制之前被删除。

对于我的使用模式，我还认为快照复制可能比事务复制更合适，但问题是快照复制需要在每次更新时发送整个该死的数据库；因为我计划在最近一次复制后立即备份数据库，所以我不需要每次都进行整个传输；只是自上次以来的变化。