问题[sql-server](dba)

按照SQL Server DAG 的手动故障转移中的步骤操作

要验证两个可用性组是否已准备好进行故障转移，请使用以下在 SQLCMD 模式下运行的 T-SQL 查询：

:CONNECT TDPRD071  
SELECT ag.name, drs.database_id, db_name(drs.database_id) as database_name,  
       drs.group_id, drs.replica_id, drs.last_hardened_lsn  
FROM sys.dm_hadr_database_replica_states drs  
INNER JOIN sys.availability_groups ag ON drs.group_id = ag.group_id;

:CONNECT TDDR071  
SELECT ag.name, drs.database_id, db_name(drs.database_id) as database_name,  
       drs.group_id, drs.replica_id, drs.last_hardened_lsn  
FROM sys.dm_hadr_database_replica_states drs  
INNER JOIN sys.availability_groups ag ON drs.group_id = ag.group_id;

如文章中所示，从全局主节点运行上述查询的结果屏幕截图显示了与 DAG 中每个 AG 的连接。

但是，当我在 DAG 的全局主节点上运行查询时，得到的结果仅来自与转发器的连接。因此，显示的两组结果仅来自与 AG02 的连接。

但是，如果我单独运行这两个查询（均来自全局主数据库），它们将根据情况正确连接到 AG01 或 AG02。

请问您有什么建议可以解释我为什么会出现这种行为吗？

谢谢。

根据我在网上以及本网站找到的信息，我尝试了以下所有方法（1-5）来检索 SQL Server 的最后启动日期：

SELECT 0 as Id, SYSDATETIME() as DT
union
SELECT 1, sqlserver_start_time FROM sys.dm_os_sys_info  
union
SELECT 2, login_time FROM sys.dm_exec_sessions WHERE session_id = 1
union
SELECT 3, start_time from sys.traces where is_default = 1
union
SELECT 4, crdate FROM sysdatabases WHERE name='tempdb'
union
SELECT 5, create_date FROM sys.databases WHERE name = 'tempdb'

结果是：

0   2025-04-17 23:38:26.9364166
1   2025-04-13 19:09:42.2000000
2   2025-04-13 19:09:44.3166667
3   2025-04-13 19:09:46.5600000
4   2025-04-13 19:09:51.0900000
5   2025-04-13 19:09:51.0900000

问题：我一小时前刚启动了机器（以及随之启动的 SQL Server）。我预期的应该是 2025-04-17 22:38……，而不是 2025-04-13 19:09……。

该 SQL 服务器安装在 VMware 盒中。

在另一个 SQL Server 中，相同的版本和版本，我得到了正确的结果：

Id  DT
0   2025-04-18 14:34:49.3442637
1   2025-04-18 14:34:03.4333333
2   2025-04-18 14:34:05.1233333
3   2025-04-18 14:34:09.7400000
4   2025-04-18 14:34:14.6100000
5   2025-04-18 14:34:14.6100000

我不明白为什么虚拟机中的结果会出错。我每次工作时都会启动和关闭主机和虚拟机。

我这里遗漏了什么吗？

• SQL Server 开发版 2019

我正在阅读这篇文章 并做了以下事情：

go
create table fiirst (
col1 int,
col2 int
);
create table seecond(
col1 int,
col2 int
);
with
n1(c) as (select 0 union all select 0 ),
n2(c) as ( select 0 from n1 as t1 cross join n1 as t2),
n3(c) as ( select 0 from n2 as t1 cross join n2 as t2),
n4(c) as (select 0 from n3 as t1 cross join n3 as t2),
ids(id) as (select ROW_NUMBER() over (order by (select null)) from n4)
insert into fiirst(col1,col2)
select id,id 
from ids;
with
n1(c) as (select 0 union all select 0 ),
n2(c) as ( select 0 from n1 as t1 cross join n1 as t2),
n3(c) as ( select 0 from n2 as t1 cross join n2 as t2),
n4(c) as (select 0 from n3 as t1 cross join n3 as t2),
ids(id) as (select ROW_NUMBER() over (order by (select null)) from n4)
insert into seecond(col1,col2)
select id,id
from ids;
----Craig Freedman's query

select *
from fiirst
where fiirst.col1 > (
    select min(seecond.col1)
    from seecond
    where seecond.col2 < fiirst.col2
);

尽管表本身是堆，但我还是得到了一个索引假脱机。问题是，这是怎么发生的？为什么我会在堆上得到一个索引假脱机？在上面链接中提到的例子中，没有行，所以没有假脱机，但在这里我却看到了？

我的 T-SQL 脚本出了点问题。我正在思考如何让它输出预期的输出。

这是我的 T-SQL 脚本：

DECLARE @user_id VARCHAR(50) = 'UGUID-2025-01-27-14-09-22-1967-ABCDEFGHIJKL'; -- FOR TESTING PURPOSES ONLY
DECLARE @status_t TABLE (status VARCHAR(50));
INSERT INTO @status_t VALUES ('PENDING'),('APPROVED'),('PROCESSING')

SELECT
    orders.batch_code, -- FOR DEMONSTRATION PURPOSES ONLY
    oa_prod.nested_data AS nested_prod,
    cnb.amount_to_pay
FROM
    [testDB].[dbo].[Orders] AS orders
RIGHT OUTER JOIN
    [testDB].[dbo].[Payment] AS cnb
ON
    orders.order_id = cnb.order_id
LEFT OUTER JOIN
    [testDB].[dbo].[StatusRef] AS stat_ref
ON
    orders.stat_ref_id = stat_ref.stat_ref_id
CROSS APPLY( -- OR OUTER APPLY
    SELECT
        orders.batch_code,
        orders.order_id,
        prod.prod_name,
        pv_kn.key_name,
        pv_kv.value,
        CASE WHEN
            orders.prod_id IS NOT NULL
        THEN
            prod.disc_price
        WHEN
            orders.prod_var_id IS NOT NULL
        THEN
            prod_var.disc_price
        END AS disc_price,
        orders.quantity
    FROM
        [testDB].[dbo].[Product] AS prod
    RIGHT OUTER JOIN
        [testDB].[dbo].[SubProduct] AS prod_var
    ON
        prod.prod_id = prod_var.prod_id
    LEFT OUTER JOIN
        [testDB].[dbo].[SubProductVarKeyValue] AS pv_kv
    ON
        prod_var.prod_var_id = pv_kv.prod_var_id
    LEFT OUTER JOIN
        [testDB].[dbo].[SubProductVarKeyNames] AS pv_kn
    ON
        pv_kv.pv_key_name_id = pv_kn.pv_key_name_id
    WHERE       
        prod.prod_id = orders.prod_id
        OR prod_var.prod_var_id = orders.prod_var_id
    FOR JSON PATH,
    INCLUDE_NULL_VALUES
) AS oa_prod(nested_data) -- it's a syntax analyzer lint conflict ONLY
WHERE
    orders.disable = 0
    AND cnb.disable = 0
    AND orders.user_id = @user_id
    AND stat_ref.com_usd_wrds IN (SELECT status FROM @status_t)
ORDER BY
    orders.dt_stamp DESC,
    orders.batch_code ASC -- To prevent batch_code from being separated
FOR JSON PATH,
INCLUDE_NULL_VALUES

这是我使用表变量的最小、可重复的示例：

