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)

About these ads

7 thoughts on “SQL SERVER – Puzzle – Computed Columns Datatype Explanation

  1. This discussion is getting very interesting. The same precedence rules applicable for a CASE expression too.

    For example:
    DECLARE @var INT
    SELECT @var = 1

    SELECT
    CASE @var
    WHEN 1 THEN 1
    WHEN 2 THEN 5.0
    END AS Value
    /*
    Value
    —————————————
    1.0

    Returns 1.0 instead of 1 because the second case
    expression returns a float
    */

    btw: how do you define a TINYINT data type as UNSIGNED? I don’t find this documented: http://msdn.microsoft.com/en-us/library/aa933198(SQL.80).aspx

    Like

  2. Hi Dave,

    This Knowledge Sessions are quite helpful for us. we can take a break out of our Schedule to Reply & Learn somthin New…

    Keep it up Guru….

    I guess it would be option a) It will error out.

    Good Day…

    Like

  3. Pingback: SQL SERVER - Puzzle - Solution - Computed Columns Datatype Explanation Journey to SQL Authority with Pinal Dave

  4. Pingback: SQL SERVER – Weekly Series – Memory Lane – #048 | Journey to SQL Authority with Pinal Dave

  5. Pingback: SQL SERVER – Weekly Series – Memory Lane – #049 | Journey to SQL Authority with Pinal Dave

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s