How to Add a Auto_Increment Primary Key in SQL Server Database

SQL Server add auto increment primary key to existing table

No - you have to do it the other way around: add it right from the get go as INT IDENTITY - it will be filled with identity values when you do this:

ALTER TABLE dbo.YourTable
   ADD ID INT IDENTITY

and then you can make it the primary key:

ALTER TABLE dbo.YourTable
   ADD CONSTRAINT PK_YourTable
   PRIMARY KEY(ID)

or if you prefer to do all in one step:

ALTER TABLE dbo.YourTable
   ADD ID INT IDENTITY
       CONSTRAINT PK_YourTable PRIMARY KEY CLUSTERED

Auto increment primary key in SQL Server Management Studio 2012

Make sure that the Key column's datatype is int and then setting identity manually, as image shows

Or just run this code

-- ID is the name of the  [to be] identity column
ALTER TABLE [yourTable] DROP COLUMN ID 
ALTER TABLE [yourTable] ADD ID INT IDENTITY(1,1)

the code will run, if ID is not the only column in the table

image reference fifo's

How do I add a auto_increment primary key in SQL Server database?

It can be done in a single command. You need to set the IDENTITY property for "auto number":

ALTER TABLE MyTable ADD mytableID int NOT NULL IDENTITY (1,1) PRIMARY KEY

More precisely, to set a named table level constraint:

ALTER TABLE MyTable
   ADD MytableID int NOT NULL IDENTITY (1,1),
   CONSTRAINT PK_MyTable PRIMARY KEY CLUSTERED (MyTableID)

See ALTER TABLE and IDENTITY on MSDN

how to add primary key column with auto increment in sql server

You should use inline constraint syntax

ALTER TABLE OLTMS_0B8DF2
ADD ID INT IDENTITY(1,1) PRIMARY KEY

SQL Express Alter table add primary key auto increment

You might not be able to alter the table this way depending on what data is there and if there are dependencies.

If you are using SSMS, I would recommend scripting the database to an SQL file including both the schema and the data and setting DROP and CREATE. Then you can edit the table definition(s) and regenerate the database.

By the way, for SQL-Server, the Auto increment is called IDENTITY.

How do I add a autoincrement primary key in sql server with nvarchar?

You can't do this directly - what you can do is this:

• create an auto-increment column to handle the numeric part
• add a computed column that concatenates the string prefix and the number

So try something like this:

CREATE TABLE dbo.YourTable
    (ID INT IDENTITY(1,1) NOT NULL,
     StringPrefix NVARCHAR(10) NOT NULL,
     IDandPrefix AS ISNULL(StringPrefix + CAST(ID AS NVARCHAR(10)), 'X') PERSISTED
    )

Now when you insert rows like this:

INSERT INTO dbo.YourTable(StringPrefix) VALUES('A'), ('B'), ('A')

you should get rows like this:

ID   StringPrefix   IDandPrefix
 1        A             A1
 2        B             B2
 3        A             A3

And you can define your primary key on that IDandPrefix column, too:

ALTER TABLE dbo.YourTable 
ADD CONSTRAINT PK_YourTable PRIMARY KEY CLUSTERED (IDandPrefix)

Add auto increment with labeled primary key to existing table in SQL Server and H2 / Liquibase

Unfortunately, H2 1.4.200 doesn't yet emulate non-standard IDENTITY clause from SQL Server well enough and SQL Server doesn't support standard identity columns (ID BIGINT GENERATED BY DEFAULT AS IDENTITY etc.) supported by H2 and various other DBMS.

You need to compile H2 from its current sources available on GitHub:
https://github.com/h2database/h2database

There are build.cmd and build.sh scripts in h2 subdirectory. You need to launch a proper script for you OS with jar argument (build jar or ./build.sh jar). Use Java 8 or 11 (compiled jar will be compatible with more recent versions too).

With the compiled snapshot version you will be able to use

CREATE TABLE TEST1(ID BIGINT IDENTITY, V INT);

CREATE TABLE TEST2(V INT);
ALTER TABLE TEST2 ADD ID BIGINT IDENTITY;

and similar commands in MSSQLServer compatibility mode (append ;MODE=MSSQLServer to JDBC URL) and these commands will not create unexpected primary key constraints.

---

Related Topics