Learn more about SQL Server tools

mssqltips logo
giveaway
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Workaround for lack of support for constraints on SQL Server Memory-Optimized Tables


By:   |   Read Comments (7)   |   Related Tips: More > In-Memory OLTP

Problem

SQL Server 2014 offers In-Memory OLTP, but you may be hesitant to use this because of the lack of support for constraints on Memory-Optimized Tables. In this tip we will see how we can solve these issues.

Solution

One of the reasons that might keep you from migrating tables to In-Memory OLTP is the lack of support for constraints and default values for Memory-Optimized Tables. With the help of stored procedures, views, triggers and a bit of ingenuity we can overcome this limitation.

So before we start, I will first give you a quick review about the types of constraints and their support for Memory-Optimized Tables.

Types of Constraints

Type of Constraint

Supported

Description

NOT NULL

Yes

Specifies that the column does not accept NULL values.

CHECK

No

Enforces domain integrity by limiting the values that can be put in a column.

UNIQUE

No

Enforce the uniqueness of the values in a set of columns.

PRIMARY KEY

Yes

Identify the column or set of columns that have values that uniquely identify a row in a table.

FOREIGN KEY

No

Identify and enforce the relationships between tables.

DEFAULT

No

Provides a default value to a column when the INSERT statement does not provide a specific value.

So, we need a work around for FOREIGN KEY, CHECK, UNIQUE and DEFAULT constraints.

Way to resolve the constraint limitations

At first glance, the trivial solution is to use wrapper views and create triggers for DML operations, but there is an issue with that. We must ensure that nobody inserts records that violate the constraints on the base tables.

The solution is to create a user to act as a proxy, set permissions and create the triggers with the EXECUTE AS USER clause.

Sample implementation

I have created a simple design to show how to set up constraints. Here is a diagram of the on disk tables.

Diagram of tables

From the image above we can see the following relations defined:

  • Table OnDisk.OrderHeader establishes a FOREIGN KEY constraint on column CustomerID that references column CustomerID on OnDisk.Customers table. The Column CustomerID is nullable because I created the foreign key with the ON DELETE SET NULL clause.
  • Table OnDisk.OrderDetail has two foreign keys. One is on ProductID which references table OnDisk.Products and the other is on OrderID to reference OnDisk.OrderHeader table. I created the last foreign key with ON DELETE CASCADE clause, so if a row is deleted from OnDisk.OrderHeader table, all rows of OnDisk.OrderDetail which are referenced will be deleted.

Constraints

Table OnDisk.Products has a CHECK constraint to verify that unit price is bigger than unit cost. Table OnDisk.Customers has a UNIQUE constraint on CustomerName and CustomerAddress, so no customers with the same name and address can be in the table.

Default values

Table OnDisk.OrderHeader has a default value of GETDATE() for column OrderDate.

Scripts to simulate constraints for memory optimized tables

I will guide you step by step on how to implement the constraints with the following scripts which I will explain next.

NOTE: You can download the scripts via the attached .zip file which you can unzip and use to execute the sample code that is presented in this tip.

1. Create sample database

First we need to create a sample database with a Memory-Optimized Filegroup.

CREATE DATABASE TestDB
ON PRIMARY
 (NAME = TestDB_file1,
  FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Data\TestDB_1.mdf',
     SIZE = 100MB,     
     FILEGROWTH = 10%),
