InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comSun, 19 May 2013 02:07:24 GMT20http://www.sqlservercentral.com/Forums/Topic327630-335-1.aspxI can understand why people might not like the idea of storing multiple column values in one encoded field, but it does occur to me that this would be a great technique for passing values in a web app. instead of http://blah.com?field1=1&field2=5&field3=7Bitmask all the values selected and pass them encodedhttp://blah.com?referringData=00bx7fthen decode on the page to values for SQL or whatever.You get a lot of things here: users can't fish by inserting other values in the POST string; your POST string looks LEE7 (sorry...:-D); you have a shorter POST string.If you do want to store bitmasked vals in the DB, then your response data could also be de-coded on the client side using Javascript (if this is supported). The downside there is, you can see Javascript in View Source. But, seems like this technique could help speed up data-intensive web applications.Just my 2 centavos. Sorry it's off-topic, but hey.Thu, 21 May 2009 14:29:30 GMTsteve skeltonhttp://www.sqlservercentral.com/Forums/Topic327630-335-1.aspxgood 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.Tue, 25 Mar 2008 12:22:13 GMTsteitelbaumhttp://www.sqlservercentral.com/Forums/Topic327630-335-1.aspxWhether I agree with bit-masking in SQL Server or not, I enjoyed the article. Thanks.Wed, 05 Dec 2007 17:01:10 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic327630-335-1.aspx[quote][b]Matthew Ross (2/12/2007)[/b][hr] 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 [/quote]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.Wed, 05 Dec 2007 14:50:20 GMTDCPetersonhttp://www.sqlservercentral.com/Forums/Topic327630-335-1.aspx[quote][b]ab5sr (3/23/2007)[/b][hr]... those otherwise opposed to using the relational engine in this fashion, would be the first to store XML, a blob, etc. in the database...[/quote]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. :)Wed, 05 Dec 2007 13:57:37 GMTJohn Rempelhttp://www.sqlservercentral.com/Forums/Topic327630-335-1.aspxThanks 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.Fri, 23 Mar 2007 12:21:00 GMTab5srhttp://www.sqlservercentral.com/Forums/Topic327630-335-1.aspx<SPAN id=Showtread1_ThreadRepeater__ctl1_lblFullMessage> <P>Lee,</P><P> 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.</P><P> 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.</P><P> 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.</P><P>Fantastic!</P><P>- M. Ross</P></SPAN>Mon, 12 Feb 2007 18:31:00 GMTMatthew Rosshttp://www.sqlservercentral.com/Forums/Topic327630-335-1.aspxWhat do you get when you cross a sheep with a bee?Baa Hum BugSun, 24 Dec 2006 05:32:00 GMTDavid.Poolehttp://www.sqlservercentral.com/Forums/Topic327630-335-1.aspxDoesn'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 <img src='images/emotions/biggrin.gif' height='20' width='20' border='0' title='Big Grin' align='absmiddle'>Fri, 22 Dec 2006 17:15:00 GMTab5srhttp://www.sqlservercentral.com/Forums/Topic327630-335-1.aspxYep. I show up at IEEE meetings now and then, and CACTUSS almost all the time, and wherever anyone will invite me to speak in Austin. I am currently doing book #7 (hope to finish before April 2007) and teaching a DB Design course on-line for MySQL AB from home. In January, I will be a Texas-42 instructor for Royal Caribbean Cruises for a week -- it is a domino game only played in Texas. I hope to get to Australia in 2007. Fri, 22 Dec 2006 15:22:00 GMTCELKOhttp://www.sqlservercentral.com/Forums/Topic327630-335-1.aspxJoe you still in the Austin area?Fri, 22 Dec 2006 14:40:00 GMTab5srhttp://www.sqlservercentral.com/Forums/Topic327630-335-1.aspxDon't forget to go to the mall and snarl "Merry Christmas, you stupid %^%!@#$& !" at your fellow man. Fri, 22 Dec 2006 14:20:00 GMTCELKOhttp://www.sqlservercentral.com/Forums/Topic327630-335-1.aspxI've got to put up a Christmas tree, swear at the lights, murder a few carols and fight with relatives, but I will give it a go after Christmas and get back to you.Fri, 22 Dec 2006 09:55:00 GMTDavid.Poolehttp://www.sqlservercentral.com/Forums/Topic327630-335-1.aspx<P>The problem has never been selectivity... I'm still wondering how the heck the server will be able to do a seek on a search like this one : </P><P> </P><P>WHERE ( Oct_1 = 72 AND (Oct_2&gt;108 OR (Oct_2=108 AND Oct_3&gt;10) OR(Oct_2=108 AND Oct_3=10 AND Oct_4&gt;=2)) )OR Oct_1 = 73 AND (Oct_2&lt;90 OR (Oct_2=90 AND Oct_3&lt;=205) OR(Oct_2=90 AND Oct_3=205 AND Oct_4&lt;=1)) )</P>Fri, 22 Dec 2006 09:49:00 GMTNinja's_RGR'ushttp://www.sqlservercentral.com/Forums/Topic327630-335-1.aspxIn 192.168.2.10 the octets would be as follows<ol start="1"><li>192</li><li>168</li><li>2</li><li>10</li></ol>Would I be correct in thinking that by using the most changing octet SQL Server is most likely to think that the index is a good selective index?Fri, 22 Dec 2006 09:44:00 GMTDavid.Poolehttp://www.sqlservercentral.com/Forums/Topic327630-335-1.aspxAssume that octet_4 is the fastest changing octet-1 is the slowest, so I would use: CREATE INDEX IPaddresses_idx ON Foobar (octet_4, octet_3, octet_3, octet_1);That will give me the best tree structure and then I can assemble a display string in a VIEW or the front end. Wed, 20 Dec 2006 16:23:00 GMTCELKOhttp://www.sqlservercentral.com/Forums/Topic327630-335-1.aspxI'm not sure I'm following you here Joe. Can you explain more on what you think is the best indexing approach? I have no experience live or theorical in that case, especially with ultra large amounts of data.Wed, 20 Dec 2006 15:00:00 GMTNinja's_RGR'ushttp://www.sqlservercentral.com/Forums/Topic327630-335-1.aspxWhat is bad about a four octet index? In fact, I can probably build a very one by ordering the octets from fastest changing to slowest. Wed, 20 Dec 2006 14:56:00 GMTCELKOhttp://www.sqlservercentral.com/Forums/Topic327630-335-1.aspx<P>It gets materialised when indexed. So you add another 8 bytes / row + PK size.</P><P> </P><P>But then again this may be very acceptable if the inserts can be a tiny bit slower but the selects much easier to write, and maybe more efficient (still don't know if that bunch of ors completely miss the index seek or not)).</P>Wed, 20 Dec 2006 13:21:00 GMTNinja's_RGR'ushttp://www.sqlservercentral.com/Forums/Topic327630-335-1.aspxI don't think that there is any easy answer.One of the examples in the "Update your development skills to SQL2005" course uses a CLR datatype to hold an IP address.I will have to experiment to see if that works.The dilema I am facing is that on one hand I need efficient storage but on the other I need readable data.I haven't index a calculated field so I'm not sure if the field is calculated on the fly or whether it is materialised.Wed, 20 Dec 2006 13:15:00 GMTDavid.Poolehttp://www.sqlservercentral.com/Forums/Topic327630-335-1.aspxWhat about adding an indexed calculated field based on those 4 columns. Than would make the range search much more simpler and it should be much more overhead on the server (not more than having to scan an index (not sure here but it seems that a scan is hard to avoid)).Wed, 20 Dec 2006 12:45:00 GMTNinja's_RGR'ushttp://www.sqlservercentral.com/Forums/Topic327630-335-1.aspxThanks, but the WHERE Oct_1 IN () clause doesn't really provide an easy solution.I can't remember the specific IP address range for the Google ROBOT but similar exclusions look for something like72.108.10.2 to 73.90.205.1<pre>WHERE ( Oct_1 = 72 AND (Oct_2>108 OR (Oct_2=108 AND Oct_3>10) OR(Oct_2=108 AND Oct_3=10 AND Oct_4>=2)) )OR Oct_1 = 73 AND (Oct_2<90 OR (Oct_2=90 AND Oct_3<=205) OR(Oct_2=90 AND Oct_3=205 AND Oct_4<=1)) )</pre>I suspect that the data analysts may raise the relative complexity as an issue.Particularaly as a lot of queries are for ad-hoc data mining though I suppose that we could build a template for the data analysts to use.Wed, 20 Dec 2006 12:40:00 GMTDavid.Poolehttp://www.sqlservercentral.com/Forums/Topic327630-335-1.aspx>> Our data analysts wanted to build a model that excluded IP addresses in a pretty obscure range <<SELECT .. FROM .. WHERE oct_1 IN (<pretty obscure range>) AND oct_2 IN (<pretty obscure range>) etc.>>I like the display column idea but what is the performance like? << CAST(oct_1 AS VARCHAR(3)) || '.' || CAST(oct_2 AS VARCHAR(3)) || '.' || CAST(oct_3 AS VARCHAR(3)) || '.' || CAST(oct_4 AS VARCHAR(3))Which should be pretty fast compared to a lot of math on long integers. Wed, 20 Dec 2006 04:33:00 GMTCELKOhttp://www.sqlservercentral.com/Forums/Topic327630-335-1.aspxOur data analysts wanted to build a model that excluded IP addresses in a pretty obscure range The problem was coming up with a simple query that would work on 4 separate fields simultaneously.I like the display column idea but what is the performance like?Tue, 19 Dec 2006 16:10:00 GMTDavid.Poolehttp://www.sqlservercentral.com/Forums/Topic327630-335-1.aspx>> I wouldn't recommend storing an IP address as 4 separate TINYINT fields for the reasons Joe stated. An octet by itself has no meaning, it is scalar but not atomic. <<I have come to like that format. It falls into the scalar/atomic rule as four scalars/one atomic like (longitude/latitude) do for location. I can do the (0-255) restrictions on the octets easily. I can construct a disply column in a view with CAST and || operators. I can restrict each octet as I wish with reasonably simple CHECK() constraints. Tue, 19 Dec 2006 11:43:00 GMTCELKOhttp://www.sqlservercentral.com/Forums/Topic327630-335-1.aspxFor IP address manipulation, we store them as BIGINTs. We have two UDFs; one that encodes an IP address as a BIGINT and one that converts back. Information is decoded just before display and encoded before used in a query. By doing the same with the netmask, we can find the range of addresses that are on a particular subnet and simply select those devices between the range of BIGINTs represented by the range.-- MitchTue, 19 Dec 2006 11:40:00 GMTMitch Millerhttp://www.sqlservercentral.com/Forums/Topic327630-335-1.aspxThe IP Address problem is one that I am facing.When stored as a string it is a VARCHAR(15), it is human readable but difficult to do range searches.When stored as a BIGINT you can convert 192.168.10.1 to 192168010001. Still readable (just), you can do a range search on it and it now takes 8 bytes. For the pedants amongst you, yes I know you have to consider whether the column is NULLable.When stored as an INT by combining the octets you loose readability, but storage is now 4 bytes and you can do range searches.Consider that the IP address is coming from the log files of a large web farm registering millions of hits per hour and the 4 byte solution starts to look very attractive.When it comes to deciphering the IP address when it is encoded within an INT you don't. Well, not straight away.You do your data mining first and the significant IP addresses get decoded.If you want to do searches for a range of IP addresses you use the udf that encodes the IP address in the first place to tell you what encoded values to search for.I wouldn't recommend storing an IP address as 4 separate TINYINT fields for the reasons Joe stated. An octet by itself has no meaning, it is scalar but not atomic.Tue, 19 Dec 2006 11:29:00 GMTDavid.Poolehttp://www.sqlservercentral.com/Forums/Topic327630-335-1.aspx<P>Indeed it won't work - and that was my point.</P><P>The bitmask should be 3 ie binary 00000011 to mask out everything except the bottom two bits. Then, Windows would decode as 01 = 1, Linux would decode as 10 = 2, and Mac as 11 = 3. It would not matter in which order the comparisons were done. Whereas in the original article, by masking everything but one bit, both Mac and Windows would decode as 1, and the order of the comparisons would need to be considered to ensure that it worked.</P><P>In other words:</P><P>mask 00000011 AND windows 00000001 = 1</P><P>mask 00000011 AND linux 00000010 = 2</P><P>mask 00000011 AND Mac 00000011 = 3</P><P>works whatever order they are compared. Also, I was looking at the technique, not just its implementation in T-SQL. Someone could take your example and struggle to make it work in VB or C#</P><P> </P><P> </P>Sun, 17 Dec 2006 14:08:00 GMTpg53http://www.sqlservercentral.com/Forums/Topic327630-335-1.aspx>> But what happens when the number of bit columns is not known at design time, for example the processor affinity mask? <<Then you have not done your analysis phase properly. The order that you approach a DB (or pretty much any IT) problem is:1) Analysis and fact gathering. To some extent an art form; this is why Barbara Walters makes the big bucks.2) Database design and construction, followed by adding column constraints, followed by referential constraints. Then look at VIEWs, TRIGGERs, PROCEDUREs, auxiliary table and all that jazz that makes a workable model of the reality of the problem. 3) Pass the data dictionary to the application developers. Wait for them to bitch. The bad news is that people try to do it in reverse order. Start coding apps first, then cobble up a DB for just this app (designed much like a file in the host procedural language) and finally start thinking on a global/enterprise level and retro-fitting things in a mad scramble that has no data integrity. Fri, 15 Dec 2006 09:46:00 GMTCELKOhttp://www.sqlservercentral.com/Forums/Topic327630-335-1.aspx>> We chose to implement a single attribute table for this type of storage in our application. Three columns: DeviceName, AttributeName and AttributeValue. For us, a clustered PK on DeviceName/AttributeName worked best (since we most often search by device name). <<Google up "EAV design" for a few thousand words and example of why this is a total nightmare. I found an old "cut & paste". Someone like you posted this:CREATE TABLE EAV -- no key declared(key_col VARCHAR (10) NULL, attrib_value VARCHAR (50) NULL);INSERT INTO EAV VALUES ('LOCATION', 'Bedroom');INSERT INTO EAV VALUES ('LOCATION', 'Dining Room');INSERT INTO EAV VALUES ('LOCATION', 'Bathroom');INSERT INTO EAV VALUES ('LOCATION', 'courtyard');INSERT INTO EAV VALUES ('EVENT', 'verbal aggression');INSERT INTO EAV VALUES ('EVENT', 'peer');INSERT INTO EAV VALUES ('EVENT', 'bad behavior');INSERT INTO EAV VALUES ('EVENT', 'other');CREATE TABLE EAV_DATA -note lack of constraints, defaults, DRI(id INTEGER IDENTITY (1, 1) NOT NULL, bts_id INTEGER NULL, key_col VARCHAR (10) NULL, attrib_value VARCHAR (50) NULL ); INSERT INTO EAV_DATA VALUES (1, 'LOCATION', 'Bedroom');INSERT INTO EAV_DATA VALUES (1, 'EVENT', 'other');INSERT INTO EAV_DATA VALUES (1, 'EVENT', 'bad behavior');INSERT INTO EAV_DATA VALUES (2, 'LOCATION', 'Bedroom');INSERT INTO EAV_DATA VALUES (2, 'EVENT', 'other');INSERT INTO EAV_DATA VALUES (2, 'EVENT', 'verbal aggression'); INSERT INTO EAV_DATA VALUES (3, 'LOCATION', 'courtyard');INSERT INTO EAV_DATA VALUES (3, 'EVENT', 'other');INSERT INTO EAV_DATA VALUES (3, 'EVENT', 'peer');Ideally, the result set of the query would be Location Event count (headings if possible) Bedroom verbal aggression 1 Bedroom peer 0 Bedroom bad behavior 0 Bedroom other 2 Dining Room verbal aggression 0 Dining Room peer 0 Dining Room bad behavior 0 Dining Room other 0 Bathroom verbal aggression 0 Bathroom peer 0 Bathroom bad behavior 0 Bathroom other 0 courtyard verbal aggression 0 courtyard peer 1 courtyard bad behavior 0 courtyard other 1 Also, if possible, another query would return this result set. (I think I know how to do this one.) Location Event count Bedroom verbal aggression 1 Bedroom other 2 courtyard peer 1 courtyard other 1 Here is an answer From: Thomas ColemanSELECT Locations.locationvalue, Events.eventvalue, (SELECT COUNT(*) FROM (SELECT LocationData.locationvalue, EventData.eventvalue FROM (SELECT TD1.bts_id, TD1.value AS locationvalue FROM eav_data AS TD1 WHERE TD1.key = 'location') AS LocationData INNER JOIN (SELECT TD2.bts_id, TD2.value AS eventvalue FROM eav_data AS TD2 WHERE TD2.key = 'event' ) AS EventData ON LocationData.bts_id = EventData.bts_id ) AS CollatedEventData WHERE CollatedEventData.locationvalue = Locations.locationvalue AND CollatedEventData.eventvalue = Events.eventvalue FROM (SELECT T1.value AS locationvalue FROM EAV AS T1 WHERE T1.key = 'location') AS Locations, (SELECT T2.value AS eventvalue FROM EAV AS T2 WHERE T2.key = 'event') AS Events ORDER BY Locations.locationvalue, Events.eventvalue , SELECT Locations.locationvalue, Events.eventvalue (SELECT COUNT(*) FROM (SELECT LocationData.locationvalue, EventData.eventvalue FROM (SELECT TD1.bts_id, TD1.value AS locationvalue FROM eav_data AS TD1 WHERE TD1.key = 'location') AS LocationData INNER JOIN (SELECT TD2.bts_id, TD2.value AS eventvalue FROM eav_data AS TD2 WHERE TD2.key = 'event') AS EventData ON LocationData.bts_id = EventData.bts_id) AS CollatedEventData WHERE CollatedEventData.locationvalue = Locations.locationvalue AND CollatedEventData.eventvalue = Events.eventvalue) FROM (SELECT T1.value AS locationvalue FROM EAV AS T1 WHERE T1.key = 'location') AS Locations, (SELECT T2.value AS eventvalue FROM EAV AS T2 WHERE T2.key = 'event') AS Events;Is the same thing in a proper schema as: SELECT L.locationvalue, E.eventvalue, COUNT(*) FROM Locations AS L, Events AS E WHERE L.btd_id = E.btd_id GROUP BY L.locationvalue, E.eventvalue;The reason that I had to use so many subqueries is that those entities are all plopped into the same table. There should be separate tables for Locations and Events. The column names are seriously painful. Don't use reserved words like "key" and "value" for column names. It means that the developer *has* surround the column name with double quotes for everything. And they are too vague to be data element names anyway! There is such a thing as "too" generic. There has to be some structure or everything becomes nothing more than a couple of tables called "things". The real key (no pun intended) is commonality. Is there a pattern to the data that they want to store? It may not be possible to create one structure to rule them all and in the darkness bind them. "To be is to be something in particular; to be nothing in particular is to be nothing." --Aristole All data integrity is destroyed. Any typo becomes a new attribute or entity. Entities are found missing attributes, so all the reports are wrong. Try to write a single CHECK() constraint that works for all the attributes of those 30+ entities your users created because you were too dumb or too lazy to do your job. It can be done! You need a case expression almost 70 WHEN clauses for a simple invoice and order system when I tried it as an exercise. Try to write a single DEFAULT clause for 30+ entities crammed into one column. Impossible! Try to set up DRI actions among the entities. If you thought the WHEN clauses in the single CASE expression were unmaintainable, wait until you see the "TRIGGERs from Hell" -- Too bad that they might not fit into older SQL Server which had some size limits. Now maintain it. For those who are interested, there are couple of links to articles I found on the net: Generic Design of Web-Based Clinical Databases http://www.jmir.org/2003/4/e27­/ The EAV/CR Model of Data Representation http://ycmi.med.yale.edu/nadka­rni/eav_CR_contents.htm An Introduction to Entity-Attribute-Value Design for Generic Clinical Study Data Management Systems http://ycmi.med.yale.edu/nadka­rni/Introduction%20to%20EAV%20­systems.htm Data Extraction and Ad Hoc Query of an Entity— Attribute— Value Database http://www.pubmedcentral.nih.g­ov/articlerender.fcgi?tool=pub­med&pubme... Exploring Performance Issues for a Clinical Database Organized Using an Entity-Attribute-Value Representation http://www.pubmedcentral.nih.g­ov/articlerender.fcgi?tool=pub­med&pubme... Fri, 15 Dec 2006 09:34:00 GMTCELKOhttp://www.sqlservercentral.com/Forums/Topic327630-335-1.aspxRef. the example I gave above ... handles this very nicely. And, if you use VARxxx columns, it's not *overly* wasteful. Still not as small as a BIT column, nor as small as an encoded bit as described in this article, but I think that's already been well argued.Fri, 15 Dec 2006 07:26:00 GMTMitch Millerhttp://www.sqlservercentral.com/Forums/Topic327630-335-1.aspx<P>My stand - I liked the article (very well, and professionally, written imho),I want to understand the technique, but am NOT in favour of it's usage in almost any circumstance I can think off ... but that doesn't mean those curcumstances don't exist.</P><P>I am looking forward to the second article, but I would like to see it more clearly stated in the article that this is an advanced technique, for use in specialized circumstances - I fear going into a client site in years to come, and seeing many systems absed on this design because "it was a really good way to do things - you should see the article I read on it...."</P><P>Again, Lee - kudo's on the presentation of the articles, and on your repsonses in the forum which have been pertinent and not a dogmatic defence...</P>Fri, 15 Dec 2006 05:30:00 GMTRegan Galbraithhttp://www.sqlservercentral.com/Forums/Topic327630-335-1.aspx<P>I think I agree that bitmasks are to be avoided in most instances. But what happens when the number of bit columns is not known at design time, for example the processor affinity mask?</P><P>John</P>Fri, 15 Dec 2006 02:59:00 GMTJohn Mitchell-245523http://www.sqlservercentral.com/Forums/Topic327630-335-1.aspx<P>Call me a "purist" or whatever, I don't care. This article is well written and this kind of stuff is fun and interesting (in limited quantities), but it is also completely stupid to do in a relational database, period, end of story! Any benefit accrued by this approach will be more than offset by the loss of data integrity and clarity.</P><P>The arguments in favor of this technique betray a sad lack of understanding. (I almost said "shocking" but this kind of ignorance has long ago ceased to shock me.) </P><P>The Relational Model says very little about data types and says absolutely NOTHING about limiting data type complexity, so arguments like "well, Dr. Codd couldn't have had data type X in mind when he came up with the RM, therefore the RM must be inadequate for handling data type X...blah, blah, blah..." are just stupidity of the highest order. </P><P>The problem with complex data types (including relation valued data types) is that in order to support them relationally you must have operators for them. Having operators for a data type is much more than just being able to insert, update, select, and delete them. The difficulties involved in implementing complex data types have NOTHING to do with the RM.</P>Thu, 14 Dec 2006 15:13:00 GMTDCPetersonhttp://www.sqlservercentral.com/Forums/Topic327630-335-1.aspx<P>The points critcal of this article are well made. That said - there is certainly a place for this sort of thing in a system designed to quickly capture lots of information in a condensed format from very many concurrent users. I've used bitmasks to do just that and asyncronously replicated the data to relational data stores where the "secretary with an excel spreadsheet" can get at the information represented. In fact the example the author mentions, that of capturing session information, is a perfect one for this sort of thing. Bitmasking is obviously not a good idea everywhere however. Did anyone say it was? BTW - the 50's were great! Good article!</P>Thu, 14 Dec 2006 12:33:00 GMTdvdvonhttp://www.sqlservercentral.com/Forums/Topic327630-335-1.aspxEric Wilson - I could not have said it better. Well put.This is important information to have in one's toolkit, only for the times when you have to undo it in a system you've inherited from someone who thought it was a good idea :-)Thu, 14 Dec 2006 10:44:00 GMTMerrill Aldrichhttp://www.sqlservercentral.com/Forums/Topic327630-335-1.aspx<P>Hello...</P><P> </P><P>I have to agree with most former posters! Bitmasking is NOT the way to go... I can see the "posibility" to store "ONE" flagged enumerations inside a single field(This is "possibe but BAD"), but mixing several fields into one is EVIL. It will almost allways lead to some chaos. </P><P>If you think about bitmasking in the suggested manner, why not deserialize complete object graphs directly into the Database? You will be able to get along with only 3 fields and one Table for your WHOLE System! Isnt that sweet? Of course you could encode the objecttype into the binary data also and you could do without a primary key... Now we got 1 Table with one field to store all our data! Sweet!</P><P>But i am interested in indexing this system... Will you create a index for each datatype/bit position, so you can determine if bit 37 is set or not? A regular index wont help here, so you have to get fancy there also... (I can only think along the lines of computed colums (myBitMaskField &amp; (128+256)) which need to be indexes seperately, but then you would have the "extra columns which you wanted to avoid...)</P>Thu, 14 Dec 2006 05:45:00 GMTHeiko Hatzfeldhttp://www.sqlservercentral.com/Forums/Topic327630-335-1.aspxGood One Wed, 13 Dec 2006 21:57:00 GMTAkash Agarwalhttp://www.sqlservercentral.com/Forums/Topic327630-335-1.aspxOh my heavens what crap!WARNING TO NEWBIES: Please do not be dissuaded by the seeming "balance" of comments supporting bit-masking as a "useful" technique.POINT #1: You cannot do bitmasking in a relational database. Period. Doing this "technique" by definition makes it no longer relational. (Sure, you can do it in some "relational" products, the same way you can hammer nails with an HP calculator. Doesn't mean you should.) This sort of "step outside the rules" way have custom handling data values was *exactly* the sort of idiocy relational was invented to avoid. Heed Celko's post earlier and the others that echo this sentiment.(To be clear, putting data into a "relational" product does not make it a relational database any more than typing characters in Word makes you a Lawyer. See <a href="http://www.datazulu.com/portal/Home/tabid/36/EntryID/4/Default.aspx">http://www.datazulu.com/portal/Home/tabid/36/EntryID/4/Default.aspx</a>.)POINT #2: It *is* useful to understand how this kind of thing works. This is because at some point in your career you are likely to inherit some crap like this built by people doing what's "cool" instead of what's right and you will have to untangle the mess. To that end, this is a well-written article. Good use of screen-shots, etc. I hope Part 2 covers how to screen-capture your order processing system, do text recognition, and print out a spreadsheet the secretary can use to type into the fulfillment system. This is another example of craptastic systems we are likely to inherit. It might be good to know the mind-set of those who build such things.POINT #3: Be especially cautious of so-called performance benefit. It is very unlikely in SQL Server due to the availability of 1-bit columns. (As other comments point out.) You should explore such extreme violations of clarity and logic like this technique advocates ONLY for cases where you prove the benefit and the solution is in an ultra-performance-sensitive area.(For cases I've inherited, 8 of 9 of them had no performance benefit whatever. Some were worse. They did cause confusion though, all 9 of them. One of the 9 had a very minor performance benefit and it was not a performance-critical system. Across dozens of systems I have never seen one that had such a bit-crunch and performance problem that this technique was the best solution.)For those who wish to turn a relational database engine into an indexed file system or a C++ persistence layer, then all bets are off. But most of us are trying to collect and manipulate data in ways that are most useful and transparent.To me, using bit-masking in an otherwise relational database is like injecting a little bit of COBOL into your Java app just to show you know COBOL. In every case I've seen this where the person was still around, it was clear the only *real* reason they did it was to feel smart. Well, sorry, but that's a very *dumb* reason to put hacks like this into a system.Wed, 13 Dec 2006 19:24:00 GMTEric Wilsonhttp://www.sqlservercentral.com/Forums/Topic327630-335-1.aspx"Hmmm, let's see; column A547DD tracks the OS while columns A547DE tracks cookies. Piece of cake." I think not.I can see where you're coming from now ... 'cause it's easier to have 100 undocumented bits than 100 columns that have been defined in a dictionary.(smacking forehead)Wed, 13 Dec 2006 17:20:00 GMTMitch Miller