Bitwise Logic To Make Values Negative

Kevin Feasel

2018-05-25

T-SQL

David Fowler is working with arcane magic:

The short answer to the question is to simply apply a bitwise NOT to the number that you want to convert and add 1 to the result.  I wrote about bitwise logic in The Arcane Science of Bitwise Logic and SQL Server but didn’t mention the bitwise NOT.

If you want to apply a bitwise NOT to a value, simply use the ~

Give it a go, try converting 10 to a negative,

SELECT ~ 10 + 1

The result, -10.

One of my rules of thumb (given how many I have, I need more thumbs) is that if you’re using bitwise logic in SQL Server, something has likely gone wrong somewhere along the way.  But read David’s explanation, which is quite clear.

Related Posts

Dealing With Large JSON Values

Bert Wagner investigates an issue he found where his long JSON strings were becoming NULL in SQL Server: After a little bit more research, I discovered that the return type for JSON_VALUE is limited to 4000 characters.   Since JSON_VALUE is in lax mode by default, if the output has more than 4000 characters, it fails silently. […]

Read More

Deleting Top Records With An Order By Clause

Kenneth Fisher shows that deleting the top N records with an ORDER BY clause is not straightforward: Did you know you can’t do this? DELETE TOP (10) FROM SalesOrderDetail ORDER BY SalesOrderID DESC; Msg 156, Level 15, State 1, Line 8 Incorrect syntax near the keyword ‘ORDER’. I didn’t. Until I tried it anyway. Turns […]

Read More

Categories

May 2018
MTWTFSS
« Apr Jun »
 123456
78910111213
14151617181920
21222324252627
28293031