Category name:MSSQL

Mssql: Cannot use bitwise operators on varbinary or numeric

I want to perform bitwise operations on a varbinary colum. As it contains binary data I though that it would work but it didn’t

This example works: 

declare @a int
declare @b int

set @a = 1
set @b = 2

select @a | @b

But this wont:

declare @a varbinary(100)
declare @b varbinary(100)

set @a = 1
set @b = 2

select @a | @b

The same problem with numeric data type. So that currently leaves me without a mssql solution. I guess I have to fallback to code to perform the conversion that I need.

Change mssql isolation level to read uncommitted data

Sometimes you are debugging an application and want to query the database while the database connection still has not committed (or rollbacked) the transaction. So you fire up the query analyzer just to find out that you cannot see the modifications yet (ofcourse). Today a collegue of mine (Frank Bakker) found out that it is possible to read uncommitted data by altering the isolation level on a connection.



SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED


When you have executed this statement you can now see all modifications that are still in a transaction. Isnt that neat or what?


The thing is that I used this some years ago in some functionality that generates reports based on website visits. The query did a select statement and it wouldn’t be bothered if some additional records would have beed added to the table. You can get this behaviour by adding the keyword NOLOCK like the following example.



SELECT count(*)
FROM MyTable NOLOCK
GROUP BY MyCoolColumn


So NOLOCK and SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED are the same but the first (NOLOCK) is usualy best option as it is directly visible that the query needs a different isolation level. Setting the isolation level seperately can ofcourse increase readability is your query does a lot of joins though.


You can also do this in MySql in almost the same syntax as shown below.



SELECT count(*)
FROM MyTable(nolock)
GROUP BY MyCoolColumn


Happy ‘reading’…

  • Recent Posts
  • Recent Comments
  • Archives
  • Categories
  • Meta