共用方式為


了解 SQL 相依性

SQL 相依性是 SQL 運算式中使用的依名稱 (by-name) 參考,可以讓一個實體相依於另一個實體。參考其定義中另一個實體,而且該定義儲存在系統目錄中的實體稱為參考實體。受到另一個實體參考的實體則稱為受參考的實體。由 Database Engine 追蹤的相依性類型有兩種。

  • 結構描述繫結的相依性

    結構描述繫結的相依性是兩個實體間的關聯性,只要參考實體存在,就可以防止受參考的實體遭到卸除或修改。使用 WITH SCHEMABINDING 子句建立檢視或使用者自訂函數時,會建立結構描述繫結的相依性。當資料表在 CHECK 或 DEFAULT 條件約束或計算資料行的定義中參考另一個實體 (例如,Transact-SQL 使用者自訂函數、使用者定義型別,或 XML 結構描述集合) 時,也可以建立結構描述繫結的相依性。使用兩段式 (schema_name.object_name) 名稱指定物件不會限定為結構描述繫結的參考。

  • 非結構描述繫結的相依性

    非結構描述繫結的相依性是兩個實體間的關聯性,無法防止受參考的實體遭到卸除或修改。

下圖顯示 SQL 相依性的範例。

SQL 相依性的描述

圖中有兩個實體:程序 X 和程序 Y。程序 X 包含的 SQL 運算式具有程序 Y 的依名稱參考。程序 X 稱為參考實體,而程序 Y 則稱為受參考的實體。由於程序 X 相依於程序 Y,因此如果程序 Y 不存在,程序 X 就會因為執行階段錯誤而失敗。不過,如果程序 X 不存在,程序 Y 並不會失敗。

下列範例顯示預存程序 X 如何相依於預存程序 Y。

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

若要查看 X 對 Y 的相依性,請執行下列查詢。

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

參考實體與受參考實體的類型

下表將列出建立並維護相依性資訊的實體類型。該表格會指出是當做參考實體或是受參考的實體追蹤。系統不會針對規則、預設值、暫存資料表、暫存預存程序或系統物件建立或維護相依性資訊。

實體類型

參考實體

受參考的實體

資料表

是*

檢視

Transact-SQL 預存程序**

CLR 預存程序

Transact-SQL 使用者定義函數

CLR 使用者定義函數

CLR 觸發程序 (DML 和 DDL)

Transact-SQL DML 觸發程序

Transact-SQL 資料庫層級 DDL 觸發程序

Transact-SQL 伺服器層級 DDL 觸發程序

擴充預存程序

佇列

同義字

型別 (別名和 CLR 使用者定義型別)

XML 結構描述集合

資料分割函數

* 只有當資料表參考計算資料行的定義、CHECK 條件約束或 DEFAULT 條件約束中的 Transact-SQL 模組、使用者定義型別或 XML 結構描述集合時,系統才會將它當做參考實體進行追蹤。

** 所包含之整數值大於 1 的編號預存程序不會當做參考或受參考的實體進行追蹤。

如何追蹤相依性資訊

建立、改變或卸除參考實體時,Database Engine 會自動追蹤相依性資訊,並將該資訊記錄在 SQL Server 系統目錄中。例如,如果您建立參考資料表的觸發程序,就會記錄這些實體之間的相依性。如果之後您卸除該觸發程序,就會從系統目錄移除相依性資訊。

不像舊版 SQL Server 的相依性是依識別碼追蹤,現在的相依性是依名稱追蹤。也就是說,即使受參考的實體在建立參考實體時不存在,Database Engine 還是會追蹤兩個實體之間的相依性資訊。這個情況會因為延遲名稱解析而發生。例如,即使受參考的資料表不存在於資料庫中,也可以成功建立參考資料表的預存程序,但是無法執行該預存程序。Database Engine 會記錄程序和資料表之間的相依性,但是會因為物件不存在而無法記錄資料表的識別碼。如果稍後建立資料表,就會與其他相依性資訊一起傳回該資料表的識別碼。

