IF OBJECT_ID('dbo.Numbers1', 'U') IS NOT NULL
 DROP TABLE dbo.Numbers1;

CREATE TABLE dbo.Numbers1 (
  NumberID int NOT NULL,
INDEX ix_NumberID1 CLUSTERED COLUMNSTORE
);
  
WITH
  Pass0 as (select 1 as C union all select 1), --2 rows
  Pass1 as (select 1 as C from Pass0 as A, Pass0 as B),--4 rows
  Pass2 as (select 1 as C from Pass1 as A, Pass1 as B),--16 rows
  Pass3 as (select 1 as C from Pass2 as A, Pass2 as B),--256 rows
  Pass4 as (select 1 as C from Pass3 as A, Pass3 as B),--65536 rows
  Pass5 as (select 1 as C from Pass4 as A, Pass4 as B),--4,294,967,296 rows
  Tally as (select row_number() over(order by C) as Number from Pass5)
INSERT dbo.Numbers1 (NumberID)
 select TOP(1000) Number
  from Tally;

ALTER INDEX ix_NumberID1 ON dbo.Numbers1 REORGANIZE;

-- Check row groups
SELECT b.name,a.partition_number,a.row_group_id,a.state_description,a.total_rows,a.size_in_bytes
FROM sys.column_store_row_groups a
INNER JOIN sys.objects b
ON a.object_id = b.object_id
WHERE b.object_id IN (OBJECT_ID('dbo.Numbers1', 'U'))
ORDER BY b.name,a.partition_number,a.row_group_id;

-- SSMs
-- Include Actual Execution Plan
-- Batch mode for serial query - NEW!
SELECT a.numberID
FROM dbo.Numbers1 a
INNER JOIN dbo.Numbers1 b
ON a.NumberID = b.NumberID;

The server only has 1 CPU so we get a serial plan yet the hash match is run in batch mode

Snapshot isolation and read-committed snapshot isolation are supported

IF OBJECT_ID('dbo.Person', 'U') IS NOT NULL
 DROP TABLE dbo.Person;

CREATE TABLE dbo.Person
(
PersonID INT NOT NULL,
PersonName NVARCHAR(100),
);

ALTER DATABASE justdave SET ALLOW_SNAPSHOT_ISOLATION ON;

CREATE CLUSTERED COLUMNSTORE INDEX ix_PersonName ON dbo.Person;

SET TRANSACTION ISOLATION LEVEL SNAPSHOT;

DBCC USEROPTIONS;

BEGIN TRAN;

-- Transaction isolation level snapshot - NEW! 
-- 2014 gives
-- Msg 35371, Level 16, State 1, Line 3
-- SNAPSHOT isolation level is not supported on a table which has a clustered columnstore index.
INSERT INTO Person(PersonID,PersonName) VALUES (1,'Person 1');

COMMIT TRAN;

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

Specify columnstore index when creating a table

IF OBJECT_ID('dbo.Numbers1', 'U') IS NOT NULL
 DROP TABLE dbo.Numbers1;

CREATE TABLE dbo.Numbers1 (
  NumberID int NOT NULL,
INDEX ix_NumberID1 CLUSTERED COLUMNSTORE
);

AlwaysOn readable secondary supports updateable columnstore indexes

AlwaysOn readable secondary supports updateable columnstore indexes!

Updateable nonclustered columnstore index on heap or btree

There can only be 1 Updatable Non-Clustered Columnstore Index!

IF OBJECT_ID('dbo.Person', 'U') IS NOT NULL
 DROP TABLE dbo.Person;

CREATE TABLE dbo.Person
(
PersonID INT NOT NULL,
PersonName NVARCHAR(100),
);

CREATE CLUSTERED INDEX ix_PersonID ON Person(PersonID);
CREATE NONCLUSTERED COLUMNSTORE INDEX ix_PersonName ON Person(PersonName);

-- Table is writable - NEW!
INSERT INTO dbo.Person VALUES (1,'Person 1');

Btree index on a clustered columnstore index

IF OBJECT_ID('dbo.Person1', 'U') IS NOT NULL
 DROP TABLE dbo.Person1;

CREATE TABLE dbo.Person1
(
PersonID INT NOT NULL,
PersonName NVARCHAR(100),
);

--Store the table as a columnstore.
CREATE CLUSTERED COLUMNSTORE INDEX ix_Person1CCI ON Person1;

-- Unique B-tree NC index - NEW
CREATE UNIQUE INDEX ix_Person1NC1 ON Person1(PersonID);

IF OBJECT_ID('dbo.Person2', 'U') IS NOT NULL
 DROP TABLE dbo.Person2;

CREATE TABLE dbo.Person2
(
PersonID INT NOT NULL,
PersonName NVARCHAR(100),
);

--Store the table as a columnstore.
CREATE CLUSTERED COLUMNSTORE INDEX ix_Person2CCI ON Person2;

