[SQL2005] 스키마(Schema) 이해

SQL 서버 2005에서 데이터베이스에 테이블, 뷰, 저장프로시저와 같은 개체를 만들면 스키마(Schema)안에 만들어 집니다. SQL 서버 2000과 달라진 부분이기 때문에 SQL 서버 2000 사용자 분들이 이것에 대해 많이 혼란스러워합니다. 스키마에 대해 살펴보도록 하겠습니다.

1. 스키마란

스키마는 데이터베이스 개체에 대한 네임스페이스(Namespace) 입니다. 즉 데이터베이스 개체가 갖는 고유한 이름을 결정지어주게 됩니다. 스키마는 데이터베이스 안에서 고유해야 하기 때문에 데이터베이스의 모든 개체는 다음과 같은 형식의 고유한 이름을 갖게 됩니다.

Server.Database.Schema.Object

SQL 서버 2000에서는 Server.Database.Owner.Object와 같은 형식으로 이름이 결정되었습니다. 하지만 SQL 서버 2005에서는 개체의 소유자(Owner)가 아닌 스키마를 사용해 개체의 이름에서 소유자 부분을 분리시켰습니다. 이렇게 함으로써 얻어지는 이점이 있습니다.

1) 개체의 소유자와는 상관없이 스키마를 이용해 개체를 그룹화 하여 관리에 효율을 꾀할 수 있습니다.
2) 권한 관리의 경우 개체 개별 적으로 권한을 허용하지 않고 스키마에 권한을 허용하면 스키마에 포함된 모든 개체에 대해 자동으로 권한을 줄 수 있어 권한 관리가 효율적입니다.
3) 소유자 계정을 삭제하기 위해 소유자가 소유한 개테의 소유자를 변경하거나 개체를 삭제할 필요가 없습니다. SQL 서버 2000에서는 개체를 소유한 소유자의 계정을 삭제 할 수 없었습니다.

SQL 서버 2005의 예제 데이터베이스인 AdventureWorks 데이터베이스는 다음과 같은 스키마를 가지고 있습니다.

- HumanResources
- Person
- Production
- Purchasing
- Sales

Management Studio에서 AdventureWorks 데이터베이스의 테이블 부분을 확장하면 위 스키마로 시작된 테이블들을 확인 할 수 있습니다.

2. dbo 스키마

dbo 스키마는 모든 데이터베이스에 포함되어 있는 특수한 스키마입니다. 데이터베이스의 사용자 계정은 기본 스키마를 갖게 됩니다. 그리고 이 사용자가 테이블을 만들면 그 테이블은 사용자의 기본 스키마에 포함되게 됩니다. 예를 들어 기본 스키마가 Sales 인 사용자 계정이 Customer 테이블을 만들면 Sales.Customer와 같은 이름을 갖게 됩니다.

dbo 스키마는 별도의 기본 스키마가 지정되지 않은 사용자 계정의 기본 스키마가 됩니다. 만일 기본 스키마가 별도로 지정되지 않은 사용자 계정이 Orders 테이블을 만들면 기 테이블은 dbo.Orders 라는 이름을 갖게 됩니다.

3. 스키마 만들기

스키마는 SQL Server Management Studio 또는 CREATE SCHEMA 문을 이용해 만들 수 있습니다.

USE AdventureWorks GO CREATE SCHEMA Sales GO

위 예제 에서는 AdventureWorks 데이터베이스에 Sales 스키마를 만들고 있습니다. 만일 AdventureWorks 데이터베이스에 Jane 이라는 사용자 계정이 있다고 가정한다면 다음과 같이 같이 Jane의 기본 스키마를 Sales로 지정 할 수 있습니다.

USE AdventureWorks GO ALTER USER Jane WITH DEFAULT_SCHEMA = Sales GO

이러한 작업은 Management Studio에서도 가능합니다.

4. 스키마 삭제

스키마 삭제는 SQL Server Management Studio 또는 DROP SCHEMA 문을 이용하여 할 수 있습니다.

USE AdventureWorks GO DROP SCHEMA Sales GO

5. 개체 이름 확인 과정

