Sql Server - Create a Custom Auto-Increment Field

SQL Server - Create a custom auto-increment field

The best solution is to use

• an ID INT IDENTITY(1,1) column to get SQL Server to handle the automatic increment of your numeric value
• a computed, persisted column to convert that numeric value to the value you need

So try this:

CREATE TABLE dbo.tblCompany
  (ID INT IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED,
   CompanyID AS 'S1501.' + RIGHT('000' + CAST(ID AS VARCHAR(3)), 3) PERSISTED,
   .... your other columns here....
  )

Now, every time you insert a row into tblCompany without specifying values for ID or CompanyID:

INSERT INTO dbo.tblCompany(Col1, Col2, ..., ColN)
VALUES (Val1, Val2, ....., ValN)

then SQL Server will automatically and safely increase your ID value, and CompanyID will contain values like S1501.001, S1501.002,...... and so on - automatically, safely, reliably, no duplicates.

Create a custom auto-increment field in SQL Server

You can keep two fields:
1. ID field - auto incremented on each record inserted
2. Varchar ID field - computed column

Try creating table like this:

CREATE TABLE PaperTable
(
    PaperID int IDENTITY (1,1) NOT NULL
 , PageNumber varchar(100)
  , PaperAlphaID AS Cast(Right(Year(getDate()),2) as varchar(2)) +'-'+ PageNumber
);

Result I got when I added "001" and "002" in as my paper number:

PaperID   PageNumber  PaperAlphaID
-------   ----------  ------------
  1         001          15-001
  2         002          15-002

You may use PaperID if you Paper number to be autogenerated. You will just then need to type cast and concate:

PaperAlphaID AS Cast(Right(Year(getDate()),2) as varchar(2)) +'-'+ Cast(PaperID  as varchar(50))

How to create a custom auto generated ID number for a primary key column?

If you want to make Product_No the primary key - just use this SQL syntax:

CREATE TABLE Product
(
     ID INTEGER IDENTITY(1,1) NOT NULL
          CONSTRAINT UC_Product_ID UNIQUE,
     Product_No AS RIGHT ('PDT0000' + CAST(ID AS VARCHAR(10)), 10) PERSISTED 
          CONSTRAINT PK_Product PRIMARY KEY CLUSTERED,
     Product_Name VARCHAR(50) NOT NULL
)

Now your column Product_No is the primary key for this table. You cannot combine the IDENTITY value column with the computed column specification into a single column - that's just not possible.

HOWEVER: even with this, and your sample data, this query:

SELECT * 
FROM Product

still returns this result:

ID  Product_No  Product_Name
------------------------------
1   PDT00001    Fish
2   PDT00002    Shrimp
3   PDT00003    Crab

But what is wrong with that output?? I don't quite understand why this is a problem in your case?

If you don't want (or create for) the ID column, just run this query:

SELECT Product_No, Product_Name
FROM Product

to get this result:

Product_No  Product_Name
------------------------------
PDT00001    Fish
PDT00002    Shrimp
PDT00003    Crab

maintaining a custom auto increment column

I ended up creating another table to store the last increment. In the trigger, inside a transaction, I select from the new table with hints (UPDLOCK, ROWLOCK).

---

table

Info (LastId int)

---

trigger - instead of insert

declare @nextId int;

begin tran t1

  set @nextId = (select top 1 LastId from Info with (UPDLOCK, ROWLOCK)) + 1;

  update Info set LastId = nextId;

commit tran t1

insert into [xtable] (XID)
select @nextId
from inserted [i] 

---

In SQL Server how do I generate an auto-increment primary key ID that consists of year , a special char and a sequential series no.?

Here is a complete solution. Please note that it's exactly the same as the duplicate I've marked - Database scheme, autoincrement - Just different details.

CREATE TABLE [dbo].[STUDENT]
(
    [ID] int identity(1,1) PRIMARY KEY,
    [Stud_LName] [varchar](100) NOT NULL,
    [Stud_FName] [varchar](100) NOT NULL,
    [Stud_MName] [varchar](100) NOT NULL
)
GO

CREATE FUNCTION dbo.GetSudentId
(
    @id int
)
RETURNS varchar(10)
AS
BEGIN
    RETURN Concat(Year(Getdate()), '-', RIGHT(Concat('0000', (SELECT COUNT(*) FROM STUDENT WHERE id < @Id)), 6))
END
GO

ALTER TABLE [dbo].[STUDENT]
    ADD Stud_ID AS (dbo.GetSudentId(Id))
GO

Please note that the primary key of the table must still be the identity column (as shown in the script) since the computed column can't be the primary key.

How to generate auto increment field in select query

If it is MySql you can try

SELECT @n := @n + 1 n,
       first_name, 
       last_name
  FROM table1, (SELECT @n := 0) m
 ORDER BY first_name, last_name

SQLFiddle

And for SQLServer

SELECT row_number() OVER (ORDER BY first_name, last_name) n,
       first_name, 
       last_name 
  FROM table1 

SQLFiddle

---

Related Topics