Category Archives: Hekaton

Hekaton Part 7: Error Handling

One in a series of posts about Microsoft’s In-Memory OLTP Functionality

In the previous post in this series, I discussed three validation errors that are unique to Hekaton and their common causes:

  • Error 41302 – Generated when two concurrent sessions update the same record.
  • Error 41303 – Generated when a concurrent update causes a repeatable read failure.
  • Error 41325 – Generated when a concurrent insert causes a serializable failure.

There is also error 41301 that I will address later in this series.

Applications that access databases containing In-Memory tables need to be aware of these errors and be able to respond gracefully when they occur.

There is one error condition that has been around for a long time in SQL Server that closely parallels these three errors, and that is the deadlock (error 1205). Deadlocks, however, are largely not accounted for in error handling because they tend to be rare, and can largely be avoided by good coding practices (specifically, consistent order of access to tables and other resources, when at all possible). On the other hand, the Hekaton errors will, in practice, be more commonplace, and I consider them to be “normal” events in practice, especially as the throughput on the system becomes heavy.

The appropriate response, in most situations, is to retry the operation when this error is raised. Microsoft has an example of handling errors in stored procedures, but applications also need to address these errors.

Here is a stub of a data access class that demonstrates how this can be done.

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace HekatonErrorTrapping
{
    class Dal : IDisposable
    {
        private SqlConnection _connection;

        public Dal()
        {
            SqlConnectionStringBuilder sb = new SqlConnectionStringBuilder();
            sb.DataSource = @".";
            sb.InitialCatalog = "Hekaton";
            sb.IntegratedSecurity = true;

            _connection = new SqlConnection(sb.ToString());
            _connection.Open();
        }

        public SqlTransaction StartTransaction()
        {
            return _connection.BeginTransaction();
        }

        public void UpdateProductUnitPrice(int productId, decimal newPrice, SqlTransaction transaction)
        {
            string sql = @"update dbo.Product with (snapshot) set UnitPrice = @NewPrice where ProductID = @ProductId;";
            SqlParameter idParameter = new SqlParameter("@ProductId", productId);
            SqlParameter priceParameter = new SqlParameter("@NewPrice", newPrice);

            using (SqlCommand command = new SqlCommand(sql, _connection, transaction))
            {
                command.Parameters.Add(idParameter);
                command.Parameters.Add(priceParameter);

                int retryCount = 5;
                bool isComplete = false;
                while (isComplete == false)
                {
                    try
                    {
                        command.ExecuteNonQuery();
                        isComplete = true;
                    }
                    catch (SqlException ex)
                    {
                        switch (ex.Number)
                        {
                            // 41302: Updated a record that has been updated since the txn started.
                            case 41302:

                            // 41305: Repeatable read validation failure.
                            case 41305:

                            // 41325: Serializable validation failure.
                            case 41325:

                            // 41301: Commit dependency on a transaction that aborted.
                            case 41301:

                            // 1205: Deadlock (not applicable to in-memory tables, but same principles apply).
                            case 1205:
                                retryCount--;
                                if (retryCount <= 0)
                                {
                                    throw;
                                }

                                // Take a very brief pause -- mostly needed in cases of very high write contention.
                                System.Threading.Thread.Sleep(1);
                                break;

                            default:
                                throw;
                        }
                    }
                }
            }
        }

        public void Dispose()
        {
            Dispose(true);
            GC.SuppressFinalize(this);
        }

        private void Dispose(bool disposing)
        {
            if (disposing)
            {
                if (_connection != null)
                {
                    _connection.Dispose();
                    _connection = null;
                }
            }
        }
    }
}

And here is an example of how to call this code.

using (Dal dal = new Dal())
{
    using (SqlTransaction transaction = dal.StartTransaction())
    {
        dal.UpdateProductUnitPrice(productId, newPrice, transaction);
        transaction.Commit();
    }
}

Now if one of these errors is generated during the update, the system will pause for one millisecond (just in case there is a lot of write contention going on; this pause may need to be longer on some systems), and retry the operation up to five times. (Of course, these values should be configurable and not hard-coded.)

There is one HUGE caveat to this, however. When one of these errors is thrown, the entire transaction is rolled back. In this simple example, the transaction is just a single update statement, but if the transaction spans a dozen statements, they all get rolled back.

