Comparing Bits and Ints

• Phil Parkin

  SSC Guru

  Points: 246963

  More actions

  January 2, 2015 at 7:44 am

  #293438

  No doubt this will prompt cries of "Who cares?" from some. To you, I apologise in advance 🙂

  Imagine some code like this:

  declare @IsTrue bit = 1;

  ...

  if @IsTrue = 1

  --Do stuff

  Presumably, there will be two implicit conversions going on here between Int and Bit.

  Would it be better to declare @IsTrue as, say, TinyInt? Or is that just forcing a different type of conversion (Int to TinyInt)?

  Or how about the snappy

  If @IsTrue = cast(1 as bit)

  option?

  Any opinions on which datatype to use for flags like this?

  The absence of evidence is not evidence of absence
  - Martin Rees
  The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
  - Phil Parkin

• Eirikur Eiriksson

  SSC Guru

  Points: 182912

  More actions

  January 2, 2015 at 8:18 am

  #1767395

  Quick thought, don't think that in this case it makes any difference, looked into this quite a while back and I couldn't find any implicit conversion in the execution plan.

  😎

• Phil Parkin

  SSC Guru

  Points: 246963

  More actions

  January 2, 2015 at 8:23 am

  #1767397

  Eirikur Eiriksson (1/2/2015)

  ---

  Quick thought, don't think that in this case it makes any difference, looked into this quite a while back and I couldn't find any implicit conversion in the execution plan.

  😎

  I did that too and you are right.

  But I think that may be because the execution plan does not include anything which is executing without hitting any tables.

  The absence of evidence is not evidence of absence
  - Martin Rees
  The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
  - Phil Parkin

• Steve Jones - SSC Editor

  SSC Guru

  Points: 736270

  More actions

  January 2, 2015 at 8:30 am

  #1767398

  I don't think there's an issue here. There might be a small conversion, but it can't be much. There can't be a "bit" in the register. It will load as 9 bits.

• Jeff Moden

  SSC Guru

  Points: 1004362

  More actions

  January 2, 2015 at 8:30 am

  #1767399

  I agree with Eirikur on this. I don't see an implicit conversion on the typical million row test and, not that you'd ever put an index on such a thing, it does do an index seek if you do.

  IIRC, such an implicit conversion existed way back in SQL Server 7 and they fixed it in the optimizer. Again, IIRC, it's part of the reason why the BIT datatype got such a bad rep.

  --Jeff Moden

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)
  

• Phil Parkin

  SSC Guru

  Points: 246963

  More actions

  January 2, 2015 at 8:39 am

  #1767405

  Steve Jones - SSC Editor (1/2/2015)

  ---

  I don't think there's an issue here. There might be a small conversion, but it can't be much. There can't be a "bit" in the register. It will load as 9 bits.

  It's not really an issue. Just a best-practice question.

  However I have seen problems when comparing a Bit column in a table to an Int, meaning that it was necessary to do something like this:

  join dbo.Component c on pc.ComponentID = c.ComponentID

  and c.Active = cast(1 as bit)

  So this got me wondering whether something similar might be happening when no tables are involved.

  The absence of evidence is not evidence of absence
  - Martin Rees
  The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
  - Phil Parkin

• Luis Cazares

  SSC Guru

  Points: 183706

  More actions

  January 2, 2015 at 8:42 am

  #1767407

  I just made a test and it doesn't seem to be an implicit conversion when using 1 or 0. It's different when you use 'true'.

  CREATE TABLE bitTest(

  somebit bit)

  INSERT INTO bitTest

  SELECT ABS(object_id) % 2

  FROM sys.all_columns

  SELECT *

  FROM bitTest

  WHERE somebit = 1

  SELECT *

  FROM bitTest

  WHERE somebit = 'True'

  GO

  DROP TABLE bitTest

  Luis C.
  General Disclaimer:
  Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

  How to post data/code on a forum to get the best help: Option 1 / Option 2

• Phil Parkin

  SSC Guru

  Points: 246963

  More actions

  January 2, 2015 at 8:47 am

  #1767410

  Thanks Luis

  On 2014, I get exactly the same execution plan for both 1 and 'true'. And no implicit conversions.

  --Edit

  Each statement seems to be converted to

  SELECT * FROM [bitTest] WHERE [somebit]=@1

  The absence of evidence is not evidence of absence
  - Martin Rees
  The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
  - Phil Parkin

