In this tutorial, I will write an article about How to Calculate Total Rows Inserted per Second in SQL Server 2014. Ever expected to compute the quantity of columns embedded consistently, for each table in every database on a server? Alternately, have you ever expected to approve that all methods have quit keeping in touch with tables? These sorts of inquiries come up routinely for me. To help with this, I've composed the following script, which inspects metadata qualities utilizing sys.partitions. This system isn't as precise as running SELECT COUNT(*) FROM, however its much quicker.

Remember, since it’s just looking at row counts, its very little help on tables that have a considerable measure of update/delete  action. Yet it does what I need it to do, and I utilize it pretty frequently, so I thought I'd experience case any other individual can advantage from it as well.

/* Declare Parameters */

DECLARE @newBaseline BIT = 1 -- change to 0 when you don't want to replace the baseline, i.e. after initial run
  , @delay CHAR(8) = '00:00:30'; -- change as needed 
IF @newBaseline = 1
BEGIN
    IF OBJECT_ID('tempdb..#baseline') IS NOT NULL
        DROP TABLE #baseline; 
    CREATE TABLE #baseline
    (
         database_name  SYSNAME
       , table_name     SYSNAME
       , table_rows     BIGINT
       , captureTime    DATETIME NULL
    );
END 

IF OBJECT_ID('tempdb..#current') IS NOT NULL
    DROP TABLE #current;
 CREATE TABLE #current
(
     database_name  SYSNAME
   , table_name     SYSNAME
   , table_rows     BIGINT
   , captureTime    DATETIME NULL
); 
IF @newBaseline = 1
BEGIN
    EXECUTE sp_MSforeachdb 'USE ?;
        INSERT INTO #baseline
        SELECT DB_NAME()
            , o.name As [tableName]
            , SUM(p.[rows]) As [rowCnt]
            , GETDATE() As [captureTime]
        FROM sys.indexes As i
        JOIN sys.partitions As p
            ON i.[object_id] = p.[object_id]
           AND i.index_id  = p.index_id
        JOIN sys.objects As o
            ON i.[object_id] = o.[object_id]
        WHERE i.[type] = 1
        GROUP BY o.name;' 
    WAITFOR DELAY @delay;
END
 EXECUTE sp_MSforeachdb 'USE ?;
INSERT INTO #current
SELECT DB_NAME()
    , o.name As [tableName]
    , SUM(p.[rows]) As [rowCnt]
    , GETDATE() As [captureTime]
FROM sys.indexes As i
JOIN sys.partitions As p
    ON i.[object_id] = p.[object_id]
   AND i.index_id  = p.index_id
JOIN sys.objects As o
    ON i.[object_id] = o.[object_id]
WHERE i.[type] = 1
GROUP BY o.name;' 
SELECT  c.*
      , c.table_rows - b.table_rows AS 'new_rows'
      , DATEDIFF(second, b.captureTime, c.captureTime) AS 'time_diff'
      , (c.table_rows - b.table_rows) / DATEDIFF(second, b.captureTime, c.captureTime) AS 'rows_per_sec'
FROM #baseline AS b
JOIN #current AS c
    ON b.table_name = c.table_name
   AND b.database_name = c.database_name
ORDER BY new_rows DESC;

HostForLIFE.eu SQL Server 2014 Hosting
HostForLIFE.eu is European Windows Hosting Provider which focuses on Windows Platform only. We deliver on-demand hosting solutions including Shared hosting, Reseller Hosting, Cloud Hosting, Dedicated Servers, and IT as a Service for companies of all sizes. We have customers from around the globe, spread across every continent. We serve the hosting needs of the business and professional, government and nonprofit, entertainment and personal use market segments.

In this tutorial, I will write an article about How to Calculate Total Rows Inserted per Second in SQL Server 2014. Ever expected to compute the quantity of columns embedded consistently, for each table in every database on a server? Alternately, have you ever expected to approve that all methods have quit keeping in touch with tables? These sorts of inquiries come up routinely for me. To help with this, I've composed the following script, which inspects metadata qualities utilizing sys.partitions. This system isn't as precise as running SELECT COUNT(*) FROM, however its much quicker.

Remember, since it’s just looking at row counts, its very little help on tables that have a considerable measure of update/delete  action. Yet it does what I need it to do, and I utilize it pretty frequently, so I thought I'd experience case any other individual can advantage from it as well.

