Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 4 down vote favorite

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
share|improve this question
4  
Have you tried putting a CAST in there for the results? i.e. CAST(1 as BIT)? – JNK♦ Feb 8 '12 at 21:45
That did it, but SSMS changed it to CONVERT([bit], (0), (0)) seems to work well. Thanks. Post as an answer? – Nate Feb 8 '12 at 22:03
This seems a dubious candidate for a persisted computed column anyway IMO. Why are you making it PERSISTED? – Martin Smith Feb 8 '12 at 22:05
@MartinSmith I agree; but I know consumers will query on it, so I want to add an index on it. Maybe PERSISTED is not required for indexing? Maybe I misunderstood what I read about the limitations. – Nate Feb 8 '12 at 22:07
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 '12 at 22:08
show 2 more comments

2 Answers

up vote 5 down vote accepted

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

share|improve this answer
up vote 5 down vote

Or slightly simpler...

IsActive AS CONVERT(BIT, CASE ... END) PERSISTED
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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