Sql-server – How to add a autoincrement primary key in sql server with nvarchar

sql-serversql-server-2008sqldatatypes

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

Best Solution

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:

CREATE TABLE dbo.YourTable
    (ID INT IDENTITY(1,1) NOT NULL,
     StringPrefix NVARCHAR(10) NOT NULL,
     IDandPrefix AS ISNULL(StringPrefix + CAST(ID AS NVARCHAR(10)), 'X') PERSISTED
    )

Now when you insert rows like this:

INSERT INTO dbo.YourTable(StringPrefix) VALUES('A'), ('B'), ('A')

you should get rows like this:

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

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

ALTER TABLE dbo.YourTable 
ADD CONSTRAINT PK_YourTable PRIMARY KEY CLUSTERED (IDandPrefix)