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.