-- Unique B-tree NC index - NEW
CREATE UNIQUE INDEX ix_Person2NC1 ON Person2(PersonID);

TRUNCATE TABLE dbo.Person1;
INSERT INTO Person1 (PersonID,PersonName) VALUES(1,'Person1');

TRUNCATE TABLE dbo.Person2;
INSERT INTO Person2 (PersonID,PersonName) VALUES(1,'First Person');
INSERT INTO Person2 (PersonID,PersonName) VALUES(2,'Second Person');

ALTER INDEX ix_Person1CCI ON dbo.Person1 REORGANIZE;
ALTER INDEX ix_Person2CCI ON dbo.Person2 REORGANIZE;

SELECT * FROM Person1;
SELECT * FROM Person2;

# Not supported
MERGE Person1 AS T
USING Person2 AS S
ON (T.PersonID = S.PersonID)
WHEN NOT MATCHED BY TARGET
THEN INSERT (PersonID,PersonName) VALUES (S.PersonID,S.PersonName)
WHEN MATCHED
THEN UPDATE SET T.PersonName=S.PersonName;

Columnstore index on a memory-optimized table

IF OBJECT_ID('dbo.Person', 'U') IS NOT NULL
 DROP TABLE dbo.Person;

-- NOTE: This must be durability SCHEMA_AND_DATA though
CREATE TABLE dbo.Person
(
PersonID INT PRIMARY KEY NONCLUSTERED,
PersonName NVARCHAR(100),
INDEX ix_PersonID CLUSTERED COLUMNSTORE
) WITH (MEMORY_OPTIMIZED=ON, DURABILITY=SCHEMA_ONLY);

-- Primary key still has to be nonclustered
-- Therefore memory-optimized tables can be Columnstore+Clustered not B-tree+Clustered
-- Non-clustered B-tree index supports foreign keys
-- The columnstore index has to be created when the table is created
-- The columnstore index cannot be filtered
-- The olumnstore index is accessed in interOP mode, not in-memory native mode
IF OBJECT_ID('dbo.Person', 'U') IS NOT NULL
 DROP TABLE dbo.Person;

CREATE TABLE dbo.Person
(
PersonID INT PRIMARY KEY NONCLUSTERED,
PersonName NVARCHAR(100),
INDEX ix_PersonID CLUSTERED COLUMNSTORE
) WITH (MEMORY_OPTIMIZED=ON, DURABILITY=SCHEMA_AND_DATA);

-- NOTE: ALTER TABLE is not supported on memory optimized tables that have a column store index.

-- Msg 10794, Level 16, State 15, Line 2
-- The operation 'ALTER TABLE' is not supported with memory optimized tables that have a column store index.
ALTER TABLE Person
 ADD PersonComments NVARCHAR(150);

Nonclustered columnstore index definition supports using a filtered condition

-- Hot and Cold Data
IF OBJECT_ID('dbo.Orders', 'U') IS NOT NULL
 DROP TABLE dbo.Orders;

CREATE TABLE Orders
(
OrderID int NOT NULL PRIMARY KEY,
OrderDate INT,
UnitsSold INT
);

CREATE NONCLUSTERED COLUMNSTORE INDEX Orders_NCCI1 ON Orders(OrderDate,UnitsSold)
WHERE OrderDate < 2016;

CREATE NONCLUSTERED INDEX Orders_NCI1 ON Orders(OrderDate)
WHERE OrderDate >= 2016;

-- Memory optimized + columnstore (or b-tree) + filtered index
IF OBJECT_ID('dbo.Orders', 'U') IS NOT NULL
 DROP TABLE dbo.Orders;

CREATE TABLE Orders
(
OrderID int NOT NULL PRIMARY KEY NONCLUSTERED,
OrderDate INT,
UnitsSold INT
) WITH (MEMORY_OPTIMIZED=ON, DURABILITY=SCHEMA_AND_DATA);

-- Msg 10794, Level 16, State 12, Line 1
-- The operation 'CREATE INDEX' is not supported with memory optimized tables.
CREATE NONCLUSTERED COLUMNSTORE INDEX Orders_NCCI1 ON Orders(OrderDate,UnitsSold)
WHERE OrderDate < 2016;

-- Msg 10794, Level 16, State 12, Line 1
-- The operation 'CREATE INDEX' is not supported with memory optimized tables.
CREATE NONCLUSTERED INDEX Orders_NCI1 ON Orders(OrderDate)
WHERE OrderDate >= 2016;

Improved Seek Performance (CTP 2.1) WIP
Improved scan performance with partitioned tables (CTP 2.1) WIP

2.1.1.2 A read-only nonclustered columnstore index is updateable after upgrade (CTP 3.0)

After the upgrade process read-only nonclustered columnstore index are writeable

