If you’re getting an error that reads something like “Computed column ‘c2’ in table ‘t1’ is not allowed to be used in another computed-column definition” in SQL Server, it appears that you’re trying to create a computed column that uses another computed column.

We can’t use other computed columns in our computed column definitions.

To fix this issue, one option is to replicate the logic of the first computed column into the second. Another option is to create a view that implements the logic of the second computed column.

Example of Error

Here’s an example of code that leads to the error:

CREATE TABLE t1 (
    c1 int,
    c2 AS NEWID(),
    c3 AS LEFT( c2, 8 )
);

Output:

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

Here, my c3 column is trying to use the c2 column, but the c2 column is a computed column itself (hence the error).

Here’s another example that produces the error:

CREATE TABLE t2 (
    c1 int,
    c2 int,
    c3 AS c1 * c2,
    c4 AS c3 * 15,
);

Output:

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

Same issue. This time the c4 column is trying to make use of the c3 column, which itself is a computed column.

Solution 1

One option is to replicate the definition of the first computed column in our second one. However, this may or may not be successful, depending on what the first computed column does, and what your objective is.

In my case, this probably won’t work for my first table (t1), given it uses the NEWID() function. But the second table should be fine.

Let’s try it on t1 anyway:

CREATE TABLE t1 (
    c1 int,
    c2 AS NEWID(),
    c3 AS LEFT( NEWID(), 8 )
);

Output:

Commands completed successfully.

This time it ran successfully.

However, our celebrations will be short-lived.

Let’s insert data and select it:

INSERT INTO t1 VALUES (1);
SELECT * FROM t1
UNION ALL
SELECT * FROM t1;

Output:

c1  c2                                    c3      
--  ------------------------------------  --------
1   EF8488FD-C237-4DA2-9802-A16C8B4684CF  883421F4
1   2356CE44-C100-4F34-9AB3-4D5462AA7D87  F4822FAA

We can see that by replicating the logic of the first computed column, we also produced a new GUID with the NEWID() function. That’s because the function is nondeterministic – it produces a different result every time it’s called, even when given the same input. And we called it twice – once in the c2 and again in the c3 column. If the goal was to use the actual value produced by the first computed column, then this outcome fails. If that wasn’t the goal, then perhaps it’s acceptable.

Now let’s do the second example:

CREATE TABLE t2 (
    c1 int,
    c2 int,
    c3 AS c1 * c2,
    c4 AS (c1 * c2) * 15,
);

Output:

Commands completed successfully.

Now let’s insert data and select the contents:

INSERT INTO t2 VALUES (2, 3);
SELECT * FROM t2;

Output:

c1  c2  c3  c4
--  --  --  --
2   3   6   90

This one works fine.

Solution 2

Another option to solve the error is to move the second computed column’s logic to a view, then users can query the view instead of the table.

This will resolve the outstanding issue we have with the first table (where the first and second columns generate different GUIDs). It can also be used for the second table.

Let’s start with the first table:

-- Create the table
CREATE TABLE t1 (
    c1 int,
    c2 AS NEWID()
);
GO

-- Create the view
CREATE VIEW v1 AS
SELECT 
    c1,
    c2,
    LEFT( c2, 8 ) AS c3
FROM t1;
GO

Now let’s insert data and select it:

INSERT INTO t1 VALUES (1);
SELECT * FROM t1;
SELECT * FROM v1;

Output:

c1  c2                                  
--  ------------------------------------
1   A99DB531-9756-4716-B3EC-DE7B5F82E0EB


c1  c2                                    c3      
--  ------------------------------------  --------
1   3BC10BC2-2CE9-4950-8BD9-F7D992B88A21  3BC10BC2

The first result is the table and the second is the view.

This time the c3 column used the same GUID value that was produced by the c2 column (recall that in solution 1 it used a different GUID value).

Let’s use the view technique to fix the second example:

-- Create the table
CREATE TABLE t2 (
    c1 int,
    c2 int,
    c3 AS c1 * c2
);
GO

-- Create the view
CREATE VIEW v2 AS
SELECT 
    c1,
    c2,
    c3,
    c3 * 15 AS c4
FROM t2;
GO

Now let’s insert data and select it:

INSERT INTO t2 VALUES (2, 3);
SELECT * FROM t2;
SELECT * FROM v2;

Output:

c1  c2  c3
--  --  --
2   3   6 

c1  c2  c3  c4
--  --  --  --
2   3   6   90

Done.