Decimal 데이터를 가변 길이로 저장
decimal 및 numeric 데이터 형식은 일반적으로 디스크에 고정 길이 데이터로 저장됩니다. numeric 데이터 형식은 기능상 decimal 데이터 형식과 같습니다. SQL Server 2005 SP2(서비스 팩 2) 이상 버전에서는 VarDecimal 저장소 형식을 사용하여 decimal 및 numeric 데이터 형식을 가변 길이 열로 저장할 수 있습니다. VarDecimal 저장소 형식은 SQL Server Enterprise Edition, Developer Edition 및 Evaluation Edition에서만 사용할 수 있습니다.
[!참고]
Microsoft SQL Server의 이후 버전에서는 이 기능이 제거됩니다. 새 개발 작업에서는 이 기능을 사용하지 않도록 하고, 현재 이 기능을 사용하는 응용 프로그램은 수정하십시오. 대신 ROW 및 PAGE 압축을 사용하십시오. 자세한 내용은 압축된 테이블 및 인덱스 만들기를 참조하십시오.
[!참고]
VarDecimal은 데이터 형식이 아니라 저장소 형식입니다.
VarDecimal 저장소 형식을 사용하면 데이터의 저장소 크기가 훨씬 줄어들지만 약간의 CPU 오버헤드가 추가됩니다. VarDecimal 저장소 형식은 테이블 수준에서 적용되므로 테이블의 일부 decimal 열은 VarDecimal 저장소 형식으로 저장하고 다른 열은 이 형식으로 저장하지 않도록 설정할 수 없습니다. decimal 데이터는 정확한 데이터 형식으로 유지됩니다.
테이블에 VarDecimal 저장소 형식이 설정되어 있으면 decimal 데이터는 VarDecimal 저장소 형식으로 데이터, 인덱스 및 로그 페이지에 저장됩니다. 저장소 형식 변경은 오프라인에서 수행할 수 있습니다. 수정 중인 테이블은 배타적으로 잠기므로 변경 중에는 읽거나 쓸 수 없습니다.
VarDecimal 저장소 형식 구현
열의 전체 자릿수(1에서 38 사이)에 따라 decimal 값의 저장소는 5바이트에서 17바이트 사이를 사용합니다. 테이블에서 VarDecimal 저장소 형식을 사용하지 않는 경우 이 테이블의 모든 항목은 행 값이 0 또는 NULL이거나 숫자 3과 같이 더 작은 바이트 수로 표현될 수 있는 값인 경우에도 정의된 각 Decimal 열에 대해 동일한 수의 바이트를 사용합니다. 테이블이 VarDecimal 저장소 형식으로 저장된 경우 각 행의 decimal 열은 제공된 숫자를 포함하는 데 필요한 공간에 2바이트의 오버헤드를 더한 공간만 사용합니다. 결과는 항상 5바이트에서 20바이트 사이입니다. 여기에는 값에 대한 오프셋을 저장하기 위한 2바이트 오버헤드가 포함됩니다. 그러나 null 값과 0은 특별히 처리되고 2바이트만 사용합니다.
테이블에 가변 길이 열이 없는 경우 가변 길이 열 수를 저장하기 위해 행당 2바이트의 추가 오버헤드가 있습니다. 테이블에 가변 길이 열이 하나 이상 있으면 추가 오버헤드가 없습니다.
다음 표에서는 Decimal 데이터를 일반 고정 형식으로 저장하는 데 필요한 바이트 수 및 Decimal 데이터를 VarDecimal 저장소 형식으로 저장하는 데 필요한 최대 바이트 수를 보여 줍니다. 테이블이 고정 형식으로 저장된 경우 나열된 값이 모든 행에 사용됩니다. 테이블이 VarDecimal 저장소 형식으로 저장된 경우 대부분의 값에 나열된 바이트 수보다 적은 공간이 필요합니다.
열의 전체 자릿수 |
원래 고정 Decimal 크기(바이트) |
최대 VarDecimal 데이터 영역(바이트) |
오프셋을 저장하기 위한 오버헤드(바이트) |
사용되는 최대 VarDecimal 저장소(바이트) |
|---|---|---|---|---|
1-3 |
5 |
3 |
2 |
5 |
4-6 |
5 |
4 |
2 |
6 |
7-9 |
5 |
5 |
2 |
7 |
10-12 |
9 |
6 |
2 |
8 |
13-15 |
9 |
8 |
2 |
10 |
16-18 |
9 |
9 |
2 |
11 |
19 |
9 |
10 |
2 |
12 |
20-21 |
13 |
10 |
2 |
12 |
22-24 |
13 |
11 |
2 |
13 |
25-27 |
13 |
13 |
2 |
15 |
28 |
13 |
14 |
2 |
16 |
29-30 |
17 |
14 |
2 |
16 |
31-33 |
17 |
15 |
2 |
17 |
34-36 |
17 |
16 |
2 |
18 |
37-38 |
17 |
18 |
2 |
20 |
VarDecimal 저장소 형식 사용
다음 문제를 해결하려는 경우 VarDecimal 저장소 형식을 사용할 수 있습니다.
디스크 공간이 부족합니다.
디스크 액세스(I/O)에 병목 상태가 있어 시스템 성능을 저하시킵니다.
많은 행에 0 또는 정수 값이 포함된 decimal 열이 있는 데이터 웨어하우스의 테이블과 같이 대부분의 값이 작거나 NULL 또는 0인 경우에도 일부 데이터를 위해 높은 수준의 전체 자릿수가 있어야 합니다.
테이블의 저장소 형식을 변경하려면 테이블 저장소(클러스터형 인덱스 또는 힙)를 다시 작성해야 합니다. 테이블의 클러스터형 인덱스에 decimal 열이 있는 경우 비클러스터형 인덱스에 클러스터형 키 값이 포함되므로 비클러스터형 인덱스도 모두 다시 작성해야 합니다. 클러스터형 인덱스에 decimal 열이 없으면 비클러스터형 인덱스는 decimal 열이 포함된 경우에만 다시 작성합니다. 테이블이 힙인 경우, 즉 테이블에 클러스터형 인덱스가 없는 경우 힙의 새 행 위치를 가리키도록 모든 비클러스터형 인덱스를 다시 작성해야 합니다.
테이블을 다시 작성하여 VarDecimal 저장소 형식을 설정하거나 해제하려면 원래 테이블의 총 저장 공간보다 두 배 이상 필요할 수 있습니다. 테이블에 decimal 또는 numeric 열이 없는 경우 VarDecimal 저장소 형식을 설정할 때 메타데이터 작업만 수행됩니다. 테이블과 인덱스를 다시 작성하는 경우 많은 로그 작업이 필요합니다.
VarDecimal 저장소 형식은 읽기 전용 테이블과 읽기/쓰기 테이블에 모두 사용할 수 있습니다. 행에 액세스할 때마다 행의 저장소 형식을 변환하는 데 필요한 추가 CPU 사용을 고려하여 저장소 절약 수준을 조정해야 합니다. 또한 VarDecimal 저장소 형식을 사용하는 테이블에 쓰는 경우 페이지 분할 수가 증가하기 때문에 성능이 느려질 수 있습니다.
VarDecimal 저장소 형식의 제한 사항
다음과 같은 제한 사항이 있습니다.
SQL Server 2005 SP2 이상 버전이 필요합니다.
VarDecimal 저장소 형식은 master, model, msdb, tempdb 또는 distribution과 같은 시스템 데이터베이스에서 사용할 수 없습니다. 쿼리에서 VarDecimal 저장소 형식으로 저장된 데이터를 정렬하면 tempdb의 데이터는 고정 Decimal 상태로 정렬됩니다. 일반적으로 원본 데이터베이스의 VarDecimal 저장소 형식 원본 테이블에서 사용하는 공간보다 tempdb의 데이터에 더 많은 공간이 필요합니다.
VarDecimal 저장소 형식은 뷰, 인덱싱된 뷰, XML 인덱스 및 전체 텍스트 인덱스에 적용할 수 없습니다. 그러나 이러한 개체의 기반이 되는 테이블은 VarDecimal 저장소 형식을 사용할 수 있습니다.
메타데이터 및 알림 테이블 같은 내부 테이블은 VarDecimal 저장소 형식을 사용할 수 없습니다.
테이블 반환 함수는 VarDecimal 저장소 형식을 사용할 수 없습니다.
또한 VarDecimal 저장소 형식 테이블에 저장된 numeric 열은 암호화할 수 없습니다.
다른 유형의 파티션(즉, 고정 Decimal 형식 및 VarDecimal 저장소 형식 파티션)은 지원되지 않습니다.
Transact-SQL SELECT … INTO… 구문을 사용하여 VarDecimal 저장소 형식 테이블에서 만든 새 테이블은 VarDecimal 저장소 형식을 상속하지 않습니다.
데이터베이스 미러링용으로 설정된 데이터베이스의 VarDecimal 저장소 형식 상태는 변경할 수 없습니다. 데이터베이스에 VarDecimal 저장소 형식을 설정하려면 데이터베이스 미러링을 제거해야 합니다. 그러나 개별 테이블에 VarDecimal 저장소 형식을 설정하거나 해제할 때는 데이터베이스 미러링을 제거하지 않아도 됩니다.
SQL Server에서 모든 업데이트가 성공할 것이며 항상 테이블을 고정 Decimal 형식으로 되돌릴 수 있다고 보장할 수 있어야 합니다. 따라서 추가 오버헤드로 인해 기존 행이 8060바이트를 초과하거나 기존 인덱스 값이 900바이트를 초과하는 경우 테이블을 VarDecimal 저장소 형식으로 변경할 수 없습니다.
[!참고]
VarDecimal 저장소 형식은 모든 변수 열이 최대 크기일 경우 SQL Server를 사용하여 8060바이트를 초과하는 행을 만들 수 있다는 점에서 변수 텍스트(varchar) 저장소와 다릅니다. SQL Server는 텍스트 데이터를 삽입하거나 업데이트할 때 8060바이트 제한을 적용합니다. SQL Server에서는 행에 대한 8060바이트 제한을 초과하는 Decimal 열 집합을 만들 수 없습니다. 8060바이트 제한은 테이블 형식을 VarDecimal 저장소로 변경할 때 적용됩니다.
데이터베이스 복사 마법사의 분리/연결 방법을 사용하여 데이터베이스를 전송하는 경우 대상 데이터베이스 엔진이 SQL Server 2005 SP2 이상이 아니면 연결 작업이 실패합니다. SQL Server Management Objects 방법은 VarDecimal 저장소 형식을 사용하지 않고 새 데이터베이스 및 테이블을 만듭니다. 데이터베이스 엔진이 SQL Server 2005 SP2 이상이면 전송 후 데이터베이스와 테이블을 VarDecimal 형식으로 변경할 수 있습니다.
백업 및 복구, 데이터베이스 미러링, sp_attach_db 및 로그 전달
백업 및 복구, 데이터베이스 미러링, sp_attach_db 및 로그 전달은 VarDecimal 저장소 형식에서 제대로 작동합니다. 그러나 VarDecimal 저장소 형식을 사용하는 데이터베이스를 포함하려면 각 SQL Server 인스턴스를 SQL Server 2005 SP2 이상으로 업그레이드해야 합니다. 예를 들어 VarDecimal 저장소 형식이 설정된 데이터베이스의 로그 백업은 이 저장소 형식이 설정되지 않은 데이터베이스로 복원할 수 없고, VarDecimal 저장소 형식이 설정된 데이터베이스는 이 저장소 형식이 설정되지 않은 데이터베이스로 미러링할 수 없으며, SQL Server 2005 SP2에서 VarDecimal 저장소 형식이 설정된 데이터베이스를 이전 버전의 SQL Server에 연결할 수 없습니다. VarDecimal 저장소 형식이 설정된 데이터베이스의 전체 백업을 VarDecimal 저장소 형식이 설정되지 않은 데이터베이스로 복원하면 데이터베이스가 VarDecimal 저장소 형식으로 설정됩니다.
테이블이 VarDecimal 저장소 형식으로 변경된 경우 로그 백업 체인이 계속 유효하며 마지막 전체 백업과 유효한 로그 체인을 적용하여 데이터베이스를 복원할 수 있습니다. 유효하지 않은 백업 생성을 방지하려면 테이블을 수정하여 VarDecimal 저장소 형식을 제거하기 전에 데이터베이스를 단순 복구 모델로 변경해야 합니다. 테이블의 VarDecimal 저장소 형식을 제거한 후 전체 데이터베이스 백업을 만들어야 합니다.
데이터베이스 미러링에 VarDecimal 저장소 형식 사용
다음 절차에서는 데이터베이스 미러링에 VarDecimal 저장소 형식을 사용하는 단계를 제공합니다.
데이터베이스 미러링에 VarDecimal 저장소 형식을 사용하려면
주 파트너 인스턴스와 미러 파트너 인스턴스를 모두 SQL Server 2005 SP2 이상으로 업그레이드합니다.
현재 데이터베이스 미러링을 사용하는 경우 데이터베이스 미러링을 제거하고 미러 파트너를 삭제합니다. 자세한 내용은 방법: 데이터베이스 미러링 제거(Transact-SQL)를 참조하십시오.
주 데이터베이스에 VarDecimal 저장소 형식을 설정하고(데이터베이스가 SQL Server 2005에 있는 경우) 주 데이터베이스가 전체 복구 모델을 사용하는지 확인합니다.
주 데이터베이스에서 전체 백업과 로그 백업을 사용하여 데이터베이스 미러링을 설정합니다. 자세한 내용은 방법: Windows 인증을 사용하여 데이터베이스 미러링 세션 구성(Transact-SQL)을 참조하십시오.
VarDecimal 저장소 형식을 사용하도록 개별 테이블을 수정합니다.
[!참고]
개별 테이블의 저장소 형식을 변경하기 위해 데이터베이스 미러링을 제거할 필요는 없습니다.
VarDecimal 저장소 형식을 제거하려면
주 데이터베이스의 테이블을 수정하여 VarDecimal 저장소 형식을 제거합니다.
데이터베이스 미러링을 제거합니다.
주 데이터베이스를 단순 복구 모드로 설정합니다. 이 경우 로그 체인이 끊어집니다.
데이터베이스가 SQL Server 2005에 있으면 주 데이터베이스에서 VarDecimal 저장소 형식을 해제합니다.
미러 파트너 데이터베이스를 삭제합니다.
주 데이터베이스를 다시 전체 복구 모드로 변경합니다.
주 데이터베이스를 백업하고 데이터베이스 미러링을 다시 설정합니다.
VarDecimal 저장소 형식이 복제 작업에 미치는 영향
복제는 VarDecimal 저장소 형식을 사용하는 테이블에서도 일반적인 방식으로 작동하지만 다음 사항을 고려해야 합니다.
VarDecimal 저장소 형식으로 저장된 decimal 데이터 형식은 복제 중에 전송을 위해 고정 Decimal 형식으로 변환됩니다. 배포 데이터베이스에는 VarDecimal 저장소 형식을 설정할 수 없습니다. 따라서 배포 데이터베이스의 복제 테이블에 데이터를 저장할 때는 데이터가 VarDecimal 저장소 형식으로 저장되지 않습니다. 구독자에서 로그 레코드는 일반적으로 적용됩니다.
VarDecimal 저장소 형식 테이블은 고정 Decimal 형식 테이블로 복제될 수 있고 고정 Decimal 형식 테이블은 VarDecimal 저장소 형식 테이블로 복제될 수 있습니다.
새 구독과 관련된 테이블 생성 프로세스에서는 VarDecimal 저장소 형식을 사용하여 테이블을 만들지 않습니다. 이 경우 데이터베이스 엔진의 서비스 팩 수준이나 구독하는 데이터베이스의 VarDecimal 저장소 형식 설정 상태에 관계없이 복제에 성공할 수 있습니다. 테이블이 생성된 후 구독자에서 또는 생성 스크립트가 적용되기 전에 수정하여 구독하는 테이블을 VarDecimal 저장소 형식으로 설정할 수 있습니다.
다음 표에서는 다양한 구독자의 스크립트 요구 사항에 대해 설명합니다.
구독자 |
스크립트 |
|---|---|
SQL Server 2000 또는 SQL Server 버전 7.0 |
테이블 생성 스크립트를 수정하지 않고 사용할 수 있습니다. |
SQL Server 2005 - 데이터베이스가 VarDecimal 저장소 형식으로 표시되지 않는 경우 |
테이블 생성 스크립트를 수정하지 않고 사용할 수 있습니다. |
SQL Server 2005 - 데이터베이스가 VarDecimal 저장소 형식으로 표시되지만 구독자 테이블에 VarDecimal 저장소 형식을 설정하지 않으려는 경우 |
테이블 생성 스크립트를 수정하지 않고 사용할 수 있습니다. |
SQL Server 2005 - 데이터베이스가 VarDecimal 저장소 형식으로 표시되며 SQL Server 2005 구독자 테이블에 VarDecimal 저장소 형식을 설정하려는 경우 |
테이블 생성 스크립트를 수정하여 데이터베이스에 VarDecimal 저장소 형식을 설정하고 테이블의 VarDecimal 저장소 형식을 설정할 수 있습니다. 또는 뒤에 나오는 "VarDecimal 저장소 형식 설정" 섹션에 설명된 저장 프로시저를 사용하여 구독자 데이터베이스 및 테이블을 설정할 수 있습니다. |
기타 고려 사항
다음은 VarDecimal 저장소 형식을 사용하여 작업할 때 추가로 고려할 사항입니다.
VarDecimal 저장소 형식은 대량 가져오기 및 내보내기(bcp) 작업에 영향을 주지 않습니다.
DATALENGTH 함수는 VarDecimal 저장소 형식을 검색하지 않으며 고정 Decimal 형식으로 저장될 바이트 수를 반환합니다.
드물긴 하지만 VarDecimal 저장소 형식을 설정하면 SQL Server에서 고정 Decimal 데이터에 최적화된 쿼리 계획을 사용하지 못할 수 있습니다.
VarDecimal 저장소 형식은 모든 데이터베이스 호환성 수준에서 사용할 수 있습니다.
sp_tableoption을 실행할 때 테이블에 decimal 또는 numeric 데이터 형식의 열이 없으면 테이블에서 VarDecimal 저장소 형식을 사용하고 있음을 나타내기 위해 테이블 메타데이터가 변경됩니다. 나중에 새 decimal 열을 추가하면 VarDecimal 저장소 형식으로 저장됩니다. VarDecimal 저장소 형식을 사용하는 테이블에서 열을 추가하거나 제거하기 위해 특별한 방법을 사용할 필요는 없습니다.
VarDecimal 저장소 형식 설정
VarDecimal 저장소 형식을 설정하거나 변경하려면 다음 사용 권한이 필요합니다.
데이터베이스에 VarDecimal 저장소 형식을 설정하려면 서버에 대한 ALTER DATABASE 권한이 필요합니다.
테이블을 VarDecimal 저장소 형식으로 변경하려면 테이블에 대한 ALTER 권한이 필요합니다.
VarDecimal 저장소 형식을 설정하기 전에 먼저 VarDecimal 저장소 형식이 설정될 때 테이블이 축소되는지 확인해야 합니다. 정의된 열 전체 자릿수가 대부분의 행에 필요한 경우 VarDecimal 저장소 형식과 관련된 오버헤드가 절약되는 양보다 많을 수 있으므로 테이블이 더 커질 수 있습니다. 테이블을 수정하기 전에 행 크기 축소를 추정하려면 sp_estimated_rowsize_reduction_for_vardecimal 저장 프로시저를 사용합니다. 테이블의 저장소 형식을 변경하려면 데이터베이스를 VarDecimal 저장소 형식으로 설정한 다음 개별 테이블을 VarDecimal 저장소 형식으로 설정합니다. SQL Server 2008 데이터베이스는 VarDecimal 저장소 형식으로 설정할 필요가 없습니다.
SQL Server 2008 데이터베이스에서 저장 프로시저나 SQL Server Management Studio를 사용하여 decimal 데이터 형식에 대해 VarDecimal 저장소 형식을 설정할 수 있습니다.
sp_db_vardecimal_storage_format을 실행하여 데이터베이스에 VarDecimal 저장소 형식을 설정한 다음(SQL Server 인스턴스가 SQL Server 2005, SP 2인 경우) sp_tableoption을 실행하여 해당 테이블에 VarDecimal 저장소 형식을 설정합니다.
Management Studio에서 데이터베이스 속성 옵션 페이지를 사용하여 데이터베이스에 VarDecimal 저장소 형식을 설정합니다. 테이블을 VarDecimal 저장소 형식으로 변경하려면 sp_tableoption을 사용해야 합니다.
[!참고]
SQL Server 2008부터는 모든 데이터베이스에 Vardecimal 저장소 형식을 사용할 수 있습니다.
VarDecimal 저장소 형식 테이블 식별
VarDecimal 저장소 형식을 사용하는 데이터베이스 테이블을 확인하려면 OBJECTPROPERTY 함수를 사용하여 TableHasVarDecimalStorageFormat 속성을 검색합니다.
다음 예에서는 Production.WorkOrderRouting 테이블에서 VarDecimal 저장소 형식을 사용하는 경우 1을 반환하고 그렇지 않으면 0을 반환합니다.
USE AdventureWorks2008R2 ;
GO
SELECT OBJECTPROPERTY(OBJECT_ID('Production.WorkOrderRouting'),
'TableHasVarDecimalStorageFormat') ;
GO
다음 예에서는 AdventureWorks2008R2 데이터베이스에서 VarDecimal 저장소 형식을 사용하는 모든 테이블을 검색합니다.
USE AdventureWorks2008R2 ;
GO
SELECT name, object_id, type_desc
FROM sys.objects
WHERE OBJECTPROPERTY(object_id,
N'TableHasVarDecimalStorageFormat') = 1 ;
GO
VarDecimal 저장소 형식 제거 문제
테이블에서 VarDecimal 저장소 형식을 제거하려면 고정 Decimal 형식으로 테이블을 다시 작성해야 합니다. 이 경우 테이블에 사용되는 디스크 공간이 훨씬 늘어날 수 있습니다. 디스크 공간이 충분하지 않으면 작업이 실패합니다. 따라서 VarDecimal 저장소 형식을 해제하려면 SQL Server에서 충분한 디스크 공간을 사용할 수 있는지 확인해야 합니다. 확장 작업을 수행할 때도 VarDecimal 형식과 일반 형식 둘 다로 데이터를 저장하기 위해 임시 공간이 필요합니다. 확장된 테이블이 확장 후 사용 가능한 디스크 공간에 들어가지만 임시 디스크 공간이 부족하여 확장되지 않는 경우 테이블 행을 확장되지 않은 새 테이블에 복사하여 증분 방식으로 데이터를 확장할 수 있습니다.
데이터베이스가 수정된 직후 데이터베이스에서 VarDecimal 저장소 형식을 제거하려면 데이터베이스를 삭제한 다음 VarDecimal 저장소 형식 설정 전에 수행된 데이터베이스 백업에서 데이터베이스를 복원합니다.
SQL Server 2005 Enterprise, Developer 또는 Evaluation Edition에서 다른 버전이나 이전 버전의 SQL Server로 데이터베이스를 이동하는 경우 먼저 필요한 버전 중 하나를 사용하여 데이터베이스를 열고 VarDecimal 저장소 형식을 제거한 다음 데이터베이스를 마이그레이션해야 합니다. VarDecimal 저장소 형식이 포함된 데이터베이스를 부적합한 서버에 연결하면 실패합니다.