SQL Server - Highest Priority Item Search with Bitwise Operators

Question

Problem:

I want to return all rows from the primary data table along with the highest priority based on the current assigned priority in the exception table.

I created a simplified example of my data setup below (with creation scripts), so I hope you can help with some pretty fast T-SQL problem.

Setup:

I have a primary data table in which each row can have one or more exceptions stored as a bitmask.

 CREATE TABLE [dbo].[PrimaryData](
    Id [INT] IDENTITY(1,1) NOT NULL,
    SomeData [VARCHAR](30) NOT NULL,
    Exceptions [INT] NOT NULL,
 )
 INSERT INTO [dbo].[PrimaryData](SomeData, Exceptions)
    VALUES('Data A', 0)
 INSERT INTO [dbo].[PrimaryData](SomeData, Exceptions)
    VALUES('Data B', 6)
 INSERT INTO [dbo].[PrimaryData](SomeData, Exceptions)
    VALUES('Data C', 6)
 INSERT INTO [dbo].[PrimaryData](SomeData, Exceptions)
    VALUES('Data D', 192)
 INSERT INTO [dbo].[PrimaryData](SomeData, Exceptions)
    VALUES('Data E', 132)

, , . , , , 1 .

CREATE TABLE [dbo].[Exception](
    Id [INT] IDENTITY(1,1) NOT NULL,
    Priority [INT] NOT NULL, 
    Mask [SMALLINT] NOT NULL,
    Description [VARCHAR](30) NOT NULL
 )
 INSERT INTO [dbo].[Exception] (Priority, Mask, Description)
      VALUES(1, 1, 'Exception A')
 INSERT INTO [dbo].[Exception] (Priority, Mask, Description)
      VALUES(2, 2, 'Exception B')
 INSERT INTO [dbo].[Exception] (Priority, Mask, Description)
      VALUES(3, 4, 'Exception C')
 INSERT INTO [dbo].[Exception] (Priority, Mask, Description)
      VALUES(4, 8, 'Exception D')
 INSERT INTO [dbo].[Exception] (Priority, Mask, Description)
      VALUES(5, 16, 'Exception E')
 INSERT INTO [dbo].[Exception] (Priority, Mask, Description)
      VALUES(6, 32, 'Exception F')
 INSERT INTO [dbo].[Exception] (Priority, Mask, Description)
      VALUES(7, 64, 'Exception G')
 INSERT INTO [dbo].[Exception] (Priority, Mask, Description)
      VALUES(8, 128, 'Exception H')
 INSERT INTO [dbo].[Exception] (Priority, Mask, Description)
      VALUES(9, 256, 'Exception I')

, , SomeData, Mask ( ) ( ).

.

| Data B | 2 | Exception B

, , 25K.

.

Answer 1

SELECT  *
FROM    PrimaryData pd
CROSS APPLY
        (
        SELECT  TOP 1 *
        FROM    Exception e
        WHERE   e.Mask & pd.Exceptions <> 0
        ORDER BY
                e.Priority
        ) q

Answer 2

, PrimaryData.

select top 1 SomeData, Mask
  from PrimaryData
    inner join Exceptions
      on (PrimaryData.Exceptions & Exceptions.Mask <> 0)
  where PrimaryData.Id = 27
  order by Priority

- ( Quassnoi)

with data as (
  select SomeData, Mask, row_number() over
      (partition by PrimaryData.Id order by Priority) AS row
    from PrimaryData
      inner join Exceptions
        on (PrimaryData.Exceptions & Exceptions.Mask <> 0)
)
select * 
  from data
  where row = 1

| &

Answer 3

() , . :

CREATE FUNCTION GetPriorityMask(@value int)
RETURNS smallint
with schemabinding
AS
BEGIN
    declare @mask smallint

    if @value = 0 return null

    set @mask = 1
    while @mask <= @value
    begin
        if @value | @mask = @value 
            break;

        set @mask = @mask * 2
    end

    RETURN @mask

END

PrimaryDataTable, .

alter table PrimaryData
add PriorityMask as (dbo.GetPriorityMask(Exceptions)) persisted

create index IX_PrimaryDate_PriorityMask
on PrimaryData(PriorityMask)

, , :

alter table Exception
add constraint UQ_Exception_Mask UNIQUE (Mask)

alter table PrimaryData
add constraint FK_PrimaryData_Exception foreign key(PriorityMask) references Exception(Mask)

:

select *
from PrimaryData
left join Exception on PrimaryData.PriorityMask = Exception.Mask

Answer 4

, " 10 " .

, , , , 2. FLOOR(LOG(Exceptions)/LOG(2). ( , .)

SELECT SomeData, Mask, Description
FROM PrimaryData
INNER JOIN Exception ON
    CASE
        WHEN Exceptions & 0x1 <> 0 THEN 1
        WHEN Exceptions & 0x2 <> 0 THEN 2
        WHEN Exceptions & 0x4 <> 0 THEN 4
        WHEN Exceptions & 0x8 <> 0 THEN 8
        WHEN Exceptions & 0x10 <> 0 THEN 16
        WHEN Exceptions & 0x20 <> 0 THEN 32
        WHEN Exceptions & 0x40 <> 0 THEN 64
        WHEN Exceptions & 0x80 <> 0 THEN 128
        WHEN Exceptions & 0x100 <> 0 THEN 256
        WHEN Exceptions & 0x200 <> 0 THEN 512
        WHEN Exceptions & 0x400 <> 0 THEN 1024
        WHEN Exceptions & 0x800 <> 0 THEN 2048
        WHEN Exceptions & 0x1000 <> 0 THEN 4096
        WHEN Exceptions & 0x2000 <> 0 THEN 8192
        WHEN Exceptions & 0x4000 <> 0 THEN 16384
        WHEN Exceptions & 0x8000 <> 0 THEN 32768
        WHEN Exceptions & 0x10000 <> 0 THEN 65536
        WHEN Exceptions & 0x20000 <> 0 THEN 131072
        WHEN Exceptions & 0x40000 <> 0 THEN 262144
        WHEN Exceptions & 0x80000 <> 0 THEN 524288
        WHEN Exceptions & 0x100000 <> 0 THEN 1048576
        WHEN Exceptions & 0x200000 <> 0 THEN 2097152
        WHEN Exceptions & 0x400000 <> 0 THEN 4194304
        WHEN Exceptions & 0x800000 <> 0 THEN 8388608
        WHEN Exceptions & 0x1000000 <> 0 THEN 16777216
        WHEN Exceptions & 0x2000000 <> 0 THEN 33554432
        WHEN Exceptions & 0x4000000 <> 0 THEN 67108864
        WHEN Exceptions & 0x8000000 <> 0 THEN 134217728
        WHEN Exceptions & 0x10000000 <> 0 THEN 268435456
        WHEN Exceptions & 0x20000000 <> 0 THEN 536870912
        WHEN Exceptions & 0x40000000 <> 0 THEN 1073741824
        WHEN Exceptions & 0x80000000 <> 0 THEN -2147483648
        ELSE 0
    END = Mask
WHERE Exceptions <> 0
ORDER BY PrimaryData.Id