Database Reference
In-Depth Information
you may have committed transactions, but you could still lose that data, which is different from how standard tables
work within SQL Server. The most commonly known uses for data that isn't durable are things such as session state
or time-sensitive information such as an electronic shopping cart. Anyway, in-memory storage is not the same as the
usual storage within your standard relational tables. So, a separate file group and files must be created. To do this, you
can just alter the database, shown here:
ALTER DATABASE InMemoryTest
ADD FILEGROUP InMemoryTest_InMemoryData
CONTAINS MEMORY_OPTIMIZED_DATA;
ALTER DATABASE InMemoryTest
ADD FILE (NAME='InMemoryTest_InMemoryData',
FILENAME ='D:\Data\InMemoryTest_InMemoryData.ndf')
TO FILEGROUP InMemoryTest_InMemoryData;
I would have simply altered the
AdventureWorks2012
database that you've been experimenting with, but another
consideration for in-memory optimized tables is that you can't remove the special filegroup once it's created. You can
only ever drop the database. That's why I'll just experiment with a separate database. It's safer.
There are some limitations to features available to a database using in-memory OLTP.
DBCC CHECKDB
: You can run consistency checks, but the memory-optimized tables will be
skipped. You'll get an error if you attempt to run
DBCC CHECKTABLE
.
•
AUTO_CLOSE
: This is not supported.
•
DATABASE SNAPSHOT
: This is not supported.
•
ATTACH_REBUILD_LOG
: This is also not supported.
•
Once these modifications are complete, you can now begin to create in-memory tables in your system.
Create Tables
Once the database setup is complete, you now have the capability to create tables that will be memory optimized
as described earlier. The actual syntax is quite straightforward. I'm going to replicate, as much as I can, the
Person.Address
table from
AdventureWorks2012
.
USE DATABASE InMemoryTest;
GO
CREATE TABLE dbo.Address
(
AddressID INT IDENTITY(1, 1)
NOT NULL
PRIMARY KEY NONCLUSTERED HASH WITH (BUCKET_COUNT = 50000),
AddressLine1 NVARCHAR(60) NOT NULL,
AddressLine2 NVARCHAR(60) NULL,
City NVARCHAR(30) NOT NULL,
StateProvinceID INT NOT NULL,
PostalCode NVARCHAR(15) NOT NULL,
--[SpatialLocation geography NULL,
--rowguid uniqueidentifier ROWGUIDCOL NOT NULL CONSTRAINT DF_Address_rowguid DEFAULT
(newid()),
ModifiedDate DATETIME
NOT NULL
