6

I have just added a computed column to a table, and it is showing up as an int and I'd like to make it a bit is this possible? I'm having a hard time finding any info on this. Below is the computed column:

IsActive  AS case when DateDecommissioned is null then 0 else 1 end PERSISTED 
3
  • 1
    @Nate - It doesn't have to be persisted to be indexed unless it is imprecise/float or a CLR UDT. An index on a bit column won't be used unless heavily skewed. Are you on 2008? You could look into filtered indexes. Feb 8, 2012 at 22:08
  • @MartinSmith Good to know. So I can drop the PERSISTED and it will not impact index performance?
    – Nate
    Feb 8, 2012 at 22:09
  • 1
    @Nate - dropping PERSISTED just means that it won't take up space in the data page. Doesn't affect any other indexes that use it. Feb 8, 2012 at 22:10

3 Answers 3

9

You should be able to CAST to your preferred data type inside the CASE:

...IS NULL THEN CAST(0 as BIT) ELSE CAST(1 as BIT) END

5

Or slightly simpler...

IsActive AS CONVERT(BIT, CASE ... END) PERSISTED
-1

Try this:

ALTER TABLE dbo.MyTablr
ADD  DateDecommissioned AS (cast(Value1 AS bit) * cast(Value2 AS bit)) PERSISTED 

This way you can determine the Data Type on the fly while you create the computed column

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.