Post reply

How to set primary key auto increment

  • 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?

  • 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]

  • 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

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply