若想要自動忽略重覆鍵錯誤.可使用IGNORE_DUP_KEY = ON,如此仍可繼續寫入其餘交易資料忽略此錯誤
用到的情境不多，多虧同事無意間的測試看到這個參數...
--重建資料表 CREATE TABLE dbo.PurchaseOrderDetail
(
PurchaseOrderID int NOT NULL
REFERENCES Purchasing.PurchaseOrderHeader(PurchaseOrderID),
LineNumber smallint NOT NULL,
ProductID int NULL
REFERENCES Production.Product(ProductID),
UnitPrice money NULL,
OrderQty smallint NULL,
ReceivedQty float NULL,
RejectedQty float NULL,
DueDate datetime NULL,
rowguid uniqueidentifier ROWGUIDCOL NOT NULL
CONSTRAINT DF_PurchaseOrderDetail_rowguid DEFAULT (newid()),
ModifiedDate datetime NOT NULL
CONSTRAINT DF_PurchaseOrderDetail_ModifiedDate DEFAULT (getdate()),
LineTotal AS ((UnitPrice*OrderQty)),
StockedQty AS ((ReceivedQty-RejectedQty)),
CONSTRAINT PK_PurchaseOrderDetail_PurchaseOrderID_LineNumber
PRIMARY KEY CLUSTERED (PurchaseOrderID, LineNumber)
WITH (IGNORE_DUP_KEY = OFF)
)
ON PRIMARY;
|
--修改索引 USE AdventureWorks2008R2;
GO
ALTER INDEX AK_SalesOrderHeader_SalesOrderNumber ON
Sales.SalesOrderHeader
SET (
STATISTICS_NORECOMPUTE = ON,
IGNORE_DUP_KEY = ON,
ALLOW_PAGE_LOCKS = ON
) ;
GO
|
參考MSDN上的說明
create table
alter index
沒有留言:
張貼留言