DECLARE @StatusRef TABLE(
    stat_ref_id VARCHAR(50) PRIMARY KEY NOT NULL,
    com_usd_wrds NVARCHAR(100) NOT NULL
);

DECLARE @Product TABLE(
    prod_id VARCHAR(50) PRIMARY KEY NOT NULL,
    prod_name VARCHAR(200) NOT NULL,
    stock INT NOT NULL,
    disc_price DECIMAL(12, 2) NOT NULL
);

DECLARE @SubProduct TABLE(
    prod_var_id VARCHAR(50) PRIMARY KEY NOT NULL,
    stock INT NOT NULL,
    disc_price DECIMAL(12, 2) NOT NULL,
    prod_id VARCHAR(50) NOT NULL
);

DECLARE @Orders TABLE(
    order_id VARCHAR(50) PRIMARY KEY NOT NULL,
    batch_code VARCHAR(50) NULL,
    quantity INT NOT NULL,
    stat_ref_id VARCHAR(50) NOT NULL,
    disable BIT DEFAULT (0) NOT NULL,
    dt_stamp DATETIME NOT NULL,
    prod_id VARCHAR(50) NULL,
    prod_var_id VARCHAR(50) NULL,
    user_id VARCHAR(50) NOT NULL
);

DECLARE @Payment TABLE(
    amount_to_pay DECIMAL(14, 2) NOT NULL,
    order_id VARCHAR(50) NOT NULL,
    disable BIT DEFAULT (0) NOT NULL
);

DECLARE @SubProductVarKeyValue TABLE(
    value VARCHAR(100) NOT NULL,
    prod_var_id VARCHAR(50) NOT NULL,
    pv_key_name_id VARCHAR(50) NOT NULL
);

DECLARE @SubProductVarKeyNames TABLE(
    pv_key_name_id VARCHAR(50) PRIMARY KEY NOT NULL,
    key_name VARCHAR(100) NOT NULL
);

INSERT INTO @StatusRef
VALUES
(
    'STAT-REF-1001', -- stat_ref_id
    'PENDING' -- com_usd_wrds
),
(
    'STAT-REF-1002', -- stat_ref_id
    'APPROVED' -- com_usd_wrds
),
(
    'STAT-REF-1003', -- stat_ref_id
    'PROCESSING' -- com_usd_wrds
);

INSERT INTO @Product
VALUES
(
    'PROD-ID-1001', -- prod_id
    'iPhone', -- prod_name
    0, -- stock | dependent to @SubProduct
    0.00 -- disc_price | dependent to @SubProduct
),
(
    'PROD-ID-1002', -- prod_id
    'Samsung', -- prod_name
    0, -- stock | dependent to @SubProduct
    0.00 -- disc_price | dependent to @SubProduct
),
(
    'PROD-ID-1003', -- prod_id
    'Nokia', -- prod_name
    75, -- stock
    33150.00 -- disc_price
),
(
    'PROD-ID-1004', -- prod_id
    'Google', -- prod_name
    100, -- stock
    53509.00 -- disc_price
),
(
    'PROD-ID-1005', -- prod_id
    'Sony', -- prod_name
    0, -- stock | dependent to @SubProduct
    0.00 -- disc_price | dependent to @SubProduct
),
(
    'PROD-ID-1006', -- prod_id
    'Lenovo', -- prod_name
    0, -- stock | dependent to @SubProduct
    0.00 -- disc_price | dependent to @SubProduct
);

INSERT INTO @SubProduct
VALUES
(
    'PROD-VAR-ID-1', -- prod_var_id
    25, -- stock
    45809.00, -- disc_price
    'PROD-ID-1001' -- prod_id
),
(
    'PROD-VAR-ID-2', -- prod_var_id
    50, -- stock
    40209.00, -- disc_price
    'PROD-ID-1002' -- prod_id
),
(
    'PROD-VAR-ID-3', -- prod_var_id
    0, -- stock | dependent to @Product
    0.00, -- disc_price | dependent to @Product
    'PROD-ID-1003' -- prod_id
),
(
    'PROD-VAR-ID-4', -- prod_var_id
    0, -- stock | dependent to @Product
    0.00, -- disc_price | dependent to @Product
    'PROD-ID-1004' -- prod_id
),
(
    'PROD-VAR-ID-5', -- prod_var_id
    125, -- stock
    25809.00, -- disc_price
    'PROD-ID-1005' -- prod_id
),
(
    'PROD-VAR-ID-6', -- prod_var_id
    150, -- stock
    49100.00, -- disc_price
    'PROD-ID-1006' -- prod_id
);

INSERT INTO @SubProductVarKeyValue
VALUES
(
    'new', -- value
    'PROD-VAR-ID-1', -- prod_var_id
    'PVKN-ID-1' -- pv_key_name_id
),
(
    'new', -- value
    'PROD-VAR-ID-2', -- prod_var_id
    'PVKN-ID-1' -- pv_key_name_id
),
(
    'new', -- value
    'PROD-VAR-ID-5', -- prod_var_id
    'PVKN-ID-1' -- pv_key_name_id
),
(
    'new', -- value
    'PROD-VAR-ID-6', -- prod_var_id
    'PVKN-ID-1' -- pv_key_name_id
)

INSERT INTO @SubProductVarKeyNames
VALUES
(
    'PVKN-ID-1', -- pv_key_name_id
    'Condition' -- key_name
)

INSERT INTO @Orders
(
    order_id,
    batch_code,
    quantity,
    stat_ref_id,
    dt_stamp,
    prod_id,
    prod_var_id,
    user_id
)
VALUES
(
    'ORDER-2025-04-11-B71D0E2F5D8C', -- order_id
    NULL, -- batch_code
    1, -- quantity
    'STAT-REF-1003', -- stat_ref_id
    '2025-04-14 10:17:20.963', -- dt_stamp
    NULL, -- prod_id
    'PROD-VAR-ID-1', -- prod_var_id
    'UGUID-2025-01-27-14-09-22-1967-ABCDEFGHIJKL' -- user_id
),
(
    'ORDER-2025-04-11-D95EB033CA40', -- order_id
    'BGUID-2025-04-11-6D81B58FAE94', -- batch_code
    2, -- quantity
    'STAT-REF-1001', -- stat_ref_id
    '2025-04-13 09:17:20.963', -- dt_stamp
    NULL, -- prod_id
    'PROD-VAR-ID-2', -- prod_var_id
    'UGUID-2025-01-27-14-09-22-1967-ABCDEFGHIJKL' -- user_id
),
(
    'ORDER-2025-04-11-7F04EFA2BB60', -- order_id
    'BGUID-2025-04-11-6D81B58FAE94', -- batch_code
    2, -- quantity
    'STAT-REF-1001', -- stat_ref_id
    '2025-04-13 09:17:20.963', -- dt_stamp
    'PROD-ID-1003', -- prod_id
    NULL, -- prod_var_id
    'UGUID-2025-01-27-14-09-22-1967-ABCDEFGHIJKL' -- user_id
),
(
    'ORDER-2025-04-10-3F03EAA47686', -- order_id
    'BGUID-2025-04-10-20239FD2059F', -- batch_code
    1, -- quantity
    'STAT-REF-1002', -- stat_ref_id
    '2025-04-12 08:17:20.963', -- dt_stamp
    'PROD-ID-1004', -- prod_id
    NULL, -- prod_var_id
    'UGUID-2025-01-27-14-09-22-1967-ABCDEFGHIJKL' -- user_id
),
(
    'ORDER-2025-04-10-F4A89E2C4A30', -- order_id
    'BGUID-2025-04-10-20239FD2059F', -- batch_code
    1, -- quantity
    'STAT-REF-1002', -- stat_ref_id
    '2025-04-12 08:17:20.963', -- dt_stamp
    NULL, -- prod_id
    'PROD-VAR-ID-5', -- prod_var_id
    'UGUID-2025-01-27-14-09-22-1967-ABCDEFGHIJKL' -- user_id
),
(
    'ORDER-2025-04-08-31BD887341FA', -- order_id
    NULL, -- batch_code
    1, -- quantity
    'STAT-REF-1001', -- stat_ref_id
    '2025-04-11 07:17:20.963', -- dt_stamp
    NULL, -- prod_id
    'PROD-VAR-ID-6', -- prod_var_id
    'UGUID-2025-01-27-14-09-22-1967-ABCDEFGHIJKL' -- user_id
);

