• 아티클

ALTER TABLE(Transact-SQL)

열과 제약 조건을 변경, 추가 또는 삭제하거나 파티션을 재할당하거나 제약 조건과 트리거를 설정 또는 해제하여 테이블 정의를 수정합니다.

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

구문

ALTER TABLE [ database_name . [ schema_name ] . | schema_name . ] table_name 
{ 
    ALTER COLUMN column_name 
    { 
        [ type_schema_name. ] type_name [ ( { precision [ , scale ] 
            | max | xml_schema_collection } ) ] 
        [ COLLATE collation_name ] 
        [ NULL | NOT NULL ] [ SPARSE ]
    | {ADD | DROP } 
        { ROWGUIDCOL | PERSISTED | NOT FOR REPLICATION | SPARSE }
    } 
        | [ WITH { CHECK | NOCHECK } ]

    | ADD 
    { 
        <column_definition>
      | <computed_column_definition>
      | <table_constraint> 
      | <column_set_definition> 
    } [ ,...n ]

    | DROP 
    { 
        [ CONSTRAINT ] constraint_name 
        [ WITH ( <drop_clustered_constraint_option> [ ,...n ] ) ]
        | COLUMN column_name 
    } [ ,...n ] 

    | [ WITH { CHECK | NOCHECK } ] { CHECK | NOCHECK } CONSTRAINT 
        { ALL | constraint_name [ ,...n ] } 

    | { ENABLE | DISABLE } TRIGGER 
        { ALL | trigger_name [ ,...n ] }

    | { ENABLE | DISABLE } CHANGE_TRACKING 
        [ WITH ( TRACK_COLUMNS_UPDATED = { ON | OFF } ) ]

    | SWITCH [ PARTITION source_partition_number_expression ]
        TO target_table 
        [ PARTITION target_partition_number_expression ]

    | SET ( FILESTREAM_ON = { partition_scheme_name | filegroup | 
                "default" | "NULL" } )

    | REBUILD 
      [ [PARTITION = ALL]
        [ WITH ( <rebuild_option> [ ,...n ] ) ] 
      | [ PARTITION = partition_number 
           [ WITH ( <single_partition_rebuild_option> [ ,...n ] )]
        ]
      ]

    | (<table_option>)
}
[ ; ]

<column_set_definition> ::= 
    column_set_name XML COLUMN_SET FOR ALL_SPARSE_COLUMNS

<drop_clustered_constraint_option> ::=  
    { 
        MAXDOP = max_degree_of_parallelism

      | ONLINE = {ON | OFF }
      | MOVE TO { partition_scheme_name ( column_name ) | filegroup
          | "default" }
    }
<table_option> ::=
    {
        SET ( LOCK_ESCALATION = { AUTO | TABLE | DISABLE } )
    }

<single_partition_rebuild__option> ::=
{
      SORT_IN_TEMPDB = { ON | OFF }
    | MAXDOP = max_degree_of_parallelism
    | DATA_COMPRESSION = { NONE | ROW | PAGE} }
}

