SQL Server/T-SQL Tutorial/Data Types/bitwise operators

Материал из SQL эксперт
Перейти к: навигация, поиск

Bitwise AND (&)

   <source lang="sql">

Truth Table & 0 1 0 0 0 1 0 1 Finding Out if the First Bit Is Turned On, Behind the Scenes

 10101010 -- 170

&

 00000001 -- 1
 --------
 00000000 -- 0

Finding Out if the Second Bit is Turned On, Using T-SQL 18> SELECT 19> 170 & 2 20> GO


         2

(1 rows affected)</source>


Bitwise NOT (~)

   <source lang="sql">

Bitwise NOT (~) Truth Table Value ~ 0 1 1 0</source>


Bitwise NOT (~) Validity Table

   <source lang="sql">

Datatype ~ int valid binary error bit valid NULL NULL int stands for any of the following integer datatypes: tinyint, smallint, int, and bigint)</source>


Bitwise OR (|)

   <source lang="sql">

Bitwise OR (|) Truth Table | 0 1 0 0 1 1 1 1 Combining Multiple Flags Using the Bitwise OR Operator, Behind the Scenes

00000001 -- 1

|

 00000010 -- 2

|

 00000100 -- 4

|

 00001000 -- 8
 --------
 00001111 -- 1+2+4+8 = 15

Combining Multiple Flags Using the Bitwise OR Operator, in T-SQL 19> SELECT 20> 1 | 2 | 4 | 8 21> GO


        15

(1 rows affected)</source>


Exclusive Or (^)

   <source lang="sql">

Bitwise XOR (^) Truth Table ^ 0 1 0 0 1 1 1 0</source>


Legal Bitwise Operation

   <source lang="sql">

27> 28> SELECT 29> CAST(0x00000001 AS int) 30> & 31> 0x00000001 32> GO


         1

(1 rows affected)</source>


Retrieving Index Properties by Using the Bitwise AND (&) Operator

   <source lang="sql">

40> SELECT 41> object_name([id]) AS table_name, 42> [indid] AS index_id, 43> [name] as index_name, 44> status, 45> CASE 46> WHEN status & 2 = 2 THEN "Yes" 47> ELSE "No" 48> END AS is_unique, 49> CASE 50> WHEN status & 16 = 16 THEN "Yes" 51> ELSE "No" 52> END AS is_clustered, 53> CASE 54> WHEN status & 2048 = 2048 THEN "Yes" 55> ELSE "No" 56> END AS is_PK_CNS, 57> CASE 58> WHEN status & 4096 = 4096 THEN "Yes" 59> ELSE "No" 60> END AS is_UNQ_CNS 61> FROM 62> sysindexes 63> WHERE 64> indid BETWEEN 1 AND 254 -- clustered and nonclustered indexes 65> ORDER BY 66> table_name, 67> index_id 68> GO table_name

        index_id index_name
                          status      is_unique is_clustered is_PK_CNS is_UNQ_CNS


-------- ------------------------------------------------------------------------------------------------------
----------- --------- ------------ --------- ----------

MSreplication_options

               2 _WA_Sys_00000001_4460231C
                              8388704 No        No           No        No

queue_messages_1003150619

               1 queue_clustered_index
                                   18 Yes       Yes          No        No

queue_messages_1003150619

               2 queue_secondary_index
                                    2 Yes       No           No        No

queue_messages_1035150733

               1 queue_clustered_index
                                   18 Yes       Yes          No        No

queue_messages_1035150733

               2 queue_secondary_index
                                    2 Yes       No           No        No

queue_messages_1067150847

               1 queue_clustered_index
                                   18 Yes       Yes          No        No

queue_messages_1067150847

               2 queue_secondary_index
                                    2 Yes       No           No        No

spt_values

               1 spt_valuesclust
                                   18 Yes       Yes          No        No

spt_values

               2 ix2_spt_values_nu_nc
                                    0 No        No           No        No

spt_values

               3 _WA_Sys_00000001_4277DAAA
                              8388704 No        No           No        No

sysallocunits

               1 clust
                                   18 Yes       Yes          No        No

sysasymkeys

               1 cl
                                   18 Yes       Yes          No        No

sysasymkeys

               2 nc1
                                    2 Yes       No           No        No

sysasymkeys

               3 nc3
                                    2 Yes       No           No        No

sysbinobjs

               1 clst
                                   18 Yes       Yes          No        No

sysbinobjs

               2 nc1
                                    2 Yes       No           No        No

sysbinsubobjs

               1 clst
                                   18 Yes       Yes          No        No

sysbinsubobjs

               2 nc1
                                    2 Yes       No           No        No

syscerts

               1 cl
                                   18 Yes       Yes          No        No

syscerts

               2 nc1
                                    2 Yes       No           No        No

syscerts

               3 nc2
                                    2 Yes       No           No        No

syscerts

               4 nc3
                                    2 Yes       No           No        No

syschildinsts

               1 cl
                                   18 Yes       Yes          No        No

sysclsobjs

               1 clst
                                   18 Yes       Yes          No        No

sysclsobjs

               2 nc
                                    2 Yes       No           No        No

sysclsobjs

              11 _WA_Sys_00000002_00000040
                              8388704 No        No           No        No

sysclsobjs

              12 _WA_Sys_00000006_00000040
                              8388704 No        No           No        No

syscolpars

               1 clst
                                   18 Yes       Yes          No        No

syscolpars

               2 nc
                                    2 Yes       No           No        No

syscolpars

              11 _WA_Sys_00000002_00000029
                              8388704 No        No           No        No

syscolpars

              12 _WA_Sys_0000000B_00000029
                              8388704 No        No           No        No

syscolpars

              13 _WA_Sys_00000003_00000029
                              8388704 No        No           No        No

syscolpars

              14 _WA_Sys_0000000F_00000029
                              8388704 No        No           No        No

syscolpars

              15 _WA_Sys_0000000E_00000029
                              8388704 No        No           No        No

syscolpars

              16 _WA_Sys_00000006_00000029
                              8388704 No        No           No        No

sysconvgroup

               1 clst
                                   18 Yes       Yes          No        No

sysdbfiles

               1 clst
                                   18 Yes       Yes          No        No

sysdbfiles

              11 _WA_Sys_00000008_0000004C
                              8388704 No        No           No        No

sysdbfiles

              12 _WA_Sys_0000000C_0000004C
                              8388704 No        No           No        No

sysdbfiles

              13 _WA_Sys_0000000D_0000004C
                              8388704 No        No           No        No

sysdbfiles

              14 _WA_Sys_00000002_0000004C
                              8388704 No        No           No        No

sysdbreg

               1 clst
                                   18 Yes       Yes          No        No

sysdbreg

               2 nc1
                                    2 Yes       No           No        No

sysdbreg

               3 nc2
                                    2 Yes       No           No        No

sysdbreg

              11 _WA_Sys_00000004_0000001C
                              8388704 No        No           No        No

sysdbreg

              12 _WA_Sys_00000005_0000001C
                              8388704 No        No           No        No

sysdbreg

              13 _WA_Sys_00000006_0000001C
                              8388704 No        No           No        No

sysdercv

               1 cl
                                   18 Yes       Yes          No        No

sysdesend

               1 cl
                                   18 Yes       Yes          No        No

sysendpts

               1 clst
                                   18 Yes       Yes          No        No

sysendpts

               2 nc1
                                    2 Yes       No           No        No

sysftinds

               1 clst
                                   18 Yes       Yes          No        No

sysftinds

              11 _WA_Sys_00000003_00000019
                              8388704 No        No           No        No

sysguidrefs

               1 cl
                                   18 Yes       Yes          No        No

sysguidrefs

               2 nc
                                    2 Yes       No           No        No

syshobtcolumns

               1 clust
                                   18 Yes       Yes          No        No

syshobts

               1 clust
                                   18 Yes       Yes          No        No

sysidxstats

               1 clst
                                   18 Yes       Yes          No        No

sysidxstats

               2 nc
                                    2 Yes       No           No        No

sysidxstats

              11 _WA_Sys_00000004_00000036
                              8388704 No        No           No        No

sysidxstats

              12 _WA_Sys_00000002_00000036
                              8388704 No        No           No        No

sysidxstats

              13 _WA_Sys_00000009_00000036
                              8388704 No        No           No        No

sysidxstats

              14 _WA_Sys_00000008_00000036
                              8388704 No        No           No        No

sysiscols

               1 clst
                                   18 Yes       Yes          No        No

syslnklgns

               1 cl
                                   18 Yes       Yes          No        No

syslnklgns

              11 _WA_Sys_00000002_00000030
                              8388704 No        No           No        No

syslnklgns

              12 _WA_Sys_00000004_00000030
                              8388704 No        No           No        No

syslogshippers

               1 clst
                                   18 Yes       Yes          No        No

sysmultiobjrefs

               1 clst
                                   18 Yes       Yes          No        No

sysmultiobjrefs

               2 nc1
                                    2 Yes       No           No        No

sysmultiobjrefs

              11 _WA_Sys_00000001_0000004B
                              8388704 No        No           No        No