This means that at the very least the application needs to be aware that the full transaction will go away, and it should wrap transaction appropriately. Better yet, the application will know how to replay the full transaction up to the point of failure and will gracefully retry the operation a given number of times before giving up.

Hekaton Part 6: MVCC Assumptions and Validation

One in a series of posts about Microsoft’s In-Memory OLTP Functionality

I originally intended to make part 6 in this series about error handling, but quickly realized that I needed to lay a bit of a foundation on multi-version concurrency control (MVCC), and particularly some of the assumptions made by MVCC and how those assumptions are subsequently confirmed.

Historically, SQL Server has used a pessimistic form of concurrency. My session sees every other connection as the enemy. Every other connection on the system is presumed to want access to my resources, and so SQL employs a locking mechanism to prevent anyone else from stomping over my work.

Now, locking and isolation levels is a complex topic in itself, but a simple example will suffice for the the purposes of this discussion. Suppose I update the Product table for ID 23, and before I commit my transaction, you come along and try to update the same row. Your connection will be blocked from proceeding until I commit or rollback, at which point you will be allowed to make the update. Under most isolation levels you won’t even be allowed to read the row I’ve updated so long as it is uncommitted.

Blocking is a good thing in that it preserved isolation, but a bad thing when everybody is always waiting on someone else.

In SQL Server 2005, Microsoft introduced a quasi-optimistic concurrency model called snapshot that addressed some of these problems. With snapshot enabled, you would be able to read the row that I’ve updated (you will see the values in the row prior to any of my changes). However, you still won’t be able to update it. You will be blocked just as always.

Snapshot isolation is frequently explained as reader don’t block writers, and writers don’t block readers.

Hekaton takes us a huge step forward toward optimistic concurrency. It makes a couple of assumptions that remove that “every other session is the enemy” paradigm. One assumption is that write conflicts are rare, so SQL always proceeds with data modifications under the assumption that no one else is going to change the same row.

A second assumption is that competing transactions are going be committed and not rolled back.

Now, sometimes these assumptions simply aren’t valid, so SQL Server will includes validation checks to make sure that things don’t go awry.

We’ll get into internals in a later part of this series to better explain how SQL does all this magic, but for now suffice it to say that SQL maintains multiple versions of the same row along with a pair of time stamps indicating when the row is valid. So when we update ProductId 23, SQL will actually create a new row in the table, and mark the original row as valid through, say, 1:17 pm, and the new row only becomes valid at 1:17 pm. When a user starts a new transaction, SQL notes the time that the transaction starts and during the course the transaction will always return the row that was valid and committed at that time.

Let’s demonstrate. We’ll open two tabs in Management Studio, each connected to our Hekaton database. In the first tab, we’ll make sure that the price on product 23 is a known value.

update dbo.Product
set UnitPrice = 23.00
where ProductID = 23;

Now in tab 1, we’ll start a transaction and modify the price.

begin transaction;

update dbo.Product with (snapshot)
set UnitPrice = 30.00
where ProductID = 23;

In tab 2, let’s examine the value.

select ProductID, UnitPrice
from dbo.Product
where ProductID = 23;

This confirms that connection 2 sees a UnitPrice of 23.00, which is the committed value of the row at the time the transaction started. Continuing in tab 2, start a new transaction:

begin transaction;

Switch back to tab 1 and commit.

commit;

Re-execute the select in connection 2, and you will still get 23.00. Again, this was the committed value of the row at the time the transaction began. But if we close the transaction and do it again:

commit;

begin transaction;

select ProductID, UnitPrice
from dbo.Product with (snapshot)
where ProductID = 23;

commit;

This time we get a unit price of 30.00, reflecting the committed price when the new transaction begins.

So that is MVCC in a nutshell.

What about the validations? If one of the MVCC assumptions doesn’t hold up, SQL Server will detect this circumstance by one of several validation methods. In some cases, these validations happen as the transaction progresses, and in cases the validation happens when the transaction commits.

In connection 1, make sure that there are no open transactions, then run the following:

begin transaction;

update dbo.Product with (snapshot)
set UnitPrice = 30.00
where ProductID = 23;

And in connection 2 we will try a simultaneous update.

begin transaction;

update dbo.Product with (snapshot)
set UnitPrice = 35.00
where ProductID = 23;

This immediately fails with an error:

