How to Fix the Invalid Column Name Error When Creating Computed Columns in SQL Server

Learn how to resolve the error message when creating computed columns in SQL Server by understanding the parsing process and properly structuring your SQL commands.
---

Visit these links for original content and any more details, such as alternate solutions, latest updates/developments on topic, comments, revision history etc. For example, the original title of the Question was: Error creating a computed column in SQL Server

If anything seems off to you, please feel free to write me at vlogize [AT] gmail [DOT] com.
---
Understanding the Error Creating a Computed Column in SQL Server

When diving into SQL Server and creating computed columns for the first time, many newcomers face the frustrating challenge of the dreaded Invalid column name error. If you've found yourself grappling with this issue, you're not alone. Today, we'll walk through the underlying problem and provide clear solutions to help you successfully create computed columns in SQL Server.

The Problem: What's Going Wrong?

Let's consider the scenario where you have a table set up, and you're attempting to add a computed column based on other existing columns. Here's a brief look at the setup:

[[See Video to Reveal this Text or Code Snippet]]

In this setup, everything looks good. You then aim to add two new columns, c and d, along with a computed column e that adds the values of columns c and d. The SQL commands you run might look something like this:

[[See Video to Reveal this Text or Code Snippet]]

However, you receive error messages like:

Invalid column name 'c'

Invalid column name 'd'

Why the Error Occurs

The core reason for this error lies in the SQL Server's parsing process. When you execute the statements, SQL Server composes them into a batch. During this compilation phase, it checks to see what objects or properties already exist. Unfortunately, columns c and d do not exist just yet in the current batch when it parses the ADD e AS c + d; statement.

As a result, SQL Server throws an error because it can't find the columns c and d that you attempted to reference in your computed column.

The Solution: How to Fix It

The good news is that there are straightforward methods to resolve this issue. Here are two effective approaches:

Method 1: Use Batch Separators

A simple way to fix this is to break your commands into separate batches using the GO statement. Here’s how you can structure your SQL commands:

[[See Video to Reveal this Text or Code Snippet]]

By adding GO, SQL Server treats each batch separately, allowing it to recognize the new columns c and d before it processes the computed column e.

[[See Video to Reveal this Text or Code Snippet]]

With this adjustment, SQL Server does not look for column c and d during the initial parsing of the batch, allowing you to successfully create your computed column e.

Conclusion

Now you're equipped with the knowledge to tackle computed column creation in SQL Server confidently! Happy coding!