How do I add string with auto increment value in SQL Server?

Question

How do I add a string with auto-increment value in SQL Server?

create table date (sno int 'emp_'+ identity(1,1))

I need following as output

emp_1
emp_2

Answer 1

1. Define your table with a normal INT IDENTITY column
2. Add a computed column that merges string and ID together:

Something like this:

    CREATE TABLE dbo.YourTable
       (ID INT IDENTITY(1,1),
        EmpID AS 'emp_' + CAST(ID AS VARCHAR(10)) PERSISTED, 
        ......
       )

This way, your ID column is a normal IDENTITY column, and EmpID will contain emp_1, emp_2, emp_3, ......, emp_42, ...

By making it a persisted computed column, the data is actually stored on disk and doesn't need to be re-calculated every time you access the table. Also, when persisted, you can easily put an index on that computed column, too

Answer 2

You can't use any string as autoincrement

Lets think you have a table

|- id -|- name -|
|-  1 -|- Utku -|
|-  2 -|- Gopi -|

Lets select them as emp_1,emp_2

SELECT CONCAT("emp_",id) AS emp_id,name
FROM table

Result:

|- emp_id -|- name -|
|-  emp_1 -|- Utku -|
|-  emp_2 -|- Gopi -|

Answer 3

Try using computed column MSDN

CREATE TABLE Yourtablename
(
    ID int IDENTITY (1,1) NOT NULL
    InvoiceID AS 'INV'+ right('000000' cast(ID as varchar(20),7)
);