21

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?

jrara
  • 16,239
  • 33
  • 89
  • 120

2 Answers2

28

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

Aaron Bertrand
  • 272,866
  • 37
  • 466
  • 490
  • 2
    If you want to maintain a non-null contract, you'll have to wrap this whole thing in `ISNULL( [CONVERT(..)], 0)` despite 1 or 0 never being null. – JoeBrockhaus Sep 01 '15 at 01:22
  • @Joe aside from what SSMS' flimsy designers show, what difference does it make? The only way you could get a non-NULL into the actual table is if someone modified the computed column definition (in which case, they are just as likely to remove the ISNULL() wrapper, since it probably won't seem logical to them for it to be there in the first place). – Aaron Bertrand Sep 01 '15 at 01:47
  • Not sure, maybe for some who use EF to pull the schema into an EDMX. And in my case (code-first + migrations + bit calculated), I was required to add the CONVERT/CAST because after data was entered, EF cried about trying to set a boolean from an INT. In one fell swoop I added the outer ISNULL, so I'm not sure if it would equally complain about nullability. Presumably no, since it seemed to be type inference (and it could never be null), but perhaps others will find it useful. I wouldn't have thought EF would complain about setting 1/0 as a bool either.. – JoeBrockhaus Sep 01 '15 at 14:09
16

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.

Community
  • 1
  • 1
Martin Smith
  • 438,706
  • 87
  • 741
  • 845
  • Quality answer as usual Martin. And congrats on breaking 6-digits (you will before I see your next comment). To think we were both around the same mark just 2 years ago :) Time flies. – RichardTheKiwi Sep 23 '12 at 20:21
  • @Richardakacyberkiwi - Thanks! Yes I remember you getting through 20K ahead of me. Was that really two years ago? Time flies indeed! – Martin Smith Sep 24 '12 at 12:03