SQL Server - Create auto-incrementing unique key

Question

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.

Answer 1

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

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

Answer 2

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.