SQL 종속성 이해
SQL 종속성은 다른 엔터티에 종속된 엔터티를 만드는 SQL 식에 사용되는 이름 기준의 참조입니다. 정의에서 다른 엔터티를 참조하고 시스템 카탈로그에 해당 정의가 저장되어 있으면 이 엔터티를 참조 엔터티라고 합니다. 다른 엔터티에 의해 참조되는 엔터티는 참조된 엔터티라고 합니다. 데이터베이스 엔진에서 추적되는 종속성 유형에는 두 가지가 있습니다.
스키마 바운드 종속성
스키마 바운드 종속성은 참조 엔터티가 존재하는 한 참조된 엔터티가 삭제되거나 수정되지 않도록 방지하는 두 엔터티 간 관계입니다. 스키마 바운드 종속성은 WITH SCHEMABINDING 절을 사용하여 뷰 또는 사용자 정의 함수를 만들면 생성됩니다. 스키마 바운드 종속성은 테이블이 CHECK 또는 DEFAULT 제약 조건이나 계산 열의 정의에서 Transact-SQL 사용자 정의 함수, 사용자 정의 형식 또는 XML 스키마 컬렉션과 같은 다른 엔터티를 참조해도 생성됩니다. 두 부분으로 된 이름(schema_name.object_name)을 사용하여 개체를 지정하면 스키마 바운드 참조로 처리되지 않습니다.
비스키마 바운드 종속성
비스키마 바운드 종속성은 참조된 엔터티가 삭제되거나 수정되지 않도록 방지하지 않는 두 엔터티 간 관계입니다.
다음 그림에서는 SQL 종속성의 예를 보여 줍니다.
.gif "SQL 종속성 설명")
이 그림에는 프로시저 X와 프로시저 Y라는 두 개의 엔터티가 있습니다. 프로시저 X에는 프로시저 Y에 대한 이름 기준 참조가 있는 SQL 식이 있습니다. 프로시저 X는 참조 엔터티이고 프로시저 Y는 참조된 엔터티입니다. 프로시저 X는 프로시저 Y에 종속되므로 프로시저 Y가 없을 경우 런타임 오류가 발생하며 작업이 실패합니다. 그러나 프로시저 Y는 프로시저 X가 없어도 실패하지 않습니다.
다음 예에서는 저장 프로시저 X가 저장 프로시저 Y에 종속되는 방법을 보여 줍니다.
USE tempdb;
GO
CREATE PROCEDURE dbo.Y AS
SELECT * FROM sys.objects
GO
CREATE PROCEDURE dbo.X as
EXEC dbo.Y;
GO
Y에 대한 X의 종속성을 확인하려면 다음 쿼리를 실행합니다.
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보다 큰 번호가 있는 저장 프로시저는 참조 엔터티나 참조된 엔터티로 추적되지 않습니다.
종속성 정보 추적 방법
데이터베이스 엔진은 참조 엔터티가 생성, 변경 또는 삭제될 때 자동으로 종속성 정보를 추적하여 SQL Server 시스템 카탈로그에 이 정보를 기록합니다. 예를 들어 테이블을 참조하는 트리거를 만드는 경우 이러한 엔터티 간 종속성이 기록됩니다. 이후에 트리거를 삭제하면 종속성 정보가 시스템 카탈로그에서 제거됩니다.
종속성이 ID별로 추적되는 이전 버전의 SQL Server와 달리 이제 종속성은 이름 기준으로 추적됩니다. 즉, 참조 엔터티가 생성될 때 참조된 엔터티가 없더라도 데이터베이스 엔진이 두 엔터티 간 종속성 정보를 추적합니다. 이러한 상황은 지연된 이름 확인으로 인해 발생할 수 있습니다. 예를 들어 참조된 테이블이 데이터베이스에 없더라도 테이블을 참조하는 저장 프로시저를 만들 수 있습니다(실행할 수는 없음). 데이터베이스 엔진이 프로시저와 테이블 간 종속성을 기록하지만 개체가 아직 존재하지 않기 때문에 테이블의 ID를 기록할 수 없습니다. 나중에 테이블을 만들면 테이블의 ID가 다른 종속성 정보와 함께 반환됩니다.
참조된 엔터티가 참조 엔터티의 지속형 SQL 식에서 이름 기준으로 나타나면 종속성 정보가 추적됩니다. 종속성 정보는 엔터티를 다음과 같은 방법으로 이름을 참조하면 얻을 수 있습니다.
Transact-SQL모듈의 정의에서 다음 문 중 하나 사용
DML(데이터 조작 언어) 문(SELECT, INSERT, UPDATE, DELETE, MERGE)
EXECUTE
DECLARE
SET(SET을 사용자 정의 함수 또는 사용자 정의 형식과 함께 사용. 예: DECLARE @var int; SET @var = dbo.udf1)
CREATE, ALTER 또는 DROP과 같은 DDL(데이터 정의 언어) 문을 사용하여 Transact-SQL 모듈의 정의에서 참조된 엔터티는 추적되지 않습니다.
문이 Transact-SQL 모듈에 있지 않고 참조된 엔터티가 계산 열, CHECK 제약 조건 또는 DEFAULT 제약 조건에 정의된 Transact-SQL 사용자 정의 함수, 사용자 정의 형식 또는 XML 스키마 컬렉션인 경우 CREATE, ALTER 또는 DROP TABLE 문 사용
데이터베이스 간 및 서버 간 종속성
데이터베이스 간 종속성은 엔터티가 세 부분으로 된 올바른 이름을 사용하여 다른 엔터티를 참조할 때 생성됩니다. 서버 간 참조는 엔터티가 네 부분으로 된 올바른 이름을 사용하여 다른 엔터티를 참조할 때 생성됩니다. 서버 및 데이터베이스의 이름은 해당 이름을 명시적으로 지정할 경우에만 기록됩니다. 예를 들어 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라는 사용자 정의 함수에 대한 참조인지, 아니면 GetOrder()라는 메서드를 사용하는 UDT 유형의 Sales라는 열에 대한 참조인지는 알 수 없습니다. 참조가 모호한 경우 sys.sql_expression_dependencies 및 sys.dm_sql_referenced_entities의 is_ambiguous 열을 1로 설정하여 종속성이 모호한 것으로 보고됩니다. 다음과 같은 종속성 정보가 보고됩니다.
저장 프로시저와 테이블 간 종속성
저장 프로시저와 사용자 정의 함수 간 종속성. 해당 함수가 있으면 함수의 ID가 보고되고 그렇지 않으면 ID가 NULL입니다.
함수에 대한 종속성이 모호한 것으로 표시됩니다. 즉, is_ambiguous가 1로 설정됩니다.
열이 참조되는 문을 바인딩할 수 없으므로 열 수준 종속성이 보고되지 않습니다.
종속성 유지
데이터베이스 엔진은 스키마 바운드 종속성과 비스키마 바운드 종속성을 모두 유지합니다. 이러한 종속성은 이전 버전의 SQL Server에서 데이터베이스를 업그레이드하거나 데이터베이스의 데이터 정렬을 변경하는 경우 등에서와 같이 종속성 추적에 영향을 주는 작업을 수행하는 동안 자동으로 새로 고쳐집니다.
변경 내역
업데이트된 내용 |
|---|
주의 섹션에서 "열 종속성은 CLR 테이블 반환 함수에 대해 추적되지 않습니다."라는 문장을 제거했습니다. |