sepupic's questions

我有一个带有许多索引的分区表，所有索引都是对齐的，只有一个索引没有对齐。这是因为它通过多个分区来控制唯一性：

CREATE UNIQUE NONCLUSTERED INDEX [ix_uq_fltr_IdTransazioneRettificata_BM] ON [dbo].[Transazioni]
(
    [IdTransazioneRettificata] ASC
)
WHERE ([id]>(10000000000.) AND [StatusFatturazione]<>(15) AND [IdTransazioneRettificata] IS NOT NULL)
ON [PRIMARY]

当我执行一个简单的删除操作时，会发生错误：

select id
into #t
from billing_prod2.dbo.Transazioni
where DataInserimento = '20240229' 
and Data = '20240201'
--(162.321 rows affected)

alter table #t add primary key(id)

delete t
from billing_prod2.dbo.Transazioni t
     join #t tt
        on t.id =  tt.id

SSMS 的输出：

消息 3624，级别 20，状态 1，第 11 行 系统断言检查失败。检查 SQL Server 错误日志以获取详细信息。通常，断言失败是由软件错误或数据损坏引起的。要检查数据库是否损坏，请考虑运行 DBCC CHECKDB。如果您同意在安装过程中将转储发送给 Microsoft，则小型转储将发送给 Microsoft。Microsoft 可能会在最新的 Service Pack 或技术支持的修补程序中提供更新。消息 596，级别 21，状态 1，第 10 行 无法继续执行，因为会话处于终止状态。消息 0，级别 20，状态 0，第 10 行 当前命令发生严重错误。如果有结果，则应丢弃。

错误日志的输出：

错误：17065，严重性：16，状态：1。 SQL Server 断言：文件：<“cxrowset.cpp”>，行 = 2026 失败断言 = 'cstePrefix >= 1' 范围在分区 id 列上缺少前缀。此错误可能与时间相关。如果重新运行该语句后错误仍然存​​在，请使用 DBCC CHECKDB 检查数据库的结构完整性，或重新启动服务器以确保内存中的数据结构未损坏。错误：3624，严重性：20，状态：1。系统断言检查失败。检查 SQL Server 错误日志以获取详细信息。通常，断言失败是由软件错误或数据损坏引起的。要检查数据库是否损坏，请考虑运行 DBCC CHECKDB。如果您同意在安装过程中将转储发送给 Microsoft，则小型转储将发送给 Microsoft。

还有一个足够长的转储文件，但如果它可能停止，我也可以粘贴它。

这些都没有明确说明哪个索引有问题。

DBCC CHECKDB什么也没说，

dbcc checktable('dbo.transazioni') with NO_INFOMSGS, ALL_ERRORMSGS

什么也没说。

我唯一能猜到的应该是一个不经常使用的索引，否则错误日志中会报告更多错误，所以我从这个开始，不对齐且较少使用。

删除它后，错误就消失了，所以问题确实出在那个未对齐的索引上。

谁能解释一下吗？

数据库已启用 TDE。

Microsoft SQL Server 2019 (RTM-CU22-GDR) (KB5029378) - 15.0.4326.1 (X64) 2023 年 8 月 18 日 14:05:15 版权所有 (C) 2019 Microsoft Corporation 标准版（64 位），位于 Windows Server 2016 Datacenter 10.0 (内部版本 14393:)（虚拟机管理程序）

我的任务是定期复制数据库，每天两次，我不应该破坏任何备份链，也不应该积累这些临时备份。因此，我尝试进行copy_only备份init以覆盖单个备份文件。

我的数据库使用 TDE 加密。

我backup compression在服务器级别使用，“备份压缩默认值”在我的 sys.configurations 中为 1。我没有with compression在我的命令中使用显式选项。

这是我完整的@@version：

Microsoft SQL Server 2019 (RTM-CU8) (KB4577194) - 15.0.4073.23 (X64) Sep 23 2020 16:03:08 版权所有 (C) 2019 Microsoft Corporation Enterprise Edition (64-bit) o​​n Windows Server 2016 Datacenter 10.0 (Build 14393) :)（管理程序）

我的问题是下面的这个错误：

消息 3241，级别 16，状态 40，第 8 行设备“W:\SQL_backup\copy_only\billing_prod2_test.bak”上的媒体系列格式不正确。SQL Server 无法处理此媒体系列。消息 3013，级别 16，状态 1，第 8 行 RESTORE DATABASE 异常终止。

这是我执行的完整代码：

backup database billing_prod2
to disk = 'W:\SQL_backup\copy_only\billing_prod2_test.bak'
with init, copy_only--, format;
go

restore database billing_1
from disk = 'W:\SQL_backup\copy_only\billing_prod2_test.bak'
with move 'billing_prod2' to 'E:\SQL_data\billing_1.mdf',
move 'billing_prod2_log' to 'E:\SQL_data\billing_1_log.ldf',
replace

第一次执行时没有错误，完整备份的大小约为35Mb，并且可以完美恢复。

第二次备份执行没有错误，但奇怪的是大小为 260Mb。这是我在磁盘上看到的大小，并且在输出中写入了相同的大小，restore headeronly它仍然可以毫无错误地执行。但是当我这样做时restore database，就会出现上面提到的错误。

我尝试使用另一个数据库和另一个磁盘执行相同的代码来保存备份，但结果始终相同：第一次工作，第二次文件被覆盖但大小不同，不仅仅是双倍大小，它几乎是初始大小的 10 倍。restore headeronly永远不会失败，并且总是只报告备份文件中的一个文件，但大小是多倍的并且restore database失败了。

只有当我添加format选项时，备份的大小才变得正常并且恢复成功。

我的问题是：在这种情况下，为什么我应该在备份中使用格式才能恢复？

这个事实记录在这里：

更改授权 (Transact-SQL)

如果目标实体不是数据库并且实体正在转移给新所有者，则将删除目标上的所有权限。

在我面对事实之前，我对此一无所知。

有一天，需要向EXECUTE某个用户授予对每个现有/未来存储过程的权限，这个用户只是db_datareader数据库角色的成员，他不应该修改任何数据，但是一旦他拥有所有权链EXECUTE他将能够通过存储过程修改数据的权限。所以我不得不打破所有权链并生成一个脚本，将所有现有表的授权更改为其他用户。

一旦更改了授权，该数据库中所有表的所有权限都消失了。我检查了上面提到的文档，发现记录了这种行为。但这很出乎意料（至少对我来说），所以我想知道是否有人有任何解释。他们为什么这样做？如果某个表的授权已更改，为什么需要删除该表的所有权限？

这里我们有两个类似grouping sets 的查询，使用 whereSELECT子句包含一些聚合计算的表达式：

SELECT RN10, RN10 / 10, COUNT(*) FROM 
(
       SELECT RN, RN/10 AS RN10, RN/100 AS RN100 FROM 
       (
               SELECT RN = -1 + ROW_NUMBER() OVER (ORDER BY 1/0) 
               FROM master..spt_values
       ) A
) B
GROUP BY GROUPING SETS ((RN10), (RN10 / 10), ())
ORDER BY 1, 2

它的计划在这里：第一个查询计划

和

SELECT RN10, SUBSTRING(RN,3,99), COUNT(*) FROM 
(
       SELECT RN, SUBSTRING(RN,2,99) AS RN10 FROM 
       (
               SELECT RN = CAST(-1 + ROW_NUMBER() OVER (ORDER BY 1/0) AS VARCHAR(99)) 
               FROM master..spt_values
       ) A
) B
GROUP BY GROUPING SETS ((RN10), (SUBSTRING(RN,3,99)), ())
ORDER BY 1, 2

对应的计划在这里：第二个查询计划

这两个查询首先计算一些聚合表达式，RN10 / 10在第一种情况和SUBSTRING(RN,3,99)第二种情况下，然后在SELECT子句中使用相同的表达式，但正如第一个计划所示，它在第一个查询中重新计算，而不是在第二个查询中。

结果，我们NULL在第一个结果集中有 s，这是非常出乎意料的：

有人可以解释为什么第一个查询进行两次计算（一次在聚合中，一次在 final 中select），而第二次只计算一次？

这是小型复制品：

create table dbo.t (id int primary key, v int);
insert into dbo.t values (1, 1), (2, 2);

create table dbo.s (id int primary key, v int);
insert into dbo.s values (1, 10);
go

create trigger dbo.tr_t__iou
on dbo.t
instead of update
as
begin
 set nocount on;

 exec sp_lock @@spid;
