Migrieren von Überprüfungs- und Fremdschlüsseleinschränkungen
Check- und Fremdschlüsseleinschränkungen werden in In-Memory OLTP in SQL Server 2014 nicht unterstützt. Diese Konstrukte werden normalerweise verwendet, um die Integrität logischer Daten im Schema zu erzwingen und können wichtig sein, um die funktionale Korrektheit von Anwendungen aufrechtzuerhalten.
Logische Integritätsprüfungen für eine Tabelle, z. B. Überprüfungen und Fremdschlüsseleinschränkungen, erfordern eine zusätzliche Verarbeitung für Transaktionen und sollten in der Regel bei leistungssensiblen Anwendungen vermieden werden. Wenn solche Überprüfungen jedoch für Ihre Anwendung von entscheidender Bedeutung sind, gibt es zwei Problemumgehungen.
Überprüfen von Einschränkungen nach einem Einfüge-, Update- oder Löschvorgang
Diese Problemumgehung ist optimistisch und basiert auf der Annahme, dass die meisten Änderungen nicht gegen die Einschränkungen verstoßen. In dieser Problemumgehung werden die Daten zuerst geändert, bevor die Einschränkungen ausgewertet werden. Wenn eine Einschränkung verletzt wird, wird sie erkannt, aber die Änderung wird nicht zurückgesetzt.
Diese Problemumgehung hat den Vorteil, dass sie minimale Auswirkungen auf die Leistung hat, da Datenänderungen nicht durch Einschränkungsprüfungen blockiert werden. Wenn jedoch eine Änderung auftritt, die gegen eine oder mehrere Einschränkungen verstößt, kann der Prozess zum Zurücksetzen dieser Änderung lange dauern.
Erzwingen von Einschränkungen vor einem Einfüge-, Update- oder Löschvorgang
Diese Problemumgehung emuliert das Verhalten SQL Server Einschränkungen. Die Einschränkungen werden vor der Datenänderung überprüft und beenden die Transaktion, wenn eine Überprüfung fehlschlägt. Diese Methode führt zu Leistungseinbußen für Datenänderungen, stellt jedoch sicher, dass Daten in einer Tabelle immer die Einschränkungen erfüllen.
Verwenden Sie diese Problemumgehung, wenn die Integrität logischer Daten für die Richtigkeit von entscheidender Bedeutung ist und Änderungen, die gegen eine Einschränkung verstoßen, wahrscheinlich sind. Um die Integrität zu gewährleisten, müssen jedoch alle Datenänderungen über gespeicherte Prozeduren erfolgen, die diese Erzwingungen enthalten. Änderungen durch Ad-hoc-Abfragen und andere gespeicherte Prozeduren erzwingen diese Einschränkungen nicht und können daher ohne Warnungen gegen diese verstoßen.
Beispielcode
Die folgenden Beispiele basieren auf der AdventureWorks2012-Datenbank. Insbesondere basieren diese Beispiele auf dem [Vertrieb]. [SalesOrderDetail]-Tabelle und die zugehörigen Check- und Fremdschlüsseleinschränkungen zusätzlich zum eindeutigen Index.
Die hier angegebenen gespeicherten Prozeduren dienen nur für Einsetvorgänge. Gespeicherte Prozeduren für Aktualisierungs- und Löschvorgänge sollten ähnliche Strukturen aufweisen.
Tabellendefinition für die Problemumgehungen
Vor der Konvertierung in eine speicheroptimierte Tabelle die Definition für [Sales]. [SalesOrderDetail] lautet wie folgt:
USE [AdventureWorks2012]
GO
CREATE TABLE [Sales].[SalesOrderDetail]([SalesOrderID] [int] NOT NULL,
[SalesOrderDetailID] [int] IDENTITY(1,1) NOT NULL,
[CarrierTrackingNumber] [nvarchar](25) NULL,
[OrderQty] [smallint] NOT NULL,
[ProductID] [int] NOT NULL,
[SpecialOfferID] [int] NOT NULL,
[UnitPrice] [money] NOT NULL,
[UnitPriceDiscount] [money] NOT NULL CONSTRAINT [DF_SalesOrderDetail_UnitPriceDiscount] DEFAULT ((0.0)),
[LineTotal] AS (isnull(([UnitPrice]*((1.0)-[UnitPriceDiscount]))*[OrderQty],(0.0))),
[rowguid] [uniqueidentifier] ROWGUIDCOL NOT NULL CONSTRAINT [DF_SalesOrderDetail_rowguid] DEFAULT (newid()),
[ModifiedDate] [datetime] NOT NULL CONSTRAINT [DF_SalesOrderDetail_ModifiedDate] DEFAULT (getdate()),
CONSTRAINT [PK_SalesOrderDetail_SalesOrderID_SalesOrderDetailID] PRIMARY KEY CLUSTERED
(
[SalesOrderID] ASC,
[SalesOrderDetailID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]) ON [PRIMARY]
GO
ALTER TABLE [Sales].[SalesOrderDetail] WITH CHECK ADD CONSTRAINT [FK_SalesOrderDetail_SalesOrderHeader_SalesOrderID] FOREIGN KEY([SalesOrderID])
REFERENCES [Sales].[SalesOrderHeader] ([SalesOrderID])
ON DELETE CASCADE
GO
ALTER TABLE [Sales].[SalesOrderDetail] CHECK CONSTRAINT [FK_SalesOrderDetail_SalesOrderHeader_SalesOrderID]
GO
ALTER TABLE [Sales].[SalesOrderDetail] WITH CHECK ADD CONSTRAINT [FK_SalesOrderDetail_SpecialOfferProduct_SpecialOfferIDProductID] FOREIGN KEY([SpecialOfferID], [ProductID])
REFERENCES [Sales].[SpecialOfferProduct] ([SpecialOfferID], [ProductID])
GO
ALTER TABLE [Sales].[SalesOrderDetail] CHECK CONSTRAINT [FK_SalesOrderDetail_SpecialOfferProduct_SpecialOfferIDProductID]
GO
ALTER TABLE [Sales].[SalesOrderDetail] WITH CHECK ADD CONSTRAINT [CK_SalesOrderDetail_OrderQty] CHECK (([OrderQty]>(0)))
GO
ALTER TABLE [Sales].[SalesOrderDetail] CHECK CONSTRAINT [CK_SalesOrderDetail_OrderQty]
GO
ALTER TABLE [Sales].[SalesOrderDetail] WITH CHECK ADD CONSTRAINT [CK_SalesOrderDetail_UnitPrice] CHECK (([UnitPrice]>=(0.00)))
GO
ALTER TABLE [Sales].[SalesOrderDetail] CHECK CONSTRAINT [CK_SalesOrderDetail_UnitPrice]
GO
ALTER TABLE [Sales].[SalesOrderDetail] WITH CHECK ADD CONSTRAINT [CK_SalesOrderDetail_UnitPriceDiscount] CHECK (([UnitPriceDiscount]>=(0.00)))
GO
ALTER TABLE [Sales].[SalesOrderDetail] CHECK CONSTRAINT [CK_SalesOrderDetail_UnitPriceDiscount]
GO
Nach der Konvertierung in eine speicheroptimierte Tabelle die Definition für [Sales]. [SalesOrderDetail] lautet wie folgt:
Beachten Sie, dass rowguid kein ROWGUIDCOL mehr ist, da sie in In-Memory OLTP nicht unterstützt wird. Die Spalte wurde entfernt. Darüber hinaus ist LineTotal eine berechnete Spalte und außerhalb des Bereichs für diesen Artikel, sodass es ebenfalls entfernt wurde.
USE [AdventureWorks2012]
GO
CREATE TABLE [Sales].[SalesOrderDetail]([SalesOrderID] [int] NOT NULL,
[SalesOrderDetailID] [int] IDENTITY(1,1) NOT NULL,
[CarrierTrackingNumber] [nvarchar](25) NULL,
[OrderQty] [smallint] NOT NULL,
[ProductID] [int] NOT NULL,
[SpecialOfferID] [int] NOT NULL,
[UnitPrice] [money] NOT NULL,
[UnitPriceDiscount] [money] NOT NULL CONSTRAINT [DF_SalesOrderDetail_UnitPriceDiscount] DEFAULT ((0.0)),
[ModifiedDate] [datetime] NOT NULL CONSTRAINT [DF_SalesOrderDetail_ModifiedDate] DEFAULT (getdate()),
CONSTRAINT [PK_SalesOrderDetail_SalesOrderID_SalesOrderDetailID] PRIMARY KEY NONCLUSTERED
(
[SalesOrderID] ASC,
[SalesOrderDetailID] ASC
),
INDEX [AK_SalesOrderDetail_rowguid] NONCLUSTERED HASH ([rowguid]) WITH (BUCKET_COUNT = 1048576),
INDEX [IX_SalesOrderDetail_ProductId] NONCLUSTERED ([ProductId] ASC)) WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_AND_DATA)
GO
Überprüfen von Einschränkungen nach einem Einfüge-, Update- oder Löschvorgang
USE AdventureWorks2012
GO
CREATE PROCEDURE Sales.usp_insert_SalesOrderDetails
@SalesOrderId int, @CarrierTrackingNumber nvarchar(25) = null, @OrderQty smallint, @ProductId int, @SpecialOfferID int,
@UnitPrice money, @UnitPriceDiscount money = 0.00, @ModifiedDate datetime = null
AS
BEGIN
BEGIN TRANSACTION
-- handle defaults for the insert.
-- This is to make the insert logic less complex. Default constraints on the table should be in sync with this logic.
-- Conversely, you can write an INSERT statement for each case where one or more values for the three columns with default constraints are not specified.
IF @ModifiedDate = null SET @ModifiedDate = GETDATE()
-- Insert the row.
INSERT INTO Sales.SalesOrderDetail
(SalesOrderID, CarrierTrackingNumber, OrderQty, ProductID, SpecialOfferID, UnitPrice, UnitPriceDiscount, ModifiedDate)
VALUES
(@SalesOrderId, @CarrierTrackingNumber, @OrderQty, @ProductID, @SpecialOfferID, @UnitPrice, @UnitPriceDiscount, , @ModifiedDate)
-- Now handle constraints
DECLARE @violations TABLE
(
ConstraintName sysname,
ViolatedValue1 sql_variant,
ViolatedValue2 sql_variant
)
-- FK_SalesOrderDetail_SalesOrderHeader_SalesOrderID
IF NOT EXISTS (SELECT soh.SalesOrderId AS [Exists] FROM Sales.SalesOrderHeader soh WHERE soh.SalesOrderID = @SalesOrderId)
INSERT INTO @violations (ConstraintName, ViolatedValue1, ViolatedValue2)
VALUES (N'FK_SalesOrderDetail_SalesOrderHeader_SalesOrderID', @SalesOrderId, NULL)
-- FK_SalesOrderDetail_SpecialOfferProduct_SpecialOfferIDProductID
IF NOT EXISTS (SELECT sop.SpecialOfferID, sop.ProductID FROM [Sales].[SpecialOfferProduct] sop WHERE sop.SpecialOfferID = @SpecialOfferID AND sop.ProductID = @ProductId)
INSERT INTO @violations (ConstraintName, ViolatedValue1, ViolatedValue2)
VALUES (N'FK_SalesOrderDetail_SpecialOfferProduct_SpecialOfferIDProductID', @SpecialOfferId, @ProductId)
-- CK_SalesOrderDetail_OrderQty
IF NOT @OrderQty > 0
INSERT INTO @violations (ConstraintName, ViolatedValue1, ViolatedValue2)
VALUES (N'CK_SalesOrderDetail_OrderQty', @OrderQty, NULL)
-- CK_SalesOrderDetail_UnitPrice
IF NOT @UnitPrice >= 0.00
INSERT INTO @violations (ConstraintName, ViolatedValue1, ViolatedValue2)
VALUES (N'CK_SalesOrderDetail_UnitPrice', @UnitPrice, NULL)
-- CK_SalesOrderDetail_UnitPriceDiscout
IF NOT @UnitPriceDiscount >= 0.00
INSERT INTO @violations (ConstraintName, ViolatedValue1, ViolatedValue2)
VALUES (N'CK_SalesOrderDetail_UnitPriceDiscount', @UnitPriceDiscount, NULL)
-- Return a rowset containing violated constraints. On an item that doesn't violate anything, should return an empty rowset.
SELECT ConstraintName, ViolatedValue1, ViolatedValue2 FROM @violations
COMMIT TRANSACTION
END
Erzwingen von Einschränkungen vor einem Einfüge-, Update- oder Löschvorgang
USE AdventureWorks2012
GO
CREATE PROCEDURE Sales.usp_insert_SalesOrderDetails
@SalesOrderId int, @CarrierTrackingNumber nvarchar(25) = null, @OrderQty smallint, @ProductId int, @SpecialOfferID int,
@UnitPrice money, @UnitPriceDiscount money = 0.00, @ModifiedDate datetime = null
AS
BEGIN
BEGIN TRY
BEGIN TRANSACTION
-- Verify the constraints first.
-- FK_SalesOrderDetail_SalesOrderHeader_SalesOrderID
IF NOT EXISTS (SELECT soh.SalesOrderId FROM Sales.SalesOrderHeader soh WHERE soh.SalesOrderID = @SalesOrderId)
THROW 50547, N'This SalesOrderId does not exist in SalesOrderHeader', 1
-- FK_SalesOrderDetail_SpecialOfferProduct_SpecialOfferIDProductID
IF NOT EXISTS (SELECT sop.SpecialOfferID, sop.ProductID FROM [Sales].[SpecialOfferProduct] sop WHERE sop.SpecialOfferID = @SpecialOfferID AND sop.ProductID = @ProductId)
THROW 50547, N'This combination of SpecialOfferID and ProductID does not exist in SpecialOfferProduct', 1
-- CK_SalesOrderDetail_OrderQty
IF NOT @OrderQty > 0
THROW 50547, N'OrderQty must be greater than zero.', 1
-- CK_SalesOrderDetail_UnitPrice
IF NOT @UnitPrice >= 0.00
THROW 50547, N'UnitPrice cannot be negative.', 1
-- CK_SalesOrderDetail_UnitPriceDiscout
IF NOT @UnitPriceDiscount >= 0.00
THROW 50547, N'UnitPriceDiscount cannot be negative', 1
-- All verifications have now passed. Proceed to insert.
-- handle defaults for the insert.
-- This is to make the insert logic less complex. Default constraints on the table should be in sync with this logic.
-- Conversely, you can write an INSERT statement for each case where one or more values for the three columns with default constraints are not specified.
IF @ModifiedDate = null SET @ModifiedDate = GETDATE()
-- Calculate computed columnn and store it.
DECLARE @LineTotal numeric(38, 6)
SET @LineTotal = (isnull((@UnitPrice * ((1.0) - @UnitPriceDiscount)) * @OrderQty, (0.0)))
-- Insert the row.
INSERT INTO Sales.SalesOrderDetail
(SalesOrderID, CarrierTrackingNumber, OrderQty, ProductID, SpecialOfferID, UnitPrice, UnitPriceDiscount, ModifiedDate)
VALUES
(@SalesOrderId, @CarrierTrackingNumber, @OrderQty, @ProductID, @SpecialOfferID, @UnitPrice, @UnitPriceDiscount, @ModifiedDate)
COMMIT TRANSACTION
END TRY
BEGIN CATCH
IF @@TRANCOUNT > 0
ROLLBACK TRANSACTION
THROW;
END CATCH
END