I have gathered some notes frow my SQLServer guru friends on the topic when I asked a similar question. Here they are:
Note #1 From: Doug Nelson Date: Jul 12, 2005 12:11 PM if you mean on/off true/false I prefer to use smallints as the datatype - 1 -> true 0 -> false Then when I write stored procs, I can pass a don't care value of -1 for example select * from myTable where myBitField = case when @MyParam = -1 then myBitField else @MyParam end anyway, my two cents Note #2 From: Jeffrey Schoolcraft Date: Jul 12, 2005 12:00 PM Use them if they're truly bits (on/off, true/false). Don't use them if you'd be better off with INT's and use 0,1, for false/true and 2 for new and 3 for bar and something else for something else (status codes or something). Also use them if a record could have all bits apply to them. Instead of having one field with six statuses, a record might have 0,3,5 and another 0 and another 0,1,2,3,5 it's easier to do that with bit fields than with some mask or some other 1-m/m-m recordXstatus type table. You can do some interesting things like this though I've done some other stuff with similar ideas. http://www.cs.newpaltz.edu/~pletch/ADB/char_func.pdf Note #3 From: Paul G. Chu Date: Jul 12, 2005 12:07 PM Chaz, Old time mainframe programmers used "bit masks" to store boolean state for multiple switches in one character ( byte ) or 2 bytes etc. Here's a sql server article: http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=5970 Sql Bit manipulation here http://www32.brinkster.com/srisamp/sqlArticles/article_6.htm Note #4 From: Jeffrey Schoolcraft Date: Jul 12, 2005 12:11 PM 1. we're not old time. 2. memory is cheap 3. it won't overly affect performance to have 15 bit fields, it might be confusing for developers to GROK bit masks (and the associated Flags attribute for enumartions and bitwise | & comparisons) 4. design might be more intuitive with 15 bit fields just food for thought, not flaming to flame. Note #5 From: Jeffrey Schoolcraft Date: Jul 12, 2005 12:05 PM some people say don't use BIT's use INTs. Some people also say define all your lengths as powers of 2 (so varchar(256) instead of varchar(200)) Some of them are for "performance" reasons, like the latter, which I don't buy into. Some of them (the former) are out of fear the database will change and they'll need to do more than be able to just capture 0/1 but some other state as well. I don't buy that either, change or stick to your guns. BITs are BITs and they're damn good at being my dbBool. Note #6 From: Paul G. Chu Chaz, Whatever the database column is.... Design a sql server function to turn on / off the switches and abstract out the details from your developers. All the bit twiddling is done by one sql developer. Also, need a function to display the state of all switches for queries too. You need to consider performance design here. When comparing a field with aggregate of switches you will have a function operation to compare instead of a easy true or false for separate Bit fields. Note #7 From: Doug Nelson Date: Jul 12, 2005 10:38 PM A little more information on bitfields Bitfields are handy away to code user roles for most system, provided the total number of roles is less than either 31 or 63. These correspond to using a 32 bit int or a 64 int bit, avoid the most significant bit which is a sign bit. Each bit corresponds to a binary weighted number, 1,2,4,8.... In most systems, users are in mulitple roles at the same time, and be summing the enabled bits, the resultant number can be stored in a single integer field in the user table. A simple logical compare with the desired role weight yeilds whether the user is in the role or not. const int PowerUser = 2 const int LocalAdmin = 4 const int God = 8 if the user is a Power User and a Local Admin, the userRole value would be 2 + 4 = 6 if ( UserRole && LocalAdmin > 0 ) { // User is LocalAdmin } if ( UserRole && PowerUser . 0 ) { // User is Power User } a simple IsInRole function can be written as function bool IsInRole( int UserRoles, int DesiredRole ) { if ( UserRoles && DesiredRole ) { return true; } return false; } Using this function, you easily add roles to the system without having to resort to adding columns to datatables which would be required in you were using bit database fields. On 11/2/05, sas0riza <[EMAIL PROTECTED]> wrote: > Hi, > > I have a SP where I insert into 2 tables. On the second insert, I use > the PK from the first insert. My question is, how do flag a column in > the second table? In the second table, I have several columns that > are > of type bit. > > Thanks. > > > > > > > ________________________________ > YAHOO! GROUPS LINKS > > > Visit your group "AspNetAnyQuestionIsOk" on the web. > > To unsubscribe from this group, send an email to: > [EMAIL PROTECTED] > > Your use of Yahoo! Groups is subject to the Yahoo! Terms of Service. > > ________________________________ > ------------------------ Yahoo! Groups Sponsor --------------------~--> Get Bzzzy! (real tools to help you find a job). Welcome to the Sweet Life. http://us.click.yahoo.com/A77XvD/vlQLAA/TtwFAA/saFolB/TM --------------------------------------------------------------------~-> Yahoo! Groups Links <*> To visit your group on the web, go to: http://groups.yahoo.com/group/AspNetAnyQuestionIsOk/ <*> To unsubscribe from this group, send an email to: [EMAIL PROTECTED] <*> Your use of Yahoo! Groups is subject to: http://docs.yahoo.com/info/terms/
