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
Improve this question
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.
    – Martin Smith
    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.
    – Martin Smith
    Feb 8, 2012 at 22:10

3 Answers 3

Reset to default
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

Improve this answer
5

Or slightly simpler...

IsActive AS CONVERT(BIT, CASE ... END) PERSISTED
Improve this answer
-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

Improve this answer

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.