Msg 41302, Level 16, State 110, Line 18
The current transaction attempted to update a record that has been updated since this transaction started. The transaction was aborted.
Msg 3998, Level 16, State 1, Line 16
Uncommittable transaction is detected at the end of the batch. The transaction is rolled back.
The statement has been terminated.

I find the wording on the 41302 error a bit odd because it’s not completely correct, but the upshot is that there are two simultaneous update operations in progress. The “write-write conflict” assumption has failed, so the error is generated. Note that SQL Server does not block the progress of the transaction, as with previous versions, but it does generate an error.

In addition to the 41302 error, we might also get 41303 and 41325 errors. These relate to violations of the repeatable read and serializable isolation level requirements. Make sure that both connections don’t have open transactions, then run this in connection 1:

begin transaction;

select ProductID, UnitPrice
from dbo.Product with (repeatableread)
where ProductID = 23;

Then back in connection 2 change the price:

begin transaction;

update dbo.Product with (snapshot)
set UnitPrice = 38.00
where ProductID = 23;

commit;

Now try to commit transaction 1:

commit;

At commit time, SQL Server will validate the repeatable read requirement and the following error will be generated.


Msg 41305, Level 16, State 0, Line 25
The current transaction failed to commit due to a repeatable read validation failure.

Finally, we can create the conditions for a 41325 error. Commit any open transactions, then in connection 1 run:

delete dbo.Product where ProductID = 1000;

begin transaction;

select ProductID, UnitPrice
from dbo.Product with (serializable)
where ProductID = 1000;

And in connection 2 run:

insert dbo.Product (ProductID, Description, UnitPrice)
values (1000, 'Product 1000', 1000.00);

Back in connection 1:

commit;


Msg 41325, Level 16, State 0, Line 35
The current transaction failed to commit due to a serializable validation failure.

Whew, that was a lot just to talk about the validations that take place (and there is actually one more validation that happens at commit time, but we’ll address that separately). With that background, we can now talk about error handling in the next post.

Hekaton Part 5: Native Procedures

One in a series of posts about Microsoft’s In-Memory OLTP Functionality

Now we come to one of the Hekaton components that is really quite cool but that I haven’t found a practical use for. Not even once.

Natively compiled stored procedures.

The cool part is that they can be really, really fast. They can be orders of magnitude faster than traditional interpreted procedures. The not so cool part is that they support only a very, very limited subset of T-SQL.

Let’s demonstrate. First, we’ll create a new table and add a couple of rows.

if exists (select * from sys.tables where name = 'Product')
	drop table dbo.Product;
create table dbo.Product
(
	ProductID int not null,
	Description nvarchar(500) not null,
	UnitPrice money not null,

	primary key nonclustered hash (ProductID) with (bucket_count = 1048576),
	index idx_Product__UnitPrice nonclustered (UnitPrice)
) with (memory_optimized = on, durability = schema_and_data);

-- Populate a few rows into the table

insert dbo.Product (ProductID, Description, UnitPrice)
values (1, 'Gizmo', 34.00),
	(2, 'Whatzit', 16.00);

Now can create a natively compiled stored procedure to update the table. This isn’t going to demonstrate speed, but it’s just to show the general idea (and the general awkwardness of the syntax).

if exists (select * from sys.objects where type = 'P' and name = 'usp_UpdateProduct')
	drop procedure usp_UpdateProduct
go
create procedure dbo.usp_UpdateProduct (@ProductID int, @NewUnitPrice money)
with native_compilation, schemabinding, execute as owner
as
begin atomic with (transaction isolation level = snapshot, language = 'us_english')
	update dbo.Product
	set UnitPrice = @NewUnitPrice
	where ProductID = @ProductID;
end
go

We can demonstrate that the procedure really works by running something like.

select ProductID, Description, UnitPrice from dbo.Product where ProductID = 1;
exec dbo.usp_UpdateProduct @ProductID = 1, @NewUnitPrice = 42.00;
select ProductID, Description, UnitPrice from dbo.Product where ProductID = 1;

Now let’s write a procedure to add a bunch of new rows. First, we will create and populate a tally table.

create table Tally
(
    Number int not null,
    constraint PK_Tally primary key nonclustered
        (Number)
) with (memory_optimized = on, durability = schema_and_data);

insert Tally (Number)
select top 1000000 row_number() over (order by v1.number)
from master.dbo.spt_values v1 cross join master.dbo.spt_values v2;

And then try this for a stored procedure:

