对内存中 OLTP 的 SQL Server 管理对象支持
本主题介绍 In-Memory OLTP 的 SQL Server 管理对象 (SMO) 中的更改。
以下类型和成员支持内存中 OLTP:
代码示例
该示例将执行以下操作:
使用内存优化文件组和内存优化文件创建数据库。
使用主键、非聚集索引和非聚集哈希索引创建持久内存优化表。
创建列和索引。
创建用户定义的内存优化表类型。
创建本机编译的存储过程。
此示例需要引用以下程序集:
Microsoft.SqlServer.Smo.dll
Microsoft.SqlServer.Management.Sdk.Sfc.dll
Microsoft.SqlServer.ConnectionInfo.dll
Microsoft.SqlServer.SqlEnum.dll
using Microsoft.SqlServer.Management.Smo;
using System;
public class A {
static void Main(string[] args) {
Server server = new Server("(local)");
// Create a database with memory-optimized filegroup and memory-optimized file
Database db = new Database(server, "MemoryOptimizedDatabase");
db.Create();
FileGroup fg = new FileGroup(db, "memOptFilegroup", FileGroupType.MemoryOptimizedDataFileGroup);
db.FileGroups.Add(fg);
fg.Create();
// change this path if needed
DataFile file = new DataFile(fg, "memOptFile", @"C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\MSSQLmemOptFileName");
file.Create();
// Create a durable memory-optimized table with primary key, nonclustered index and nonclustered hash index
// Define the table as memory optimized and set the durability
Table table = new Table(db, "memOptTable");
table.IsMemoryOptimized = true;
table.Durability = DurabilityType.SchemaAndData;
// Create columns
Column col1 = new Column(table, "col1", DataType.Int);
col1.Nullable = false;
table.Columns.Add(col1);
Column col2 = new Column(table, "col2", DataType.Float);
col2.Nullable = false;
table.Columns.Add(col2);
Column col3 = new Column(table, "col3", DataType.Decimal(2, 10));
col3.Nullable = false;
table.Columns.Add(col3);
// Create indexes
Index pk = new Index(table, "PK_memOptTable");
pk.IndexType = IndexType.NonClusteredIndex;
pk.IndexKeyType = IndexKeyType.DriPrimaryKey;
pk.IndexedColumns.Add(new IndexedColumn(pk, col1.Name));
table.Indexes.Add(pk);
Index ixNonClustered = new Index(table, "ix_nonClustered");
ixNonClustered.IndexType = IndexType.NonClusteredIndex;
ixNonClustered.IndexKeyType = IndexKeyType.None;
ixNonClustered.IndexedColumns.Add(new IndexedColumn(ixNonClustered, col2.Name));
table.Indexes.Add(ixNonClustered);
Index ixNonClusteredHash = new Index(table, "ix_nonClustered_Hash");
ixNonClusteredHash.IndexType = IndexType.NonClusteredHashIndex;
ixNonClusteredHash.IndexKeyType = IndexKeyType.None;
ixNonClusteredHash.BucketCount = 1024;
ixNonClusteredHash.IndexedColumns.Add(new IndexedColumn(ixNonClusteredHash, col3.Name));
table.Indexes.Add(ixNonClusteredHash);
table.Create();
// Create a user-defined memory-optimized table type
UserDefinedTableType uDTT = new UserDefinedTableType(db, "memOptUDTT");
uDTT.IsMemoryOptimized = true;
// Add columns
Column udTTCol1 = new Column(uDTT, "udtCol1", DataType.Int);
udTTCol1.Nullable = false;
uDTT.Columns.Add(udTTCol1);
Column udTTCol2 = new Column(uDTT, "udtCol2", DataType.Float);
udTTCol2.Nullable = false;
uDTT.Columns.Add(udTTCol2);
Column udTTCol3 = new Column(uDTT, "udtCol3", DataType.Decimal(2, 10));
udTTCol3.Nullable = false;
uDTT.Columns.Add(udTTCol3);
// Add index
Index ix = new Index(uDTT, "IX_UDT");
ix.IndexType = IndexType.NonClusteredHashIndex;
ix.BucketCount = 1024;
ix.IndexKeyType = IndexKeyType.DriPrimaryKey;
ix.IndexedColumns.Add(new IndexedColumn(ix, udTTCol1.Name));
uDTT.Indexes.Add(ix);
uDTT.Create();
// Create a natively compiled stored procedure
StoredProcedure sProc = new StoredProcedure(db, "nCSProc");
sProc.TextMode = false;
sProc.TextBody = "--Type body here";
sProc.IsNativelyCompiled = true;
sProc.IsSchemaBound = true;
sProc.ExecutionContext = ExecutionContext.Owner;
sProc.Create();
}
}