none
Nonclustered index RRS feed

  • Domanda

  • Avrei bisogno di fare una drop di un nonclustered index e dopo aver fatto un alter su una tabella, ricreare l'indice uguale a prima. Visto che il mi script deve essere generico, come posso conservare le proprietà dell'indice droppato per poi ricrearlo uguale?

    Grazie

    Tommaso

    venerdì 20 ottobre 2017 09:15

Risposte

  • Ciao Tommaso,

    prima di eliminare l'indice dovresti generare lo script di creazione dell'indice stesso, così come è attualmente. Dopo averlo eliminato, ed eseguiti gli ALTER TABLE del caso, potrai ricreare l'indice dallo script.

    Se hai la necessità di automatizzare il processo, puoi partire da questi due script che generano rispettivamente i comandi di CREATE INDEX e DROP INDEX degli indici di un DB.

    WITH Indici AS ( SELECT 'A' AS RowType ,T.object_id ,T.index_id ,T.IdxLevel ,T.KeyOrdinal ,T.IsUnique ,T.IsClustered ,T.SchemaName ,T.TableName ,CAST('CREATE ' + T.IsUnique + T.IsClustered + 'INDEX ' + RTRIM(T.IndexName) + ' ON ' + RTRIM(T.SchemaName) + '.' + RTRIM(T.TableName) + ' ( ' AS VARCHAR(MAX)) AS Command FROM ( SELECT DISTINCT idx.object_id ,idx.index_id ,CAST(0 AS INTEGER) AS IdxLevel ,CAST(0 AS INTEGER) AS KeyOrdinal ,CAST(CASE (idx.is_unique)

    WHEN 1 THEN 'UNIQUE '

    WHEN 0 THEN '' ELSE '' END AS VARCHAR(MAX)) AS IsUnique ,CAST(CASE (idx.type) WHEN 1 THEN 'CLUSTERED ' WHEN 2 THEN 'NONCLUSTERED ' ELSE '' END AS VARCHAR(MAX)) AS IsClustered ,idx.name AS IndexName ,sch.name AS SchemaName ,obj.name AS TableName FROM sys.index_columns AS idxc JOIN sys.indexes AS idx ON ((idx.index_id=idxc.index_id) AND (idx.object_id=idxc.object_id)) JOIN sys.objects AS obj ON idxc.object_id=obj.object_id JOIN sys.columns AS col ON ((col.column_id=idxc.column_id) AND (col.object_id=idxc.object_id)) JOIN sys.schemas AS sch ON obj.schema_id=sch.schema_id WHERE (idx.is_unique_constraint = 0) AND (idx.is_primary_key = 0) AND (obj.type = 'U') ) AS T UNION ALL SELECT 'R' AS RowType ,idxcol.object_id ,idxcol.index_id ,CAST(I.IdxLevel + 1 AS INTEGER) AS IdxLevel ,CAST(idxcol.key_ordinal AS INTEGER) KeyOrdinal ,CAST('' AS VARCHAR(MAX)) AS IsUnique ,CAST('' AS VARCHAR(MAX)) AS IsClustered ,I.SchemaName ,I.TableName ,CAST(I.Command + CASE (idxcol.key_ordinal) WHEN 1 THEN '' ELSE ',' END + ' [' + RTRIM(col.name) + '] ' AS VARCHAR(MAX)) AS Command FROM Indici AS I JOIN sys.index_columns AS idxcol ON idxcol.object_id=I.object_id AND idxcol.index_id=I.index_id JOIN sys.columns AS col ON ((col.column_id=idxcol.column_id) AND (col.object_id=idxcol.object_id)) WHERE (idxcol.key_ordinal = I.KeyOrdinal + 1) ), Indici2 AS ( SELECT MAX(Indici.KeyOrdinal) AS MaxKeyOrdinal ,Indici.object_id ,Indici.index_id FROM Indici JOIN sys.objects AS O ON O.object_id=Indici.object_id WHERE (Indici.RowType = 'R') GROUP BY Indici.object_id ,Indici.index_id ) SELECT Indici.SchemaName ,Indici.TableName ,Command = Indici.Command + ')' FROM Indici2 JOIN Indici ON (Indici.object_id=Indici2.object_id) AND (Indici.index_id=Indici2.index_id) AND (Indici.KeyOrdinal=Indici2.MaxKeyOrdinal); GO


    SELECT
      DISTINCT
      SchemaName = sch.name
      ,TableName = obj.name
      ,('DROP INDEX ' +
          RTRIM(sch.name) + '.' +
    	  RTRIM(obj.name) + '.' +
    	  RTRIM(idx.name) + '; '
       ) AS Command
    FROM
      sys.index_columns AS idxc
    JOIN
      sys.indexes AS idx ON ((idx.index_id=idxc.index_id) AND (idx.object_id=idxc.object_id))
    JOIN
      sys.objects AS obj ON idxc.object_id=obj.object_id
    JOIN
      sys.columns AS col ON ((col.column_id=idxc.column_id) AND (col.object_id=idxc.object_id))
    JOIN
      sys.schemas AS sch ON obj.schema_id=sch.schema_id
    WHERE
      (idx.is_unique_constraint = 0)
      AND (idx.is_primary_key = 0)
      AND (obj.type = 'U')
    ORDER BY
      Command;
    GO

    I due script sono abbastanza generici ed estraggono anche gli indici cluster, se non servono, puoi evitare di estrarli.

    Mi raccomando, fai qualche test prima di eseguirli in produzione :)

    Ciao!


    Sergio Govoni

    Microsoft Data Platform MVP | MVP Profile | English Blog | Twitter | LinkedIn


    martedì 24 ottobre 2017 21:59
    Moderatore

