SQL Server의 큰 테이블에 NOT NULL 열을 추가하는 방법은 무엇입니까?

SQL Server의 큰 테이블에 NOT NULL 열을 추가하는 방법은 무엇입니까?

레코드가 많은 테이블에 NOT NULL 열을 추가하려면 DEFAULT 제약 조건을 적용해야 합니다.이 제약 조건으로 인해 테이블이 매우 클 경우 전체 ALTER TABLE 명령이 실행되는 데 오랜 시간이 걸립니다.그 이유는:

가정:

1. DEFAULT 제약 조건은 기존 레코드를 수정합니다.이는 DB가 각 레코드의 크기를 늘려야 하므로 전체 데이터 페이지의 레코드를 다른 데이터 페이지로 이동시키고 시간이 걸린다는 것입니다.
2. DEFAULT 업데이트는 원자 트랜잭션으로 실행됩니다.이는 필요한 경우 롤백을 실행할 수 있도록 트랜잭션 로그를 늘려야 함을 의미합니다.
3. 트랜잭션 로그는 전체 기록을 추적합니다.따라서 하나의 필드만 수정되더라도 로그가 필요로 하는 공간은 전체 레코드의 크기에 기존 레코드의 #를 곱한 값을 기준으로 합니다.즉, 두 테이블에서 레코드의 총 개수가 동일하더라도 레코드가 작은 테이블에 열을 추가하는 것이 레코드가 큰 테이블에 열을 추가하는 것보다 빠를 것입니다.

가능한 해결책:

1. 그것을 빨아들이고 프로세스가 완료될 때까지 기다립니다.타임아웃 기간을 매우 길게 설정해야 합니다.이것의 문제점은 레코드 수에 따라 몇 시간이 걸리거나 며칠이 걸릴 수 있다는 것입니다.
2. 열을 추가하지만 NULL을 허용합니다. 그런 다음 UPDATE 쿼리를 실행하여 기존 행에 대한 DEFAULT 값을 설정합니다.UPDATE *를 수행하지 마십시오. 한 번에 레코드 배치를 업데이트하지 않으면 솔루션 #1과 같은 문제가 발생합니다.이 접근 방식의 문제점은 불필요한 옵션임을 알 때 NULL을 허용하는 열이 발생한다는 것입니다.필요하지 않은 한 NULL을 허용하는 열을 가져서는 안 된다는 모범 사례 문서가 있다고 생각합니다.
3. 동일한 스키마로 새 테이블을 만듭니다.해당 스키마에 열을 추가합니다.원래 테이블에서 데이터를 전송합니다.원래 테이블을 내려놓고 새 테이블의 이름을 바꿉니다.이게 어떻게 1위보다 더 나은지 잘 모르겠습니다.

질문:

1. 제 가정이 맞습니까?
2. 이것이 제 유일한 해결책인가요?그렇다면 어떤 것이 가장 좋습니까?그렇지 않다면 제가 또 무엇을 할 수 있겠습니까?

저도 일 때문에 이 문제에 부딪혔습니다.그리고 저의 해결책은 #2입니다.

다음은 저의 단계입니다(SQL Server 2005를 사용하고 있습니다).

1) 기본값으로 테이블에 열을 추가합니다.

ALTER TABLE MyTable ADD MyColumn varchar(40) DEFAULT('')

) 를 합니다.NOT NULL을 가함NOCHECK옵션. 더NOCHECK다에 하지 않습니다.

ALTER TABLE MyTable WITH NOCHECK
ADD CONSTRAINT MyColumn_NOTNULL CHECK (MyColumn IS NOT NULL)

3) 표의 값을 점진적으로 업데이트합니다.

GO
UPDATE TOP(3000) MyTable SET MyColumn = '' WHERE MyColumn IS NULL
GO 1000

• 업데이트 문은 최대 3000개의 레코드만 업데이트합니다.이를 통해 당시의 데이터 덩어리를 저장할 수 있습니다.제 테이블에 시퀀스 프라이머리 키가 없기 때문에 "My Column IS NULL"을 사용해야 합니다.

• GO 1000는 이전 문장을 1000번 실행합니다.이렇게 하면 300만 개의 기록이 갱신됩니다. 더 필요하다면 이 숫자를 늘리면 됩니다.SQL Server가 UPDATE 문에 대해 0개의 레코드를 반환할 때까지 계속 실행됩니다.

제가 시도하고 싶은 것은 다음과 같습니다.

• 데이터베이스의 전체 백업을 수행합니다.
• null을 허용하는 새 열을 추가합니다. 기본값을 설정하지 않습니다.
• 각 배치가 커밋되자마자 트랜슬로그를 잘라내는 Simple 복구를 설정합니다.
• SQL은 다음과 같습니다. ALTER Database XXX SET RECOVERY 심플
• 위에서 설명한 대로 업데이트를 일괄적으로 실행하고 각 업데이트 후 커밋합니다.
• null을 더 이상 허용하지 않도록 새 열을 재설정합니다.
• 일반적인 FULL 복구로 돌아갑니다.
• SQL: ALTER DB XXX SET RECOVERY FULL
• 데이터베이스를 다시 백업합니다.

