ALTER TABLE(Transact-SQL)

업데이트: 2007년 9월 15일

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

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 ] 
    | {ADD | DROP } { ROWGUIDCOL | PERSISTED | NOT FOR REPLICATION}
    } 
    | [ WITH { CHECK | NOCHECK } ] ADD 
    { 
        <column_definition>
      | <computed_column_definition>
      | <table_constraint> 
    } [ ,...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 ] }
    | SWITCH [ PARTITION source_partition_number_expression ]
        TO target_table 
        [ PARTITION target_partition_number_expression ]
}
[ ; ]

<drop_clustered_constraint_option> ::=  
    { 
        MAXDOP = max_degree_of_parallelism
      | ONLINE = {ON | OFF }
      | MOVE TO { partition_scheme_name ( column_name ) | filegroup
          | "default"}
    }

인수

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

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

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

• ALTER COLUMN
  명명된 열을 변경하도록 지정합니다. 호환성 수준이 65 이하일 경우 ALTER COLUMN을 사용할 수 없습니다. 자세한 내용은 sp_dbcmptlevel(Transact-SQL)을 참조하십시오.

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

  • 데이터 형식이 timestamp인 열
  • 테이블의 ROWGUIDCOL
  • 계산 열이나 계산 열에 사용된 열
  • 인덱스에 사용된 열. 열의 데이터 형식이 <languageKeyword>varchar</languageKeyword>, <languageKeyword>nvarchar</languageKeyword> 또는 <languageKeyword>varbinary</languageKeyword>가 아니고, 데이터 형식이 변경되지 않고, 새 크기가 원래의 크기보다 크거나 같고, 인덱스가 PRIMARY KEY 제약 조건의 결과가 아닐 경우입니다.
  • CREATE STATISTICS 문으로 생성된 통계에 사용된 열. 먼저 DROP STATISTICS 문을 사용하여 통계를 제거합니다. 쿼리 최적화 프로그램에 의해 자동으로 생성된 통계는 ALTER COLUMN에 의해 자동으로 삭제됩니다.
  • PRIMARY KEY 또는 [FOREIGN KEY] REFERENCES 제약 조건에 사용된 열
  • CHECK 또는 UNIQUE 제약 조건에 사용된 열. 그러나 CHECK 또는 UNIQUE 제약 조건에 사용된 가변 길이 열의 길이는 변경할 수 있습니다.
  • 기본값 정의에 연결된 열. 그러나 데이터 형식이 변경되지 않은 경우 열의 길이, 전체 자릿수 또는 소수 자릿수를 변경할 수 있습니다.
    text, ntext 및 image 열의 데이터 형식은 다음과 같은 방식으로만 변경할 수 있습니다.
    • text: varchar(max), nvarchar(max) 또는 xml
    • ntext: varchar(max), nvarchar(max) 또는 xml
    • image : 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 2005 시스템 데이터 형식
  • SQL Server 시스템 데이터 형식을 기반으로 하는 별칭 데이터 형식. 별칭 데이터 형식은 CREATE TYPE 문으로 만들어진 다음 테이블 정의에 사용됩니다.
  • .NET Framework 사용자 정의 형식 및 이 사용자 정의 형식이 속한 스키마. .NET Framework 사용자 정의 형식은 CREATE TYPE 문으로 만들어진 다음 테이블 정의에 사용됩니다.

  변경된 열의 type_name에 대한 기준은 다음과 같습니다.

  • 이전 데이터 형식은 암시적으로 새 데이터 형식으로 변환 가능해야 합니다.
  • type_name은 timestamp일 수 없습니다.
  • ALTER COLUMN에 대해 ANSI_NULL 기본값이 항상 설정되어 있습니다. 값을 지정하지 않으면 열에 Null이 허용됩니다.
  • ALTER COLUMN에 대해 ANSI_PADDING 패딩이 항상 설정되어 있습니다.
  • 수정된 열이 ID 열이면 new_data_type은 ID 속성을 지원하는 데이터 형식이어야 합니다.
  • SET ARITHABORT의 현재 설정이 무시됩니다. ALTER TABLE은 ARITHABORT가 설정된 것처럼 작동합니다.

  [!참고] 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 데이터 정렬 이름(Transact-SQL)을 참조하십시오.

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

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

  • CHECK, FOREIGN KEY 제약 조건 또는 계산 열이 변경된 열을 참조하는 경우
  • 인덱스, 통계 또는 전체 텍스트 인덱스가 열에 생성된 경우. 변경된 열에 자동으로 생성된 통계는 해당 열의 데이터 정렬이 변경되면 삭제됩니다.
  • 스키마 바운드 뷰 또는 함수가 열을 참조하는 경우

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

• 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로 지정된 열인 경우 SQL Server 2005 데이터베이스 엔진은 계산된 값을 테이블에 저장하고 계산 열이 종속된 다른 열이 업데이트되면 해당 값을 업데이트합니다. 계산 열을 PERSISTED로 표시하면 결정적이지만 정확하지 않은 식에 정의된 계산 열에 인덱스를 생성할 수 있습니다. 자세한 내용은 계산 열에 인덱스 만들기를 참조하십시오.

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

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

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

  기존 데이터에 대해 새로운 CHECK 또는 FOREIGN KEY 제약 조건을 확인하지 않으려면 WITH NOCHECK를 사용합니다. 이 방법은 꼭 필요한 경우를 제외하면 사용하지 않는 것이 좋습니다. 새로운 제약 조건은 향후 데이터가 업데이트될 때마다 평가됩니다. 따라서 제약 조건 추가 시 WITH NOCHECK에 의해 숨겨진 모든 제약 조건 위반은 제약 조건에 위배되는 데이터가 있는 행을 업데이트할 경우 업데이트 오류를 발생시킬 수 있습니다.

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

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