INSERT INTO @Payment
(
    amount_to_pay,
    order_id
)
VALUES
(
    45809.00, -- amount_to_pay
    'ORDER-2025-04-11-B71D0E2F5D8C' -- order_id
),
(
    146718.00, -- amount_to_pay
    'ORDER-2025-04-11-D95EB033CA40' -- order_id
),
(
    146718.00, -- amount_to_pay
    'ORDER-2025-04-11-7F04EFA2BB60' -- order_id
),
(
    79318.00, -- amount_to_pay
    'ORDER-2025-04-10-3F03EAA47686' -- order_id
),
(
    79318.00, -- amount_to_pay
    'ORDER-2025-04-10-F4A89E2C4A30' -- order_id
),
(
    49100.00, -- amount_to_pay
    'ORDER-2025-04-08-31BD887341FA' -- order_id
);

SELECT * FROM @StatusRef

输出：

SELECT * FROM @Product

输出：

SELECT * FROM @SubProduct

输出：

SELECT * FROM @Orders ORDER BY dt_stamp

输出：

SELECT * FROM @Payment

输出：

SELECT * FROM @SubProductVarKeyValue

输出：

SELECT * FROM @SubProductVarKeyNames

输出：

以下是修改后的脚本示例，与提供的第一个脚本类似：

DECLARE @user_id VARCHAR(50) = 'UGUID-2025-01-27-14-09-22-1967-ABCDEFGHIJKL'; -- FOR TESTING PURPOSES ONLY
DECLARE @status_t TABLE (status VARCHAR(50));
INSERT INTO @status_t VALUES ('PENDING'),('APPROVED'),('PROCESSING')

SELECT
    orders.batch_code, -- FOR DEMONSTRATION PURPOSES ONLY
    oa_prod.nested_data AS nested_prod,
    cnb.amount_to_pay
FROM
    @Orders AS orders
RIGHT OUTER JOIN
    @Payment AS cnb
ON
    orders.order_id = cnb.order_id
LEFT OUTER JOIN
    @StatusRef AS stat_ref
ON
    orders.stat_ref_id = stat_ref.stat_ref_id
CROSS APPLY( -- OR OUTER APPLY
    SELECT
        orders.batch_code,
        orders.order_id,
        prod.prod_name,
        pv_kn.key_name,
        pv_kv.value,
        CASE WHEN
            orders.prod_id IS NOT NULL
        THEN
            prod.disc_price
        WHEN
            orders.prod_var_id IS NOT NULL
        THEN
            prod_var.disc_price
        END AS disc_price,
        orders.quantity
    FROM
        @Product AS prod
    RIGHT OUTER JOIN
        @SubProduct AS prod_var
    ON
        prod.prod_id = prod_var.prod_id
    LEFT OUTER JOIN
        @SubProductVarKeyValue AS pv_kv
    ON
        prod_var.prod_var_id = pv_kv.prod_var_id
    LEFT OUTER JOIN
        @SubProductVarKeyNames AS pv_kn
    ON
        pv_kv.pv_key_name_id = pv_kn.pv_key_name_id
    WHERE       
        prod.prod_id = orders.prod_id
        OR prod_var.prod_var_id = orders.prod_var_id
    FOR JSON PATH,
    INCLUDE_NULL_VALUES
) AS oa_prod(nested_data) -- it's a syntax analyzer lint conflict ONLY
WHERE
    orders.disable = 0
    AND cnb.disable = 0
    AND orders.user_id = @user_id
    AND stat_ref.com_usd_wrds IN (SELECT status FROM @status_t)
ORDER BY
    orders.dt_stamp DESC
    --orders.batch_code ASC -- To prevent batch_code from being separated
FOR JSON PATH,
INCLUDE_NULL_VALUES

不幸的是，当我美化 JSON 输出时，它生成的内容如下：

[
  {
    "batch_code": null,
    "nested_prod": [
      {
        "batch_code": null,
        "order_id": "ORDER-2025-04-11-B71D0E2F5D8C",
        "prod_name": "iPhone",
        "key_name": "Condition",
        "value": "new",
        "disc_price": 45809,
        "quantity": 1
      }
    ],
    "amount_to_pay": 45809
  },
  {
    "batch_code": "BGUID-2025-04-11-6D81B58FAE94",
    "nested_prod": [
      {
        "batch_code": "BGUID-2025-04-11-6D81B58FAE94",
        "order_id": "ORDER-2025-04-11-D95EB033CA40",
        "prod_name": "Samsung",
        "key_name": "Condition",
        "value": "new",
        "disc_price": 40209,
        "quantity": 2
      }
    ],
    "amount_to_pay": 146718
  },
  {
    "batch_code": "BGUID-2025-04-11-6D81B58FAE94",
    "nested_prod": [
      {
        "batch_code": "BGUID-2025-04-11-6D81B58FAE94",
        "order_id": "ORDER-2025-04-11-7F04EFA2BB60",
        "prod_name": "Nokia",
        "key_name": null,
        "value": null,
        "disc_price": 33150,
        "quantity": 2
      }
    ],
    "amount_to_pay": 146718
  },
  {
    "batch_code": "BGUID-2025-04-10-20239FD2059F",
    "nested_prod": [
      {
        "batch_code": "BGUID-2025-04-10-20239FD2059F",
        "order_id": "ORDER-2025-04-10-3F03EAA47686",
        "prod_name": "Google",
        "key_name": null,
        "value": null,
        "disc_price": 53509,
        "quantity": 1
      }
    ],
    "amount_to_pay": 79318
  },
  {
    "batch_code": "BGUID-2025-04-10-20239FD2059F",
    "nested_prod": [
      {
        "batch_code": "BGUID-2025-04-10-20239FD2059F",
        "order_id": "ORDER-2025-04-10-F4A89E2C4A30",
        "prod_name": "Sony",
        "key_name": "Condition",
        "value": "new",
        "disc_price": 25809,
        "quantity": 1
      }
    ],
    "amount_to_pay": 79318
  },
  {
    "batch_code": null,
    "nested_prod": [
      {
        "batch_code": null,
        "order_id": "ORDER-2025-04-08-31BD887341FA",
        "prod_name": "Lenovo",
        "key_name": "Condition",
        "value": "new",
        "disc_price": 49100,
        "quantity": 1
      }
    ],
    "amount_to_pay": 49100
  }
]

但是，我需要的输出看起来像这样：