/* Declare Parameters */
DECLARE @newBaseline BIT = 1 -- change to 0 when you don't want to replace the baseline, i.e. after initial run
  , @delay CHAR(8) = '00:00:30'; -- change as needed 
IF @newBaseline = 1
BEGIN
    IF OBJECT_ID('tempdb..#baseline') IS NOT NULL
        DROP TABLE #baseline; 
    CREATE TABLE #baseline
    (
         database_name  SYSNAME
       , table_name     SYSNAME
       , table_rows     BIGINT
       , captureTime    DATETIME NULL
    );
END 

IF OBJECT_ID('tempdb..#current') IS NOT NULL
    DROP TABLE #current;
 CREATE TABLE #current
(
     database_name  SYSNAME
   , table_name     SYSNAME
   , table_rows     BIGINT
   , captureTime    DATETIME NULL
); 
IF @newBaseline = 1
BEGIN
    EXECUTE sp_MSforeachdb 'USE ?;
        INSERT INTO #baseline
        SELECT DB_NAME()
            , o.name As [tableName]
            , SUM(p.[rows]) As [rowCnt]
            , GETDATE() As [captureTime]
        FROM sys.indexes As i
        JOIN sys.partitions As p
            ON i.[object_id] = p.[object_id]
           AND i.index_id  = p.index_id
        JOIN sys.objects As o
            ON i.[object_id] = o.[object_id]
        WHERE i.[type] = 1
        GROUP BY o.name;' 
    WAITFOR DELAY @delay;
END
 EXECUTE sp_MSforeachdb 'USE ?;
INSERT INTO #current
SELECT DB_NAME()
    , o.name As [tableName]
    , SUM(p.[rows]) As [rowCnt]
    , GETDATE() As [captureTime]
FROM sys.indexes As i
JOIN sys.partitions As p
    ON i.[object_id] = p.[object_id]
   AND i.index_id  = p.index_id
JOIN sys.objects As o
    ON i.[object_id] = o.[object_id]
WHERE i.[type] = 1
GROUP BY o.name;' 
SELECT  c.*
      , c.table_rows - b.table_rows AS 'new_rows'
      , DATEDIFF(second, b.captureTime, c.captureTime) AS 'time_diff'
      , (c.table_rows - b.table_rows) / DATEDIFF(second, b.captureTime, c.captureTime) AS 'rows_per_sec'
FROM #baseline AS b
JOIN #current AS c
    ON b.table_name = c.table_name
   AND b.database_name = c.database_name
ORDER BY new_rows DESC;

HostForLIFE.eu SQL Server 2014 Hosting
HostForLIFE.eu is European Windows Hosting Provider which focuses on Windows Platform only. We deliver on-demand hosting solutions including Shared hosting, Reseller Hosting, Cloud Hosting, Dedicated Servers, and IT as a Service for companies of all sizes. We have customers from around the globe, spread across every continent. We serve the hosting needs of the business and professional, government and nonprofit, entertainment and personal use market segments.

Transactions in SQL Server’s In-Memory OLTP are rather straight forward. While there are probably optimizations that are not discussed, the basic design pattern is fairly easy to follow and could probably be reused in other projects.

Transactions in SQL Server’s In-Memory OLTP rely on a timestamp-like construct known as a Transaction ID. A transaction uses two timestamps, one for the beginning of the operation and one that is assigned when the transaction is committed. While multiple transactions can share the same start value.

Likewise each version of a row in memory has a starting and ending transaction id. The basic rule is that a transaction can only read data when RowVersion.StartingId <= Transaction.StartingId < RowVersion.EndingId.

For a DELETE operation, the row version’s ending id is initially set to the transaction’s starting id. Then a flag is set to indicate that a transaction is in process.

UPDATE operations begin like DELETE operations with the setting of an ending transaction id on the previous row version. Then a new row version is created with a starting transaction id that is equal to the transaction’s starting id. The ending id is initially set to infinity and again an active transaction flag is set. The old row version also gets a pointer to the new row version.

An INSERT operation is the same as an UPDATE without the need to delete the previous row version.

Commit and Validation

