public override string ToSql()
{
StringBuilder sql = new StringBuilder();
string includes = "";
if ((Type == IndexTypeEnum.Clustered) && (IsUniqueKey))
{
sql.Append("CREATE UNIQUE CLUSTERED ");
}
if ((Type == IndexTypeEnum.Clustered) && (!IsUniqueKey))
{
sql.Append("CREATE CLUSTERED ");
}
if ((Type == IndexTypeEnum.Nonclustered) && (IsUniqueKey))
{
sql.Append("CREATE UNIQUE NONCLUSTERED ");
}
if ((Type == IndexTypeEnum.Nonclustered) && (!IsUniqueKey))
{
sql.Append("CREATE NONCLUSTERED ");
}
if (Type == IndexTypeEnum.XML)
{
sql.Append("CREATE PRIMARY XML ");
}
sql.Append("INDEX [" + Name + "] ON " + Parent.FullName + "\r\n(\r\n");
/*Ordena la coleccion de campos del Indice en funcion de la propieda IsIncluded*/
Columns.Sort();
for (int j = 0; j < Columns.Count; j++)
{
if (!Columns[j].IsIncluded)
{
sql.Append("\t[" + Columns[j].Name + "]");
if (Type != IndexTypeEnum.XML)
{
if (Columns[j].Order)
{
sql.Append(" DESC");
}
else
{
sql.Append(" ASC");
}
}
if (j < Columns.Count - 1)
{
sql.Append(",");
}
sql.Append("\r\n");
}
else
{
if (String.IsNullOrEmpty(includes))
{
includes = ") INCLUDE (";
}
includes += "[" + Columns[j].Name + "],";
}
}
if (!String.IsNullOrEmpty(includes))
{
includes = includes.Substring(0, includes.Length - 1);
}
sql.Append(includes);
sql.Append(")");
if (!String.IsNullOrEmpty(FilterDefintion))
{
sql.Append("\r\n WHERE " + FilterDefintion + "\r\n");
}
sql.Append(" WITH (");
if (Parent.ObjectType == Enums.ObjectType.TableType)
{
if ((IgnoreDupKey) && (IsUniqueKey))
{
sql.Append("IGNORE_DUP_KEY = ON ");
}
else
{
sql.Append("IGNORE_DUP_KEY = OFF ");
}
}
else
{
if (IsPadded)
{
sql.Append("PAD_INDEX = ON, ");
}
else
{
sql.Append("PAD_INDEX = OFF, ");
}
if (IsAutoStatistics)
{
sql.Append("STATISTICS_NORECOMPUTE = ON, ");
}
else
{
sql.Append("STATISTICS_NORECOMPUTE = OFF, ");
}
if (Type != IndexTypeEnum.XML)
{
if ((IgnoreDupKey) && (IsUniqueKey))
{
sql.Append("IGNORE_DUP_KEY = ON, ");
}
else
{
sql.Append("IGNORE_DUP_KEY = OFF, ");
}
}
if (AllowRowLocks)
{
sql.Append("ALLOW_ROW_LOCKS = ON, ");
}
else
{
sql.Append("ALLOW_ROW_LOCKS = OFF, ");
}
if (AllowPageLocks)
{
sql.Append("ALLOW_PAGE_LOCKS = ON");
}
else
{
sql.Append("ALLOW_PAGE_LOCKS = OFF");
}
if (FillFactor != 0)
{
sql.Append(", FILLFACTOR = " + FillFactor.ToString());
}
}
sql.Append(")");
if (!String.IsNullOrEmpty(FileGroup))
{
sql.Append(" ON [" + FileGroup + "]");
}
sql.Append("\r\nGO\r\n");
if (IsDisabled)
{
sql.Append("ALTER INDEX [" + Name + "] ON " + ((Table)Parent).FullName + " DISABLE\r\nGO\r\n");
}
sql.Append(ExtendedProperties.ToSql());
return(sql.ToString());
}
public override String CreateScript()
{
StringBuilder builder = new StringBuilder(String.Empty);
IEnumerable <String> includedColumns = null;
Boolean isXml = TypeStatement.ToLower().Equals("xml");
if (isXml)
{
builder.AppendLine($"CREATE {( UsingXMLIndexId == 0 ? "PRIMARY" : "" )} XML INDEX {Name}");
builder.Append($"ON {ParentObject.QualifiedName}");
}
else if (IsPrimaryKey)
{
builder.AppendFormat($"ALTER TABLE {ParentObject.QualifiedName} ADD CONSTRAINT [{Name}] PRIMARY KEY {TypeStatement}");
}
else if (IsUniqueConstraint)
{
builder.Append($"ALTER TABLE {ParentObject.QualifiedName} ADD CONSTRAINT [{Name}] UNIQUE {TypeStatement}");
}
else
{
if (IsUnique)
{
builder.Append($"CREATE UNIQUE {TypeStatement} INDEX [{Name}] ON {ParentObject.QualifiedName}");
}
else
{
builder.Append($"CREATE {TypeStatement} INDEX [{Name}] ON {ParentObject.QualifiedName}");
}
includedColumns = Columns.Where(c => c.IsIncluded).Select(c => $"[{c.Name}]");
}
builder.Append("\r\n(");
if (isXml)
{
builder.Append(String.Join(",", Columns.Where(c => !c.IsIncluded).Select(c => $"[{c.Name}]")));
}
else
{
builder.Append(String.Join(",", Columns.Where(c => !c.IsIncluded).Select(c => c.ScriptStatement)));
}
builder.AppendLine(" )");
if (includedColumns != null && includedColumns.Count() > 0)
{
builder.Append("INCLUDE (");
builder.Append(String.Join(",", includedColumns));
builder.AppendLine(" )");
}
if (isXml && UsingXMLIndexId != 0)
{
builder.AppendLine($"USING XML INDEX {UsingXMLIndex.Name} FOR {XMLSecondaryType} ");
}
builder.AppendLine("WITH ( ");
builder.AppendFormat($"PAD_INDEX = {SqlStatement.GetOnOffStatement(IsPadded)}, ");
if (!isXml)
{
builder.AppendFormat($"STATISTICS_NORECOMPUTE = {SqlStatement.GetOnOffStatement(IsAutoStatistics)}, ");
}
builder.AppendFormat($"IGNORE_DUP_KEY = {SqlStatement.GetOnOffStatement(IgnoreDupKey)}, ");
builder.AppendFormat($"ALLOW_ROW_LOCKS = {SqlStatement.GetOnOffStatement(AllowRowLocks)}, ");
builder.AppendFormat($"ALLOW_PAGE_LOCKS = {SqlStatement.GetOnOffStatement(AllowPageLocks)} ");
if (FillFactor != 0)
{
builder.AppendFormat($", FILLFACTOR = {FillFactor.ToString()}");
}
builder.Append(" ) ");
//if (!String.IsNullOrEmpty(FileGroup) && !isXml) builder.Append(" ON [" + FileGroup + "]");
builder.Append(SqlStatement.GO);
return(builder.ToString());
}