sysmultiobjrefs

              12 _WA_Sys_00000003_0000004B
                              8388704 No        No           No        No

sysmultiobjrefs

              13 _WA_Sys_00000005_0000004B
                              8388704 No        No           No        No

sysnsobjs

               1 clst
                                   18 Yes       Yes          No        No

sysnsobjs

               2 nc
                                    2 Yes       No           No        No

sysnsobjs

              11 _WA_Sys_00000002_0000002C
                              8388704 No        No           No        No

sysnsobjs

              12 _WA_Sys_00000004_0000002C
                              8388704 No        No           No        No

sysobjkeycrypts

               1 cl
                                   18 Yes       Yes          No        No

sysobjkeycrypts

              11 _WA_Sys_00000002_0000005E
                              8388704 No        No           No        No

sysobjkeycrypts

              12 _WA_Sys_00000003_0000005E
                              8388704 No        No           No        No

sysobjkeycrypts

              13 _WA_Sys_00000004_0000005E
                              8388704 No        No           No        No

sysobjvalues

               1 clst
                                   18 Yes       Yes          No        No

sysobjvalues

              11 _WA_Sys_00000002_0000003C
                              8388704 No        No           No        No

sysobjvalues

              12 _WA_Sys_00000003_0000003C
                              8388704 No        No           No        No

sysobjvalues

              13 _WA_Sys_00000004_0000003C
                              8388704 No        No           No        No

sysobjvalues

              14 _WA_Sys_00000005_0000003C
                              8388704 No        No           No        No

sysowners

               1 clst
                                   18 Yes       Yes          No        No

sysowners

               2 nc1
                                    2 Yes       No           No        No

sysowners

               3 nc2
                                    2 Yes       No           No        No

sysowners

              11 _WA_Sys_00000003_0000001B
                              8388704 No        No           No        No

sysprivs

               1 clust
                                   18 Yes       Yes          No        No

sysprivs

              11 _WA_Sys_00000002_0000001D
                              8388704 No        No           No        No

sysprivs

              12 _WA_Sys_00000004_0000001D
                              8388704 No        No           No        No

sysprivs

              13 _WA_Sys_00000005_0000001D
                              8388704 No        No           No        No

sysprivs

              14 _WA_Sys_00000006_0000001D
                              8388704 No        No           No        No

sysqnames

               1 clst
                                   18 Yes       Yes          No        No

sysqnames

               2 nc1
                                    2 Yes       No           No        No

sysremsvcbinds

               1 clst
                                   18 Yes       Yes          No        No

sysremsvcbinds

               2 nc1
                                    2 Yes       No           No        No

sysremsvcbinds

               3 nc2
                                    2 Yes       No           No        No

sysrmtlgns

               1 cl
                                   18 Yes       Yes          No        No

sysrmtlgns

              11 _WA_Sys_00000003_0000002F
                              8388704 No        No           No        No

sysrowsetcolumns

               1 clust
                                   18 Yes       Yes          No        No

sysrowsetrefs

               1 clust
                                   18 Yes       Yes          No        No

sysrowsets

               1 clust
                                   18 Yes       Yes          No        No

sysrts

               1 clst
                                   18 Yes       Yes          No        No

sysrts

               2 nc1
                                    2 Yes       No           No        No

sysrts

               3 nc2
                                    2 Yes       No           No        No

sysscalartypes

               1 clst
                                   18 Yes       Yes          No        No

sysscalartypes

               2 nc1
                                    2 Yes       No           No        No

sysscalartypes

               3 nc2
                                    2 Yes       No           No        No

sysschobjs

               1 clst
                                   18 Yes       Yes          No        No

sysschobjs

               2 nc1
                                    2 Yes       No           No        No

sysschobjs

               3 nc2
                                    2 Yes       No           No        No

sysschobjs

               4 nc3
                                    0 No        No           No        No

sysschobjs

              11 _WA_Sys_00000006_00000022
                              8388704 No        No           No        No

sysschobjs

              12 _WA_Sys_00000008_00000022
                              8388704 No        No           No        No

sysschobjs

              13 _WA_Sys_00000003_00000022
                              8388704 No        No           No        No

sysserefs

               1 clust
                                   18 Yes       Yes          No        No

syssingleobjrefs

               1 clst
                                   18 Yes       Yes          No        No

syssingleobjrefs

               2 nc1
                                    2 Yes       No           No        No

syssingleobjrefs

              11 _WA_Sys_00000001_0000004A
                              8388704 No        No           No        No

syssingleobjrefs

              12 _WA_Sys_00000003_0000004A
                              8388704 No        No           No        No

