-4

Can someone tell me what this WHERE clause means?

SELECT * 
FROM dbo.vw_EnrollSchedule 
WHERE (dbo.vw_EnrollSchedule.AdEnrollRegistrationStatus & 513) != 0

The vw_EnrollSchedule view has a column AdEnrollRegistrationStatus which is of integer datatype, NOT NULL column, and has a default value of zero, so will either be 0 or some value.

What does the

dbo.vw_EnrollSchedule.AdEnrollRegistrationStatus & 513

portion of the statement accomplish?

Just wondering about the syntax.

Trisped
  • 5,705
  • 2
  • 45
  • 58
NCC1701CAPT
  • 1
  • 4
    This is a [*bitwise and*](https://learn.microsoft.com/en-us/sql/t-sql/language-elements/bitwise-operators-transact-sql?view=sql-server-ver16) - presumably the column contains properties stored as bits. – Stu Aug 08 '23 at 16:15
  • 1
    In SQL Server 2022 you could write this as `GET_BIT(AdEnrollRegistrationStatus ,0) = 1 AND GET_BIT(AdEnrollRegistrationStatus ,9) = 1` - the `513` is `SELECT POWER(2,9)|POWER(2,0)` – Martin Smith Aug 08 '23 at 16:29
  • What does the `& 513` supposed to do? – Eric Aug 08 '23 at 19:24
  • 1
    Actually I just noticed my comment above has an error. It is checking `!=0` so it is just checking that **either** of those two bits are set - not that both of them are. So equivalent to `WHERE 1 IN (GET_BIT(AdEnrollRegistrationStatus ,0), GET_BIT(AdEnrollRegistrationStatus ,9))` – Martin Smith Aug 08 '23 at 19:29

1 Answers1

0

& is a logical AND operation. So it looks like it's testing some binary bits are set in the integer value. That's bit 1 and bit 10. The decimal value of that is 513 (512+1).

Applications sometimes use binary bits because you can pack 8 yes / no or true false values into each byte. Each T-SQL integer is usually 32 bits so that's quite a lot of flags you can pack into one single value.

The application will define what those bits actually signify however.

Michael
  • 11
  • 3
  • 3
    **bitwise** and **not** logical and! – Dale K Aug 08 '23 at 19:29
  • 2
    Or bits `0` and `9` as they are often numbered. ([LSb](https://en.wikipedia.org/wiki/Bit_numbering#Bit_significance_and_indexing) being bit `0`.) – HABO Aug 08 '23 at 19:54