인수

  • database_name
    테이블이 생성된 데이터베이스 이름입니다.

  • schema_name
    테이블이 속한 스키마 이름입니다.

  • table_name
    변경할 테이블의 이름입니다. 테이블이 현재 데이터베이스에 없거나 현재 사용자가 소유한 스키마에 포함되지 않은 경우에는 데이터베이스와 스키마를 명시적으로 지정해야 합니다.

  • ALTER COLUMN
    명명된 열을 변경하도록 지정합니다. 자세한 내용은 sp_dbcmptlevel(Transact-SQL)을 참조하십시오.

    다음과 같은 열은 수정할 수 없습니다.

    • 데이터 형식이 timestamp인 열

    • 테이블의 ROWGUIDCOL

    • 계산 열 또는 계산 열에 사용되는 열

    • 인덱스에 사용되는 열. 단, 열의 데이터 형식이 varchar, nvarchar 또는 varbinary인 경우, 데이터 형식이 변경되지 않은 경우, 새 크기가 원래 크기보다 크거나 같은 경우, 인덱스가 PRIMARY KEY 제약 조건의 결과가 아닌 경우는 제외합니다.

    • CREATE STATISTICS 문으로 생성된 통계에 사용되는 열. 단, 열의 데이터 형식이 varchar, nvarchar 또는 varbinary인 경우, 데이터 형식이 변경되지 않은 경우, 새 크기가 원래 크기보다 크거나 같은 경우, 열이 Not Null에서 Null로 변경된 경우는 제외합니다. 먼저 DROP STATISTICS 문을 사용하여 통계를 제거합니다. 쿼리 최적화 프로그램에 의해 자동으로 생성된 통계는 ALTER COLUMN에 의해 자동으로 삭제됩니다.

    • PRIMARY KEY 또는 [FOREIGN KEY] REFERENCES 제약 조건에 사용된 열

    • CHECK 또는 UNIQUE 제약 조건에 사용된 열. 그러나 CHECK 또는 UNIQUE 제약 조건에 사용된 가변 길이 열의 길이는 변경할 수 있습니다.

    • DEFAULT 정의와 연결되는 열. 그러나 데이터 형식이 변경되지 않은 경우 열의 길이, 전체 자릿수 또는 소수 자릿수를 변경할 수 있습니다.

      text, ntext 및 image 열의 데이터 형식은 다음과 같은 방식으로만 변경할 수 있습니다.

      • text를 varchar(max), nvarchar(max) 또는 xml로 변경

      • ntext를 varchar(max), nvarchar(max) 또는 xml로 변경

      • image to varbinary(max)

      데이터 형식을 변경하면 데이터 자체가 변경되는 경우도 있습니다. 예를 들어 nchar 또는 nvarchar 열을 char 또는 varchar로 변경하면 확장 문자가 변환될 수 있습니다. 자세한 내용은 CAST 및 CONVERT(Transact-SQL)를 참조하십시오. 열의 전체 자릿수 또는 소수 자릿수를 줄이면 데이터가 잘릴 수 있습니다.

      분할된 테이블의 열 데이터 형식은 변경할 수 없습니다.

  • column_name
    변경, 추가 또는 삭제할 열의 이름입니다. column_name의 최대 길이는 128자입니다. 새 열의 경우 timestamp 데이터 형식으로 생성된 열에는 column_name을 생략할 수 있습니다. timestamp 데이터 형식 열에 column_name을 지정하지 않으면 timestamp가 이름으로 사용됩니다.

  • [ type_schema_name**.** ] type_name
    변경된 열의 새 데이터 형식 또는 추가된 열의 데이터 형식입니다. 분할된 테이블의 기존 열에 type_name을 지정할 수 없습니다. type_name은 다음 중 한 가지입니다.

    • SQL Server 시스템 데이터 형식

    • SQL Server 시스템 데이터 형식을 기반으로 하는 별칭 데이터 형식. 별칭 데이터 형식은 CREATE TYPE 문으로 만들어진 다음 테이블 정의에 사용됩니다.

    • .NET Framework 사용자 정의 형식 및 이 사용자 정의 형식이 속한 스키마. .NET Framework 사용자 정의 형식은 CREATE TYPE 문으로 만들어진 다음 테이블 정의에 사용됩니다.

    변경된 열의 type_name에는 다음과 같은 조건이 적용됩니다.

    • 이전 데이터 형식은 암시적으로 새 데이터 형식으로 변환 가능해야 합니다.

    • type_name은 timestamp가 될 수 없습니다.

    • ALTER COLUMN에 대해 ANSI_NULL 기본값이 항상 설정되어 있습니다. 값을 지정하지 않으면 열에 Null이 허용됩니다.

    • ALTER COLUMN에 대해 ANSI_PADDING 패딩이 항상 ON으로 설정되어 있습니다.

    • 수정된 열이 ID 열이면 new_data_type은 ID 속성을 지원하는 데이터 형식이어야 합니다.

    • SET ARITHABORT의 현재 설정이 무시됩니다. ALTER TABLE은 ARITHABORT가 ON으로 설정된 것처럼 동작합니다.

    [!참고]

    COLLATE 절을 지정하지 않으면 열의 데이터 형식을 변경했을 때 데이터 정렬이 데이터베이스의 기본 데이터 정렬로 변경됩니다.

  • precision
    지정한 데이터 형식의 전체 자릿수입니다. 유효한 전체 자릿수 값에 대한 자세한 내용은 전체 자릿수, 소수 자릿수 및 길이(Transact-SQL)를 참조하십시오.

  • scale
    지정한 데이터 형식에 대한 소수 자릿수입니다. 유효한 소수 자릿수 값에 대한 자세한 내용은 전체 자릿수, 소수 자릿수 및 길이(Transact-SQL)를 참조하십시오.

  • max
    2^31-1바이트 문자, 이진 데이터 및 유니코드 데이터를 저장하기 위해 varchar, nvarchar 및 varbinary 데이터 형식에만 적용됩니다.

  • xml_schema_collection
    XML 스키마를 xml 데이터 형식에 연결하기 위해 이 형식에만 적용됩니다. 스키마 컬렉션에 xml 열을 입력하기 전에 먼저 CREATE XML SCHEMA COLLECTION을 사용하여 데이터베이스에 해당 스키마 컬렉션을 만들어야 합니다.

  • COLLATE < collation_name >
    변경된 열에 대한 새 데이터 정렬을 지정합니다. 이를 지정하지 않으면 열에 데이터베이스의 기본 데이터 정렬이 할당됩니다. 데이터 정렬 이름으로는 Windows 데이터 정렬 이름 또는 SQL 데이터 정렬 이름을 사용할 수 있습니다. 목록 및 자세한 내용은 Windows 데이터 정렬 이름(Transact-SQL) 및 SQL Server 데이터 정렬 이름(Transact-SQL)을 참조하십시오.

    char, varchar, nchar 및 nvarchar 데이터 형식의 열에 대한 데이터 정렬을 변경하는 데만 COLLATE 절을 사용할 수 있습니다. 사용자 정의 별칭 데이터 형식 열의 데이터 정렬을 변경하려면 별도의 ALTER TABLE 문을 실행하여 열을 SQL Server 시스템 데이터 형식으로 변경하고 해당 데이터 정렬을 변경한 다음 그 열을 별칭 데이터 형식으로 다시 변경해야 합니다.

    다음 조건이 하나 이상 해당되는 경우 ALTER COLUMN으로 데이터 정렬을 변경할 수 없습니다.

    • CHECK 제약 조건, FOREIGN KEY 제약 조건 또는 계산 열이 변경된 열을 참조하는 경우

    • 인덱스, 통계 또는 전체 텍스트 인덱스가 열에 생성된 경우. 변경된 열에 자동으로 만들어진 통계는 열 데이터 정렬이 변경되면 삭제됩니다.

    • 스키마 바운드 뷰 또는 함수가 열을 참조하는 경우

    자세한 내용은 COLLATE(Transact-SQL)를 참조하십시오.

  • SPARSE NULL | NOT NULL
    열이 스파스 열이거나 Null 값을 허용할 수 있는지 여부를 지정합니다. 변경할 열이 스파스 열이면 이 속성을 명시적으로 지정해야 합니다. 그렇지 않으면 열이 스파스가 아닌 열로 되돌아갑니다. 스파스 열은 NOT NULL로 지정할 수 없습니다. 스파스 열에서 스파스가 아닌 열로 변환하거나 그 반대로 변환하면 명령이 실행되는 동안 테이블이 잠깁니다.

    추가 제한 사항 및 스파스 열과 Null 허용 여부에 대한 자세한 내용은 스파스 열 사용을 참조하십시오.

    Null 값이 허용되지 않는 열은 기본값이 지정되어 있거나 테이블이 비어 있는 경우에만 ALTER TABLE을 사용하여 추가할 수 있습니다. 계산 열에서는 PERSISTED를 지정한 경우에만 NOT NULL을 지정할 수 있습니다. 새 열이 Null 값을 허용하고 기본값이 지정되어 있지 않으면 테이블에서 각 행의 새 열은 Null 값을 포함합니다. 새 열이 Null 값을 허용하고 해당 열에 기본 정의가 추가되어 있으면 WITH VALUES를 사용하여 테이블에서 기존 행의 새 열에 기본값을 저장할 수 있습니다.

    새 열이 Null 값을 허용하지 않고 테이블이 비어 있지 않은 경우에는 DEFAULT 정의가 새 열에 추가되어야 합니다. 그러면 각 기존 행의 새 열에 기본값이 자동으로 로드됩니다.

    PRIMARY KEY 제약 조건이 있는 열을 제외하고 ALTER COLUMN에 NULL을 지정하여 NOT NULL 열이 Null 값을 허용하도록 강제 설정할 수 있습니다. 열에 Null 값이 포함되어 있지 않을 경우에만 ALTER COLUMN에 NOT NULL을 지정할 수 있습니다. ALTER COLUMN NOT NULL을 허용하려면 다음과 같이 Null 값을 다른 값으로 업데이트해야 합니다.

    UPDATE MyTable SET NullCol = N'some_value' WHERE NullCol IS NULL
