How to set primary key auto increment

• raaz66

  Mr or Mrs. 500

  Points: 598

  More actions

  February 4, 2010 at 12:42 am

  #215231

  I want to create primary key column in my table,and i want to auto increment the primary key like

  a001,a002,a003.How it is possible?

• wschampheleer

  SSCertifiable

  Points: 5504

  More actions

  February 4, 2010 at 1:10 am

  #1113521

  You could use a trigger, but this also works:

  CREATE TABLE [dbo].[mytable] (

  [col1] [int] IDENTITY(1, 1)

  NOT NULL

  ,[col2] AS (ISNULL('a' + RIGHT('00' + CONVERT([varchar](10), [col1]), 3), 'a000'))

  ,CONSTRAINT [PK_mytable] PRIMARY KEY CLUSTERED ([col2] ASC))

  The downside is that in this case you have an extra column (col1).

  Regards,

  Willem
  http://wschampheleer.wordpress.com[/url]

• SQLRNNR

  SSC Guru

  Points: 281334

  More actions

  February 4, 2010 at 3:45 pm

  #1114056

  That is a good suggestion.

  My question is why create a primary key like this? Do you ever need to increment the alpha part of the PK?

  Have you considered a composite key?

  Rather than incrementing a PK in this fashion, would a GUID work for you?

  I would lean to the composite key though.

  Jason...AKA CirqueDeSQLeil
  _______________________________________________
  I have given a name to my pain...MCM SQL Server, MVP
  SQL RNNR
  Posting Performance Based Questions - Gail Shaw[/url]
  Learn Extended Events