Home > Scripts > Indicizzazione dei vincoli FOREIGN KEY

Indicizzazione dei vincoli FOREIGN KEY

Una relazione tra le tabelle di un database è espressa nei valori dei dati attraverso una chiave primaria (Primary Key) ed una chiave esterna (Foreign Key).

La chiave primaria è rappresenta da una o più colonne di una tabella i cui valori identificano in modo univoco ogni riga della tabella stessa. La chiave esterna è rappresenta da una o più colonne i cui valori sono gli stessi della chiave primaria.

La relazione viene costruita tra due tabelle confrontando i valori della chiave esterna con quelli della chiave primaria, in altre parole la chiave esterna rappresenta una copia della chiave primaria.

Creare un indice su una chiave esterna rappresenta spesso un’operazione utile in quanto:

  • Le modifiche apportate ai vincoli PRIMARY KEY vengono confrontate con i vincoli FOREIGN KEY nelle tabelle correlate
  • Le colonne chiave primaria vengono generalmente citate nei criteri di JOIN quando i dati provenienti da tabelle correlate vengono riuniti in query confrontando le colonne nel vincolo FOREIGN KEY di una tabella con le colonne chiave primaria o univoca nell’altra tabella. In queste situazioni, la presenza di un indice consente all’engine, di SQL Server, di trovare rapidamente i dati correlati nella tabella della chiave esterna
    La stored procedure dbo.usp_create_index_on_foreign_key, definita di seguito, rappresenta uno strumento per determinare e generare un indice (nonclustered) per ogni FOREIGN KEY CONSTRAINT rilevato sulla tabella specificata o sull’intero database di riferimento.

— Drop when exist
IF OBJECT_ID(‘usp_create_index_on_foreign_key’) IS NOT NULL
  DROP PROCEDURE dbo.usp_create_index_on_foreign_key
go

— Create procedure
CREATE PROCEDURE dbo.usp_create_index_on_foreign_key
(@mode tinyint,
 @tableschema nvarchar(128),
 @tablename sysname,
 @enforce_existing_index tinyint)
AS BEGIN

  /*
    Descrizione:
      Determina/genera un indice (nonclustered)
      per ogni FOREIGN KEY CONSTRAINT rilevato
      sulla tabella specificata o sull’intero
      database di riferimento

    Parametri:
      @mode = Modalità di utilizzo,
              Può assumere i valori: 1= Exec Mode, 0= Print Mode

      @tableschema = Schema di riferimento

      @tablename = Tabella di riferimento
                
      @enforce_existing_index = Applica comunque l’indice anche se ne esiste
                                già uno che interessa la stessa
                                colonna (citata nella Foreign Key)
                                Può assumere i valori: 1= Applica comunque, 0= Non applicare
  */

  DECLARE @cmd nvarchar(500),
          @count_idx integer,
          @msg nvarchar(500)

  — Controllo parametri di input
  — @mode
  IF (@mode NOT IN (0, 1))
  BEGIN
    PRINT OBJECT_NAME(@@PROCID) +
          ‘: Modalità di esecuzione non supportata, ‘ +
          ‘@mode può assumere i seguenti valori: ‘ +
          ‘1= Modalità Exec, 0= Modalità Print. ‘ +
          ‘Il valore di @mode passato è: ‘ + LTRIM(STR(@mode))
    RETURN
  END

  — @enforce_existing_index
  IF (@enforce_existing_index NOT IN (0, 1))
  BEGIN
    PRINT OBJECT_NAME(@@PROCID) +
          ‘: Valore di input non previsto, ‘ +
          ‘@enforce_existing_index può assumere i seguenti valori: ‘ +
          ‘1= Applica comunque l”indice, 0= Non applicare l”indice ‘ +
          ‘se già esistente per la colonna interessata.’
          –‘Il valore di @enforce_existing_index passato è: ‘ +
          –LTRIM(STR(@enforce_existing_index))
    RETURN
  END

  IF (LTRIM(RTRIM(@tablename)) = ”)
    SET @tablename = NULL
  ELSE
    SET @tablename = LTRIM(RTRIM(@tablename))

  IF (LTRIM(RTRIM(@tableschema)) = ”)
    SET @tableschema = NULL
  ELSE
    SET @tableschema = LTRIM(RTRIM(@tableschema))
  
  DECLARE cur CURSOR FOR
    SELECT
      ‘CREATE NONCLUSTERED INDEX ‘ +
      ‘[IDX__’ + LTRIM(RTRIM(ccu.table_name)) + ‘_’ +
                 LTRIM(RTRIM(ccu.column_name)) + ‘] ‘ +
      ‘ON ‘ + LTRIM(RTRIM(ccu.table_schema)) + ‘.’ +
              LTRIM(RTRIM(ccu.table_name)) + ‘(‘ +
              LTRIM(RTRIM(ccu.column_name)) + ‘)’ AS cmd,

   (SELECT
         count(cu.column_id)
       FROM
         sys.indexes i
       JOIN
         sys.objects t on t.object_id=i.object_id
       JOIN
         sys.index_columns c on c.index_id=i.index_id
       JOIN
         sys.columns cu on cu.column_id=c.column_id
       WHERE
         (t.object_id=OBJECT_ID( ISNULL(@tableschema, LTRIM(RTRIM(ccu.table_schema))) + ‘.’ +
                                 ISNULL(@tablename, LTRIM(RTRIM(ccu.table_name))) )) AND
         (i.is_primary_key <> 1) AND
         (c.object_id=t.object_id) AND
         (cu.object_id=t.object_id) AND
         (cu.name=ccu.column_name)
      ) AS exists_index_on_column

    FROM
      INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE ccu
    JOIN
      INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS rc ON RTRIM(rc.constraint_name)=RTRIM(ccu.constraint_name)
    WHERE
      (ISNULL(@tablename, LTRIM(RTRIM(ccu.table_name))) = (LTRIM(RTRIM(ccu.table_name)))) AND
      (ISNULL(@tableschema, LTRIM(RTRIM(ccu.table_schema))) = (LTRIM(RTRIM(ccu.table_schema)))) AND
      NOT EXISTS (SELECT
                    i.object_id
                  FROM
                    sys.indexes i
                  WHERE
                    LTRIM(RTRIM(i.name))= ‘IDX__’ + LTRIM(RTRIM(ccu.table_name)) + ‘_’ +
                                                    LTRIM(RTRIM(ccu.column_name)))   

  — Open cursore cur
  OPEN cur

  — Fetch fuori ciclo
  FETCH NEXT FROM cur
    INTO @cmd, @count_idx

  IF (@mode = 1)
    SET @msg = OBJECT_NAME(@@PROCID) + ‘ : Generazione indici…’
  ELSE
    SET @msg = OBJECT_NAME(@@PROCID) + ‘ : Scrip indici…’

  PRINT (@msg)
  PRINT (”)

  IF (@@FETCH_STATUS <> 0)
  BEGIN
    SET @msg = OBJECT_NAME(@@PROCID) + ‘ : Non sono stati rilevati indici da generare’

    IF (@tablename IS NULL) AND (@tableschema IS NULL)
      SET @msg = @msg + ‘ sul database corrente.’

    ELSE IF (@tablename IS NOT NULL) AND (@tableschema IS NOT NULL)
      SET @msg = @msg + ‘ sulla tabella ‘ + @tableschema + ‘.’ + @tablename

    ELSE IF (@tablename IS NULL) AND (@tableschema IS NOT NULL)
      SET @msg = @msg + ‘ sullo schema ‘ + @tableschema

    ELSE IF (@tablename IS NOT NULL) AND (@tableschema IS NULL)
      SET @msg = @msg + ‘ sulla tabella ‘ + @tablename

    PRINT (@msg)
  END

  WHILE (@@FETCH_STATUS = 0)
  BEGIN
    IF (((@count_idx = 0) OR (@enforce_existing_index = 1)))
    BEGIN
      –PRINT STR(@count_idx)
      –PRINT STR(@enforce_existing_index)
      IF (@mode = 1) 
      BEGIN
        EXEC (@cmd)
        IF (@@ERROR = 0)
        BEGIN
          SET @msg = ‘Esecuzione di: ‘ + @cmd + ‘ completata correttamente.’
          PRINT (@msg)
        END
      END
      ELSE
        PRINT (@cmd)

      PRINT (”)
    END

    FETCH NEXT FROM cur
      INTO @cmd, @count_idx
  END

  — Close e deallocate cur
  CLOSE cur
  DEALLOCATE cur

  IF (@mode = 1)
    SET @msg = OBJECT_NAME(@@PROCID) + ‘ : Generazione indici completata.’
  ELSE
    SET @msg = OBJECT_NAME(@@PROCID) + ‘ : Scrip indici completata.’

  PRINT (”)
  PRINT (@msg)
