CODEWITHSUNDEEP
primary-keysql-server-expressuniqueidentifiercreate-table
Paul Michaels
Paul Michaels

Reputation: 16685

SQL Server - Create auto-incrementing unique key

Is there a way in SQL Server to create a table with a primary key that auto-increments itself? I've been looking at the "UniqueIdentifier" type, but this doesn't seem to do what I expect.

Currently, I have something like this:

CREATE TABLE [dbo].[MyTable](
        [Date] [datetime] NOT NULL,
    [MyField1] [nchar](50) NOT NULL,
    [MyField2] [nvarchar](max) NULL,
    [Key] [uniqueidentifier] NOT NULL
) ON [PRIMARY]

Basically, I want KEY to start at 1 and just increment itself for each record.

Upvotes: 2

Views: 8605

Answers (2)

Paul Sasik
Paul Sasik

Reputation: 81429

You're looking for the IDENTITY property.

From the documentation:

Creates an identity column in a table. This property is used with the CREATE TABLE and ALTER TABLE Transact-SQL statements.

IDENTITY [ (seed , increment) ]

seed

Is the value that is used for the very first row loaded into the table.

increment

Is the incremental value that is added to the identity value of the previous row that was loaded.

You must specify both the seed and increment or neither. If neither is specified, the default is (1,1).

Btw, all of this is also easily achieved from Sql Server's mgmt UI. Just right click on your table and select design. Then select the proper column and set the IDENTITY property.

Upvotes: 5

Per Hornshøj-Schierbeck
Per Hornshøj-Schierbeck

Reputation: 15196

Define your primary key in the table create statement like this:

[Key] [int] IDENTITY(1,1) NOT NULL

Upvotes: 2

Related Questions