if exists (select * from sys.objects where type = 'P' and name = 'usp_CreateNewRecords')
	drop procedure usp_CreateNewRecords
go
create procedure dbo.usp_CreateNewRecords (@RecordCount int)
with native_compilation, schemabinding, execute as owner
as
begin atomic with (transaction isolation level = snapshot, language = 'us_english')
	declare @firstId int;
	select @firstId = isnull(max(ProductID), 0) from dbo.Product;

	insert	dbo.Product (ProductID, Description, UnitPrice)
	select	t.Number + @firstId ProductID,
			'Product ' + cast(t.Number + @firstId as nvarchar(500)) Description,
			cast(t.Number + @firstId as money) UnitPrice
	from	dbo.Tally t
	where	t.Number <= @RecordCount;
end
go

Then we’ll run the procedure to add 1,000,000 records:

exec dbo.usp_CreateNewRecords @RecordCount = 1000000;

This goes pretty fast on my machine, consistently adding the 1M records in 5 to 6 seconds.

I wrote the procedure the way I did because I’ve always been taught (any experience has validated) that set-based logic is almost always superior to row-by-row processing. Hekaton definitely changes that paradigm. Let’s rewrite that procedure to do one-off inserts.

if exists (select * from sys.objects where type = 'P' and name = 'usp_CreateNewRecords')
	drop procedure usp_CreateNewRecords
go
create procedure dbo.usp_CreateNewRecords (@RecordCount int)
with native_compilation, schemabinding, execute as owner
as
begin atomic with (transaction isolation level = snapshot, language = 'us_english')
	declare @firstId int;
	select @firstId = isnull(max(ProductID), 0) from dbo.Product;

	declare @counter int = 1;
	while @counter <= @RecordCount
	begin
		insert dbo.Product (ProductID, Description, UnitPrice)
		values (@firstId + @counter,
			'Product ' + cast(@firstId + @counter as nvarchar(500)),
			cast(@firstId + @counter as money));
		select @counter = @counter + 1;
	end
end
go

Now that insert runs in 3 to 4 seconds. Not a dramatic difference, but intriguing to me because it seems to be a game-changer.

Finally, the limitations. This is why I’ve not found natively compiled procedures to be terribly compelling, because I am constantly running up against some limitation. The Microsoft master In-Memory Limitations page lists them. Scroll down to the Natively Compiled Stored Procedures section, which occupies the majority of the page, and you’ll see the long list. I won’t repeat them all here, but the big ones for me are lack of support for CTEs and subqueries, CASE statements, temp tables, non-BIN2 limits, lots of unsupported join types, no IN or OR or LIKE, no DISTINCT and limitations on aggregations, no ROW_NUMBER(), and of course no references to disk-based tables.

Whew! Even my abbreviated list is pretty long!

Hekaton Part 4: Isolation Levels

One in a series of posts about Microsoft’s In-Memory OLTP Functionality

One thing to keep in mind about querying Hekaton tables is that the isolation level you use is quite important. Queries must be run at the snapshot, repeatable read or serializable isolation level. In the previous post, we executed a select query. Let’s add an explicit isolation level.

set transaction isolation level read committed;

select EmployeeID, FirstName, LastName, Salary
from dbo.Employee
order by LastName, FirstName;

And this query runs just fine. We execute at the read committed level using snapshot isolation.

But change that “read committed” to just about anything else (read uncommitted, repeatable read) and the statement will produce an error.

Msg 10794, Level 16, State 80, Line 3
The transaction isolation level 'READ UNCOMMITTED' is not supported with memory optimized tables.

Msg 41333, Level 16, State 1, Line 3
The following transactions must access memory optimized tables and natively compiled stored procedures under snapshot isolation: RepeatableRead transactions, Serializable transactions, and transactions that access tables that are not memory optimized in RepeatableRead or Serializable isolation.

Msg 41333, Level 16, State 1, Line 3
Memory optimized tables and natively compiled stored procedures cannot be accessed or created when the session TRANSACTION ISOLATION LEVEL is set to SNAPSHOT.

Things also start to break down when you use an explicit transaction (instead of the autocommit transaction above).

set transaction isolation level read committed;

begin transaction;

select EmployeeID, FirstName, LastName, Salary
from dbo.Employee
order by LastName, FirstName;

commit;