[
  {
    "batch_code": null,
    "nested_prod": [
      {
        "batch_code": null,
        "order_id": "ORDER-2025-04-11-B71D0E2F5D8C",
        "prod_name": "iPhone",
        "key_name": "Condition",
        "value": "new",
        "disc_price": 45809,
        "quantity": 1
      }
    ],
    "amount_to_pay": 45809
  },
  {
    "batch_code": "BGUID-2025-04-11-6D81B58FAE94",
    "nested_prod": [
      {
        "batch_code": "BGUID-2025-04-11-6D81B58FAE94",
        "order_id": "ORDER-2025-04-11-D95EB033CA40",
        "prod_name": "Samsung",
        "key_name": "Condition",
        "value": "new",
        "disc_price": 40209,
        "quantity": 2
      },
      {
        "batch_code": "BGUID-2025-04-11-6D81B58FAE94",
        "order_id": "ORDER-2025-04-11-7F04EFA2BB60",
        "prod_name": "Nokia",
        "key_name": null,
        "value": null,
        "disc_price": 33150,
        "quantity": 2
      }
    ],
    "amount_to_pay": 146718
  },
  {
    "batch_code": "BGUID-2025-04-10-20239FD2059F",
    "nested_prod": [
      {
        "batch_code": "BGUID-2025-04-10-20239FD2059F",
        "order_id": "ORDER-2025-04-10-3F03EAA47686",
        "prod_name": "Google",
        "key_name": null,
        "value": null,
        "disc_price": 53509,
        "quantity": 1
      },
      {
        "batch_code": "BGUID-2025-04-10-20239FD2059F",
        "order_id": "ORDER-2025-04-10-F4A89E2C4A30",
        "prod_name": "Sony",
        "key_name": "Condition",
        "value": "new",
        "disc_price": 25809,
        "quantity": 1
      }
    ],
    "amount_to_pay": 79318
  },
  {
    "batch_code": null,
    "nested_prod": [
      {
        "batch_code": null,
        "order_id": "ORDER-2025-04-08-31BD887341FA",
        "prod_name": "Lenovo",
        "key_name": "Condition",
        "value": "new",
        "disc_price": 49100,
        "quantity": 1
      }
    ],
    "amount_to_pay": 49100
  }
]

注意：是在客户端应用中预先计算的。因此，当重复的被分组amount_to_pay时，它应该成为一个实例。batch_code

用户订单可能会导致上面显示的理想 JSON 输出发生变化。

有人熟悉我正在处理的问题吗？

我尽可能地不愿意实现通用表表达式 (CTE) 方法。

这篇经典的并发安全文章显然是为一次只更新一行而设计的。在我的例子中，我有一个表值输入，并且希望以并发安全的方式更新每一行。我知道这并不总是可行的，但我希望尽可能地接近这一点。MERGE这似乎是一个自然的解决方案，但我并不信任它，而且它确实很容易出现 bug。Michael J. Swart 文章中剩下的两种方法是：

1. 事务内部的锁提示（更新更常见）

CREATE PROCEDURE s_AccountDetails_Upsert ( @Email nvarchar(4000), @Etc nvarchar(max) )
AS 
SET XACT_ABORT ON;
BEGIN TRAN
 
  UPDATE TOP (1) dbo.AccountDetails WITH (UPDLOCK, SERIALIZABLE)
     SET Etc = @Etc
   WHERE Email = @Email;
 
  IF (@@ROWCOUNT = 0)
  BEGIN      
      INSERT dbo.AccountDetails ( Email, Etc )
      VALUES ( @Email, @Etc );
  END 
COMMIT

2. 事务内部的锁提示（插入更常见的）

CREATE PROCEDURE s_AccountDetails_Upsert ( @Email nvarchar(4000), @Etc nvarchar(max) )
AS 
SET XACT_ABORT ON;
BEGIN TRAN
 
  INSERT dbo.AccountDetails ( Email, Etc )
  SELECT @Email, @Etc
  WHERE NOT EXISTS (
      SELECT *
      FROM dbo.AccountDetails WITH (UPDLOCK, SERIALIZABLE)
      WHERE Email = @Email
  )
 
  IF (@@ROWCOUNT = 0)
  BEGIN      
      UPDATE TOP (1) dbo.AccountDetails
      SET Etc = @Etc
      WHERE Email = @Email;
  END 
COMMIT 

我可以调整其中任何一个以使用表变量（例如，我怀疑IF (@@ROWCOUNT = 0)需要完全删除表变量），但是使用表值输入是否明显表明我们应该选择第一个或第二个解决方案？如果不是，那么应该基于什么做出决定？

我有点像‘偶然的 DBA’，所以我可能完全偏离了这里。

我们是本地部署，我们正在讨论我们的测试环境。

我们正计划更换托管公司。

我们将把我们的 AG 转移到新的托管公司

如果新托管公司出现问题，我们希望能够以某种方式快速切换回旧的托管公司。

我们有很多时间表，因此无法通过事务日志进行正常复制。

虽然这是一个测试环境，但有很多测试活动，如果新的托管公司失败，我们不想失去这些活动。（我们可以备份和恢复，但我正在寻找更快的解决方案）

日志传输可以解决这个问题吗？或者您建议其他方法吗？

问候

介绍

我读过很多关于从数据库中删除数据以及使用 shrink 命令的风险的文章。但是，我有机会将数据库迁移到一台停机时间较长的新服务器，因此我打算重置数据库并缩减其大小（以提高性能并节省托管环境中的成本）。

背景

• 现有的SQL Server是2016Web版。
• 新的 SQL Server 2022 标准版。
• 数据库大小~120GB。
• 一些大表以及这些大表上的索引构成了其中的大部分。
• 我只想专注于数据库大小的缩减。对于创建新服务器的其他部分，我感觉还算满意。

拟议流程

停电前

1. 运行使用语法的存储过程DELETE FROM。我已经仔细测试过了。它们会批量删除，并且会在几天内运行。
2. 监视日志文件和日志磁盘以仔细检查是否有足够的空间。
3. 确保在到达停机时间时删除处理已经完成。

停电期间

4. 对数据库进行完整备份并放在安全的地方。
5. 删除最大的索引（我将针对大约 3 或 4 个）。
6. 将数据库置于简单恢复模式。
7. 尽可能缩小日志和数据库文件。
8. 备份数据库。
9. 将备份文件复制到新服务器。
10. 将数据库还原到正确的文件位置
11. 应用已删除的索引。
12. 重建所有索引。
13. 更新统计数据。
14. 将数据库恢复到完整恢复模式。
15. 测试和重启应用程序

首先，由于我正在使用。如果事务块中SET XACT_ABORT ON的任何INSERT或语句发生异常，则它应该终止并回滚整个事务。这意味着，如果中的任何和语句出现错误。那么它应该自动回滚该事务。UPDATEINSERTUPDATEBEGIN TRAN

此外，我正在使用 SQL Server 2017。在我的脚本中，我初始化了一个表变量DECLARE @cust_debugger TABLE (exec_checker VARCHAR(50));，并且由于我的 SQL Server Management Studio IDE 中缺少内置调试功能，因此它在我的脚本中临时实现以调试我的存储过程。

这是我的完整 T-SQL 脚本：

SET XACT_ABORT ON; -- To automatically rollback the entire transaction if an exception is encountered
                    
BEGIN TRAN;

DECLARE @cust_debugger TABLE (exec_checker VARCHAR(50)); -- custom debugger