• Phil Parkin

  SSC Guru

  Points: 246963

  More actions

  January 2, 2015 at 8:52 am

  #1767413

  Viewing the XML for the execution plan, there are references to the CONVERT_IMPLICIT() function.

  The absence of evidence is not evidence of absence
  - Martin Rees
  The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
  - Phil Parkin

• Luis Cazares

  SSC Guru

  Points: 183706

  More actions

  January 2, 2015 at 10:07 am

  #1767441

  This is what I get in the Predicate for each SELECT statement.

  SELECT *

  FROM bitTest

  WHERE somebit = 1

  --[Test].[dbo].[bitTest].[somebit]=[@1]

  SELECT *

  FROM bitTest

  WHERE somebit = 'True'

  --[Test].[dbo].[bitTest].[somebit]=CONVERT_IMPLICIT(bit,[@1],0)

  SELECT *

  FROM bitTest

  WHERE somebit = 112

  --[Test].[dbo].[bitTest].[somebit]=[@1]

  --No results

  Luis C.
  General Disclaimer:
  Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

  How to post data/code on a forum to get the best help: Option 1 / Option 2

• Eirikur Eiriksson

  SSC Guru

  Points: 182912

  More actions

  January 2, 2015 at 10:33 am

  #1767445

  Only the second query which uses 'true' as a predicate has an implicit conversion

  😎

  <Object Database="[tempdb]" Schema="[dbo]" Table="[bitTest]" IndexKind="Heap" Storage="RowStore" />

  <Predicate>

  <ScalarOperator ScalarString="[tempdb].[dbo].[bitTest].[somebit]=CONVERT_IMPLICIT(bit,[@1],0)">

  <Compare CompareOp="EQ">

  <ScalarOperator>

  <Identifier>

  <ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[bitTest]" Column="somebit" />

  </Identifier>

  </ScalarOperator>

  <ScalarOperator>

  <Identifier>

  <ColumnReference Column="ConstExpr1003">

  <ScalarOperator>

  <Convert DataType="bit" Style="0" Implicit="true">

  <ScalarOperator>

  <Identifier>

  <ColumnReference Column="@1" />

  </Identifier>

  </ScalarOperator>

  </Convert>

  </ScalarOperator>

  </ColumnReference>

  </Identifier>

  </ScalarOperator>

  </Compare>

  </ScalarOperator>

  </Predicate>

  Changing the statement using a variable eliminates the implicit conversion.

  DECLARE @TBIT BIT = 'true';

  SELECT *

  FROM bitTest

  WHERE somebit = @TBIT;

• Jeff Moden

  SSC Guru

  Points: 1004362

  More actions

  January 2, 2015 at 1:09 pm

  #1767470

  Phil Parkin (1/2/2015)

  ---

  Viewing the XML for the execution plan, there are references to the CONVERT_IMPLICIT() function.

  I only looked at the "properties" in the execution plan. I'll go back to my example and see what's in the XML for the execution plan tonight. I was also doing it on SQL Server 2008 (not R2).

  --Jeff Moden

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)
  

• ScottPletcher

  SSC Guru

  Points: 101103

  More actions

  January 7, 2015 at 1:01 pm

  #1768585

  I'm almost certain the optimizer is sophisticated enough to implicitly convert the literal value rather than a variable value or a column.

  If you're still concerned about it, you could do this:

  if @IsTrue = '1'

  SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

• Dwain Camps

  SSC Guru

  Points: 86948

  More actions

  January 8, 2015 at 3:10 am

  #1768716

  I will need to retain the INTriguing BIT[/i] of knowledge in this thread should it ever come INTo play for me.

  ---

  My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]

  My thought question: Have you ever been told that your query runs too fast?

  My advice:
  INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
  The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

  Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
  Since random numbers are too important to be left to chance, let's generate some![/url]
  Learn to understand recursive CTEs by example.[/url]
  [url url=http://www.sqlservercentral.com/articles/St