[sql] 기존 열에 ID 추가

SQL 2005 이상에서는 테이블의 데이터 페이지를 변경하지 않고이 문제를 해결하는 트릭이 있습니다. 이것은 모든 데이터 페이지를 터치하는 데 몇 분 또는 몇 시간이 걸릴 수있는 큰 테이블에 중요합니다. 트릭은 ID 열이 기본 키이거나 클러스터형 또는 비 클러스터형 인덱스의 일부이거나 더 단순한 “add / remove / rename column”솔루션을 트립 할 수있는 기타 문제의 경우에도 작동합니다.

트릭은 다음과 같습니다. SQL Server의 ALTER TABLE … SWITCH 문을 사용하여 데이터를 변경하지 않고 테이블의 스키마를 변경할 수 있습니다. 즉, 테이블을 동일한 테이블 스키마로, IDENTITY 열없이 IDENTITY로 바꿀 수 있습니다. 동일한 방법으로 기존 열에 IDENTITY를 추가 할 수 있습니다.

일반적으로 ALTER TABLE … SWITCH 는 분할 된 테이블의 전체 파티션을 새로운 빈 파티션으로 효율적으로 교체하는 데 사용됩니다. 그러나 파티션되지 않은 테이블에서도 사용할 수 있습니다.

이 트릭을 사용하여 5 초 이내에 25 억 행 테이블의 열을 IDENTITY에서 비 IDENTITY로 변환했습니다 (비 IDENTITY에 대해 쿼리 계획이 더 나은 다중 시간 쿼리를 실행하기 위해) 그런 다음 IDENTITY 설정을 다시 5 초 이내에 복원했습니다.

작동 방식에 대한 코드 샘플은 다음과 같습니다.

 CREATE TABLE Test
 (
   id int identity(1,1),
   somecolumn varchar(10)
 );

 INSERT INTO Test VALUES ('Hello');
 INSERT INTO Test VALUES ('World');

 -- copy the table. use same schema, but no identity
 CREATE TABLE Test2
 (
   id int NOT NULL,
   somecolumn varchar(10)
 );

 ALTER TABLE Test SWITCH TO Test2;

 -- drop the original (now empty) table
 DROP TABLE Test;

 -- rename new table to old table's name
 EXEC sp_rename 'Test2','Test';

 -- update the identity seed
 DBCC CHECKIDENT('Test');

 -- see same records
 SELECT * FROM Test; 

이것은 다른 답변의 솔루션보다 분명히 관련이 있지만 테이블이 크면 실제로 생명을 구할 수 있습니다. 몇 가지주의 사항이 있습니다.

• 내가 아는 한,이 방법으로 테이블의 열에 대해 ID 만 변경할 수 있습니다. 열 추가 / 제거, Null 허용 변경 등은 허용되지 않습니다.
• 전환하기 전에 foriegn 키를 삭제 한 후 복원해야합니다.
• WITH SCHEMABINDING 함수, 뷰 등과 동일합니다.
• 새 테이블의 인덱스는 정확히 일치해야합니다 (같은 열, 같은 순서 등).
• 이전 테이블과 새 테이블은 동일한 파일 그룹에 있어야합니다.
• SQL Server 2005 이상에서만 작동
• 필자는 이전에이 트릭이 Enterprise 또는 Developer 버전의 SQL Server에서만 작동한다고 믿었지만 (파티션은 Enterprise 및 Developer 버전에서만 지원되므로) 아래 의견에서 Mason G. Zhwiti는 SQL Standard Edition에서도 작동한다고 말합니다. 이것은 Enterprise 또는 Developer에 대한 제한이 ALTER TABLE … SWITCH에 적용되지 않는다는 것을 의미한다고 가정합니다.

위의 요구 사항을 자세히 설명하는 TechNet에 대한 좋은 기사 가 있습니다 .

