-1

I'm trying to create a (persisted) computed column on a table that is essentially:YEAR(DateColumn). When I try to directly call the YEAR() function inside the Formula of the Computed Column Specification, I get the SSMS error:

Error validating the formula for column 'ComputedColumnYear'.

Do you want to cancel your changes ?

Equivalent T-SQL:

ALTER TABLE CorpIX
ADD ComputedColumnYear AS (YEAR(DateColumn)) PERSISTED;

Error when using T-SQL:

Msg 1759, Level 16, State 0, Line 41 Computed column 'DateColumn' in table 'CorpIX' is not allowed to be used in another computed-column definition.

I verified that there are no computed columns for the CorpIX table with the following query on the sys views:

sys.columns 1

Or here's Solomon Rutzky's version of the query (just to show the table does exist, sorry for the column obfuscation):

sys.columns 2

  • Can you include the CREATE TABLE for CorpIX? – LowlyDBA Dec 16 '19 at 16:24
3

It sounds like it thinks DateColumn is already being used in a computed column, but that's not true. I checked the sys.columns system view for this table, and it has no computed columns at the moment.

I realize that you checked sys.columns to verify whether or not "DateColumn" was a computed column and it doesn't show as being one, so I am going to speculate that you are looking at either the wrong table, or the wrong field within sys.columns. I say this because:

  1. You most certainly can add a computed column using YEAR() (with or without PERSISTED)
  2. The error message indicates that the problem is that DateColumn is already a computed column, and you can't use a computed column in the definition of another computed column.

Simple test showing that you can use YEAR in a computed column, but you get the same error that you are getting now if you try to use a computed column in another computed column:

USE [tempdb];

-- DROP TABLE dbo.CorpIX;
CREATE TABLE dbo.CorpIX
(
  ID INT NOT NULL IDENTITY(1, 1),
  Something NVARCHAR(50) NOT NULL,
  [DateColumn] DATE NOT NULL,
  [NextYear] AS (DATEADD(YEAR, 1, [DateColumn]))
);

ALTER TABLE dbo.CorpIX
ADD [ComputedColumnYear] AS (YEAR([DateColumn])) PERSISTED;
-- Success


ALTER TABLE dbo.CorpIX
ADD [ComputedColumnNextYear] AS (YEAR([NextYear])) PERSISTED;
/*
Msg 1759, Level 16, State 0, Line XXXXX
Computed column 'NextYear' in table 'CorpIX' is not allowed to be used
   in another computed-column definition.
*/

I tested on SQL Server 2016 as that is what O.P. is using.

To check the existing column definitions, use the following query and just change the table name (and maybe also schema name) in the OBJECT_ID() function:

SELECT col.[name], col.[is_computed], cmp.[definition]
FROM   sys.columns col
LEFT JOIN sys.computed_columns cmp
       ON cmp.[object_id] = col.[object_id]
      AND cmp.[column_id] = col.[column_id]
WHERE  col.[object_id] = OBJECT_ID(N'dbo.CorpIX') -- change to your table name!
ORDER BY col.[column_id];

That query will list all columns for the table (which is why I didn't use only sys.computed_columns) and shows whether or not they are computed, and if computed, their definition.

|improve this answer|||||
  • 2
    Ah I realized my mistake. So I'll give you the win on this one, because your first statement was half right, I was looking at the wrong column but not when checking sys.columns, literally in my T-SQL when trying to create the computed column, so embarassing. :( But you pointed me in the right direction. Thanks! – J.D. Dec 16 '19 at 17:10
  • 2
    @J.D. Glad you got it fixed. No need to be embarrassed, we often see what we were expecting to see, hence one reason code reviews are so helpful :-) – Solomon Rutzky Dec 16 '19 at 17:17

Not the answer you're looking for? Browse other questions tagged or ask your own question.