主页 / dba / 问题 / 31647
Accepted
Scott Chamberlain
Asked: 2013-01-10 10:17:54 +0800 CST

是否有相当于“OVERRIDING USER VALUE”的 SQL Server

  • 772

在谷歌搜索时，我发现了IBM iSeries的OVERRIDING USER VALUE参数。INSERT

Microsoft SQL Server 2005 或更高版本是否有等效命令允许您插入具有IDENTITY列的表并使用自动分配的值而不是传入的用户值？

在这种情况下，主要目标是帮助说服上级不要让我们停止使用 GUID 作为集群主键（该模式是在 SQL 2000 之前开发的，这是一个不错的想法，而现在它是一个可怕的想法想法）向表中添加标识列并将聚集主键移动到该列并将旧索引转换为非聚集唯一索引。

最大的阻力是：

遗留代码中有很多地方使用诸如Insert into XXXX Select * from YYYY where ...列数根据客户端前端的运行时可调整设置而不同的地方。使用标识列会破坏这些查询，而 GUID 是唯一的并且不会出现问题，并且重写所有查询以使用命名列的工作量太大，因为每个安装的客户端的列名都是可变的。

这就是为什么SET IDENTITY_INSERT ON不是一个选项，因为这将是一个主键列。我希望提出这样的论点，即如果我们可以使用 SQL Server 等价物，OVERRIDING USER VALUE我可以使用类似“我们可以在插入语句中添加这个小部分，我们不需要包含列名”这样的论点。

sql-server sql-server-2005

2 个回答

  1. Best Answer

    在 SQL Server 中，您实际上不需要对外围代码进行任何更改或添加 INSTEAD OF 触发器来完成这项工作。这是一个简单的示例，在 SQL Server 2012 上进行了测试，但在 2005 上也应该可以正常工作：

    CREATE TABLE dbo.source(bar INT, x UNIQUEIDENTIFIER PRIMARY KEY DEFAULT NEWID());
GO
CREATE TABLE dbo.[target](bar INT, x UNIQUEIDENTIFIER PRIMARY KEY);
GO

INSERT dbo.source(bar) SELECT 1;
GO
INSERT dbo.[target] SELECT * FROM dbo.source;
GO

ALTER TABLE dbo.[target] ADD TargetID INT IDENTITY(1,1);
GO

TRUNCATE TABLE dbo.source;
INSERT dbo.source(bar) SELECT 1;
GO
INSERT dbo.[target] SELECT * FROM dbo.source;
GO

SELECT * FROM dbo.[target];

    结果：

    bar   x                                      TargetID
---   ------------------------------------   --------
1     EFF8DAC4-FB3E-4734-80BE-6DC229846203   1
1     5036688D-C04A-45FC-920E-FF44D7D501D1   2

    现在我也可以更改主键并重复该过程：

    ALTER TABLE [dbo].[target] DROP CONSTRAINT PK__target__3BD019E50386B4EA;
ALTER TABLE [dbo].[target] ADD CONSTRAINT PK__target__3BD019E50386B4EA  
  PRIMARY KEY (targetID);

TRUNCATE TABLE dbo.source;
INSERT dbo.source(bar) SELECT 1;
GO
INSERT dbo.[target] SELECT * FROM dbo.source;
GO

SELECT * FROM dbo.[target];

    结果：

    bar   x                                      TargetID
---   ------------------------------------   --------
1     EFF8DAC4-FB3E-4734-80BE-6DC229846203   1
1     5036688D-C04A-45FC-920E-FF44D7D501D1   2
1     41FE97FF-7D45-46EB-8A0D-B2C3BA1E67EA   3

    因此，我不必更改使用插入/选择而没有任何列列表的错误代码，只要源表也不会更改，并且假设对目标的唯一更改是添加标识列。

    PS 这里是如何自动生成 IDENTITY 列（假设你会想要<tablename>ID）：

    DECLARE @sql NVARCHAR(MAX);

SET @sql = N'';

SELECT @sql = @sql + '
  ALTER TABLE ' + QUOTENAME(OBJECT_SCHEMA_NAME(t.[object_id]))
  + '.' + QUOTENAME(t.name) + ' ADD ' + t.name + 'ID INT IDENTITY(1,1);'
FROM sys.tables AS t
WHERE name IN (...) -- you will need to fill in this part
AND NOT EXISTS 
(
  SELECT 1 FROM sys.columns WHERE [object_id] = t.[object_id] 
    AND (is_identity = 1 OR name = t.name + 'ID')
);

SELECT @sql;
-- EXEC sp_executesql @sql;

    （请注意，SELECT输出将大致向您显示命令的外观，但由于 SSMS 中的输出限制以及您拥有的表数量，它不一定会向您显示当您取消注释时将执行的完整命令EXEC。 )

    以及主键的删除/重新创建：

    DECLARE @sql NVARCHAR(MAX);

SET @sql = N'';

SELECT @sql = @sql + '
  ALTER TABLE ' + 
  + QUOTENAME(OBJECT_SCHEMA_NAME(t.[object_id]))
  + '.' + QUOTENAME(t.name) + ' DROP CONSTRAINT ' + k.name + ';
  ALTER TABLE ' + 
  + QUOTENAME(OBJECT_SCHEMA_NAME(t.[object_id]))
  + '.' + QUOTENAME(t.name) + ' ADD CONSTRAINT ' 
  + k.name + ' PRIMARY KEY (' + t.name + 'ID);' 
FROM sys.key_constraints AS k
INNER JOIN sys.tables AS t
ON k.parent_object_id = t.[object_id]
WHERE k.[type] = 'PK'
AND t.name IN (...); -- again, you'll want to identify the list of tables

SELECT @sql;
-- EXEC sp_executesql @sql;

    您需要在数据库处于SINGLE_USER模式或应用程序无法连接到数据库时执行此操作。您还需要在 QA 或开发系统上测试所有这些，然后再将其用于生产。

    现在，这仍然不是最佳实践——我强烈建议您停止在您的应用程序中嵌入 SQL 代码，尤其是在不指定列列表的情况下插入/选择的 SQL 代码。

    • 2

  2. 您提供的链接中的示例如下所示：

    INSERT INTO ORDERS OVERRIDING USER VALUE
   (SELECT * FROM TODAYS_ORDER)

    除了OVERRIDING USER VALUESQL Server 不知道的之外，此语句还遵循错误的编码习惯。出于多种原因，您总是希望指定要插入的列以及选择中的列。有了它，复制功能很容易 - 只需省略标识列：

    INSERT INTO dbo.Orders(col2, col3, col4)
SELECT Col2, Col3, Col4 FROM dbo.TodaysOrders;

    既然您为问题添加了更多细节，我想列列表没有帮助。但是，您可以通过在触发器内提供完整的列列表插入来添加一个代替触发器来丢弃标识列。

    您甚至可以自动生成这些触发器。

    • 0

相关问题