I saw Gregory Beamer speak on In-Memory OLTP last night.

OLTP (On-line Transaction Processing) is the stuff of a normalized schema. In-Memory OLTP, as described by Gregory Beamer last night at the Austin .NET User Group is really just the art of keeping tables and stored procedures in memory (after being read out of .dlls). It is something coming in MSSQL 2014. This feature is not unlike Memcached and has ACID Durability by way of using FileStream to push data back to cache. The reason to use this feature to begin with is to eliminate slowness due to lock contention or CPU bottlenecking. The in-memory content may perform multiple times faster and alleviate the pain of a chokepoint. To use this feature at a database one must empower it through a few alterations like so:

ALTER DATABASE imoltp ADD
      FILEGROUP [imoltp] CONTAINS
      MEMORY_OPTIMIZED_DATA
GO
ALTER DATABASE imoltp ADD FILE(NAME
      = 'imoltp_dir',
      FILENAME='c:\data\imoltp') TO
      FILEGROUP [imoltp]
GO

 
 

Then create an in-memory table like so:

CREATE TABLE [Customer] (
   [CustomerID] INT NOT NULL PRIMARY
         KEY NONCLUSTERED HASH
         WITH (BUCKET_COUNT= 1000000),
   [CompanyName] NVARCHAR(40) NOT
         NULL INDEX [IName] HASH WITH
         (BUCKET_COUNT = 1000000),
   [ContactName] NVARCHAR(40) NULL
)
WITH (MEMORY_OPTIMIZED = ON,
      DURABILITY = SCHEMA_AND_DATA);

 
 

Make the bucket size twice that of the number of rows you think you might have. Triggers and CLR do not exist in the in-memory space. There are no "large objects" either such as varchar(max) or XML blobs either. You cannot alter in-memory tables. You have to destroy them and recreate them to make a tweak. When spinning up an offline instance of MSSQL Server it will take a moment for the in-memory tables to indeed be in-memory, but once they are there they are golden. An in-memory sproc looks like this:

CREATE PROCEDURE [dbo].[InsertOrder] @id INT, @date DATETIME
   WITH NATIVE_COMPILATION, SCHEMABINDING, EXECUTE AS OWNER
AS
BEGIN ATOMIC
   WITH (TRANSACTION ISOLATION LEVEL = SNAPSHOT,
         LANGUAGE = 'us_english')
   --the guts of the stored procedure go here
END