SELECT TOP 1
    @d_pay_method_id = pay_method_id
FROM
    [testDB].[dbo].[Payment]
WHERE
    method_name LIKE '%Bank Transfer%';
            
SELECT TOP 1
    @d_stat_ref_id = stat_ref_id
FROM
    [testDB].[dbo].[StatusRef]
WHERE
    com_usd_wrds = 'PENDING';

SET @d_batch_code = 'BGUID' + '-' + CONVERT(VARCHAR,GETDATE(),23)
    + '-' + SUBSTRING(CONVERT(VARCHAR(12),REPLACE(CONVERT(VARCHAR(64), NEWID()), '-', '')), 1, 13);--DATETIME2(4)-&-12-RANDOM-CODE

INSERT INTO -- first batch
    [testDB].[dbo].[Orders]
(
    order_id,
    batch_code,
    quantity,
    cart_id,
    user_id,
    pay_method_id,
    stat_ref_id
)
OUTPUT
    INSERTED.order_id,
    INSERTED.cart_id
INTO
    @order_id_tbl
SELECT
    'ORDER' + '-' + CONVERT(VARCHAR,GETDATE(),23)
        + '-' + SUBSTRING(CONVERT(VARCHAR(12),REPLACE(CONVERT(VARCHAR(64), NEWID()), '-', '')), 1, 13) AS order_id,--DATE-&-12-RANDOM-CODE
    @d_batch_code AS batch_code,
    cart.quantity,
    cart.cart_id,
    @user_id AS user_id,
    @d_pay_method_id AS pay_method_id,
    @d_stat_ref_id AS stat_ref_id
FROM
    [testDB].[dbo].[PinkBasket] AS cart
LEFT OUTER JOIN
    [testDB].[dbo].[StatusRef] AS stat_ref
ON
    cart.stat_ref_id = stat_ref.stat_ref_id
WHERE
    cart.user_id = @user_id
    AND cart.disable = 0
    AND cart.is_selected = 1
    AND cart.is_purchased = 0
    AND cart.is_wishlisted = 0
    AND stat_ref.com_usd_wrds = 'PENDING';

IF @@ROWCOUNT > 0
BEGIN

    SET @place_order_fbatch_cntr += 1;

    INSERT INTO @cust_debugger VALUES ('first set');

END

INSERT INTO -- first batch
    [testDB].[dbo].[Payment]
(
    cnb_pay_id,
    pay_ref_no,
    amount_to_pay,
    order_id
)
SELECT
    'PGUID' + '-' + REPLACE(REPLACE(REPLACE(CAST(sysdatetime() AS DATETIME2(4)), ' ', '-'), ':', '-'), '.', '-')
        + '-' + SUBSTRING(CONVERT(VARCHAR(12),REPLACE(CONVERT(VARCHAR(64), NEWID()), '-', '')),1,13), --DATETIME2(4)-&-12-RANDOM-CODE
    @pay_ref_no,
    @amount_to_pay,
    tempData.order_id
FROM
    @order_id_tbl AS tempData;

IF @@ROWCOUNT > 0
BEGIN

    SET @place_order_fbatch_cntr += 1;

    INSERT INTO @cust_debugger VALUES ('second set');

END

SELECT TOP 1
    @d_stat_ref_id = stat_ref_id
FROM
    [testDB].[dbo].[StatusRef]
WHERE
    com_usd_wrds = 'ORDERED';

UPDATE -- first batch
    [testDB].[dbo].[PinkBasket]
SET
    stat_ref_id = @d_stat_ref_id,
    is_purchased = 1
WHERE
    cart_id IN
        (
            SELECT
                tempData.cart_id
            FROM
                @order_id_tbl AS tempData
        );

IF @@ROWCOUNT > 0
BEGIN

    SET @place_order_fbatch_cntr += 1;

    INSERT INTO @cust_debugger VALUES ('third set');

END

UPDATE
    prod
SET
    prod.stock = CASE WHEN (prod.stock - nested_ref.quantity) <= 0 THEN prod.stock ELSE prod.stock - nested_ref.quantity END,
    prod.availability = CASE WHEN (prod.stock - nested_ref.quantity) <= 0 THEN 0 ELSE 1 END
FROM
    [testDB].[dbo].[Product] AS prod
INNER JOIN
(
    SELECT
        cart.prod_id,
        cart.quantity
    FROM
        [testDB].[dbo].[PinkBasket] AS cart
    LEFT OUTER JOIN
        [testDB].[dbo].[StatusRef] AS stat_ref
    ON
        cart.stat_ref_id = stat_ref.stat_ref_id                     
    WHERE
        cart.user_id = @user_id
        AND cart.prod_id IS NOT NULL
        AND cart.disable = 0
        AND cart.is_selected = 1
        AND cart.is_purchased = 0
        AND cart.is_wishlisted = 0
        AND stat_ref.com_usd_wrds = 'PENDING'
) AS nested_ref
ON
    prod.prod_id = nested_ref.prod_id
WHERE
    prod.disable = 0
    AND prod.availability = 1
    AND prod.is_draft = 0;

IF @@ROWCOUNT > 0
BEGIN

    SET @place_order_sbatch_cntr += 1;

    INSERT INTO @cust_debugger VALUES ('fourth set');

END

UPDATE
    prod_var
SET
    prod_var.stock = CASE WHEN (prod_var.stock - nested_ref.quantity) <= 0 THEN prod_var.stock ELSE prod_var.stock - nested_ref.quantity END,
    prod_var.availability = CASE WHEN (prod_var.stock - nested_ref.quantity) <= 0 THEN 0 ELSE 1 END
FROM
    [testDB].[dbo].[SubProduct] AS prod_var
INNER JOIN
(
    SELECT
        cart.prod_var_id,
        cart.quantity
    FROM
        [testDB].[dbo].[PinkBasket] AS cart
    LEFT OUTER JOIN
        [testDB].[dbo].[StatusRef] AS stat_ref
    ON
        cart.stat_ref_id = stat_ref.stat_ref_id                     
    WHERE
        cart.user_id = @user_id
        AND cart.prod_var_id IS NOT NULL
        AND cart.disable = 0
        AND cart.is_selected = 1
        AND cart.is_purchased = 0
        AND cart.is_wishlisted = 0
        AND stat_ref.com_usd_wrds = 'PENDING'
) AS nested_ref
ON
    prod_var.prod_var_id = nested_ref.prod_var_id
WHERE
    prod_var.disable = 0
    AND prod_var.availability = 1
    AND prod_var.is_reserved = 1;

IF @@ROWCOUNT > 0
BEGIN

    SET @place_order_sbatch_cntr += 1;

    INSERT INTO @cust_debugger VALUES ('fifth set');

END

IF (@place_order_fbatch_cntr + @place_order_sbatch_cntr) >= 4 --@place_order_fbatch_cntr should be 3, and @place_order_sbatch_cntr should be greater than or equal to 1

BEGIN

    COMMIT TRAN;

    SELECT @successful AS json_data;

END

ELSE

BEGIN

    ROLLBACK TRAN;

    SELECT(SELECT exec_checker FROM @cust_debugger FOR JSON PATH, INCLUDE_NULL_VALUES) AS json_data; -- debugging purposes only

END

每次运行存储过程时，我都会收到此输出（在我的 Express.js API 服务中）：