2.1.1.3 Performance improvements for analytics queries on columnstore indexes (CTP 3.0)

Additional logical operators can run in batch mode

IF OBJECT_ID('dbo.Numbers1', 'U') IS NOT NULL
 DROP TABLE dbo.Numbers1;

CREATE TABLE dbo.Numbers1 (
  NumberID int NOT NULL,
  Name varchar(10)
);

CREATE CLUSTERED COLUMNSTORE INDEX ix_NumberID1 ON dbo.Numbers1;

WITH
  Pass0 as (select 1 as C union all select 1), --2 rows
  Pass1 as (select 1 as C from Pass0 as A, Pass0 as B),--4 rows
  Pass2 as (select 1 as C from Pass1 as A, Pass1 as B),--16 rows
  Pass3 as (select 1 as C from Pass2 as A, Pass2 as B),--256 rows
  Pass4 as (select 1 as C from Pass3 as A, Pass3 as B),--65536 rows
  Pass5 as (select 1 as C from Pass4 as A, Pass4 as B),--4,294,967,296 rows
  Tally as (select row_number() over(order by C) as Number from Pass5)
INSERT dbo.Numbers1 (NumberID)
 select TOP(10000) Number
  from Tally;

ALTER INDEX ix_NumberID1 ON dbo.Numbers1 REORGANIZE;

-- Check row groups
SELECT b.name,a.partition_number,a.row_group_id,a.state_description,a.total_rows,a.size_in_bytes
FROM sys.column_store_row_groups a
INNER JOIN sys.objects b
ON a.object_id = b.object_id
WHERE b.object_id IN (OBJECT_ID('dbo.Numbers1', 'U'))
ORDER BY b.name,a.partition_number,a.row_group_id;

Clustered Index Scan (Serial Plan),added CTP 2.0

SELECT a.numberID
FROM dbo.Numbers1 a
INNER JOIN dbo.Numbers1 b
ON a.NumberID = b.NumberID;

Compute Scalar (Serial Plan),added CTP 2.0

SELECT a.numberID*2
FROM dbo.Numbers1 a
INNER JOIN dbo.Numbers1 b
ON a.NumberID = b.NumberID;

Concatenation (Serial Plan),added CTP 2.0

SELECT a.numberID
FROM dbo.Numbers1 a 
UNION ALL
SELECT a.numberID
FROM dbo.Numbers1 a
UNION ALL
SELECT a.numberID
FROM dbo.Numbers1 a;

Filter (Serial Plan),added CTP 2.0

SELECT a.numberID
FROM dbo.Numbers1 a
INNER JOIN dbo.Numbers1 b
ON a.NumberID = b.NumberID
WHERE a.Name is NOT NULL;

Hash Match (Serial Plan),added CTP 2.0

SELECT a.numberID
FROM dbo.Numbers1 a
INNER JOIN dbo.Numbers1 b
ON a.NumberID = b.NumberID;

Index Scan Nonclustered (Serial Plan),added CTP 2.0

-- Change create index statement to
-- change to insert data, create index and do not reorganize as only this order is supported under 2014
CREATE NONCLUSTERED COLUMNSTORE INDEX ix_NumberID1 ON dbo.Numbers1(NumberID);

SELECT a.numberID
FROM dbo.Numbers1 a
WITH (INDEX(ix_NumberID1))
INNER JOIN dbo.Numbers1 b
WITH (INDEX(ix_NumberID1))
ON a.NumberID = b.NumberID

Additional operators can run in batch mode - WIP

STDEV/STDEVP,added CTP 2.0

SELECT STDEV(a.numberID),STDEVP(a.numberID)
FROM dbo.Numbers1 a
INNER JOIN dbo.Numbers1 b
ON a.NumberID = b.NumberID;
We have a compute scalar operator running in batch mode

A hash match aggregate running in batch mode

Another compute scalar running in batch mode

Before we finally go to row mode for the stream aggregate and final computer scalar
With SQL Server 2014 we went straight from the hash match join to the row mode stream aggregate

SUM/AVG/COUNT/MIN/MAX,added CTP 2.0
```
SELECT SUM(a.numberID),AVG(a.numberID),COUNT(a.numberID),MIN(a.numberID),MAX(a.numberID)
FROM dbo.Numbers1 a
INNER JOIN dbo.Numbers1 b
ON a.NumberID = b.NumberID;
```
We get a hash match aggregate and compute scalar in batch mode before we go to a row mode stream aggregate

Whereas 2014 does not have these operators
COUNT_BIG/VAR/VARP,added CTP 2.0
```
SELECT COUNT_BIG(a.numberID),VAR(a.numberID),VARP(a.numberID)
FROM dbo.Numbers1 a
INNER JOIN dbo.Numbers1 b
ON a.NumberID = b.NumberID;
```
Similarly we get a 2 compute scalars and hash match aggregate all running in match mode