데이터베이스에 여러 개의 스키마가 있다면 개체 이름 확인은 단순하지 않습니다. 하지만 기본 스키마에 대해 정확히 파악하고 있다면 이 부분을 쉽게 이해 할 수 있습니다. 만일 데이터베이스에 다음과 같이 Person, Sales, dbo 스키마가 있다고 가정하겠습니다.

그리고 Person 스키마에는 Contact 테이블이 dbo 스키마에는 ErrorLog 테이블이 존재하고 있습니다. 물론 Sales 스키마에도 몇몇 테이블이 존재하고 있습니다. 이 상황에서 다음의 예에서 어떻게 개체를 찾게 되는지 확인을 해보도록 하겠습니다.

[예제1] 기본 스키마가 Person인 사용자 Lance가 다음 쿼리 문을 수행

SELECT * FROM Contact

개체 이름에 스키마가 생략되어 있으면 SQL 서버는 쿼리 문을 수행하는 사용자의 기본 스키마에서 해당 테이블을 찾게 됩니다. Lance의 기본 스키마가 Person이므로 위 쿼리 문 수행을 위해 SQL 서버는 Person.Contact 테이블을 찾게 됩니다. 그리고 Person.Contact 테이블에 존재하므로 결과를 리턴해 줍니다.

[예제2] 기본 스키마가 Sales 인 사용자 Anders가 다음 세 개의 쿼리 문을 수행

SELECT * FROM Person.Contact

Anders의 기본 스키마가 Sales 이지만 쿼리 문에 Person.Contact이라고 스키마가 지정되어 개체 이름을 사용했으므로 SQL 서버는 Person.Contact 테이블을 찾게 됩니다. 그리고 Person.Contact 테이블이 존재하므로 결과를 리턴해 줍니다.

SELECT * FROM ErrorLog

이 경우 개체 이름에 스키마가 생략되어 있으므로 SQL 서버는 쿼리 문을 수행하는 사용자의 기본 스키마에서 해당 테이블을 찾게 됩니다. Anders의 기본 스키마가 Sales이므로 Sales.ErrorLog 테이블을 찾게 됩니다. 하지만 Sales 스키마에는 ErrorLog 테이블이 없습니다. SQL 서버는 여기서 포기 하지 않고 dbo 스키마에 ErrorLog 테이블이 있는지 찾습니다. 즉 dbo.ErrorLog 테이블을 찾습니다. 그리고 dbo.ErrorLog 테이블이 있으므로 그 결과를 리턴해 줍니다.

SELECT * FROM Contact

역시 개체 이름에 스키마가 생략되어 있으므로 SQL 서버는 쿼리 문을 수행하는 사용자의 기본 스키마에서 해당 테이블을 찾게 됩니다. Anders의 기본 스키마가 Sales이므로 Sales.Contact 테이블을 찾습니다. 하지만 Sales 스키마에는 Contact 테이블이 없으므로 Sales.Contact 테이블 찾기는 실패합니다. SQL 서버는 이어서 dbo.Contact 테이블을 찾습니다. dbo 스키마에도 Contact 테이블이 없으므로 찾기에 실패합니다. 결국 이 쿼리 문은 개체를 찾지 못해 에러를 발생시킵니다.

정리 하면, 사용자의 기본 스키마에 속한 개체나 dbo 스키마에 속한 개체는 스키마를 생략해도 개체를 찾을 수 있으나, 그 외의 스키마에 속한 개체를 찾기 위해서는 개체 이름 앞에 스키마를 정확히 포함시켜주어야 한다는 것입니다. 가장 좋은 방법은 개체 이름 앞에 항상 스키마를 포함시키는 습관을 갖는 것입니다.

6. 정리

일반적으로 스키마라고 하면 테이블의 구조를 이야기 합니다. 하지만 SQL 서버 2005에서는 다른 의미를 가지고 있습니다. 스키마는 관리 및 보안을 위해 유익한 기능을 제공하고 있으며, SQL 서버 2005를 사용하려면 꼭 이해하고 있어야 하는 개체입니다. 본 강좌에서 다 다루지 못한 부분은 온라인 설명서를 통해 명확히 이해 하시는게 좋을것 같습니다.

[출처] 디비누리