How Do I Add A Autoincrement Primary Key In Sql Server With Nvarchar?

How make a auto increment primary key with nvarchar datatype in SQL Server 2008 Express? I want my output primary key like: Id Name A1 AAA A2 BBB Thank you

Solution 1:

You can't do this directly - what you can do is this:

• create an auto-increment column to handle the numeric part
• add a computed column that concatenates the string prefix and the number

So try something like this:

CREATETABLE dbo.YourTable
    (ID INTIDENTITY(1,1) NOTNULL,
     StringPrefix NVARCHAR(10) NOTNULL,
     IDandPrefix AS ISNULL(StringPrefix +CAST(ID AS NVARCHAR(10)), 'X') PERSISTED
    )

Now when you insert rows like this:

INSERTINTO dbo.YourTable(StringPrefix) VALUES('A'), ('B'), ('A')

you should get rows like this:

ID   StringPrefix   IDandPrefix
 1A             A1
 2B             B2
 3A             A3

And you can define your primary key on that IDandPrefix column, too:

ALTERTABLE dbo.YourTable 
ADDCONSTRAINT PK_YourTable PRIMARY KEY CLUSTERED (IDandPrefix)

Solution 2:

That's simple. You can't. Autoincrement only works for numeric types.

Instead, add an int or bigint autoincrement PK and use a computed column to generate the other nvarcharcolumns.

Finally, you can create a non-clustered index on your computed column.

Solution 3:

Another workaround would be to set the default for Id to a function that can derive the next value.

Function (SQL Server 2008):

CREATEFUNCTION [dbo].[fx_GetNextMyTableId] ()
        RETURNSvarchar(50)
        ASBEGINDECLARE@NextIdvarchar(50), @IdCountint, @Prefixvarchar(25), @NumberOfSuffixDigits tinyint
            Set@Prefix='A'Set@NumberOfSuffixDigits=1SELECT@IdCount=count(*) FROM dbo.MyTable
            SET@NextId=@Prefix+ REPLICATE('0', @NumberOfSuffixDigits- LEN(@IdCount+1)) +CAST (@IdCount+1ASvarchar)

            RETURN (@NextId)
        END

Function (SQL Server 2012):

CREATEFUNCTION [dbo].[fx_GetNextMyTableId] ()
        RETURNSvarchar(50)
        ASBEGINDECLARE@NextIdvarchar(50), @IdCountint, @Prefixvarchar(25), @NumberOfSuffixDigits tinyint
            Set@Prefix='A'Set@NumberOfSuffixDigits=1SELECT@IdCount=count(*) FROM dbo.MyTable
            SET@NextId=@Prefix+ FORMAT(@IdCount+1, REPLICATE('0', @NumberOfSuffixDigits))

            RETURN (@NextId)
        END

Set Default:

ALTER TABLE[dbo].[MyTable] ADD CONSTRAINT [DEFAULT_Id] DEFAULT [dbo].[fx_GetNextMyTableId]() FOR [Id]