Again 2014 does not have these operators

CHECKSUM_AGG (CTP 3.2 still runs in row mode!) WIP

SELECT CHECKSUM_AGG(CAST(a.numberID as INT))
FROM dbo.Numbers1 a
INNER JOIN dbo.Numbers1 b
ON a.NumberID = b.NumberID;

CLR - WIP

CUME_DIST,added CTP 2.0

IF OBJECT_ID('dbo.Numbers1', 'U') IS NOT NULL
 DROP TABLE dbo.Numbers1;

CREATE TABLE dbo.Numbers1 (
  NumberID int NOT NULL,
  DeptID int NOT NULL,
  DivisionID int NOT NULL
);

CREATE CLUSTERED COLUMNSTORE INDEX ix_NumberID1 ON dbo.Numbers1;

WITH
  Pass0 as (select 1 as C union all select 1), --2 rows
  Pass1 as (select 1 as C from Pass0 as A, Pass0 as B),--4 rows
  Pass2 as (select 1 as C from Pass1 as A, Pass1 as B),--16 rows
  Pass3 as (select 1 as C from Pass2 as A, Pass2 as B),--256 rows
  Pass4 as (select 1 as C from Pass3 as A, Pass3 as B),--65536 rows
  Pass5 as (select 1 as C from Pass4 as A, Pass4 as B),--4,294,967,296 rows
  Tally as (select row_number() over(order by C) as Number from Pass5)
INSERT dbo.Numbers1 (NumberID,DeptID,DivisionID)
 select TOP(10000) Number,Number/10,Number/100
  from Tally;

ALTER INDEX ix_NumberID1 ON dbo.Numbers1 REORGANIZE;

SELECT a.NumberID,a.DeptID,a.DivisionID,
CUME_DIST() OVER (PARTITION BY a.DivisionID ORDER BY a.DeptID) AS CumeDist
FROM dbo.Numbers1 a
INNER JOIN dbo.Numbers1 b
ON a.NumberID = b.NumberID;

We get a sort,2 window aggregates and a compute scalar all running in batch mode

Whereas with 2014 we get table spools, segment operators all running in row mode

Only the upper right index scans/hash match and the leftmost computer scalar run in batch mode

/li>

FIRST_VALUE - WIP

SELECT NumberID,DeptID,
FIRST_VALUE(NumberID) OVER (ORDER BY DeptID) AS FirstValue
FROM dbo.Numbers1;

GROUPING,added CTP 2.0
```
SELECT DivisionID,DeptID,SUM(NumberID)
FROM dbo.Numbers1
GROUP BY GROUPING SETS ((DivisionID), (DeptID))
```
The query plan has all operators in batch mode

Whereas in 2014 all operators are in row mode

GROUPING_ID,added CTP 2.0

SELECT DivisionID,DeptID,SUM(NumberID),GROUPING_ID(DivisionID)
FROM dbo.Numbers1
GROUP BY GROUPING SETS ((DivisionID), (DeptID))

The query plan has all operators in batch mode

Whereas in 2014 all operators are in row mode

LAST_VALUE- WIP

LAG - WIP

SELECT NumberID,DeptID,
LAG(NumberID) OVER (ORDER BY DeptID) AS FirstValue
FROM dbo.Numbers1;
SELECT a.NumberID,a.DeptID,a.DivisionID,
LAG(NumberID) OVER (PARTITION BY a.DivisionID ORDER BY a.DeptID) AS CumeDist
FROM dbo.Numbers1 a
INNER JOIN dbo.Numbers1 b
ON a.NumberID = b.NumberID;

LEAD- WIP

PERCENTILE_CONT/PERCENTILE_DISC/PERCENT_RANK,added CTP 2.0

SELECT a.NumberID,a.DeptID,a.DivisionID,
PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY a.DeptID) OVER (PARTITION BY a.DivisionID)  AS PercCont,
PERCENTILE_DISC(0.5) WITHIN GROUP (ORDER BY a.DeptID) OVER (PARTITION BY a.DivisionID)  AS PercDisc,
PERCENT_RANK() OVER (PARTITION BY a.DivisionID ORDER BY a.DeptID ) AS PercRank
FROM dbo.Numbers1 a
INNER JOIN dbo.Numbers1 b
ON a.NumberID = b.NumberID;

The query plan has all operators in batch mode

Whereas in 2014 all operators to the left of the hash match from the index scan (apart from the final computer scalar)are in row mode with spools/nested loop joins

Aggregate computation can be pushed down to be done during the scan node of the query plan

The following operations support pushing the aggregate calculation down to be done during the SCAN node of a query plan

MIN
MAX
SUM
COUNT
AVG

Limitations are:

Any datatype <= 64 bits is supported
The aggregate operator must be on top of SCAN node or SCAN node with group by

