What is the data type of a computed column?
In addition, if you want to force a specific data type instead of relying on data type precedence, you can use CONVERT
or CAST
in the computation to force it (assuming all potential outcomes are compatible with the type you choose). This can be very useful in cases where, by default, you end up with a wider data type than you intended; the most common use case I've seen is when you end up with an INT instead of, say, a BIT:
Active1 AS CASE WHEN something THEN 1 ELSE 0 END,Active2 AS CONVERT(BIT, CASE WHEN something THEN 1 ELSE 0 END)
In this case Active1
is an INT
(4 bytes) while Active2
is a BIT
(1 byte - or less, potentially, if it is adjacent to other BIT
columns).
For a CASE
expression it is the branch with the highest datatype precedence. In your example all three branches are literals that are in the range that will be interpreted as integers so the type of the column will be int
You can use sql_variant_property
to determine what datatype a literal expression is as per my answer here. e.g. 2147483648
is interpreted as numeric(10,0)
rather than bigint
.
In the case in the question SQL Server recognises that the resultant column will be NOT NULL
but often for calculated expressions it is necessary to wrap the expression in an ISNULL
to get that effect.