Ampersand (&) operator in a SQL Server WHERE Clause

by

& is the bitwise logical and operator – It performs the operation on 2 integer values. 

WHERE (sc.Attributes & 1) = 0

The above code checks to see if sc.Attributes is an even number. Which is the same as saying that the first bit is not set.

Because of the name of the column though: “Attributes”, then the “1” value is probably just some flag that has some external meaning.

It is common to use 1 binary digit for each flag stored in a number for attributes. So to test for the first bit you use sc.Attributes&1, to test for the second you use sc.Attributes&2, to test for the third you use sc.Attributes&4, to test for the fourth you use sc.Attributes&8, …

The = 0 part is testing to see if the first bit is NOT set.

Some binary examples: (== to show the result of the operation)

//Check if the first bit is set, same as sc.Attributes&1
11111111 & 00000001 == 1
11111110 & 00000001 == 0
00000001 & 00000001 == 1


//Check if the third bit is set, same as sc.Attributes&4
11111111 & 00000100 == 1
11111011 & 00000100 == 0
00000100 & 00000100 == 1

More Related Contents:

  1. How do I create a unique constraint that also allows nulls?
  2. How do I get list of all tables in a database using TSQL?
  3. Get first day of week in SQL Server
  4. What is the significance of 1/1/1753 in SQL Server?
  5. Cannot truncate table because it is being referenced by a FOREIGN KEY constraint?
  6. Escape a string in SQL Server so that it is safe to use in LIKE expression
  7. Insert Update trigger how to determine if insert or update
  8. SQL time difference between two dates result in hh:mm:ss
  9. What is the best way to truncate a date in SQL Server?
  10. Dynamic SQL (passing table name as parameter)
  11. SQL Server: Difference between PARTITION BY and GROUP BY
  12. How do you truncate all tables in a database using TSQL?
  13. Understanding SQL Server LOCKS on SELECT queries
  14. SQL Server: How to select all days in a date range even if no data exists for some days
  15. SQL – The conversion of a varchar data type to a datetime data type resulted in an out-of-range value
  16. TSQL: How to convert local time to UTC? (SQL Server 2008)
  17. T-SQL Dynamic SQL and Temp Tables
  18. Find non-ASCII characters in varchar columns using SQL Server
  19. How can I determine in SQL Server if a dateTime range overlaps another
  20. How do you change the datatype of a column in SQL Server?
  21. How to convert number of minutes to hh:mm format in TSQL?
  22. How do I select last 5 rows in a table without sorting?
  23. SELECT INTO a table variable in T-SQL
  24. T-SQL query to show table definition?
  25. How can I insert binary file data into a binary SQL field using a simple insert statement?
  26. Cannot find either column “dbo” or the user-defined function or aggregate “dbo.Splitfn”, or the name is ambiguous
  27. Include missing months in Group By query
  28. SQL SERVER – Understanding how MIN(text) works
  29. @@ERROR and/or TRY – CATCH
  30. How to calculate difference in hours (decimal) between two dates in SQL Server?

Leave a Comment