按位和SQL Server [英] Bitwise AND in Sql Server

问题描述

我有一个很典型的情况。我们有一个叫做用户表，有一栏叫分行（VARCHAR 1000）。

I have a very typical situation. We have a table called Users which has a column called Branches (varchar 1000).

该组织可以有1000分支机构。因此，如果用户访问分支1，5，10，树枝串看起来像：

The organization can have 1000 branches. So if a user has access to branch 1, 5, and 10, the branches string would look like:

1000100001000000000 ......

1000100001000000000......

（即对于位置的用户具有分支访问基于分支的数目1）。请不要劝更好的数据存储选项，这是从跨大陆部署了传统的应用程序来找我。

(i.e. 1 for a position a User has branch access to based on the branch's number). Please do not advise better data storage options, this is coming to me from a legacy application that is deployed across continents.

现在给出这样的背景下（并考虑到可能有> 10000用户），我想寻找谁有权访问给定的分支，例如中的任何一个用户都发现谁有权访问任一分支10，65，90或125的所有用户。

Now given this background (and considering that there can be > 10000 users), I want to search for all Users who have access to any one of given set of branches, e.g. Find all users who have access to either branch 10, 65, 90 or 125.

一个简单的解决方案是分支机构所需的设定（即10，65，90，125）转换为分支的字符串（00000010100等），然后用一个标量UDF来遍历两个分支串并在第一次返回true匹配occurence其中，2支字符串有1，假如果没有一个1日共同立场。

One easy solution is to convert the desired set of branches (i.e. 10, 65, 90, 125) to a branch string (00000010100 etc), then use a scalar UDF to iterate over both the branch strings and return true at first matching occurence where 2 branch strings have 1, and false if there is not a 1 at common position.

除此之外，我也有在C＃应用程序搜索的一个选项。一些这些用户是特权（约1000个或更多），并因为它是非常频繁访问他们的数据在应用缓存。但是对于不是特权的其他用户，数据仅在分贝。

Other than that, I also have an option of searching in application in C#. Some of these users are privileged (approx 1000 or more) and their data is cached in application as it is accessed very frequently. But for other users that are not privileged, data is only in db.

我有2个问题在这里：
1）对于一个数据库的搜索，有没有比我提到的UDF方法以外的更好的方法。
2）对于特权用户，这将是在性能方面更好，在应用程序搜索（其中还可以基于一个对分支串回路像UDF，或者2支阵列LINQ的相交运营商，即一个LINQ相交在[1,5,9,50,80,200]和[6,90,256,300]等）
将一个数据库的搜索产生更快的结果或应用程序基于搜索？

I have 2 questions here: 1) For a db search, is there a better way other than the UDF approach I mentioned. 2) For privileged users, what would be better in terms of performance, search in application (which further can be based on a for loop on branch strings like in UDF, or as a Linq Intersect operator on 2 branch arrays, i.e. a Linq Intersect on [1,5,9,50,80,200] and [6,90,256,300] etc.) Would a db search produce faster results or an application based search?

考虑有可能是在这两种情况下搜索其他参数，例如姓氏开头。

Consider there might be other parameters for search in both cases, e.g. Last name starts with.

我目前的做法是，以过滤分贝行两种情况先于其他参数（如姓打头）。然后用一个标量UDF来过滤基于此分支结果集，然后返回结果。

My current approach is to filter rows in db for both situations first on other parameters (like Last name starts with). Then use a scalar UDF to filter this result-set based on branches and then return the results.

推荐答案

做在SQL，它会比C＃或其他前端做只快100倍。

Do it in SQL, it will be only 100 times faster than doing it in C# or other front end.

使用内置的数字表打破了长串入位置（数列上升到2047）。

Use the built-in numbers table to break the long string into positions (number series goes up to 2047).

示例表

create table users (userid int)
insert users select 1 union all select 2

create table permission (userid int, bigstr varchar(1000))
insert permission
select 1, REPLICATE('0', 56) + '1' -- 57th
        + REPLICATE('0', 32) + '1' -- 90th
        + REPLICATE('0', 64) + '1' -- 155th
        + REPLICATE('0', 845)
insert permission
select 2, REPLICATE('0', 66) + '1' -- 67th
        + REPLICATE('0', 98) + '1' -- 166th
        + REPLICATE('0', 657) + '1' -- 824th
        + REPLICATE('0', 176)

样品展示对列表中的所有匹配的权限

Sample showing all the matching permissions against a list

select *
from users u
inner join permission p on p.userid=u.userid
inner join master..spt_values v on v.type='p'
  and SUBSTRING(p.bigstr,v.number,1) = '1'
  and v.number between 1 and LEN(p.bigstr)  -- or 1000 if it is always 1000
where v.number in (57,90,824)

要找到谁拥有在访问至少一个分支列表中的用户：

To find users who have at access to at least one branch in the list:

select distinct u.userid
from users u
inner join permission p on p.userid=u.userid
inner join master..spt_values v on v.type='p'
  and SUBSTRING(p.bigstr,v.number,1) = '1'
  and v.number between 1 and LEN(p.bigstr)  -- or 1000 if it is always 1000
where v.number in (57,90,824)

等

这篇关于按位和SQL Server的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！