[sql] 테이블의 열에서 ID 제거

이 작업을 수행하려면 추가하고 새 열을 채우지 않고 , 열을 재정렬없이 하고, 중단 시간없이 어떤 파티션이 사용되지 않기 때문에 데이터가 테이블에 변경되지 않기 때문에,하자가 기능을 분할하여 마법을 (하지만 당신은 돈 ‘ 엔터프라이즈 에디션 필요) :

1. 이 테이블을 가리키는 모든 외래 키 제거
2. 생성 할 테이블을 스크립팅합니다. 예를 들어 ‘MyTable2’, ‘MyIndex2’등과 같은 모든 이름을 바꿉니다. IDENTITY 사양을 제거합니다.
3. 이제 두 개의 “동일한”테이블이 있어야합니다. 하나는 가득 차고 다른 하나는 IDENTITY없이 비어 있습니다.
4. 운영 ALTER TABLE [Original] SWITCH TO [Original2]
5. 이제 원래 테이블이 비어 있고 새 테이블에 데이터가 있습니다. 두 테이블의 메타 데이터를 전환했습니다 (인스턴트).
6. 원본 (현재 비어있는 테이블)을 삭제하고 exec sys.sp_rename다양한 스키마 개체의 이름을 다시 원래 이름으로 바꾼 다음 외래 키를 다시 만들 수 있습니다.

예를 들면 다음과 같습니다.

CREATE TABLE Original
(
  Id INT IDENTITY PRIMARY KEY
, Value NVARCHAR(300)
);
CREATE NONCLUSTERED INDEX IX_Original_Value ON Original (Value);

INSERT INTO Original
SELECT 'abcd'
UNION ALL
SELECT 'defg';

다음을 수행 할 수 있습니다.

--create new table with no IDENTITY
CREATE TABLE Original2
(
  Id INT PRIMARY KEY
, Value NVARCHAR(300)
);
CREATE NONCLUSTERED INDEX IX_Original_Value2 ON Original2 (Value);

--data before switch
SELECT 'Original', *
FROM Original
UNION ALL
SELECT 'Original2', *
FROM Original2;

ALTER TABLE Original SWITCH TO Original2;

--data after switch
SELECT 'Original', *
FROM Original
UNION ALL
SELECT 'Original2', *
FROM Original2;

--clean up 
IF NOT EXISTS (SELECT * FROM Original) DROP TABLE Original;
EXEC sys.sp_rename 'Original2.IX_Original_Value2', 'IX_Original_Value', 'INDEX';
EXEC sys.sp_rename 'Original2', 'Original', 'OBJECT';


UPDATE Original
SET Id = Id + 1;

SELECT *
FROM Original;

답변

alter table yourTable add tempId int NOT NULL default -1;
update yourTable set tempId = id;

SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE TABLE_NAME = 'yourTable';

ALTER TABLE yourTable DROP CONSTRAINT PK_yourTable_id;

SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE TABLE_NAME = 'otherTable';
alter table otherTable drop constraint fk_otherTable_yourTable;
commit;
..

ALTER TABLE yourTable DROP CONSTRAINT PK_yourTable_id;
alter table yourTable drop column id;
EXEC sp_rename 'yourTable.tempId', 'id', 'COLUMN';
ALTER TABLE yourTable ADD CONSTRAINT PK_yourTable_id PRIMARY KEY (id)
commit;

alter table otherTable add constraint fk_otherTable_yourTable foreign key (yourTable_id) references yourTable(id);
..

BEGIN TRAN
BEGIN TRY
    EXEC sp_rename '[SomeTable].[Id]', 'OldId';

    ALTER TABLE [SomeTable] ADD Id int NULL

    EXEC ('UPDATE [SomeTable] SET Id = OldId')

    ALTER TABLE [SomeTable] NOCHECK CONSTRAINT ALL

    ALTER TABLE [SomeTable] DROP CONSTRAINT [PK_constraintName];
    ALTER TABLE [SomeTable] DROP COLUMN OldId
    ALTER TABLE [SomeTable] ALTER COLUMN [Id] INTEGER NOT NULL
    ALTER TABLE [SomeTable] ADD CONSTRAINT PK_JobInfo PRIMARY KEY (Id)

    ALTER TABLE [SomeTable] CHECK CONSTRAINT ALL

    COMMIT TRAN
END TRY
BEGIN CATCH
    ROLLBACK TRAN
    SELECT ERROR_MESSAGE ()
END CATCH

insert into Invoice_DELETED select * from Invoice where ...


SELECT t1.*
INTO Invoice_DELETED
FROM Invoice t1
LEFT JOIN Invoice ON 1 = 0
--WHERE t1.InvoiceID = @InvoiceID

ALTER TABLE tablename add newcolumn int
update tablename set newcolumn=existingcolumnname
ALTER TABLE tablename DROP COLUMN existingcolumnname;
EXEC sp_RENAME 'tablename.oldcolumn' , 'newcolumnname', 'COLUMN'