Creating a "PERSISTED COMPUTED COLUMN" with date, time and SEQUENCE value

Question

I want to generate a column with auto generated value in pattern of MMddyyyyhhmmss0001

Here 0001 is SEQUENCE value

After a lot of search I found a solution

CREATE SEQUENCE [dbo].[BikePartIdHelper] 
AS [int]
START WITH 1
INCREMENT BY 1
MINVALUE 0
MAXVALUE 2147483647
CACHE 
GO    

CREATE TABLE [dbo].[BikeParts]
(
    [ID] [INT] IDENTITY(1,1) NOT NULL,
    [BikeParts_GUID]  AS ((REPLACE(CONVERT([VARCHAR](10), GETDATE(),(101)), '/', '') + REPLACE(CONVERT([VARCHAR], GETDATE(), (8)), ':', '')) + RIGHT(REPLICATE('0', (4)) + CONVERT([VARCHAR], [BikePart_ID]), (4))) PERSISTED,
    [BikePart_ID] [INT] NOT NULL,
    [BikePart_Name] [VARCHAR](100) NULL,

    CONSTRAINT [PK_BikeParts] 
        PRIMARY KEY CLUSTERED ([ID] ASC)
                    WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, 
                          IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, 
                          ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[BikeParts] 
    ADD DEFAULT (NEXT VALUE FOR [dbo].[BikePartIdHelper]) FOR [BikePart_ID]
GO

But I get this error from this code:

Msg 4936, Level 16, State 1, Line 11
Computed column 'BikeParts_GUID' in table 'BikeParts' cannot be persisted because the column is non-deterministic

Code is working fine if I remove PERSISTED, but in that case it will generate new value in every select statement, So I need to make it as PERSISTED.

It is not necessary to use computed column, If there are any alternatives for solving my purpose of generate value of column as MMddyyyyhhmmss0001

I will really thankful for any type of help.

What would be wrong with keeping things simple and just concatenating together the current timestamp and the primary key column? — Tim Biegeleisen, Aug 20 '19 at 06:09
Perhapse this helps: https://stackoverflow.com/questions/14124439/cannot-persist-computed-column-not-deterministic — Mario The Spoon, Aug 20 '19 at 06:11
Possible duplicate of [Cannot persist computed column - not deterministic](https://stackoverflow.com/questions/14124439/cannot-persist-computed-column-not-deterministic) — Mario The Spoon, Aug 20 '19 at 06:12

Zohar Peled · Accepted Answer · 2019-08-20T08:10:02.753

The reason the column definition is not persisted is because it relies on GetDate(), which is a non-deterministic function (A deterministic function will return the same value any time it's called with the same input, anything else is non-deterministic).

There is, however, a workaround - You can add another column to the table that it's default value would be GetDate() and base your computed column on it.

While I was doing that, I also took the liberty of simplifying your code a bit and name your default constraint for the BikePart_ID column (Best practice is to always name your constraints).

The following code tested on Rexstester and seem to be working fine:

CREATE SEQUENCE [dbo].[BikePartIdHelper] 
AS [int]
    START WITH 1
    INCREMENT BY 1
    MINVALUE 0
    MAXVALUE 2147483647
    CACHE 
GO    

CREATE TABLE [dbo].[BikeParts]
(
    [ID] [INT] IDENTITY(1,1) NOT NULL,
    [BikeParts_GUID] AS 
        REPLACE(CONVERT(CHAR(10), BikePart_CreateDate, 101), '/', '') + 
        REPLACE(CONVERT(CHAR(8), BikePart_CreateDate, 114), ':', '') + 
        RIGHT('0000' + CAST([BikePart_ID] As VARCHAR(4)), 4) PERSISTED,
    [BikePart_ID] [INT] NOT NULL CONSTRAINT DF_BikePart_ID DEFAULT (NEXT VALUE FOR [dbo].[BikePartIdHelper]),
    [BikePart_Name] [VARCHAR](100) NULL,
    [BikePart_CreateDate] DateTime CONSTRAINT DF_BikePart_CreateDate DEFAULT(GETDATE())

    CONSTRAINT [PK_BikeParts] 
        PRIMARY KEY CLUSTERED ([ID] ASC)
                    WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, 
                          IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, 
                          ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

One more thing to consider - if you are only going to use 4 digits in your computed column, there's no point of having the sequence go any higher than 9999 - you can also define it to recycle after it hits the max value.

score 0 · Answer 2 · answered Aug 20 '19 at 07:59

Add a column for the date value with getdate() as defaul and use that column in your computed persisted column.

create table T
(
  ID int identity primary key,
  Created datetime not null default getdate(),
  PersistedID as replace(
                 replace(
                 replace(convert(varchar(19), Created, 126), 
                 '-', ''), 
                 'T', ''), 
                 ':', '') + 
                 right('0000' + convert(varchar(4), ID), 4)
                 persisted
);


go

insert into T default values;

go

select *
from T;

Creating a "PERSISTED COMPUTED COLUMN" with date, time and SEQUENCE value

2 Answers2