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

QUESTO ARGOMENTO SI APPLICA A: sìSQL ServersìDatabase SQL di AzurenoAzure SQL Data Warehouse noParallel Data Warehouse THIS TOPIC APPLIES TO: yesSQL ServeryesAzure SQL DatabasenoAzure SQL Data Warehouse noParallel Data Warehouse

Questo argomento descrive gli elementi di SQL Server Management Objects (SMO) che supportano OLTP in memoria.This topic describes items in SQL Server Management Objects (SMO) that support In-Memory OLTP.

Tipi e membri di SMOSMO types and members

I seguenti tipi e membri sono inclusi nello spazio dei nomi Microsoft.SqlServer.Management.Smo, e supportano OLTP in memoria:The following types and members are in the namespace Microsoft.SqlServer.Management.Smo, and they support In-Memory OLTP:

Esempio di codice C#C# code example

Assembly a cui si fa riferimento nell'esempio di codice compilatoAssemblies referenced by the compiled code example

  • Microsoft.SqlServer.ConnectionInfo.dllMicrosoft.SqlServer.ConnectionInfo.dll
  • Microsoft.SqlServer.Management.Sdk.Sfc.dllMicrosoft.SqlServer.Management.Sdk.Sfc.dll
  • Microsoft.SqlServer.Smo.dllMicrosoft.SqlServer.Smo.dll
  • Microsoft.SqlServer.SqlEnum.dllMicrosoft.SqlServer.SqlEnum.dll

Azioni intraprese nell'esempio di codiceActions taken in the code example

  1. Creare un database con filegroup e file con ottimizzazione per la memoria.Create a database with memory-optimized filegroup and memory-optimized file.
  2. Creare una tabella durevole con ottimizzazione per la memoria con una chiave primaria, un indice non cluster e un indice hash non cluster.Create a durable memory-optimized table with a primary key, nonclustered index, and a nonclustered hash index.
  3. Creare colonne e indici.Create columns and indexes.
  4. Creare un tipo di tabella con ottimizzazione per la memoria definito dall'utenteCreate a user-defined memory-optimized table type.
  5. Creare una stored procedure compilata in modo nativoCreate a natively compiled stored procedure.

Codice sorgenteSource code

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