title: Bitwise AND in Sql Server
body: <p>I have a very typical situation. We have a table called Users which has a column called Branches (varchar 1000).</p>
  
  <p>The organization can have 1000 branches. So if a user has access to branch 1, 5, and 10, the branches string would look like:</p>
  
  <p>1000100001000000000......</p>
  
  <p>(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.</p>
  
  <p>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.</p>
  
  <p>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.</p>
  
  <p>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.</p>
  
  <p>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?</p>
  
  <p>Consider there might be other parameters for search in both cases, e.g. Last name starts with.</p>
  
  <p>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.</p>