Msg 41368, Level 16, State 0, Line 5
Accessing memory optimized tables using the READ COMMITTED isolation level is supported only for autocommit transactions. It is not supported for explicit or implicit transactions. Provide a supported isolation level for the memory optimized table using a table hint, such as WITH (SNAPSHOT).

The error message give the solution: use the with (snapshot) table hint.

set transaction isolation level read committed;

begin transaction;

select EmployeeID, FirstName, LastName, Salary
from dbo.Employee with (snapshot)
order by LastName, FirstName;

commit;

There is a database-level setting to allow omitting the table hint.

alter database Hekaton
set memory_optimized_elevate_to_snapshot = on;

And now the table hint can be left out of the query.

set transaction isolation level read committed;

begin transaction;

select EmployeeID, FirstName, LastName, Salary
from dbo.Employee
order by LastName, FirstName;

commit;

Things get a bit more interesting when queries access both Hekaton tables and disk-based tables. Suppose we have an on-disk Customer table and we want to note which Employees have certain relationships to a given Customer.

create table CustomerEmployeeRelationship
(
	RelationshipId int not null identity(1,1) primary key clustered,
	CustomerId int not null,
	EmployeeId int not null,
	RelationshipTypeId int not null
);

Side note: Trying create a foreign key back to the Employee table won’t work, another limitation of Hekaton tables.

alter table dbo.CustomerEmployeeRelationship
add constraint fk_CustomerEmployeeRelationship_EmployeeId
foreign key (EmployeeId) references dbo.Employee (EmployeeID);


Msg 10794, Level 16, State 10, Line 57
The feature 'FOREIGN KEY' is not supported with memory optimized tables.
Msg 1750, Level 16, State 0, Line 57
Could not create constraint or index. See previous errors.

Let’s find customer-employee relationships of a certain type:

select rel.CustomerId, rel.EmployeeId
from dbo.Employee emp
inner join dbo.CustomerEmployeeRelationship rel
on emp.EmployeeID = rel.EmployeeId
where rel.RelationshipTypeId = 7;

That works fine and well, but some combinations of isolation levels don’t mix and match.

set transaction isolation level repeatable read;

select rel.CustomerId, rel.EmployeeId
from dbo.Employee emp with (repeatableread)
inner join dbo.CustomerEmployeeRelationship rel
on emp.EmployeeID = rel.EmployeeId
where rel.RelationshipTypeId = 7;


Msg 41333, Level 16, State 1, Line 74
The following transactions must access memory optimized tables and natively compiled stored procedures under snapshot isolation: RepeatableRead transactions, Serializable transactions, and transactions that access tables that are not memory optimized in RepeatableRead or Serializable isolation.

Hekaton Part 3: Querying In-Memory Tables

One in a series of posts about Microsoft’s In-Memory OLTP Functionality

So now that we’ve created our Hekaton table, we should be able to query and modify it just like any other table, right?

Actually, yes. OK, so there are a few limitations, but by and large this is the part of Hekaton that is the most fully supported part of the product.

insert dbo.Employee (EmployeeID, FirstName, LastName, Salary)
values
(1, 'Marie', 'Salazar', 106779.90),
(2, 'Jason', 'Brown', 85553.93),
(3, 'Felicia', 'Normandin', 52278.50),
(4, 'Peter', 'Sabatino', 55018.27),
(5, 'Gregory', 'Mannan', 66715.94);

Selecting from the data works well.

select EmployeeID, FirstName, LastName, Salary
from dbo.Employee
order by LastName, FirstName;

The data can be modified just like normal.

update dbo.Employee
set Salary = 57219.00
where EmployeeID = 4;

The table can be joined to other tables, whether another Hekaton table or a good old-fashioned disk table, so long as the other table in in the same database. If you try to cross over to a different database, you get this error:

Msg 41317, Level 16, State 1, Line 20
A user transaction that accesses memory optimized tables or natively compiled procedures cannot access more than one user database or databases model and msdb, and it cannot write to master.

Some constructs aren’t supported. This MERGE statement is valid for a disk-based table.

merge dbo.Employee emp
using (values (5, 'Gregory', 'Mannan', 69384.58), (6, 'Michelle', 'Irvin', 80221.66) )
as src (EmployeeID, FirstName, LastName, Salary)
on emp.EmployeeID = src.EmployeeID
when matched then
	update set FirstName = src.FirstName,
		LastName = src.LastName,
		Salary = src.Salary
