是否有相當於“OVERRIDING USER VALUE”的 SQL Server

在Google搜尋時，我發現了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 中，您實際上不需要對外圍程式碼進行任何更改或添加 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 程式碼。

引用自：https://dba.stackexchange.com/questions/31647