當受參考的實體依名稱顯示在參考實體的保存 SQL 運算式中時,會追蹤相依性資訊。以下列方式依名稱參考實體時,則會取得相依性資訊:

  • 藉由在 Transact-SQL模組的定義中使用以下任何陳述式:

    • 資料操作語言 (DML) 陳述式 (SELECT、INSERT、UPDATE、DELETE、MERGE)

    • EXECUTE

    • DECLARE

    • SET (搭配使用者自訂函數或使用者定義型別使用 SET 時。例如,DECLARE @var int; SET @var = dbo.udf1)。

    使用資料定義語言 (Data Definition Language,DDL) 陳述式 (例如,CREATE、ALTER 或 DROP),在 Transact-SQL 模組定義中參考的實體不會受到追蹤。

  • 藉由當 CREATE、ALTER 或 DROP TABLE 陳述式不在 Transact-SQL 模組中,而且受參考的實體為 Transact-SQL 使用者自訂函數、使用者定義型別,或在計算資料行中定義之 XML 結構描述集合、CHECK 條件約束,或 DEFAULT 條件約束時,使用這些陳述式。

跨資料庫與跨伺服器的相依性

當實體使用有效的三部分名稱參考另一個實體時,會建立跨資料庫相依性。當實體使用有效的四部分名稱參考另一個實體時,則會建立跨伺服器參考。只有在明確指定名稱時,才會記錄伺服器和資料庫的名稱。例如,指定為 MyServer.MyDB.MySchema.MyTable 時,會記錄伺服器和資料庫名稱,但是,指定為 MyServer..MySchema.MyTable 時,則只會記錄伺服器名稱。如需有關有效之多部分名稱的詳細資訊,請參閱<Transact-SQL 語法慣例 (Transact-SQL)>。

適用下列限制:

  • OPENROWSET、OPENQUERY 以及 OPENDATASOURCE 陳述式的跨伺服器相依性不會受到追蹤。

  • EXEC (' ') AT linked_server 陳述式的相依性不會受到追蹤。

下表提供受追蹤之跨伺服器和跨資料庫相依性,以及在系統目錄中記錄,並由 sys.sql_expression_dependencies (Transact-SQL) 報告之資訊的摘要。

模組中的 SQL 運算式

受到追蹤

受參考的伺服器名稱

受參考的資料庫名稱

受參考的結構描述名稱

受參考的實體名稱

SELECT * FROM s1.db2.sales.t1

s1

db2

sales

t1

SELECT * FROM db3..t1

 

db3

 

t1

EXEC db2.dbo.Proc1

 

db2

dbo

proc1

EXEC (' ') AT linked_srv1

 

 

 

 

EXEC linked_svr1.db2.sales.proc2

linked_svr1

db2

sales

proc2

相依性追蹤定序的效果

定序可決定排序和比較資料的規則。資料庫的定序用於識別資料庫中的實體相依性資訊。例如,如果預存程序參考使用區分大小寫定序之資料庫中的實體 Some_Table 和 SOME_TABLE,就會因為比較兩個名稱後指出這兩個是不同的實體,而記錄相依性資訊。但是,如果資料庫使用不區分大小寫的定序,只會記錄單一相依性。

若是跨伺服器和跨資料庫的相依性,參考物件所在伺服器的定序用於解析伺服器和資料庫的名稱。目前資料庫的定序則用於解析結構描述名稱和物件名稱。

請考慮下列的預存程序定義。如果在不區分大小寫之伺服器定序的 SQL Server 執行個體上,建立區分大小寫定序之資料庫中的預存程序,會針對實體 srv_referenced.db_referenced.dbo.p_referenced 和 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;

解析模稜兩可的參考

當參考可以在執行階段解析成使用者自訂函數、使用者定義型別 (UDT),或 xml 類型資料行的 xquery 參考時,表示該參考模糊不清。

請考慮下列的預存程序定義。

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

在建立預存程序時,不知道 Sales.GetOrder() 是 Sales 結構描述中,名為 GetOrder 的使用者自訂函數,還是參考名為 Sales、類型是 UDT 而且具有名為 GetOrder() 之方法的資料行。當參考模糊不清時,將 sys.sql_expression_dependencies 和 sys.dm_sql_referenced_entities 中的 is_ambiguous 資料行設定為 1,則可將相依性報告為模糊不清。系統會報告下列相依性資訊:

  • 預存程序及資料表之間的相依性。

  • 預存程序及使用者自訂函數之間的相依性。如果該函數存在,就會報告該函數的識別碼,否則,識別碼為 NULL。

  • 函數的相依性會標示為模糊不清。也就是說,is_ambiguous 是設定為 1。

  • 由於無法繫結資料行所參考的陳述式,因此,不會報告資料行層級相依性。

維護相依性

Database Engine 會同時維護結構描述繫結的相依性與非結構描述繫結的相依性。在影響相依性追蹤的任何作業期間,系統會自動重新整理這些相依性,例如從舊版 SQL Server 升級資料庫,或變更資料庫的定序時。