ALTER TABLE MyTable ALTER COLUMN NullCOl NVARCHAR(20) NOT NULL

    CREATE TABLE 또는 ALTER TABLE 문을 사용하여 테이블을 만들거나 변경하는 경우 데이터베이스 및 세션 설정은 열 정의에 사용되는 데이터 형식의 Null 허용 여부에 영향을 주거나 이를 재정의할 가능성이 있습니다. 비계산 열은 항상 NULL 또는 NOT NULL로 명시적으로 정의하는 것이 좋습니다.

    사용자 정의 데이터 형식이 있는 열을 추가할 경우에는 사용자 정의 데이터 형식과 같은 Null 허용 여부를 사용하여 열을 정의하고 열의 기본값을 지정하는 것이 좋습니다. 자세한 내용은 CREATE TABLE(Transact-SQL)을 참조하십시오.

    [!참고]

    ALTER COLUMN에 NULL 또는 NOT NULL을 지정할 때는 new_data_type [(precision [, scale ])]도 함께 지정해야 합니다. 데이터 형식, 전체 자릿수 및 소수 자릿수가 변경되지 않으면 현재 열 값을 지정합니다.

  • [ {ADD | DROP} ROWGUIDCOL ]
    지정된 열에 대해 ROWGUIDCOL 속성을 추가하거나 삭제하도록 지정합니다. ROWGUIDCOL은 열이 행 GUID 열임을 나타냅니다. 각 테이블마다 하나의 uniqueidentifier 열만 ROWGUIDCOL 열로 지정할 수 있으며 ROWGUIDCOL 속성은 uniqueidentifier 열에만 할당할 수 있습니다. ROWGUIDCOL은 사용자 정의 데이터 형식의 열에 지정할 수 없습니다.

    ROWGUIDCOL은 열에 저장된 값이 고유하도록 설정하지 않으며 테이블에 삽입된 새 행의 값을 자동으로 생성하지 않습니다. 각 열에 대해 고유한 값을 생성하려면 INSERT 문에 NEWID 함수를 사용하거나 NEWID 함수를 열의 기본값으로 지정합니다.

  • [ {ADD | DROP} PERSISTED ]
    지정된 열에 대해 PERSISTED 속성을 추가하거나 삭제하도록 지정합니다. 이 열은 결정적인 식으로 정의된 계산 열이어야 합니다. PERSISTED로 지정된 열인 경우 데이터베이스 엔진은 계산된 값을 테이블에 저장하고 계산 열이 종속된 다른 열이 업데이트될 때 해당 값을 업데이트합니다. 계산 열을 PERSISTED로 표시하면 결정적이지만 정확하지는 않은 식에 정의된 계산 열에 인덱스를 만들 수 있습니다. 자세한 내용은 계산 열에 인덱스 만들기를 참조하십시오.

    분할된 테이블의 분할 열로 사용되는 계산 열은 명시적으로 PERSISTED로 표시해야 합니다.

  • DROP NOT FOR REPLICATION
    복제 에이전트가 삽입 작업을 수행할 때 ID 열의 값이 증가하도록 지정합니다. 이 절은 column_name이 ID 열인 경우에만 지정할 수 있습니다. 자세한 내용은 NOT FOR REPLICATION으로 제약 조건, ID 및 트리거 제어를 참조하십시오.

  • SPARSE
    추가하거나 삭제할 열을 스파스 열로 지정합니다. 스파스 열의 저장소는 Null 값에 대해 최적화됩니다. 스파스 열은 NOT NULL로 지정할 수 없습니다. 스파스 열에서 스파스가 아닌 열로 변환하거나 그 반대로 변환하면 명령이 실행되는 동안 테이블이 잠깁니다. REBUILD 절을 사용하여 모든 공간 절약을 회수해야 할 수도 있습니다.

    중요 정보중요

    열을 변경할 때마다 SPARSE 속성을 지정해야 합니다. 그렇지 않으면 열이 스파스가 아닌 열로 되돌아갑니다.

    추가 제한 사항 및 스파스 열에 대한 자세한 내용은 스파스 열 사용을 참조하십시오.

  • WITH CHECK | WITH NOCHECK
    새로 추가되거나 다시 설정된 FOREIGN KEY 또는 CHECK 제약 조건에 대해 테이블의 데이터 유효성을 검사할지 여부를 지정합니다. 값을 지정하지 않으면 새 제약 조건에는 WITH CHECK가 지정되고 다시 설정된 제약 조건에는 WITH NOCHECK가 지정된 것으로 간주됩니다.

    기존 데이터에 대해 새 CHECK 또는 FOREIGN KEY 제약 조건을 확인하지 않으려면 WITH NOCHECK를 사용합니다. 이 방법은 꼭 필요한 경우가 아니면 사용하지 않는 것이 좋습니다. 새 제약 조건은 나중에 데이터가 업데이트될 때마다 평가됩니다. 따라서 제약 조건이 추가될 때 WITH NOCHECK에 의해 제약 조건 위반이 알려지지 않으면 제약 조건에 맞지 않는 데이터가 있는 행을 업데이트할 경우 업데이트 오류가 발생할 수 있습니다.

    쿼리 최적화 프로그램은 WITH NOCHECK가 정의된 제약 조건을 고려하지 않습니다. 이러한 제약 조건은 ALTER TABLE <table> WITH CHECK CHECK CONSTRAINT ALL을 사용하여 다시 설정될 때까지 무시됩니다.

  • ADD
    하나 이상의 열 정의, 계산 열 정의 또는 테이블 제약 조건이 추가되도록 지정합니다.

  • DROP { [ CONSTRAINT ] constraint_name | COLUMN column_name }
    테이블에서 constraint_name 또는 column_name이 제거되도록 지정합니다. 여러 개의 열과 제약 조건을 나열할 수 있습니다.

    제약 조건의 사용자 정의 또는 시스템 제공 이름은 sys.check_constraint, sys.default_constraints, sys.key_constraints 및 sys.foreign_keys 카탈로그 뷰를 쿼리하여 확인할 수 있습니다.

    테이블에 XML 인덱스가 있으면 PRIMARY KEY 제약 조건을 삭제할 수 없습니다.

    다음과 같은 열은 삭제할 수 없습니다.

    • 인덱스에 사용된 열

    • CHECK, FOREIGN KEY, UNIQUE 또는 PRIMARY KEY 제약 조건에 사용되는 열

    • DEFAULT 키워드로 정의된 기본값과 연결되거나 기본 개체에 바인딩된 열

    • 규칙에 바인딩된 열

    [!참고]

    열을 삭제해도 해당 열의 디스크 공간은 회수되지 않습니다. 테이블의 행 크기가 제한에 근접하거나 제한을 초과한 경우에는 삭제된 열의 디스크 공간을 회수해야 할 수도 있습니다. ALTER INDEX를 사용하여 기존 클러스터형 인덱스를 다시 작성하거나 테이블에 클러스터형 인덱스를 생성하면 공간을 회수할 수 있습니다.

  • WITH <drop_clustered_constraint_option>
    하나 이상의 클러스터형 제약 조건 삭제 옵션이 설정되도록 지정합니다.

  • MAXDOP = max_degree_of_parallelism
    작업 중에만 max degree of parallelism 구성 옵션을 재정의합니다. 자세한 내용은 max degree of parallelism 옵션을 참조하십시오.

    MAXDOP 옵션을 사용하여 병렬 계획 실행에 사용되는 프로세서 수를 제한합니다. 최대값은 64개입니다.

    max_degree_of_parallelism에는 다음 값 중 하나를 사용할 수 있습니다.

    • 1
      병렬 계획이 생성되지 않습니다.

    • >1
      병렬 인덱스 작업에 사용되는 최대 프로세서 수를 지정된 값으로 제한합니다.

    • 0(기본값)
      현재 시스템 작업에 따라 실제 프로세서 수 이하의 프로세서를 사용합니다.

    자세한 내용은 병렬 인덱스 작업 구성을 참조하십시오.

    [!참고]

    병렬 인덱스 작업은 SQL Server Enterprise, Developer 및 Evaluation Edition에서만 사용할 수 있습니다.

  • ONLINE = { ON | OFF }
    인덱스 작업 중에 쿼리 및 데이터 수정에 기본 테이블과 관련 인덱스를 사용할 수 있는지 여부를 지정합니다. 기본값은 OFF입니다. REBUILD 작업은 ONLINE 작업으로만 수행할 수 있습니다.

    • ON
      인덱스 작업 중에 장기 테이블 잠금이 유지되지 않습니다. 인덱스 작업의 주 단계 중 IS(내재된 공유) 잠금만 원본 테이블에 유지됩니다. 따라서 기본 테이블과 인덱스를 계속 쿼리하거나 업데이트할 수 있습니다. 작업이 시작될 때 아주 짧은 기간 동안 S(공유) 잠금이 원본 개체에 유지됩니다. 작업이 끝날 때 짧은 기간 동안 비클러스터형 인덱스가 생성되는 경우에는 원본에 대해 공유(S) 잠금이 획득되고, 온라인 상태에서 클러스터형 인덱스가 생성 또는 삭제될 때와 클러스터형 또는 비클러스터형 인덱스가 다시 작성될 때는 스키마 수정(SCH-M) 잠금이 획득됩니다. 로컬 임시 테이블에서 인덱스를 생성하는 경우에는 ONLINE을 ON으로 설정할 수 없습니다. 단일 스레드 힙 다시 작성 작업만 허용됩니다.

    • OFF
      인덱스 작업 중에 테이블 잠금이 적용됩니다. 클러스터형 인덱스를 생성, 다시 작성 또는 삭제하거나 비클러스터형 인덱스를 다시 작성 또는 삭제하는 오프라인 인덱스 작업을 통해 테이블의 SCH-M(스키마 수정) 잠금을 획득합니다. 이 경우 작업 중에 모든 사용자가 기본 테이블에 액세스할 수 없게 됩니다. 비클러스터형 인덱스를 만드는 오프라인 인덱스 작업을 통해 테이블의 S(공유) 잠금을 획득합니다. 따라서 기본 테이블을 업데이트할 수 없지만 SELECT 문과 같은 읽기 작업은 허용됩니다. 다중 스레드 힙 다시 작성 작업이 허용됩니다.

    자세한 내용은 온라인 인덱스 작동 방식을 참조하십시오. 잠금에 대한 자세한 내용은 잠금 모드를 참조하십시오.

    [!참고]

    온라인 인덱스 작업은 일부 SQL Server 버전에서 사용할 수 없습니다. 자세한 내용은 SQL Server 2008 R2 버전에서 지원하는 기능(Features Supported by the Editions of SQL Server 2008 R2)을 참조하십시오.

  • MOVE TO { partition_scheme_name(column_name [ 1, ... n] ) | filegroup | "default" }
    현재 클러스터형 인덱스의 리프 수준에 있는 데이터 행을 이동할 위치를 지정합니다. 테이블이 새 위치로 이동됩니다. 이 옵션은 클러스터형 인덱스를 만드는 제약 조건에만 적용됩니다.

    [!참고]

    이 컨텍스트에서 default는 키워드가 아니라 기본 파일 그룹에 대한 식별자이므로 MOVE TO "default" 또는 MOVE TO [default]와 같이 구분되어야 합니다. "default"가 지정되면 현재 세션에 대해 QUOTED_IDENTIFIER 옵션이 ON이어야 합니다. 이 값은 기본 설정입니다. 자세한 내용은 SET QUOTED_IDENTIFIER(Transact-SQL)를 참조하십시오.

  • { CHECK | NOCHECK } CONSTRAINT
    constraint_name의 설정 여부를 지정합니다. 이 옵션은 FOREIGN KEY 및 CHECK 제약 조건에만 사용할 수 있습니다. NOCHECK를 지정하면 제약 조건이 해제되고 향후 해당 열에 삽입하거나 해당 열을 업데이트할 때 제약 조건에 대한 유효성 검사가 수행되지 않습니다. DEFAULT, PRIMARY KEY 및 UNIQUE 제약 조건은 해제할 수 없습니다.

  • ALL
    NOCHECK 옵션을 사용하여 모든 제약 조건을 해제하거나 CHECK 옵션을 사용하여 모든 제약 조건을 설정하도록 지정합니다.

  • { ENABLE | DISABLE } TRIGGER
    trigger_name의 설정 여부를 지정합니다. 트리거를 해제해도 테이블에는 계속 정의되어 있습니다. 그러나 테이블에 대해 INSERT, UPDATE 또는 DELETE 문이 실행되면 트리거가 다시 설정될 때까지 트리거의 동작이 수행되지 않습니다.

  • ALL
    테이블의 모든 트리거를 설정하거나 해제하도록 지정합니다.

  • trigger_name
    설정하거나 해제할 트리거의 이름을 지정합니다.

  • { ENABLE | DISABLE } CHANGE_TRACKING
    테이블에 대해 변경 내용 추적이 설정되는지 여부를 지정합니다. 기본적으로 변경 내용 추적은 비활성화됩니다.

    이 옵션은 데이터베이스에 대해 변경 내용 추적이 설정된 경우에만 사용할 수 있습니다. 자세한 내용은 ALTER DATABASE SET 옵션(Transact-SQL)을 참조하십시오.

    변경 내용 추적을 설정하려면 테이블에 기본 키가 있어야 합니다.

  • WITH ( TRACK_COLUMNS_UPDATED = { ON | OFF } )
    데이터베이스 엔진에서 업데이트된 변경 내용 추적 열을 추적하는지 여부를 지정합니다. 기본값은 OFF입니다.

  • SWITCH [ PARTITION source_partition_number_expression ] TO [ schema_name. ] target_table [ PARTITION target_ partition_number_expression ]
    다음 방법 중 하나를 사용하여 데이터 블록을 전환합니다.

    • 테이블의 모든 데이터를 기존의 분할된 테이블에 파티션으로 재할당합니다.

    • 분할된 테이블 간에 파티션을 전환합니다.

    • 분할된 테이블의 한 파티션에 있는 모든 데이터를 기존의 분할되지 않은 테이블에 재할당합니다.

    table이 분할된 테이블인 경우 source_partition_number_expression을 지정해야 합니다. target_table이 분할된 경우에는 target_partition_number_expression을 지정해야 합니다. 테이블의 데이터를 기존의 분할된 테이블에 파티션으로 재할당하거나 분할된 한 테이블의 파티션을 다른 테이블로 전환하는 경우에는 비어 있는 대상 파티션이 있어야 합니다.

    한 파티션의 데이터를 재할당하여 단일 테이블을 구성하는 경우 비어 있는 대상 테이블이 미리 만들어져 있어야 합니다. 원본 테이블 또는 파티션과 대상 테이블 또는 파티션이 모두 같은 파일 그룹에 있어야 합니다. 해당하는 인덱스 또는 인덱스 파티션도 같은 파일 그룹에 있어야 합니다. 파티션 전환에는 여러 가지 추가 제한 사항이 적용됩니다. 자세한 내용은 파티션 전환을 사용하여 데이터를 효율적으로 전송을 참조하십시오. table과 target_table은 같을 수 없습니다. target_table은 여러 부분으로 구성된 식별자일 수 있습니다.

    source_partition_number_expression과 target_partition_number_expression은 변수와 함수를 참조할 수 있는 상수 식입니다. 이러한 식은 사용자 정의 형식 변수와 사용자 정의 함수를 포함하며 Transact-SQL 식을 참조할 수 없습니다.

    복제를 사용할 때의 SWITCH 제한 사항은 분할 테이블 및 인덱스 복제를 참조하십시오.

  • SET ( FILESTREAM_ON = { partition_scheme_name | filestream_filegroup_name | "default" | "NULL"} )
    FILESTREAM 데이터가 저장되는 위치를 지정합니다.

    SET FILESTREAM_ON 절이 있는 ALTER TABLE은 테이블에 FILESTREAM 열이 없는 경우에만 성공합니다. FILESTREAM 열은 두 번째 ALTER TABLE 문을 사용하여 추가할 수 있습니다.

    partition_scheme_name이 지정되면 CREATE TABLE에 대한 규칙이 적용됩니다. 테이블은 이미 행 데이터를 위해 분할되어 있어야 하며 테이블의 파티션 구성표는 FILESTREAM 파티션 구성표와 동일한 파티션 함수 및 열을 사용해야 합니다.

    filestream_filegroup_name은 FILESTREAM 파일 그룹의 이름을 지정합니다. 파일 그룹에는 CREATE DATABASE 또는 ALTER DATABASE 문을 사용하여 파일 그룹에 대해 정의된 한 파일이 있어야 하며 그렇지 않으면 오류가 발생합니다.

    **"default"**는 DEFAULT 속성이 설정된 FILESTREAM 파일 그룹을 지정합니다. FILESTREAM 파일 그룹이 없으면 오류가 발생합니다.

    **"NULL"은 테이블의 FILESTREAM 파일 그룹에 대한 모든 참조를 제거하도록 지정합니다. All FILESTREAM 열을 먼저 삭제해야 합니다. SET FILESTREAM_ON="NULL"**을 사용하여 테이블과 관련된 모든 FILESTREAM 데이터를 삭제해야 합니다.

  • SET ( LOCK_ESCALATION = { AUTO | TABLE | DISABLE } )
    테이블에 대해 허용되는 잠금 에스컬레이션 방법을 지정합니다.

    • AUTO
      이 옵션을 선택하면 SQL Server 데이터베이스 엔진에서 테이블 스키마에 적절한 잠금 에스컬레이션 세분성을 선택할 수 있습니다.

      • 테이블이 분할되지 않은 경우에는 잠금이 파티션으로 에스컬레이션됩니다. 잠금이 파티션 수준으로 에스컬레이션된 후에는 나중에 잠금이 TABLE 세분성으로 에스컬레이션되지 않습니다.

      • 테이블이 분할되지 않은 경우에는 잠금이 TABLE 세분성으로 에스컬레이션됩니다.

    • TABLE
      테이블이 분할되었는지 여부에 관계없이 잠금 에스컬레이션이 테이블 수준 세분성으로 수행됩니다. 이 동작은 SQL Server 2005와 같습니다. 기본값은 TABLE입니다.

    • DISABLE
      대부분의 경우 잠금 에스컬레이션이 허용되지 않습니다. 테이블 수준 잠금은 부분적으로 허용됩니다. 예를 들어 직렬화 가능 격리 수준에서 클러스터형 인덱스가 없는 테이블을 검색하면 데이터베이스 엔진에서 테이블 잠금을 사용하여 데이터 무결성을 보호해야 합니다.

  • REBUILD
    REBUILD WITH 구문을 사용하여 분할된 테이블의 파티션을 포함한 전체 테이블을 다시 작성할 수 있습니다. 테이블에 클러스터형 인덱스가 포함된 경우 REBUILD 옵션을 사용하면 클러스터형 인덱스가 다시 작성됩니다. REBUILD 작업은 ONLINE 작업으로만 수행할 수 있습니다.

    REBUILD PARTITION 구문을 사용하여 분할된 테이블의 단일 파티션을 다시 작성할 수 있습니다.

  • PARTITION = ALL
    파티션 압축 설정을 변경할 때 모든 파티션을 다시 작성합니다.

  • REBUILD WITH ( <rebuild_option> )
    모든 옵션이 클러스터형 인덱스가 있는 테이블에 적용됩니다. 테이블에 클러스터형 인덱스가 없는 경우에는 일부 옵션만 힙 구조에 영향을 줍니다.

    REBUILD 작업을 사용하여 특정 압축 설정을 지정하지 않은 경우에는 파티션에 대한 현재 압축 설정이 사용됩니다. 현재 설정을 반환하려면 sys.partitions 카탈로그 뷰의 data_compression 열을 쿼리합니다.

    다시 작성 옵션에 대한 자세한 내용은 index_option(Transact-SQL)을 참조하십시오.

  • DATA_COMPRESSION
    지정된 테이블, 파티션 번호 또는 파티션 범위에 대한 데이터 압축 옵션을 지정합니다. 다음과 같은 옵션이 있습니다.

    • NONE
      테이블 또는 지정된 파티션이 압축되지 않습니다.

    • ROW
      테이블 또는 지정된 파티션이 행 압축을 사용하여 압축됩니다.

    • PAGE
      테이블 또는 지정된 파티션이 페이지 압축을 사용하여 압축됩니다.

    여러 파티션을 동시에 다시 작성하려면 index_option(Transact-SQL)을 참조하십시오. 테이블에 클러스터형 인덱스가 없는 경우 데이터 압축을 변경하면 힙과 비클러스터형 인덱스가 다시 작성됩니다. 압축에 대한 자세한 내용은 압축된 테이블 및 인덱스 만들기 및 유니코드 압축 개요를 참조하십시오.

  • column_set_name XML COLUMN_SET FOR ALL_SPARSE_COLUMNS
    열 집합의 이름입니다. 열 집합은 구조화된 출력으로 테이블의 모든 스파스 열을 결합하는 형식화되지 않은 XML 표현입니다. 스파스 열을 포함하는 테이블에는 열 집합을 추가할 수 없습니다. 열 집합에 대한 자세한 내용은 열 집합 사용을 참조하십시오.