FILEGROUP TestDB_MemoryOptimized_filegroup CONTAINS MEMORY_OPTIMIZED_DATA
 ( NAME = TestDB_MemoryOptimized,
  FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Data\TestDB_MemoryOptimized')
LOG ON
 ( NAME = TestDB_log_file1,
  FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Data\TestDB_1.ldf',
     SIZE = 100MB,     
     FILEGROWTH = 10%)
GO

2. Create schemas for On Disk and In Memory tables

I decided to use two different schemas. One for Disk based tables and the other for Memory optimized tables.

USE [TestDB]
GO
CREATE SCHEMA OnDisk
GO
CREATE SCHEMA InMemory
GO

3. Create On Disk tables

The next script creates the schema and disk based tables that we will use as reference.  These are only used as reference so we can mimic the behavior.

USE TestDB;
GO

/* First we drop the tables if they exists*/
IF OBJECT_ID('OnDisk.OrderDetail','U') IS NOT NULL
BEGIN
 DROP TABLE OnDisk.OrderDetail
END
GO
IF OBJECT_ID('OnDisk.OrderHeader','U') IS NOT NULL
BEGIN
 DROP TABLE OnDisk.OrderHeader
END
GO
IF OBJECT_ID('OnDisk.Customers','U') IS NOT NULL
BEGIN
 DROP TABLE OnDisk.Customers
END
GO
IF OBJECT_ID('OnDisk.Products','U') IS NOT NULL
BEGIN
 DROP TABLE OnDisk.Products
END
GO
/*------------------------------------------------------------------------*/
/* Now we create the tables*/
CREATE TABLE OnDisk.Products
(
  ProductID   INT IDENTITY (1, 1) NOT NULL,
  Description  NVARCHAR (50) NOT NULL,
  UnitCost    MONEY NULL,
  UnitPrice   MONEY NULL,
  UnitsInStock  INT NULL,
  Active     BIT NULL,
  CHECK ( (UnitPrice > UnitCost)),
  PRIMARY KEY CLUSTERED (ProductID)
)
CREATE TABLE OnDisk.Customers
(
  CustomerID    INT IDENTITY (1, 1) NOT NULL,
  CustomerName   NVARCHAR (50) NOT NULL,
  CustomerAddress  NVARCHAR (50) NULL,
  PRIMARY KEY CLUSTERED (CustomerID),
  CONSTRAINT U_OnDisk_Customersg_1 UNIQUE NONCLUSTERED (CustomerName, CustomerAddress)
)
CREATE TABLE OnDisk.OrderHeader
(
  OrderID   INT IDENTITY (1, 1) NOT NULL,
  OrderDate  DATE NOT NULL DEFAULT (GETDATE ()),
  CustomerID  INT NULL,
  TotalDue   MONEY NOT NULL,
  PRIMARY KEY CLUSTERED (OrderID),
  FOREIGN KEY (CustomerID)
   REFERENCES OnDisk.Customers (CustomerID) ON DELETE SET NULL
)
CREATE TABLE OnDisk.OrderDetail
(
  OrderID     INT NOT NULL,
  OrderDetailID  INT IDENTITY (1, 1) NOT NULL,
  ProductID    INT NOT NULL,
  Quantity    INT NOT NULL,
  PRIMARY KEY CLUSTERED (OrderID, OrderDetailID),
  FOREIGN KEY (ProductID) 
  REFERENCES OnDisk.Products (ProductID),
  FOREIGN KEY (OrderID) 
  REFERENCES OnDisk.OrderHeader (OrderID) ON DELETE CASCADE
)
GO

4. Create database user

This script will create a new user "InMemoryUsr" without a login and with default schema "InMemory".

USE TestDB
GO
CREATE USER InMemoryUsr WITHOUT LOGIN WITH DEFAULT_SCHEMA = InMemory
GO

5. Create Memory-Optimized tables

Now we create the Memory-Optimized tables.

USE TestDB;
GO
IF OBJECT_ID('InMemory.tblProducts','U') IS NOT NULL
BEGIN
 DROP TABLE InMemory.tblProducts
END
GO
CREATE TABLE InMemory.tblProducts
(
  ProductID   INT NOT NULL,
  Description  NVARCHAR (50) NOT NULL,
  UnitCost    MONEY NULL,
  UnitPrice   MONEY NULL,
  UnitsInStock  INT NULL,
  Active     BIT NULL,  
  PRIMARY KEY NONCLUSTERED HASH (ProductID) 
    WITH (BUCKET_COUNT = 1024) 
) WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_AND_DATA)
GO
IF OBJECT_ID('InMemory.tblCustomers','U') IS NOT NULL
BEGIN
 DROP TABLE InMemory.tblCustomers
END
GO
CREATE TABLE InMemory.tblCustomers
(
  CustomerID    INT NOT NULL,
  CustomerName   NVARCHAR (50) COLLATE Latin1_General_100_BIN2 NOT NULL,
  CustomerAddress  NVARCHAR (50) COLLATE Latin1_General_100_BIN2 NOT NULL,
  ChkSum    INT NOT NULL
  PRIMARY KEY NONCLUSTERED HASH (CustomerID) WITH (BUCKET_COUNT = 1024),
  INDEX IX_InMemory_TblCustomers_CustomerName_CustomerAddress HASH (ChkSum) 
    WITH (BUCKET_COUNT = 1024)
) WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_AND_DATA)
GO
IF OBJECT_ID('InMemory.tblOrderHeader','U') IS NOT NULL
BEGIN
 DROP TABLE InMemory.tblOrderHeader
END
GO

CREATE TABLE InMemory.tblOrderHeader
(
  OrderID   INT NOT NULL,
  OrderDate  DATE NOT NULL,
  CustomerID  INT NULL,
  TotalDue   MONEY NOT NULL,
  PRIMARY KEY NONCLUSTERED HASH (OrderID) 
    WITH (BUCKET_COUNT = 1024)
) WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_AND_DATA)
GO
IF OBJECT_ID('InMemory.tblOrderDetail','U') IS NOT NULL
BEGIN
 DROP TABLE InMemory.tblOrderDetail
END
GO

CREATE TABLE InMemory.tblOrderDetail
(
  OrderID     INT NOT NULL,
  OrderDetailID  INT NOT NULL,
  ProductID    INT NOT NULL,
  Quantity    INT NOT NULL,
  PRIMARY KEY NONCLUSTERED HASH (OrderID, OrderDetailID) 
    WITH (BUCKET_COUNT = 1024) ,
) WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_AND_DATA)
GO

Note that the table InMemory.tblCustomers has an additional column called "ChkSum". That is in order to set up the UNIQUE Constraint on CustomerName and CustomerAddress. At first sight you may think about performing a string comparison, but that would be very inefficient. So I decided to create the "ChkSum" column and an index to keep the checksum of columns CustomerName and CustomerAddress to speed up comparisons. To understand this, think about how a UNIQUE constraint is implemented by the SQL Server engine. The UNIQUE constraint is much like a UNIQUE INDEX which is not supported on Memory-Optimized Tables. Also the CHECKSUM() function can be used to build a hash index.

6. Create sequences to mimic IDENTITY columns

Because of the lack of support for IDENTITY columns for Memory-Optimized Tables, we need to create sequence objects that will provide us the same functionality when we use them inside a trigger.