spOutput = "[{\"exec_checker\":\"first set\"},{\"exec_checker\":\"second set\"},{\"exec_checker\":\"third set\"}]"

您会注意到表变量@cust_debugger 成功获得了前三个IF @@ROWCOUNT > 0验证部分（即混合了两个INSERT和一条UPDATE语句）。

问题是，为什么最后两段代码执行不正常？但是，由于我SET XACT_ABORT ON之前声明了BEGIN TRAN，所以交易过程中不会出现任何异常。

但是，如果我提取第四组和第五组的部分并排除BEGIN TRAN和的实例SET XACT_ABORT ON（出于测试和调查目的），则脚本如下：

DECLARE @cust_debugger TABLE (exec_checker VARCHAR(50)); -- custom debugger

UPDATE
    prod
SET
    prod.stock = CASE WHEN (prod.stock - nested_ref.quantity) <= 0 THEN prod.stock ELSE prod.stock - nested_ref.quantity END,
    prod.availability = CASE WHEN (prod.stock - nested_ref.quantity) <= 0 THEN 0 ELSE 1 END
FROM
    [testDB].[dbo].[Product] AS prod
INNER JOIN
(
    SELECT
        cart.prod_id,
        cart.quantity
    FROM
        [testDB].[dbo].[PinkBasket] AS cart
    LEFT OUTER JOIN
        [testDB].[dbo].[StatusRef] AS stat_ref
    ON
        cart.stat_ref_id = stat_ref.stat_ref_id                     
    WHERE
        cart.user_id = @user_id
        AND cart.prod_id IS NOT NULL
        AND cart.disable = 0
        AND cart.is_selected = 1
        AND cart.is_purchased = 0
        AND cart.is_wishlisted = 0
        AND stat_ref.com_usd_wrds = 'PENDING'
) AS nested_ref
ON
    prod.prod_id = nested_ref.prod_id
WHERE
    prod.disable = 0
    AND prod.availability = 1
    AND prod.is_draft = 0;

IF @@ROWCOUNT > 0
BEGIN

    INSERT INTO @cust_debugger VALUES ('fourth set');

END

UPDATE
    prod_var
SET
    prod_var.stock = CASE WHEN (prod_var.stock - nested_ref.quantity) <= 0 THEN prod_var.stock ELSE prod_var.stock - nested_ref.quantity END,
    prod_var.availability = CASE WHEN (prod_var.stock - nested_ref.quantity) <= 0 THEN 0 ELSE 1 END
FROM
    [testDB].[dbo].[SubProduct] AS prod_var
INNER JOIN
(
    SELECT
        cart.prod_var_id,
        cart.quantity
    FROM
        [testDB].[dbo].[PinkBasket] AS cart
    LEFT OUTER JOIN
        [testDB].[dbo].[StatusRef] AS stat_ref
    ON
        cart.stat_ref_id = stat_ref.stat_ref_id                     
    WHERE
        cart.user_id = @user_id
        AND cart.prod_var_id IS NOT NULL
        AND cart.disable = 0
        AND cart.is_selected = 1
        AND cart.is_purchased = 0
        AND cart.is_wishlisted = 0
        AND stat_ref.com_usd_wrds = 'PENDING'
) AS nested_ref
ON
    prod_var.prod_var_id = nested_ref.prod_var_id
WHERE
    prod_var.disable = 0
    AND prod_var.availability = 1
    AND prod_var.is_reserved = 1;

IF @@ROWCOUNT > 0
BEGIN

    INSERT INTO @cust_debugger VALUES ('fifth set');

END

SELECT * FROM @cust_debugger

正在生成以下输出：

BEGIN TRAN我的问题是，为什么第四组和第五组只有在没有、COMMIT TRAN和的情况下执行才能正确运行ROLLBACK TRAN？

如果最后两组工作正常（如果我提取第四组和第五组的部分并排除BEGIN TRAN和的实例SET XACT_ABORT ON），那么它应该执行该IF @@ROWCOUNT > 0语句。（即，变量也应该增加：）SET @place_order_sbatch_cntr += 1;。

另一方面，我发现Product和SubProduct表定义了TRIGGER，下面是以下TRIGGER函数：

产品触发：

ALTER TRIGGER [dbo].[ProductReferentialIntegrityInsteadOfDelete]
   ON  [dbo].[Product]
   INSTEAD OF DELETE
AS 
BEGIN

    SET NOCOUNT ON;

    -- Insert statements for trigger here
    IF EXISTS (
        SELECT
            1
        FROM
            testImgDB.dbo.ProductImg prodImg
        JOIN
            deleted d
        ON
            prodImg.prod_id = d.prod_id
    )
    BEGIN
        RAISERROR ('Cannot delete, as related records exist in testImgDB.dbo.ProductImg.', 16, 1);
        ROLLBACK;
    END
    ELSE
    BEGIN
        DELETE FROM
            testDB.dbo.Product
        WHERE
            prod_id IN (SELECT prod_id FROM deleted);
    END
END

子产品触发器：

ALTER TRIGGER [dbo].[SubProductReferentialIntegrityInsteadOfDelete]
   ON  [dbo].[SubProduct]
   INSTEAD OF DELETE
AS 
BEGIN

    SET NOCOUNT ON;

    -- Insert statements for trigger here
    IF EXISTS (
        SELECT
            1
        FROM
            testImgDB.dbo.SubProductImg prodImg
        JOIN
            deleted d
        ON
            prodImg.prod_var_id = d.prod_var_id
    )
    BEGIN
        RAISERROR ('Cannot delete, as related records exist in testImgDB.dbo.SubProductImg.', 16, 1);
        ROLLBACK;
    END
    ELSE
    BEGIN
        DELETE FROM
            testDB.dbo.SubProduct
        WHERE
            prod_var_id IN (SELECT prod_var_id FROM deleted);
    END
END

但令人怀疑的是，为什么

如果最后两组工作正常（如果我提取第四组和第五组的部分并排除BEGIN TRAN 和的实例SET XACT_ABORT ON）

尽管禁用了两个触发器。

并重新测试，问题仍然存在。

以下是我为解决我的问题所考虑的内容：

• 建议将复杂的读取（SELECT）过程分离到最后两部分吗？我的意思是，我需要将 SELECT 数据传入临时表或表变量。这样，由于关注点分离的概念，存储的数据最终将通过复杂度较低的 UPDATE 操作进行处理。

• I want to know how to review the processing of my script where the issue arises. Such as if I could review why the UPDATE process doesn't update, then I could also decide what measures of response I needed to take.

• How to check for Locks / Deadlocks / Blocking, hoping that it might cause the unexpected outcomes.

Here are my assumptions that might be causing the conflicts:

• For the Product and SubProduct tables, there are triggers in each of the two tables. But it is defined with INSTEAD OF DELETE. Could it affect the process? Ahhh, because UPDATE statements are also deleting a record, right? The first thing it does is DELETE, and the second is INSERT. Because that's how UPDATE works under the hood of SQL Server, right? I forgot to look at it; it seems it was because of the TRIGGER.

• According to what I've observed. When you create a TRIGGER. You can use the INSERTED table and the DELETED table (if it's handling an UPDATE statement). When you UPDATE a table, it will delete the record first, then it will be stored in the DELETED table, then the DELETED table will pass the data into the INSERTED table and use the argument that is passed to the UPDATE statement instead. Take note, I'm not sure how SQL Server processes the DELETED table and INSERTED table when the UPDATE triggers.