The aggregation is done on compressed/encoded data in cache-friendly execution and by leveraging SIMD

String predicates can be pushed down in the SCAN node during a query plan

The primary/secondary dictionary created for column compression is used for predicate processing

The predicate is computed against just the dictionary entries rather than each row

If the dictionary entry passes the predicate all rows referring to the dictionary entry are automatically qualified

This results in potential performance improvements:

Only the qualified rows are passed out of the SCAN node in the query plan rather than all rows
The number of string comparisions matches the number of dictionary entries rather than the number of index rows

2.1.1.4 DMVs and XEvents have supportability improvements

New DMVs

sys.dm_db_column_store_row_group_operational_stats, added in CTP 2.0 - row-level I/O, locking, and access method activity for compressed rowgroups in a columnstore index
sys.dm_db_column_store_row_group_physical_stats, added in CTP 2.0 - rowgroup-level information including why a rowgroup was trimmed to contain less than the maximum mrows and why the rowgroup moved from the delta store to the compress columnstore
sys.internal_partitions, added in CTP 2.0 - tracks internal rowset data,deleted rows, rowgroup mappings, and delta store rowgroups

Updated DMVs

sys.dm_db_xtp_hash_index_stats - adds xtp_object_id, added in CTP 2.0
sys.dm_db_xtp_index_stats - adds xtp_object_id, added in CTP 2.0
sys.dm_db_xtp_memory_consumers - adds xtp_object_id, added in CTP 2.0
sys.dm_db_xtp_nonclustered_index_stats - adds xtp_object_id,uses_key_normalization, added in CTP 2.0
sys.dm_db_xtp_object_stats - adds xtp_object_id, added in CTP 2.0

Need to add XEvents - WIP

2.1.2 In-Memory OLTP

2.1.2.1 Transact-SQL Improvements

2.1.2.1.1 Query Surface Area in Native Modules (CTP 2.0/CTP 3.0/CTP 3.1/CTP 3.2)

Newly supported constructs are

ALTER TABLE
ALTER PROCEDURE
Natively compiled inline table-valued functions (TVFs)
Nesting natively compiled stored procedures (Nesting natively compiled stored procedures)
Scalar User Defined Functions
EXECUTE AS CALLER support - the EXECUTE AS clause is no longer required for native modules
INSERT SELECT
SELECT DISTINCT - DISTINCT aggregates are not supported
UNION ALL and UNION
COLUMNSTORE
COLLATE
LEFT OUTER JOIN, RIGHT OUTER JOIN, CROSS JOIN (not just SELECT queries) and INNER JOIN (not just SELECT queries)
NOT, OR, and IN operators in SELECT, UPDATE and DELETE statements, in the WHERE clause, HAVING clause, and JOIN conditions. This support extends to NOT IN and NOT BETWEEN

AND and EXISTS in the WHERE clause for SELECTs

WHERE is supported with UPDATE and DELETE statements,Subqueries are not supported. In the WHERE or HAVING clause, AND and BETWEEN are supported; OR, NOT, and IN are not supported.

GROUP BY clause with no aggregate functions in the SELECT clause
Scaler subqueries
Subqueries [AS] table_alias
Declaring variables as NULL or NOT NULL. If a variable is declared as NOT NULL, the declaration must have an initializer. If a variable is not declared as NOT NULL, an initializer is optional
All Math functions not just ACOS, ASIN, ATAN, ATN2, COS, COT, DEGREES, EXP, LOG, LOG10, PI, POWER, RADIANS, RAND, SIN, SQRT, SQUARE, and TAN
Row level security functions IS_MEMBER({'group' | 'role'}), IS_ROLEMEMBER ('role' [, 'database_principal']), IS_SRVROLEMEMBER ('role' [, 'login']), ORIGINAL_LOGIN(), SESSION_USER, CURRENT_USER, SUSER_ID(['login']), SUSER_SID(['login'] [, Param2]), SUSER_SNAME([server_user_sid]), SYSTEM_USER, SUSER_NAME, USER, USER_ID(['user']), USER_NAME([id]), CONTEXT_INFO()

2.1.2.1.2 Altering Natively Compiled Stored Procedures (CTP 2.0)

IF OBJECT_ID('dbo.Person', 'U') IS NOT NULL
 DROP TABLE dbo.Person;

CREATE TABLE Person 
(
PersonID INT NOT NULL,
PersonName NVARCHAR(80),
PersonQuality NVARCHAR(50),
INDEX ix_PersonID NONCLUSTERED HASH (PersonId) WITH (BUCKET_COUNT=2000)
) WITH (MEMORY_OPTIMIZED=ON, DURABILITY=SCHEMA_ONLY);

INSERT into dbo.Person (PersonID) Values(1);

IF OBJECT_ID('dbo.PersonUpdateQuality', 'P') IS NOT NULL
 DROP PROCEDURE dbo.PersonUpdateQuality;

