Ian Kemp's questions

我知道我可以通过多种方式实现这一点——这是一个纯粹的学术问题，看看是否可以在一条语句中做到这一点，以加深我对 SQL 的了解。

我们将一个旧的宽表拆分为两个较窄的表，一个父表和一个子表：

create table WorkTable
     ( ParentId     int             null
     , ChildId      int         not null
     , ParentField1 varchar(50) not null )

create table ParentTable
     ( Id     int         not null primary key identity
     , Field1 varchar(50) not null )

create table ChildTable
     ( Id       int not null primary key identity
     , ParentId int not null foreign key references ParentTable ( Id ) )

WorkTable包含原始表中的一堆记录 - 我们要用作子表中 ID 的该表中的 ID（通过identity_insert），以及我们实际想要在父表中设置的该表中的字段。因此，对于 中的每一行WorkTable，我们将在 中得到一行ParentTable，在 中得到另一行ChildTable。

现在我要填充ParentTable，并获取其新插入记录的 ID，以便随后插入ChildTable：

declare @IdsMap table
      ( ParentId int not null
      , ChildId  int not null )

merge ParentTable dst
using (
select ChildId
     , ParentField1
  from WorkTable
) src on 1 = 0
when not matched by target then
insert
     ( Field1 )
values
     ( src.ParentField1 )
output inserted.Id as [ParentId]
     , src.ChildId -- can't do this with a standard INSERT...OUTPUT, hence the use of MERGE
  into @IdsMap
;

update wkt
   set wkt.ParentId = ids.ParentId 
  from WorkTable wkt
           join
       @IdsMap   ids on ids.ChildId = wkt.ChildId

这有效，但它很丑陋。如果我可以将它简化为一条语句，我会更愿意，这样插入的 ID 可以ParentTable直接更新回- 从而消除了对仅用于完成此更新的表 varWorktable的需要。@IdsMap

我想我可以通过使用merge嵌套 DML 来完成此操作：

update wkt
   set wkt.ParentId = cte.ParentId
  from WorkTable wkt
           join
(
merge ParentTable dst
using (
select ChildId
     , ParentField1
  from WorkTable
) src on 1 = 0
when not matched by target then
insert
     ( Field1 )
values
     ( src.ParentField1 )
output inserted.Id as [ParentId]
     , src.ChildId
) cte on cte.ChildId = wkt.ChildId

但 MSSQL 说不：

A nested INSERT, UPDATE, DELETE, or MERGE statement is not
allowed on either side of a JOIN or APPLY operator.

CTE 中的嵌套 DML 同样失败：

;with cte as
(
select *
  from
(
merge ParentTable dst
using (
select ChildId
     , ParentField1
  from WorkTable
) src on 1 = 0
when not matched by target then
insert
     ( Field1 )
values
     ( src.ParentField1 )
output inserted.Id as [ParentId]
     , src.ChildId
) _
)
update wkt
   set wkt.ParentId = cte.ParentId
  from WorkTable wkt
           join
                 cte on cte.ChildId = wkt.ChildId

A nested INSERT, UPDATE, DELETE, or MERGE statement is not allowed in a SELECT
statement that is not the immediate source of rows for an INSERT statement.

有什么办法可以实现我想要的吗？

考虑以下表达式，它从日期时间值截断（不舍入）毫秒：

declare @now datetime2 = sysdatetime();
select @now;
select convert(datetime2, convert(varchar(20), @now, 120));

-- Output
2021-07-30 09:38:33.5566666
2021-07-30 09:38:33.0000000

注意varchar(20). 我不喜欢那个特定的长度值，因为如果我应该更改我的数据类型，可能会丢失数据：

declare @now datetimeoffset = sysdatetimeoffset() at time zone 'Pacific Standard Time';
select @now;
select convert(datetimeoffset, convert(varchar(20), @now, 120));

-- Output
2021-07-30 02:39:12.7200000 -07:00
2021-07-30 02:39:12.0000000 +00:00 -- oops, we lost the time zone too!

因此，我宁愿使用以下内容：

declare @now datetimeoffset = sysdatetimeoffset() at time zone 'Pacific Standard Time';
select @now;
select convert(datetimeoffset, convert(varchar(max), @now, 120)); -- note MAX not N

