What is the data type of a computed column?

Question

When you CREATE TABLE using CASE expression to create a computed column, you do not explicitly define the data type of this column:

CREATE TABLE OrderDetail ( OrderID INT , ProductID INT , Qty INT , OrderDate DATETIME , ShipDate DATETIME , STATUS AS CASE        WHEN shipdate is NULL AND orderdate < DATEADD( dd, -7, GETDATE()) THEN 3         WHEN shipdate is NOT NULL THEN 2         ELSE 1    end  ) GO 

How SQL Server decides the data type of this column?

Answer 1

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).