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)