Trying to create a computed column in SQL Server

Question

I am trying to create a computed column in SQL Server.

This is script to create column.

CREATE TABLE [dbo].[Invoice]
(
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [InvoiceID] AS (('INV' + FORMAT(GETUTCDATE(), 'yyyyMMdd')) + RIGHT('000000' + CONVERT([VARCHAR](20), [ID]), (7))),
    [Name] [nvarchar](50) NOT NULL,
    [CreatedOn] [datetime] NOT NULL 
         CONSTRAINT [DF_Invoice_CreatedOn]  DEFAULT (getutcdate())
) ON [PRIMARY]
GO

It will return a value for InvoiceId column like this:

ID  InvoiceID            Name    CreatedOn
--- -------------------- ------- -----------------------
1   INV201705090000001   amki    2017-05-09 13:11:06.790
2   INV201705090000002   amkit   2017-05-09 13:11:26.600
3   INV201705090000003   amkit3  2017-05-09 13:11:32.397
4   INV201705090000004   amkit6  2017-05-09 13:11:35.070

But I want to modify it more.

When server date changes, then again it start from 1.

This is my expected output.

ID  InvoiceID            Name    CreatedOn
--- -------------------- ------- -----------------------
1   INV201705090000001   amki    2017-05-09 13:11:06.790
2   INV201705090000002   amkit   2017-05-09 13:11:26.600
3   INV201705090000003   amkit3  2017-05-09 13:11:32.397
4   INV201705100000001   amkit6  2017-05-10 13:11:35.070
5   INV201705100000002   amkit6  2017-05-10 13:11:35.070
6   INV201705110000001   amkit6  2017-05-11 13:11:35.070

As you can see from above result, It again start from 1 where date change from 20170509 to 20170510.

If it can not be done using computed column, Then is there any other way I can achieve this.

I know there are many post related to how to create computed column. But I don't how to make it again start from 1 when date changes.

Answer 1

I'm with TheGameiswar on this one. I think a view (or simple query) would be best

Example

Select ID
      ,Invoice = concat('INV',convert(varchar(8),CreatedOn,112),right('0000000'+convert(varchar(5),Row_Number() over (Partition By convert(date,CreatedOn) Order by CreatedOn,ID)),7))
      ,Name
      ,CreatedOn
 From  YourTable

Returns

After Update