Incrementing primary key value

Using numeric autoincrement identity columns create a ton of problems. I touch of some of them in this article especially for tables that heavily modified. That article covers Oracle and PostgreSQL, so I won't repost the DDL.

For SQL Server you would need something like this.

/****** Object:  Table [dbo].[Issuer]    Script Date: 02/18/2010 08:36:57 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Issuer](
    [ID] [uniqueidentifier] ROWGUIDCOL  NOT NULL,
    [Name] [nvarchar](50) NOT NULL,
 CONSTRAINT [PK_Issuer] PRIMARY KEY CLUSTERED 
(
    [Name] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY],
 CONSTRAINT [IX_Issuer] UNIQUE NONCLUSTERED 
(
    [ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Issuer] ADD  CONSTRAINT [DF_Issuer_ID]  DEFAULT (newid()) FOR [ID]
GO

As you can see, the row identity column is not the human meaningful data, thus, not what is going to be searched on most of the time. Therefore, the primary key is the human meaningful column "Name"


Additionally if you want to generate your IDs in code, IDENTITY columns require that you Enable Identity Insert. On the other hand, uniqueidentifier columns with the default as shown above mean you can insert your own value from Guid.NewGuid().

NOTE: Using any kind of sequential id generation in code (like MAX(ID)+1) could cause conflicts if multiple inserts are happening at the same time (in other words 2 processes or threads could generate the next id as 5). Where as using a Guid.NewGuid generates a pseudo-random number that has a 1 in 2¹²² chance of duplication.

Hi there,
During the design time of your data table in the database
Do the following.
1.click on the primary key field during design time
2. You will see the properties window for that perticular column below the table design surface.
3. Set the Identity value to Yes.
4. Set the Identity Increment to 1.

Hope it helps :-\

I always like to have GUID for my database Primary key.

But if you still like to have this, you might go for :

Identity(1,1) for your column.