Cade Roux's questions

背景：

我有多个源系统产品，每个产品都在自己的数据库中，彼此之间的依赖性非常有限，而且相当模块化，也有点可选。它们在维护时可能处于不同的版本级别。

我有一个单独的数据收集系统数据库，它从其他系统收集使用信息，我希望它继续与其他系统保持大致的前向和后向兼容。该系统完全是数据库内部的 - 计划作业、进程等。没有外部 Windows 程序、服务等。

理想情况下，源系统将公开一致的接口，并以面向未来的方式管理它们自己公开的内容，我确实会尽可能地利用这一点。但其他产品的路线图总是能够容纳例外情况。我希望新版本的数据收集系统无论源系统中是否存在表（或多个表）都能正常工作，并且如果不适合使用，只需注意或跳过它或根据需要记录它。如果该系统稍后升级，我不希望必须接触数据收集系统。因此，任何过程都不应被更改或重新创建。

我们通常对每个源系统使用同义词，以便在尽可能少的地方处理数据库重命名（理想情况下仅在同义词中）。

我做了一些实验，似乎您可以CREATE SYNONYM对尚不存在的表/视图进行操作而不会失败。您也可以CREATE PROC使用这些同义词而不会失败。因此，可以在有和没有源数据库的系统上以相同的方式安装系统，并且应该能够在安装（或删除）该模块的数据库时不加修改地运行。

我目前只是在寻找一种方法来做以下两件事 - 知道同义词指向某个东西，并检查那个东西并确保它满足所有要求：

1. 可靠地了解同义词是否有效。当此处的第一列为 NULL 时，是否足以知道它无效？

SELECT object_id(base_object_name), * FROM sys.synonyms

2. 如果同义词有效，是否有办法获取元数据来检查它？可以从sys.columns（或INFORMATION_SCHEMA）获取列、数据类型，但需要使用已知的数据库名称进行限定 - 下面的答案首先选择临时表，然后查看临时表的模式。有没有更好的选择？

https://stackoverflow.com/a/27126581/18255

我有一个从 Microsoft.MasterDataServices.DataQuality 在大约 1000 行左右调用 XmlTransform 的查询，并且需要 30 秒来完成整个设置（XSL 在一个固定变量中），有没有办法查看在 XmlTransform 中花费的实际时间是否在执行计划中？

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

这个特定的环境是 SQL Server 2012

我想检测可用的 SQL Server Express 引擎版本，以便我可以连接到 (localdb)\v.11（SQL Server 2012 per https://learn.microsoft.com/en-us/previous-版本/sql/sql-server-2012/hh510202(v=sql.110)?redirectedfrom=MSDN#Anchor_1 ) 或 (localdb)\MSSQLLocalDB （SQL Server 2014 及更高版本，每https://learn.microsoft.com/en- us/sql/database-engine/configure-windows/sql-server-express-localdb?view=sql-server-ver15&redirectedfrom=MSDN&viewFallbackFrom=sql-server-2014#Anchor_1 ) 尝试在连接字符串中附加文件时的实例名称使用 AttachDBFileName= 机制。

我很可能想以某种方式从 Powershell 执行此操作，但无论哪种方法可靠，我都可以使用。我确实知道，有时 LocalDB 连接可能会有点慢，因为它附加文件并按需启动，因此与真正的 SQL Server 连接相比，我过去对这些 LocalDB 连接的连接超时非常宽容，所以我宁愿不尝试连接并等待超时，因为我认为我已经有一个夸大的超时只是为了正常的成功连接。

SQL Server 2012 中是否有办法获取特定日期/时间的服务器时区？在 SQL Server 2016 中，您有AT TIME ZONE，但在 2012 年不可用。我有一个datetimeoffset，我需要将其转换为服务器的时区（在该日期），然后再将其截断为datetime. SWITCHOFFSET仅当您知道偏移量时才有效，但对于每年更改两次时区的服务器，偏移量将来回翻转。

此查询说明了 SQL Server 2016 中的功能：

SELECT @@SERVERNAME AS [SERVERNAME]
    , GETDATE() AT TIME ZONE 'Central Standard Time' AS [GETDATE() AT TIME ZONE 'Central Standard Time']
    , GETDATE() AT TIME ZONE 'Eastern Standard Time' AS [GETDATE() AT TIME ZONE 'Eastern Standard Time']
    , DATEADD(MONTH, -6, GETDATE()) AT TIME ZONE 'Central Standard Time' AS [DATEADD(MONTH, -6, GETDATE()) AT TIME ZONE 'Central Standard Time']
    , DATEADD(MONTH, -6, GETDATE()) AT TIME ZONE 'Eastern Standard Time' AS [DATEADD(MONTH, -6, GETDATE()) AT TIME ZONE 'Eastern Standard Time']