when not matched by target then
	insert (EmployeeID, FirstName, LastName, Salary)
	values (src.EmployeeID, src.FirstName, src.LastName, src.Salary);

But for a Hekaton table we get an error:

Msg 10794, Level 16, State 100, Line 25
The operation 'MERGE' is not supported with memory optimized tables.

(However, using a Hekaton table in a MERGE statement but not as the target is OK.)

truncate table dbo.Employee;

Msg 10794, Level 16, State 92, Line 37
The statement 'TRUNCATE TABLE' is not supported with memory optimized tables.

So yeah, a few limitations on querying, but most things work just fine.

Hekaton Part 2: Table Limitations

One in a series of posts about Microsoft’s In-Memory OLTP Functionality

One the big bummers about the first version of Hekaton is the limitations, which are legion. Microsoft has the complete list, but here are the big ones as far as I’m concerned.

Once you’ve created a table structure, it can’t be modified. No new columns, no new indexes. If you need to change the schema, you need to copy out the data somewhere else, drop and recreate the table with the modified structure, then copy the data back. This is tedious at best for small data sets, but a possible killer for larger tables.

A table cannot have more than 8 indexes. It need to have at least one index, a primary key, but otherwise none of the indexes can be unique. Also, you cannot update the primary key columns. Oh, and if a index is sorted ascending, SQL can only use that index in ascending mode. If queries need descending order, you’ll have to create a second index on the same keys, but in the reverse order.

There is a hard limit of 8,060 bytes per row, enforced at table create time. Disk-based tables had a similar limitation at one point, but I sure have liked things since that went away.

As a natural extension of the 8,060 byte limit, tables cannot have LOB columns.

No foreign keys. No check constraints.

Any string columns in an index must be in a BIN2 collation. This is a big one, in my experience. We’ve come to expect certain behavior from certain collations, and not having this behavior has been a deal-breaker in a number of circumstances when I’ve considered using an in-memory table.

A note about indexes that I didn’t cover before is that are two types of indexes. One is the hash index, and you would create one by simply declaring a nonclustered hash index, and include a bucket count based on how many items you expect the index will ultimately hold. Hash indexes are more useful if the query specifies an equality.

index idx_Product__UnitPrice nonclustered hash (UnitPrice)
with (bucket_count = 1048576)

Range indexes are useful for finding ranges of data, such as when the query uses an inequality or a range of values. To create a range index, omit the HASH keyword.

index idx_Product__UnitPrice nonclustered (UnitPrice)

Hekaton Part 1: Getting started

One in a series of posts about Microsoft’s In-Memory OLTP Functionality

So, back in SQL Server 2014, Microsoft introduced a new technology by the name of In-Memory OLTP.  It was code named Hekaton, which is a much cooler name.  And shorter.  And easier to say.  So I’m just going to keep calling it Hekaton.

This post is part one of what I expect will be a long series of brain dumps on how to use Hekaton as well as some of the internal that I have picked up over the past couple of years.  With the run-up to SQL Server 2016, we’ll also see some pretty good enhancements, so I’ll address these as well in due course.

Back when Hekaton was first announced (and once I started to wrap my head around it), I thought it was the coolest thing ever, and that it was going to revolutionize SQL Server.  Once it came out, it was quickly appparent that it was a “version 1” product and that there were just too many limitations around it to be truly useful from the get-go.

So it didn’t exactly set the world on fire, and even with many improvements coming in “version 2,” it still has a ways to go.  I am still firmly of the belief that the day will come when in-memory technology will become the de facto standard for new tables.

So, with that background and that hope for the future, let’s start with how to enable Hekaton at the database and how to create a new in-memory table.

create database Hekaton
on primary (name = 'Hekaton',
	filename = 'D:\sql\data\Hekaton.mdf',
	size = 25600KB, filegrowth = 5120KB),
filegroup InMemoryFileGroup contains memory_optimized_data
	(name = 'InMemoryFiles',
	filename = 'D:\sql\HekatonInMemoryFileGroup')
log on (name = 'Hekaton_log',
	filename = 'D:\sql\log\Hekaton_log.ldf',
	size = 10240KB, filegrowth = 10240KB);
use Hekaton;

create table Employee
(
	EmployeeID int not null,
	FirstName varchar(50) not null,
	LastName varchar(50) not null,
	Salary money not null,
	constraint PK_Employee primary key nonclustered
		(EmployeeID)
) with (memory_optimized = on, durability = schema_and_data);