CREATE PROCEDURE dbo.PersonUpdateQuality(@PersonID INTEGER)
WITH NATIVE_COMPILATION,SCHEMABINDING,EXECUTE AS SELF
AS
BEGIN ATOMIC
WITH (TRANSACTION ISOLATION LEVEL = SNAPSHOT, LANGUAGE = N'us_english')
    UPDATE dbo.Person Set PersonQuality='OK' 
     WHERE PersonID=@PersonID;
END;

SELECT * FROM dbo.Person;
EXECUTE dbo.PersonUpdateQuality 1;
SELECT * FROM dbo.Person;

ALTER PROCEDURE dbo.PersonUpdateQuality(@PersonID INTEGER,@PersonQuality NVARCHAR(50))
WITH NATIVE_COMPILATION,SCHEMABINDING,EXECUTE AS SELF
AS
BEGIN ATOMIC
WITH (TRANSACTION ISOLATION LEVEL = SNAPSHOT, LANGUAGE = N'us_english')
    UPDATE dbo.Person Set PersonQuality=@PersonQuality 
     WHERE PersonID=@PersonID;
END;

SELECT * FROM dbo.Person;
EXECUTE dbo.PersonUpdateQuality 1,'Excellent';
SELECT * FROM dbo.Person;

-- CTP 2.2 add EXECUTE AS CALLER
CREATE PROCEDURE dbo.PersonUpdateQuality(@PersonID INTEGER)
WITH NATIVE_COMPILATION,SCHEMABINDING,EXECUTE AS CALLER
AS
BEGIN ATOMIC
WITH (TRANSACTION ISOLATION LEVEL = SNAPSHOT, LANGUAGE = N'us_english')
    UPDATE dbo.Person Set PersonQuality='OK' 
     WHERE PersonID=@PersonID;
END

IF OBJECT_ID('dbo.PersonUpdateQuality', 'P') IS NOT NULL
 DROP PROCEDURE dbo.PersonUpdateQuality;

2.1.2.1.2 Altering Memory-Optimized Tables (CTP 2.0)

IF OBJECT_ID('dbo.Person', 'U') IS NOT NULL
 DROP TABLE dbo.Person;

CREATE TABLE dbo.Person 
(
PersonID INT NOT NULL,
PersonName NVARCHAR(80),
PersonQuality NVARCHAR(50) NOT NULL,
INDEX ix_PersonID NONCLUSTERED HASH (PersonId) WITH (BUCKET_COUNT=2000)
) WITH (MEMORY_OPTIMIZED=ON, DURABILITY=SCHEMA_ONLY);
 
INSERT into dbo.Person (PersonID,PersonQuality) Values(1,'First Person');
INSERT into dbo.Person (PersonID,PersonQuality) Values(2,'Second Person');

-- Alter Memory Optimized Table - NEW
ALTER TABLE dbo.Person
 ADD PersonComments NVARCHAR(200) NOT NULL CONSTRAINT PersonOK DEFAULT 'OK' WITH VALUES;

ALTER TABLE dbo.Person
 ADD CONSTRAINT PK_PersonID PRIMARY KEY NONCLUSTERED (PersonID);

ALTER TABLE dbo.Person
 ADD INDEX Quality (PersonQuality);

ALTER TABLE dbo.Person
 DROP INDEX Quality;

ALTER TABLE dbo.Person
 DROP CONSTRAINT PersonOK;

ALTER TABLE dbo.Person
 DROP COLUMN PersonComments;

ALTER TABLE dbo.Person
 ALTER INDEX ix_PersonID
 REBUILD WITH (BUCKET_COUNT=5000);

2.1.2.1.3 Full support for all Collation and Unicode Support (CTP 2.0)

In CTP 2.0 In-Memory OLTP now fully supports collations.

(var)char columns can use any code page supported by SQL Server
Character columns in index keys can use any SQL Server collation
Expressions in natively compiled modules as well as constraints on memory-optimized tables can use any SQL Server collation

2.1.2.1.4 Scalar User-Defined Functions (CTP 2.0)

-- All syntax needed
-- Msg 10796, Level 15, State 2, Procedure fn_securitypredicate, Line 27
-- The SCHEMABINDING option is supported only for natively compiled modules, and is required for those modules.
-- Msg 10783, Level 15, State 2, Procedure fn_securitypredicate, Line 19
-- The body of a natively compiled module must be an ATOMIC block.
-- Msg 10784, Level 15, State 1, Procedure fn_securitypredicate, Line 18
-- The WITH clause of BEGIN ATOMIC statement must specify a value for the option 'transaction isolation level'.
-- Msg 10784, Level 15, State 1, Procedure fn_securitypredicate, Line 18
-- The WITH clause of BEGIN ATOMIC statement must specify a value for the option 'language'.