USE [TestDB]
GO
IF OBJECT_ID('InMemory.SO_Products_ProductID', 'SO') IS NOT NULL 
  BEGIN
    DROP SEQUENCE InMemory.SO_Products_ProductID
  END
GO
CREATE SEQUENCE InMemory.SO_Products_ProductID 
START WITH 1
INCREMENT BY 1
GO
IF OBJECT_ID('InMemory.SO_Customers_CustomerID', 'SO') IS NOT NULL 
  BEGIN
    DROP SEQUENCE InMemory.SO_Customers_CustomerID
  END
GO
CREATE SEQUENCE InMemory.SO_Customers_CustomerID 
START WITH 1
INCREMENT BY 1
GO
IF OBJECT_ID('InMemory.SO_OrderHeader_OrderID', 'SO') IS NOT NULL 
  BEGIN
    DROP SEQUENCE InMemory.SO_OrderHeader_OrderID
  END
GO
CREATE SEQUENCE InMemory.SO_OrderHeader_OrderID 
START WITH 1
INCREMENT BY 1
GO
IF OBJECT_ID('InMemory.SO_OrderDetail_OrderDetailID', 'SO') IS NOT NULL 
  BEGIN
    DROP SEQUENCE InMemory.SO_OrderDetail_OrderDetailID
  END
GO
CREATE SEQUENCE InMemory.SO_OrderDetail_OrderDetailID 
START WITH 1
INCREMENT BY 1
GO

7. Create views

We are ready to create the views that will act as a wrapper to the base tables.

These are very simple views with the addition of the WITH SNAPSHOT hint to avoid cross-container transactions when accessing Memory-Optimized tables. Also note that the view InMemory.Customers does not include column "ChkSum". That column is for our internal use only.

USE TestDB
GO
IF OBJECT_ID('InMemory.Customers', 'V') IS NOT NULL 
  BEGIN
    DROP VIEW InMemory.Customers
  END
GO
CREATE VIEW InMemory.Customers
 WITH SCHEMABINDING
AS
  SELECT CustomerID ,
      CustomerName ,
      CustomerAddress
  FROM  InMemory.tblCustomers WITH (SNAPSHOT)
GO

USE TestDB
GO
IF OBJECT_ID('InMemory.Products', 'V') IS NOT NULL 
  BEGIN
    DROP VIEW InMemory.Products
  END
GO
CREATE VIEW InMemory.Products
 WITH SCHEMABINDING
AS
  SELECT ProductID ,
      Description ,
      UnitCost ,
      UnitPrice ,
      UnitsInStock ,
      Active
  FROM  InMemory.tblProducts WITH (SNAPSHOT)
GO

USE TestDB
GO
IF OBJECT_ID('InMemory.OrderHeader', 'V') IS NOT NULL 
  BEGIN
    DROP VIEW InMemory.OrderHeader
  END
GO
CREATE VIEW InMemory.OrderHeader
 WITH SCHEMABINDING
AS
  SELECT OrderID ,
      OrderDate ,
      CustomerID ,
      TotalDue
  FROM  InMemory.tblOrderHeader WITH (SNAPSHOT)
GO

USE TestDB
GO
IF OBJECT_ID('InMemory.OrderDetail', 'V') IS NOT NULL 
  BEGIN
    DROP VIEW InMemory.OrderDetail
  END
GO
CREATE VIEW InMemory.OrderDetail
 WITH SCHEMABINDING
AS
  SELECT OrderID ,
      OrderDetailID ,
      ProductID ,
      Quantity
  FROM  InMemory.tblOrderDetail WITH (SNAPSHOT)
GO

8. Create INSTEAD OF Triggers for View InMemory.Products

Here we will simulate a CHECK constraint on the INSTEAD OF INSERT and INSTEAD OF UPDATE triggers to verify that unit price is bigger than unit cost. To do this, we only need to check for the existence of records that do not accomplish the condition above and if so, throw an exception.

USE TestDB
GO
IF OBJECT_ID('InMemory.TR_Products_Insert', 'TR') IS NOT NULL 
  BEGIN
    DROP TRIGGER InMemory.TR_Products_Insert
  END
GO
CREATE TRIGGER InMemory.TR_Products_Insert ON InMemory.Products  
 WITH EXECUTE AS 'InMemoryUsr' 
 INSTEAD OF INSERT
AS
 SET NOCOUNT ON
 --CHECK ( (UnitPrice > UnitCost))  
 IF EXISTS ( SELECT 0
        FROM  INSERTED
        WHERE  UnitPrice < UnitCost ) 
    BEGIN
      ;THROW 50001, 'Violation of CHECK Constraint! (UnitPrice > UnitCost)!', 1
    END
  INSERT INTO InMemory.tblProducts WITH (SNAPSHOT)
      ( ProductID, 
       Description ,
       UnitCost ,
       UnitPrice ,
       UnitsInStock ,
       Active
      )
      SELECT NEXT VALUE FOR
          InMemory.SO_Products_ProductID ,
          Description ,
          UnitCost ,
          UnitPrice ,
          UnitsInStock ,
          Active
      FROM  INSERTED 
 
 GO

USE TestDB
GO
IF OBJECT_ID('InMemory.TR_tblProducts_Update', 'TR') IS NOT NULL 
  BEGIN
    DROP TRIGGER InMemory.TR_tblProducts_Update
  END
GO
CREATE TRIGGER InMemory.TR_tblProducts_Update ON InMemory.Products
 WITH EXECUTE AS 'InMemoryUsr'
  INSTEAD OF UPDATE
