sql-server - 如何为查询中的批量插入中的所有行生成单个 GUID？

问题描述

我正在编写一个快速而简单的应用程序来将销售计划数据加载到 SQL Server（2008 FWIW，尽管我认为特定版本并不重要）。

数据集是公司销售计划：客户、零件编号和月份的每个组合的几千行单位、美元和价格。这些数据每隔几周就会更新一次，重要的是要跟踪谁更改了它以及更改了哪些内容。

--  Metadata columns are suffixed with ' ##', to enable an automated 
--  tool I wrote to handle repetitive tasks such as de-duplication of 
--  records whose values didn't change in successive versions of the 
--  forecast.  

CREATE TABLE [SlsPlan].[PlanDetail]
(
    [CustID] [char](15) NOT NULL,
    [InvtID] [char](30) NOT NULL,
    [FiscalYear] [int] NOT NULL,
    [FiscalMonth] [int] NOT NULL,
    [Version Number ##] [int] IDENTITY(1,1) NOT NULL,

    [Units] [decimal](18, 6) NULL,
    [Unit Price] [decimal](18, 6) NULL,
    [Dollars] [decimal](18, 6) NULL,

    [Batch GUID ##] [uniqueidentifier] NOT NULL,
    [Record GUID ##] [uniqueidentifier] NOT NULL DEFAULT (NEWSEQUENTIALID()),
    [Time Created ##] [datetime] NOT NULL,
    [User ID ##] [varchar](64) NULL DEFAULT (ORIGINAL_LOGIN()),

    CONSTRAINT [PlanByProduct_PK] PRIMARY KEY CLUSTERED 
        ([CustID], [InvtID], [FiscalYear], [FiscalMonth], [Version Number ##])
)

为了跟踪更改，我使用 IDENTITY 列作为主键的一部分，以启用具有相同主键的多个版本。为了跟踪谁进行了更改，并且如果有人做了一些完全愚蠢的事情，也为了启用退出整个错误更新，我插入了该版本记录的创建者的 Active Directory 登录、一个时间戳和两个 GUID。

批次中所有记录的“批次 GUID”列应相同；“记录 GUID”列显然对于该特定记录是唯一的，并且仅用于重复数据删除，而不用于任何类型的查询。

我强烈希望在查询中生成批处理 GUID，而不是通过编写明显的存储过程：

DECLARE @BatchGUID UNIQUEIDENTIFIER = NEWID()

INSERT INTO MyTable 
SELECT I.*, @BatchGUID
FROM InputTable I

我认为最简单的方法是使用时间戳、用户 ID 和调用 NEWID() 来创建批处理 GUID 构建单行结果。然后，执行 CROSS JOIN 以将该单行附加到要插入的每一行。我尝试了几种不同的方法，看起来查询执行引擎本质上是执行 GETDATE() 一次，因为所有行中都会出现一个时间戳（即使对于 500 万行的测试用例也是如此）。但是，我为结果集中的每一行得到不同的 GUID。

下面的例子只关注查询，省略了它们周围的插入逻辑。

WITH MySingleRow AS 
(
    Select              NewID() as [Batch GUID ##], 
                        ORIGINAL_LOGIN() as [User ID ##],
                        getdate() as [Time Created ##]
)
SELECT                  N.*, R1.*
FROM                    util.zzIntegers N
    CROSS JOIN          MySingleRow R1
WHERE                   N.Sequence < 10000000

在上面的查询中，“util.zzIntegers”只是一个从 0 到 1000 万的整数表。查询需要大约 10 秒才能在我的服务器上运行一个冷缓存，所以如果 SQL Server 对主表的每一行执行 GETDATE() 函数，它肯定至少在毫秒列中会有不同的值，但是所有 1000 万行都具有相同的时间戳。但是我为每一行得到不同的 GUID。正如我之前所说，目标是在每一行中都有相同的 GUID。

我还决定尝试一个带有显式表值构造函数的版本，希望能够欺骗优化器做正确的事情。我还针对真实表运行它，而不是像单列整数列表那样相对“综合”测试。以下产生了相同的结果。

WITH AnotherSingleRow AS
(
    SELECT              SingleRow.* 
    FROM                (
                            VALUES (NewID(), Original_Login(), getdate())
                        ) 
                        AS SingleRow(GUID, UserID, TimeStamp)
)

SELECT                  R1.*, S.*
FROM                    SalesOrderLineItems S
    CROSS JOIN          AnotherSingleRow R1

SalesOrderLineItems 是一个包含 600 万行和 135 列的表，以确保运行时间足够长，如果 SQL Server 完全优化表值构造函数并在每次查询运行时调用函数，则 GETDATE() 会增加.

我已经在这里潜伏了一段时间，这是我的第一个问题，所以我绝对想做好研究，避免因为只是提出一个问题而受到批评。此站点上的以下问题涉及 GUID，但不直接相关。我还花了半个小时在谷歌上搜索各种短语组合，似乎没有找到任何东西。

Azure 实际上做了我想做的事，正如我在研究中发现的以下问题所证明的那样： Guid.NewGuid() always return same Guid for all rows。但是，我不在 Azure 上，也不会很快去那里。
有人试图在 SSIS 中做同样的事情（如何在 SSIS 导入中插入相同的 guid），但该查询的答案是您在 SSIS 中生成 GUID 作为变量并将其插入每一行。我当然可以在存储过程中做同样的事情，但是为了优雅和可维护性（我的同事在 SQL Server 查询方面的经验比我少），我更愿意在查询中保留批处理 GUID 的创建，并且尽可能简化任何存储过程。

顺便说一句，我的经验水平是 1-2 年的 SQL Server 作为数据分析师/SQL 开发人员，这是 10 多年编写代码的一部分，但在过去的 20 年里，我主要是一个数字人而不是 IT 人。在我职业生涯的早期，作为查询优化器的开发人员之一，我曾在一家开创性的数据库供应商工作，所以我非常清楚查询优化器的作用，但还没有时间真正深入研究 SQL Server 是如何做到的. 所以我可能完全错过了对其他人来说显而易见的东西。

预先感谢您的帮助。

标签： sql-server

sql-server - 如何为查询中的批量插入中的所有行生成单个 GUID？

问题描述

解决方案

推荐阅读