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“. His comment led me to a small puzzle on computed columns datatype.

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

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.