AS
 SET NOCOUNT ON
  
 --CHECK ( (UnitPrice > UnitCost))   
  IF EXISTS ( SELECT 0
        FROM  INSERTED
        WHERE  UnitPrice < UnitCost) 
    BEGIN
      ;THROW 50001, 'Violation of CHECK Constraint! (UnitPrice > UnitCost)', 1
    END
 UPDATE InMemory.tblProducts WITH (SNAPSHOT)
  SET  Description = I.DESCRIPTION,
    UnitCost = I.UnitCost,
    UnitPrice = I.UnitPrice,
    UnitsInStock = I.UnitsInStock,
    Active = I.Active
 FROM INSERTED I 
 INNER JOIN InMemory.tblProducts P WITH (SNAPSHOT)
  ON I.ProductID = P.ProductID
 GO

In the INSTEAD OF DELETE trigger we must validate that the simulated foreign key on table InMemory.tblOrderDetail won't be violated. So we need to check the records to be deleted on table InMemory.tblProducts are not referenced by table InMemory.tblOrderDetail.

USE TestDB
GO
IF OBJECT_ID('InMemory.TR_Products_Delete', 'TR') IS NOT NULL 
  BEGIN
    DROP TRIGGER InMemory.TR_Products_Delete
  END
GO
CREATE TRIGGER InMemory.TR_Products_Delete ON InMemory.Products
 WITH EXECUTE AS 'InMemoryUsr'
 INSTEAD OF DELETE
AS
 SET NOCOUNT ON
 
 -- TABLE InDisk.OrderDetail
 -- FOREIGN KEY (ProductID) REFERENCES OnDisk.Products (ProductID) 
  IF EXISTS ( SELECT 0
        FROM  DELETED D
    INNER JOIN InMemory.tblOrderDetail OD WITH (SNAPSHOT)
     ON D.ProductID = OD.ProductID
        ) 
    BEGIN
      ;THROW 50001, 'Violation of FOREIGN KEY Constraint! Table InMemory.OrderDetail, Column (ProductID)', 1
    END
    
 DELETE InMemory.tblProducts WITH (SNAPSHOT)
  FROM InMemory.tblProducts P WITH (SNAPSHOT)
  INNER JOIN DELETED D
  ON p.ProductID = D.ProductID
GO

9. Create INSTEAD OF Triggers for View InMemory.Customers

In the following scripts we are going to emulate the UNIQUE constraint for table InMemory.tblCustomers. As I mentioned before, we are using the CHECKSUM() function to implement the UNIQUE constraint.  We must check that data to be inserted is different then existing data in the table and that data is different itself. This is very important, because if you omit the last check, you will insert invalid data without notice.

USE TestDB
GO
IF OBJECT_ID('InMemory.TR_Customers_Insert', 'TR') IS NOT NULL 
  BEGIN
    DROP TRIGGER InMemory.TR_Customers_Insert
  END
GO
CREATE TRIGGER InMemory.TR_Customers_Insert ON InMemory.Customers
 WITH EXECUTE AS 'InMemoryUsr'
  INSTEAD OF INSERT  
AS
 SET NOCOUNT ON
 --CONSTRAINT U_OnDisk_Customersg_1 UNIQUE NONCLUSTERED (CustomerName, CustomerAddress)
  IF EXISTS (
    -- Check if rows to be inserted are consistent with CHECK constraint by themselves
    SELECT 0
        FROM  INSERTED I
    GROUP BY CHECKSUM(I.CustomerName, I.CustomerAddress) 
    HAVING COUNT(0) > 1
    UNION ALL
        
       -- Check if rows to be inserted are consistent with UNIQUE constraint with existing data
    SELECT 0
        FROM  INSERTED I
    INNER JOIN InMemory.tblCustomers C WITH (SNAPSHOT)
      ON  C.ChkSum = CHECKSUM(I.CustomerName, I.CustomerAddress)
    ) 
    BEGIN
      ;THROW 50001, 'Violation of UNIQUE Constraint! (CustomerName, CustomerAddress)', 1
    END
  INSERT INTO InMemory.tblCustomers WITH (SNAPSHOT)
      ( CustomerID ,
       CustomerName ,
       CustomerAddress,
    chksum
      )
  SELECT  NEXT VALUE FOR InMemory.SO_Customers_CustomerID ,
      CustomerName ,
      CustomerAddress,
   CHECKSUM(CustomerName, CustomerAddress)
 FROM INSERTED
 GO

USE TestDB
GO
IF OBJECT_ID('InMemory.TR_Customers_Update', 'TR') IS NOT NULL 
  BEGIN
    DROP TRIGGER InMemory.TR_Customers_Update
  END
GO
CREATE TRIGGER InMemory.TR_Customers_Update ON InMemory.Customers
 WITH EXECUTE AS 'InMemoryUsr'
  INSTEAD OF UPDATE
