Worth Corner, Crawley, RH10 7SL

Support Hours - Mon-Sat, 8.00-18.00

Welcome to our SQL Server blog

This blog was created in order to pass on some of our knowledge and advice in the hope that the community might find it useful.

Obviously Microsoft SQL Server is our area of expertise and therefore this is our most thorough blog with everything from the basic through to the the incredibly advanced and, as such, we should have something for everyone within our pages.


Creating an In-Memory Table
Published: Nov 29, 2020
You know what in-memory tables are, and you have your in-memory filegroup, so now you need to know how to create one…

First thing to note is that all in-memory tables MUST have at least one index. With that in mind, let’s jump right in:

create table
testMem
(
      
id int identity primary key nonclustered,
      
miscData varchar(100)
)
with (memory_optimized = on)
go


That’s it… you now have your first in-memory table. Exciting.

The key is, as you can guess, the last line of the declaration and this is where we can make some alterations based on our type of in-memory storage. The above caters for in-memory schema AND data. Therefore, on server restart SQL will recreate our table in memory and maintain our data as well.

If we aren’t interested in keeping our data but simply need the schema to remain after a restart then we need to add a durability clause:

create table testMem
(
      
id int identity primary key nonclustered,
      
miscData varchar(100)
)
with (memory_optimized = on, durability=schema_only)
go


There are a couple of things to note… firstly you cannot create a clustered index on an in-memory table. This is due to the nature in which the data is held, but therefore all indexes must be nonclustered. Secondly, any indexes you do wish to declare on an in-memory table must be done at table creation and as part of the creation script. For example, this will lead to an error:

create nonclustered index ix_testMem on testMem(miscData)



Therefore you have to do this inline:

create table testMem
(
      
id int identity primary key nonclustered,
      
miscData varchar(100),
      
index ix_testMem (miscData)
)
with (memory_optimized = on, durability=schema_only)
go


With this in mind, you need to remember to think through your architecture carefully when using in-memory tables because once they’re in place they aren’t easy to change. As we pass through more iterations of SQL Server (currently 2017) in-memory is supporting more and more functionality (such as ALTER), but it would still be advisable to get your schema as accurate as you can from the start.

Either way, you now know how to create them and can go ahead and have a play.



Leave a Comment
Your email address will not be published. All fields are mandatory.
NB: Comments will only appear once they have been moderated.

SQL  World  CEO
Kevin  Urquhart

iPhone Selfie

I am a SQL Server DBA, Architect, Developer, Trainer, and CEO of SQL World. This is my blog in which I’m simply trying to share my SQL knowledge and experiences with the world.

Categories


© Copyright 2020 SQLTraining Ltd.