Integer bigger than bigint?

• Stephen Fry

  SSC-Addicted

  Points: 434

  More actions

  January 21, 2010 at 2:04 pm

  #215940

  QUESTION:

  Is there any way to use an integer data type larger than a (signed) bigint in SQL Server 2008?

  PROBLEM:

  I have an "item" that can belong to N number of "categories". To implement this, I've created a "category" bitmask that's assigned to each "item". I then perform bitwise operations to determine which "categories" an "item" belongs to. The "category" list must be dynamic (iow, you can add "categories" at runtime). With a signed bigint, this only allows me 63 "categories" (2^63-1)...I need more.

  NOTES:

  1. From what I've seen, you must use an integer data type to perform bitwise operations (which makes perfect sense). e.g., i couldn't use a numeric(38,0).

  2. I don't want to just create a bigint rule to make it unsigned. That would still leave me with a 63-bit integer.

  3. I'm open to suggestions on alternate ways to implement this, although everything I've thought of so far seems like it would cause a massive performance hit (like introducing a table with each item/category combination).

  Thanks so much for your input!

• ChrisM@home

  SSC-Insane

  Points: 24260

  More actions

  January 21, 2010 at 2:21 pm

  #1106962

  We use three tables for this:

  an Item table with pk ItemID

  a Category table with pk CategoryID

  an ItemCategory table with pkItemCategory and fk's ItemID and CategoryID

  There are no limits (to us) on how many categories per item and vice versa.

  There's no developer overhead of dicking around trying to figure out what the hell is associated with what, you can see it all unfold with a simple query.

  It uses bogstandard INNER and LEFT JOINS instead of iBitmap purple blogsquabs or whatever.

  I won't have to worry about leaving my house on a dark night in three years' time and being whacked over the head with something heavy and blunt by a developer driven over the edge by a design idea which seemed fancy schmancy at the time but which sucked in practice.

  Of course you can do it, if you really really want to.

  Edit: How about giving this <<a massive performance hit (like introducing a table with each item/category combination)>> a wee try with some sample data?

  ---

  [font="Arial"]^{Low-hanging fruit picker and defender of the moggies}[/font]

  For better assistance in answering your questions, please read this[/url].

  ---

  Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]

  Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]

• Revenant

  SSC-Forever

  Points: 42467

  More actions

  January 21, 2010 at 2:35 pm

  #1106965

  Bitwise operators can operate on varbinary as the left operand; only the mask ( the right operand) has to be one of the integer types. To quote SQLS 2008 Books Online, "The operands for bitwise operators can be any one of the data types of the integer or binary string data type categories (except for the image data type), except that both operands cannot be any one of the data types of the binary string data type category."

• Stephen Fry

  SSC-Addicted

  Points: 434

  More actions

  January 21, 2010 at 2:36 pm

  #1106968

  I'm trying to make it so that you can select N number of categories in the UI and the program will return all the items that match either [any] or [all] of those categories (depending on what the user specifies). When searching with the [all] option, the only way I see to do this with the ItemCategory table is to first pivot the table and then search explicitly for the desired categories, or, to dynamically create a statement with the necessary joins. Seems to me that either option would be a significant performance drag as well as significantly more complicated that a single bitwise operation. Unless I'm missing something?

  Thanks again!

• Lowell

  SSC Guru

  Points: 323482

  More actions

  January 21, 2010 at 4:04 pm

  #1106999

  i have to go with lobbymunchers suggestion; a standard parent child relationship would be much more appropriate; i think you are mentally locked into bitwise operations because it's neat to do, but since you have more objects to track than bits, you don't want to let go of an inappropriate model.

  Lowell

  ---

  --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

• Stephen Fry

  SSC-Addicted

  Points: 434

  More actions

  January 21, 2010 at 4:25 pm

  #1107010

  @lowell:

  I'm certainly not locked into any single way of doing it. As I stated in my previous post, the only implementations I can think of with that approach are less efficient and more complicated. So that's why I wanted to see if I could get around this roadblock. If a bigint is as large of an integer as I can use in SQL Server, then so be it, I'll have to do it the "normal" way. That's what I'm trying to find out with this post.

• SQLRNNR

  SSC Guru

  Points: 281324

  More actions

  January 21, 2010 at 4:38 pm

  #1107013

  One more nod in agreement with Lowell and Lobbymuncher

  Jason...AKA CirqueDeSQLeil
  _______________________________________________
  I have given a name to my pain...MCM SQL Server, MVP
  SQL RNNR
  Posting Performance Based Questions - Gail Shaw[/url]
  Learn Extended Events

• mister.magoo

  SSC-Forever

  Points: 47068

  More actions

  January 21, 2010 at 4:56 pm

  #1107020

  If you do have to use a categories table (and I agree that you do), how about something like this,

  which uses a categories table, but with a sort of mask to select the result....It is certainly not complicated,

  but possibly not super speedy - I am sure someone could provide a quicker solution -

  this was intended as an indication that it need not be complicated to work with a categories table.

  You would generate your list of selectable categories in the application from the "categories" table and create a comma separated list of selected categories (their ids) to pass to SQL.

  --= declare a variable to hold the list of selected categories (the id column from the categories table)

  --= and set a value corresponding to the user's choice of categories (category1 and category4)

  declare @category_list varchar(1000) -- big enough for quite a few categories ?

  set @category_list = '1,4,5'

  --= declare a variable to hold the user's choice of ALL or ANY matches

  declare @all bit

  set @all = 0

  --= CTE to generate some tables

  ;with categories(id,category)

  as (

  select 1,'category1' union all

  select 2,'category2' union all

  select 3,'category3' union all

  select 4,'category4' union all

  select 5,'category5'

  )

  , item_categories(id,itemid,categoryid)

  as (

  select 1,1,1 union all

  select 2,1,4 union all

  select 3,1,5 union all

  select 4,2,1

  )

  , items(id,description)

  as (

  select 1,'Item 1' union all

  select 2,'Item 2'

  )

  --= Now the actual query

  select

  [id],

  [description],

  case match.list

  when '' then 'No match'

  when @category_list then 'Exact Match'

  else 'Partial Match'

  end as MatchStatus

  from items

  cross apply (

  select STUFF(

  (

  select ','+convert(varchar,categoryid)

  from item_categories

  where itemid = items.id and charindex(','+convert(varchar,categoryid)+',',','+@category_list+',')>0

  for xml path('')

  ),1,1,'')

  ) as match(list)

  where (@all = 0 AND match.list<>'') OR (@all = 1 AND match.list = @category_list)

  As long as you don't have millions of categories, and you index correctly, something like this should be workable.

  Try setting different values for @category_list and @all (1=match all categories, 0=match any)

  MM
  

  ---

  
  select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

• Forum Etiquette: How to post Reporting Services problems
[/url]
• Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
[/url]
• How to Post Performance Problems - by Gail Shaw
[/url]