SQL Server, Como definir o incremento automático depois de criar uma tabela sem perda de dados?

Eu tenho uma tabela table1 no SQL Server 2008 e tem registros nele.

Eu quero que a coluna de chave primária table1_Sno seja uma coluna de incremento automático. Isso pode ser feito sem qualquer transferência de dados ou clonagem de tabela?

Eu sei que posso usar ALTER TABLE para adicionar uma coluna de incremento automático, mas posso simplesmente adicionar a opção AUTO_INCREMENT a uma coluna existente que é a chave primária?

   

Alterar a propriedade IDENTITY é realmente uma alteração somente de metadados. Mas, para atualizar os metadados diretamente, é necessário iniciar a instância no modo de usuário único e mexer com algumas colunas em sys.syscolpars e não está documentado / não é compatível e não é algo que eu recomendaria ou forneceria detalhes adicionais.

Para as pessoas que se depararem com essa resposta no SQL Server 2012+, a maneira mais fácil de obter esse resultado de uma coluna de incremento automático seria criar um object SEQUENCE e definir o next value for seq como o padrão da coluna.

Alternativamente, ou para versões anteriores (a partir de 2005), a solução contida neste item de conexão mostra uma maneira completamente suportada de fazer isso sem qualquer necessidade de tamanho de operações de dados usando ALTER TABLE...SWITCH . Também blogged sobre no MSDN aqui . Embora o código para conseguir isso não é muito simples e há restrições – como a tabela a ser alterada não pode ser alvo de uma restrição de chave estrangeira.

Exemplo de código

Configure a tabela de teste sem coluna de identity .

 CREATE TABLE dbo.tblFoo ( bar INT PRIMARY KEY, filler CHAR(8000), filler2 CHAR(49) ) INSERT INTO dbo.tblFoo (bar) SELECT TOP (10000) ROW_NUMBER() OVER (ORDER BY (SELECT 0)) FROM master..spt_values v1, master..spt_values v2 

Altere para ter uma coluna de identity (mais ou menos instantânea).

 BEGIN TRY; BEGIN TRANSACTION; /*Using DBCC CHECKIDENT('dbo.tblFoo') is slow so use dynamic SQL to set the correct seed in the table definition instead*/ DECLARE @TableScript nvarchar(max) SELECT @TableScript = ' CREATE TABLE dbo.Destination( bar INT IDENTITY(' + CAST(ISNULL(MAX(bar),0)+1 AS VARCHAR) + ',1) PRIMARY KEY, filler CHAR(8000), filler2 CHAR(49) ) ALTER TABLE dbo.tblFoo SWITCH TO dbo.Destination; ' FROM dbo.tblFoo WITH (TABLOCKX,HOLDLOCK) EXEC(@TableScript) DROP TABLE dbo.tblFoo; EXECUTE sp_rename N'dbo.Destination', N'tblFoo', 'OBJECT'; COMMIT TRANSACTION; END TRY BEGIN CATCH IF XACT_STATE() <> 0 ROLLBACK TRANSACTION; PRINT ERROR_MESSAGE(); END CATCH; 

Teste o resultado.

 INSERT INTO dbo.tblFoo (filler,filler2) OUTPUT inserted.* VALUES ('foo','bar') 

 bar filler filler2 ----------- --------- --------- 10001 foo bar 

Limpar

 DROP TABLE dbo.tblFoo 

SQL Server: Como definir o incremento automático em uma tabela com linhas:

Essa estratégia copia fisicamente as linhas ao redor duas vezes, o que pode levar muito mais tempo se a tabela que você está copiando for muito grande.

Você pode salvar seus dados, descartar e reconstruir a tabela com o incremento automático e a chave primária e, em seguida, carregar os dados novamente.

Eu andarei com um exemplo:

Etapa 1, crie tabela foobar (sem chave primária ou incremento automático):

 CREATE TABLE foobar( id int NOT NULL, name nchar(100) NOT NULL, ) 

Etapa 2, insira algumas linhas

 insert into foobar values(1, 'one'); insert into foobar values(2, 'two'); insert into foobar values(3, 'three'); 

Passo 3, copie os dados foobar para uma tabela temporária:

 select * into temp_foobar from foobar 

Passo 4, drop table foobar:

 drop table foobar; 

Etapa 5, recrie sua tabela com as propriedades de chave primária e de incremento automático:

 CREATE TABLE foobar( id int primary key IDENTITY(1, 1) NOT NULL, name nchar(100) NOT NULL, ) 

Etapa 6, insira seus dados da tabela temporária de volta no foobar

 SET IDENTITY_INSERT temp_foobar ON INSERT into foobar (id, name) select id, name from temp_foobar; 

Passo 7, largue a sua tabela temporária e verifique se funcionou:

 drop table temp_foobar; select * from foobar; 

Você deve obter isso, e quando você inspecionar a tabela foobar, a coluna id será auto incremento de 1 e id é uma chave primária:

 1 one 2 two 3 three 

Se você quiser fazer isso através do designer, pode fazê-lo seguindo as instruções aqui “Salvar alterações não é permitido” ao alterar uma coluna existente para ser anulável

Se você não quiser adicionar uma nova coluna e puder garantir que sua coluna int atual é única, poderá selecionar todos os dados em uma tabela temporária, descartar a tabela e recriar com a coluna IDENTITY especificada. Em seguida, usando SET IDENTITY INSERT ON você pode inserir todos os seus dados na tabela temporária na nova tabela.

Sim você pode. Vá para Ferramentas> Designers> Tabela e Designers e desmarque a opção “Evitar Salvar Alterações que Impedem a Recreação de Tabela” .

Não, você não pode adicionar uma opção de incremento automático a uma coluna existente com dados, acho que a opção mencionada é a melhor.

Dê uma olhada aqui .