Have you ever wondered how computed columns work in SQL Server? In this article, we will explore the concept of computed columns and discuss their datatype and limitations.

Before we dive into the details, let’s first understand what a computed column is. A computed column is a virtual column that is not physically stored in the database but is calculated based on the values of other columns in the table. Its value is determined by an expression or formula involving other columns.

In SQL Server, when creating a computed column, you do not specify its datatype explicitly. Instead, the datatype of the computed column is determined based on the datatypes of the columns involved in the expression. If the expression involves columns with different datatypes, the datatype hierarchy rules are followed to determine the resulting datatype.

For example, let’s consider a table with three columns: FirstCol, SecondCol, and ThirdCol. We want to create a computed column, ComputedCol, which is the result of adding FirstCol and SecondCol and then multiplying it by ThirdCol. Since all the columns involved in the expression are of datatype TINYINT, the resulting computed column will also be of datatype TINYINT.

However, it’s important to note that if the computed column’s value exceeds the range of the datatype, an arithmetic overflow error will occur. In such cases, we can use a workaround to change the datatype of the computed column to a higher range datatype, such as INT, to avoid the error.

Let’s take a look at an example. Suppose we have a table with the same columns as before, but this time we want to insert values that would result in a computed column value larger than the TINYINT range. If we try to insert the values (6, 6, 100), an arithmetic overflow error will occur because the computed column’s value would be 1200, which is outside the TINYINT range.

To solve this issue, we can use the CAST function to convert one of the columns involved in the expression to a higher range datatype, such as INT. By doing so, the computed column’s datatype will be escalated to INT, allowing it to store the resulting value of 1200.

Here’s an example of how the workaround can be implemented:

CREATE TABLE MyTable (
    ID TINYINT NOT NULL IDENTITY (1, 1),
    FirstCol TINYINT NOT NULL,
    SecondCol TINYINT NOT NULL,
    ThirdCol TINYINT NOT NULL,
    ComputedCol AS (FirstCol + SecondCol) * CAST(ThirdCol AS INT)
) ON [PRIMARY];

INSERT INTO MyTable (FirstCol, SecondCol, ThirdCol) VALUES (6, 6, 100);

SELECT * FROM MyTable;

DROP TABLE MyTable;

By using this workaround, we can overcome the limitation of SQL Server’s automatic assumption of the computed column’s datatype and ensure that it can accommodate the desired values.

Understanding computed columns and their datatypes is crucial for database developers who work with different datatype operations. It’s also worth noting that the same concept applies when using the CASE statement, which we will explore in future articles.

Thank you for reading this article. I hope you found it informative and helpful in understanding computed columns in SQL Server.