Tutte le risposte

  • credo tu stia proseguendo su un 3d diverso lo stesso rpoblema comunque leggi anche qui

    https://sqlstudies.com/2014/12/11/drop-index-and-create-index-vs-alter-index-rebuild/


    Edoardo Benussi
    Microsoft MVP - Cloud and Datacenter Management
    e[dot]benussi[at]outlook[dot]it

    venerdì 20 ottobre 2017 14:12
    Moderatore
  • Ciao Tommaso,

    prima di eliminare l'indice dovresti generare lo script di creazione dell'indice stesso, così come è attualmente. Dopo averlo eliminato, ed eseguiti gli ALTER TABLE del caso, potrai ricreare l'indice dallo script.

    Se hai la necessità di automatizzare il processo, puoi partire da questi due script che generano rispettivamente i comandi di CREATE INDEX e DROP INDEX degli indici di un DB.

    WITH Indici AS ( SELECT 'A' AS RowType ,T.object_id ,T.index_id ,T.IdxLevel ,T.KeyOrdinal ,T.IsUnique ,T.IsClustered ,T.SchemaName ,T.TableName ,CAST('CREATE ' + T.IsUnique + T.IsClustered + 'INDEX ' + RTRIM(T.IndexName) + ' ON ' + RTRIM(T.SchemaName) + '.' + RTRIM(T.TableName) + ' ( ' AS VARCHAR(MAX)) AS Command FROM ( SELECT DISTINCT idx.object_id ,idx.index_id ,CAST(0 AS INTEGER) AS IdxLevel ,CAST(0 AS INTEGER) AS KeyOrdinal ,CAST(CASE (idx.is_unique)

    WHEN 1 THEN 'UNIQUE '

    WHEN 0 THEN '' ELSE '' END AS VARCHAR(MAX)) AS IsUnique ,CAST(CASE (idx.type) WHEN 1 THEN 'CLUSTERED ' WHEN 2 THEN 'NONCLUSTERED ' ELSE '' END AS VARCHAR(MAX)) AS IsClustered ,idx.name AS IndexName ,sch.name AS SchemaName ,obj.name AS TableName FROM sys.index_columns AS idxc JOIN sys.indexes AS idx ON ((idx.index_id=idxc.index_id) AND (idx.object_id=idxc.object_id)) JOIN sys.objects AS obj ON idxc.object_id=obj.object_id JOIN sys.columns AS col ON ((col.column_id=idxc.column_id) AND (col.object_id=idxc.object_id)) JOIN sys.schemas AS sch ON obj.schema_id=sch.schema_id WHERE (idx.is_unique_constraint = 0) AND (idx.is_primary_key = 0) AND (obj.type = 'U') ) AS T UNION ALL SELECT 'R' AS RowType ,idxcol.object_id ,idxcol.index_id ,CAST(I.IdxLevel + 1 AS INTEGER) AS IdxLevel ,CAST(idxcol.key_ordinal AS INTEGER) KeyOrdinal ,CAST('' AS VARCHAR(MAX)) AS IsUnique ,CAST('' AS VARCHAR(MAX)) AS IsClustered ,I.SchemaName ,I.TableName ,CAST(I.Command + CASE (idxcol.key_ordinal) WHEN 1 THEN '' ELSE ',' END + ' [' + RTRIM(col.name) + '] ' AS VARCHAR(MAX)) AS Command FROM Indici AS I JOIN sys.index_columns AS idxcol ON idxcol.object_id=I.object_id AND idxcol.index_id=I.index_id JOIN sys.columns AS col ON ((col.column_id=idxcol.column_id) AND (col.object_id=idxcol.object_id)) WHERE (idxcol.key_ordinal = I.KeyOrdinal + 1) ), Indici2 AS ( SELECT MAX(Indici.KeyOrdinal) AS MaxKeyOrdinal ,Indici.object_id ,Indici.index_id FROM Indici JOIN sys.objects AS O ON O.object_id=Indici.object_id WHERE (Indici.RowType = 'R') GROUP BY Indici.object_id ,Indici.index_id ) SELECT Indici.SchemaName ,Indici.TableName ,Command = Indici.Command + ')' FROM Indici2 JOIN Indici ON (Indici.object_id=Indici2.object_id) AND (Indici.index_id=Indici2.index_id) AND (Indici.KeyOrdinal=Indici2.MaxKeyOrdinal); GO


    SELECT
      DISTINCT
      SchemaName = sch.name
      ,TableName = obj.name
      ,('DROP INDEX ' +
          RTRIM(sch.name) + '.' +
    	  RTRIM(obj.name) + '.' +
    	  RTRIM(idx.name) + '; '
       ) AS Command
    FROM
      sys.index_columns AS idxc
    JOIN
      sys.indexes AS idx ON ((idx.index_id=idxc.index_id) AND (idx.object_id=idxc.object_id))
    JOIN
      sys.objects AS obj ON idxc.object_id=obj.object_id
    JOIN
      sys.columns AS col ON ((col.column_id=idxc.column_id) AND (col.object_id=idxc.object_id))
    JOIN
      sys.schemas AS sch ON obj.schema_id=sch.schema_id
    WHERE
      (idx.is_unique_constraint = 0)
      AND (idx.is_primary_key = 0)
      AND (obj.type = 'U')
    ORDER BY
      Command;
    GO

    I due script sono abbastanza generici ed estraggono anche gli indici cluster, se non servono, puoi evitare di estrarli.

    Mi raccomando, fai qualche test prima di eseguirli in produzione :)

    Ciao!


    Sergio Govoni

    Microsoft Data Platform MVP | MVP Profile | English Blog | Twitter | LinkedIn


    martedì 24 ottobre 2017 21:59
    Moderatore