public string GetQuery()
{
string sql = string.Empty;
string indexName = IndexName.ToQuota();
string objectName = $"{SchemaName.ToQuota()}.{ObjectName.ToQuota()}";
string fullIndexName = $"{indexName} ON {objectName}";
string partition = IsPartitioned ? PartitionNumber.ToString() : "ALL";
if (IndexType == IndexType.HEAP && FixType == IndexOp.CREATE_COLUMNSTORE_INDEX)
{
sql = $"CREATE CLUSTERED COLUMNSTORE INDEX [CCL] ON {objectName} WITH (COMPRESSION_DELAY = 0, DATA_COMPRESSION = {DataCompression.COLUMNSTORE});";
}
else if (IsColumnstore)
{
switch (FixType)
{
case IndexOp.REBUILD:
case IndexOp.REBUILD_COLUMNSTORE:
case IndexOp.REBUILD_COLUMNSTORE_ARCHIVE:
DataCompression compression = (FixType == IndexOp.REBUILD_COLUMNSTORE) ? DataCompression.COLUMNSTORE : DataCompression.COLUMNSTORE_ARCHIVE;
sql = $"ALTER INDEX {fullIndexName} REBUILD PARTITION = {partition}{Environment.NewLine} " +
$"WITH (DATA_COMPRESSION = {(FixType == IndexOp.REBUILD ? DataCompression : compression)}, MAXDOP = {Settings.Options.MaxDop});";
break;
case IndexOp.REORGANIZE:
case IndexOp.REORGANIZE_COMPRESS_ALL_ROW_GROUPS:
sql = $"ALTER INDEX {fullIndexName} REORGANIZE PARTITION = {partition}" +
$"{(FixType == IndexOp.REORGANIZE_COMPRESS_ALL_ROW_GROUPS ? $"{Environment.NewLine} WITH (COMPRESS_ALL_ROW_GROUPS = ON)" : "")};";
break;
case IndexOp.TRUNCATE_TABLE:
sql = IsPartitioned
? $"TRUNCATE TABLE {objectName} WITH (PARTITIONS ({partition}));"
: $"TRUNCATE TABLE {objectName};";
break;
}
}
else
{
switch (FixType)
{
case IndexOp.REBUILD:
case IndexOp.REBUILD_ROW:
case IndexOp.REBUILD_PAGE:
case IndexOp.REBUILD_NONE:
case IndexOp.REBUILD_ONLINE:
case IndexOp.CREATE_INDEX:
DataCompression compression;
if (FixType == IndexOp.REBUILD_PAGE)
{
compression = DataCompression.PAGE;
}
else if (FixType == IndexOp.REBUILD_ROW)
{
compression = DataCompression.ROW;
}
else if (FixType == IndexOp.REBUILD_NONE)
{
compression = DataCompression.NONE;
}
else if (Settings.Options.DataCompression != DataCompression.DEFAULT && FixType != IndexOp.REBUILD)
{
compression = Settings.Options.DataCompression;
}
else
{
compression = DataCompression;
}
string onlineRebuild = "OFF";
if (FixType == IndexOp.REBUILD_ONLINE || (Settings.Options.Online && IsAllowOnlineRebuild))
{
if (Settings.Options.WaitAtLowPriority && Settings.ServerInfo.MajorVersion >= ServerVersion.Sql2014)
{
onlineRebuild = "ON (" +
$"WAIT_AT_LOW_PRIORITY (MAX_DURATION = {Settings.Options.MaxDuration} MINUTES, " +
$"ABORT_AFTER_WAIT = {Settings.Options.AbortAfterWait}))";
}
else
{
onlineRebuild = "ON";
}
}
string sqlHeader;
if (IndexType == IndexType.MISSING_INDEX)
{
sqlHeader = $"CREATE NONCLUSTERED INDEX {fullIndexName}{Environment.NewLine} ({IndexColumns}){Environment.NewLine} "
+ (string.IsNullOrEmpty(IncludedColumns) ? "" : $"INCLUDE ({IncludedColumns}){Environment.NewLine} ");
}
else if (IndexType == IndexType.HEAP)
{
sqlHeader = $"ALTER TABLE {objectName} REBUILD PARTITION = {partition}{Environment.NewLine} ";
}
else
{
sqlHeader = $"ALTER INDEX {fullIndexName} REBUILD PARTITION = {partition}{Environment.NewLine} ";
}
string nr = Settings.Options.NoRecompute == NoRecompute.DEFAULT
? ((IsNoRecompute ?? false) ? "ON" : "OFF")
: Settings.Options.NoRecompute.ToString();
sql = sqlHeader +
"WITH (" +
(IndexType == IndexType.HEAP
? ""
: $"SORT_IN_TEMPDB = {Settings.Options.SortInTempDb.OnOff()}, ") +
(IsPartitioned || IndexType == IndexType.HEAP
? ""
: $"PAD_INDEX = {Settings.Options.PadIndex.OnOff()}, ") +
(IsPartitioned || Settings.Options.FillFactor == 0
? ""
: $"FILLFACTOR = {Settings.Options.FillFactor}, ") +
(IndexType == IndexType.HEAP || IsPartitioned
? ""
: $"STATISTICS_NORECOMPUTE = {nr}, ") +
(!IsAllowCompression
? ""
: $"DATA_COMPRESSION = {compression}, ") +
(Settings.ServerInfo.MajorVersion <= ServerVersion.Sql2012 && IsPartitioned // 'ONLINE' is not a recognized ALTER INDEX REBUILD PARTITION option
? ""
: $"ONLINE = {onlineRebuild}, ") +
$"MAXDOP = {Settings.Options.MaxDop});";
break;
case IndexOp.REORGANIZE:
sql = $"ALTER INDEX {fullIndexName} REORGANIZE PARTITION = {partition}{Environment.NewLine} " +
$"WITH (LOB_COMPACTION = {Settings.Options.LobCompaction.OnOff()});";
break;
case IndexOp.DISABLE_INDEX:
sql = $"ALTER INDEX {fullIndexName} DISABLE;";
break;
case IndexOp.DROP_INDEX:
sql = $"DROP INDEX {fullIndexName};";
break;
case IndexOp.DROP_TABLE:
sql = $"DROP TABLE {objectName};";
break;
case IndexOp.TRUNCATE_TABLE:
sql = IsPartitioned
? $"TRUNCATE TABLE {objectName} WITH (PARTITIONS ({partition}));"
: $"TRUNCATE TABLE {objectName};";
break;
case IndexOp.UPDATE_STATISTICS_SAMPLE:
case IndexOp.UPDATE_STATISTICS_RESAMPLE:
case IndexOp.UPDATE_STATISTICS_FULL:
string nr2 = (Settings.Options.NoRecompute == NoRecompute.DEFAULT && (IsNoRecompute ?? false)) ||
Settings.Options.NoRecompute == NoRecompute.ON
? ", NORECOMPUTE"
: "";
sql = $"UPDATE STATISTICS {objectName} {indexName}{Environment.NewLine} " + (
FixType == IndexOp.UPDATE_STATISTICS_SAMPLE
? $"WITH SAMPLE {Settings.Options.SampleStatsPercent} PERCENT{nr2};"
: (FixType == IndexOp.UPDATE_STATISTICS_FULL ? $"WITH FULLSCAN{nr2};" : $"WITH RESAMPLE{nr2};")
);
break;
}
}
return(sql);
}
public string GetQuery()
{
string sql = string.Empty;
string indexName = IndexName.ToQuota();
string schemaName = SchemaName.ToQuota();
string objectName = ObjectName.ToQuota();
string partition = IsPartitioned ? PartitionNumber.ToString() : "ALL";
DataCompression compression = DataCompression;
if (IsColumnstore)
{
if (FixType == IndexOp.RebuildColumnstore)
{
compression = DataCompression.Columnstore;
}
if (FixType == IndexOp.RebuildColumnstoreArchive)
{
compression = DataCompression.ColumnstoreArchive;
}
switch (FixType)
{
case IndexOp.Rebuild:
case IndexOp.RebuildColumnstore:
case IndexOp.RebuildColumnstoreArchive:
sql = $"ALTER INDEX [{indexName}]\n " +
$"ON [{schemaName}].[{objectName}] REBUILD PARTITION = {partition}\n " +
$"WITH (DATA_COMPRESSION = {compression.ToDescription()}, MAXDOP = {Settings.Options.MaxDop});";
break;
case IndexOp.Reorganize:
sql = $"ALTER INDEX [{indexName}]\n " +
$"ON [{schemaName}].[{objectName}] REORGANIZE PARTITION = {partition};";
break;
case IndexOp.ReorganizeCompressAllRowGroup:
sql = $"ALTER INDEX [{indexName}]\n " +
$"ON [{schemaName}].[{objectName}] REORGANIZE PARTITION = {partition}\n " +
"WITH (COMPRESS_ALL_ROW_GROUPS = ON);";
break;
}
}
else
{
if (FixType == IndexOp.RebuildPage)
{
compression = DataCompression.Page;
}
else if (FixType == IndexOp.RebuildRow)
{
compression = DataCompression.Row;
}
else if (FixType == IndexOp.RebuildNone)
{
compression = DataCompression.None;
}
switch (FixType)
{
case IndexOp.CreateIndex:
sql = $"CREATE NONCLUSTERED INDEX [{indexName}]\n" +
$"ON [{schemaName}].[{objectName}] ({IndexColumns})\n" +
(string.IsNullOrEmpty(IncludedColumns) ? "" : $"INCLUDE({IncludedColumns})\n") +
$"WITH (SORT_IN_TEMPDB = {(Settings.Options.SortInTempDb ? "ON" : "OFF")}, " +
(Settings.Options.FillFactor.IsBetween(1, 100)
? $"FILLFACTOR = {Settings.Options.FillFactor}, "
: ""
) +
(Settings.Options.DataCompression == "DEFAULT"
? ""
: $"DATA_COMPRESSION = {Settings.Options.DataCompression}, "
) +
$"MAXDOP = {Settings.Options.MaxDop});";
break;
case IndexOp.Rebuild:
case IndexOp.RebuildPage:
case IndexOp.RebuildRow:
case IndexOp.RebuildNone:
case IndexOp.RebuildOnline:
case IndexOp.RebuildFillFactorZero:
if (IndexType == IndexType.Heap)
{
sql = $"ALTER TABLE [{schemaName}].[{objectName}] REBUILD PARTITION = {partition}\n " +
$"WITH (DATA_COMPRESSION = {compression.ToDescription()}, MAXDOP = {Settings.Options.MaxDop});";
}
else
{
string onlineRebuild = "OFF";
if (FixType == IndexOp.RebuildOnline)
{
if (Settings.Options.WaitAtLowPriority && Settings.ServerInfo.MajorVersion >= 12)
{
onlineRebuild = $"ON (WAIT_AT_LOW_PRIORITY(MAX_DURATION = {Settings.Options.MaxDuration} MINUTES, ABORT_AFTER_WAIT = {Settings.Options.AbortAfterWait}))";
}
else
{
onlineRebuild = "ON";
}
}
sql = $"ALTER INDEX [{indexName}]\n " +
$"ON [{schemaName}].[{objectName}] REBUILD PARTITION = {partition}\n " +
$"WITH (SORT_IN_TEMPDB = {(Settings.Options.SortInTempDb ? "ON" : "OFF")}, " +
$"ONLINE = {onlineRebuild}, " +
(FixType == IndexOp.RebuildFillFactorZero
? "FILLFACTOR = 100, "
: (Settings.Options.FillFactor.IsBetween(1, 100)
? $"FILLFACTOR = {Settings.Options.FillFactor}, "
: ""
)
) +
$"DATA_COMPRESSION = {compression.ToDescription()}, " +
$"MAXDOP = {Settings.Options.MaxDop});";
}
break;
case IndexOp.Reorganize:
sql = $"ALTER INDEX [{indexName}]\n " +
$"ON [{schemaName}].[{objectName}] REORGANIZE PARTITION = {partition}\n " +
$"WITH (LOB_COMPACTION = {(Settings.Options.LobCompaction ? "ON" : "OFF")});";
break;
case IndexOp.Disable:
sql = $"ALTER INDEX [{indexName}] ON [{schemaName}].[{objectName}] DISABLE;";
break;
case IndexOp.Drop:
sql = $"DROP INDEX [{indexName}] ON [{schemaName}].[{objectName}];";
break;
case IndexOp.UpdateStatsSample:
case IndexOp.UpdateStatsResample:
case IndexOp.UpdateStatsFull:
sql = $"UPDATE STATISTICS [{schemaName}].[{objectName}] [{indexName}]\n " + (
FixType == IndexOp.UpdateStatsSample
? $"WITH SAMPLE {Settings.Options.SampleStatsPercent} PERCENT;"
: (FixType == IndexOp.UpdateStatsFull ? "WITH FULLSCAN;" : "WITH RESAMPLE;")
);
break;
}
}
return(sql);
}