Search code examples
sql-serverintegration-testingin-memory-databasedatabase-testing

Does SQL Server support in-memory database?

I have basically a testing scenario, where I

  1. Create database
  2. Fill it with some data
  3. Execute the business logic to be tested, which modifies the data. I don't own the business logic implementation or the DB schema. I must test what is already there.
  4. Assert the data are changed as expected
  5. Delete the database

Does SQL Server support something like that in memory only, so that I can speed it up by removing #1 and #5? Obviously I don't need persistence.

Solution

  • SQL Server has in-memory OLTP. This feature is quite close to what you are looking into. Starting SQL Server 2016 SP1 it is possible in all editions, including sql express.

    Obviously I don't need persistence

    The option DURABILITY=SCHEMA_ONLY preserves only the metadata. Such a scenario can be optimal for staging tables because it results in a lock/latch/log free way of data load. Obviously, the table will not survive instance/service restart.

    CREATE DATABASE [Test]
GO 
-- Memory Optimized FG
ALTER DATABASE [Test] ADD FILEGROUP [MemoryOptimizedFG] CONTAINS MEMORY_OPTIMIZED_DATA 
ALTER DATABASE [Test] ADD FILE (name='Test1', filename='D:\SQLData\TestInMemory') TO FILEGROUP [MemoryOptimizedFG]
GO

CREATE TABLE dbo.[TestTable] (
    ID int NOT NULL IDENTITY (1, 1) PRIMARY KEY NONCLUSTERED,
    ColumnID int NOT NULL,
    Description varchar(100) NOT NULL,
    dateAndTime datetime NOT NULL
)  WITH (MEMORY_OPTIMIZED=ON, DURABILITY=SCHEMA_ONLY) 
GO

    References: