{"id":1905,"date":"2014-11-18T00:00:00","date_gmt":"2014-11-14T00:00:00","guid":{"rendered":"https:\/\/test.simple-talk.com\/uncategorized\/bitwise-operations-in-t-sql\/"},"modified":"2021-09-29T16:21:32","modified_gmt":"2021-09-29T16:21:32","slug":"bitwise-operations-in-t-sql","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/databases\/sql-server\/t-sql-programming-sql-server\/bitwise-operations-in-t-sql\/","title":{"rendered":"Bitwise Operations in T-SQL"},"content":{"rendered":"
\n

Nowadays, you are unlikely to need any bit-level manipulation of data for general work in SQL Server, since most of the places where it was once necessary are no longer so. You can see some of the problems we used to face in deprecated parts of the system such as sys.syspermissions <\/a> and some system stored procedures (e.g. sys.sp_MSdependencies<\/b>) that used bitwise flags as parameters.  Since the introduction of the BIT Datatype to SQL Server in 2005, the last reason for doing routine bit manipulation,  economy of storage,  disappeared. Nowadays, the SQL Server Database Engine can index bit columns , and  optimize the storage of them  if you use enough of them in a table. If there are eight or fewer bit columns in a table, the columns are stored as one byte. If there are from nine up to sixteen  bit columns, the columns are stored as two bytes, and so on.  You can now do easily-SARGable queries on bit-fields. The practice of using bitmaps in tables for indicating sequence has been rendered unnecessary by the introduction of window functions. <\/p>\n

However, once in the while, it is useful:  Bitwise manipulation can solve some types of problem that are awkward to do any other way such as assessing check or parity bits in constraints, doing simple encryption or data-compression (or both at the same time!). It is also interesting when you are exploring the way that data is stored on disk<\/a> or investigating  the intimate secrets of the transaction log<\/a>.  Again, certain applications will throw up special cases that still require a bitmap; particularly where information is heavily sequential in nature, but this is rare. I once used a bitmap in SQL Server  for an effective algorithm for allocating seats, or blocks of adjacent seats  in a football stadium according to a number of different rules. Occasionally, you’ll find tables from ‘legacy’ databases that use bitmaps coded in integer or binary columns and which will need shredding. Yes, it still pays to keep up your knowledge of bit-manipulation.<\/p>\n

Datatypes and their representation or notation.<\/h1>\n

In any traditional storage system, there is a clear distinction between  the storage form of a datatype, its representation form and  its transmission form.  A datatype is usually stored in the compact form  of its binary value, ranging in size from byte, through various widths of integer and strings. Encryption and compression merely translates these values.  Datatype notations are string versions of the datatypes, in a format designed to be readable, and most computer languages have ‘format’ commands to do this conversion.  Although there is usually a clear distinction between the two,  there are hybrid types such as BCD, Binary-Coded-Decimal.  The ‘transmission’ representation of a datatype is an unambiguous international standard for transferring information between different systems, to be read by machines rather than humans. Here, in this article that aims to show bitwise manipulation, we will need to  tackle the topic of  converting between the storage form of  data, and its representation form as binary notation, a string comprising a series of ‘0’s and ‘1’s. The hex notation of the value of a datatype usually has a prefix ‘0x’.There is no general agreement on the binary equivalent<\/a>, so I leave it out in this article, though I have a liking for the ‘0b’ prefix<\/p>\n

An example of Bitwise manipulation<\/h1>\n

Just to kick off, here is an example of a ‘legacy’ SQL Server function that returns a bitmap: columns_Updated()<\/code>. It is only usable or relevant in a trigger, and  tells you what columns were updated (or inserted) in a table. It is used most often perform whatever trigger-based action you need only when particular relevant columns are updated.  <\/p>\n

If you have a column that is rarely changed, but, if it does, it requires  data manipulation elsewhere in the database, then it makes sense to do this. You know the rows that have changed from the INSERTED and DELETED temporary, memory-resident table. If there is a change in the value of data in  a particular column and row, you have to do something; maybe just an action to maintain referential integrity, or a complex check on the integrity of the new data that can’t be done in a constraint. <\/p>\n

The data in columns_Updated()<\/code>  is simply a bitmap of the columns. If a particular column in the map was altered, then the bit was set to 1. If you know, by patient experiment, which bit corresponds with each column, and you don’t make rash changes to the metadata, then you’re probably home and dry at this point until someone unwittingly inserts or deletes a column, and thereby changes the order!  Just to make things more interesting for you, Microsoft chose to make the bitmap a LittleEndian<\/a> one.  MSDN’s description of this is a classic<\/p>\n

‘COLUMNS_UPDATED returns one or more bytes that are ordered from left to right, with the least significant bit in each byte being the rightmost. The rightmost bit of the leftmost byte represents the first column in the table; the next bit to the left represents the second column, and so on. COLUMNS_UPDATED returns multiple bytes if the table on which the trigger is created contains more than eight columns, with the least significant byte being the leftmost.‘<\/span><\/i><\/p>\n

So that’s clear then. Mercifully there are some examples.<\/span><\/p>\n

Here is an example of using it to find out what columns have changed. We are using AdventureWorks, and I’ve chosen a table with more than sixteen values. You may need to temporarily disable the existing trigger to try this out.<\/span><\/p>\n

IF EXISTS (\n  SELECT * FROM sys.triggers WHERE name LIKE 'SalesOrderHeaderAudit' \n  ) --trigger names must be unique in the DB\n  DROP TRIGGER  sales.SalesOrderHeaderAudit \nGO\nCREATE TRIGGER Sales.SalesOrderHeaderAudit ON Sales.SalesOrderHeader\n\/**\nsummary:   >\n  This is a simple sample trigger that sends a list of the columns \n  affected by the UPDATE of a table (Sales.SalesOrderHeader) via\n  the message stream. It uses a temporary table because of a bug\n  in the way BIGINTs use bitwise operators, and to provide a safe\n  way of doing the string concatenation trick\nAuthor: Phil Factor\nRevision: 1.1\ndate: 10 November 2014\n**\/ \n \n AFTER UPDATE AS\nDECLARE @TheInt bigint, @ListOfColumns VARCHAR(8000), @Digits CHAR(16),\n        @end INT, @ii INT, @LittleEndianHex VARCHAR(64);\nDECLARE @temp TABLE (Name VARCHAR(2000),Bits bigint,Column_ID INT PRIMARY KEY);\nSELECT @LittleEndianHex=CONVERT(VARCHAR(100),columns_Updated(),2);--get the bitmap of cols\n\/*turn this wierd thing into a proper bitmask so it is easy to use (so column 1 is bit 1,\ncolumn 2 is bit 2, column 3 is bit 3 ...and so on... *\/\nSELECT @end=LEN (@LittleEndianHex);, because we work backwards from the end\nSELECT @Theint =0, @ii=@end, @digits='123456789ABCDEF'--0 (and any rubbish) is returned as 0!\nWHILE @ii>0\n  BEGIN\n  SELECT @TheInt=CHARINDEX(SUBSTRING(@LittleEndianHex,@ii-1,1),@digits)+(@TheInt*16);\n  SELECT @TheInt=CHARINDEX(SUBSTRING(@LittleEndianHex,@ii,1),@digits)+(@TheInt*16);\n  SELECT @ii-=2;\n  END\n--OK. We know what we are dealing with. A conventional bitmap. Now we simply join with\n-- the system table to get the names of the columns affected \nINSERT INTO @temp (Bits, name, column_ID)\n  SELECT CONVERT(bigint,POWER(2,Column_ID-1) & @TheInt ) AS bits,Name, column_ID \n  FROM sys.columns\n  WHERE OBJECT_NAME(OBJECT_ID) ='SalesOrderHeader';\n-- and produce a comma-delimited list.  \nSELECT @ListOfColumns=COALESCE(@ListOfColumns+',','')+Name \n  FROM @temp \n  WHERE Bits>0;\n  \n      PRINT @ListOfColumns\n<\/pre>\n
With a bit of extra code, this would give you a log of all the changes, with the before and after  values, but you can do this without the bitmap. The columns_Updated<\/code>() function is better for using to  implement column-based trigger actions, or for specifying actions to be executed only  when  one or more specified columns change.  <\/p>\n
Converting between Hex, decimal and binary notations of integers and varbinaries.<\/h1>\n
 Before you do any serious experimenting with bitwise manipulations in SQL Server, there are two  useful functions you’ll need for converting to and from binary notation. This is because the CAST and CONVERT functions  will convert HEXT to INT and INT to HEX, but will  do neither Octal nor  Binary notations. We’d like to see individual bits just to see what is happening with bitwise logic. One utility  converts an integer or varbinary  into a string binary-notation  representation, and there is another utility to convert back the other way, from  a string binary value to an BigInt.<\/p>\n
Although the functions pass BigInt, beware that the MSDN documentation lists only ints, smallints  and tinyints as being suitable for use with the bitwise operators. Although I found one or two very strange quirks,  Bigint Seems to work as you’d expect.<\/p>\n
 There is a complication with converting between HEX or Binary to decimal, or the other way. HEX or  binary has no intrinsic way of representing negative numbers. This is done by ‘Two’s Complement’. Although this makes  arithmetic easy, it complicates the conversion,  and can cause difficulties  when using maths functions to manipulate bits.  However, this is only used  with SMALLINTs, INTs and BIGINTs. Both ‘1111111111111111’ and ‘11111111111111111111111111111111’, for example,  mean -1, as does 64 ‘1’s;  because  they represent  SMALLINTs, INTs and BIGINTs accordingly. It will all become  clear by experiment.<\/p>\n
Firstly, we’ll create our functions that convert to or from the binary notation (e.g.  9 to  1001, or 1100 to 12).  We start with ToBinaryString<\/b>. We are able to make use of the conversion routine from a varbinary to hex to make this  easier and more versatile. If you are stuck with SQL 2005 or early versions of 2008, you’ll need to adopt the XML trick  that I’ve included, commented out.<\/p>\n
IF OBJECT_ID (N'ToBinaryString') IS NOT NULL\n  DROP FUNCTION ToBinaryString\nGO\nCREATE FUNCTION ToBinaryString\n      \/**\nsummary:  >\n Converts any type of integer that can be implicitly \n converted to a bigint into a binary string (e.g. 3 = 11)\nAuthor: Phil Factor\nRevision: 1.0\ndate: 20 May 2013\nexample:\ncode: |\n  Select dbo.ToBinaryString (CAST(0xFF AS tinyint))--11111111\n  Select dbo.ToBinaryString (8045)--1111101101101\n  Select dbo.ToBinaryString (0xFFFF)\n  Select dbo.ToBinaryString (0xAFDC1111)\nreturns:  >\n The number as a binary string\n**\/\n  (\n    @InputInteger VARBINARY(8)\n  )\nRETURNS VARCHAR(70)\n      AS\n  BEGIN\n  DECLARE @representation VARCHAR(70);\n  SELECT @Representation=\n        REPLACE(\n          REPLACE(\n            CONVERT(VARCHAR(64), @InputInteger, 2), --convert to HEX\n        '0','0000'),\n      '1','0001'); --you need to do 0 and 1 first, and separately. \n    --before 2008, one had to use this to get the hex conversion...\n    --select cast('' as xml).value('xs:hexBinary(sql:variable(\"@InputInteger\") )', 'varchar(64)');\n  SELECT @Representation=\n        REPLACE(@Representation,HexDigit,BinaryDigits)\n    FROM (\n      VALUES('2','0010'), ('3','0011'),('4','0100'),('5','0101'),('6','0110'),\n      ('7','0111'),('8','1000'), ('9','1001'),('A','1010'),('B','1011'),\n      ('C','1100'),('D','1101'),('E','1110'),('F','1111'))f(HexDigit,BinaryDigits);\n        \/* this simply replaces the ASCII HEX notation with its ASCII binary equivalent. I haven't performance-tested it to see if it is quicker or slower than the maths technique, but it is more like SQL! *\/\n  RETURN @Representation;\n  END\nGO---and we now finish off by executing some quick assertion tests to check\nIF (\n  SELECT dbo.ToBinaryString (3))<> '00000000000000000000000000000011'\n  RAISERROR('''ToBinaryString '' stopped working (1)',16,1);\nIF (\n  SELECT dbo.ToBinaryString (287))<> '00000000000000000000000100011111'\n  RAISERROR('''ToBinaryString'' stopped working (2)',16,1);\nIF (\n  SELECT dbo.ToBinaryString (56))<> '00000000000000000000000000111000'\n  RAISERROR('''ToBinaryString'' stopped working (3)',16,1);\nIF (\n  SELECT dbo.ToBinaryString (-170))<> '11111111111111111111111101010110'\n  RAISERROR('''ToBinaryString'' stopped working (4)',16,1);\nIF (\n  SELECT dbo.ToBinaryString (CONVERT(smallint,-1)))<> '1111111111111111'\n  RAISERROR('''ToBinaryString'' stopped working (5)',16,1);\nIF (\n  SELECT dbo.ToBinaryString (CONVERT(INT,-1)))<> '11111111111111111111111111111111'\n  RAISERROR('''ToBinaryString'' stopped working (6)',16,1);\nIF (\n  SELECT dbo.ToBinaryString (CONVERT(Bigint,-1)))<> (\n  SELECT REPLICATE('1',64))\n  RAISERROR('''ToBinaryString'' stopped working (7)',16,1);\nIF (\n  SELECT dbo.ToBinaryString (0xAFDC))<> '1010111111011100'\n  RAISERROR('''ToBinaryString '' stopped working (1)',16,1);\nGO\n\t<\/pre>\n
In order to convert the other way, faced, for example with a string of ‘0’s and ‘1’s, we need to  know what we want to convert to, and determine the shortest length of integer datatype that can accommodate the results.  and we have the complication of having to follow the twos complement convention where appropriate. <\/p>\n
IF OBJECT_ID (N'BinaryStringToInt') IS NOT NULL\n  DROP FUNCTION BinaryStringToInt\nGO\n  --and create BinaryStringToInt\nCREATE FUNCTION BinaryStringToInt\n      \/**\nsummary:  >\n Converts a string that is a binary number into a bigint (e.g. 3 = '11')\nAuthor: Phil Factor\nRevision: 1.0\ndate: 24 May 2013\nexample:\ncode: |\n  Select dbo.BinaryStringToInt('10101010') \n  Select dbo.BinaryStringToInt('1110'+'1111') \nreturns:  >\n the binary string as an integer\n**\/\n  (\n    @InputBinaryString VARCHAR(2000)\n  )\nRETURNS bigint\n      AS\n  BEGIN\n  DECLARE @ii INT, @len INT,@Output Bigint,\n    @length INT, @Negative bit,@CurrentBit INT;\n  SELECT @InputBinaryString=REPLACE(@InputBinaryString,' ','');\n    --remove any extra spaces\n  SELECT @Len=LEN(@InputBinaryString), @Output=0,@ii=1\n    --determine what sort of value it is\n  SELECT @length=CASE\n          WHEN @len=1 THEN 2000 --no two's complement\n          WHEN @len<=8 THEN 2000 --no two's complement\n          WHEN @len<=16 THEN 16 -- -2^15 (-32,768) to 2^15-1 (32,767)\n          WHEN @len<=32 THEN 32 -- -2^31 (-2,147,483,648) to 2^31-1 (2,147,483,647)\n          WHEN @len<=64 THEN 64 -- -2^63 (-9,223,372,036,854,775,808) to 2^63-1 \n    ELSE 2000 END; --no two's complement\n    --checkk to see if we have to do twos complement maths on it.\n  IF (@Len=@Length \n        AND @length IN (16,32,64))\n    IF SUBSTRING(@InputBinaryString,@ii,1)='1'\n    SELECT @Negative=1; --flag that we need to 'twos complement' \n  IF (@InputBinaryString NOT LIKE '%[^01]%')--check for errors\n    BEGIN --doing repeated multiplication to do the conversion\n    WHILE(@ii <= @len \n          AND @@Error=0) \n      BEGIN\n      SELECT @currentBit=CAST(SUBSTRING(@InputBinaryString,@ii,1) AS INT);\n      IF @negative<>0 \n        SELECT @CurrentBit= CASE @CurrentBit WHEN 0 THEN 1 \n        ELSE 0 END;\n      SELECT @Output = (@output*2)+@CurrentBit;\n      SELECT @ii = @ii + 1;\n      END\n    IF @negative<>0 \n      SELECT @Output = -(@Output+1); --twos complement \n    END\n  ELSE\n  SELECT @Output=NULL; --unknown or error\n  RETURN @output;\n  END\nGO --and do our unit 'assertion' test to check that nothing obvious is broken\nIF (\n  SELECT dbo.BinaryStringToInt ('10101010')) <> 170\n  RAISERROR('''BinaryStringToInt '' stopped working (1)',16,1);\nIF (\n  SELECT dbo.BinaryStringToInt ('1111111111111111')) <> -1\n  RAISERROR('''BinaryStringToInt '' stopped working (2)',16,1);\nIF (\n  SELECT dbo.BinaryStringToInt ('11111111111111111111111111111111')) <> -1\n  RAISERROR('''BinaryStringToInt '' stopped working (3)',16,1);\nIF (\n  SELECT dbo.BinaryStringToInt ('100011111'))<> 287\n  RAISERROR('''BinaryStringToInt'' stopped working (4)',16,1);\nIF (\n  SELECT dbo.BinaryStringToInt ('111111111'))<> 511\n  RAISERROR('''BinaryStringToInt'' stopped working (5)',16,1);\nIF (\n  SELECT dbo.BinaryStringToInt ('NotAnumber'))IS NOT NULL\n  RAISERROR('''BinaryStringToInt'' stopped working (6)',16,1);\nIF (\n  SELECT dbo.BinaryStringToInt (NULL))IS NOT NULL\n  RAISERROR('''BinaryStringToInt'' stopped working (7)',16,1);\nIF (\n  SELECT dbo.BinaryStringToInt ('10000000000000000000000000000000')) \n              <> -2147483648\n  RAISERROR('''BinaryStringToInt'' stopped working (8)',16,1);\nIF (\n  SELECT dbo.BinaryStringToInt ('10001000100010001000100010001000'))\n             <> -2004318072\n  RAISERROR('''BinaryStringToInt'' stopped working (9)',16,1);\nGO\n<\/pre>\n
The third utility that we need for doing bitwise manipulation is only of value in going from binary  notation to HEX. <\/p>\n
IF OBJECT_ID (N'BinaryStringToHex') IS NOT NULL\n  DROP FUNCTION BinaryStringToHex;\nGO\nCREATE FUNCTION BinaryStringToHex\n      \/**\nsummary:  >\n Converts a string that is a binary number into a Hex string\nRevision: 1.0\ndate: 20 June 2013\nexample:\ncode: |\n  Select dbo.BinaryStringToHex('10101010') \n  Select dbo.BinaryStringToHex('10001000100010001000100010001000') \nreturns:  >\n the binary string as a hex string\n**\/\n  (\n    @InputBinaryString VARCHAR(2000)\n  )\nRETURNS VARCHAR(252)\n      AS\n  BEGIN\n  DECLARE @ii INT, @len INT,@Output Bigint, @byte tinyint,@Hex VARCHAR(100),\n    @length INT, @Negative bit, @CurrentBit INT;\n  SELECT @InputBinaryString=REPLACE(@InputBinaryString,' ','');\n  SELECT @Len=LEN(@InputBinaryString), @Output=0, @ii=1;\n  SELECT @length=CASE\n          WHEN @len<=4 THEN 4 -- NIBBLE \n          WHEN @len<=8 THEN 8 -- TINYINT \n          WHEN @len<=16 THEN 16 -- smallint\n          WHEN @len<=32 THEN 32 -- int\n          WHEN @len<=64 THEN 64 -- Bigint \n    ELSE 2000 END --crikey\n  IF (@Length <> 2000)\n    SELECT @InputBinaryString=REPLICATE('0',@Length-@len)+@InputBinaryString\n  ELSE \n  IF @Len % 4 >0 --round up to a 4-bit boundary \n    SELECT @InputBinaryString=REPLICATE('0',4-(@len % 4))+@InputBinaryString;\n  SELECT @byte=0, @ii=1;\n  IF (@InputBinaryString NOT LIKE '%[^01]%')--check for errors\n    WHILE @ii<=LEN(@InputBinaryString)\n    BEGIN --do each representation of a bit value in turn. \n    SELECT @currentBit=CAST(SUBSTRING(@InputBinaryString,@ii,1) AS INT);\n    SELECT @byte=(@byte*2)+@CurrentBit;\n      --Select @ii,@byte,@currentbit,@hex,@CurrentBit\n    IF @ii % 4=0 \n      SELECT @hex=COALESCE(@hex,'0x')+SUBSTRING('0123456789ABCDEF',@byte+1,1),@Byte=0;\n    SELECT @ii=@ii+1;\n    END\n  ELSE \n  SELECT @Hex=NULL;\n  RETURN @hex;\n  END\nGO\n  --here we do our assertion tests as a first-line check of brokenness\nIF (\n  SELECT dbo.BinaryStringTohex ('1')) <> '0x1'\n  RAISERROR('''BinaryStringToHex '' stopped working (1)',16,1);\nIF (\n  SELECT dbo.BinaryStringTohex ('11111')) <> '0x1F'\n  RAISERROR('''BinaryStringToHex '' stopped working (2)',16,1);\nIF (\n  SELECT dbo.BinaryStringTohex ('1010101111001101')) <> '0xABCD'\n  RAISERROR('''BinaryStringToHex '' stopped working (3)',16,1);\nIF (\n  SELECT dbo.BinaryStringTohex ('1')) <> '0x1'\n  RAISERROR('''BinaryStringToHex '' stopped working (4)',16,1);\nIF (\n  SELECT dbo.BinaryStringTohex ('11111')) <> '0x1F'\n  RAISERROR('''BinaryStringToHex '' stopped working (5)',16,1);\nIF (\n  SELECT dbo.BinaryStringTohex ('BADSTRING')) IS NOT NULL\n  RAISERROR('''BinaryStringToHex '' stopped working (6)',16,1);\n<\/pre>\n
Now we’ve got that out of the way, we can demonstrate some of the operations that are possible.<\/p>\n
First off, the top bit of an integer (bit 32 in an INT) is different in that it represents the sign  bit. Our function can’t easily deal with this because it doesn’t know what sort of integer (Int, smallint or tinyint)  you’ve passed to it, so it determines the smallest integer  that can  accommodate  the  value.  It then checks to see if the ‘sign bit’ is set. This can produce results that are  counter-intuitive.<\/p>\n
SELECT dbo.BinaryStringToInt ('11111111111111111111111111111111')\n  --   -1  \nSELECT dbo.BinaryStringToInt ('1111111111111111111111111111111')\n  --    2147483647  \n<\/pre>\n
We can convert from decimal to hex without a special utility <\/p>\n
SELECT CONVERT(VARBINARY(8), 2147483647)--convert the result to HEX\n  --    0x7FFFFFFF\n  --Hex input is implicitly converted\nSELECT dbo.ToBinaryString (0xFFFFFFFF)\n  --11111111111111111111111111111111\n \n--if you are stuck on slightly old versions of SQL Server, you can still use this function\nIF OBJECT_ID (N'DecToHex') IS NOT NULL DROP FUNCTION DecToHex;\nGO\nCREATE FUNCTION DecToHex (@InputInteger VARBINARY(8)) RETURNS VARCHAR(64)\nAS BEGIN \n  RETURN CAST('' AS XML).value('xs:hexBinary(sql:variable(\"@InputInteger\") )', 'varchar(64)'); \nEND\nGO\nSELECT dbo.dectohex (12345)\n-- 00003039  \n<\/pre>\n
 We can easily see what can be stored in each of the different integer formats by doing a conversion  from a varbinary. This is because it truncates the excess.<\/p>\n
--and we can use up to 64 bit on a BIGINT\nSELECT LEN(dbo.ToBinaryString (0xFFFFFFFFFFFFFFFF));--64 bits\n  --so lets just check on what is available for each type\nSELECT CONVERT(VARBINARY(8), CONVERT(BIGINT, 0xFFFFFFFFFFFFFFFFFFFF));\n  --0xFFFFFFFFFFFFFFFF-64 bit\nSELECT CONVERT(VARBINARY(8), CONVERT(INT, 0xFFFFFFFFFFFFFFFFFFFF));\n  --0xFFFFFFFF--32 bit\nSELECT CONVERT(VARBINARY(8), CONVERT(SMALLINT, 0xFFFFFFFFFFFFFFFFFFFF));\n  --0xFFFF --16-bit \nSELECT CONVERT(VARBINARY(8), CONVERT(TINYINT, 0xFFFFFFFFFFFFFFFFFFFF));\n  --0xFF --a 8-bit byte\nSELECT CONVERT(VARBINARY(8), CONVERT(bit, 0xFFFFFFFFFFFFFFFFFFFF));\n  --0x01 --a 1-bit bit\nSELECT CONVERT(VARBINARY(8), 2147452927); --int to hex conversion\nSELECT dbo.toBinaryString(2147452927); -- int to binary notation    \n\t<\/pre>\n
Having seen the size of the different integer datatypes,  let’s now look at the bitwise operations.   we’ll start by demonstrating what happens when we use the various bitwise operators, using the utilities I’ve  introduced to you<\/p>\n
There are three that operate on two integer or binary expressions. These are & (Bitwise AND)| (Bitwise OR) and ^ (Bitwise Exclusive OR).<\/p>\n
~ (Bitwise NOT) Performs a bitwise logical NOT operation on a single integer value.<\/p>\n
We can now peer at what is going on at the bit level. If you experiment with different values, it  is a great way of getting a feel for what is going on.<\/p>\n
 We’ll run through examples using an INT integer.<\/p>\n
  --results of a bitwise AND. (try flipping bits to convince yourself)\nSELECT dbo.toBinaryString\n  (CONVERT\n    (INT, -- first we do a bitwise AND\n      ( dbo.BinaryStringToInt ('01111111111110111101111101111111')--AND this ...\n      & dbo.BinaryStringToInt ('01111111011111111000011111100111')--... with this ...\n      )                      -- 01111111011110111000011101100111 --to get this.\n    )\n  );\n  -- only where there were bits set in the same position in both numbers were the bits set \n  --results of using a bitwise OR. \nSELECT dbo.toBinaryString\n  (CONVERT\n    (INT, -- then we do a bitwise OR\n      ( dbo.BinaryStringToInt ('01111111111110111101111101111111')--OR this ...\n      | dbo.BinaryStringToInt ('01111111011111111000011111100111')--... with this ...\n      )                      -- 01111111111111111101111111111111 --to get this.\n    )\n  );\n  -- 01111111111111111101111111111111                   --to get this.\n  -- where there were bits set in the same position in either numbers were the bits set \n  --results of using a bitwise XOR (exclusine OR) \nSELECT dbo.toBinaryString\n  (CONVERT\n    (INT, -- and the last we do a bitwise XOR\n      ( dbo.BinaryStringToInt ('01111111111110111101111101111111')--XOR this ...\n      ^ dbo.BinaryStringToInt ('01111111011111111000011111100111')--... with this ...\n      )                      -- 00000000100001000101100010011000 --to get this.\n    )\n  );\n  --only where one but not both bits set in the that position in the numbers were the bits set \n  --useful for toggling individual bits\n \n--and finally theresults of using a bitwise NOT. \nSELECT dbo.toBinaryString \n   (Convert\n     (int, -- and the last we do a bitwise XOR\n      ~ dbo.BinaryStringToInt ('01111111111110111101111101111111')--NOT this ...\n      )                      -- 10000000000001000010000010000000 --to get this.\n   );\n--only where one but not both bits set in the that position in the numbers were the bits set \n \n<\/pre>\n
These are by no means the only useful ways of manipulating  bits. Beware, though, that you can easily get in a muddle with the effect of two’s complement  that is used to represent negative integers.<\/p>\n
You can shift left and right easily. You’ll get an ‘overflow’  error if you try to shift into the left-most bit.<\/p>\n
SELECT dbo.ToBinaryString ( --shift right\n        CONVERT(tinyint, dbo.BinaryStringToInt ('00100001')\/2) --shift this right\n  );                                          -- 00010000    to get this   \nSELECT dbo.ToBinaryString ( --shift left\n        CONVERT(tinyint, dbo.BinaryStringToInt ('00100001')*2) --shift this left\n  );                                          -- 01000010    to get this\nSELECT dbo.ToBinaryString ( --shift left\n        CONVERT(tinyint, dbo.BinaryStringToInt ('00100001')*4) --shift this left\n  );                                          -- 01000010    to get this\n  --What if you want to set a particular bit? Lets say you want to set the seventh bit?\nSELECT dbo.ToBinaryString (CONVERT (tinyint,POWER(2,7-1)))   --Set bit 7\n  -- 01000000    to get this\n  --so you can set any bits this way.\nDECLARE @BitToSet INT; \nSELECT @BitToSet =20;\nSELECT dbo.ToBinaryString (CONVERT (INT,POWER(2,@BitToSet-1)));\n  --00000000000010000000000000000000 if @BitToSet is set to 20\n  --What if you want to set several bits at once? Say bits 3, 4 and 10\nSELECT dbo.ToBinaryString (CONVERT (smallint,POWER(2,3-1) |POWER(2,4-1)|POWER(2,10-1)));\n  -- 0000001000001100\nGO\n<\/pre>\n
So, with the means to convert between decimal, hex and binary notations, we can more easily explore  data. With the bitwise operators we can do stuff. What stuff? Well, the following is very much illustrative, as it isn’t  of serious use but it shows the power of bitmaps. In our illustration, the entire algorithm becomes useless if the  bicycle company were to introduce many more colours. I’ve seen it used in the past more effectively for weekdays or  weeks of the year, which aren’t going to change much.  More damaging is the fact  that joins are likely to be inefficient with tables of any size.  <\/p>\n
 IF EXISTS (\n  SELECT * \n    FROM tempdb.information_schema.tables\n    WHERE TABLE_NAME LIKE '#colours%')\n  DROP TABLE #colours\nGO\nCREATE TABLE #colours(color_id INT IDENTITY,color VARCHAR(6), code INT not null default 0)\nINSERT INTO #colours(color,code)\n  SELECT x,0 \n  FROM (\n    VALUES\n    ('Aqua'),('Azure'),('Beige'),('Black'),('Blue'),('Brown'),('Cyan'),('Gold'),\n    ('Gray'),('Green'),('Ivory'),('Khaki'),('Lime'),('Maroon'),('Navy'),('Olive'),\n    ('Orange'),('Orchid'),('Pink'),('Plum'),('Purple'),('Red'),('Salmon'),('Sienna'),\n    ('Silver'),('Snow'),('Tan'),('Teal'),('Violet'),('White'),('Yellow'))f(x);\nUPDATE #colours SET code=POWER(2,color_id-1);--OK it isn't real data!\nALTER TABLE #colours ADD PRIMARY KEY (code)\nIF EXISTS (\n  SELECT * \n    FROM tempdb.information_schema.tables\n    WHERE TABLE_NAME LIKE '#bicycles%')\n  DROP TABLE #bicycles;\nCREATE TABLE #bicycles(BicycleName VARCHAR(20) PRIMARY KEY, colourmap INT);\nINSERT INTO #bicycles(BicycleName,colourmap)\n  SELECT x,CAST( RAND(CHECKSUM(NEWID()))*2147483647 AS INT) \n  FROM (\n    VALUES\n    ('Hercules'),('Tartar'),('Sussex'),('Canute'),('Saxon'),('Sondes'),('Tiger'),\n    ('Echo'),('Chaplin'),('Minerva'),('Nelson'),('Nile'),('Tweed'),('Undine'),\n    ('Clyde'),('Gem'),('Eagle'),('Falcon'),('Lion'),('Volcano'),('Vesuvius'),\n    ('Meteor'),('Swale'),('Magnus'),('Aeolus'),('Os'),('Brigand'),('Ruby'),\n    ('Aas'),('Adrian'),('Huz'),('Rose'),('Dawn'),('Bluebell'),('Reindeer'),\n    ('Enigma'),('Europa'),('Terrier'),('Belgravia'),('Lyons'),('Richmond'),\n    ('Jumbo'),('Gladstone'),('Special'),('Pioneer'),('Precursor'),('Cowes'),\n    ('Osborne'),('Newport'))f(x);\n  --find out what colours are available for each bicycle\nSELECT BicycleName,color \n  FROM #bicycles\n      INNER JOIN #colours\n      ON colourmap & code <>0\n  ORDER BY bicyclename;\n  \n  --find out what colours are not available for each bicycle\nSELECT BicycleName,color \n  FROM #bicycles\n      INNER JOIN #colours\n      ON colourmap & code =0\n  ORDER BY bicyclename;\n  \n  --How many colours are available for each bicycle\nSELECT BicycleName, COUNT(*) AS ColourCount \n  FROM #bicycles\n      INNER JOIN #colours\n      ON colourmap & code <>0\n  GROUP BY bicyclename\n  ORDER BY COUNT(*) DESC\n go--------------------------------------------------\n  \n  --now for the next trick we need fewer bicycles\nIF EXISTS (\n  SELECT * \n    FROM tempdb.information_schema.tables\n    WHERE TABLE_NAME LIKE '#bicycles%')\n  DROP TABLE #bicycles;\nGO\nCREATE TABLE #bicycles(BicycleName VARCHAR(20) PRIMARY KEY, colourmap INT)\nINSERT INTO #bicycles(BicycleName,colourmap)\n  SELECT x,CAST( RAND(CHECKSUM(NEWID()))*2147483647 AS INT) \n  FROM (\n    VALUES\n    ('Hercules'),('Tartar'),('Sussex')\n  )f(x);\n \n--what colours aren't chosen by any bicycle?\nDECLARE @bitmap INT=0 --sadly there is no OR aggregation is SQL\nSELECT @Bitmap=@bitmap | colourmap \n  FROM #bicycles;\nSELECT color \n  FROM #colours \n  WHERE @Bitmap & code = 0;\n<\/pre>\n
For some bitwise operations, it makes sense to have a helper table, though for our bicycle example,  it has proved unnecessary. Here is a simple way of creating the table. <\/p>\n
IF EXISTS (\n  SELECT * \n    FROM information_schema.tables\n    WHERE TABLE_NAME LIKE 'Bithelper')\n  DROP TABLE Bithelper\nGO\nCREATE TABLE BitHelper (\n TheBit INT IDENTITY,\n TheMask AS POWER(CONVERT(bigint,2),CONVERT(bigint,TheBit-1)) PERSISTED\n  )\nDECLARE @ii INT\nSELECT @ii=63\nWHILE @ii>0 \n  BEGIN \n  INSERT INTO BitHelper DEFAULT \n      VALUES; \n  SELECT @ii=@ii-1; END\n  \n  --and we can then check that it is OK\nSELECT dbo.ToBinaryString(TheMask) \n  FROM BitHelper ORDER BY TheBit\n<\/pre>\n
Conclusions<\/h1>\n
The evolution of SQL Server has gradually reduced the requirement for the database developer to be  familiar with data at BIT level  or to understand bitwise operations. It hasn’t  entirely removed the requirement, though.  Like a lot of the capabilities of SQL  Server, they are there when you need them and the requirement can come up unexpectedly.  I  still find myself occasionally needing to work with data at the machine level, and I keep my aging HP 16C programmers’  calculator close at hand. <\/p>\n
Just occasionally you’ll stumble over a  requirement!<\/p>\n<\/p><\/div>\n","protected":false},"excerpt":{"rendered":"
How can you, in T-SQL, get a list of columns that have changed within a trigger? How can you see what bits are set within a varbinary or integer? How would you pass a bitmap parameter to a system stored procedure? Phil Factor shows how, introduces the bitwise operations, illustrates ways of peeking at bit-level operations, and explains the concepts behind the coding of integer datatypes.…<\/p>\n","protected":false},"author":154613,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[143531],"tags":[4178,4150,4183,4252],"coauthors":[],"class_list":["post-1905","post","type-post","status-publish","format-standard","hentry","category-t-sql-programming-sql-server","tag-bi","tag-sql","tag-t-sql","tag-t-sql-programming"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/1905","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/users\/154613"}],"replies":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/comments?post=1905"}],"version-history":[{"count":4,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/1905\/revisions"}],"predecessor-version":[{"id":41096,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/1905\/revisions\/41096"}],"wp:attachment":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media?parent=1905"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/categories?post=1905"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/tags?post=1905"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/coauthors?post=1905"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}