단순 복구 모델을 사용한다고 해서 로깅이 중단되는 것은 아니지만, 그 영향은 크게 줄어듭니다.이는 서버가 커밋할 때마다 복구 정보를 폐기하기 때문입니다.

할 수 있습니다.

1. 거래를 시작합니다.
2. 원래 테이블에 아무도 쓰지 않도록 쓰기 잠금을 잡으세요.
3. 새 스키마로 섀도 테이블을 만듭니다.
4. 원래 테이블에서 모든 데이터를 전송합니다.
5. sp_rename를 실행하여 이전 테이블의 이름을 바꿉니다.
6. sp_rename을 실행하여 새 테이블의 이름을 변경합니다.
7. 마지막으로 거래를 실행합니다.

이 접근 방식의 장점은 긴 프로세스 동안 독자들이 테이블에 접근할 수 있고 백그라운드에서 어떤 종류의 스키마 변경도 수행할 수 있다는 것입니다.

이것을 최신 정보로 업데이트 할 뿐입니다.

SQL Server 2012에서는 다음과 같은 상황에서 온라인 작업으로 수행할 수 있습니다.

1. 엔터프라이즈 에디션만 해당
2. 기본값은 런타임 상수여야 합니다.

두 일 수 있습니다.GETDATE()모든 행에 대해 동일한 값으로 평가됩니다.값:NEWID()조건을 충족하지 못하고 계속해서 모든 행을 업데이트하게 됩니다.

자격을 부여하는 기본값의 경우 SQL Server는 기본값을 평가하고 결과를 열 메타데이터에 기본값으로 저장하므로 생성되는 기본 제약 조건과 무관합니다(더 이상 필요하지 않을 경우 삭제할 수도 있음).에서 볼 수 .sys.system_internals_partition_columns은 다음 값은 다음 번에 업데이트될 때까지 행에 기록되지 않습니다.

여기에 대한 자세한 내용: sql server 2012에서 값 열 추가와 함께 온라인 null이 아닌 경우

이것은 오래된 질문임을 인정합니다.제 동료는 최근에 13개의 테이블 위에 있는 하나의 변경 가능한 문구로 그것을 할 수 있었다고 말했습니다.6M 줄.SQL Server 2012에서 1초 안에 완료되었습니다.저는 8M 행의 테이블에서도 같은 내용을 확인할 수 있었습니다.SQL Server의 최신 버전에서 변경된 사항이 있습니까?

Alter table mytable add mycolumn char(1) not null default('N');

사용하는 SQL 맛에 따라 다를 것 같은데, 옵션 2를 선택했는데 맨 끝에 null이 아닌 기본값으로 변경할 수 있는 테이블이 있다면 어떨까요?

모든 값이 null이 아닌 것으로 보이는데 빠른가요?

같은 테이블에 있는 칼럼을 원하신다면, 그냥 그렇게 하시면 됩니다.이 작업이 진행되는 동안에도 데이터베이스를 "라이브"할 수 있기 때문에 옵션 3이 이 작업에 가장 적합합니다.옵션 1을 사용하면 작업이 진행되는 동안 테이블이 잠겨 있다가 정말 막히게 됩니다.

열이 테이블에 있는지 여부를 별로 신경 쓰지 않는다면, 세분화된 접근 방식이 차선책이라고 생각합니다.하지만 저는 정말로 이를 피하려고 노력합니다. 왜냐하면 Charles Bretana가 말한 것처럼, 해당 테이블을 업데이트/삽입하고 수정하는 모든 장소를 확인해야 하기 때문입니다.으악!

저도 비슷한 문제가 있어서 2번 옵션을 선택했습니다.이쪽으로 가면 20분이 걸리는데 반대로 32시간이 걸립니다!!!큰 차이가 있습니다. 팁을 알려주셔서 감사합니다.이에 대한 전체 블로그 항목을 작성했지만 중요한 sql은 다음과 같습니다.

Alter table MyTable
Add MyNewColumn char(10) null default '?';
go

update MyTable set MyNewColumn='?' where MyPrimaryKey between 0 and 1000000
go
update MyTable set MyNewColumn='?' where MyPrimaryKey between 1000000 and 2000000
go
update MyTable set MyNewColumn='?' where MyPrimaryKey between 2000000 and 3000000
go
..etc..

Alter table MyTable
Alter column MyNewColumn char(10) not null;

그리고 관심이 있다면 블로그 항목: http://splinter.com.au/adding-a-column-to-a-massive-sql-server-table

저도 비슷한 문제가 있어서 #3 방식을 수정했습니다.제 경우에는 데이터베이스가 SIMPLETE 복구 모드에 있었고 열을 추가해야 하는 테이블이 FK 제약 조건에 의해 참조되지 않았습니다.