END

Esempio

Dopo aver creato la stored procedure dbo.usp_create_index_on_foreign_key sul database AdventureWorks, sarà possibile ottenere uno script con i comandi di creazione degli indici mancanti sui vincoli foreign key (ad esempio) relativi tabella HumanResources.Employee.

USE AdventureWorks;
go

EXEC dbo.usp_create_index_on_foreign_key 0, N’HumanResources’, N’Employee’, 0;

Output

usp_create_index_on_foreign_key : Scrip indici…

CREATE NONCLUSTERED INDEX [IDX__Employee_ContactID] ON HumanResources.Employee(ContactID)

usp_create_index_on_foreign_key : Scrip indici completata.

Post correlati

Abilita o Disabilita i FOREIGN KEYS CONSTRAINT

Chi è Sergio Govoni

Sergio Govoni è laureato in Scienze e Tecnologie Informatiche. Da oltre 16 anni lavora presso una software house che produce un noto sistema ERP, distribuito a livello nazionale ed internazionale, multi azienda client/server su piattaforma Win32. Attualmente si occupa di progettazione e analisi funzionale, coordina un team di sviluppo ed è responsabile tecnico di prodotto. Lavora con SQL Server dalla versione 7.0 e si è occupato d'implementazione e manutenzione di database relazionali in ambito gestionale, ottimizzazione delle prestazioni e problem solving. Nello staff di UGISS si dedica alla formazione e alla divulgazione in ambito SQL Server e tecnologie a esso collegate, scrivendo articoli e partecipando come speaker ai workshop e alle iniziative del primo e più importante User Group Italiano sulla tecnologia SQL Server. Ha conseguito la certificazione MCP, MCTS SQL Server. Per il suo contributo nelle comunità tecniche e per la condivisione della propria esperienza con altri, dal 2010 riceve il riconoscimento SQL Server MVP (Microsoft Most Valuable Professional). Nel corso dell'anno 2011 ha contribuito alla scrittura del libro SQL Server MVP Deep Dives Volume 2 (http://www.manning.com/delaney/).

Leggi Anche

Aggiornamento statistiche SQL per database che contengono External Table PolyBase

Recentemente, ho avuto l’occasione di configurare un piano di manutenzione database per un DB in …