sp_tableoption(Transact-SQL)

사용자 정의 테이블의 옵션 값을 설정합니다. sp_tableoption을 사용하여 varchar(max), nvarchar(max), varbinary(max), xml, text, ntext, image 또는 큰 사용자 정의 형식 열이 있는 테이블의 행 내부 동작을 제어할 수 있습니다.

중요 정보중요

SQL Server의 이후 버전에서는 text in row 기능이 제거됩니다. 큰 값 데이터를 저장하려면 varchar(max), nvarchar(max) 및 varbinary(max) 데이터 형식을 사용하는 것이 좋습니다.

항목 링크 아이콘 Transact-SQL 구문 표기 규칙

구문

sp_tableoption [ @TableNamePattern = ] 'table' 
     , [ @OptionName = ] 'option_name' 
     ,[ @OptionValue =] 'value'

인수

  • [ @TableNamePattern =] 'table'
    사용자 정의 데이터베이스 테이블의 정규화 또는 비정규화된 이름입니다. 데이터베이스 이름을 포함한 정규화된 테이블 이름인 경우 데이터베이스 이름이 반드시 현재 데이터베이스의 이름이어야 합니다. 여러 테이블에 대한 테이블 옵션을 동시에 설정할 수 없습니다. table는 nvarchar(776)이며 기본값은 없습니다.

  • [ @OptionName = ] 'option_name'
    테이블 옵션 이름입니다. option_name은 varchar(35)이며 기본값 NULL이 없습니다. option_name은 다음 값 중 하나일 수 있습니다.

    설명

    table lock on bulk load

    기본값이 해제되면 사용자 정의 테이블에 대량 로드 처리를 수행하여 행 잠금을 얻습니다. 기본값이 설정되면 사용자 정의 테이블에 대량 로드 처리를 수행하여 대량 업데이트 잠금을 얻습니다.

    insert row lock

    더 이상 지원되지 않습니다.

    SQL Server의 잠금 전략은 잠금 수준을 페이지 또는 테이블 잠금 수준으로 올릴 수 있는 행 잠금입니다. 이 옵션은 SQL Server의 잠금 동작에 영향을 주지 않으며 기존 스크립트 및 프로시저와의 호환성을 위해 포함됩니다.

    text in row

    OFF 또는 0(해제, 기본값)이면 현재 동작을 바꾸지 않으며 행에 BLOB이 없습니다.

    이 값이 지정되고 @OptionValue가 ON(설정) 또는 24에서 7000까지의 정수 값이면 새 text, ntext 또는 image 문자열이 데이터 행에 직접 저장됩니다. 기존의 모든 BLOB(Binary Large Object) 데이터, 예를 들어 text, ntext 또는 image 데이터는 BLOB 값이 업데이트될 때 text in row 형식으로 변경됩니다. 자세한 내용은 설명 부분을 참조하십시오.

    large value types out of row

    테이블의 1 = varchar(max), nvarchar(max), varbinary(max), xml 및 큰 UDT(사용자 정의 형식) 열은 루트에 대한 16바이트 포인터와 함께 행 외부에 저장됩니다.

    0 = varchar(max), nvarchar(max), varbinary(max), xml 및 큰 UDT 값은 레코드에 맞는 한 최대 8000바이트까지 데이터 행에 직접 저장됩니다. 값이 레코드에 맞지 않으면 포인터는 행 내부에 저장되고 나머지는 행 외부 LOB 저장 공간에 저장됩니다. 0이 기본값입니다.

    vardecimal 저장소 형식

    TRUE, ON 또는 1이면 지정된 테이블을 VarDecimal 저장소 형식에 사용할 수 있습니다. FALSE, OFF 또는 0이면 지정된 테이블을 VarDecimal 저장소 형식에 사용할 수 없습니다. sp_db_vardecimal_storage_format을 사용하여 데이터베이스를 vardecimal 저장소 형식에 사용하도록 설정한 경우에만 vardecimal 저장소 형식을 사용할 수 있습니다. SQL Server 2008 이상에서는 vardecimal 저장소 형식이 더 이상 사용되지 않습니다. 대신 ROW 압축을 사용하십시오. 자세한 내용은 데이터 압축를 참조하십시오. 0이 기본값입니다.

  • [ @OptionValue =] 'value'
    option_name을 설정(TRUE, ON 또는 1) 또는 해제(FALSE, OFF 또는 0)할지 여부입니다. value는 varchar(12)이며 기본값은 없습니다. value는 대/소문자를 구분하지 않습니다.

    유효한 text in row 옵션 값은 0, ON, OFF 또는 24에서 7000까지의 정수입니다. value가 ON이면 기본 제한 값은 256바이트가 됩니다.

반환 코드 값

0(성공) 또는 오류 번호(실패)

주의

sp_tableoption은 사용자 정의 테이블의 옵션 값을 설정할 때만 사용할 수 있습니다. 테이블 속성을 표시하려면 OBJECTPROPERTY를 사용하십시오.

sp_tableoption의 text in row 옵션은 텍스트 열을 포함하는 테이블에서만 설정하거나 해제할 수 있습니다. 테이블에 텍스트 열이 없으면 SQL Server에서 오류가 발생합니다.

text in row 옵션을 설정하면 @OptionValue 매개 변수를 사용하여 BLOB에 대한 행에 저장되는 최대 크기를 지정할 수 있습니다. 기본값은 256바이트이며 24에서 7000바이트까지의 범위에서 값을 지정할 수 있습니다.

