CREATE TABLE Customer Customer ID INT NOT NULL
CREATE TABLE [Customer]( [Customer. ID] INT NOT NULL PRIMARY KEY NONCLUSTERED, [Name] NVARCHAR(250) NOT NULL, [Customer. Since] DATETIME 2 NULL, INDEX [ICustomer. Since] NONCLUSTERED (Customer. ID, Customer. Since) ) WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_AND_DATA); This table is durable (default). Non-durable tables: DURABILITY=SCHEMA_ONLY
CREATE PROCEDURE [dbo]. [Insert. Order] @id INT, @date DATETIME 2 WITH NATIVE_COMPILATION, SCHEMABINDING AS BEGIN ATOMIC WITH (TRANSACTION ISOLATION LEVEL = SNAPSHOT, LANGUAGE = N‘Dutch') -- insert T-SQL here END
Durabilit y level Configuration Pros/Cons Scenarios Full durability Default Pro: • Every committed change is guaranteed to survive failure Con: • Latency impact: every commit requires log IO Default: most scenarios need full durability Delayed durability • Transaction commit time DURABILITY=SCHEMA_AND_DATA Pro: COMMIT WITH • Low latency due to no log IO in (DELAYED_DURABILITY=ON) transaction execution path • Atomic block of native procedure • Efficient log IO due to batching BEGIN ATOMIC WITH Con: (DELAYED_DURABILITY=ON, …) • Limited data loss on failure • Database level ALTER DATABASE CURRENT SET (usually ~60 K or ~1 ms worth) Low latency requirements Table creation • Transient data such as session state • Caching • ETL (staging tables) DELAYED_DURABILITY=FORCED Nondurable tables DURABILITY=SCHEMA_ONLY Pro: • No IO at all Con: • Lose data on failure Can accept some data loss OR Copy of recent data exists elsewhere in case of failure Always. On auto-failover (sync replicas) with low latency
In-Memory OLTP perf demo Memory-optimized table variables and temp tables
Domain controllers
Domain controllers
Sensors Temporal memoryoptimized table https: //github. com/Microsoft/sql-server-samples/tree/master/samples/applications/iot-smart-grid
Applications / Web servers
Memory-optimized table variables and temp tables
Data Warehouse – SQL Server 2016
www. microsoft. com/itprocareercenter www. microsoft. com/itprocloudessentials www. microsoft. com/mechanics https: //techcommunity. microsoft. com
http: //myignite. microsoft. com https: //aka. ms/ignite. mobileapp
- Slides: 28