CODEWITHSUNDEEP
t-sqlmemory-optimized-tables
paparazzo
paparazzo

Reputation: 45096

SQL Server 2014 Memory Optimized Table Type

Just installed SQL Server 2014.

Syntax straight from the documentation fails.

This fails on Sales:

CREATE TYPE [Sales].[SalesOrderDetailType_inmem] AS TABLE(
  [OrderQty] [smallint] NOT NULL,
  [ProductID] [int] NOT NULL,
  [SpecialOfferID] [int] NOT NULL,
  [LocalID] [int] NOT NULL,

  INDEX [IX_ProductID] HASH ([ProductID]) WITH ( BUCKET_COUNT = 8),
  INDEX [IX_SpecialOfferID] NONCLUSTERED 
)
WITH ( MEMORY_OPTIMIZED = ON )

But if I remove Sales it still fails 

CREATE TYPE [SalesOrderDetailType_inmem] AS TABLE(
  [OrderQty] [smallint] NOT NULL,
  [ProductID] [int] NOT NULL,
  [SpecialOfferID] [int] NOT NULL,
  [LocalID] [int] NOT NULL,

  INDEX [IX_ProductID] HASH ([ProductID]) WITH ( BUCKET_COUNT = 8),
  INDEX [IX_SpecialOfferID] NONCLUSTERED 
)
WITH ( MEMORY_OPTIMIZED = ON )

With the following error

Msg 8135, Level 16, State 0, Line 10
Table level constraint does not specify column list, table 'SalesOrderDetailType_inmem'.

How to create a memory optimize table type?

Upvotes: 0

Views: 682

Answers (1)

MarkD
MarkD

Reputation: 5316

The following syntax seems to work. I've scripted for the dbo schema. It is recommended that the BUCKET_COUNT is set to ~1x-2x the unique values that will be held.

CREATE TYPE [SalesOrderDetailType_inmem] AS TABLE(
  [OrderQty] [smallint] NOT NULL,
  [ProductID] [int] NOT NULL 
    INDEX [IX_ProductID] HASH ([ProductID]) WITH ( BUCKET_COUNT = 8),
  [SpecialOfferID] [int] NOT NULL 
    INDEX [IX_SpecialOfferID] NONCLUSTERED ,
  [LocalID] [int] NOT NULL

)
WITH ( MEMORY_OPTIMIZED = ON )

Upvotes: 1

Related Questions