SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Introduction to Bitmasking in SQL Server 2005


Introduction to Bitmasking in SQL Server 2005

Author
Message
ab5sr
ab5sr
UDP Broadcaster
UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)

Group: General Forum Members
Points: 1456 Visits: 705
Joe you still in the Austin area?

Lee Everest


ab5sr
ab5sr
UDP Broadcaster
UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)

Group: General Forum Members
Points: 1456 Visits: 705
Doesn't sound like you are slowing down any. Good. Live longer!

I'll be heading down that way tomorrow, G'town, to Mom's for Christmas. Hope to plow over as many 'horns as possible en route

Lee Everest


Dave Poole
Dave Poole
SSC-Insane
SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)

Group: General Forum Members
Points: 24232 Visits: 3490
What do you get when you cross a sheep with a bee?

Baa Hum Bug

LinkedIn Profile
www.simple-talk.com
Matthew Ross
Matthew Ross
SSC Rookie
SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)

Group: General Forum Members
Points: 32 Visits: 1

Lee,

I found your article very intriguing. The idea of bitmasking use hex values is indeed an old technique. Back when programmers actually cared about memory and disk space; back when you had to. Today's programmers take this for granted and create bloated programs that cost the end user gigabytes of space and a trip to the local computer shack for more RAM.

Your article reminds us how to maximize space by representing data in hex format. Actually reminds me a lot of IPV6 and all the hex values now used in this protocol. Hex allows us to represent far more data in less space. Even the authors of IPV4 learned this the hard way. Now we use Hex for IPV6, Ok lessoned learned.

Again great article and as far as using a DB for this, who cares? A DB is a data repository key word being data just as the file system (e.g. Windows registry) is used as a repository, or memory (temporary, however it is), and XML.

Fantastic!

- M. Ross


ab5sr
ab5sr
UDP Broadcaster
UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)

Group: General Forum Members
Points: 1456 Visits: 705
Thanks Matt. I agree and have come to the conclusion that those not agreeing with the article, or those otherwise opposed to using the relational engine in this fashion, would be the first to store XML, a blob, etc. in the database, but would rather have someone else's engine mask the data to binary. I am simply replacing someone's engine for doing this. This is the what they cannot comprehend or make sense out of. And, as far as I can remember, a binary data type is ANSI compliant regardless of who fashions the binary data which goes into it - a third-party program, .Net, or a programmer/db developer.

Lee Everest


John Rempel
John Rempel
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1265 Visits: 289
ab5sr (3/23/2007)
... those otherwise opposed to using the relational engine in this fashion, would be the first to store XML, a blob, etc. in the database...


I'd say just the opposite. Those who have issues with storing data in this manner would likely have the same issue with XML in the database and for the same reason. Sure it's possible to do it, but then it's not data anymore. It cannot be queried with SET logic. All the benefits of using a relational database are lost. I suppose if you're okay with that, then Godspeed. I would personally avoid it if at all possible.

It is an interesting article nonetheless. Smile
DCPeterson
DCPeterson
SSCertifiable
SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)

Group: General Forum Members
Points: 5063 Visits: 432
Matthew Ross (2/12/2007)


Lee,

... as far as using a DB for this, who cares? A DB is a data repository key word being data just as the file system (e.g. Windows registry) is used as a repository, or memory (temporary, however it is), and XML.

- M. Ross


Yeah... a database is a place to store data, just like an F-15 is a place to store jet fuel. Sorry, but you don't know what you are talking about.

/*****************

If most people are not willing to see the difficulty, this is mainly because, consciously or unconsciously, they assume that it will be they who will settle these questions for the others, and because they are convinced of their own capacity to do this. -Friedrich August von Hayek



*****************/
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (339K reputation)SSC Guru (339K reputation)SSC Guru (339K reputation)SSC Guru (339K reputation)SSC Guru (339K reputation)SSC Guru (339K reputation)SSC Guru (339K reputation)SSC Guru (339K reputation)

Group: General Forum Members
Points: 339584 Visits: 42623
Whether I agree with bit-masking in SQL Server or not, I enjoyed the article. Thanks.

--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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
steitelbaum
steitelbaum
SSC-Addicted
SSC-Addicted (464 reputation)SSC-Addicted (464 reputation)SSC-Addicted (464 reputation)SSC-Addicted (464 reputation)SSC-Addicted (464 reputation)SSC-Addicted (464 reputation)SSC-Addicted (464 reputation)SSC-Addicted (464 reputation)

Group: General Forum Members
Points: 464 Visits: 81
good article, it will be relevant to folks working with hierarchy ids that need to maintain sibling order.

I think I see why Lee avoided varbinary in the example. It looks like assigning a value to a varbinary, witnessing it's length as n, and then bit masking it against itself can result in it having a length longer than n, even if it (the growth in bytes) wasnt necessary.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search