;

至少在 6 月，前两列中的 tzoffset 与后两列不同，因为“标准”时区在 12 月的偏移量与 6 月不同。

我有一个关于这个小提琴的问题：

DECLARE @TestVal AS float = 8.88;
SELECT flt = @TestVal
    , xml = (SELECT Value = @TestVal FOR XML PATH(''), TYPE)
    , fmt17 = FORMAT(@TestVal, 'G17')
    , fmt = FORMAT(@TestVal, 'G')
    , cst = CAST(@TestVal AS nvarchar(50))
    , fmt17_roundtrip = CAST(FORMAT(@TestVal, 'G17') AS float)
    , fmt_roundtrip = CAST(FORMAT(@TestVal, 'G') AS float)
    , cst_roundtrip = CAST(CAST(@TestVal AS nvarchar(50)) AS float)
;

https://dbfiddle.uk?rdbms=sqlserver_2019&fiddle=0cf05f882eb24f53e9484f043af99446

我遇到了一些默认情况下以科学记数法输出的 XML 的问题，虽然它不是不正确或不准确的，但可读性不是很好。

我最初使用 FORMAT(floatcol, 'G17') 是因为此文档页面上的评论：

请注意，当与 Double 值一起使用时，“G17”格式说明符可确保原始 Double 值成功往返。这是因为 Double 是符合 IEEE 754-2008 的双精度 (binary64) 浮点数，最多可提供 17 位有效数字的精度。我们建议使用它而不是“R”格式说明符，因为在某些情况下，“R”无法成功地往返双精度浮点值。以下示例说明了一种此类情况。

好吧，今天我发现它似乎在字符串中添加了一些额外的无关紧要的数字。在这个特殊的例子中，它们都“往返”很好，但 G17 格式有一个额外的无关紧要的数字。

尽管它可能不会影响我的往返旅行，但我真的不想将其发送给具有额外数​​字的另一方。

现在我倾向于更改为 FORMAT('G')，但不确定它的含义。目前，这些 XML 导出中使用的格式字符串是存储在我的系统中的配置设置，因此继续使用 FORMAT 是最简单的，因为它不需要更改代码。

因此，在我对各种其他值进行测试之前，我的问题是 G 和 G17 总体上有什么区别，使用 G 而不是 G17 可能会遇到什么问题？

（是的，这个值需要是浮点数，而不是小数或整数或货币，并且浮点数的域可能因不同的测量/上下文而异）。

系统确实需要在 2012 年及更高版本上运行。

SET NOCOUNT ON;
DECLARE @xml AS Xml = '<a><b>bbb</b><c>ccc</c><d>ddd</d></a>';
SELECT @xml;
SELECT @xml.query('/a/*[self::b or self::c]');
SET @xml.modify('delete /a/d');
SELECT @xml;

给出以下结果集

原来的：

<a><b>bbb</b><c>ccc</c><d>ddd</d></a>

过滤以排除非 (b|c) - 但缺少父母：

<b>bbb</b><c>ccc</c>

我想要的（可用于删除步骤，但不可用于 .query）：

<a><b>bbb</b><c>ccc</c></a>

是否可以在 XQuery 中保留父母？

我正在尝试在多个表上生成触发器以强制数据为大写并在插入或更新数据时修剪前导和尾随空格。我遇到的问题是在触发器中确定一种将 INSERTED 伪表绑定到基表的通用方法。并非所有表都具有相同的 PK 名称，尽管许多表在每个表中都有一个可以使用的名为 _DataChanges_RowID 的唯一列。我知道我可以检查 PK 并使用这些列生成触发器，但在查看之前，我想知道是否有一种通用方法可以将 INSERTED 伪表连接到基表，这种方法更简单且与列无关。

DECLARE @TriggerTemplate AS VARCHAR(MAX) = 
'CREATE TRIGGER <<SchemaName>>.<<TriggerName>> ON <<FullTableName>>
AFTER INSERT, UPDATE
AS BEGIN
    IF (ROWCOUNT_BIG() = 0)
        RETURN;
    IF TRIGGER_NESTLEVEL(( SELECT object_id FROM sys.triggers WHERE QUOTENAME(name) = ''<<TriggerName>>'' ), ''AFTER'', ''DML'') < 1
    UPDATE <<FullTableName>>
    SET <<SQLColumnUpdate>>
    WHERE <<FullTableName>>._DataChanges_RowID IN (SELECT _DataChanges_RowID FROM INSERTED);
