Retrieving Index Properties by Using the Bitwise AND (&) Operator : bitwise operators « Data Types « SQL Server / T-SQL Tutorial






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)








5.3.bitwise operators
5.3.1.Selecting data using bitwise operators.
5.3.2.Testing numeric values with bitwise operators.
5.3.3.Bitwise NOT (~) Validity Table
5.3.4.Legal Bitwise Operation
5.3.5.Retrieving Index Properties by Using the Bitwise AND (&) Operator
5.3.6.Bitwise AND (&)
5.3.7.Bitwise OR (|)
5.3.8.Exclusive Or (^)
5.3.9.Bitwise NOT (~)
5.3.10.The bitwise_not() Scalar Function
5.3.11.The bitwise_and() Scalar Function
5.3.12.The bitwise_or() Scalar Function
5.3.13.The bitwise_xor() Scalar Function