@mister.magoo:
Thanks for the effort, much appreciated!
What you have though is precisely my point...while I agree that it's not overly complicated, it's certainly "more" complicated, and i would think significantly less efficient than a single bitwise operation. Everyone seems to think the bitwise approach is too "fancy", but I think what you have is a lot "fancier" than a single condition of "@ItemBitmask & @SelectedBitmask = @SelectedBitmask".
I'm not opposed to an approach such as yours, and it looks like I'll have to go with something along those lines, but I was hoping I could go with what I still perceive to be the simpler and quicker way. So if anyone is aware of how to get at an integer larger than bigint, I'd still love to know about it
Thanks again for all the input.
You're welcome
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
Revenant (1/21/2010)
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."
That still means a limit on the mask, though.
--Jeff Moden
Change is inevitable... Change for the better is not.
Stephen Fry (1/21/2010)
@mister.magoo:Thanks for the effort, much appreciated!
What you have though is precisely my point...while I agree that it's not overly complicated, it's certainly "more" complicated, and i would think significantly less efficient than a single bitwise operation. Everyone seems to think the bitwise approach is too "fancy", but I think what you have is a lot "fancier" than a single condition of "@ItemBitmask & @SelectedBitmask = @SelectedBitmask".
I'm not opposed to an approach such as yours, and it looks like I'll have to go with something along those lines, but I was hoping I could go with what I still perceive to be the simpler and quicker way. So if anyone is aware of how to get at an integer larger than bigint, I'd still love to know about it
Thanks again for all the input.
Bit masking will always have the problem of being non-sargeable for table lookups which also means it will also be comparatively slow. A normalize associative table with one column for the item and one column to hold one category (you'd need one row for each item/category) would be highly performant even in the presence of "ORs".
If you still like the idea of denormalizing (for performance reasons, you really shouldn't) the table by having a single column with multiple categories, you could use a binary column and use character conversions to do "byte slicing" in a fashion similar (but not identical to) the byte slicing they explain in Books Online to determine which columns have been updated in triggers.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 12 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply