Does SQL Server support in-memory database?

Question

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.

Answer 1

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:

• https://www.red-gate.com/simple-talk/sql/database-administration/in-memory-oltp-understanding-memory-optimized-tables/
• https://learn.microsoft.com/en-us/sql/relational-databases/in-memory-oltp/in-memory-oltp-in-memory-optimization?view=sql-server-2017