-- Output
2021-07-30 02:41:16.4566666 -07:00
2021-07-30 02:41:16.0000000 -07:00

varchar(max)我的问题是，使用over是否有任何有意义的性能影响varchar(N)——包括但不限于内存分配？

我知道如果在 predicates 中使用数据类型而不是数据类型会对查询性能产生影响(max)(N)，但在我的特定示例中我没有这样做 - 只是分配varchars 然后在将它们转换回所需的数据类型后将它们丢弃。

我们的 SQL Server 2016 Standard (SP2 CU6) 数据库上的查询存储已进入ERROR状态，我们现在正尝试使其重新联机。我看到的所有关于 QS 故障排除的文章（例如这篇文章，甚至微软自己的文档）都建议执行以下操作：

ALTER DATABASE [QDS] SET QUERY_STORE = OFF
exec [QDS].dbo.sp_query_store_consistency_check
ALTER DATABASE [QDS] SET QUERY_STORE = ON
ALTER DATABASE [QDS] SET QUERY_STORE (OPERATION_MODE = READ_WRITE)

但在我们的实例中，该过程sp_query_store_consistency_check似乎不存在任何形式。我们缺少什么？

表LogCount：

Column1 int       not null
Column2 int       not null
Column3 int       not null
[Date]  datetime  not null
[Count] float(53) not null

该表包含约 8600 万行并具有以下索引：

alter table LogCount add constraint PK_LogCount_Id primary key clustered 
( [Date], Column1, Column2, Column3 )
go
create nonclustered index IX_Column2_Date on LogCount ( Column2, [Date] )
include ( [Count] )
go

sp_spaceused给出以下内容：

name        rows        reserved    data        index_size  unused  
LogCount    85800181    8089216 KB  4226664 KB  3860968 KB  1584 KB

该Count列不会也永远不会存储浮点数，只存储整数，所以我将其更改为smallint（我预计）每行将节省 6 个字节（float(53)= 8 个字节，smallint= 2 个字节）：

drop index LogCount.IX_Column2_Date
go
alter table LogCount alter column [Count] smallint not null
go
create nonclustered index IX_Column2_Date on LogCount ( Column2, [Date] )
include ( [Count] )
go

然后我重新运行sp_spaceused：

name        rows        reserved    data        index_size  unused
LogCount    85800181    7670848 KB  5255528 KB  2414496 KB  824 KB

正如预期的那样，索引大小急剧下降，但数据大小却增加了 1 GB！

然后我重新运行上面的 drop/alter/create 语句但使用int（4 个字节）并得到以下结果：

name        rows        reserved    data        index_size  unused
LogCount    85800181    7848032 KB  5255528 KB  2591688 KB  816 KB

然后我尝试了float(1)（也是 4 个字节）：

name        rows        reserved    data        index_size  unused
LogCount    85800181    7848016 KB  5255528 KB  2591672 KB  816 KB

最后我回到原来的float(53)：

name        rows        reserved    data        index_size  unused
LogCount    85800181    10680584 KB 7726896 KB  2952464 KB  1224 KB

与原始版本相比，数据大小增加了约 3.3 GB（几乎翻了一番），而索引大小减少了约 900MB。

一位同事建议罪魁祸首可能是 MSSQL 正在为alter column语句分配额外的页面并且之后没有释放它们，所以我也尝试dbcc shrinkdatabase在每一步之后执行，但结果是一样的。

所以我的问题：

1. 为什么将列从较大数据类型更改为较小数据类型会导致使用更多数据空间？
2. sp_spaceused可靠吗？如果不是，我应该使用什么来代替？如果没有更好的选择，我如何确定更改列的数据类型是否会对磁盘空间使用产生正面或负面影响？

给定以下代码段：

-- error checking omitted for brevity
begin tran

exec database1..my_stored_procedure
exec database2..my_other_stored_procedure

if (@@error <> 0)
  rollback

commit

事务信息将插入到哪个数据库的事务日志中？

我希望两个日志都能获取所有数据，因为如果您尝试重播database1事务日志并且它只影响该数据库，那将毫无意义。我还希望您无法在不存在database1的服务器上重播事务日志database2，反之亦然。

..但我愿意更正！