AS
 --CONSTRAINT U_OnDisk_Customersg_1 UNIQUE NONCLUSTERED (CustomerName, CustomerAddress)
  IF EXISTS (
    -- Check if rows to be inserted are consistent with UNIQUE constraint by themselves
    SELECT 0
        FROM  INSERTED I
    GROUP BY CHECKSUM(I.CustomerName, I.CustomerAddress)
    HAVING COUNT(0) > 1
    UNION ALL
        
       -- Check if rows to be inserted are consistent with UNIQUE constraint with existing data
    SELECT 0
        FROM  INSERTED I
    INNER JOIN InMemory.tblCustomers C WITH (SNAPSHOT)
      ON  C.ChkSum = CHECKSUM(I.CustomerName, I.CustomerAddress)
    ) 
    BEGIN
      ;THROW 50001, 'Violation of UNIQUE Constraint! (CustomerName, CustomerAddress)', 1
    END
  UPDATE InMemory.tblCustomers WITH (SNAPSHOT)
  SET  CustomerName = I.CustomerName,
       CustomerAddress = I.CustomerAddress,
    ChkSum = CHECKSUM(I.CustomerName, I.CustomerAddress)
 FROM INSERTED I
 INNER JOIN InMemory.tblCustomers C WITH (SNAPSHOT)
  ON I.CustomerID = C.CustomerID
 GO

Inside the INSTEAD OF DELETE trigger we will set the CustomerID column of InMemory.tblOrderHeader table to NULL following the foreign key definition.

USE TestDB
GO
IF OBJECT_ID('InMemory.TR_Customers_Delete', 'TR') IS NOT NULL 
  BEGIN
    DROP TRIGGER InMemory.TR_Customers_Delete
  END
GO
CREATE TRIGGER InMemory.TR_Customers_Delete ON InMemory.Customers
 WITH EXECUTE AS 'InMemoryUsr'
  INSTEAD OF DELETE
AS
 SET NOCOUNT ON
 -- Enforces FOREIGN KEY of OrderHeader 
 --FOREIGN KEY (CustomerID)
 --REFERENCES OnDisk.Customers (CustomerID) ON DELETE SET NULL
 UPDATE InMemory.tblOrderHeader WITH (SNAPSHOT)
  SET CustomerID = NULL
 FROM InMemory.tblOrderHeader OH WITH (SNAPSHOT)
 INNER JOIN DELETED D
  ON OH.CustomerID = D.CustomerID 
  DELETE InMemory.tblCustomers WITH (SNAPSHOT)
  FROM InMemory.tblCustomers C WITH (SNAPSHOT)
  INNER JOIN DELETED D
  ON C.CustomerID = D.CustomerID 
    
 GO

10. Create INSTEAD OF Triggers for View InMemory.OrderHeader

To emulate the foreign key, we need to check that the CustomerID value being inserted exists in InMemory.tblCustomers table. And to implement the default value of column OrderDate we can use the ISNULL() function to check if a value has been inserted, and if not, we issue a call to GETDATE() function. In addition, in the INSTEAD OF DELETE trigger we must delete all referenced records of InMemory.tblOrderDetail table to enforce cascade deletion.

USE TestDB
GO
IF OBJECT_ID('InMemory.TR_OrderHeader_Insert', 'TR') IS NOT NULL 
  BEGIN
    DROP TRIGGER InMemory.TR_OrderHeader_Insert
  END
GO
CREATE TRIGGER InMemory.TR_OrderHeader_Insert ON InMemory.OrderHeader
 WITH EXECUTE AS 'InMemoryUsr'
  INSTEAD OF INSERT
AS
 SET NOCOUNT ON
  -- Table InDisk.OrderDetail
  -- FOREIGN KEY (CustomerID) REFERENCES OnDisk.Customers (CustomerID) ON DELETE SET NULL
  IF NOT EXISTS ( SELECT 0
        FROM  INSERTED I
    INNER JOIN InMemory.tblCustomers C WITH (SNAPSHOT)
     ON I.CustomerID = C.CustomerID
         ) 
    BEGIN
      ;THROW 50001, 'Violation of FOREIGN KEY Constraint! Table InMemory.Customers, Column (CustomerID)', 1
    END
  INSERT INTO InMemory.tblOrderHeader WITH (SNAPSHOT)
      ( OrderID ,
       OrderDate ,
       CustomerID ,
       TotalDue
      )      
  SELECT  NEXT VALUE FOR InMemory.SO_OrderHeader_OrderID ,
      ISNULL(OrderDate, GETDATE()) , -- Enforces DEFAULT Value
      CustomerID,
   TotalDue
 FROM INSERTED
 GO

USE TestDB
GO
IF OBJECT_ID('InMemory.TR_OrderHeader_Update', 'TR') IS NOT NULL 
  BEGIN
    DROP TRIGGER InMemory.TR_OrderHeader_Update
  END
GO
CREATE TRIGGER InMemory.TR_OrderHeader_Update ON InMemory.OrderHeader
 WITH EXECUTE AS 'InMemoryUsr'
  INSTEAD OF UPDATE
AS
 SET NOCOUNT ON
  --FOREIGN KEY (CustomerID)
  --  REFERENCES OnDisk.Customers (CustomerID) ON DELETE SET NULL
  IF NOT EXISTS ( SELECT 0
        FROM  INSERTED I
    INNER JOIN InMemory.tblCustomers C WITH (SNAPSHOT)
     ON I.CustomerID = C.CustomerID
        ) 
    BEGIN
      ;THROW 50001, 'Violation of FOREIGN KEY Constraint! Table InMemory.Customers, Column (CustomerID)', 1
    END
    
  UPDATE InMemory.tblOrderHeader WITH (SNAPSHOT)
  SET  OrderDate = I.OrderDate,
       CustomerID = I.CustomerID,
    TotalDue = I.TotalDue
 FROM INSERTED I
 INNER JOIN InMemory.tblOrderHeader OH WITH (SNAPSHOT)
  ON I.OrderID = OH.OrderID
  
 GO

