Force computed column data type

[jrsansom]

Morning,

I have a computed column on a SQL2K5 table. The data type is defaulting to int however I would like it to be stored as type bit.

Is there anyway that I can force this to occur?

The code for the column is

case when [jobcode] like '%SR%' then (1) else (0) end

Many Thanks,
John

 

[AlexCuse]

This should work?

cast(case when [jobcode] like '%SR%' then 1 else 0 end as BIT)

Hope this helps,

Alex

Ignorance of certain subjects is a great part of wisdom

 

[jrsansom]

Hi AlexCuse,

Thank you for the response.

That will certainly force the resulting data type to be BIT, however does this then mean that the cast calculation is performed for each column value, thereby resulting in additional overhead when compared to being able to just specify the data type of the column.

Hope that makes sense

Cheers,
John

 

[AlexCuse]

It will, but I think that somehow this is less costly than computing the column in your query. Somehow, you need to cast the 1 and 0 to bit, so I don't think there is really any other option.

You could make the computed column PERSISTED if you are really worried about the performance, but I wouldn't worry too much about it. Are your queries running extra slow when you use the cast?

Hope this helps,

Alex

Ignorance of certain subjects is a great part of wisdom

 

[jrsansom]

Hi,

I have persisted the column to the database as the column will be counted on hundreds of times each month.

It took under a minute to build on 3 million records so no need to be concerned about performance I think.

Many Thanks,
John