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

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.

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

Download the scripts for this tip here.
Take a look at my previous tip about migrating to Memory-Optimized Tables: Overcoming storage speed limitations with Memory-Optimized Tables for SQL Server.
Take a look at The Importance of SQL Server Foreign Keys.
Check out the constraints tip category.
Review INSTEAD OF triggers usage with this tip: Using INSTEAD OF triggers in SQL Server for DML operations.
Enrich your knowledge about views.
To get more information about EXECUTE AS read this: Granting permission with the EXECUTE AS command in SQL Server.
Read this to learn more about permissions: Giving and removing permissions in SQL Server.
Read more about SQL Server 2012 Sequence Numbers.
Review SNAPSHOT isolation: Snapshot Isolation in SQL Server 2005.
Read this tip about Transaction isolation levels: Demonstrations of Transaction Isolation Levels in SQL Server.
Learn how to raise an exception with the THROW statement: SQL Server 2012 THROW statement to raise an exception.
Also check the error handling tips category.
Read this tip about the CHECKSUM() function and its use to build a hash index: CHECKSUM Functions in SQL Server 2005.
Review this tip to see how you can use the CHECKSUM() function to enforce UNIQUE CONSTRAINTS: SQL Server Unique Constraints for Large Text Columns.
Also you can read the following tip about aggregate functions: https://www.mssqltips.com/sqlservertip/1221/sql-server-tsql-aggregate-functions/
Check out the functions tips category for additional resources. https://www.mssqltips.com/category/functions–system/.
See this tip about how to use different collations: Case Sensitive Search on a Case Insensitive SQL Server.
Also, this tip will give you more information about table collation: Create SQL Server temporary tables with the correct collation.
Read this tip to understand SQL Server Indexing: Understanding SQL Server Indexing .
Take a look at these other tips on indexing.
If you don’t understand schema naming, please review SQL Server Four part naming.
Read this tip about SCHEMABINDING usage: Using schema binding to improve SQLServer UDF performance.
If you need to find schema-bound dependencies, then this is the tip for you: Different Ways to Find SQL Server Object Dependencies.

Daniel Farina

Daniel Farina was born in Buenos Aires, Argentina. Self-educated, since childhood he showed a passion for learning. He studied at Universidad de Buenos Aires. Daniel started working as a programmer at a young age. Over the years he specialized in databases, particularly SQL Server and Oracle. Now with 30 years of age, his work experience includes working with various technologies like VB, C, .NET, web development, Windows and Linux systems. He likes to read about science, psychology, philosophy and many other things. In his spare time, he trains powerlifting aiming to compete.

MSSQLTips Awards: Author of the Year – 2018 | Champion (100+ tips) – 2018 | Author Contender – 2015-2017, 2019