syssqlguides

               1 clst
                                   18 Yes       Yes          No        No

syssqlguides

               2 nc1
                                    2 Yes       No           No        No

syssqlguides

               3 nc2
                                    2 Yes       No           No        No

systypedsubobjs

               1 clst
                                   18 Yes       Yes          No        No

systypedsubobjs

               2 nc
                                    2 Yes       No           No        No

sysusermsgs

               1 clst
                                   18 Yes       Yes          No        No

sysusermsgs

              11 _WA_Sys_00000002_0000002D
                              8388704 No        No           No        No

sysusermsgs

              12 _WA_Sys_00000003_0000002D
                              8388704 No        No           No        No

syswebmethods

               1 clst
                                   18 Yes       Yes          No        No

sysxlgns

               1 cl
                                   18 Yes       Yes          No        No

sysxlgns

               2 nc1
                                    2 Yes       No           No        No

sysxlgns

               3 nc2
                                    2 Yes       No           No        No

sysxlgns

              11 _WA_Sys_00000005_0000002A
                              8388704 No        No           No        No

sysxmitqueue

               1 clst
                                   18 Yes       Yes          No        No

sysxmlcomponent

               1 cl
                                   18 Yes       Yes          No        No

sysxmlcomponent

               2 nc1
                                    2 Yes       No           No        No

sysxmlfacet

               1 cl
                                   18 Yes       Yes          No        No

sysxmlplacement

               1 cl
                                   18 Yes       Yes          No        No

sysxmlplacement

               2 nc1
                                    2 Yes       No           No        No

sysxprops

               1 clust
                                   18 Yes       Yes          No        No

sysxsrvs

               1 cl
                                   18 Yes       Yes          No        No

sysxsrvs

               2 nc1
                                    2 Yes       No           No        No

sysxsrvs

              11 _WA_Sys_00000005_0000002B
                              8388704 No        No           No        No

(146 rows affected)</source>


Selecting data using bitwise operators.

   <source lang="sql">

Operator Description

   &           Bitwise, logical AND    
   ?           Bitwise, logical OR 
   ^           Bitwise, logical exclusive OR   
   ~           Bitwise NOT 

4> 5> 6> 7> CREATE TABLE tableX ( 8> bitPriced BIT, 9> bitCredit_checked BIT, 10> bitAllocated BIT, 11> bitShipped BIT 12> ) 13> GO 1> 2> INSERT tableX (bitPriced, bitCredit_checked, bitAllocated, bitShipped) VALUES (1,1,0,0) 3> INSERT tableX (bitPriced, bitCredit_checked, bitAllocated, bitShipped) VALUES (1,0,1,0) 4> INSERT tableX (bitPriced, bitCredit_checked, bitAllocated, bitShipped) VALUES (0,0,0,0) 5> INSERT tableX (bitPriced, bitCredit_checked, bitAllocated, bitShipped) VALUES (1,1,1,0) 6> GO (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) 1> 2> SELECT * 3> FROM tableX 4> WHERE bitAllocated ^ bitPriced = 1 5> GO bitPriced bitCredit_checked bitAllocated bitShipped


----------------- ------------ ----------
       1                 1            0          0

(1 rows affected) 1> 2> DROP TABLE tableX 3> GO 1> 2></source>


Testing numeric values with bitwise operators.

   <source lang="sql">

6> 7> CREATE PROC pr_bitwise 8> AS 9> DECLARE @chvHold INTEGER 10> SELECT @chvHold = 2 11> IF ( @chvHold & 1) = 1 12> BEGIN 13> PRINT "One Selected" 14> RETURN 15> END 16> IF ( @chvHold & 2) = 2 17> BEGIN 18> PRINT "Two Selected" 19> RETURN 20> END 21> IF ( @chvHold & 3) = 3 22> BEGIN 23> PRINT "Three Selected" 24> RETURN 25> END 26> IF ( @chvHold & 4) = 4 27> BEGIN 28> PRINT "Four Selected" 29> RETURN 30> END 31> GO 1> 2> EXEC pr_bitwise 3> GO Two Selected 1> 2> DROP PROC pr_bitwise 3> GO 1></source>


The bitwise_and() Scalar Function

   <source lang="sql">

