Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2001-08-21 : 09:47:09
|
| Paul writes "I'm surprised to find out that within the organization I work for the DBA people don't understand or believe that a bitmap column and associated table can be supported with referential integrity. This technique is often much superior from an application point of view. It's simply has better performance, less code complexity and thereby fewer points of failure. It's is easier to impliment and work with and a good alternative to intersection tables for many/many relationship when the dataset is known to be small (<= 32/64 entries) and very stable. However, in todays world of built in contraint facilities, it seems odd because it requires a trigger to enforce the integrity. This is because the current constraint facility in SQLServer is based on a unique and exact match to a single row.Can anyone offer words of encouragement on the bitmap technique or truely offer a legitimate reason why it isn't a good practice? Everything I've heard locally so far is untrue and erroneous. The method is outlined below...-------------------------------Table A as column Bits that is an int composed of bit values that are documented in Table B. Tables B has 2 columns, aBit int and aDesc varchar. Data in B is 0x0001,"Value 1", 0x0002,"Value 2", 0x0004,"Value 3", 0x0008,"Value 4"...The intersection is:Select B.aDesc from A,B where (A.Bits & B.aBit) = B.AUpdate to A.Bits is simple Bits = @Bits A trigger would be used to guarantee that Bits is found.It would be something like select @theBits = sum(aBit) from Bif (@theBits & @Bits) <> @Bits some bits aren't definedIn the web world I can load the table B once at the start of a web application and never need to reload or access it again.I can read a single row for my data and not have to read multiple record sets. Either way I have the complosition to represent an update back. From 3rd party application the select can be hidden in an view just as it probablly would for an intersection table.This is opposed to:Table A has index A_ID, Table B has index B_ID and varchar aDesc,Table C has A_ID, B_ID intersections.To udpate the relationship I might be set a string of ids in a varchar separated by commas. 1,2,3 etc..declare @sqlstr varchar(200)select @sqlstr = 'select ' + @A_ID + ' A_ID, B_ID into #B1 from B were B.B_ID in (' + @B_IDs + ')'execute @sqlstr -- or use an from stored procedure to return pick out the ids begin transactiondelete C.* from Intersection C where C.A_ID = @A_IDinsert into Intersection C (A_ID, B_ID)select A_ID, B_ID from #B1committo select the row - select * from A where A_ID = @A_IDto select each intersection set ---select B.* from C,B,A Where A_ID = @A_IDand C.A_ID = @A_IDand B.B_ID = C.B_ID------------------I hope this is clear enough and someone can give me feedback..Basically I'm looking for proof it's bad, proof it's good, recommendations, references, etc.." |
|
|
|
|
|
|
|
Bitmap columns and document tables in SQLServer