How to create a composite key in a table, one of which will be auto-incrementing?

Question

SQL

Here is such a request. As a result, after its execution, a new column is added to the table, the primary key and constraint are deleted, and then the composite key is added. And how to do so. so that TFS_ID is also auto-incrementing?

CREATE TABLE dbo.CONTENT_TFS (
  TFS_ID BIGINT NOT NULL,
  TFS_CONTENT NVARCHAR(1024) NOT NULL,
  PRIMARY KEY CLUSTERED (TFS_ID)
) ON [PRIMARY]
GO

INSERT INTO dbo.CONTENT_TFS (TFS_ID, TFS_CONTENT)
  VALUES 
  (1, '111'),
  (2, '222')
GO

-------------------------------------------------------------------------------
-- Добавляем столбец "SERVER_ID" в таблицу "CONTENT_TFS" 
-------------------------------------------------------------------------------
ALTER TABLE [dbo].[CONTENT_TFS] 
  ADD SERVER_ID INT NOT NULL DEFAULT (-1); -- -1, как не заданный идентификатор
GO

-------------------------------------------------------------------------------
-- Удаляем первичный ключ и ограничение для таблицы "CONTENT_TFS" 
-------------------------------------------------------------------------------
DECLARE @defname VARCHAR(100), @cmd VARCHAR(250)
SET @defname =
  (
    SELECT name
    FROM sysobjects so
    WHERE object_name(so.parent_obj) = 'CONTENT_TFS' AND so.xtype = 'PK' 
  )
SET @cmd = 'ALTER TABLE [dbo].[CONTENT_TFS] DROP CONSTRAINT ' + @defname
EXEC(@cmd)

SET @defname =
  (
    SELECT name
    FROM sysobjects so JOIN sysconstraints sc ON so.id = sc.constid
    WHERE object_name(so.parent_obj) = 'CONTENT_TFS' AND so.xtype = 'D'
      AND sc.colid = (
      SELECT colid FROM syscolumns WHERE id = object_id('[dbo].[CONTENT_TFS]') AND name = 'SERVER_ID')
  )
SET @cmd = 'ALTER TABLE [dbo].[CONTENT_TFS] DROP CONSTRAINT ' + @defname
EXEC(@cmd)

-------------------------------------------------------------------------------
-- Добавляем составной ключ для таблицы "CONTENT_TFS" 
-------------------------------------------------------------------------------
ALTER TABLE [dbo].[CONTENT_TFS]
  ADD CONSTRAINT PK_CONTENT_TFS PRIMARY KEY NONCLUSTERED (TFS_ID, SERVER_ID) 
GO
--///////////////////////////////////////////////////////////////////////////--

Answer 1

in order for the column to be with autoincrement, it must first be created in this way, specifying IDENTITY(1, 1) - https://msdn.microsoft.com/ru-ru/library/ms186775%...
in your case it is easier to immediately make it like this and not create a PK, since you then delete it.

CREATE TABLE dbo.CONTENT_TFS2 (
  TFS_ID BIGINT IDENTITY(1, 1) NOT NULL,
  TFS_CONTENT NVARCHAR(1024) NOT NULL
) ON [PRIMARY]
GO

INSERT INTO CONTENT_TFS2 (TFS_CONTENT)
VALUES 
  ('111'),
  ('222')

if you really need to first push your values ​​into the TFS_ID column, then like this:

SET IDENTITY_INSERT CONTENT_TFS2 ON
INSERT INTO CONTENT_TFS2 (TFS_ID,TFS_CONTENT)
VALUES 
  (7,'333'),
  (8,'555')
SET IDENTITY_INSERT CONTENT_TFS2 OFF