• Articolo

Informazioni sulle dipendenze SQL

Le dipendenze SQL sono i riferimenti in base al nome utilizzati nelle espressioni SQL per creare dipendenze tra entità. Un'entità che fa riferimento a un'altra entità nella propria definizione archiviata nel catalogo del sistema viene definita entità di riferimento. Un'entità a cui fa riferimento un'altra entità viene definita entità con riferimenti. Esistono due tipi di dipendenza registrati da Motore di database.

  • Dipendenza associata a schema

    Una dipendenza associata a schema è una relazione tra due entità che impedisce l'eliminazione o la modifica dell'entità a cui si fa riferimento finché esiste l'entità di riferimento. Una dipendenza associata a schema viene creata quando una vista o la funzione definita dall'utente è creata utilizzando la clausola WITH SCHEMABINDING. È anche possibile creare una dipendenza associata a schema quando una tabella fa riferimento a un'altra entità, ad esempio una funzione definita dall'utente di Transact-SQL, un tipo definito dall'utente o una raccolta di XML Schema in un vincolo CHECK o DEFAULT o nella definizione di una colonna calcolata. La specifica di un oggetto utilizzando un nome composto da due parti (schema_name.object_name) non viene identificata come un riferimento associato a schema.

  • Dipendenza non associata a schema

    Una dipendenza non associata a schema è una relazione tra due entità che non impedisce l'eliminazione o la modifica dell'entità a cui si fa riferimento.

Nella figura seguente viene illustrato un esempio di dipendenza SQL.

Illustrazione di una dipendenza SQL

Nella figura sono presenti due entità: la procedura X e la procedura Y. La procedura X contiene un'espressione SQL che presenta un riferimento in base al nome alla procedura Y. La procedura X è nota come entità di riferimento, mentre la procedura Y è nota come entità con riferimenti. Poiché la procedura X dipende dalla procedura Y, se quest'ultima non esiste, la procedura X genererà un errore di run-time. Diversamente, la procedura Y non genera un errore se la procedura X non esiste.

Nell'esempio seguente viene illustrata la dipendenza della stored procedure X dalla stored procedure Y.

USE tempdb;
GO
CREATE PROCEDURE dbo.Y AS
SELECT * FROM sys.objects
GO
CREATE PROCEDURE dbo.X as
    EXEC dbo.Y;
GO

Per visualizzare la dipendenza di X da Y, eseguire la query seguente.

SELECT * 
FROM sys.sql_expression_dependencies 
WHERE referencing_id = OBJECT_ID('X')
    AND referenced_id = OBJECT_ID('Y')
    AND referenced_schema_name = 'dbo'
    AND referenced_entity_name = 'Y'
    AND referenced_database_name IS NULL
    AND referenced_server_name IS NULL;
GO

Tipi di entità di riferimento e con riferimenti

Nella tabella seguente sono elencati i tipi di entità per i quali vengono create e gestite le informazioni sulle dipendenze. La tabella indica se l'entità è registrata come un'entità di riferimento o un'entità a cui si fa riferimento. Le informazioni sulle dipendenze non vengono create né gestite per regole, impostazioni predefinite, tabelle temporanee, stored procedure temporanee o oggetti di sistema.

Tipo di entità

Entità di riferimento

Entità con riferimenti

Tabella

Sì*

Sì

Vista

Sì

Sì

Stored procedure Transact-SQL**

Sì

Sì

Stored procedure CLR

No

Sì

Funzione Transact-SQL definita dall'utente

Sì

Sì

Funzione CLR definita dall'utente

No

Sì

Trigger CLR (DML e DDL)

No

No

Trigger DML Transact-SQL

Sì

No

Trigger DDL Transact-SQL a livello di database

Sì

No

Trigger DDL Transact-SQL a livello di server

Sì

No

Stored procedure estese

No

Sì

Coda

No

Sì

Sinonimo

No

Sì