USE TestDB
GO
IF OBJECT_ID('InMemory.TR_OrderHeader_Delete', 'TR') IS NOT NULL 
  BEGIN
    DROP TRIGGER InMemory.TR_OrderHeader_Delete
  END
GO
CREATE TRIGGER InMemory.TR_OrderHeader_Delete ON InMemory.OrderHeader
 WITH EXECUTE AS 'InMemoryUsr'
  INSTEAD OF DELETE
AS
 SET NOCOUNT ON
 -- FOREIGN KEY (OrderID) 
 --  REFERENCES OnDisk.OrderHeader (OrderID) ON DELETE CASCADE
 DELETE InMemory.tblOrderDetail WITH (SNAPSHOT)
  FROM InMemory.tblOrderDetail OD WITH (SNAPSHOT)
  INNER JOIN DELETED D
   ON OD.OrderID = D.OrderID 
 DELETE InMemory.tblOrderHeader WITH (SNAPSHOT)
  FROM InMemory.tblOrderHeader OH WITH (SNAPSHOT)
  INNER JOIN DELETED D
   ON OH.OrderID = D.OrderID 
GO

11. Create INSTEAD OF Triggers for View InMemory.OrderDetail

Now, to emulate the foreign keys which references InMemory.tblOrderHeader and InMemory.tblProducts we only need to check the existence of the keys in the parent tables.

USE TestDB
GO
IF OBJECT_ID('InMemory.TR_OrderDetail_Insert', 'TR') IS NOT NULL 
  BEGIN
    DROP TRIGGER InMemory.TR_OrderDetail_Insert
  END
GO
CREATE TRIGGER InMemory.TR_OrderDetail_Insert ON InMemory.OrderDetail
 WITH EXECUTE AS 'InMemoryUsr'
  INSTEAD OF INSERT
AS
 SET NOCOUNT ON
 -- FOREIGN KEY (OrderID) 
  --REFERENCES OnDisk.OrderHeader (OrderID) ON DELETE CASCADE
 IF NOT EXISTS (
    SELECT 0
    FROM InMemory.tblOrderHeader OH WITH (SNAPSHOT)
    INNER JOIN INSERTED I
     ON OH.OrderID = I.OrderID
    )
 BEGIN
  ;THROW 50001, 'Violation of FOREIGN KEY Constraint! Table InMemory.OrderHeader, Column (OrderID)', 1
 END 
 -- FOREIGN KEY (ProductID) 
  --REFERENCES OnDisk.Products (ProductID)
 IF NOT EXISTS (
    SELECT 0
    FROM InMemory.tblProducts P WITH (SNAPSHOT)
    INNER JOIN INSERTED I
     ON P.ProductID = I.ProductID
    )
 BEGIN
  ;THROW 50001, 'Violation of FOREIGN KEY Constraint! Table InMemory.Products, Column (ProductID)', 1
 END 
  INSERT INTO InMemory.tblOrderDetail WITH (SNAPSHOT)
      ( OrderID ,
       OrderDetailID ,
       ProductID ,
       Quantity
      )
  SELECT OrderID,
   NEXT VALUE FOR InMemory.SO_OrderDetail_OrderDetailID,
       ProductID,
       Quantity   
 FROM INSERTED
 GO

USE TestDB
GO
IF OBJECT_ID('InMemory.TR_OrderDetail_Update', 'TR') IS NOT NULL 
  BEGIN
    DROP TRIGGER InMemory.TR_OrderDetail_Update
  END
GO
CREATE TRIGGER InMemory.TR_OrderDetail_Update ON InMemory.OrderDetail
 WITH EXECUTE AS 'InMemoryUsr'
  INSTEAD OF UPDATE
AS
 SET NOCOUNT ON
 -- FOREIGN KEY (OrderID) 
 --  REFERENCES OnDisk.OrderHeader (OrderID) ON DELETE CASCADE
 IF NOT EXISTS (
    SELECT 0
    FROM InMemory.tblOrderHeader OH WITH (SNAPSHOT)
    INNER JOIN INSERTED I
     ON OH.OrderID = I.OrderID
    )
 BEGIN
  ;THROW 50001, 'Violation of FOREIGN KEY Constraint! Table InMemory.OrderHeader, Column (OrderID)', 1
 END 
 -- FOREIGN KEY (ProductID) 
  --REFERENCES OnDisk.Products (ProductID)
 IF NOT EXISTS (
    SELECT 0
    FROM InMemory.tblProducts P WITH (SNAPSHOT)
    INNER JOIN INSERTED I
     ON P.ProductID = I.ProductID
    )
 BEGIN
  ;THROW 50001, 'Violation of FOREIGN KEY Constraint! Table InMemory.Products, Column (ProductID)', 1
 END 
 
 UPDATE InMemory.tblOrderDetail WITH (SNAPSHOT)
  SET  ProductID = I.ProductID,
    Quantity = I.Quantity
 FROM INSERTED I
 INNER JOIN InMemory.tblOrderDetail OD WITH (SNAPSHOT)
  ON  I.OrderID = OD.OrderID AND
     I.OrderDetailID = OD.OrderDetailID 
  
 GO

The INSTEAD OF DELETE Trigger only performs the deletion of InMemory.tblOrderDetail rows.

