Charles Kincaid (9/18/2012)
DB.Duck (9/17/2012)
CELKO (9/9/2012)
...we do not use BIT flags in SQL...You, sir, couldn't be more wrong. That is all.
Actually it's almost a great point but poorly stated. Let me give this a shot. For the most part you should avoid bit flags.
Often they are used to replicate data. Case in point is the [TerminationDate] of an employee. That column will be NULL for everyone who is still working and have a valid date for folks who have, or were, terminated. Therefore a [Terminated] bit column is not needed.
Then there is the whole aspect of indexing on bits. SQL Server is a quite fine product (all software has bugs so don't start) but even the greatest software has to make compromises. Indexing on a bit suffers. I'll leave further reading to you. There are many good articles and books.
Bits are tempting. Recent versions will cram 8 bits into one byte of actual storage. Sounds cool. You create the first one at design time. You are sucked into a false sense as adding the next seven are very fast (only the meta data is changed). Now you add the ninth and wait as every page in your table is rewritten.
Ok so I'm willing to accept that bit flags should be used when appropriate. For example when you want to know if a professor has tenure or not, but don't care when they got it. At that point putting in a date field is wasteful. Or possibly a better example would be if you are storing the results of a questionnaire and have a number of true/false or yes/no questions. It's really all about what your data requirements are.
I haven't read about the indexing problem of bit's but I certainly will at this point. Either way though I don't imagine it being all that helpful as the cardinality of a bit is bound to be terrible.
As far as that 9th bit column. I could be wrong (and that happens far more frequently than I would like) but wouldn't the 9th bit column (adding 1 byte to the row) be the same effect as adding a char(1), tinyint, or really any data type?
Kenneth FisherI was once offered a wizards hat but it got in the way of my dunce cap.--------------------------------------------------------------------------------For better, quicker answers on T-SQL questions, click on the following... http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]For better answers on performance questions, click on the following... http://www.sqlservercentral.com/articles/SQLServerCentral/66909/[/url]Link to my Blog Post --> www.SQLStudies.com[/url]