// -------------------------------
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());
}
