SQL SERVER – Puzzle – Solution – Computed Columns Datatype Explanation

Just a day before I wrote article SQL SERVER – Puzzle – Computed Columns Datatype Explanation
which was inspired by SQL Server MVP Jacob Sebastian. I suggest that before continuing this article read original puzzle question SQL SERVER – Puzzle – Computed Columns Datatype Explanation.

The question was if computed column was of datatype TINYINT how to create Computed Column of datatype INT?

Before we continue with the answer let us run following script and understand how computed column is created.

USE AdventureWorks
GO
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)*ThirdCol
) ON [PRIMARY] GO
INSERT INTO MyTable
([FirstCol],[SecondCol] ,[ThirdCol])
SELECT 1,2,3
UNION
SELECT 2,3,4
UNION
SELECT 3,4,5
UNION
SELECT 4,5,6
UNION
SELECT 5,6,7
GO
SELECT *
FROM MyTable
GO
DROP TABLE MyTable
GO

If you see in above script ComputedCol is computed column and notice that user can not specify datatype of Computed Columns. Definition of computed column is given by using other columns of the table. (In our case ComputedCol is defined as adding of FirstCol to SecondCol, multiplied by ThirdCol.)

As we have seen we do not specify the datatype of the computed column, question comes to mind that what will be the datatype of the computed column. From previous article we have observed when there are operation between different datatypes it follows the datatype hierarchy.

When an operator combines two expressions of different data types, the rules for data type precedence specify that the data type with the lower precedence is converted to the data type with the higher precedence. If the conversion is not a supported implicit conversion, an error is returned. When both operand expressions have the same data type, the result of the operation has that data type. (From BOL). Read the precedence here.

As in computed column all the datatypes are TINYINT the computed column (ComputedCol) will be of TINYINT as well. Let us test the assumption here. Run following query where we try to insert such values in the first three columns its computed result will be larger than datatype TINYINT’s range. Range of TINYINT is from 0 to 255.

USE AdventureWorks
GO
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)*ThirdCol
) ON [PRIMARY] GO
INSERT INTO MyTable
([FirstCol],[SecondCol],[ThirdCol])
VALUES (6,6,100)
GO
/* Run only till here first.*/

/* Running following SELECT will give Error*/
SELECT *
FROM MyTable
GO
DROP TABLE MyTable
GO

While running above script we found that there was absolutely no issue till INSERT statement was ran. As soon as next SELECT statement ran it throwed following error.

(1 row(s) affected)
Msg 8115, Level 16, State 2, Line 4
Arithmetic overflow error converting expression to data type tinyint.

What this means is when SELECT statement is executed Computed Columns is built/calculated. This observation is interesting as well also makes sense. Computed Column is like addendum to any table and its definition can be changed easily. If computed column does not support any column type there should be no constraint on inserting in the original columns (if their datatype has no conflicts).

So far we have understood what is computed column and how it works and what is its limited. This also gives answer to our original puzzle.

Now the question is what happens when there are more than 256 TINYINT datetypes are as part of definition of the computed column?

Options are:

a) It will error Out. (Correct Answer)
b) It will convert it automatically to next upper range datatype (i.e. INT etc)
c) User workaround. (Preferred Solution)

Answer to our puzzle is it will error out but it would be really good if we use Workaround and we do not get limited with SQL Server’s automatic assumption of datatype for Computed Column. If it was regular column we would have changed the datatype of the column if arithmetic overflow had occurred. In case of the computed columns we do not specify datatype of the computed column when we create it.

The solution is very simple and it follow the same pricincipal of which we discussed earlier. When an operator combines two expressions of different data types, the rules for data type precedence specify that the data type with the lower precedence is converted to the data type with the higher precedence. We can convert one of the datatype of the our computed column definition to the datatype which we want and it can solve the problem.

In our example we will have 1200 value as final answer which is result of computation (6+6)*100. Datatype TINYINT can not store 1200 value but datatype INT can. Let us run following T-SQL script and understand workaround.

USE AdventureWorks
GO
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] GO
INSERT INTO MyTable
([FirstCol],[SecondCol],[ThirdCol])
VALUES (6,6,100)
GO
SELECT *
FROM MyTable
GO
DROP TABLE MyTable
GO

In our example in the definition of the computed column one of the columns datatype is converted to INT, as result whole computed column is converted or escalated INT.

This was one interesting puzzle to solve. The summary is very simple but keeping this concept in focus will help Database Developer who have to deal with different datatype operations.

My friend Jacob has also pointed out that this concept is also applied while using CASE statement. We will understand that concept with example in future articles. I suggest following related reading along with this article.

Download all the script used in this article.

SQL SERVER – Get Answer in Float When Dividing of Two Integer
SQL SERVER – Puzzle – Computed Columns Datatype Explanation
SQL SERVER – Puzzle – Solution – Computed Columns Datatype Explanation

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