3> 4> CREATE FUNCTION dbo.bitwise_and 5> ( 6> @arg1 varbinary(8), 7> @arg2 varbinary(8) 8> ) RETURNS varbinary(8) 9> AS 10> BEGIN 11> DECLARE 12> @result AS varbinary(8000), 13> @numbytes AS int, 14> @curpos AS int 15> SET @result = 0x 16> SET @numbytes = DATALENGTH(@arg2) 17> SET @curpos = 1 18> WHILE @curpos <= @numbytes 19> BEGIN 20> SELECT 21> @result = @result + CAST(SUBSTRING(@arg1, @curpos, 1) & 22> CAST(SUBSTRING(@arg2, @curpos, 1) AS tinyint) 23> AS binary(1)) 24> SET @curpos = @curpos + 1 25> END 26> RETURN @result 27> END 28> GO 1> GRANT EXECUTE ON dbo.bitwise_and TO public 2> GO 1> 2> SELECT dbo.bitwise_and(0x00000001000000010000000100000001, 3> 0xffffffffffffffffffffffffffffffff) 4> 5> drop function dbo.bitwise_and 6> GO







0x0000000100000001


(1 rows affected) 1></source>


The bitwise_not() Scalar Function

   <source lang="sql">

5> 6> CREATE FUNCTION dbo.bitwise_not 7> ( 8> @arg1 varbinary(8) 9> ) RETURNS varbinary(8) 10> AS 11> BEGIN 12> DECLARE 13> @result AS varbinary(8000), 14> @numbytes AS int, 15> @curpos AS int 16> SET @result = 0x 17> SET @numbytes = DATALENGTH(@arg1) 18> SET @curpos = 1 19> WHILE @curpos <= @numbytes 20> BEGIN 21> SELECT 22> @result = @result + 23> CAST(~ CAST(SUBSTRING(@arg1, @curpos, 1) 24> AS tinyint)AS binary(1)) 25> SET @curpos = @curpos + 1 26> END 27> RETURN @result 28> END 29> GO 1> GRANT EXECUTE ON dbo.bitwise_not TO public 2> GO 1> SELECT dbo.bitwise_not(0x00000001000000010000000100000001) 2> 3> drop function dbo.bitwise_not 4> GO







0xFFFFFFFEFFFFFFFE


(1 rows affected)</source>


The bitwise_or() Scalar Function

   <source lang="sql">

4> 5> CREATE FUNCTION dbo.bitwise_or 6> ( 7> @arg1 varbinary(8), 8> @arg2 varbinary(8) 9> ) RETURNS varbinary(8) 10> AS 11> BEGIN 12> 13> DECLARE 14> @result AS varbinary(8), 15> @numbytes AS int, 16> @curpos AS int 17> SET @result = 0x 18> SET @numbytes = DATALENGTH(@arg2) 19> SET @curpos = 1 20> WHILE @curpos <= @numbytes 21> BEGIN 22> SELECT 23> @result = @result + CAST(SUBSTRING(@arg1, @curpos, 1) | 24> CAST(SUBSTRING(@arg2, @curpos, 1) 25> AS tinyint) 26> AS binary(1)) 27> SET @curpos = @curpos + 1 28> END 29> 30> RETURN @result 31> END 32> GO 1> GRANT EXECUTE ON dbo.bitwise_or TO public 2> GO 1> 2> SELECT dbo.bitwise_or(0x00000001000000010000000100000001, 3> 0xffffffffffffffffffffffffffffffff) 4> 5> drop function dbo.bitwise_or 6> GO







0xFFFFFFFFFFFFFFFF


(1 rows affected) 1> 2></source>


The bitwise_xor() Scalar Function

   <source lang="sql">

5> 6> CREATE FUNCTION dbo.bitwise_xor( 7> @arg1 varbinary(8), 8> @arg2 varbinary(8) 9> ) RETURNS varbinary(8) 10> AS 11> BEGIN 12> DECLARE 13> @result AS varbinary(8), 14> @numbytes AS int, 15> @curpos AS int 16> SET @result = 0x 17> SET @numbytes = DATALENGTH(@arg2) 18> SET @curpos = 1 19> WHILE @curpos <= @numbytes 20> BEGIN 21> SELECT 22> @result = @result + CAST(SUBSTRING(@arg1, @curpos, 1) ^ 23> CAST(SUBSTRING(@arg2, @curpos,1) 24> AS tinyint) 25> AS binary(1)) 26> SET @curpos = @curpos + 1 27> END 28> RETURN @result 29> END 30> GO 1> GRANT EXECUTE ON dbo.bitwise_xor TO public 2> GO 1> 2> SELECT dbo.bitwise_xor(0x00000001000000010000000100000001, 3> 0xffffffffffffffffffffffffffffffff) 4> 5> drop function dbo.bitwise_xor 6> GO







0xFFFFFFFEFFFFFFFE


(1 rows affected) 1> 2></source>