주의

프로시저 캐시에 테이블을 참조하는 실행 계획이 있는 경우 ALTER TABLE은 다음에 실행할 때 이러한 실행 계획을 다시 컴파일하도록 표시합니다.

열 크기 변경

ALTER COLUMN 절에 열 데이터 형식의 새 크기를 지정하여 열의 길이, 전체 자릿수 또는 소수 자릿수를 변경할 수 있습니다. 열에 데이터가 있는 경우 새 크기는 데이터의 최대 크기보다 작을 수 없습니다. 또한 열이 varchar, nvarchar 또는 varbinary 데이터 형식이고 인덱스가 PRIMARY KEY 제약 조건의 결과가 아닌 경우를 제외하고 열은 인덱스에서 정의될 수 없습니다. 예 16을 참조하십시오.

잠금 및 ALTER TABLE

ALTER TABLE에 지정된 변경 내용은 즉시 구현됩니다. 변경할 때 테이블의 행을 수정해야 하는 경우 ALTER TABLE은 행을 업데이트합니다. ALTER TABLE은 테이블에 대한 스키마 수정 잠금을 획득하여 변경 중에 다른 연결이 테이블의 메타데이터도 참조하지 않도록 합니다. 단, 마지막에 아주 짧은 SCH-M 잠금이 필요한 온라인 인덱스 작업은 예외입니다. ALTER TABLE…SWITCH 작업에서 원본 및 대상 테이블 모두에 대해 잠금이 획득됩니다. 테이블의 수정 사항이 기록되며 완전히 복구 가능합니다. 열 삭제나 기본값이 있는 NOT NULL 열 추가 등 초대형 테이블의 모든 행에 영향을 주는 변경 작업은 완료하는 데 시간이 오래 걸리고 많은 로그 레코드를 생성할 수 있습니다. 이러한 ALTER TABLE 문은 많은 행에 영향을 주는 INSERT, UPDATE 또는 DELETE 문과 마찬가지로 주의해서 실행해야 합니다.

