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

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

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

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)
);