Can I perform a bitwise and on a set of numbers in Sql Server?

I have a security table containing a list of groups and users with a bitwise integer permission for each. For 开发者_运维问答each given user, I would like to perform a bitwise AND on all of their groups and of their personal permission record, if present.

Of course, I can easily do this in my code, but I'd rather do it in the database as there could be thousands of items I am querying the rights for.

I would prefer a set-based solution over a cursor.

Note than I do not have control over the schema.

A set-based solution is possible if all the values that you want to bitwise or are single-bit values: Performing a bitwise sum

Alternatively, you can use a less-elegant method for vaguely-set-based bitwise operations on sets of non-unique values:

DECLARE @BitSum INT
SET @BitSum = 0
SELECT @BitSum = @BitSum | BitValue
FROM (
    SELECT 1 AS BitValue
    UNION SELECT 7
    UNION SELECT 16
) AS SampleValues
SELECT @BitSum

Edit: Hugo Kornelis answers the question pretty comprehensively in this other post: http://www.eggheadcafe.com/software/aspnet/33139293/bitwise-aggregate-function.aspx

SQL Server natively supports bitwise math. For example, & is bitwise AND:

select  10 & 3
-->
2

Written out:

10 = 1010
3  = 0011
&  = bitwise and
2  = 0010

You can use this in a query like any other operator:

select  ss.SecurityBits & cs.CustomerBits
from    SecuritySettings ss
join    CustomerSettings cs
on      ss.ID = cs.SecuritySettingsID

You can use a recursive CTE to perform a bitwise AND.

DECLARE @table TABLE (UserID int, AccessRights int)
INSERT @table (UserID, AccessRights) VALUES (2, 0x3)
INSERT @table (UserID, AccessRights) VALUES (2, 0x2)
INSERT @table (UserID, AccessRights) VALUES (2, 0x7)

;WITH Ranked AS
(
    SELECT UserID, AccessRights
    , DENSE_RANK() OVER (PARTITION BY UserID ORDER BY AccessRights) RankNum
    , CASE WHEN DENSE_RANK()
                OVER (PARTITION BY UserID ORDER BY AccessRights DESC) = 1 
          THEN 1 
          ELSE 0 
      END IsLastItem
    FROM @table
),
RecursiveCTE AS
(
    SELECT R.UserID, RankNum, IsLastItem, R.AccessRights
    FROM Ranked R
    WHERE R.RankNum = 1

    UNION ALL

    SELECT R.UserID, R.RankNum, R.IsLastItem
            , R.AccessRights & RecursiveCTE.AccessRights
    FROM RecursiveCTE
    INNER JOIN Ranked R ON R.UserID = RecursiveCTE.UserID
    WHERE R.RankNum = RecursiveCTE.RankNum + 1
)
SELECT UserID, AccessRights
FROM RecursiveCTE
WHERE IsLastItem = 1

You can create SQL Server Aggregate functions in .NET that you can then implement in SQL server inline. I think this requires a minimum of SQL server 2005 and Visual Studio 2010. I did one using Visual Studio 2013 Community Edition (free even for commercial use) for use with .NET 2 and SQL Server 2005.

See the MSDN article: https://msdn.microsoft.com/en-us/library/91e6taax(v=vs.90).aspx

First you'll need to enable the CLR feature in SQL server: https://msdn.microsoft.com/en-us/library/ms131048.aspx

sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'clr enabled', 1;
GO
RECONFIGURE;
GO

1. Create a SQL Server -> SQL Server Database Project
2. Right-click on the new project and select Properties
3. Configure the targeted SQL Server version under Project Settings
4. Configure the targeted CLR language under SQL CLR (such as VB)
5. Right-click on the new project and select Add -> New Item...
6. When the dialog pops up, select SQL Server -> SQL CLR VB -> SQL CLR VB Aggregate

Now you can write your bitwise code in VB:

Imports System
Imports System.Data
Imports System.Data.SqlClient
Imports System.Data.SqlTypes
Imports Microsoft.SqlServer.Server


<Serializable()> _
<Microsoft.SqlServer.Server.SqlUserDefinedAggregate(Format.Native)> _
Public Structure AggregateBitwiseOR

    Private CurrentAggregate As SqlTypes.SqlInt32

    Public Sub Init()
        CurrentAggregate = 0
    End Sub

    Public Sub Accumulate(ByVal value As SqlTypes.SqlInt32)
        'Perform Bitwise OR against aggregate memory
        CurrentAggregate = CurrentAggregate OR value
    End Sub

    Public Sub Merge(ByVal value as AggregateBitwiseOR)
        Accumulate(value.Terminate())
    End Sub

    Public Function Terminate() As SqlInt32
        Return CurrentAggregate
    End Function

End Structure

Now deploy it: https://msdn.microsoft.com/en-us/library/dahcx0ww(v=vs.90).aspx

1. Build the project using the menu bar: Build -> Build ProjectName (if the build fails with error 04018 then download a new version of the data tools @ http://msdn.microsoft.com/en-US/data/hh297027 or by going to the menu bar: Tools -> Extensions And Updates, then under updates select update for Microsoft SQL Server Update For Database Tooling)
2. Copy your compiled DLL to C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Binn and to C:\

3. Register the DLL:

   CREATE ASSEMBLY [CLRTools] FROM ‘c:CLRTools.dll’ WITH PERMISSION_SET = SAFE

4. Create the aggregate in SQL:

   CREATE AGGREGATE [dbo].[AggregateBitwiseOR](@value INT) RETURNS INT EXTERNAL NAME [CLRTools].[CLRTools.AggregateBitwiseOR];

If you get the error "Incorrect syntax near 'EXTERNAL'" then change the database compatibility level using following commands:

For SQL Server 2005: EXEC sp_dbcmptlevel 'DatabaseName', 90

For SQL Server 2008: EXEC sp_dbcmptlevel 'DatabaseName', 100

5. Test your code:

   SELECT dbo.AggregateBitwiseOR(Foo) AS Foo FROM Bar

I found this article helpful: http://www.codeproject.com/Articles/37377/SQL-Server-CLR-Functions