// ------------------------------- private string CreateIndexInternal(SchemaIndex index) { // CREATE INDEX IX_tableName_columnName ON tableName // ( columnName(s) ) // WITH(FILLFACTOR = 30) // GO var sb = new StringBuilder(); var indexName = index.GenerateName(); sb.Append("CREATE INDEX ") .Append(PrependTablePrefix(indexName)) .Append(" ON ") .Append(PrependTablePrefix(index.TableName)) .Append(" (") .Append(index.Columns.ToDelimitedString(',')) .Append(")"); if (index.FillFactor > 0) { sb.Append(" WITH (FILLFACTOR = ") .Append(index.FillFactor) .Append(")"); } sb.Append(";"); return(sb.ToString()); }
// ------------------------------- private string CreateIndexInternal(SchemaIndex index) { // IF NOT EXISTS (SELECT object_id FROM sys.indexes WHERE [name] = 'prefix_IX_tableName_firstColumnName') // BEGIN // CREATE INDEX prefix_IX_tableName_firstColumnName ON tableName // ( columnName(s) ) // WITH(FILLFACTOR = 30) // END var sb = new StringBuilder(); var indexName = index.GenerateName(); // Ensure the index does not already exist before attempting to create sb.Append("IF NOT EXISTS (SELECT object_id FROM sys.indexes WHERE [name] = '") .Append(PrependTablePrefix(indexName)) .Append("')") .Append(NewLine); sb.Append("BEGIN") .Append(NewLine);; // Create index sb.Append("CREATE INDEX ") .Append(PrependTablePrefix(indexName)) .Append(" ON ") .Append(PrependTablePrefix(index.TableName)) .Append(" (") .Append(index.Columns.ToDelimitedString(',')) .Append(")"); if (index.FillFactor > 0) { sb.Append(" WITH (FILLFACTOR = ") .Append(index.FillFactor) .Append(")"); } sb.Append(";"); // End index exists check sb .Append(NewLine) .Append("END"); return(sb.ToString()); }
private string AlterIndexInternal(SchemaIndex index) { // Defragment by physically removing rows that have been logically deleted from the table, and merging rowgroups. var indexName = index.GenerateName(); var sb = new StringBuilder(); sb.Append("ALTER INDEX ") .Append(PrependTablePrefix(indexName)) .Append(" ON ") .Append(PrependTablePrefix(index.TableName)) .Append(" REORGANIZE"); return(sb.ToString()); }
private string DropIndexInternal(SchemaIndex index) { // DROP INDEX IX_tableName_firstColumn ON tableName // GO var indexName = index.GenerateName(); var sb = new StringBuilder(); sb.Append("DROP INDEX ") .Append(PrependTablePrefix(indexName)) .Append(" ON ") .Append(PrependTablePrefix(index.TableName)) .Append(";") .Append(NewLine); return(sb.ToString()); }