[sql] 기존 열에 ID 추가

테이블의 기본 키를 ID 열로 변경해야하며 테이블에 이미 많은 행이 있습니다.

ID를 정리하여 1부터 순차적으로 시작하고 테스트 데이터베이스에서 제대로 작동하는지 확인하는 스크립트가 있습니다.

ID 속성을 갖도록 열을 변경하는 SQL 명령은 무엇입니까?



답변

식별을 위해 기존 열을 변경할 수 없습니다.

두 가지 옵션이 있습니다

  1. ID가있는 새 테이블을 만들고 기존 테이블을 삭제

  2. ID가있는 새 열을 만들고 기존 열을 삭제

접근법 1. ( 새 테이블 ) 여기에서 새로 작성된 ID 열에 기존 데이터 값을 보유 할 수 있습니다.

CREATE TABLE dbo.Tmp_Names
    (
      Id int NOT NULL
             IDENTITY(1, 1),
      Name varchar(50) NULL
    )
ON  [PRIMARY]
go

SET IDENTITY_INSERT dbo.Tmp_Names ON
go

IF EXISTS ( SELECT  *
            FROM    dbo.Names )
    INSERT  INTO dbo.Tmp_Names ( Id, Name )
            SELECT  Id,
                    Name
            FROM    dbo.Names TABLOCKX
go

SET IDENTITY_INSERT dbo.Tmp_Names OFF
go

DROP TABLE dbo.Names
go

Exec sp_rename 'Tmp_Names', 'Names'

접근 방식 2 ( 새 열 ) 새로 만든 신원 열의 기존 데이터 값을 유지할 수 없습니다. 신원 열에는 일련의 번호가 있습니다.

Alter Table Names
Add Id_new Int Identity(1, 1)
Go

Alter Table Names Drop Column ID
Go

Exec sp_rename 'Names.Id_new', 'ID', 'Column'

자세한 내용은 다음 Microsoft SQL Server 포럼 게시물을 참조하십시오.

열을 ID로 변경하는 방법 (1,1)


답변

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괜찮습니다.


답변

열을 IDENTITY 열로 변경할 수 없습니다. 해야 할 일은 get-go에서 IDENTITY로 정의 된 새 열을 만든 다음 이전 열을 삭제하고 새 열의 이름을 이전 이름으로 바꾸는 것입니다.

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

ALTER TABLE (yourTable) DROP COLUMN OldColumnName

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

마크


답변

여기에 설명 된 멋진 솔루션이 있습니다.
SQL SERVER – 열에서 ID 속성 추가 또는 제거

SQL Manager에서 테이블을 수동으로 간단히 편집하면 ID를 변경하고 변경 사항을 저장하지 말고 변경 사항에 대해 생성 할 스크립트를 표시하고 복사 한 다음 나중에 사용하십시오.

스크립트 (스크립트)에는 변경하는 테이블과 관련된 모든 외래 키, 인덱스 등이 포함되어 있기 때문에 시간을 크게 절약 할 수 있습니다. 이것을 수동으로 작성하는 것은 … 신은 금지합니다.


답변

IDENTITY 대신 SEQUENCE 를 사용하십시오 .

SQL Server 2014에서는 (낮은 버전에 대해서는 모르겠습니다) 시퀀스를 사용하여 간단하게 수행 할 수 있습니다.

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

여기에서 : 열의 기본값으로 시퀀스


답변

간단한 설명

sp_RENAME을 사용하여 기존 열의 이름을 바꿉니다.

EXEC sp_RENAME ‘Table_Name.Existing_ColumnName’, ‘New_ColumnName’, ‘COLUMN’

이름 바꾸기의 예 :

기존 열 UserID의 이름이 OldUserID로 바뀝니다.

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

그런 다음 alter query를 사용하여 새 열을 추가하여 기본 키 및 ID 값으로 설정하십시오.

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

기본 키 설정의 예

새로 작성된 열 이름은 UserID입니다.

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

이제 테이블의 기존 열에 기본 키와 ID를 추가했습니다.


답변

나는 DBA가없는 팀에 일한 Java 개발자이며 개발자로서 DBA 권한을 얻을 수없는 곳입니다. 두 데이터베이스간에 전체 스키마를 이동하는 작업을 수행 했으므로 DBA가 없어도 관리자 권한이 없기 때문에 SQL Server 2008에서 GUI를 사용할 수 없어 스크립트를 실행하여 스크립트를 실행하고 수행해야했습니다.

그러나 새로운 schema.table에서 저장 프로 시저를 실행할 때 모든 것이 문제없이 이동했지만 테이블에서 ID 필드를 잃어 버렸습니다. 테이블을 만든 스크립트를 두 번 확인했지만 거기에 있었지만 스크립트를 실행할 때 SQL Server가 얻지 못했습니다. 나는 DBA에 의해 나중에 같은 문제를 본 적이 있다고 들었다.

어쨌든 SQL Server 2008의 경우이 문제를 해결하기 위해 취한 단계는 다음과 같습니다. 따라서 누군가에게 도움이되기를 바랍니다. 이것이 더 어려운 다른 테이블에 대한 FK 종속성을 가지면서 내가 한 일입니다.

이 쿼리를 사용하여 ID가 ​​실제로 누락되었는지 확인하고 테이블에 대한 종속성을 확인했습니다.

1.) 테이블에서 통계를 찾습니다.

exec sp_help 'dbo.table_name_old';

2.) 이전의 PK 필드에 ID 필드를 추가하는 것을 제외하고는 동일한 동일한 새 테이블을 복제하십시오.

3.) ID를 비활성화하여 데이터를 이동하십시오.

SET IDENTITY_INSERT dbo.table_name ON 

4.) 데이터를 전송하십시오.

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

5.) 데이터가 있는지 확인하십시오.

SELECT * FROM dbo.table_name_new

6.) ID를 다시 활성화하십시오.

SET IDENTITY_INSERT ToyRecP.ToyAwards.lkpFile_New OFF

7.) 이것은 원본 테이블이 종속성으로 참조하는 테이블을 확인하기 위해 모든 FK 관계를 얻는 가장 좋은 스크립트이며 많은 사람을 만났으므로 골키퍼입니다!

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;

8.)이 다음 단계 전에 관련된 모든 테이블에 대한 모든 PK 및 FK 스크립트가 있는지 확인하십시오.

9.) SQL Server 2008을 사용하여 각 키를 마우스 오른쪽 단추로 클릭하고 스크립트로 작성할 수 있습니다.

10.) 다음 구문을 사용하여 종속성 테이블에서 FK를 삭제하십시오.

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

11.) 원래 테이블을 삭제하십시오.

DROP TABLE dbo.table_name_old;

13.)이 다음 단계는 9 단계에서 SQL Server 2008에서 생성 한 스크립트에 의존합니다.

-새 테이블에 PK를 추가하십시오.

–FK를 새 테이블에 추가하십시오.

-FK를 다시 의존성 테이블에 추가하십시오.

14.) 모든 것이 정확하고 완전한지 확인하십시오. GUI를 사용하여 테이블을 보았습니다.

15.) 새 테이블의 이름을 원래 테이블 이름으로 바꿉니다.

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

마침내 모든 것이 작동했습니다!