Computed bit column that returns whether another column is null

Question

I try to have this computed column:

CREATE TABLE dbo.Item
(
    ItemId int NOT NULL IDENTITY (1, 1),
    SpecialItemId int NULL,
    --I tried this
    IsSpecialItem AS ISNULL(SpecialItemId, 0) > 0, 
    --I tried this
    IsSpecialItem AS SpecialItemId IS NOT NULL
    --Both don't work
)  ON [PRIMARY]

score 22 · Accepted Answer · edited Dec 06 '09 at 04:42

22

This works:

CREATE TABLE dbo.Item
(
    ItemId int NOT NULL IDENTITY (1, 1),
    SpecialItemId int NULL,
    IsSpecialItem AS
        CAST(CASE ISNULL(SpecialItemId, 0) WHEN 0 THEN 0 ELSE 1 END AS bit)
)

edited Dec 06 '09 at 04:42

Shimmy Weitzhandler

101,809
122
424
632

answered Dec 06 '09 at 04:35

Mark Byers

811,555
193
1,581
1,452

4

This only works when the column being tested is numeric. If SpecialItemId is nvarchar and has a value then this causes an error. See my other answer – Gary Barrett Jun 27 '12 at 09:07

score 19 · Answer 2 · edited May 23 '17 at 10:29

19

Mark Byer's answer causes an error with nvarchar columns, the following works regardless of whether column is int or nvarchar:

CREATE TABLE dbo.Item
(
    ItemId int NOT NULL IDENTITY (1, 1),
    SpecialItemId [nvarchar](50) NULL,
    CAST(CASE WHEN SpecialItemId Is NULL THEN 0 ELSE 1 END AS bit)
      AS IsSpecialItem 
)

edited May 23 '17 at 10:29

Community

1
1

answered Jun 27 '12 at 09:15

Gary Barrett

1,764
5
21
33

Also works with `varbinary` – The Thirsty Ape Sep 29 '22 at 20:51

score 2 · Answer 3 · answered Dec 06 '09 at 04:41

SQL Server doesn't have any native true boolean data type (in the sense that you could use a variable in place of a boolean expression, like select * from Item where IsSpecialItem). The only way you can represent it is with something like Mark suggests, using reserved values (in this case, your query would be select * from Item where IsSpecialItem = 1).

Computed bit column that returns whether another column is null

3 Answers3