The commit phase starts by assigning a unique transaction id to current transaction. Then a validation process begins in which the affected records are checked for isolation errors. The type of errors depend on the level of transactional isolation requested. Only three levels, Snapshot, Repeatable Read, and Serializable, are supported by memory optimized tables.

Snapshot

Just like with normal tables, inserts into memory optimized tables can fail if another transaction has attempted to insert a row at the same time. But the way it fails is a bit different. Normally one transaction has to wait for the other to complete, after which the losing transaction just sees the duplicate row and never make the insertion attempt.

Here we instead see both transactions insert their row. After which they will read back the data to see if they won the race. If they don’t error 41325 is raised with the message “The current transaction failed to commit due to a repeatable read validation failure on table [name].”

Repeatable Read Transactions

MSDN has this warning about the repeatable read isolation level, “One important thing to note is that, because the repeatable read isolation level is achieved using blocking of the other transaction, the use of this isolation level greatly increases the number of locks held for the duration of the transaction.”

Since memory optimized tables don’t have locks, repeatable read works very differently for them. Rather than blocking other transactions, it rereads the rows at the end of the transaction. If any of them have changed, the transaction is aborted. This is reflected in error code 41305 with the message “The current transaction failed to commit due to a repeatable read validation failure on table [name].”

Serializable Transactions

Like Repeatable Read, Serializable Transactions traditionally relied on locks to keep other transactions from interfering with the data being examined. So instead it will check for to see if it failed to read any valid rows or encountered phantom rows. If either occurs then again the transaction will be aborted.

Post Processing

If validation is successful, the ending transaction id of each affected row version is set to the transaction’s ending id. Likewise the starting id for new row versions (e.g. from inserts and updates) is set to the transaction’s ending id. The active flags are cleared and the indexes are updated to point to the new records.

Garbage Collection

It should be noted that the indexes are not necessarily updated to remove pointers to the old row versions. Nor are the old versions deleted immediately.

Instead the Memory Optimized Tables require the use of a reference counted garbage collector. Details are not available yet, but based on the rest of the design its behavior is predictable. The GC will need to start at the indexes and check to see which of them point to out of date rows. When detected, it can decrement the reference counter and update the index to point to the most recent version of the row. If the counter reaches zero, then the row version is deleted.

The tricky part with the garbage collector is to know which rows to look at in the first place. One would speculate that simply iterating over all the rows of each index would be rather cost prohibitive.

Design Notes

When using In-Memory OLTP, developers need to be much more aware of their access patterns. If code isn’t written to avoid overlapping transactions then the resulting isolation level violations will make aborted transactions much more common than they would be using traditional tables.

Sometimes you may face a case where you want to completely remove all tables, views & stored procedures in a MS SQL 2014 database without removing the system views, system tables,  and system stored procedures or without having to delete the database as a whole and recreating it.

You can right-click on each table one by one and try to delete them, only to find out that a foreign key constraint prevents you from doing so, at which time you have to try and figure out how each table relates to the other, and remove them in the correct sequence. If you have a few hundred or even thousand database objects this could take a long time! Finally, We found the solution. The script below will take care of this for you in one shot and give you a clean database to work with.

/* Drop all non-system stored procs */
DECLARE @name VARCHAR(128)
DECLARE @SQL VARCHAR(254)
SELECT @name = (SELECT TOP 1 [name]
FROM sysobjects WHERE [TYPE] = 'P' AND category = 0 ORDER BY [name])
WHILE @name IS NOT NULL
BEGIN
    SELECT @SQL = 'DROP PROCEDURE [dbo].[' + RTRIM(@name) +']'
    EXEC (@SQL)
    PRINT 'Dropped Procedure: ' + @name
    SELECT @name = (SELECT TOP 1 [name] FROM sysobjects WHERE [TYPE] = 'P' AND category = 0 AND [name] > @name ORDER BY [name])
END
GO

/* Drop all views */
DECLARE @name VARCHAR(128)
DECLARE @SQL VARCHAR(254)SELECT @name = (SELECT TOP 1 [name] FROM sysobjects WHERE [TYPE] = 'V' AND category = 0 ORDER BY [name])
WHILE @name IS NOT NULL
BEGINSELECT @SQL = 'DROP VIEW [dbo].[' + RTRIM(@name) +']'
EXEC (@SQL)    PRINT 'Dropped View: ' + @name
SELECT @name = (SELECT TOP 1 [name] FROM sysobjects WHERE [TYPE] = 'V' AND category = 0 AND [name] > @name ORDER BY [name])
END
GO

