T-sql – Bitwise operations in TSQL

t-sql

Take the following code:

Declare @a integer
set @a = 0
set @a = @a | cast (1 as BINARy)
set @a = @a | cast (100 as BINARy)
select cast (@a as integer)

Now, shouldn't the output of this code be 5 ?

I have an integer, set it to 0, so in binary its all 0's

then I OR it with 1 , so it becomes 0..001

then I OR it with 100 in binary, so it should become 00..0101 which, in decimal is 5

The output I actually get is 101

EDIT: for more clarity, I'm trying to accomplish with TSQL what the following code does in C++

#include<stdio.h>
#include<iostream>
using namespace std;
int main()
{
int a;
a = 0;
a = a | 1;
cout<< a;
a = a | 0b100;
cout<<endl<<a;
}

Best Answer

decimal    binary
100     =  1100100
1       =  0000001
                     or
101     =  1100101

Related Solutions

T-sql – Filters Joins or Where clauses TSQL

There is no performance difference
Best practice is explicit JOIN and separate filters (your 2nd example)

Note:

This changes for OUTER JOINs because you'd change it to a inner join

The quick way:

SELECT a.colum
FROM
   A 
   LEFT JOIN 
   B  ON A.ID =  B.ID AND B.STATUS  = 1
WHERE
   a.foo = 'bar'

With separate filters:

SELECT a.colum
FROM
   A 
   LEFT JOIN 
   (SELECT * FROM B WHERE STATUS  = 1) B ON A.ID =  B.ID
WHERE
   a.foo = 'bar'

Sql-server – Bitwise operators affecting performance

Short circuiting does not always function the way you might expect in SQL Server. You really have very little say in what order your where clauses are evaluated; even if you do this filtering in a subquery or CTE first, the optimizer still might evaluate it in an order you don't expect. The only two options I've seen to possibly overcome this:

(a) dump the intermediate filtered results to a #temp table.

(b) try adding the bitwise operator to the ON condition, instead of the WHERE clause, and use FORCE ORDER to ensure that the joins are evaluated in the order that you write them. I don't understand the differences between your two queries, and I think the question contains a lot of unnecessary code, but I would try this variation:

FROM 
  dbo.admanAdvertiserAdGroup_tbl AS AA 
INNER JOIN 
  dbo.admanAdvertiserCreative_tbl AS AC 
    ON AA.AdvertiserAdGroupId = AC.AdvertiserAdGroupId 
    AND (AC.Dirty & @dirtyReason > 0)
INNER JOIN 
  dbo.admanMasterAdGroup_tbl AS MA  
    ON MA.MasterAdGroupId = AA.MasterAdGroupId 
...
WHERE AA.AdvertiserId = @AdvertiserId 
...
OPTION (FORCE ORDER);

(I've also taken the liberty to add dbo. to your table references, which you should always be using, and AS for readability. Sorry, I am a little meticulous about this.)

No guarantees, obviously, but worth a try. This is assuming that your conclusion about short circuiting is even correct: how did you come to this conclusion? Can you show the execution plans and what led you to short circuiting as a possible culprit? Is the bulk of the query dealing with way too many rows that theoretically should have been filtered out by the bitwise operator?

EDIT

I've blogged about this scenario: http://www.sqlperformance.com/2012/08/t-sql-queries/dry-principle

Best Answer

Related Solutions

T-sql – Filters Joins or Where clauses TSQL

Sql-server – Bitwise operators affecting performance

Related Question