• Since I am using a test server. Here's the screenshot of the test server specifications:

• Is 8GB RAM and 178 GB of storage insufficient for testing purposes?

Does anyone know why this is happening?

假设以下数据库中有两个表，它们之间存在外键关系：

CREATE DATABASE FKLocksTest
GO
ALTER DATABASE FKLocksTest SET READ_COMMITTED_SNAPSHOT OFF

USE FkLocksTest
GO

CREATE TABLE dbo.Department
(
    DeptID INT PRIMARY KEY,
    Deptname NVARCHAR(10)
)
INSERT INTO dbo.Department VALUES (1,'IT'),(2,'HR')


CREATE TABLE dbo.Person
(
    PersonID INT PRIMARY KEY,
    PersonName NVARCHAR(10),
    DepartmentId INT FOREIGN KEY REFERENCES dbo.Department(DeptId)
)
INSERT INTO dbo.Person VALUES (1,'JohnSmith',2) 

如果我运行一些更新，我可以使用跟踪标志 1200 来验证哪些锁被占用。首先，我们可以验证对象 ID：

SELECT  OBJECT_ID('dbo.Department') AS Department,
        OBJECT_ID('dbo.Person') AS Person

现在我启用 TF1200 并运行一些更新，我可以在消息选项卡中看到已获取的锁。我还会确保使用已提交读：

DBCC TRACEON(1200,-1)

SET TRANSACTION ISOLATION LEVEL READ COMMITTED

并运行我的第一次更新

UPDATE dbo.Person SET PersonName = 'Jim'

该计划没有提及部门，并且 Traceflag 输出没有提及对象 ID 581577110，这是有道理的

接下来，如果我跑

UPDATE dbo.Person SET DepartmentId = 1 WHERE PersonID = 1

在这种情况下，执行计划引用了部门表，并且 Traceflag 输出显示在 581577110 上进行了锁定。这也是有道理的，因为 SQL Server 必须检查我们设置的 DepartmentId 是否存在于 Department 表中

这次，如果我更新父表并更新主键列

UPDATE dbo.Department SET DeptID = 10 WHERE DeptID = 1

执行计划显示了对 Person 表的引用，并且我可以看到在 613577224 上被锁定。这是合理的，因为 SQL Server 需要检查是否存在与我们正在更新的旧 DepartmentID 关联的 Person 记录。我还遇到了错误，因为有一个 Person 的 DeptId 为 1。

最后，如果我跑

UPDATE dbo.Department SET Deptname = 'aaa' WHERE DeptID = 1

我可以在 Traceflag 输出中看到对 person 表 (613577224) 进行了锁定，但计划中没有对 person 表的引用

TF1200 的简要输出显示了 Person 表上的锁

    ......

Process 59 acquiring Sch-S lock on OBJECT: 9:613577224:0  (class bit0 ref1) result: OK

......

Process 59 acquiring Sch-S lock on METADATA: database_id = 9 INDEXSTATS(object_id = 613577224, index_id or stats_id = 1), lockPartitionId = 0 (class bit0 ref1) result: OK

Process 59 acquiring Sch-S lock on METADATA: database_id = 9 INDEXSTATS(object_id = 613577224, index_id or stats_id = 1), lockPartitionId = 0 (class bit0 ref1) result: OK

Process 59 releasing lock reference on METADATA: database_id = 9 INDEXSTATS(object_id = 613577224, index_id or stats_id = 1), lockPartitionId = 0

Process 59 acquiring Sch-S lock on METADATA: database_id = 9 INDEXSTATS(object_id = 613577224, index_id or stats_id = 1), lockPartitionId = 0 (class bit0 ref1) result: OK

Process 59 releasing lock reference on METADATA: database_id = 9 INDEXSTATS(object_id = 613577224, index_id or stats_id = 1), lockPartitionId = 0

Process 59 acquiring Sch-S lock on METADATA: database_id = 9 INDEXSTATS(object_id = 613577224, index_id or stats_id = 1), lockPartitionId = 0 (class bit0 ref1) result: OK

Process 59 releasing lock reference on METADATA: database_id = 9 INDEXSTATS(object_id = 613577224, index_id or stats_id = 1), lockPartitionId = 0

Process 59 acquiring Sch-S lock on METADATA: database_id = 9 INDEXSTATS(object_id = 613577224, index_id or stats_id = 1), lockPartitionId = 0 (class bit0 ref1) result: OK

Process 59 releasing lock reference on METADATA: database_id = 9 INDEXSTATS(object_id = 613577224, index_id or stats_id = 1), lockPartitionId = 0

Process 59 acquiring Sch-S lock on METADATA: database_id = 9 INDEXSTATS(object_id = 613577224, index_id or stats_id = 1), lockPartitionId = 0 (class bit0 ref1) result: OK

Process 59 releasing lock reference on METADATA: database_id = 9 INDEXSTATS(object_id = 613577224, index_id or stats_id = 1), lockPartitionId = 0

......

Process 59 acquiring Sch-S lock on METADATA: database_id = 9 INDEXSTATS(object_id = 613577224, index_id or stats_id = 1), lockPartitionId = 0 (class bit0 ref1) result: OK

Process 59 releasing lock reference on METADATA: database_id = 9 INDEXSTATS(object_id = 613577224, index_id or stats_id = 1), lockPartitionId = 0

Process 59 acquiring Sch-S lock on METADATA: database_id = 9 INDEXSTATS(object_id = 613577224, index_id or stats_id = 1), lockPartitionId = 0 (class bit0 ref1) result: OK

Process 59 acquiring Sch-S lock on METADATA: database_id = 9 STATS(object_id = 613577224, stats_id = 1), lockPartitionId = 0 (class bit0 ref1) result: OK

Process 59 acquiring Sch-S lock on METADATA: database_id = 9 INDEXSTATS(object_id = 613577224, index_id or stats_id = 1), lockPartitionId = 0 (class bit0 ref1) result: OK

Process 59 acquiring Sch-S lock on METADATA: database_id = 9 STATS(object_id = 613577224, stats_id = 1), lockPartitionId = 0 (class bit0 ref1) result: OK

Process 59 releasing lock reference on METADATA: database_id = 9 STATS(object_id = 613577224, stats_id = 1), lockPartitionId = 0

Process 59 releasing lock reference on METADATA: database_id = 9 INDEXSTATS(object_id = 613577224, index_id or stats_id = 1), lockPartitionId = 0

Process 59 acquiring X lock on OBJECT: 9:613577224:0 [UPDSTATS] (class bit0 ref1) result: OK

Process 59 acquiring Sch-S lock on METADATA: database_id = 9 INDEXSTATS(object_id = 613577224, index_id or stats_id = 1), lockPartitionId = 0 (class bit0 ref1) result: OK

Process 59 acquiring Sch-S lock on METADATA: database_id = 9 STATS(object_id = 613577224, stats_id = 1), lockPartitionId = 0 (class bit0 ref1) result: OK

Process 59 releasing lock reference on METADATA: database_id = 9 STATS(object_id = 613577224, stats_id = 1), lockPartitionId = 0

Process 59 releasing lock reference on METADATA: database_id = 9 INDEXSTATS(object_id = 613577224, index_id or stats_id = 1), lockPartitionId = 0

Process 59 releasing lock on OBJECT: 9:613577224:0 [UPDSTATS]

