Добавление идентификатора в существующий столбец

I don't believe you can alter an existing column to be an identity column using tsql. However, you can do it through the Enterprise Manager design view.

Alternatively you could create a new row as the identity column, drop the old column, then rename your new column.

ALTER TABLE FooTable
ADD BarColumn INT IDENTITY(1, 1)
               NOT NULL
               PRIMARY KEY CLUSTERED

There isn't one, sadly; the IDENTITY property belongs to the table rather than the column.

The easier way is to do it in the GUI, but if this isn't an option, you can go the long way around of copying the data, dropping the column, re-adding it with identity, and putting the data back.

See here for a blow-by-blow account.

you can't do it like that, you need to add another column, drop the original column and rename the new column or or create a new table, copy the data in and drop the old table followed by renaming the new table to the old table

if you use SSMS and set the identity property to ON in the designer here is what SQL Server does behind the scenes. So if you have a table named [user] this is what happens if you make UserID and identity

BEGIN TRANSACTION
SET QUOTED_IDENTIFIER ON
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
SET ARITHABORT ON
SET NUMERIC_ROUNDABORT OFF
SET CONCAT_NULL_YIELDS_NULL ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
COMMIT
BEGIN TRANSACTION

GO

GO
CREATE TABLE dbo.Tmp_User
    (
    UserID int NOT NULL IDENTITY (1, 1),
    LastName varchar(50) NOT NULL,
    FirstName varchar(50) NOT NULL,
    MiddleInitial char(1) NULL

    )  ON [PRIMARY]
GO

SET IDENTITY_INSERT dbo.Tmp_User ON
GO
IF EXISTS(SELECT * FROM dbo.[User])
 EXEC('INSERT INTO dbo.Tmp_User (UserID, LastName, FirstName, MiddleInitial)
    SELECT UserID, LastName, FirstName, MiddleInitialFROM dbo.[User] TABLOCKX')
GO
SET IDENTITY_INSERT dbo.Tmp_User OFF
GO

GO
DROP TABLE dbo.[User]
GO
EXECUTE sp_rename N'dbo.Tmp_User', N'User', 'OBJECT'
GO
ALTER TABLE dbo.[User] ADD CONSTRAINT
    PK_User PRIMARY KEY CLUSTERED 
    (
    UserID
    ) ON [PRIMARY]

GO
COMMIT

Having said that there is a way to hack the system table to accomplish it by setting the bitwise value but that is not supported and I wouldn't do it

По замыслу не существует простого способа включить или выключить функцию идентификации для существующего столбца.Единственный чистый способ сделать это - создать новый столбец и сделать его столбцом идентификаторов или создать новую таблицу и перенести ваши данные.

Если мы используем SQL Server Management Studio, чтобы избавиться от значения идентификатора в столбце "id" "создается новая временная таблица, данные перемещаются во временную таблицу, старая таблица удаляется, а новая таблица переименовывается.

Используйте Management Studio, чтобы внести изменения, а затем щелкните правой кнопкой мыши в дизайнере и выберите" Сгенерировать сценарий изменения ".

Вы увидите, что это то, что SQL-сервер делает в фоновом режиме.

Используйте Management Studio, чтобы внести изменения, а затем щелкните правой кнопкой мыши в конструкторе и выберите «Создать сценарий изменения».

Вы увидите, что это то, что SQL-сервер находится в работает в фоновом режиме.

Используйте Management Studio, чтобы внести изменения, а затем щелкните правой кнопкой мыши в конструкторе и выберите «Создать сценарий изменения».

Вы увидите, что это то, что SQL-сервер находится в работает в фоновом режиме.

Вы не можете изменить столбец как столбец IDENTITY. Что вам нужно сделать, так это создать новый столбец, который изначально определен как ИДЕНТИФИКАЦИЯ, затем отбросить старый столбец и переименовать новый в старое имя.

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

ALTER TABLE (yourTable) DROP COLUMN OldColumnName

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

Marc

В SQL 2005 и более поздних версиях есть трюк, позволяющий решить эту проблему без изменения страниц данных таблицы. Это важно для больших таблиц, где касание каждой страницы данных может занять минуты или часы. Этот трюк также работает, даже если столбец идентификаторов является первичным ключом, является частью кластеризованного или некластеризованного индекса или других ошибок, которые могут помешать более простому решению «добавить / удалить / переименовать столбец».

Вот трюк: вы можете использовать оператор SQL Server ALTER TABLE ... SWITCH для изменения схемы таблицы без изменения данных, то есть вы можете заменить таблицу с IDENTITY на идентичную таблицу схема но без столбца IDENTITY. Тот же трюк работает для добавления IDENTITY к существующему столбцу.

Обычно ALTER TABLE ... SWITCH используется для эффективной замены полного раздела в многораздельной таблице новым пустым разделом. Но его также можно использовать в несекционированных таблицах.

Я использовал этот трюк, чтобы преобразовать менее чем за 5 секунд столбец таблицы с 2,5 миллиардами строк из 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; 

Это, очевидно, более сложное решение, чем решения в других ответах, но если у вас большая таблица, это может спасти жизнь. Есть некоторые предостережения:

• Насколько мне известно, identity - это единственное, что вы можете изменить в столбцах таблицы с помощью этого метода. Добавление / удаление столбцов, изменение допустимости значений NULL и т. Д. Не допускается.
• Вам нужно будет отбросить внешние ключи перед переключением и восстановить их после.
• То же самое для функций WITH SCHEMABINDING, представлений и т. Д.
• индексы новой таблицы должны точно соответствовать (те же столбцы, тот же порядок и т. д.)
• Старая и новая таблицы должны находиться в одной файловой группе.
• Работает только на SQL Server 2005 или более поздних версиях
• Ранее я считал, что этот трюк работает только в версиях SQL Server Enterprise или Developer. (поскольку разделы поддерживаются только в версиях Enterprise и Developer), но Мейсон Г. Жвити в своем комментарии ниже говорит, что это также работает и в SQL Standard Edition. Я предполагаю, что это означает, что ограничение для Enterprise или Developer не распространяется на ALTER TABLE ... SWITCH.

В TechNet есть хорошая статья , в которой подробно описаны требования, указанные выше.

ОБНОВЛЕНИЕ - ] Эрик Ву написал ниже комментарий, в котором добавлена ​​важная информация об этом решении. Скопируйте его сюда, чтобы привлечь больше внимания:

Здесь есть еще одно предостережение, о котором стоит упомянуть. Хотя new table will happily receive data from the old table, and all the new rows will be inserted following a identity pattern, they will start at 1 and potentially break if the said column is a primary key. Consider running DBCC CHECKIDENT('') immediately after switching. See msdn.microsoft.com/en-us/library/ms176057.aspx for more info.

Если таблица активно расширяется новыми строками (это означает, что у вас не так много времени простоя между добавлением IDENTITY и добавлением новых строк, тогда вместо DBCC CHECKIDENT вы захотите вручную установите начальное значение идентификатора в новую схему таблицы, чтобы оно было больше, чем самый большой существующий идентификатор в таблице, например IDENTITY (2435457, 1) . Вы можете включить оба параметра ALTER TABLE ... SWITCH и DBCC CHECKIDENT в транзакции (или нет - не тестировали это), но похоже, что установка начального значения вручную будет проще и безопаснее.

Очевидно, если в таблицу не добавляются новые строки (или они добавляются только изредка, как ежедневный процесс ETL), то этого состояния гонки не будет, поэтому DBCC CHECKIDENT в порядке.