Supporto di SQL Server Management Objects per OLTP in memoriaSQL Server Management Objects Support for In-Memory OLTP

In questo argomento vengono descritte le modifiche in SQL ServerSQL Server Management Objects (SMO) per OLTP in memoria.This topic describes changes in SQL ServerSQL Server Management Objects (SMO) for In-Memory OLTP.

OLTP in memoria รจ supportato dai seguenti tipi e membri:The following types and members support In-Memory OLTP:

Codice di esempioCode Sample

In questo esempio vengono creati gli elementi seguenti:This sample does the following:

  • Filegroup e file con ottimizzazione per la memoria.Creates a database with memory-optimized filegroup and memory-optimized file.

  • Una tabella durevole con ottimizzazione per la memoria con una chiave primaria, un indice non cluster e un indice hash non cluster.Creates a durable memory-optimized table with a primary key, nonclustered index, and a nonclustered hash index.

  • Colonne e indici.Creates columns and indexes.

  • Un tipo di tabella con ottimizzazione per la memoria definito dall'utente.Creates a user-defined memory-optimized table type.

  • Una stored procedure compilata in modo nativo.Creates a natively compiled stored procedure.

    Questo esempio deve fare riferimento ai seguenti assembly:This sample needs to reference the following assemblies:

  • Microsoft.SqlServer.Smo.dllMicrosoft.SqlServer.Smo.dll

  • Microsoft.SqlServer.Management.Sdk.Sfc.dllMicrosoft.SqlServer.Management.Sdk.Sfc.dll

  • Microsoft.SqlServer.ConnectionInfo.dllMicrosoft.SqlServer.ConnectionInfo.dll

  • Microsoft.SqlServer.SqlEnum.dllMicrosoft.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\MSSQL13.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();  
   }  
}  

Vedere ancheSee Also

Supporto di SQL Server per OLTP in memoriaSQL Server Support for In-Memory OLTP