Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««123

Introduction to Bitmasking in SQL Server, Part 2 Expand / Collapse
Author
Message
Posted Saturday, May 23, 2009 11:11 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, January 27, 2010 10:31 PM
Points: 15, Visits: 69
That would be the case if I was actually doing what's happening in that example.

In practice other constraints on the datasets ensure that the number of reads is in the hundreds if anything.

I would much prefer using a single column to store information in my cases than many separate columns. From an academic perspective a column per value is better but in real life I don't want to have to add a new column to a table, add a new index, return a new column in my result sets, add a new parameter into my stored procedures and anything else I've missed.

The alternative of adding a new bit value starts to look pretty good in that situation. Horses for courses of course.
Post #722680
Posted Sunday, May 24, 2009 12:24 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 11:19 PM
Points: 37,075, Visits: 31,636
marshall.jones (5/23/2009)
That would be the case if I was actually doing what's happening in that example.

In practice other constraints on the datasets ensure that the number of reads is in the hundreds if anything.

I would much prefer using a single column to store information in my cases than many separate columns. From an academic perspective a column per value is better but in real life I don't want to have to add a new column to a table, add a new index, return a new column in my result sets, add a new parameter into my stored procedures and anything else I've missed.

The alternative of adding a new bit value starts to look pretty good in that situation. Horses for courses of course.


I didn't say anything about a column per value.


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #722683
Posted Monday, May 25, 2009 8:35 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, May 29, 2014 9:29 AM
Points: 214, Visits: 647
mike brockington (5/22/2009)
I would love to see what this is being compared against, in order to describe this as 'elegant'.

In my dictionary, elegance requires something to be as simple as practicable, but
Bitwise comparisons are really inefficient, and extremely hard to debug by eye.


Hi Mike: I use it loosely here, but let's say comparing to a large string of values, and either parsing a comma separated list or throwing it into an IN and using dynamic SQL. These methods can introduce problems (efficiency, security, etc), so as an alternative I would consider this approach if it fit. Of course, not the solution for every problem.

Thanks much





Lee Everest

Post #722983
Posted Monday, May 25, 2009 8:36 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, May 29, 2014 9:29 AM
Points: 214, Visits: 647
Andrew Peterson (5/22/2009)
Great article. And nice example use of bitmask for searching.

If you approach the bitmask column for the purpose of high frequency searching, really for "reporting" then it does not violate any normalization rules. Report tables are almost always denormalized. I've seen some real world examples where this approach could really add value for end user searches.

And a great twist on tier to tier data transmittal. Consolidating data at the front end tier into a bitmask for rapid transmittal, and then decomposition at the middle or data tier for storage. This has some potential in selected situations.
The twist is that the time to convert to bitmask (both directions) is faster than transmitting a large volume of data - which in today's environment would be an XML file for best efficency. I cannot immediately think of a use, but it is a new tool, that may come in handy when I least expect it.

As with everything in system design and the New Yankee Workshop, more tools are better. Just use the right tool for the right job!

Thanks again!


Thanks for the kind words, Andrew





Lee Everest

Post #722984
Posted Monday, May 25, 2009 8:41 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, May 29, 2014 9:29 AM
Points: 214, Visits: 647
GregoryAJackson (5/22/2009)
I agree.

It's "cool", etc. but it doesnt seem practical.

GAJ


Sweet! Thanks bro

GregoryAJackson (5/22/2009)
I agree.
I'm not a fan of this in the world of RDMS's

It's "cool", etc. but it doesnt seem practical.

imagine writing queries and reports against the data, etc ?

GAJ



I'm not sure when you would write a report. It's a method - how do you write a report against a method? You mean like a sample of what might get returned? Or maybe a count of how many red-headed 25 year olds you might have in the database? I think that when I was at Match we still had that data available on the customer record for reporting, but when it came time to fire up a search it didn't go to textual customer data. Rather, it pinged a method such as this to fetch matches.


Lee





Lee Everest

Post #722987
Posted Tuesday, June 2, 2009 10:29 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, May 29, 2014 9:29 AM
Points: 214, Visits: 647
Here's an implementation of the technique described in part 2 written in an ASP.net web project.

http://texastoo.com/post/Implementation-for-Introduction-to-Bitmasking-in-SQL-Server-part-2.aspx






Lee Everest

Post #727576
« Prev Topic | Next Topic »

Add to briefcase «««123

Permissions Expand / Collapse