병렬 계획 실행

SQL Server 2008 Enterprise에서 단일 ALTER TABLE ADD(인덱스 기반) CONSTRAINT 또는 DROP(클러스터형 인덱스) CONSTRAINT 문을 실행하는 데 사용되는 프로세서의 수는 max degree of parallelism 구성 옵션 및 현재 작업에 따라 결정됩니다. 데이터베이스 엔진에서 시스템 사용량이 많음을 감지하면 문 실행이 시작되기 전에 작업의 병렬 처리 수준이 자동으로 감소합니다. MAXDOP 옵션을 지정하여 문을 실행하는 데 사용되는 프로세서 수를 수동으로 구성할 수 있습니다.

분할된 테이블

ALTER TABLE을 사용하면 분할된 테이블과 관련된 SWITCH 작업을 수행할 수 있을 뿐만 아니라 분할된 테이블의 열, 제약 조건 및 트리거의 상태를 분할되지 않은 테이블에 사용되는 것처럼 변경할 수 있습니다. 그러나 이 문을 사용하여 테이블 자체가 분할되는 방식을 변경할 수는 없습니다. 분할된 테이블을 재분할하려면 ALTER PARTITION SCHEME 및 ALTER PARTITION FUNCTION을 사용합니다. 또한 분할된 테이블의 열 데이터 형식을 변경할 수 없습니다.

스키마 바운드 뷰가 있는 테이블의 제한 사항

스키마 바운드 뷰가 있는 테이블에서 ALTER TABLE 문에 적용되는 제한은 단순 인덱스가 있는 테이블을 수정할 때 현재 적용되는 제한과 같습니다. 즉, 열을 추가할 수는 있지만 스키마 바운드 뷰에 포함된 열을 제거하거나 변경하는 것은 허용되지 않습니다. ALTER TABLE 문으로 스키마 바운드 뷰에 사용된 열을 변경해야 할 경우에는 ALTER TABLE이 실패하고 데이터베이스 엔진에서 오류 메시지가 나타납니다. 스키마 바인딩 및 인덱싱된 뷰에 대한 자세한 내용은 CREATE VIEW(Transact-SQL)를 참조하십시오.

기본 테이블을 참조하는 스키마 바운드 뷰를 만들어도 해당 테이블에서 트리거를 추가하거나 제거하는 데 영향을 주지 않습니다.

