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