IF OBJECT_ID('dbo.fn_AddOne', 'FN') IS NOT NULL
 DROP FUNCTION dbo.fn_AddOne;

CREATE FUNCTION dbo.fn_AddOne(@Number INTEGER)
    RETURNS INTEGER
WITH NATIVE_COMPILATION,SCHEMABINDING,EXECUTE AS SELF
AS
BEGIN ATOMIC
WITH (TRANSACTION ISOLATION LEVEL = SNAPSHOT, LANGUAGE = N'us_english')
    RETURN @Number+1;
END;

-- CTP 2.2 add EXECUTE AS CALLER
IF OBJECT_ID('dbo.fn_AddOne', 'FN') IS NOT NULL
 DROP FUNCTION dbo.fn_AddOne;

CREATE FUNCTION dbo.fn_AddOne(@Number INTEGER)
    RETURNS INTEGER
WITH NATIVE_COMPILATION,SCHEMABINDING,EXECUTE AS CALLER
AS
BEGIN ATOMIC
WITH (TRANSACTION ISOLATION LEVEL = SNAPSHOT, LANGUAGE = N'us_english')
    RETURN @Number+1;
END;

2.1.2.1.5 Constraints (CTP 3.0)

Constraints on in-memory OLTP tables

Primary
Foreign - cascade, not yet! currently must reference a primary key!
Unique
Check

IF OBJECT_ID('dbo.Person', 'U') IS NOT NULL
 DROP TABLE dbo.Person;

CREATE TABLE Person 
(
PersonID INT NOT NULL PRIMARY KEY NONCLUSTERED,
PersonName NVARCHAR(80),
PersonAge INT CONSTRAINT cc_Page CHECK (PersonAge>0),
PersonEmployeeID INT NOT NULL,
PersonQuality NVARCHAR(50),
INDEX ix_PersonID NONCLUSTERED HASH (PersonId) WITH (BUCKET_COUNT=2000),
CONSTRAINT uc_PersonEmployeeID UNIQUE (PersonEmployeeID)
) WITH (MEMORY_OPTIMIZED=ON, DURABILITY=SCHEMA_ONLY);

IF OBJECT_ID('dbo.Orders', 'U') IS NOT NULL
 DROP TABLE dbo.Orders;

CREATE TABLE Orders
(
OrderID INT NOT NULL PRIMARY KEY NONCLUSTERED,
SalesPerson INT FOREIGN KEY (SalesPerson) REFERENCES dbo.Person(PersonID), 
UnitsSold INT
) WITH (MEMORY_OPTIMIZED=ON, DURABILITY=SCHEMA_ONLY);

2.1.2.1.6 Triggers (CTP 3.0)

Triggers on memory optimizerd tables

IF OBJECT_ID('dbo.Orders', 'U') IS NOT NULL
 DROP TABLE dbo.Orders;

CREATE TABLE Orders
(
OrderID INT NOT NULL PRIMARY KEY NONCLUSTERED,
Department VARCHAR(20),
UnitsSold INT
) WITH (MEMORY_OPTIMIZED=ON, DURABILITY=SCHEMA_ONLY);

IF OBJECT_ID('dbo.TotalOrders', 'U') IS NOT NULL
 DROP TABLE dbo.TotalOrders;

CREATE TABLE TotalOrders
(
Department VARCHAR(20) PRIMARY KEY NONCLUSTERED,
TotalOrders INT NOT NULL,
LastAction CHAR(1)
) WITH (MEMORY_OPTIMIZED=ON, DURABILITY=SCHEMA_ONLY);

INSERT INTO dbo.TotalOrders (Department,TotalOrders) VALUES ('Sales',0);

IF OBJECT_ID('dbo.OrderInsert', 'TR') IS NOT NULL
 DROP TRIGGER dbo.OrderInsert;

CREATE TRIGGER OrderInsert
ON dbo.Orders
WITH NATIVE_COMPILATION,SCHEMABINDING
AFTER INSERT 
AS
BEGIN ATOMIC
WITH (TRANSACTION ISOLATION LEVEL = SNAPSHOT, LANGUAGE = N'us_english')
 UPDATE dbo.TotalOrders
 SET TotalOrders=TotalOrders+1,
 LastAction='I'
 WHERE Department='Sales';
END;

SELECT * FROM dbo.Orders;
SELECT * FROM dbo.TotalOrders;
INSERT INTO dbo.Orders(OrderID,Department,UnitsSold) VALUES(1,'Sales',10);
INSERT INTO dbo.Orders(OrderID,Department,UnitsSold) VALUES(2,'Sales',20);
SELECT * FROM dbo.Orders;
SELECT * FROM dbo.TotalOrders;

IF OBJECT_ID('dbo.OrderDelete', 'TR') IS NOT NULL
 DROP TRIGGER dbo.OrderDelete;