Process 59 acquiring Sch-S lock on METADATA: database_id = 9 INDEXSTATS(object_id = 613577224, index_id or stats_id = 1), lockPartitionId = 0 (class bit0 ref1) result: OK

Process 59 acquiring Sch-S lock on METADATA: database_id = 9 STATS(object_id = 613577224, stats_id = 1), lockPartitionId = 0 (class bit0 ref1) result: OK

Process 59 releasing lock reference on METADATA: database_id = 9 STATS(object_id = 613577224, stats_id = 1), lockPartitionId = 0

Process 59 releasing lock reference on METADATA: database_id = 9 INDEXSTATS(object_id = 613577224, index_id or stats_id = 1), lockPartitionId = 0

Process 59 acquiring Sch-S lock on METADATA: database_id = 9 INDEXSTATS(object_id = 613577224, index_id or stats_id = 2), lockPartitionId = 0 (class bit0 ref1) result: OK

Process 59 acquiring Sch-S lock on METADATA: database_id = 9 STATS(object_id = 613577224, stats_id = 2), lockPartitionId = 0 (class bit0 ref1) result: OK

Process 59 acquiring Sch-S lock on METADATA: database_id = 9 INDEXSTATS(object_id = 613577224, index_id or stats_id = 2), lockPartitionId = 0 (class bit0 ref1) result: OK

Process 59 acquiring Sch-S lock on METADATA: database_id = 9 STATS(object_id = 613577224, stats_id = 2), lockPartitionId = 0 (class bit0 ref1) result: OK

Process 59 releasing lock reference on METADATA: database_id = 9 STATS(object_id = 613577224, stats_id = 2), lockPartitionId = 0

Process 59 releasing lock reference on METADATA: database_id = 9 INDEXSTATS(object_id = 613577224, index_id or stats_id = 2), lockPartitionId = 0

......

Process 59 releasing lock on METADATA: database_id = 9 INDEXSTATS(object_id = 613577224, index_id or stats_id = 2), lockPartitionId = 0

Process 59 releasing lock on METADATA: database_id = 9 STATS(object_id = 613577224, stats_id = 2), lockPartitionId = 0

Process 59 acquiring Sch-S lock on METADATA: database_id = 9 INDEXSTATS(object_id = 613577224, index_id or stats_id = 1), lockPartitionId = 0 (class bit0 ref1) result: OK

Process 59 acquiring Sch-S lock on METADATA: database_id = 9 STATS(object_id = 613577224, stats_id = 1), lockPartitionId = 0 (class bit0 ref1) result: OK

Process 59 releasing lock reference on METADATA: database_id = 9 STATS(object_id = 613577224, stats_id = 1), lockPartitionId = 0

Process 59 releasing lock reference on METADATA: database_id = 9 INDEXSTATS(object_id = 613577224, index_id or stats_id = 1), lockPartitionId = 0

Process 59 acquiring Sch-S lock on METADATA: database_id = 9 INDEXSTATS(object_id = 613577224, index_id or stats_id = 2), lockPartitionId = 0 (class bit0 ref1) result: OK

Process 59 acquiring Sch-S lock on METADATA: database_id = 9 STATS(object_id = 613577224, stats_id = 2), lockPartitionId = 0 (class bit0 ref1) result: OK

Process 59 releasing lock on METADATA: database_id = 9 INDEXSTATS(object_id = 613577224, index_id or stats_id = 2), lockPartitionId = 0

Process 59 releasing lock on METADATA: database_id = 9 STATS(object_id = 613577224, stats_id = 2), lockPartitionId = 0

Process 59 releasing lock reference on METADATA: database_id = 9 INDEXSTATS(object_id = 613577224, index_id or stats_id = 1), lockPartitionId = 0

Process 59 releasing lock on METADATA: database_id = 9 STATS(object_id = 613577224, stats_id = 1), lockPartitionId = 0

Process 59 releasing lock on METADATA: database_id = 9 INDEXSTATS(object_id = 613577224, index_id or stats_id = 1), lockPartitionId = 0

......

Process 59 releasing lock on OBJECT: 9:613577224:0 

......

Process 59 acquiring IS lock on OBJECT: 9:613577224:0  (class bit0 ref1) result: OK

Process 59 acquiring Sch-S lock on METADATA: database_id = 9 INDEXSTATS(object_id = 613577224, index_id or stats_id = 2), lockPartitionId = 0 (class bit0 ref1) result: OK

Process 59 acquiring Sch-S lock on METADATA: database_id = 9 STATS(object_id = 613577224, stats_id = 2), lockPartitionId = 0 (class bit0 ref1) result: OK

Process 59 releasing lock on METADATA: database_id = 9 INDEXSTATS(object_id = 613577224, index_id or stats_id = 2), lockPartitionId = 0

Process 59 releasing lock on METADATA: database_id = 9 STATS(object_id = 613577224, stats_id = 2), lockPartitionId = 0

......

Msg 547, Level 16, State 0, Line 1
The UPDATE statement conflicted with the REFERENCE constraint "FK__Person__Departme__267ABA7A". The conflict occurred in database "FKLocksTest", table "dbo.Person", column 'DepartmentId'.
The statement has been terminated.

Person 表上的锁可能看起来是编译过程的一部分，如果我多次运行 UPDATE，我最终会得到一个更短的锁列表（下面是完整的），尽管这个计划很简单

Process 59 acquiring IX lock on OBJECT: 9:581577110:0  (class bit2000000 ref1) result: OK

Process 59 acquiring IX lock on PAGE: 9:1:280  (class bit2000000 ref1) result: OK

Process 59 acquiring X lock on KEY: 9:72057594043170816 (8194443284a0) (class bit2000000 ref1) result: OK

Process 59 releasing lock reference on KEY: 9:72057594043170816 (8194443284a0)

Process 59 releasing lock reference on PAGE: 9:1:280 

我的问题是，当我们更新 Department 表中的非关键列时：

• 为什么需要对 Person 表进行锁定
• 为什么只在编译时才锁定

我有两个表存储类似的数据但不同的时间段：

MainTable：包含当前年份的记录。

ArchiveTable：包含前几年的记录。

两个表都有一个名为 DateColumn（类型为 DATETIME）的列。我需要编写一个 T‑SQL 查询（或一组查询），该查询基于两个输入参数 @StartDate 和 @EndDate 执行如下操作：

仅限当前年份：如果两个日期均在当前年份内，则仅从 MainTable 中获取数据。

仅限前几年：如果两个日期都在当前年份之前，则仅从 ArchiveTable 中获取数据。

混合范围：如果日期范围跨越前几年和当前年份，则从两个表中获取数据。

例如，如果当前年份是 2025 年：

@StartDate = '2025-03-01' 和 @EndDate = '2025-03-31' 应该仅返回来自 MainTable 的行。

@StartDate = '2024-11-01' 和 @EndDate = '2024-12-31' 应该仅返回来自 ArchiveTable 的行。

@StartDate = '2024-12-15' 和 @EndDate = '2025-01-15' 应该返回两个表的行（在边界处分割）。

我曾考虑过使用 IF...ELSE 逻辑来决定查询哪个表，但我正在寻找有关实现此操作的最佳或最优雅方法的建议（也许在单个查询中，使用 UNION ALL 或动态 SQL）。

实现此逻辑的最佳实践或替代方法是什么？ 可以通过单个查询或其他可能简化维护的技术来实现吗？