sp_tableoption(Transact-SQL)

적용 대상: SQL Server Azure SQL 데이터베이스 Azure SQL Managed Instance

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

Important

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

Transact-SQL 구문 표기 규칙

구문

sp_tableoption
    [ @TableNamePattern = ] N'TableNamePattern'
    , [ @OptionName = ] 'OptionName'
    , [ @OptionValue = ] 'OptionValue'
[ ; ]

인수

[ @TableNamePattern = ] N'TableNamePattern'

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

[ @OptionName = ] 'OptionName'

테이블 옵션 이름입니다. @OptionName varchar(35)이며 다음 값 중 하나일 수 있습니다.

값	설명
table lock on bulk load	기본값이 해제되면 사용자 정의 테이블에 대량 로드 처리를 수행하여 행 잠금을 얻습니다. 사용하도록 설정하면 사용자 정의 테이블의 대량 로드 프로세스가 대량 업데이트 잠금을 얻습니다.
insert row lock	더 이상 지원되지 않습니다. 이 옵션은 SQL Server의 잠금 동작에 영향을 주지 않으며 기존 스크립트 및 프로시저의 호환성을 위해서만 포함됩니다.
text in row	기본값인 경우 OFF 또는 0 사용하지 않도록 설정하면 현재 동작이 변경되지 않으며 행에 BLOB이 없습니다. 지정되고 @OptionValue ON 설정되거나 정수 값 700024 이 있는 경우 새 텍스트, ntext 또는 이미지 문자열이 데이터 행에 직접 저장됩니다. BLOB 값이 업데이트되면 모든 기존 BLOB(이진 큰 개체: 텍스트, ntext 또는 이미지) 데이터가 행 형식의 텍스트로 변경됩니다. 자세한 내용은 설명 부분을 참조하세요.
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 값은 최대 8,000바이트까지 데이터 행에 직접 저장되며 값이 레코드에 맞을 수 있는 한. 값이 레코드에 맞지 않으면 포인터가 행에 저장되고 나머지는 LOB 스토리지 공간에 행 외부에 저장됩니다. 0이 기본값입니다. UDT(대규모 사용자 정의 형식)는 SQL Server 2008(10.0.x) 이상에 적용됩니다. CREATE TABLE 옵션을 TEXTIMAGE_ON 사용하여 큰 데이터 형식의 스토리지 위치를 지정합니다.
vardecimal 스토리지 형식	적용 대상: SQL Server 2008(10.0.x) 이상 ON1지정된 테이블이 vardecimal 스토리지 형식에 대해 사용하도록 설정된 경우 TRUE OFF0vardecimal 스토리지 형식에 대해 테이블이 활성화되지 않은 경우 FALSE vardecimal 스토리지 형식은 데이터베이스가 sp_db_vardecimal_storage_format 사용하여 vardecimal 스토리지 형식에 대해 사용하도록 설정된 경우에만 사용할 수 있습니다. SQL Server 2008(10.0.x) 이상에서는 vardecimal 스토리지 형식이 더 이상 사용되지 않습니다. 대신 압축을 사용합니다 ROW . 자세한 내용은 Data Compression을 참조하세요. 0이 기본값입니다.

[ @OptionValue = ] 'OptionValue'

@OptionName 사용(, ON또는 ) 또는 1사용 안 함(FALSETRUE또는 0OFF)으로 설정할지 여부를 지정합니다. @OptionValue 기본값이 없는 varchar(12)입니다. @OptionValue 대/소문자를 구분하지 않습니다.

행에 있는 텍스트 옵션의 경우 유효한 옵션 값은 0ON24 OFF7000. @OptionValue 경우 제한은 ON기본적으로 256바이트로 설정됩니다.

반환 코드 값

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

설명

sp_tableoption 는 사용자 정의 테이블에 대한 옵션 값을 설정하는 데만 사용할 수 있습니다. 테이블 속성을 표시하려면 OBJECTPROPERTY 또는 쿼리 sys.tables를 사용합니다.

행의 텍스트 옵션은 sp_tableoption 텍스트 열이 포함된 테이블에서만 사용하거나 사용하지 않도록 설정할 수 있습니다. 테이블에 텍스트 열이 없으면 SQL Server에서 오류가 발생합니다.

행의 텍스트 옵션을 사용하는 경우 @OptionValue 매개 변수를 사용하면 BLOB에 대해 행에 저장할 최대 크기를 지정할 수 있습니다. 기본값은 256바이트이지만 값의 범위는 24바이트에서 7000바이트까지입니다.

다음 조건이 적용되는 경우 텍스트, ntext 또는 이미지 문자열이 데이터 행에 저장됩니다.

• 행의 텍스트를 사용할 수 있습니다.
• 문자열의 길이가 @OptionValue 지정된 제한보다 짧습니다.
• 데이터 행에 사용할 수 있는 공간이 충분합니다.

BLOB 문자열이 데이터 행에 저장되면 텍스트, ntext 또는 이미지 문자열을 읽고 쓰는 것이 문자 및 이진 문자열을 읽거나 쓰는 것만큼 빠를 수 있습니다. SQL Server는 BLOB 문자열을 읽거나 쓰기 위해 별도의 페이지에 액세스할 필요가 없습니다.

텍스트, ntext 또는 이미지 문자열이 지정된 한도 또는 행의 사용 가능한 공간보다 크면 포인터가 대신 행에 저장됩니다. 그럼에도 불구하고 행에 BLOB 문자열을 저장하기 위한 조건이 적용됩니다. 데이터 행에 포인터를 저장할 충분한 공간이 있어야 합니다.

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

행의 텍스트를 처음 사용하는 경우 기존 BLOB 문자열은 즉시 변환되지 않습니다. 업데이트될 때만 변환됩니다. 마찬가지로 행 옵션 제한의 텍스트가 늘어나 면 데이터 행에 이미 있는 텍스트, ntext 또는 이미지 문자열이 업데이트될 때까지 새 제한을 준수하도록 변환되지 않습니다.

참고 항목

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

테이블 변수를 반환하는 함수를 비롯한 테이블 변수에는 기본 인라인 제한이 256인 행의 텍스트 옵션이 자동으로 사용됩니다. 이 옵션은 변경할 수 없습니다.

행의 텍스트 옵션은 TEXTPTR, WRITETEXT, UPDATETEXT 및 READTEXT 함수를 지원합니다. 사용자는 SUBSTRING() 함수를 사용하여 BLOB의 일부를 읽을 수 있지만 행 내 텍스트 포인터에는 다른 텍스트 포인터와 다른 기간 및 숫자 제한이 있다는 점을 기억해야 합니다.

vardecimal 스토리지 형식에서 일반 10진수 스토리지 형식으로 테이블을 변경하려면 데이터베이스가 SIMPLE 복구 모델에 있어야 합니다. 복구 모델을 변경하면 백업을 위해 로그 체인이 중단되므로 테이블에서 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 .

예제

A. 행 외부에 XML 데이터 저장

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

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

B. 테이블에서 vardecimal 스토리지 형식 사용

다음 예제에서는 10진수 데이터 형식을 Production.WorkOrderRouting vardecimal 스토리지 형식으로 저장하도록 테이블을 수정합니다.

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 'AdventureWorks2022', 'ON';
GO
USE AdventureWorks2022;
GO
EXEC sp_tableoption 'Production.WorkOrderRouting',
   'vardecimal storage format', 'ON';

관련 콘텐츠

• sys.tables(Transact-SQL)
• OBJECTPROPERTY(Transact-SQL)
• 시스템 저장 프로시저(Transact-SQL)
• 저장 프로시저 데이터베이스 엔진(Transact-SQL)