Tipo (alias e tipo di CLR definito dall'utente)

No

Sì

Raccolta di XML Schema

No

Sì

Funzione di partizione

No

Sì

* Una tabella è registrata come un'entità di riferimento solo quando fa riferimento al modulo Transact-SQL, a un tipo definito dall'utente o a una raccolta di XML Schema nella definizione di una colonna calcolata, un vincolo CHECK o un vincolo DEFAULT.

** Le stored procedure numerate con un valore integer maggiore di 1 non vengono registrate come entità di riferimento o entità con riferimenti.

Modalità di rilevamento delle informazioni sulle dipendenze

Il Motore di database rileva automaticamente le informazioni sulle dipendenze quando vengono create, modificate o eliminate e registrate entità di riferimento e registra queste informazioni nel catalogo di sistema SQL Server. Ad esempio, se si crea un trigger che fa riferimento a una tabella, viene registrata una dipendenza tra queste entità. Se si rilascia successivamente il trigger, le informazioni sulle dipendenze vengono rimosse dal catalogo di sistema.

A differenza delle versioni precedenti di SQL Server nelle quali le dipendenze venivano rilevate in base agli ID, le dipendenze ora vengono registrate in base al nome. Questo significa che Motore di database registra informazioni sulle dipendenze tra due entità anche se l'entità con riferimenti non esiste al momento della creazione dell'entità di riferimento. Questa circostanza può verificarsi a causa della risoluzione dei nomi posticipata. Ad esempio, una stored procedure a cui fa riferimento una tabella può essere creata correttamente, ma non eseguita, anche se la tabella a cui si fa riferimento non esiste nel database. Motore di database registra la dipendenza tra la procedura e la tabella, tuttavia, non è possibile registrare un ID per la tabella poiché l'oggetto non esiste ancora. Se la tabella viene creata in un secondo momento, l'ID della tabella viene restituito con altre informazioni sulle dipendenze.

Le informazioni sulle dipendenze vengono rilevate quando l'entità con riferimenti viene visualizzata in base al nome in un'espressione SQL persistente dell'entità di riferimento. Le informazioni sulle dipendenze vengono ottenute quando si fa riferimento alle entità in base al nome nelle modalità seguenti:

  • Utilizzando qualsiasi istruzione seguente nella definizione di un Transact-SQLmodulo:

    • Istruzioni Data Manipulation Language (SELECT, INSERT, UPDATE, DELETE, MERGE) (DML)

    • EXECUTE

    • DECLARE

    • SET (Quando SET è utilizzato con una funzione definita dall'utente o un tipo definito dall'utente. Ad esempio, DECLARE @var int; SET @var = dbo.udf1.)

    Le entità a cui viene fatto riferimento nella definizione di un modulo Transact-SQL utilizzando istruzioni Data Definition Language (DDL) ad esempio CREATE, ALTER o DROP, non sono registrate.

  • Utilizzando istruzioni CREATE, ALTER o DROP TABLE quando le istruzioni non sono in un modulo Transact-SQL e l'entità a cui si fa riferimento è una funzione definita dall'utente Transact-SQL, un tipo definito dall'utente o una raccolta di XML Schema definiti in una colonna calcolata, un vincolo CHECK o un vincolo DEFAULT.

Dipendenze tra database e tra server

Una dipendenza tra database viene creata quando un'entità fa riferimento a un'altra entità utilizzando un nome valido composto da tre parti. Una dipendenza tra server viene creata quando un'entità fa riferimento a un'altra entità utilizzando un nome valido composto da quattro parti. Il nome del server e il database vengono registrati solo quando il nome viene specificato in modo esplicito. Ad esempio, quando si specifica MyServer.MyDB.MySchema.MyTable viene registrato il nome del server e del database; tuttavia, quando viene specificato MyServer..MySchema.MyTable, viene registrato solo il nome del server. Per informazioni sui nomi validi composti da più parti, vedere Convenzioni della sintassi Transact-SQL (Transact-SQL).

Vengono applicate le limitazioni seguenti:

  • Le dipendenze tra server per le istruzioni OPENROWSET, OPENQUERY e OPENDATASOURCE non sono registrate.

  • Le dipendenze per l'istruzione EXEC ('.') AT linked_server non sono registrate.

Nella tabella seguente sono riepilogate le dipendenze tra server e tra database registrate e le informazioni registrate nel catalogo di sistema e indicate da sys.sql_expression_dependencies (Transact-SQL).

Espressione SQL in un modulo

È registrato

Nome server con riferimenti

Nome database con riferimenti

Nome schema con riferimenti

Nome entità con riferimenti

SELECT * FROM s1.db2.sales.t1

Sì

s1

db2

sales

t1

SELECT * FROM db3..t1

Sì

 

db3

 

t1

EXEC db2.dbo.Proc1

Sì

 

db2

dbo

proc1

EXEC ('…') AT linked_srv1

No

 

 

 

 

EXEC linked_svr1.db2.sales.proc2

Sì

linked_svr1

db2

sales

proc2

Effetto delle regole di confronto sul rilevamento delle dipendenze

Le regole di confronto determinano le regole in base alle quali i dati vengono ordinati e confrontati. Le regole di confronto del database vengono utilizzate per identificare le informazioni sulle dipendenze per le entità all'interno del database. Ad esempio, se una stored procedure fa riferimento alle entità Some_Table e SOME_TABLE di un database che utilizza regole di confronto che eseguono la distinzione tra maiuscole e minuscole, vengono registrate le informazioni sulle dipendenze per le due entità poiché un confronto dei due nomi indica che non sono uguali. Tuttavia, se il database utilizza regole di confronto che eseguono la distinzione tra maiuscole e minuscole, viene registrata solo una dipendenza.

Per le dipendenze tra server e tra database, le regole di confronto del server sul quale risiede l'oggetto di riferimento viene utilizzato per risolvere il nome del server e del database. Le regole di confronto del database corrente vengono utilizzate per risolvere il nome dello schema e i nomi degli oggetti.

Si consideri la seguente definizione di stored procedure. Se la stored procedure viene creata in un database con regole di confronto che eseguono la distinzione tra maiuscole e minuscole in un'istanza di SQL Server con regole di confronto del server che eseguono la distinzione tra maiuscole e minuscole, vengono registrate due dipendenze per le entità srv_referenced.db_referenced.dbo.p_referenced e srv_referenced.db_referenced.DBO.P_REFERENCED.

CREATE PROCEDURE p_referencing AS
    EXECUTE srv_referenced.db_referenced.dbo.p_referenced
    EXECUTE srv_referenced.db_referenced.DBO.P_REFERENCED
    EXECUTE SRV_REFERENCED.DB_REFERENCED.dbo.p_referenced;

Risoluzione di riferimenti ambigui

Un riferimento è ambiguo quando può essere risolto in fase di esecuzione in una funzione definita dall'utente, in un tipo definito dall'utente o in un riferimento xquery a una colonna di tipo xml.

Si consideri la seguente definizione di stored procedure.

CREATE PROCEDURE dbo.p1 AS 
    SELECT column_a, Sales.GetOrder() FROM Sales.MySales;

Durante la creazione della stored procedure non è possibile sapere se Sales.GetOrder() è una funzione definita dall'utente denominata GetOrder nello schema Sales o una colonna denominata Sales di tipo definito dall'utente con un metodo GetOrder(). Quando un riferimento è ambiguo, viene indicata una dipendenza ambigua impostando su 1 la colonna is_ambiguous in sys.sql_expression_dependencies e sys.dm_sql_referenced_entities. Vengono indicate le seguenti informazioni sulle dipendenze:

  • La dipendenza tra la stored procedure e la tabella.

  • La dipendenza tra la stored procedure e la funzione definita dall'utente. Se la funzione esiste, viene riportato l'ID della funzione; in caso contrario, l'ID è NULL.

  • La dipendenza dalla funzione è contrassegnata come ambigua. Pertanto, is_ambiguous è impostato su 1.

  • Le dipendenze a livello di colonna non vengono riportate perché non è possibile associare l'istruzione nella quale si fa riferimento alle colonne.

Gestione delle dipendenze

Motore di database gestisce le dipendenze associate a schema e non associate a schema. Queste dipendenze vengono aggiornate automaticamente durante qualsiasi operazione che ha un impatto sul rilevamento delle dipendenze, ad esempio durante l'aggiornamento di un database da una versione precedente di SQL Server o la modifica delle regole di confronto di un database.