C# Index.Create方法代码示例

        public void SynchronizeTable(SchemaTable schemaTable)
        {
            var smoTable = GetSmoTable(schemaTable);
            if (smoTable == null)
            {
                smoTable = new Table(GetSmoDatabase(), schemaTable.Name);
                foreach (var column in schemaTable.Columns)
                {
                    var newcol = column.GetSmoColumn(smoTable);
                    smoTable.Columns.Add(newcol);
                }
                smoTable.Create();

                // primary key
                if (!schemaTable.IsLogTable && !schemaTable.IsProvodkasTable)
                {
                    var pk = new Index(smoTable, "PK_" + schemaTable.Name);
                    var icol = new IndexedColumn(pk, schemaTable.GetPrimaryKeyColumn().Name, false);
                    pk.IndexedColumns.Add(icol);
                    pk.IndexKeyType = IndexKeyType.DriPrimaryKey;

                    pk.IsClustered = true;
                    pk.FillFactor = 50;

                    pk.Create();
                }
            }
            else
            {
                foreach (var schemaColumn in schemaTable.Columns)
                {
                    if (smoTable.Columns.Contains(schemaColumn.Name))
                    {
                        if ((schemaColumn.Table.GetPrimaryKeyColumn()==null || schemaColumn.Name != schemaColumn.Table.GetPrimaryKeyColumn().Name) &&
                            !(schemaColumn.DataType is TimestampDataType))
                        {
                            var smoColumn = smoTable.Columns[schemaColumn.Name];
                            var newDataType = schemaColumn.DataType.GetSmoDataType();
                            if (!smoColumn.DataType.Equals(newDataType))
                                smoColumn.DataType = newDataType;
                            if (smoColumn.Nullable != !schemaColumn.IsNotNullable)
                                smoColumn.Nullable = !schemaColumn.IsNotNullable;
                        }
                    }
                    else
                    {
                        var newcol = schemaColumn.GetSmoColumn(smoTable);
                        smoTable.Columns.Add(newcol);
                    }
                }
                smoTable.Alter();
            }
        }

        /// <summary>
        /// Создаём таблицу из схемы модуля
        /// </summary>
        /// <param name="module">Метаданные модуля</param>
        public void CreateTableFromModuleSchema(ModuleMetadata module)
        {
            if (!_database.Tables.Contains(module.ModuleName))
            {
                try
                {
                    //  создаём таблицу
                    Table targetTable = new Table(_database, module.ModuleName);

                    //
                    //  добавляем базовые столбцы в таблицу
                    //
                    #region Внешниый ключ
                    Column plowMachineIdColumn = new Column(targetTable, "PlowMachineId");
                    plowMachineIdColumn.DataType = DataType.UniqueIdentifier;
                    plowMachineIdColumn.RowGuidCol = true;
                    plowMachineIdColumn.Nullable = false;

                    ForeignKey fk = new ForeignKey(targetTable, "FK_" + module.ModuleName + "_PlowMachine");
                    ForeignKeyColumn fk_column = new ForeignKeyColumn(fk, "PlowMachineId");
                    fk_column.ReferencedColumn = "PlowMachineId";
                    fk.ReferencedTable = "PlowMachines";
                    fk.Columns.Add(fk_column);

                    targetTable.ForeignKeys.Add(fk);
                    targetTable.Columns.Add(plowMachineIdColumn);
                    #endregion

                    //
                    //  добавляем столбцы в таблицу
                    //
                    foreach (FieldMetadata f in module.MetadataFields)
                    {
                        Column column = CreateColumn(targetTable, f);
                        targetTable.Columns.Add(column);
                    }

                    targetTable.Create();

                    #region Первичный ключ

                    Index idx = new Index(targetTable, "PK_" + module.ModuleName);
                    IndexedColumn idxc = new IndexedColumn(idx, plowMachineIdColumn.Name);
                    idx.IndexedColumns.Add(idxc);
                    idx.IndexKeyType = IndexKeyType.DriPrimaryKey;
                    idx.IsClustered = true;
                    idx.IsUnique = true;
                    idx.Create();

                    #endregion
                }
                catch (Microsoft.SqlServer.Management.Smo.InvalidSmoOperationException)
                {
                    throw;
                }

            }
            else
            {
                throw new InvalidOperationException("Таблица с именем '" + module.ModuleName + "' уже существует в БД.");
            }
        }

        private void createStgIndex(Index i, TableViewBase parent)
        {
            if (i.PartitionScheme == "")
                throw (new System.NotSupportedException(
                    String.Format("The index '{0}' is not aligned to a Partition Scheme", i.Name)));

              // todo:  differentiate between Base Table as source, and View as source

              // LZAV:  Index stgIndex = new Index(parent, parent.Name + "_" + i.Name);
              String indexName = parent.Name + "_" + i.Name;		// LZAV
              if (indexName.Length > 128)						// LZAV
              indexName = "IX_CL_" + parent.Name;				// LZAV

              Index stgIndex = new Index(parent, indexName);		// LZAV

              foreach (IndexedColumn iCol in i.IndexedColumns)
              {
             IndexedColumn stgICol = new IndexedColumn(stgIndex, iCol.Name, iCol.Descending);
             stgICol.IsIncluded = iCol.IsIncluded;
             stgIndex.IndexedColumns.Add(stgICol);
              }
              stgIndex.IndexType = i.IndexType;
              stgIndex.IndexKeyType = i.IndexKeyType;
              stgIndex.IsClustered = i.IsClustered;
              stgIndex.IsUnique = i.IsUnique;
              stgIndex.CompactLargeObjects = i.CompactLargeObjects;
              stgIndex.IgnoreDuplicateKeys = i.IgnoreDuplicateKeys;
              stgIndex.IsFullTextKey = i.IsFullTextKey;
              stgIndex.PadIndex = i.PadIndex;
              stgIndex.FileGroup = db.PartitionSchemes[i.PartitionScheme].FileGroups[partitionNumber - 1];

              // add the partitioning column to the index if it is not already there
              String partitionKeyName = i.PartitionSchemeParameters[0].Name;
              if (stgIndex.IndexedColumns[partitionKeyName] == null)
              {
             IndexedColumn stgICol = new IndexedColumn(stgIndex, partitionKeyName);
             // It is added as a Key to the Clustered index and as an Include column to a Nonclustered
             stgICol.IsIncluded = !stgIndex.IsClustered;
             stgIndex.IndexedColumns.Add(stgICol);
              }

              if (srv.VersionMajor >= 10)
              {
             // Define compression property to match by creating a Physical Partition object (not applicable to Colstore)
             {
                PhysicalPartition stgPartition = new PhysicalPartition(stgIndex, 1);
                if (i.IndexType != IndexType.NonClusteredColumnStoreIndex)
                {
                    stgPartition.DataCompression = i.PhysicalPartitions[partitionNumber - 1].DataCompression;
                }
                stgIndex.PhysicalPartitions.Add(stgPartition);
             }
             // Handle Filtered Index
             if (i.HasFilter)
             {
                stgIndex.FilterDefinition = i.FilterDefinition;
             }
              }
              scriptChunks.Add(stgIndex.Script());
              if (executeCommands) stgIndex.Create();
        }

        private bool CheckHistoryTable(Database database, bool createManagementSchemaIfMissing)
        {
            var historyTable = database.Tables[HistoryTableName, SchemaName];
            if (historyTable == null)
            {
                if (createManagementSchemaIfMissing)
                {
                    historyTable = new Table(database, HistoryTableName, SchemaName);

                    var filenameColumn = new Column(historyTable, HistoryTableFilenameColumnName) { DataType = DataType.NVarChar(256), Nullable = false };
                    historyTable.Columns.Add(filenameColumn);

                    var hashColumn = new Column(historyTable, HistoryTableHashColumnName) { DataType = DataType.NVarChar(128), Nullable = false };
                    historyTable.Columns.Add(hashColumn);

                    var appliedOnColumn = new Column(historyTable, HistoryTableAppliedOnColumnName) { DataType = DataType.DateTime, Nullable = false };
                    historyTable.Columns.Add(appliedOnColumn);

                    var appliedByColumn = new Column(historyTable, HistoryTableAppliedByColumnName) { DataType = DataType.NVarChar(256), Nullable = false };
                    historyTable.Columns.Add(appliedByColumn);

                    historyTable.Create();

                    var primaryKey = new Index(historyTable, HistoryTablePrimaryKeyName);
                    primaryKey.IndexedColumns.Add(new IndexedColumn(primaryKey, filenameColumn.Name));
                    primaryKey.IsUnique = true;
                    primaryKey.IndexKeyType = IndexKeyType.DriPrimaryKey;

                    primaryKey.Create();
                }
                else
                {
                    _context.RaiseExecutionEvent(ExecutionEventType.Error, "The Gus management schema is invalid.");
                    return false;
                }
            }

            return true;
        }

 public IEnumerable<string> AddIndex(string tableName, IEnumerable<string> columnNames, string indexName)
 {
     Table table = GetTable(tableName);
     Index index = new Index(table, indexName);
     table.Indexes.Add(index);
     foreach (string columnName in columnNames)
     {
         var column = new Microsoft.SqlServer.Management.Smo.Column(table, columnName)
         {
             DataType = Microsoft.SqlServer.Management.Smo.DataType.Variant
         };
         table.Columns.Add(column);
         IndexedColumn indexedColumn = new IndexedColumn(index, columnName);
         index.IndexedColumns.Add(indexedColumn);
     }
     index.Create();
     return ScriptChanges(table.Parent.Parent);
 }

 private static IEnumerable<string> AddConstraint(string tableName, string constraintName, IndexKeyType keyType, IEnumerable<string> columnNames)
 {
     Table table = GetTable(tableName);
     Index uniqueConstraint = new Index(table, constraintName) { IndexKeyType = keyType };
     foreach (string columnName in columnNames)
     {
         Microsoft.SqlServer.Management.Smo.Column column = new Microsoft.SqlServer.Management.Smo.Column(table, columnName);
         column.DataType = Microsoft.SqlServer.Management.Smo.DataType.Bit;
         table.Columns.Add(column);
         uniqueConstraint.IndexedColumns.Add(new IndexedColumn(uniqueConstraint, columnName));
     }
     uniqueConstraint.Create();
     return ScriptChanges(table.Parent.Parent);
 }

        public static void AddColumnToIndex(Table table, Column column, Index ind)
        {
            string oldName = ind.Name;
            var idx = new Index(table, oldName)
            {
                BoundingBoxYMax = ind.BoundingBoxYMax,
                BoundingBoxYMin = ind.BoundingBoxYMin,
                BoundingBoxXMax = ind.BoundingBoxXMax,
                BoundingBoxXMin = ind.BoundingBoxXMin,
                CompactLargeObjects = ind.CompactLargeObjects,
                DisallowRowLocks = ind.DisallowRowLocks,
                FileGroup = ind.FileGroup,
                FileStreamFileGroup = ind.FileStreamFileGroup,
                FileStreamPartitionScheme = ind.FileStreamPartitionScheme,
                FillFactor = ind.FillFactor,
                FilterDefinition = ind.FilterDefinition,
                IgnoreDuplicateKeys = ind.IgnoreDuplicateKeys,
                IndexKeyType = ind.IndexKeyType,
                IsClustered = ind.IsClustered,
                IsFullTextKey = ind.IsFullTextKey,
                IsUnique = ind.IsUnique,
                Level1Grid = ind.Level1Grid,
                Level2Grid = ind.Level2Grid,
                Level3Grid = ind.Level3Grid,
                Level4Grid = ind.Level4Grid,
                MaximumDegreeOfParallelism = ind.MaximumDegreeOfParallelism,
                NoAutomaticRecomputation = ind.NoAutomaticRecomputation,
                OnlineIndexOperation = ind.OnlineIndexOperation,
                PadIndex = ind.PadIndex,
                ParentXmlIndex = ind.ParentXmlIndex,
                PartitionScheme = ind.PartitionScheme,
                SecondaryXmlIndexType = ind.SecondaryXmlIndexType,
                SortInTempdb = ind.SortInTempdb,
                SpatialIndexType = ind.SpatialIndexType,
            };

            foreach (IndexedColumn iColumn in ind.IndexedColumns)
            {
                var newIdxColumn = new IndexedColumn(idx, iColumn.Name)
                {
                    Descending = iColumn.Descending,
                    IsIncluded = iColumn.IsIncluded,
                };
                idx.IndexedColumns.Add(newIdxColumn);
            }
            idx.IndexedColumns.Add(new IndexedColumn(idx, column.Name));

            bool oldDisallowPageLocks = ind.DisallowPageLocks;
            ind.Drop();
            idx.Create();
            idx.DisallowPageLocks = oldDisallowPageLocks;
            idx.Alter();
        }

        /// <summary>
        /// Recreates primary key or index.
        /// </summary>
        /// <param name="table">Current table <see cref="Table"/></param>
        /// <param name="descriptor">Index descriptor</param>
        private static void CreateNewPrimaryKey(Table table, IndexDbo descriptor)
        {
            var indexKeyType = (IndexKeyType)Enum.Parse(typeof(IndexKeyType), descriptor.IndexKeyType);
            byte fillFactor = (byte)(descriptor.FillFactor ?? 0);

            var primaryKeyIndex = new Index(table, descriptor.Name)
            {
                CompactLargeObjects = descriptor.CompactLargeObjects,
                FillFactor = fillFactor > 0 ? fillFactor : (byte)50,
                FilterDefinition = descriptor.FilterDefinition,
                IgnoreDuplicateKeys = descriptor.IgnoreDuplicateKeys,
                IndexKeyType = indexKeyType,
                IsClustered = descriptor.IsClustered,
                IsUnique = descriptor.IsUnique,
            };
            foreach (string columnName in descriptor.IndexedColumns)
                primaryKeyIndex.IndexedColumns.Add(new IndexedColumn(primaryKeyIndex, columnName));
            primaryKeyIndex.Create();
            primaryKeyIndex.DisallowPageLocks = descriptor.DisallowPageLocks;
            primaryKeyIndex.DisallowRowLocks = descriptor.DisallowRowLocks;
            primaryKeyIndex.Alter();
            //if (descriptor.IsDisabled)
            //{
            //    primaryKeyIndex.Disable();
            //    table.Alter();
            //}
        }