다음과 같은 경우 text, ntext 또는 image 문자열은 데이터 행에 저장됩니다.

  • text in row가 설정된 경우

  • 문자열의 길이가 @OptionValue에 지정된 제한보다 짧은 경우

  • 데이터 행에 사용할 수 있는 충분한 공간이 있을 경우

BLOB 문자열이 데이터 행에 저장된 경우 문자와 이진 문자열과 마찬가지로 text, ntext 또는 image 문자열을 빠르게 읽고 쓸 수 있습니다. SQL Server에서는 BLOB 문자열을 읽거나 쓰기 위해 별도의 페이지에 액세스할 필요가 없습니다.

text, ntext 또는 image 문자열이 행에서 지정된 제한 또는 사용 가능한 공간보다 큰 경우에는 문자열 대신 포인터가 행에 저장됩니다. 하지만 이 경우에도 행에 BLOB 문자열을 저장하기 위한 조건이 적용됩니다. 데이터 행에 포인터를 저장하기에 충분한 공간이 필요합니다.

테이블 행에 저장된 BLOB 문자열 및 포인터는 가변 길이 문자열과 비슷하게 취급됩니다. SQL Server에는 문자열 또는 포인터를 저장하는 데 필요한 바이트 수만 사용됩니다.

기존 BLOB 문자열은 text in row가 처음 설정될 때 즉시 변환되지 않고 업데이트될 때만 변환됩니다. 마찬가지로 text in row 옵션 제한이 증가할 때 이미 데이터 행에 있는 text, ntext 또는 image 문자열은 업데이트되기 전까지는 새로운 제한 값을 적용하기 위해 변환되지 않습니다.

[!참고]

text in row 옵션을 해제하거나 옵션 제한을 줄이려면 모든 BLOB을 변환해야 하므로 변환할 BLOB 문자열 수에 따라 프로세스가 오래 걸릴 수도 있습니다. 변환을 처리하는 동안 테이블은 잠깁니다.

테이블 변수를 반환하는 함수를 포함한 테이블 변수의 경우 text in row 옵션이 기본 인라인 제한 값 256으로 자동 설정됩니다. 이 옵션은 변경되지 않습니다.

text in row 옵션은 TEXTPTR, WRITETEXT, UPDATETEXT 및 READTEXT 함수를 지원합니다. SUBSTRING() 함수를 사용하여 BLOB을 부분적으로 읽을 수 있지만 행 내부 텍스트 포인터에는 다른 텍스트 포인터와는 다른 기간 및 숫자 제한이 있습니다.

테이블의 VarDecimal 저장소 형식을 일반 Decimal 저장소 형식으로 변경하려면 데이터베이스는 단순 복구 모드이어야 합니다. 복구 모드를 변경하면 백업 목적의 로그 체인이 끊어지므로 테이블에서 VarDecimal 저장소 형식을 제거한 후 전체 데이터베이스 백업을 만들어야 합니다.

기존 LOB 데이터 형식 열(텍스트, ntext 또는 이미지)을 작거나 중간 크기 및 큰 값 형식(varchar(max), nvarchar(max) 또는 varbinary(max))으로 변환하고 대부분의 문에서 사용자 환경의 큰 값 형식 열을 참조하지 않는 경우 최적의 성능을 얻기 위해서는 large_value_types_out_of_row를 1로 변경해 보세요. large_value_types_out_of_row 옵션 값을 변경하는 경우 기존 varchar(max), nvarchar(max), varbinary(max) 및 xml 값이 즉시 변환되지는 않습니다. 문자열의 저장소는 다음에 업데이트될 때 변경됩니다. 테이블에 삽입되는 새 값은 적용된 테이블 옵션에 따라 저장됩니다. 즉각적인 결과를 얻으려면 데이터의 사본을 만든 다음, large_value_types_out_of_row 설정을 변경한 후 테이블을 다시 채우거나 작거나 중간 크기 및 큰 값 형식의 각 열을 자체로 업데이트하여 문자열의 저장소가 적용 중인 테이블 옵션으로 변경되도록 하세요. 테이블을 압축하려면 업데이트 또는 다시 채우기 후 테이블에 대한 인덱스를 다시 빌드하세요.

사용 권한

sp_tableoption을 실행하려면 테이블에 대한 ALTER 권한이 필요합니다.

1.xml 데이터를 행 외부에 저장

다음 예에서는 HumanResources.JobCandidate 테이블의 xml 데이터를 행 외부에 저장하도록 지정합니다.

USE AdventureWorks2012;
GO
EXEC sp_tableoption 'HumanResources.JobCandidate', 'large value types out of row', 1;

2.테이블에 VarDecimal 저장소 형식 사용

다음 예에서는 decimal 데이터 형식을 vardecimal storage format으로 저장하도록 Production.WorkOrderRouting 테이블을 수정합니다.

USE master;
GO
-- The database must be enabled for vardecimal storage format
-- before a table can be enabled for vardecimal storage format
EXEC sp_db_vardecimal_storage_format 'AdventureWorks2012', 'ON';
GO
USE AdventureWorks2012;
GO
EXEC sp_tableoption 'Production.WorkOrderRouting', 
   'vardecimal storage format', 'ON';

참고 항목

참조

sys.tables(Transact-SQL)

OBJECTPROPERTY(Transact-SQL)

시스템 저장 프로시저(Transact-SQL)

데이터베이스 엔진 저장 프로시저(Transact-SQL)