인덱스 및 ALTER TABLE

제약 조건의 일부로 만들어진 인덱스는 제약 조건을 삭제하면 같이 삭제됩니다. CREATE INDEX로 만든 인덱스는 DROP INDEX를 사용하여 삭제해야 합니다. ALTER INDEX 문을 사용하면 제약 조건 정의의 인덱스 부분을 다시 작성할 수 있습니다. 이때 ALTER TABLE을 사용하여 제약 조건을 다시 삭제하거나 추가할 필요가 없습니다.

열을 제거하려면 먼저 해당 열을 기반으로 하는 인덱스와 제약 조건을 모두 제거해야 합니다.

클러스터형 인덱스를 만든 제약 조건이 삭제되면 클러스터형 인덱스의 리프 수준에 저장된 데이터 행이 비클러스터형 테이블에 저장됩니다. 클러스터형 인덱스를 삭제하고 MOVE TO 옵션을 지정하여 결과 테이블을 단일 트랜잭션으로 다른 파일 그룹이나 파티션 구성표로 이동할 수 있습니다. MOVE TO 옵션에는 다음과 같은 제한 사항이 있습니다.

  • 인덱싱된 뷰나 비클러스터형 인덱스에는 MOVE TO를 사용할 수 없습니다.

  • 파티션 구성표 또는 파일 그룹이 이미 있어야 합니다.

  • MOVE TO를 지정하지 않으면 테이블이 클러스터형 인덱스에 대해 정의된 것과 같은 파티션 구성표 또는 파일 그룹에 있게 됩니다.

클러스터형 인덱스를 삭제할 때 ONLINE = ON 옵션을 지정하여 DROP INDEX 트랜잭션이 기본 데이터 및 관련된 비클러스터형 인덱스에 대한 쿼리와 수정 사항을 차단하지 않도록 할 수 있습니다.

ONLINE = ON에는 다음과 같은 제한 사항이 있습니다.

  • ONLINE = ON은 비활성화된 클러스터형 인덱스에 사용할 수 없습니다. 비활성 인덱스는 ONLINE = OFF를 사용하여 삭제해야 합니다.

  • 한 번에 하나의 인덱스만 삭제할 수 있습니다.

  • ONLINE = ON은 인덱싱된 뷰, 비클러스터형 인덱스 또는 로컬 임시 테이블의 인덱스에 사용할 수 없습니다.

클러스터형 인덱스를 삭제하려면 기존 클러스터형 인덱스와 크기가 같은 임시 디스크 공간이 필요합니다. 이 추가 공간은 작업이 완료되면 바로 해제됩니다.

[!참고]

<drop_clustered_constraint_option> 아래에 나열된 옵션은 테이블의 클러스터형 인덱스에 적용되며 뷰의 클러스터형 인덱스 또는 비클러스터형 인덱스에 적용할 수 없습니다.

스키마 변경 내용 복제

기본적으로 SQL Server 게시자에 게시된 테이블에 대해 ALTER TABLE을 실행하면 모든 SQL Server 구독자에 변경 내용이 전파됩니다. 이 기능에는 몇 가지 제한이 있으며 해제할 수 있습니다. 자세한 내용은 게시 데이터베이스의 스키마 변경을 참조하십시오.

데이터 압축

시스템 테이블에는 압축을 사용할 수 없습니다. 테이블이 힙인 경우 ONLINE 모드의 다시 작성 작업은 단일 스레드 작업이 됩니다. 다중 스레드 힙 다시 작성 작업에는 OFFLINE 모드를 사용하십시오. 데이터 압축에 대한 자세한 내용은 압축된 테이블 및 인덱스 만들기를 참조하십시오.

압축 상태를 변경할 경우 테이블, 인덱스 또는 파티션에 어떤 영향을 주는지 확인하려면 sp_estimate_data_compression_savings 저장 프로시저를 사용합니다.

다음은 분할된 테이블에 적용되는 제한 사항입니다.

  • 테이블에 정렬되지 않은 인덱스가 있으면 단일 파티션의 압축 설정을 변경할 수 없습니다.

  • ALTER TABLE <table> REBUILD PARTITION ... 구문은 지정된 파티션을 다시 작성합니다.

  • ALTER TABLE <table> REBUILD WITH ... 구문은 모든 파티션을 다시 작성합니다.

사용 권한

테이블에 대한 ALTER 권한이 필요합니다.

ALTER TABLE 권한은 ALTER TABLE SWITCH 문과 관련된 두 테이블에 모두 적용됩니다. 전환된 데이터는 모두 대상 테이블의 보안을 상속합니다.

ALTER TABLE 문의 열을 CLR(공용 언어 런타임) 사용자 정의 형식 또는 별칭 데이터 형식으로 정의하면 해당 형식에 대한 REFERENCES 권한이 필요합니다.

예

1. 새 열 추가

다음 예에서는 Null 값을 허용하고 DEFAULT 정의를 통해 제공된 값이 없는 열을 추가합니다. 새 열의 각 행 값은 NULL입니다.

CREATE TABLE dbo.doc_exa (column_a INT) ;
GO
ALTER TABLE dbo.doc_exa ADD column_b VARCHAR(20) NULL ;
GO
EXEC sp_help doc_exa ;
GO
DROP TABLE dbo.doc_exa ;
GO

2. 열 삭제

다음 예에서는 테이블을 수정하여 열을 제거합니다.

CREATE TABLE dbo.doc_exb (column_a INT, column_b VARCHAR(20) NULL) ;
GO
ALTER TABLE dbo.doc_exb DROP COLUMN column_b ;
GO
EXEC sp_help doc_exb ;
GO
DROP TABLE dbo.doc_exb ;
GO

3. 열의 데이터 형식 변경

다음 예에서는 테이블의 열을 INT에서 DECIMAL로 변경합니다.

CREATE TABLE dbo.doc_exy (column_a INT ) ;
GO
INSERT INTO dbo.doc_exy (column_a) VALUES (10) ;
GO
ALTER TABLE dbo.doc_exy ALTER COLUMN column_a DECIMAL (5, 2) ;
GO
DROP TABLE dbo.doc_exy ;
GO

4. 제약 조건이 있는 열 추가

다음 예에서는 UNIQUE 제약 조건이 있는 새 열을 추가합니다.

CREATE TABLE dbo.doc_exc (column_a INT) ;
GO
ALTER TABLE dbo.doc_exc ADD column_b VARCHAR(20) NULL 
    CONSTRAINT exb_unique UNIQUE ;
GO
EXEC sp_help doc_exc ;
GO
DROP TABLE dbo.doc_exc ;
GO

5. 기존 열에 확인되지 않은 CHECK 제약 조건 추가

다음 예에서는 테이블의 기존 열에 제약 조건을 추가합니다. 이 열에 제약 조건을 위반하는 값이 있습니다. 따라서 기존 행에서 제약 조건이 위반되지 않도록 하고 제약 조건을 추가할 수 있도록 WITH NOCHECK를 사용합니다.

CREATE TABLE dbo.doc_exd ( column_a INT) ;
GO
INSERT INTO dbo.doc_exd VALUES (-1) ;
GO
ALTER TABLE dbo.doc_exd WITH NOCHECK 
ADD CONSTRAINT exd_check CHECK (column_a > 1) ;
GO
EXEC sp_help doc_exd ;
GO
DROP TABLE dbo.doc_exd ;
GO

6. 기존 열에 DEFAULT 제약 조건 추가

다음 예에서는 두 개의 열이 있는 테이블을 만들어 첫 번째 열에 값을 삽입하고 다른 열은 NULL로 유지합니다. 그런 다음 DEFAULT 제약 조건이 두 번째 열에 추가됩니다. 기본값이 적용되었는지 확인하기 위해 다른 값이 첫 번째 열에 삽입되고 테이블이 쿼리됩니다.

