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.