WayneS (4/20/2010)
SW_Lindsay (4/20/2010)
Nice and helpful article - Thanks. Just Curious...To select eligible candidates in the first select you say
select * from @Candidates where MeetsEligibility = convert(bit,1);
Why do you convert the 1 into a bit? just saying 1 works. I know that the data type for MeetsEligibility is a bit and I'm just curious if there are efficiencies is converting explicitly like this or is it just a readability thing?
Steve
Avoiding an "Implicit conversion". The literal 1 is an integer, resulting in the underlying field being converted to an integer to do the match... if there is an index on this field, it won't be used. Converting the 1 to a bit avoids the implicit conversion, and allows use of an index if one is present.
Personally, I wish there were system variables @@True and @@False of datatype bit, set to 1/0 respectively.
You can use 'TRUE' and 'FALSE' as quoted strings - http://msdn.microsoft.com/en-us/library/ms177603.aspx
Your implicit conversion comment is incorrect - as I understand it, the column is treated like any exact numeric from a query perspective. An index will be used if appropriate. That will primarily be determined by whether other non-columns are required (as in your example) and by the selectivity of the index.
Matt.