END;
GO
sp_settriggerorder <<SchemaName>>.<<TriggerName>>, ''FIRST'', ''INSERT'';
sp_settriggerorder <<SchemaName>>.<<TriggerName>>, ''FIRST'', ''UPDATE'';
GO
';

WITH cols AS 
    (
        SELECT
            FullTableName = QUOTENAME(S.name) + '.' + QUOTENAME(T.name),
            SchemaName = QUOTENAME(S.name),
            TableName = QUOTENAME(T.name),
            ColumnName = QUOTENAME(C.name)
        FROM
            sys.columns C
            INNER JOIN sys.tables T
                ON C.object_id = T.object_id
            INNER JOIN sys.schemas S
                ON T.schema_id = S.schema_id
        WHERE
            C.is_computed = 0
            AND C.system_type_id IN (
                    167,  -- varchar
                    175,  -- char
                    231,  -- nvarchar
                    239   -- nchar
                )
    )
, TablesAndColumns AS (
    SELECT
        cols.FullTableName
        , cols.TableName
        , cols.SchemaName
        , TriggerName = QUOTENAME('TRG_DATA_CONFORM_' + cols.FullTableName)
        , SQLColumnUpdate = STUFF((SELECT ', ' + c2.ColumnName + ' = UPPER(LTRIM(RTRIM(' + c2.ColumnName + ')))' 
               FROM cols c2
              WHERE c2.FullTableName = cols.FullTableName
              ORDER BY c2.ColumnName
                FOR XML PATH(''), TYPE).value('.', 'VARCHAR(MAX)'), 1, 2, '')
    FROM cols
    GROUP BY cols.FullTableName, cols.SchemaName, cols.TableName
)
SELECT FullTableName
    , SQLTrigger = REPLACE(REPLACE(REPLACE(REPLACE(@TriggerTemplate, '<<SQLColumnUpdate>>', SQLColumnUpdate), '<<TriggerName>>', TriggerName), '<<FullTableName>>', FullTableName), '<<SchemaName>>', SchemaName)
    , KeyOK = CASE WHEN EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE QUOTENAME(TABLE_SCHEMA) = SchemaName AND QUOTENAME(TABLE_NAME) = TableName AND COLUMN_NAME = '_DataChanges_RowID') THEN 1 ELSE 0 END
FROM TablesAndColumns
;

为什么一个查询的多个计划不好

所以我跑去sp_Blitz了解一些系统。有一些代码可以像往常一样清理。一些应该是聚集索引的堆。等等

这个特定的查询使用文字，似乎导致了很多计划。在此查询中的表上整理索引和内容，甚至为最新代码/数据库参数化查询以投入生产。

但查询仍然显示为参数化问题。（DBA 帮助将计划缓存查询转换为 SSRS 报告，这样我就可以通过浏览器在 PROD 环境中快速运行它们）。

然后去哪儿？忽略它？（似乎很多计划都很重要）

使用强制参数化？（但是显式参数化的那个出现了）。

呃——我看到开发人员没有接受我的建议，它LEFT JOIN正在变成一个INNER JOIN……我必须把它修好……

如果没有强制参数化，所有这些字面风格的查询将永远不会共享相同的计划吗？这个查询对于简单的参数化来说是否太复杂了？

我的数据是这样的：

keycol,col1,col2,col3
1,a;b;c,some data,some other data
2,x,some data,some other data
3,y;z,some data,some other data

在源系统中用分号分隔列的位置。

我想将其转向：

1,a,some data,some other data
1,b,some data,some other data
1,c,some data,some other data
2,x,some data,some other data
3,y,some data,some other data
3,z,some data,some other data

我在这里找到了一种技术，但我不能完全让它发挥作用：

CREATE TABLE yt
    (keycol int, col1 varchar2(5), col2 varchar2(9), col3 varchar2(15))
;

INSERT ALL 
    INTO yt (keycol, col1, col2, col3)
         VALUES (1, 'a;b;c', 'some data', 'some other data')
SELECT * FROM dual
;

INSERT ALL 
    INTO yt (keycol, col1, col2, col3)
         VALUES (2, 'x', 'some data', 'some other data')
SELECT * FROM dual
;

INSERT ALL 
    INTO yt (keycol, col1, col2, col3)
         VALUES (3, 'y;z', 'some data', 'some other data')
SELECT * FROM dual
;

