Geek asks and answers

Can I do bitwise and set-up in Sql Server?

I have a security table containing a list of groups and users with a bit integer resolution for each. For each given user, I would like to perform a bitwise AND in all my groups and their personal permission record, if any.

Of course, I can easily do this in my code, but I would prefer to do it in the database, as there may be thousands of elements for which I request rights.

I would prefer a cursor-based solution.

Please note that I do not have control over the circuit.

+3
source share
4 answers

, , :

:

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

: : http://www.eggheadcafe.com/software/aspnet/33139293/bitwise-aggregate-function.aspx

+4

SQL Server . , & AND:

select  10 & 3
-->
2

:

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

, :

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

CTE 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
0

SQL Server .NET, SQL. , SQL Server 2005 Visual Studio 2010. Visual Studio 2013 Community Edition ( ) .NET 2 SQL Server 2005.

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

CLR SQL-: 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
  • SQL Server → SQL Server
  • ""
  • SQL Server " "
  • CLR SQL CLR (, VB)
  • → ...
  • , SQL Server → SQL CLR VB → SQL CLR VB Aggregate

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

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

  • , : Build → Build ProjectName ( 04018, @http://msdn.microsoft.com/en-US/data/hh297027 : → , Microsoft SQL Server Update )
  • DLL C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Binn C:\

  • DLL:

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

  • SQL:

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

" " EXTERNAL ", , :

SQL Server 2005: EXEC sp_dbcmptlevel 'DatabaseName', 90

SQL Server 2008: EXEC sp_dbcmptlevel 'DatabaseName', 100

  1. :

    SELECT dbo.AggregateBitwiseOR(Foo) AS Foo FROM Bar

: http://www.codeproject.com/Articles/37377/SQL-Server-CLR-Functions

0

More articles:


All Articles