CREATE TABLE dbo.doc_exz ( column_a INT, column_b INT) ;
GO
INSERT INTO dbo.doc_exz (column_a)VALUES ( 7 ) ;
GO
ALTER TABLE dbo.doc_exz
ADD CONSTRAINT col_b_def
DEFAULT 50 FOR column_b ;
GO
INSERT INTO dbo.doc_exz (column_a) VALUES ( 10 ) ;
GO
SELECT * FROM dbo.doc_exz ;
GO
DROP TABLE dbo.doc_exz ;
GO

7. 제약 조건이 있는 여러 열 추가

다음 예에서는 제약 조건이 정의된 여러 열을 새로 추가합니다. 첫 번째 새 열은 IDENTITY 속성을 가집니다. 테이블 각 행의 ID 열에는 새로운 증가값이 있습니다.

CREATE TABLE dbo.doc_exe ( column_a INT CONSTRAINT column_a_un UNIQUE) ;
GO
ALTER TABLE dbo.doc_exe ADD 

-- Add a PRIMARY KEY identity column.
column_b INT IDENTITY
CONSTRAINT column_b_pk PRIMARY KEY, 

-- Add a column that references another column in the same table.
column_c INT NULL  
CONSTRAINT column_c_fk 
REFERENCES doc_exe(column_a),

-- Add a column with a constraint to enforce that 
-- nonnull data is in a valid telephone number format.
column_d VARCHAR(16) NULL 
CONSTRAINT column_d_chk
CHECK 
(column_d LIKE '[0-9][0-9][0-9]-[0-9][0-9][0-9][0-9]' OR
column_d LIKE
'([0-9][0-9][0-9]) [0-9][0-9][0-9]-[0-9][0-9][0-9][0-9]'),

-- Add a nonnull column with a default.
column_e DECIMAL(3,3)
CONSTRAINT column_e_default
DEFAULT .081 ;
GO
EXEC sp_help doc_exe ;
GO
DROP TABLE dbo.doc_exe ;
GO

8. 기본값이 있는 Null 허용 열 추가

다음 예에서는 DEFAULT 정의가 있는 Null 허용 열을 추가하고 WITH VALUES를 사용하여 테이블의 각 기존 행에 대한 값을 제공합니다. WITH VALUES를 사용하지 않으면 각 행의 새 열은 NULL 값을 가집니다.

USE AdventureWorks2008R2 ; 
GO
CREATE TABLE dbo.doc_exf ( column_a INT) ;
GO
INSERT INTO dbo.doc_exf VALUES (1) ;
GO
ALTER TABLE dbo.doc_exf 
ADD AddDate smalldatetime NULL
CONSTRAINT AddDateDflt
DEFAULT GETDATE() WITH VALUES ;
GO
DROP TABLE dbo.doc_exf ;
GO

9. 제약 조건 해제 및 다시 설정

다음 예에서는 데이터에 허용되는 급여를 제한하는 제약 조건을 해제합니다. NOCHECK CONSTRAINT를 ALTER TABLE에 사용하여 제약 조건을 해제하고 일반적으로 제약 조건을 위반하는 삽입을 허용합니다. CHECK CONSTRAINT는 제약 조건을 재설정합니다.

CREATE TABLE dbo.cnst_example 
(id INT NOT NULL,
 name VARCHAR(10) NOT NULL,
 salary MONEY NOT NULL
    CONSTRAINT salary_cap CHECK (salary < 100000)
);

-- Valid inserts
INSERT INTO dbo.cnst_example VALUES (1,'Joe Brown',65000);
INSERT INTO dbo.cnst_example VALUES (2,'Mary Smith',75000);

-- This insert violates the constraint.
INSERT INTO dbo.cnst_example VALUES (3,'Pat Jones',105000);

-- Disable the constraint and try again.
ALTER TABLE dbo.cnst_example NOCHECK CONSTRAINT salary_cap;
INSERT INTO dbo.cnst_example VALUES (3,'Pat Jones',105000);

-- Re-enable the constraint and try another insert; this will fail.
ALTER TABLE dbo.cnst_example CHECK CONSTRAINT salary_cap;
INSERT INTO dbo.cnst_example VALUES (4,'Eric James',110000) ;

10. 제약 조건 삭제

다음 예에서는 테이블에서 UNIQUE 제약 조건을 제거합니다.

CREATE TABLE dbo.doc_exc ( column_a INT
CONSTRAINT my_constraint UNIQUE) ;
GO
ALTER TABLE dbo.doc_exc DROP CONSTRAINT my_constraint ;
GO
DROP TABLE dbo.doc_exc ;
GO

11. 테이블 간 파티션 전환

다음 예에서는 분할된 테이블을 만들고 파티션 구성표 myRangePS1을 데이터베이스에 이미 만들었다고 가정합니다. 그런 다음 분할되지 않은 테이블이 분할된 테이블과 같은 구조로 PartitionTable 테이블의 PARTITION 2와 같은 파일 그룹에 만들어집니다. 그러면 PartitionTable 테이블의 PARTITION 2 데이터가 NonPartitionTable 테이블로 전환됩니다.

CREATE TABLE PartitionTable (col1 int, col2 char(10))
ON myRangePS1 (col1) ;
GO
CREATE TABLE NonPartitionTable (col1 int, col2 char(10))
ON test2fg ;
GO
ALTER TABLE PartitionTable SWITCH PARTITION 2 TO NonPartitionTable ;
GO

12. 트리거 해제 및 재설정

다음 예에서는 ALTER TABLE의 DISABLE TRIGGER 옵션을 사용하여 트리거를 해제하고 일반적으로 트리거를 위반하는 삽입을 허용합니다. 그런 다음 ENABLE TRIGGER를 사용하여 트리거를 재설정합니다.

CREATE TABLE dbo.trig_example 
(id INT, 
name VARCHAR(12),
salary MONEY) ;
GO
-- Create the trigger.
CREATE TRIGGER dbo.trig1 ON dbo.trig_example FOR INSERT
AS
IF (SELECT COUNT(*) FROM INSERTED
WHERE salary > 100000) > 0
BEGIN
    print 'TRIG1 Error: you attempted to insert a salary > $100,000'
    ROLLBACK TRANSACTION
END ;
GO
-- Try an insert that violates the trigger.
INSERT INTO dbo.trig_example VALUES (1,'Pat Smith',100001) ;
GO
-- Disable the trigger.
ALTER TABLE dbo.trig_example DISABLE TRIGGER trig1 ;
GO
-- Try an insert that would typically violate the trigger.
INSERT INTO dbo.trig_example VALUES (2,'Chuck Jones',100001) ;
GO
-- Re-enable the trigger.
ALTER TABLE dbo.trig_example ENABLE TRIGGER trig1 ;
GO
-- Try an insert that violates the trigger.
INSERT INTO dbo.trig_example VALUES (3,'Mary Booth',100001) ;
GO

13. 인덱스 옵션을 설정하여 PRIMARY KEY 제약 조건 만들기

다음 예에서는 PRIMARY KEY 제약 조건 PK_TransactionHistoryArchive_TransactionID를 만들고 FILLFACTOR, ONLINE 및 PAD_INDEX 옵션을 설정합니다. 결과 클러스터형 인덱스는 제약 조건과 같은 이름을 갖습니다.

USE AdventureWorks2008R2;
GO
ALTER TABLE Production.TransactionHistoryArchive WITH NOCHECK 
ADD CONSTRAINT PK_TransactionHistoryArchive_TransactionID PRIMARY KEY CLUSTERED (TransactionID)
WITH (FILLFACTOR = 75, ONLINE = ON, PAD_INDEX = ON);
GO

14. ONLINE 모드에서 PRIMARY KEY 제약 조건 삭제