我想我可以通过这种方式在 CONNECT BY 中包含 keycol 以获得并行递归链，但我想它不会那样工作。我很确定我已经在 SQL Server 中使用递归 CTE 完成了此操作。

SELECT keycol
  ,trim(regexp_substr(col1, '[^;]+', 1, level)) col1
  ,col2 
  ,col3
FROM yt t
CONNECT BY keycol = PRIOR keycol AND instr(col1, ';', 1, level - 1) > 0

http://sqlfiddle.com/#!4/3d378

FWIW，我在 Oracle 10g 上。

与 2005 年相比，我在命令行上指定 /ConfigFile something.dtsConfig 时，2008 年包配置发生了变化，包中定义的变量保持其设计时值，而不是使用配置文件中的设置。

我不太确定我是否完全了解如何获取要使用的外部配置文件。我读过文章说只有设置的设计时配置才会覆盖外部文件的负载。这是否意味着我可以将变量更改为空白字符串，然后它们将被覆盖？我无法完全删除变量！整数呢？

我看过文章提到使用包中的包配置关闭。

我可以使用 SSIS 包编辑器或 XML 编辑器来更改包中的配置文件路径，然后它将使用该文件的“最后”设置（无论外部 /ConfigFile 选项如何），但我不想成为改变包装。我想要一个带有 Test.dtsConfig 和 Production.dtsConfig 的包，并且能够在不更改包的情况下来回交换。

现在推荐的方法是什么？

我有两个更新 - 一个首先锁定 CI，然后锁定 NCI（状态），因为状态列也在更新。另一个已经在 NCI 上拥有 U 锁，因为它知道它正在更改，然后尝试在 CI 上获得 U 锁。

强制这些序列化的最简单方法是什么？使用表级提示似乎很奇怪，因为这是一个内部索引问题——只涉及一个表——UPDLOCK、HOLDLOCK 是否会自动应用于该表所需的所有索引，从而强制它被序列化？

以下是查询：

UPDATE htt_action_log
SET status = 'ABORTED', CLOSED = GETUTCDATE()
WHERE transition_uuid = '{F53ADDDA-E46B-4726-66D8-D7B640B66597}'
AND status = 'OPEN';

那个 X 锁定 CI 中的行（在 CREATED 列上），然后尝试 X 锁定包括状态列的 NCI。

UPDATE htt_action_log
SET status = 'RUNNING {36082BCD-EB52-4358-E3D3-4D96FD5B9F0F} 1360094342'
WHERE action_uuid = (SELECT TOP 1 action_uuid
                     FROM htt_action_log
                     WHERE transition_uuid = '{F53ADDDA-E46B-4726-66D8-D7B640B66597}'
                         AND status = 'OPEN'
                     ORDER BY action_seq)

这个 U 锁定了相同的 NCI - 我猜是嵌套查询，然后去锁定 CI 以进行更新。

因此，订单产生了死锁。

最简单的解决方案是强制两个查询完全阻塞 - 即序列化。强制执行此操作的最简单方法是什么，只需WITH (UPDLOCK, HOLDLOCK)添加对表格的引用（第一个中的一个，第二个中的两个）？

DDL：

请注意，客户端在此表上有更多索引，这些索引应该受此更新影响，但死锁图中未提及。

CREATE TABLE [dbo].[HTT_ACTION_LOG](
    [ACTION_UUID] [varchar](128) NOT NULL,
    [TRANSITION_UUID] [varchar](128) NOT NULL,
    [STATUS] [varchar](128) NOT NULL,
    [CREATED] [datetime] NOT NULL,
    [CLOSED] [datetime] NULL,
    [ACTION_SEQ] [int] NOT NULL,
    [ACTION_TYPE] [varchar](15) NOT NULL,
    [ACTION_NAME] [varchar](50) NOT NULL,
    [ACTION_RESULT] [varchar](8000) NULL,
    [PENDING_SINCE] [datetime] NULL,
    [ACTION_SQL] [varchar](8000) NULL,
    [ERROR_OK] [int] NULL,
    [ERROR_COND] [varchar](2048) NULL,
    [RETRY] [varchar](128) NULL,
 CONSTRAINT [PK_HTT_ACTION_LOG_1] UNIQUE NONCLUSTERED 
(
    [ACTION_UUID] ASC
)
)

CREATE CLUSTERED INDEX [IK_HTT_ACTION_LOG_2] ON [dbo].[HTT_ACTION_LOG] 
(
    [CREATED] ASC
)