• DROP { [ CONSTRAINT ] constraint_name | COLUMN column_name }
  테이블에서 constraint_name 또는 column_name이 제거되도록 지정합니다. 여러 개의 열과 제약 조건을 나열할 수 있습니다. 호환성 수준이 65 이하일 경우에는 DROP COLUMN이 허용되지 않습니다. 자세한 내용은 sp_dbcmptlevel(Transact-SQL)을 참조하십시오.

  사용자 정의 또는 시스템 제공 제약 조건 이름은 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 2005 Enterprise Edition에서만 사용할 수 있습니다.

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

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

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

  [!참고] 온라인 인덱스 작업은 SQL Server 2005 Enterprise Edition에서만 사용할 수 있습니다.

• 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
  설정하거나 해제할 트리거의 이름을 지정합니다.

• SWITCH [ PARTITION source_partition_number_expression ] TO 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 문을 사용할 수 없습니다.

주의

새 데이터 행을 추가하려면 INSERT를 사용합니다. 데이터 행을 제거하려면 DELETE 또는 TRUNCATE TABLE을 사용합니다. 기존 행의 값을 변경하려면 UPDATE를 사용합니다.

테이블을 참조하는 프로시저 캐시에 실행 계획이 있을 경우 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 2005 Enterprise Edition에서 하나의 ALTER TABLE ADD(인덱스 기반) CONSTRAINT 또는 DROP(클러스터형 인덱스) CONSTRAINT 문을 실행하는 데 사용되는 프로세서 수는 max degree of parallelism 구성 옵션 및 현재 작업에 따라 결정됩니다. 데이터베이스 엔진이 시스템에서 진행 중인 작업이 많음을 감지하면 작업의 병렬 처리 수준은 문 실행 시작 전에 자동으로 감소됩니다. MAXDOP 옵션을 지정하여 문을 실행하는 데 사용되는 프로세서 수를 수동으로 구성할 수 있습니다.

분할된 테이블

분할된 테이블과 관련된 SWITCH 작업을 수행할 수 있을 뿐 아니라 ALTER TABLE을 사용하여 분할된 테이블의 열, 제약 조건 및 트리거의 상태를 분할되지 않은 테이블에 사용된 경우와 마찬가지로 변경할 수 있습니다. 그러나 이 문을 사용하여 테이블 자체를 분할하는 방법을 변경할 수는 없습니다. 분할된 테이블을 재분할하려면 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을 사용하여 제약 조건을 다시 삭제하거나 추가할 필요가 없습니다.

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

클러스터형 인덱스를 만든 제약 조건이 삭제되면 클러스터형 인덱스의 리프 수준에 저장된 데이터 행이 비클러스터형 테이블에 저장됩니다. SQL Server 2005에서 클러스터형 인덱스를 삭제하고 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 구독자에 변경 내용이 전파됩니다. 이 기능에는 몇 가지 제한이 있으며 해제할 수 있습니다. 자세한 내용은 게시 데이터베이스의 스키마 변경을 참조하십시오.

사용 권한

테이블에 대한 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 AdventureWorks ; 
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 AdventureWorks;
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 AdventureWorks;
GO
ALTER TABLE Production.TransactionHistoryArchive
DROP CONSTRAINT PK_TransactionHistoryArchive_TransactionID
WITH (ONLINE = ON);
GO

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

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

USE AdventureWorks ;
GO
CREATE TABLE Person.ContactBackup
(ContactID int) ;
GO
ALTER TABLE Person.ContactBackup
ADD CONSTRAINT FK_ContactBacup_Contact FOREIGN KEY (ContactID)
    REFERENCES Person.Contact (ContactID) ;
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');

참고 항목

참조

sp_rename(Transact-SQL)
CREATE TABLE(Transact-SQL)
DROP TABLE(Transact-SQL)
sp_help(Transact-SQL)
ALTER PARTITION SCHEME(Transact-SQL)
ALTER PARTITION FUNCTION(Transact-SQL)
EVENTDATA(Transact-SQL)

관련 자료

테이블 만들기 및 수정
게시 데이터베이스의 스키마 변경

도움말 및 정보

SQL Server 2005 지원 받기

변경 내역

릴리스	내역
2007년 9월 15일	변경된 내용 '열의 크기 변경' 섹션 및 예 16을 추가했습니다.
2006년 12월 12일	변경된 내용 구문 및 인수 섹션에서 NOT FOR REPLICATION 절의 위치 및 의미를 명확하게 했습니다. SWITCH 절의 대상 테이블을 여러 부분으로 구성된 식별자로 표현할 수 있다는 자세한 설명을 추가했습니다.
2006년 4월 14일	새로운 내용 복제된 테이블에는 SWITCH 문을 사용할 수 없음을 설명했습니다.
2005년 12월 5일	새로운 내용 구문 다이어그램과 인수 정의 목록에 DROP NOT FOR REPLICATION 절을 추가했습니다. 변경된 내용 COLLATE 절을 구문 다이어그램에서 올바른 위치로 이동했습니다. 예 13과 14를 수정했습니다.