다음 예에서는 ONLINE 옵션이 ON으로 설정된 PRIMARY KEY 제약 조건을 삭제합니다.

USE AdventureWorks2008R2;
GO
ALTER TABLE Production.TransactionHistoryArchive
DROP CONSTRAINT PK_TransactionHistoryArchive_TransactionID
WITH (ONLINE = ON);
GO

15. FOREIGN KEY 제약 조건 추가 및 삭제

다음 예에서는 ContactBackup 테이블을 만든 다음 Person 테이블을 참조하는 FOREIGN KEY 제약 조건을 추가했다가 다시 FOREIGN KEY 제약 조건을 삭제하여 테이블을 변경합니다.

USE AdventureWorks2008R2 ;
GO
CREATE TABLE Person.ContactBackup
(ContactID int) ;
GO
ALTER TABLE Person.ContactBackup
ADD CONSTRAINT FK_ContactBacup_Contact FOREIGN KEY (ContactID)
    REFERENCES Person.Person (BusinessEntityID) ;
ALTER TABLE Person.ContactBackup
DROP CONSTRAINT FK_ContactBacup_Contact ;
GO
DROP TABLE Person.ContactBackup ;

16. 열 크기 변경

다음 예에서는 varchar 열의 크기와 decimal 열의 전체 자릿수 및 소수 자릿수를 늘립니다. 열에 데이터가 포함되어 있으므로 열 크기는 늘리기만 가능합니다. 또한 col_a는 고유 인덱스에 정의됩니다. 데이터 형식이 varchar이고 인덱스가 PRIMARY KEY 제약 조건의 결과가 아니므로 col_a의 크기를 늘릴 수 있습니다.

IF OBJECT_ID ( 'dbo.doc_exy', 'U' ) IS NOT NULL 
    DROP TABLE dbo.doc_exy;
GO
-- Create a two-column table with a unique index on the varchar column.
CREATE TABLE dbo.doc_exy ( col_a varchar(5) UNIQUE NOT NULL, col_b decimal (4,2));
GO
INSERT INTO dbo.doc_exy VALUES ('Test', 99.99);
GO
-- Verify the current column size.
SELECT name, TYPE_NAME(system_type_id), max_length, precision, scale
FROM sys.columns WHERE object_id = OBJECT_ID(N'dbo.doc_exy');
GO
-- Increase the size of the varchar column.
ALTER TABLE dbo.doc_exy ALTER COLUMN col_a varchar(25);
GO
-- Increase the scale and precision of the decimal column.
ALTER TABLE dbo.doc_exy ALTER COLUMN col_b decimal (10,4);
GO
-- Insert a new row.
INSERT INTO dbo.doc_exy VALUES ('MyNewColumnSize', 99999.9999) ;
GO
-- Verify the current column size.
SELECT name, TYPE_NAME(system_type_id), max_length, precision, scale
FROM sys.columns WHERE object_id = OBJECT_ID(N'dbo.doc_exy');

17. 분할된 테이블의 잠금 에스컬레이션 허용

다음 예에서는 분할된 테이블의 파티션 수준에 대한 잠금 에스컬레이션을 활성화합니다. 테이블이 분할되지 않은 경우에는 잠금 에스컬레이션은 TABLE 수준에 적용됩니다.

ALTER TABLE T1 SET (LOCK_ESCALATION = AUTO);
GO

18. 테이블에 변경 내용 추적 구성

다음 예에서는 AdventureWorks2008R2 데이터베이스의 Person.Person 테이블에서 변경 내용 추적을 활성화합니다.

USE AdventureWorks2008R2;
ALTER TABLE Person.Person
ENABLE CHANGE_TRACKING;

다음 예에서는 변경 내용 추적을 활성화하고 변경 중에 업데이트된 열의 추적을 활성화합니다.

USE AdventureWorks2008R2;
ALTER TABLE Person.Person
ENABLE CHANGE_TRACKING
WITH (TRACK_COLUMNS_UPDATED = ON);

다음 예에서는 AdventureWorks2008R2 데이터베이스의 Person.Person 테이블에서 변경 내용 추적을 해제합니다.

USE AdventureWorks2008R2;
ALTER TABLE Person.Person
DISABLE CHANGE_TRACKING;

19. 테이블을 수정하여 압축 변경

다음 예에서는 분할되지 않은 테이블의 압축을 변경합니다. 힙 또는 클러스터형 인덱스는 다시 작성됩니다. 테이블이 힙인 경우에는 모든 비클러스터형 인덱스가 다시 작성됩니다.

ALTER TABLE T1 
REBUILD WITH (DATA_COMPRESSION = PAGE);

다음 예에서는 분할된 테이블의 압축을 변경합니다. REBUILD PARTITION = 1 구문은 파티션 번호 1만 다시 작성합니다.

ALTER TABLE PartitionTable1 
REBUILD PARTITION = 1 WITH (DATA_COMPRESSION =  NONE) ;
GO

동일한 작업에서 다음과 같은 대체 구문을 사용하면 테이블의 모든 파티션이 다시 작성됩니다.

ALTER TABLE PartitionTable1 
REBUILD PARTITION = ALL 
WITH (DATA_COMPRESSION = PAGE ON PARTITIONS(1) ) ;

데이터 압축 예를 더 보려면 압축된 테이블 및 인덱스 만들기를 참조하십시오.

20. 스파스 열 추가

다음 예에서는 T1 테이블의 스파스 열 추가 및 수정을 보여 줍니다. T1 테이블을 만들기 위한 코드는 다음과 같습니다.

CREATE TABLE T1
(C1 int PRIMARY KEY,
C2 varchar(50) SPARSE NULL,
C3 int SPARSE NULL,
C4 int ) ;
GO

추가 스파스 열 C5를 추가하려면 다음 문을 실행합니다.

ALTER TABLE T1
ADD C5 char(100) SPARSE NULL ;
GO

스파스가 아닌 열 C4를 스파스 열로 변환하려면 다음 문을 실행합니다.

ALTER TABLE T1
ALTER COLUMN C4 ADD SPARSE ;
GO

스파스 열 C4 를 스파스가 아닌 열로 변환하려면 다음 문을 실행합니다.

ALTER TABLE T1
ALTER COLUMN C4 DROP SPARSE;
GO

21. 열 집합 추가

다음 예에서는 T2 테이블에 열을 추가하는 방법을 보여 줍니다. 스파스 열이 이미 포함되어 있는 테이블에는 열 집합을 추가할 수 없습니다. T2 테이블을 만들기 위한 코드는 다음과 같습니다.

CREATE TABLE T2
(C1 int PRIMARY KEY,
C2 varchar(50) NULL,
C3 int NULL,
C4 int ) ;
GO

다음 3개의 문은 CS라는 열 집합을 추가한 다음, C2 및 C3 열을 SPARSE로 수정합니다.

ALTER TABLE T2
ADD CS XML COLUMN_SET FOR ALL_SPARSE_COLUMNS ;
GO

ALTER TABLE T2
ALTER COLUMN C2 ADD SPARSE ; 
GO

ALTER TABLE T2
ALTER COLUMN C3 ADD SPARSE ;
GO

22. 열 데이터 정렬 변경

다음 예에서는 열의 데이터 정렬을 변경하는 방법을 보여 줍니다. 먼저 다음과 같이 기본 사용자 데이터 정렬을 사용하여 T3 테이블을 만듭니다.

CREATE TABLE T3
(C1 int PRIMARY KEY,
C2 varchar(50) NULL,
C3 int NULL,
C4 int ) ;
GO

그런 다음 C2 열 데이터 정렬을 Latin1_General_BIN으로 변경합니다. 데이터 형식은 변경하지 않더라도 필요합니다.

ALTER TABLE T3
ALTER COLUMN C2 varchar(50) COLLATE Latin1_General_BIN 
GO