/* Drop all functions */
DECLARE @name VARCHAR(128)
DECLARE @SQL VARCHAR(254)
SELECT @name = (SELECT TOP 1 [name] FROM sysobjects WHERE [TYPE] IN (N'FN', N'IF', N'TF', N'FS', N'FT') AND category = 0 ORDER BY [name])
WHILE @name IS NOT NULL
BEGIN
    SELECT @SQL = 'DROP FUNCTION [dbo].[' + RTRIM(@name) +']'
    EXEC (@SQL)
    PRINT 'Dropped Function: ' + @name
    SELECT @name = (SELECT TOP 1 [name] FROM sysobjects WHERE [TYPE] IN (N'FN', N'IF', N'TF', N'FS', N'FT') AND category = 0 AND [name] > @name ORDER BY [name])
END
GO

/* Drop all Foreign Key constraints */
DECLARE @name VARCHAR(128)
DECLARE @CONSTRAINT VARCHAR(254)
DECLARE @SQL VARCHAR(254)
SELECT @name = (SELECT TOP 1 TABLE_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE constraint_catalog=DB_NAME() AND CONSTRAINT_TYPE = 'FOREIGN KEY' ORDER BY TABLE_NAME)
WHILE @name IS NOT NULL
BEGIN
 SELECT @CONSTRAINT = (SELECT TOP 1 CONSTRAINT_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE constraint_catalog=DB_NAME() AND CONSTRAINT_TYPE = 'FOREIGN KEY' AND TABLE_NAME = @name ORDER BY CONSTRAINT_NAME)    WHILE @CONSTRAINT IS NOT NULL
    BEGIN
        SELECT @SQL = 'ALTER TABLE [dbo].[' + RTRIM(@name) +'] DROP CONSTRAINT [' + RTRIM(@CONSTRAINT) +']'
        EXEC (@SQL)
        PRINT 'Dropped FK Constraint: ' + @CONSTRAINT + ' on ' + @name

        SELECT @CONSTRAINT = (SELECT TOP 1 CONSTRAINT_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE constraint_catalog=DB_NAME() AND CONSTRAINT_TYPE = 'FOREIGN KEY' AND CONSTRAINT_NAME <> @CONSTRAINT AND TABLE_NAME = @name ORDER BY CONSTRAINT_NAME)
    END
SELECT @name = (SELECT TOP 1 TABLE_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE constraint_catalog=DB_NAME() AND CONSTRAINT_TYPE = 'FOREIGN KEY' ORDER BY TABLE_NAME)
END
GO

/* Drop all Primary Key constraints */
DECLARE @name VARCHAR(128)
DECLARE @CONSTRAINT VARCHAR(254)
DECLARE @SQL VARCHAR(254)
SELECT @name = (SELECT TOP 1 TABLE_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE constraint_catalog=DB_NAME() AND CONSTRAINT_TYPE = 'PRIMARY KEY' ORDER BY TABLE_NAME)
WHILE @name IS NOT NULL
BEGIN
    SELECT @CONSTRAINT = (SELECT TOP 1 CONSTRAINT_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE constraint_catalog=DB_NAME() AND CONSTRAINT_TYPE = 'PRIMARY KEY' AND TABLE_NAME = @name ORDER BY CONSTRAINT_NAME)    WHILE @CONSTRAINT IS NOT NULL
    BEGIN
        SELECT @SQL = 'ALTER TABLE [dbo].[' + RTRIM(@name) +'] DROP CONSTRAINT [' + RTRIM(@CONSTRAINT)+']'
        EXEC (@SQL)
        PRINT 'Dropped PK Constraint: ' + @CONSTRAINT + ' on ' + @name
        SELECT @CONSTRAINT = (SELECT TOP 1 CONSTRAINT_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE constraint_catalog=DB_NAME() AND CONSTRAINT_TYPE = 'PRIMARY KEY' AND CONSTRAINT_NAME <> @CONSTRAINT AND TABLE_NAME = @name ORDER BY CONSTRAINT_NAME)
    END