CREATE TRIGGER OrderDelete
ON dbo.Orders
WITH NATIVE_COMPILATION,SCHEMABINDING
AFTER DELETE 
AS
BEGIN ATOMIC
WITH (TRANSACTION ISOLATION LEVEL = SNAPSHOT, LANGUAGE = N'us_english')
 UPDATE dbo.TotalOrders
 SET TotalOrders=TotalOrders-1,
 LastAction='D'
 WHERE Department='Sales';
END;

SELECT * FROM dbo.Orders;
SELECT * FROM dbo.TotalOrders;
DELETE FROM dbo.Orders where OrderID=1;
SELECT * FROM dbo.Orders;
SELECT * FROM dbo.TotalOrders;

IF OBJECT_ID('dbo.OrderUpdate', 'TR') IS NOT NULL
 DROP TRIGGER dbo.OrderUpdate;

CREATE TRIGGER OrderUpdate
ON dbo.Orders
WITH NATIVE_COMPILATION,SCHEMABINDING
AFTER UPDATE 
AS
BEGIN ATOMIC
WITH (TRANSACTION ISOLATION LEVEL = SNAPSHOT, LANGUAGE = N'us_english')
 UPDATE dbo.TotalOrders
 SET LastAction='U'
 WHERE Department='Sales';
END;

SELECT * FROM dbo.Orders;
SELECT * FROM dbo.TotalOrders;
UPDATE dbo.Orders SET UnitsSold = 5 WHERE OrderID=2;
SELECT * FROM dbo.Orders;
SELECT * FROM dbo.TotalOrders;

2.1.2.1.7 NULLable index key columns,LOB types,UNIQUE indexes (CTP 3.1)

In CTP 3.1

NULLable index key columns
LOB types [varchar(max), nvarchar(max), and varbinary(max)]
UNIQUE indexes

IF OBJECT_ID('dbo.Person', 'U') IS NOT NULL
 DROP TABLE dbo.Person;

CREATE TABLE Person 
(
PersonID INT NOT NULL PRIMARY KEY NONCLUSTERED,
PersonName NVARCHAR(80),
PersonAge INT CHECK (PersonAge>0),
PersonEmployeeID INT NULL, -- Nullable index column
PersonQuality1 VARCHAR(MAX),
PersonQuality2 NVARCHAR(MAX),
PersonQuality3 VARBINARY(MAX),
INDEX ix_PersonID NONCLUSTERED HASH (PersonId) WITH (BUCKET_COUNT=2000),
INDEX ix_PersonEmployeeID UNIQUE (PersonEmployeeID)
) WITH (MEMORY_OPTIMIZED=ON, DURABILITY=SCHEMA_ONLY);

2.1.2.2 Performance and Scaling improvements

2.1.2.2.1 Increased data size to 2TB (CTP 2.0)

A database can now have up to 2 terabytes of user data in memory-optimized tables with SCHEMA_AND_DATA

2.1.2.2.2 Scalability improvements in the persistence layer (CTP 2.0)

Multiple threads to persist memory-optimized tables
Multi-threaded recovery
MERGE Operation is now multi-threaded
The In-memory OLTP engine continues to use memory-optimized filegroup based on FILESTREAM, but the individual files in the filegroup are decoupled from FILESTREAM
These files are fully managed (such as for create, drop, and garbage collection) by the In-Memory OLTP engine.
DBCC SHRINKFILE (Transact-SQL) is not supported.
Manual Merge has been disabled as multi-threaded merge is expected to keep up with the load.

2.1.2.2.3 Parallel plan support for Accessing Memory-Optimized Tables Using Interpreted Transact-SQL (CTP 2.0)

2.1.2.2.4 Parallel scan support for HASH indexes (CTP 2.0)

2.1.2.3 Enhancements in SQL Server Management Studio

2.1.2.3.1 Determining if a Table or Stored Procedure Should Be Ported to In-Memory OLTP (CTP 2.0)

2.1.2.3.2 PowerShell Cmdlet for Migration Evaluation (CTP 2.0)

The Save-SqlMigrationReport cmdlet is a tool that evaluates the migration fitness of multiple objects in a SQL Server database.

Currently, it is limited to evaluating the migration fitness for In-Memory OLTP.

Save-SqlMigrationReport [ -MigrationType OLTP ] [ -Server server -Database database [ -Object object_name ] ] | [ -InputObject smo_object ] -FolderPath path

The report goes into 2 folders under FolderPath (Tables and Stored Procedures)

2.1.10.5 The COLUMN MASTER KEY argument of CREATE COLUMN ENCRYPTION KEY (Transact-SQL) has been renamed to COLUMN_MASTER_KEY (CTP 3.0)

2.1.10.6 Built-in support for using column master keys stored in hardware security modules (HSMs) that provide Crypto API (CAPI) or Cryptography API - Next Generation (CNG) (CTP 3.0)