SQL SERVER – Fix – Error 1759 Computed column in table is not allowed to be used in another computed-column definition

This is a very common error with beginning developers when they start working with computed columns.

When a new developer finds a joy of computed column, it is very natural to use the same computed column for other columns. However, a computed column cannot be used in another computed column or it will give an error.

Here is a simple reproduction of the scenario.

-- Create table
CREATE TABLE Table1 (Col1 INT, Col2 AS Col1*10);


Now we will add another computed column Col3 based of Col2 (which is computed column itself).

-- Create Column
ALTER TABLE Table1
ADD Col3 AS Col2*10;
When we execute above statement, it will give us following error.

Msg 1759, Level 16, State 0, Line 2
Computed column ‘Col2’ in table ‘Table1’ is not allowed to be used in another computed-column definition.

This is because we can not use computed columns in another computed column. This kind of referencing is not allowed in SQL Server.

Workaround:

Here is a quick workaround. Our end goal is to create another column which is multiply of Col2 by 10. Now Col2 is Col1 multiplied by 10. That means Col3 is Col1 multiplied by 100. Let us create a new column which is Col1 multiplied by 100.

-- Create Column
ALTER TABLE Table1
ADD Col3 AS Col1*100;

That’s it! We are done.

Reference: Pinal Dave (https://blog.sqlauthority.com)