Computed Column Cannot Be Persisted

Computed column '...' in table '...' cannot be persisted because the column is non-deterministic?

Functions must be decorated with the WITH SCHEMABINDING hint, otherwise SQL Server skips the validation of determinism (a performance optimization), and treats that default result as not being deterministic.

Cannot persist computed column - not deterministic

CONVERT([varchar](4),@Year,(0))+'-01-01' is being passed to a DATEDIFF call, in a position where a date is expected, forcing an implicit conversion to occur.

From the rules for deterministic functions:

CAST

Deterministic unless used with datetime, smalldatetime, or sql_variant.

CONVERT

Deterministic unless one of these conditions exists:

...

Source or target type is datetime or smalldatetime, the other source or target type is a character string, and a nondeterministic style is specified. To be deterministic, the style parameter must be a constant. Additionally, styles less than or equal to 100 are nondeterministic, except for styles 20 and 21. Styles greater than 100 are deterministic, except for styles 106, 107, 109 and 113.

Well, you're calling neither, but you're relying on an implicit conversion, which I'd expect to act like CAST. Rather than rely on this, I'd switch to using CONVERT and give a deterministic style parameter.

So, I'd do: CONVERT(datetime,CONVERT([varchar](4),@Year,(0))+'0101',112) in its place. Having done so, the function itself becomes deterministic

Computed Column cannot be Persisted

Add WITH SCHEMABINDING to the function like this:

ALTER FUNCTION [dbo].[FormatSSN]
(
@SSN    VARCHAR(9)
)
RETURNS CHAR(11)
WITH SCHEMABINDING
AS
BEGIN
  your stuff here
END

and then run this to verify:

IF OBJECTPROPERTY (OBJECT_ID(N'[dbo].[FormatSSN]'),'IsDeterministic') = 1
   PRINT 'Function is detrministic.'
ELSE IF OBJECTPROPERTY (OBJECT_ID(N'[dbo].[FormatSSN]'),'IsDeterministic') = 0
   PRINT 'Function is NOT detrministic'
GO

Works here.

Computed column 'BookID' in table 'Books' cannot be persisted because the column is non-deterministic

Simplest solution

I would add a CreateDate column thus:

ALTER TABLE dbo.Books
ADD CreateDate DATETIME NOT NULL
CONSTRAINT DF_Books_CreateDate
DEFAULT(GETDATE())
GO

then I would add computed column thus:

ALTER TABLE dbo.Books
ADD BookID AS  ('BID' + LTRIM(YEAR(CreateDate)) + '-' + RIGHT('0000' + LTRIM(ID), 4)) /*PERSISTED*/
GO

Computed column 'Month' in table cannot be persisted because the column is non-deterministic

The datename function can return different results dependant on the language of the logged in user hence is not deterministic. Marking a computed column as persisted means SQL Server stores the results of the computation which requires there to be exactly one result.

If you need this as persisted at all you can replace with a 12 branch case expression with the specific language you want used.

Although it would probably be all round better to have month(AppliedDate) as a persisted integer column and have the datename function in a non persisted computed column.

Edited to give example as per comments

CREATE TABLE #T
  (
     [AppliedDate] DATETIME,
     [Month] AS CASE MONTH([AppliedDate])
          WHEN 1 THEN 'January'
          WHEN 2 THEN 'February'
          WHEN 3 THEN 'March'
          WHEN 4 THEN 'April'
          WHEN 5 THEN 'May'
          WHEN 6 THEN 'June'
          WHEN 7 THEN 'July'
          WHEN 8 THEN 'August'
          WHEN 9 THEN 'September'
          WHEN 10 THEN 'October'
          WHEN 11 THEN 'November'
          WHEN 12 THEN 'December'
        END PERSISTED
  )  

  INSERT INTO #T([AppliedDate]) VALUES (GETDATE())

Hashbytes Computed column cannot be persisted because the column is non-deterministic

I got the solution. I modified HashBytes logic as below to get the required datetime format and also on the C# side, I am using default encode.

Select hashbytes('MD5', convert(varchar(200),(CONVERT(varchar(10),datestamp,126)+' '+CONVERT(VARCHAR(24),datestamp,114)),2))
from Events
Where DateStamp ='2016-06-30 12:19:35.257961'

Change an Existing Column to Computed and Persisted without dropping the table/column

I would think to approach this by:

1. Renaming the original column
2. Creating the new computed column that uses the original column and fails over to a computation if the original column has no value (is null).

Like:

-- === Setting up some data for testing
drop table if exists test_reports
create table test_reports (
  id int identity(1, 1),
  fiscal_year int,
  report_id varchar(15)
)
insert test_reports (fiscal_year, report_id) values (2122, 'RPT2122000001')
insert test_reports (fiscal_year, report_id) values (2122, 'RPT2122000002')
insert test_reports (fiscal_year, report_id) values (2122, 'RPT2122000010')
insert test_reports (fiscal_year, report_id) values (2122, 'RPT2122000011')
insert test_reports (fiscal_year, report_id) values (2122, 'RPT2122000101')
insert test_reports (fiscal_year, report_id) values (2122, 'RPT2122000102')

-- === Rename the existing column to something else because it will start to 
-- === contain nulls and the nulls may break existing code.
exec sp_rename 'test_reports.report_id', 'original_report_id'

-- === Add our new, computed column that checks the original column
-- === for a value and uses the original value, if available.  Otherwise,
-- === the computed column is an actual computation.
alter table test_reports add report_id as (
    coalesce(original_report_id,
             'RPT' + convert(varchar, fiscal_year) + right('000000' + convert(varchar, id), 6)))

insert test_reports(fiscal_year) values (2123)

select * from test_reports

---

Related Topics