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 (http://blog.SQLAuthority.com)

About these ads

SQL SERVER – Puzzle – Computed Columns Datatype Explanation

Yesterday I wrote post about SQL SERVER – Get Answer in Float When Dividing of Two Integer. I received excellent comment from SQL Server MVP Jacob Sebastian. Jacob has clarified the concept which I was trying to convey. He is famous for his “behind the scene insight“.

When I read his comment, I realize another interesting concept which is related to same idea which is being discussed in this post. Let us read what Jacob says first.

Jacob Sebastian:

Nice post and something that is very much useful in the day-to-day programming life.

Just wanted to add to what is already explained, that, SQL Server automatically does a cast to the data type having the highest precedence. So the result of INT and INT will be INT, but INT and FLOAT will be FLOAT because FLOAT has a higher precedence. If you want a different data type, you need to do an EXPLICIT cast.

For example:

SELECT 100/3 AS Result
/*
Result
———–
33
*/

SELECT 100/3.0 AS Result
/*
Result
—————————————
33.333333
*/

The precedence of each data type is documented here: http://msdn.microsoft.com/en-us/library/ms190309(SQL.90).aspx

When I read his comment I realize the same concept is applied to when computed columns are created.

A computed column is computed from an expression that can use other columns in the same table. The expression can be a noncomputed column name, constant, function, and any combination of these connected by one or more operators. The expression cannot be a subquery. (Definition from BOL)

When column is added to table as computed column expression is specified for its definition. If expression is addition of two integers, the final answer of datatype INT. This is interesting concept. If more than two columns with datatype of TINYINT, computed columns from this table is also of datatype TINYINT. The unsigned range is 0 to 255. Storage size is 1 byte.

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.
b) It will convert it automatically to next upper range datatype (i.e. INT etc)
c) User workaround.

I will let all of you my readers answer this question. I will conver this concept in future articles, where I will explain how to create computed columns. If you select answer c) please post your workaround as well.

UPDATE : Solution to Puzzle – SQL SERVER – Puzzle – Solution – Computed Columns Datatype Explanation

Reference : Pinal Dave (http://blog.SQLAuthority.com)