end;
go

update dbo.t set v = 10 where id = 1;

update t
 set
  v = 10
from
 dbo.s s join
 dbo.t t on t.id = s.id;

update t
 set
  v = 10
from
 (values (1, 10)) s(id, v) join
 dbo.t t on t.id = s.id;
go

drop table dbo.t, dbo.s;
go

sp_lock在第一个和最后一个案例中受影响行的trigger报告 U-key lock中，但在第二个案例中根本没有lock，如何解释？

在下面的示例中，结果Index Spool已经排序，为什么我们在Top N Sort这里而不是 simple Top？

use tempdb;
go

create table dbo.t1 (id int identity primary key, v int);
create table dbo.t2 (id int identity primary key, v int);

insert into dbo.t1
(v)
select top (1000)
row_number() over (order by 1/0)
from
master.dbo.spt_values a cross join
master.dbo.spt_values b;

insert into dbo.t2
(v)
select top (10000)
row_number() over (order by 1/0) + 10000
from
master.dbo.spt_values a cross join
master.dbo.spt_values b;

set statistics xml, io on;

select
sum(a.v + b.v)
from
dbo.t1 a outer apply
(select top (1) v from dbo.t2 where v >= a.v order by v) b;

set statistics xml,io off;
go

drop table dbo.t1, dbo.t2;
go

它在所有版本中都可以重现2008 R2（我只是没有要测试的早期版本的服务器）

我继承了一个应用程序在sysadmin帐户下运行的系统。我将此帐户限制为所有数据库上的db_datareader++并设置会话以在服务器上捕获。即使用户是其中的成员并且对表没有应用粒度限制， 看到一些失败也让我感到非常惊讶。db_datawriterEXECUTEextended eventspermission errors
insertsdb_datawriter

然后我注意到只有插入该集合IDENTITY_INSERT失败。有问题的数据库已经满了，identity而且他们的代码太多了SET IDENTITY_INSERT。代码不仅意味着modules存储在服务器上，还意味着C#代码。

为了能够设置identity_insert用户必须拥有该表或对该表具有ALTER权限。但事实是你不能ALTER只授予表，我被迫授予ALTER整个数据库给这个user （我会让这个用户db_ddladmin角色的成员只添加 42 个权限（！！！） vs 51 个权限，可以通过授予ALTER数据库添加到用户权限）

我对重构数据库不感兴趣sequence（服务器版本是2014这样我理论上可以做到）而且我不能只添加execute as dbo到每个使用的 spidentity_insert因为还有要重写的应用程序代码，我只是想知道为什么微软会做这么奇怪db_datawriter无法设置的权限设计identity_insert？

是否有其他方法可以使用户能够设置identity_insert添加的权限少于db_ddladmin？

更新

我尝试了LowlyDBA提供的解决方案synonyms：

create table dbo.t(id int identity);
go

alter schema sch transfer dbo.t;
go

create synonym dbo.t for sch.t;
go

set IDENTITY_INSERT dbo.t on;

这会导致错误

消息 1088，级别 16，状态 11，第 1 行找不到对象“dbo.t”，因为它不存在或您没有权限。

Antoine Hernandez仅在模式上授予用户ALTER的解决方案似乎是邪恶的，所以我接受它

这是我在SQL Server 2008 R2, 2012,上测试过的复制品2016。

第二个和第三个查询使用RowCount Spool，为什么第一个不使用？

create table dbo.t (id int identity primary key, v int);
--create statistics ST_t__v on dbo.t(v) with norecompute;

insert into dbo.t (v)
select top (10000) rand(checksum(newid())) * 5
from master.dbo.spt_values a cross join
     master.dbo.spt_values b;
go

declare @v int;

set statistics xml, io on;
select @v = v from dbo.t where exists(select 1 from dbo.t where v = 4);
select @v = v from dbo.t where exists(select 1 from dbo.t where v = 4 having count(*) > 0);
select @v = v from dbo.t where exists(select 1 from dbo.t where v = 40);
set statistics xml, io off;
go

drop table dbo.t;
go

结果，IO 统计信息为：

Table 't'. Scan count 2, ***logical reads 20024***, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.


Table 't'. Scan count 2, logical reads 48, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.


Table 't'. Scan count 2, logical reads 27, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.