동일한 스키마로 새 테이블을 만들고 원래 테이블의 내용을 복사하는 대신 SELECT...를 사용했습니다.INTO 구문.

마이크로소프트(http://technet.microsoft.com/en-us/library/ms188029(v=sql.105).aspx) )에 의하면,

SELECT에 대한 로깅 양...INTO는 데이터베이스에 적용되는 복구 모델에 따라 달라집니다.단순 복구 모델 또는 대량 로그 복구 모델에서는 대량 작업이 최소한으로 기록됩니다.최소한의 로깅으로 SELECT…INTO 문을 사용하면 표를 만든 다음 INSERT 문으로 표를 채우는 것보다 더 효율적일 수 있습니다.자세한 내용은 최소로 기록할 수 있는 작업을 참조하십시오.

단계의 순서:

1. 기본값으로 새 열을 추가하면서 이전 테이블에서 새 테이블로 데이터 이동

 SELECT  table.*,   cast (‘default’ as nvarchar(256)) new_column
 INTO    table_copy 
 FROM    table

2. 오래된 테이블을 떨어트립니다.

 DROP TABLE  table

3. 새로 생성된 테이블 이름 바꾸기

 EXEC sp_rename 'table_copy',  ‘table’

4. 새 테이블에 필요한 제약 조건 및 인덱스 만들기

제 경우에는 테이블에 1억 개 이상의 행이 있었고 이 접근 방식은 #2 접근 방식보다 더 빨리 완료되었으며 로그 공간 증가는 미미했습니다.

1) 기본값으로 테이블에 열을 추가합니다.

ALTER TABLE MyTable ADD MyColumn int default 0

2) 표의 값을 점진적으로 업데이트합니다(수용된 답변과 동일한 효과).다른 사용자/프로세스가 차단되지 않도록 환경에 업데이트되는 레코드 수를 조정합니다.

declare @rowcount int = 1

while (@rowcount > 0)
begin           

    UPDATE TOP(10000) MyTable SET MyColumn = 0 WHERE MyColumn IS NULL       
    set @rowcount = @@ROWCOUNT

end

3) null이 필요하지 않도록 열 정의를 변경합니다.테이블을 사용하지 않는 시간에 다음을 실행하거나 몇 분 동안 다운타임을 예약합니다.저는 이것을 수백만 개의 기록이 있는 테이블에 성공적으로 사용했습니다.

ALTER TABLE MyTable ALTER COLUMN MyColumn int NOT NULL

UPDATE 대신 CURRS를 사용하겠습니다.커서는 일치하는 모든 레코드를 일괄적으로 업데이트하여 레코드별로 기록합니다. 시간이 걸리지만 테이블을 잠그지는 않습니다.

잠금을 방지하려면 WAIT(기다림)을(를) 사용합니다.

또한 DEFAULT 제약 조건이 기존 행을 변경하는지도 잘 모르겠습니다.작성자가 설명한 DEFAULT 원인과 함께 NULL 제약 조건을 사용하지 않을 수 있습니다.

변경되는 경우 마지막에 추가합니다. 따라서 의사 코드는 다음과 같습니다.

-- without NOT NULL constrain -- we will add it in the end
ALTER TABLE table ADD new_column INT DEFAULT 0

DECLARE fillNullColumn CURSOR LOCAL FAST_FORWARD
    SELECT 
        key
    FROM
        table WITH (NOLOCK)
    WHERE
        new_column IS NULL

OPEN fillNullColumn

DECLARE 
    @key INT

FETCH NEXT FROM fillNullColumn INTO @key

WHILE @@FETCH_STATUS = 0 BEGIN
     UPDATE
         table WITH (ROWLOCK)
     SET
         new_column = 0 -- default value
     WHERE
         key = @key

     WAIT 00:00:05 --wait 5 seconds, keep in mind it causes updating only 12 rows per minute

     FETCH NEXT FROM fillNullColumn INTO @key
END

CLOSE fillNullColumn
DEALLOCATE fillNullColumn

ALTER TABLE table ALTER COLUMN new_column ADD CONSTRAIN xxx

몇 가지 구문 오류가 있다고 확신하지만, 이것이 당신의 문제를 해결하는 데 도움이 되기를 바랍니다.

행운을 빕니다.

테이블을 수직으로 분할합니다.즉, 두 개의 테이블과 동일한 기본 키, 그리고 정확히 동일한 개수의 레코드가 있다는 것을 의미합니다.하나는 이미 가지고 있는 것이고, 다른 하나는 키만 가지고 있는 것이며, 새로운 Non-Null 열(기본값 포함)입니다. 두 테이블이 동기화되도록 코드 삽입, 업데이트 및 삭제를 모두 수정하십시오.두 테이블을 함께 "접합"하는 보기를 생성하여 클라이언트 Select 문에 대한 단일 테이블처럼 나타나는 두 테이블의 단일 논리적 조합을 생성할 수 있습니다.

언급URL : https://stackoverflow.com/questions/287954/how-do-you-add-a-not-null-column-to-a-large-table-in-sql-server