SQL Server/T-SQL Tutorial/Data Types/bitwise operators
Содержание
- 1 Bitwise AND (&)
- 2 Bitwise NOT (~)
- 3 Bitwise NOT (~) Validity Table
- 4 Bitwise OR (|)
- 5 Exclusive Or (^)
- 6 Legal Bitwise Operation
- 7 Retrieving Index Properties by Using the Bitwise AND (&) Operator
- 8 Selecting data using bitwise operators.
- 9 Testing numeric values with bitwise operators.
- 10 The bitwise_and() Scalar Function
- 11 The bitwise_not() Scalar Function
- 12 The bitwise_or() Scalar Function
- 13 The bitwise_xor() Scalar Function
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>