SELECT @name = (SELECT TOP 1 TABLE_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE constraint_catalog=DB_NAME() AND CONSTRAINT_TYPE = 'PRIMARY KEY' ORDER BY TABLE_NAME)
END
GO
/* Drop all tables */

We are so happy that Microsoft has announced the SQL 2014. With this newest launch, we are so happy to introduce our SQL 2014 hosting our hosting environment.

SQL Server 2014 helps organizations by delivering:

1. Mission Critical Performance in SQL 2014

SQL Server 2014 delivers new in-memory capabilities built into the core database for OLTP and data warehousing, which complement existing in-memory data warehousing and business intelligence capabilities for a comprehensive in-memory database solution. In addition to in-memory, there are new capabilities to improve the performance and scalability for your mission critical applications.

• New In-Memory OLTP – built in to core SQL Server database and uniquely flexible to work with traditional SQL Server tables allowing you to improve performance of your database applications without having to refresh your existing hardware
• Enhanced In-Memory ColumnStore for Data Warehousing – now updatable with even faster query speeds and with greater data compression for more real-time analytics support.
• New buffer pool extension support to non-volatile memory such as solid state drives (SSDs) – Increase performance by extending SQL Server in-memory buffer pool to SSDs for faster paging.
• Enhanced AlwaysOn – Built upon the significant capabilities introduced with SQL Server 2012, delivers mission critical availability with up to 8 readable secondaries and no downtime during online indexing operations.
• Greater scalability of compute, networking and storage with Windows Server 2012 R2
• Enhanced Resource Governance – With Resource Governor, SQL Server today helps you with scalability and predictable performance, and in SQL Server 2014, new capabilities allow you to manage IO, in addition to compute and memory to provide more predictable performance.
• Enhanced Separation of Duties – Achieve greater compliance with new capabilities for creating role and sub-roles. For example, a database administrator can now manage the data without seeing sensitive data or personally identifiable information.

2. Faster Insights on Any Data

SQL Server 2014 is at the heart of our modern data platform which delivers a comprehensive BI solution that simplifies access to all data types big and small with additional solutions like HDInsight, Microsoft’s 100% Apache compatible Hadoop distribution and project code name “Data Explorer”, which simplifies access to internal or external data. New data platform capabilities like Polybase included in Microsoft Parallel Data Warehouse allows you to integrate queries across relational and non-relational data using your existing SQL Server skills.

With SQL Server 2014, you can accelerate insights with our new in-memory capabilities with faster performance across workloads. You can continue to refine and manage data using Data Quality Services and Analysis Services in SQL Server and finally analyze the data and unlock insights with powerful BI tools built into Excel and SharePoint.

Conclusion

If you want to try the newest SQL 2014 as your database hosting, then you don’t need to look further as we (HostForLIFE.eu) has supported the latest SQL 2014 hosting on our hosting environment. You just need to start as low as €3.00/month to get the latest SQL 2014 hosting. Are you ready??

Microsoft's new release of SQL Server 2014 comes pretty close on the heels of the last SQL Server 2012 release. In this article I will be exploring and explaining about In Memory OLTP with SQL Server 2014.

SQL Server 2014 CTP is available for download and evaluation and it contains a couple of exciting performance enhancements. One of these is OLTP databases optimized to be memory resident.

This is 2 concern that I want to check:

1. Because SQL 2014 is only at CTP1 compatibility with earlier versions is not guaranteed and therefore Microsoft won’t let you install this side by side with any other versions.

2. Because of gotcha number one you’ll probably decide,  for example you install it in a virtual machine. Using Oracle Virtual Box you might strike this error when you try to create a memory optimised filegroup:

Msg 41342, Level 15, State 1, Line 5

The model of the processor on the system does not support creating filegroups with MEMORY_OPTIMIZED_DATA. This error typically occurs with older processors. See SQL Server Books Online for information on supported models.

To resolve this – navigate to the virtual box install folder and run this command:

VBoxManage setextradata [vmname] VBoxInternal/CPUM/CMPXCHG16B 1

I had to restart the guest and the host for this change to stick.

DDL.

Now with that all working time to create the database.

CREATE DATABASE InMemDB
GO

Add a filegroup for the in memory objects (alter the path for your instance).