USE TestDB
GO
IF OBJECT_ID('InMemory.TR_OrderDetail_Delete', 'TR') IS NOT NULL 
  BEGIN
    DROP TRIGGER InMemory.TR_OrderDetail_Delete
  END
GO
CREATE TRIGGER InMemory.TR_OrderDetail_Delete ON InMemory.OrderDetail
 WITH EXECUTE AS 'InMemoryUsr'
  INSTEAD OF DELETE
AS
 SET NOCOUNT ON
  
 DELETE InMemory.tblOrderDetail WITH (SNAPSHOT)
  FROM InMemory.tblOrderDetail OD WITH (SNAPSHOT)
  INNER JOIN DELETED D
   ON  OD.OrderID = D.OrderID AND 
     OD.OrderDetailID = D.OrderDetailID
      
GO

12. Setting up user permissions

This is the last step. We set the permissions and we are good to go.

USE TestDB
GO
GRANT SELECT, INSERT, UPDATE, DELETE ON InMemory.Products TO public
GRANT SELECT, INSERT, UPDATE, DELETE ON InMemory.OrderDetail TO public
GRANT SELECT, INSERT, UPDATE, DELETE ON InMemory.Customers TO public
GRANT SELECT, INSERT, UPDATE, DELETE ON InMemory.OrderHeader TO PUBLIC
GO
REVOKE SELECT, INSERT, UPDATE, DELETE ON InMemory.tblProducts TO public
REVOKE SELECT, INSERT, UPDATE, DELETE ON InMemory.tblCustomers TO public
REVOKE SELECT, INSERT, UPDATE, DELETE ON InMemory.tblOrderHeader TO public
REVOKE SELECT, INSERT, UPDATE, DELETE ON InMemory.tblOrderDetail TO public
GO
GRANT SELECT, INSERT, UPDATE, DELETE ON InMemory.tblProducts TO InMemoryUsr
GRANT SELECT, INSERT, UPDATE, DELETE ON InMemory.tblCustomers TO InMemoryUsr
GRANT SELECT, INSERT, UPDATE, DELETE ON InMemory.tblOrderHeader TO InMemoryUsr
GRANT SELECT, INSERT, UPDATE, DELETE ON InMemory.tblOrderDetail TO InMemoryUsr
GO

13. Test execution

I have created a test script with statements that will produce constraint violations and others that should work perfectly.

USE TestDB
GO

-- CHECK Constraint Violation
INSERT INTO InMemory.Products(Description, UnitCost, UnitPrice, UnitsInStock, Active)
VALUES   (N'Adjustable Race', 100, 50, 10, 1)
GO
INSERT INTO InMemory.Products(Description, UnitCost, UnitPrice, UnitsInStock, Active)
VALUES   (N'Adjustable Race', 100, 500, 10, 1)
GO

-- UNIQUE Constraint violation
INSERT INTO InMemory.Customers(CustomerName, CustomerAddress)
 SELECT N'John Doe', N'1970 Napa Ct.'
 UNION ALL
 SELECT N'John Doe', N'1970 Napa Ct.'
GO
INSERT INTO InMemory.Customers(CustomerName, CustomerAddress)
VALUES   (N'John Doe', N'1970 Napa Ct.')
GO

-- UNIQUE Constraint violation
INSERT INTO InMemory.Customers(CustomerName, CustomerAddress)
VALUES   (N'John Doe', N'1970 Napa Ct.')
GO

-- FOREIGN KEY violation
INSERT INTO InMemory.OrderHeader(CustomerID, TotalDue)
VALUES   (11, 500)
GO
INSERT INTO InMemory.OrderHeader(CustomerID, TotalDue)
VALUES   (1, 500)
GO

-- FOREIGN KEY (InMemory.Products) violation
INSERT INTO InMemory.OrderDetail(OrderID, ProductID, Quantity)
VALUES   (1, 2, 1)
GO
INSERT INTO InMemory.OrderDetail(OrderID, ProductID, Quantity)
Next Steps


Last Update:






About the author
MSSQLTips author Daniel Farina Daniel Farina was born in Buenos Aires, Argentina. Self-educated, since childhood he showed a passion for learning.

View all my tips





More SQL Server Solutions











Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


Get free SQL tips:

*Enter Code refresh code     



Tuesday, March 14, 2017 - 12:34:45 PM - Paul Hunter Back To Top

 I like the article, but doesn't all the extra overhead of triggers, additional storage for checksum, accessing data through views, etc. negate some of the advantages of memory optomized tables?  Would it be easier and more straight forward to implement all of that logic through memory optomized procedures and set the permissions to only permit DML via those procedures?

 


Wednesday, May 21, 2014 - 11:29:25 AM - DWalker Back To Top

Yes, I had missed that.  thanks.


Tuesday, May 20, 2014 - 6:11:27 PM - Daniel Farina Back To Top

Hi DWalker,

Check out step 10. Create INSTEAD OF Triggers for View InMemory.OrderHeader.

 

  SELECT  NEXT VALUE FOR InMemory.SO_OrderHeader_OrderID ,
      ISNULL(OrderDate, GETDATE()) , -- Enforces DEFAULT Value
      CustomerID,
   TotalDue
FROM INSERTED


Tuesday, May 20, 2014 - 1:17:51 PM - DWalker Back To Top

"So, we need a work around for FOREIGN KEY, CHECK, UNIQUE and DEFAULT constraints."