업데이트 -Eric Wu 는 아래에이 솔루션에 대한 중요한 정보를 추가합니다. 더주의를 끌기 위해 여기에 복사하십시오.

여기에 언급 할만한 또 다른 경고가 있습니다. 새 테이블은 이전 테이블에서 데이터를 행복하게 수신하지만 모든 새 행은 ID 패턴에 따라 삽입되지만 1에서 시작하여 해당 열이 기본 키인 경우 중단 될 수 있습니다. DBCC CHECKIDENT('<newTableName>')전환 후 즉시 실행을 고려하십시오 . 자세한 내용은 msdn.microsoft.com/en-us/library/ms176057.aspx 를 참조 하십시오 .

테이블이 새 행으로 활발하게 확장되는 경우 (IDENTITY 추가와 새 행 추가 사이에 가동 중지 시간이 많지 않다는 것을 의미 함) 대신 DBCC CHECKIDENT새 테이블 스키마에서 ID 시드 값을 수동으로 설정하려고합니다. 예를 들어, 테이블에서 가장 큰 기존 ID보다 큽니다 (예 🙂 트랜잭션에 와를 IDENTITY (2435457, 1)둘 다 포함 할 수 있지만 (테스트하지 않은 경우) 시드 값을 수동으로 설정하는 것이 더 쉽고 더 안전 할 것 같습니다.ALTER TABLE...SWITCHDBCC CHECKIDENT

분명히 새로운 행이 테이블에 추가되지 않거나 매일 ETL 프로세스와 같이 가끔씩 추가되는 경우이 경쟁 조건이 발생하지 않으므로 DBCC CHECKIDENT괜찮습니다.

답변

ALTER TABLE (yourTable) ADD NewColumn INT IDENTITY(1,1)

ALTER TABLE (yourTable) DROP COLUMN OldColumnName

EXEC sp_rename 'yourTable.NewColumn', 'OldColumnName', 'COLUMN'

CREATE SEQUENCE  sequence_name START WITH here_higher_number_than_max_existed_value_in_column INCREMENT BY 1;

ALTER TABLE table_name ADD CONSTRAINT constraint_name DEFAULT NEXT VALUE FOR sequence_name FOR column_name

EXEC sp_RENAME 'AdminUsers.UserID' , 'OldUserID', 'COLUMN'

ALTER TABLE TableName ADD Old_ColumnName INT NOT NULL PRIMARY KEY IDENTITY(1,1)

ALTER TABLE Users ADD UserID INT NOT NULL PRIMARY KEY IDENTITY(1,1)

ALTER TABLE Table_Name DROP COLUMN Renamed_ColumnName

ALTER TABLE Users DROP COLUMN OldUserID

exec sp_help 'dbo.table_name_old';

SET IDENTITY_INSERT dbo.table_name ON 

INSERT INTO dbo.table_name_new
(
field1, field2, etc...
)
SELECT
field1, field2, etc...
FROM
dbo.table_name_old;

SELECT * FROM dbo.table_name_new

SET IDENTITY_INSERT ToyRecP.ToyAwards.lkpFile_New OFF

SELECT f.name AS ForeignKey,
   OBJECT_NAME(f.parent_object_id) AS TableName,
   COL_NAME(fc.parent_object_id, fc.parent_column_id) AS ColumnName,
   OBJECT_NAME (f.referenced_object_id) AS ReferenceTableName,
   COL_NAME(fc.referenced_object_id, fc.referenced_column_id) AS ReferenceColumnName
FROM sys.foreign_keys AS f
INNER JOIN sys.foreign_key_columns AS fc
   ON f.OBJECT_ID = fc.constraint_object_id
   ORDER BY ReferenceTableName;

ALTER TABLE [dbo].[table_name] DROP CONSTRAINT [Name_of_FK]

DROP TABLE dbo.table_name_old;

exec sp_RENAME '[Schema_Name.OldTableName]' , '[NewTableName]';