How do I add a autoincrement primary key in sql server with nvarchar?

Question

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

Answer 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:

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)

Answer 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.

Answer 3

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

Function (SQL Server 2008):

        CREATE FUNCTION [dbo].[fx_GetNextMyTableId] ()
        RETURNS varchar(50)
        AS
        BEGIN
            DECLARE @NextId varchar(50), @IdCount int, @Prefix varchar(25), @NumberOfSuffixDigits tinyint
            Set @Prefix = 'A'
            Set @NumberOfSuffixDigits = 1

            SELECT @IdCount = count(*) FROM dbo.MyTable
            SET @NextId = @Prefix + REPLICATE('0', @NumberOfSuffixDigits - LEN(@IdCount + 1)) + CAST (@IdCount + 1 AS varchar)

            RETURN (@NextId)
        END

Function (SQL Server 2012):

        CREATE FUNCTION [dbo].[fx_GetNextMyTableId] ()
        RETURNS varchar(50)
        AS
        BEGIN
            DECLARE @NextId varchar(50), @IdCount int, @Prefix varchar(25), @NumberOfSuffixDigits tinyint
            Set @Prefix = 'A'
            Set @NumberOfSuffixDigits = 1

            SELECT @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]