I don't see a workaround for DEFAULT constraints.  The others may be harder to deal with, but this article doesn't seem to say anything about Default constraints... unless I missed it.


Thursday, December 19, 2013 - 11:18:59 AM - Bill Back To Top

Perhaps I overlooked something within this otherwise extremely useful article :)

To restate what I believe the original respondent was attemtping to suggest:

CHECKSUM and HASHBYTES can be used to check "sameness", but they cannot be used to check "uniqueness". Both CHECKSUM and HASHBYTES can return the same value when different values are passed to them (I believe this is what the original respondent was attempting to say).  Thus the phrase (in section 9) "we are using the CHECKSUM() function to implement the UNIQUE constraint" is false (CHECKSUM is an insufficient check for uniqueness). As written, section 9's code can currently and incorrectly THROW a "Violation of UNIQUE Constraint!" when the values (being inserted or updated) are actually unique.

Thus section 9's code is incomplete. If duplicate CHECKSUM (or HASHBYTES) values are detected (in the current code), the actual parameter values (being passed to them) will still need to be checked for uniqueness, via a "string comparison" or SARG (under IF EXISTS). These second checks need to be accomplished within section 9's two BEGIN END blocks (that currently THROW a "Violation of UNIQUE Constraint!").  These second checks are going to be inefficient, butr the code's need to harness them should be relatively rare (and rarer still, if HASHBYTES were to be used instead of CHECKSUM).


Friday, November 22, 2013 - 9:30:31 AM - Daniel Farina Back To Top

You are right about collisions.

Since this is a sample I chose this method because I wanted to introduce the concept of Hash functions (in this case CHECKSUM() ) and hash tables. Basically this way we are building hash table and that is how hash indexes work. Because of this I wanted to reference this Jeremy’s tip :“CHECKSUM Functions in SQL Server 2005” http://www.mssqltips.com/sqlservertip/1023/checksum-functions-in-sql-server-2005/

In that tip he states “The CHECKSUM is intended to build a hash index based on an expression or column list. “

And taking his concept I created a composite unique constraint (I could have created a constraint on CustomerName alone).

Of course a Hash index would be the best way because SQL Server handles collisions by itself.

By the way, a range index isn’t the best choice for equality searches. On range indexes (btree based) searches are O(log(n)) while on hash indexes searches are O(1).

Also other method is to use a Memory-Optimized Table type to load INSERTED pseudo table into it and then perform all the comparisons so we can get rid of TempDB.

Thank you for reading and commenting!


Monday, November 18, 2013 - 11:36:12 AM - Collisions Back To Top

You chose to drop two equality string compares for an equality compare to a function (CHECKSUM) operating on two strings?  You're still doing a fair bit of operations on strings, and you could have put indexes (hash or nonclustered) on the string columns regardless.  I assure you, actual measuring the effects of string compares will not show a major performance issue.

 

More importantly, comparing on only a hash function is bad, and comparing on only a CHECKSUM is completely worthless.  Please see the CHECKSUM values of the following:

PRINT CHECKSUM(1768,99)
PRINT CHECKSUM(1770,67)
PRINT CHECKSUM(1771,83)
PRINT CHECKSUM(1772,35)

or the following, if you insist on strings:

PRINT CHECKSUM('911','47')
PRINT CHECKSUM('914','17')
PRINT CHECKSUM('916','37')
PRINT CHECKSUM('917','27')

 

Moving from CHECKSUM() to HASHBYTES() merely reduces collisions, it does not eliminate them:

PRINT HASHBYTES('MD5',0xd131dd02c5e6eec4693d9a0698aff95c2fcab58712467eab4004583eb8fb7f8955ad340609f4b30283e488832571415a085125e8f7cdc99fd91dbdf280373c5bd8823e3156348f5bae6dacd436c919c6dd53e2b487da03fd02396306d248cda0e99f33420f577ee8ce54b67080a80d1ec69821bcb6a8839396f9652b6ff72a70)
PRINT HASHBYTES('MD5',0xd131dd02c5e6eec4693d9a0698aff95c2fcab50712467eab4004583eb8fb7f8955ad340609f4b30283e4888325f1415a085125e8f7cdc99fd91dbd7280373c5bd8823e3156348f5bae6dacd436c919c6dd53e23487da03fd02396306d248cda0e99f33420f577ee8ce54b67080280d1ec69821bcb6a8839396f965ab6ff72a70)
---------------------------------------------------------------*---------------------------------------------------*---------------------------*-----------------------------------------------*---------------------------------------------------*---------------------------*----------

 

HASHBYTES('SHA1'... is perhaps a little better (reduced effort collision attacks exist), and HASHBYTES('SHA512'... is better still, but the probability of collisions is always strictly greater than zero, but both of these cost MUCH more processing time to use, and need very careful designs if used with multiple columns to hash, i.e.

-- Straight concatenation
-- Trivial failure example
print '1' + '23'
print '12' + '3'
-- Delimited concatenation
-- Trivial failure example
print '1,' + ',' + '23'
print '1' + ',' + ',23'
-- Fixed width concatenation
-- No trivial failure example, but more transformations and a lot larger data being hashed = slower
-- BEWARE 8000 character limit.
print CONVERT(CHAR(50),'1') + CONVERT(CHAR(50),'23')
print CONVERT(CHAR(50),'12') + CONVERT(CHAR(50),'3')

 

 

 


Learn more about SQL Server tools