CREATE NONCLUSTERED INDEX [IK_HTT_ACTION_LOG_1] ON [dbo].[HTT_ACTION_LOG] 
(
    [TRANSITION_UUID] ASC,
    [STATUS] ASC
)
INCLUDE ( [ACTION_UUID],
[ACTION_SEQ])

CREATE NONCLUSTERED INDEX [IK_HTT_ACTION_LOG_4] ON [dbo].[HTT_ACTION_LOG] 
(
    [ACTION_UUID] ASC,
    [STATUS] ASC
)

CREATE NONCLUSTERED INDEX [missing_index_11438530_11438529_HTT_ACTION_LOG] ON [dbo].[HTT_ACTION_LOG] 
(
    [TRANSITION_UUID] ASC,
    [ACTION_TYPE] ASC
)
INCLUDE ( [ACTION_NAME])

CREATE NONCLUSTERED INDEX [missing_index_7207590_7207589_HTT_ACTION_LOG] ON [dbo].[HTT_ACTION_LOG] 
(
    [STATUS] ASC
)
INCLUDE ( [CREATED],
[PENDING_SINCE],
[ACTION_NAME])

CREATE NONCLUSTERED INDEX [missing_index_8535421_8535420_HTT_ACTION_LOG] ON [dbo].[HTT_ACTION_LOG] 
(
    [TRANSITION_UUID] ASC
)
INCLUDE ( [ACTION_UUID],
[STATUS])

ALTER TABLE [dbo].[HTT_ACTION_LOG] SET (LOCK_ESCALATION = AUTO)

ALTER TABLE [dbo].[HTT_ACTION_LOG]  WITH CHECK ADD  CONSTRAINT [FK_HTT_ACTION_LOG_1] FOREIGN KEY([TRANSITION_UUID])
REFERENCES [dbo].[HTT_TRANSITION_LOG] ([TRANSITION_UUID])

ALTER TABLE [dbo].[HTT_ACTION_LOG] CHECK CONSTRAINT [FK_HTT_ACTION_LOG_1]

ALTER TABLE [dbo].[HTT_ACTION_LOG] ADD  DEFAULT ('OPEN') FOR [STATUS]

ALTER TABLE [dbo].[HTT_ACTION_LOG] ADD  DEFAULT (getutcdate()) FOR [CREATED]

ALTER TABLE [dbo].[HTT_ACTION_LOG] ADD  DEFAULT ((0)) FOR [ERROR_OK]

我有一个来自死锁的死锁图，其中一个进程正在执行 SELECT，而一个进程正在执行 UPDATE。这似乎是 SELECT 获得 NCI 锁以执行连接，然后获得 CI 锁以通过查找检索所有数据的经典案例。而 UPDATE 是使用 CI 锁来执行更新，然后需要锁定 NCI，因为更新会导致状态更改，而 NCI 便于按状态查找项目。

问题是 UPDATE 想要的锁之一不在它正在更新的表上，我找不到发生这种情况的原因。

这是选择：

SELECT *,
       RIGHT(c.CC_NUMBER, 4) AS CC_LAST_4,
       DATEDIFF(ss, '1970-01-01', plan_started ) plan_started_epoch,
       DATEDIFF(ss, '1970-01-01', plan_expires ) plan_expires_epoch
FROM customers c, accounts a, parent_cos pc, htt_customers_overlay_ultra u
WHERE c.customer_id = a.customer_id
AND   u.customer_id = c.customer_id
AND   a.cos_id=pc.cos_id
AND   u.customer_id = 9300;

这是更新：

UPDATE htt_customers_overlay_ultra SET plan_state = 'Active'  WHERE customer_id = 9300;

但是根据死锁图，UPDATE 正在获取 ACCOUNTS.ACCOUNT0 上的锁，这是 ACCOUNTS 表的 PK (CI)。覆盖表中没有外键。有一些我目前无权查看的默认约束。

我已经查看了 SSMS 和 SQL Sentry Plan Explorer Pro 中的死锁图，但我并不明智。

以下是执行计划：

对于选择

对于更新

我想找出它为什么会得到这个锁，然后是序列化这些调用的最佳方法。

我知道的事情我已经告知客户，这些事情与所取的锁有关，但没有解释正在出现的看似无关的锁：

删除 * 并确定所需的列并更改 NCI 以覆盖 - 这可能会使 SELECT 使用更少的锁

确定系统为什么选择另一个进程正在处理的相同数据 - 这可能会完全缓解这两个进程同时运行

SELECT 中有一个表扫描

从编辑窗口打开 .sql 文件时，是否有选项告诉它停止提示我拆分包含多个对象的文件？我从不想拆分文件！