ALTER DATABASE InMemDB ADD FILEGROUP InMemDB_mod CONTAINS MEMORY_OPTIMIZED_DATA
ALTER DATABASE InMemDB ADD FILE (name='InMemDB_mod1', filename='E:\SQLData\InMemDB_mod1') TO FILEGROUP InMemDB_mod
GO

A database can contain a mix of in memory tables (and the new natively compiled stored procedures) and traditional disk based tables. The in memory tables are marked with the keywords MEMORY_OPTIMIZED=ON and they must have at least one hash index – it is not possible to create a heap.

USE InMemDB
GO

CREATE TABLE dbo.Table1 (
   Id_tb1 int not null primary key nonclustered hash with (bucket_count=20480),

   Int_Val int not null index ix_Int_Val nonclustered hash with (bucket_count=10240),
   CreateDate datetime2 not null,
   [Description] varchar(255)
)
WITH (MEMORY_OPTIMIZED=ON)
GO

CREATE TABLE dbo.Table2 (
   Id_tb2 int not null primary key nonclustered hash with (bucket_count=4096),
   Int_Val int not null,
   CreateDate datetime2 not null,
   Id_tb1_fk int,
   index ix_Int_Val nonclustered hash (Int_Val) with (bucket_count=4096)
)
WITH (MEMORY_OPTIMIZED=ON, DURABILITY=SCHEMA_ONLY)
GO

In memory tables have a durability property that can be set to SCHEMA_AND_DATA or SCHEMA_ONLY. There are two new columns in sys.tables to track this. SCHEMA_ONLY tables are volatile, SCHEMA_AND_DATA are persisted to disk.

SELECT name,type_desc,durability_desc FROM sys.tables

The hash indexes can be tracked with a new catalog view.

SELECT * FROM sys.hash_indexes

DML.

The two big selling points of in memory OLTP are improved performance with entirely memory resident data accessed with hash indexes and use of an optimistic multiversion concurrency control … no locking.

So let’s run some DML and test this out. Insert a row:

BEGIN TRAN

INSERT dbo.Table1 VALUES (1,427,getdate(),'Insert transaction')

SELECT * FROM sys.dm_tran_locks WHERE request_session_id = @@SPID
SELECT * FROM sys.dm_db_xtp_transactions

COMMIT TRAN

The sys.dm_tran_locks DMV will return a shared object lock and a Schema shared lock on the database. The new sys.dm_db_xtp_transactions is a new DMV for in memory OLTP and returns information about current transactions against the database.

Now run an update inside an explicit transaction. Note that we will get an error if we don’t use the WITH(SNAPSHOT) hint.

BEGIN TRAN

UPDATE dbo.Table1  WITH (SNAPSHOT)
SET [Description] = 'Updated transaction'
WHERE Id_tb1 = 1

SELECT * FROM sys.dm_tran_locks WHERE request_session_id = @@SPID
SELECT * FROM sys.dm_db_xtp_transactions

In another session run a select.

SELECT *
FROM dbo.Table1
WHERE Id_tb1 = 1

The select returns the pre updated version of the row – but there is no blocking. This is achieved using a versioning system that is, despite the keyword SNAPSHOT, not the familiar SNAPSHOT ISOLATION row versioning based in tempdb. (Details of how this is achieved can be found in the readings at the end of this blog.)

Commit the update and run the following delete.

BEGIN TRAN

DELETE dbo.Table1 WITH (SNAPSHOT)
WHERE Id_tb1 = 1

SELECT * FROM sys.dm_tran_locks WHERE request_session_id = @@SPID
SELECT * FROM sys.dm_db_xtp_transactions

Note that the delete transaction is still only holding lightweight shared locks. The select in another session will now return the updated row.

SELECT *
FROM dbo.Table1
WHERE Id_tb1 = 1

Commit the delete transaction.

Concluding Remarks.

As of CTP1 not all of the features are fully working and there are a number of limitations that are likely to roll into RTM – hopefully to be slowly improved in subsequent releases. The whitepaper in the reading list below has an exhaustive list of these restrictions but one of the biggest ones for me is that foreign key constraints are not supported.

Another big feature that is available for in memory tables are the native stored procedures. I didn’t touch on these but they are tipped to offer highly optimised performance. A stored procedure that only operates against in memory tables can be marked using the WITH NATIVE_COMPILATION option and this will compile the sproc into a DLL using C code.