Create column which increases from last index

Question

I need to generate a column for InvoiceID. and I want to keep the formate of this column like this

INV0000001,
INV0000002,
.
.
.
.
INV0000010,
INV0000011,
.
. and so on.

As you can see this column is increasing with last index. How can i do this.

I'm using SQL Server 2012.

I have searched, but couldn't find, how to increase a number like this.

Pரதீப் · Accepted Answer · 2015-07-17T06:16:16.740

6

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

SQLFIDDLE DEMO

For more info on why you need to make your computed column as persisted check here

edited Jul 17 '15 at 06:16

answered Jul 17 '15 at 04:58

Pரதீப்

91,748
19
131
172

2

+1 - If your table is used frequently in queries then you may want to weigh up the benefits of making the computed column persisted too. That way you won't incur the cost of the calculation for each query. It is a trade off of storage vs performance – Mr Moose Jul 17 '15 at 05:05
@Fireblade , just for some clarity. Won't the above script then increment with last digit but still have '000000' ? Example; 0000009 would increment to 00000010 instead of 0000010 ? – PKirby Jul 17 '15 at 05:06
@PKirby - No it wont thats why this part is used `right('000000' cast(ID as varchar(20),7)` – Pரதீப் Jul 17 '15 at 05:07
@MrMoose - Completely agree with you. Updated – Pரதீப் Jul 17 '15 at 05:09
@Fireblade , thanks for the clarity :) +1 – PKirby Jul 17 '15 at 05:10
@Fireblade: would you check your query again. I'm getting error, `CREATE TABLE Invoice ( ID int IDENTITY (1,1) NOT NULL, InvoiceID AS 'INV'+ right('000000' cast(ID as varchar(20),7) PERSISTED );` – Amit Kumar Jul 17 '15 at 05:18
@Fireblade:still getting same error, `Incorrect syntex near cast` – Amit Kumar Jul 17 '15 at 06:05
@AmitKumar - missed comma check now – Pரதீப் Jul 17 '15 at 06:05
@Fireblade: I have tried that. same problem . after putting `,`, it is saying right function requires 2 argument. as i can see, you are passing 2 argument but don't know why it is still giving this error. – Amit Kumar Jul 17 '15 at 06:08
@AmitKumar - Sorry dont have SSMS to validate my answer check now paranthesis was at wrong place. Try now – Pரதீப் Jul 17 '15 at 06:10
Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/83501/discussion-between-amit-kumar-and-fireblade). – Amit Kumar Jul 17 '15 at 06:13
@AmitKumar - Finally its working. Added demo too – Pரதீப் Jul 17 '15 at 06:17

score 1 · Answer 2 · answered Jul 17 '15 at 05:00

try, this one :- (completely dynamic)

Declare @NextInvoice    Int = 0

Select  @NextInvoice = Isnull(Max(Cast(Replace('INV0000011','INV','') As Bigint)),0) + 1

Select  'INV' + Left('0000000', (Len('0000000') -Len(@NextInvoice))) + Cast(@NextInvoice As Varchar(20))

score 0 · Answer 3 · answered Jul 17 '15 at 05:00

0

hope this will work for u

SELECT
    INV + right('000' + an_auto_increment_column, 3)  AS InvoiceID
    FROM yourTable

answered Jul 17 '15 at 05:00

Tanmay Nehete

2,138
4
31
42

Create column which increases from last index

3 Answers3

Linked