Help with SQL Server (NOT A programming question) [modified]
-
I need some strong supportive arguments to use "bit" field type in a SQL Server 2000/2005 table to store boolean values. Our application is in WPF with C# & XAML. Our DBA wants to use char(1) field with constraints of 'Y','y','N','n' in the database which uses Latin1_General_CS_AS (case sensitive/accent sensitive) collation. I am baffled by the suggestion of the DBA. In my past experience, I've never heard of such a wild idea. The DBA proposes the char(1) field type because of two reasons: 1) When an admin looks at the table, 'Y','N','y','n' are more obvious than 0 and 1. (But then again, if one does not know 0 & 1 (for bit field) then s/he shouldn't be admin in the first place)! 2) In HIS past projects, he used char(1) and no one complained. My problems are.. - This breaks all the queries/SQL that has been written to do true/false (0/1) comparison. - Unlike bit field (which only has two values), another isuse wih char is that unless someone explicitely looks at the constraint, there is no clea indication that the field only accepts Y & N. - Bit is CLR/SQL compliant. - Char is more expensive (size & equality comparison wise) than bit. - The mapping will have to be changed in my business objects from boolean to string or char. - UI will have to introduce some new logic for converting char/string to boolean for displaying radio/checkbox options .. :( So anyways ... any suggestions? or links to Microsoft/publication white papers? I'd really like have some articles to back my argument. Thanks, - Malhar -- modified at 17:23 Monday 10th September, 2007
As you stated, bits are the appropriate type to use here but in case you lose that battle, I have a suggestion:
malharone wrote:
- The mapping will have to be changed in my business objects from boolean to string or char. - UI will have to introduce some new logic for converting char/string to boolean for displaying radio/checkbox options ..
Change your mapping or data access layer to convert the string to and from a boolean and keep the boolean in your business object. This means no changes to the UI and it makes more logical sense in the program.
WarePhreak Programmers are tools to convert caffiene to code.
-
I need some strong supportive arguments to use "bit" field type in a SQL Server 2000/2005 table to store boolean values. Our application is in WPF with C# & XAML. Our DBA wants to use char(1) field with constraints of 'Y','y','N','n' in the database which uses Latin1_General_CS_AS (case sensitive/accent sensitive) collation. I am baffled by the suggestion of the DBA. In my past experience, I've never heard of such a wild idea. The DBA proposes the char(1) field type because of two reasons: 1) When an admin looks at the table, 'Y','N','y','n' are more obvious than 0 and 1. (But then again, if one does not know 0 & 1 (for bit field) then s/he shouldn't be admin in the first place)! 2) In HIS past projects, he used char(1) and no one complained. My problems are.. - This breaks all the queries/SQL that has been written to do true/false (0/1) comparison. - Unlike bit field (which only has two values), another isuse wih char is that unless someone explicitely looks at the constraint, there is no clea indication that the field only accepts Y & N. - Bit is CLR/SQL compliant. - Char is more expensive (size & equality comparison wise) than bit. - The mapping will have to be changed in my business objects from boolean to string or char. - UI will have to introduce some new logic for converting char/string to boolean for displaying radio/checkbox options .. :( So anyways ... any suggestions? or links to Microsoft/publication white papers? I'd really like have some articles to back my argument. Thanks, - Malhar -- modified at 17:23 Monday 10th September, 2007
By far the best argument is that it will make the business logic harder to implement. The database is storage for the application, not a pretty picture for the dba to look at. Maybe you can get him to accept views that show bit fields as Y/N. While you are at it, get him an Etch-A-Sketch and tell him it's a new Vista tablet PC. That will keep him out of your hair for a while.:laugh:. Tim Bailey Energy Laboratories, Inc.
-
I need some strong supportive arguments to use "bit" field type in a SQL Server 2000/2005 table to store boolean values. Our application is in WPF with C# & XAML. Our DBA wants to use char(1) field with constraints of 'Y','y','N','n' in the database which uses Latin1_General_CS_AS (case sensitive/accent sensitive) collation. I am baffled by the suggestion of the DBA. In my past experience, I've never heard of such a wild idea. The DBA proposes the char(1) field type because of two reasons: 1) When an admin looks at the table, 'Y','N','y','n' are more obvious than 0 and 1. (But then again, if one does not know 0 & 1 (for bit field) then s/he shouldn't be admin in the first place)! 2) In HIS past projects, he used char(1) and no one complained. My problems are.. - This breaks all the queries/SQL that has been written to do true/false (0/1) comparison. - Unlike bit field (which only has two values), another isuse wih char is that unless someone explicitely looks at the constraint, there is no clea indication that the field only accepts Y & N. - Bit is CLR/SQL compliant. - Char is more expensive (size & equality comparison wise) than bit. - The mapping will have to be changed in my business objects from boolean to string or char. - UI will have to introduce some new logic for converting char/string to boolean for displaying radio/checkbox options .. :( So anyways ... any suggestions? or links to Microsoft/publication white papers? I'd really like have some articles to back my argument. Thanks, - Malhar -- modified at 17:23 Monday 10th September, 2007
I *MOSTLY* agree with what everyone has said about showing this to the DBA's or your manager, but it would sound like some aren't quite listening to you or have read between the lines... I am in the same position that a lot of the posters are probably in: I am the lead programmer and the DBA for our organization, so I do not have the issues you are having. I will also say nothing chaps my hide more than looking at the SQL database some third party has developed and seeing things like CHAR(1) fields used for something that boolean should have been used for, so that isn't the issue. However, from the sounds of it, bumping heads with this DBA will more than likely result in things being made worse, to the point that his requirements will be totally assinine just to be spiteful or to push his weight around. All of your arguements are valid as far as the problems this will cause, and the answers here (especially about bit versus byte and being able to have 7 more bit fields in a table for the same cost as one) are very good, so I would hope that maybe in your company there is another DBA or DBA assistant you can approach that may have the ear of the DBA in question to point out your arguements. Only if you can't do that, or it fails would i go above his head. As far as links, in this case MSDN would have plenty of links on the memory usage of different data types, and look at best practices on MS website as well. Also, you might find something in patterns and practices from Microsoft. Hope this helps Cosmo
-
Let me guess, he stores dates as strings too, right ?
Christian Graus - Microsoft MVP - C++ "I am working on a project that will convert a FORTRAN code to corresponding C++ code.I am not aware of FORTRAN syntax" ( spotted in the C++/CLI forum )
Well think about it guys. The hardware stores and retrieves data in 8 bit bytes, right? Unless you have defined 8 single bit fields in a row your one bit switch is going to take up a whole byte anyway. Also there is a thing called a check bit that comes into play. If your server is using ECC memory then by using bit switches you lose the error correcting capability. Does anyone know if SQL server actually stores a one bit field as a bit or does it store it as a byte?
-
I agree with the Bit argument, but there is also anm argument for using a char(1). we use chars, and yes its been like it since before i started the compant, and it has cases the odd problem. but by using a char it makes the fiels nicely expandable. i just reciently in the last couple of days had to add a 3rd valud to a Y/N field for one custoner. so the database schema is shared, and now one customer has Y/N/X dont ask, but you know customers. also ive uses Y/N/[null] as a was of saying it is yes or no, but null is not set. so there can be time where this is aplicable, and like someone else said. the cost of a char is no more or less, and every /Net control can use what ever value you want, so no hard no foul. (till some newbie f's thing up by of cause). oh love the comment about storing the enum name thats class!!
-
I agree on all points although I'll add that SQL Server will use a whole byte for a bit field and so a dba might consider having only one bit field as wasteful or something. I think the original post stated char(1) so the end result would be comparable but using bit would allow for 7 more bit fields in the table without any row size increase but the same 7 extra char(1) would add 7 more bytes, no brainer for me. Personally I use bit for boolean every time.
Without knowing the problem domain, the programming language(is it T-SQL?) and at least some information about the size and use of the database, it's pretty hard to make a decision about which is best and why. I've been a DBA for 15 years and 12 of that with SQLServer and I'd have to say, it depends. I would say this however, I have spent many nights looking at data because I was paged over a critical system being down and I would much rather select the rows of data and compare them against a "Y" or "N" then trying to do some bit manipulation to see which bit was set. MHO If you feel strongly about this, do the research, write the emails, try to make a difference and if things blow up, produce your emails and sit around with a smug satisfied look. Just one more note, DO NOT SHOW THIS THREAD TO YOUR MANAGEMENT!!!!!. I am certain they will NOT take it seriously when they see someone giving you advice about leaving your job over some compromises that the company has made. The last thing you want people in your company to think is that your are childish and will run whenever you don't get your way.
-
By far the best argument is that it will make the business logic harder to implement. The database is storage for the application, not a pretty picture for the dba to look at. Maybe you can get him to accept views that show bit fields as Y/N. While you are at it, get him an Etch-A-Sketch and tell him it's a new Vista tablet PC. That will keep him out of your hair for a while.:laugh:. Tim Bailey Energy Laboratories, Inc.
-
I need some strong supportive arguments to use "bit" field type in a SQL Server 2000/2005 table to store boolean values. Our application is in WPF with C# & XAML. Our DBA wants to use char(1) field with constraints of 'Y','y','N','n' in the database which uses Latin1_General_CS_AS (case sensitive/accent sensitive) collation. I am baffled by the suggestion of the DBA. In my past experience, I've never heard of such a wild idea. The DBA proposes the char(1) field type because of two reasons: 1) When an admin looks at the table, 'Y','N','y','n' are more obvious than 0 and 1. (But then again, if one does not know 0 & 1 (for bit field) then s/he shouldn't be admin in the first place)! 2) In HIS past projects, he used char(1) and no one complained. My problems are.. - This breaks all the queries/SQL that has been written to do true/false (0/1) comparison. - Unlike bit field (which only has two values), another isuse wih char is that unless someone explicitely looks at the constraint, there is no clea indication that the field only accepts Y & N. - Bit is CLR/SQL compliant. - Char is more expensive (size & equality comparison wise) than bit. - The mapping will have to be changed in my business objects from boolean to string or char. - UI will have to introduce some new logic for converting char/string to boolean for displaying radio/checkbox options .. :( So anyways ... any suggestions? or links to Microsoft/publication white papers? I'd really like have some articles to back my argument. Thanks, - Malhar -- modified at 17:23 Monday 10th September, 2007
Tell the DBA he can create a View to translate the bit field into a "more descriptive" char(1) field. Then take away his/her rights to read/write tables and give him/her read access to Views. Then ask your boss to pay you for the DBA work you are doing.
-
That's so true. Bit are seen as True or False in Enterprise Manager. ORRRRRRRRR He should just make HIS own queries to see the data that says something like.... (CASE WHEN 0 THEN 'Please note DBA.. This is FAAALLLSSEEEE' WHEN 1 THEN 'This is TRUEEE DBAAA, TRUEEE' END) AS MyStupidDBAHelper HAHAHAHA
Nah, a computed column.
-
:) .. it's not that bad! Our disagreement is on booleans. Though he wants me to textualize enums and store their text values as strings instead of storing the numeric representation. Currently, we don't use [flags] on enums and we are too scared of renaming the enum so it has been working out so far. Anyways, he's at a highler level than I am in the hierarchy of things so my voice has no opinion to the management. Furthermore, the "varchar" for boolean strangely made into the "SQL Standards" document in the company (before I joined) and even more strangely it got approved. So as of now, I look like a clueless idiot! This is so obvious, may be that's why I wasn't able to find any links. Any suggestion would be greatly helpful.
Wow, I think firing someone or leaving your job is a little overkill for this problem. I think you might have to look at this from the DBA or even the customer's point of view. I have no idea what the boolean field will be used for, but if the software your designing is the only thing accessing it, then you probably have a valid argument. However, if this field will be accessed from multiple applications - Crystal Reports, SQL Reporting Services, Analysis Services, Some Web App, etc.. - then you have to think about how it will look in those reports and applications. From your point of view, a 0 and 1 is pretty straight forward. However, from the customer's point a view a 0 or 1 does not look nice in a report. You have to take a step back and look at the big picture. Does the performance increase of using a binary datatype outweigh the readability of the data? How many rows of data are you expecting and how often will this field be accessed? These are all questions you need to ask. Speak with your DBA, if he has been there longer than you, he may know the reasons for doing it that way. Maybe the rest of you need to think through situations a bit more before jumping to conclusions based on so little information. I suppose its just too easy to judge people. -- modified at 11:10 Tuesday 11th September, 2007
-
I need some strong supportive arguments to use "bit" field type in a SQL Server 2000/2005 table to store boolean values. Our application is in WPF with C# & XAML. Our DBA wants to use char(1) field with constraints of 'Y','y','N','n' in the database which uses Latin1_General_CS_AS (case sensitive/accent sensitive) collation. I am baffled by the suggestion of the DBA. In my past experience, I've never heard of such a wild idea. The DBA proposes the char(1) field type because of two reasons: 1) When an admin looks at the table, 'Y','N','y','n' are more obvious than 0 and 1. (But then again, if one does not know 0 & 1 (for bit field) then s/he shouldn't be admin in the first place)! 2) In HIS past projects, he used char(1) and no one complained. My problems are.. - This breaks all the queries/SQL that has been written to do true/false (0/1) comparison. - Unlike bit field (which only has two values), another isuse wih char is that unless someone explicitely looks at the constraint, there is no clea indication that the field only accepts Y & N. - Bit is CLR/SQL compliant. - Char is more expensive (size & equality comparison wise) than bit. - The mapping will have to be changed in my business objects from boolean to string or char. - UI will have to introduce some new logic for converting char/string to boolean for displaying radio/checkbox options .. :( So anyways ... any suggestions? or links to Microsoft/publication white papers? I'd really like have some articles to back my argument. Thanks, - Malhar -- modified at 17:23 Monday 10th September, 2007
malharone wrote:
Our DBA wants to use char(1) field with constraints of 'Y','y','N','n'
Sounds like someone uses Access a little too much. That is what this reminds me of. If my memory serves me correctly, when in Access, a boolean is represented as Y/N. However, even though I am NOT a dba, I stay as far away from Access as humanly possible. Sounds like your dba used to be an Access dba. I got an Access guy here who says that he prefers the Access UI in favor of a web UI. I told him that he just hasn't seen a good web UI yet then, because the ones we have here stink. X| And yes, I am changing jobs... Anywho, you asked about articles. Here is something to help. The first link talks about bit storage and string conversions: http://msdn2.microsoft.com/en-us/library/ms177603.aspx[^link^] This is about char and varchar: http://msdn2.microsoft.com/en-us/library/ms176089.aspx[^link^] It mainly talks about storage sizes. That can be one of your arguments.
Success is the happy feeling you get between the time you do something and the time you tell a woman what you did. --Dibert My left name is Tremendous Savings, Ms. America – Señor Cardgage
-
I need some strong supportive arguments to use "bit" field type in a SQL Server 2000/2005 table to store boolean values. Our application is in WPF with C# & XAML. Our DBA wants to use char(1) field with constraints of 'Y','y','N','n' in the database which uses Latin1_General_CS_AS (case sensitive/accent sensitive) collation. I am baffled by the suggestion of the DBA. In my past experience, I've never heard of such a wild idea. The DBA proposes the char(1) field type because of two reasons: 1) When an admin looks at the table, 'Y','N','y','n' are more obvious than 0 and 1. (But then again, if one does not know 0 & 1 (for bit field) then s/he shouldn't be admin in the first place)! 2) In HIS past projects, he used char(1) and no one complained. My problems are.. - This breaks all the queries/SQL that has been written to do true/false (0/1) comparison. - Unlike bit field (which only has two values), another isuse wih char is that unless someone explicitely looks at the constraint, there is no clea indication that the field only accepts Y & N. - Bit is CLR/SQL compliant. - Char is more expensive (size & equality comparison wise) than bit. - The mapping will have to be changed in my business objects from boolean to string or char. - UI will have to introduce some new logic for converting char/string to boolean for displaying radio/checkbox options .. :( So anyways ... any suggestions? or links to Microsoft/publication white papers? I'd really like have some articles to back my argument. Thanks, - Malhar -- modified at 17:23 Monday 10th September, 2007
My name is Jamie Haddock. I helped Jeffrey Richter with the creation of 'CLR via C#" (Microsoft Press - See acknowledgement section of book). It concerns me deeply to hear about your situation. And it concerns me even more to hear the choices your DBA has made for the variable types your application uses. Based on what you have stated regarding the uncooperative nature of your DBA, I recommend you review your application's SQL DB requirements with your management and let them decide how best to deal with the DBA. You sound like your understand what you are talking about. Explain it to your management. If they disagree with you then you'll have to decide if you want to remain in that work environment.
-
I need some strong supportive arguments to use "bit" field type in a SQL Server 2000/2005 table to store boolean values. Our application is in WPF with C# & XAML. Our DBA wants to use char(1) field with constraints of 'Y','y','N','n' in the database which uses Latin1_General_CS_AS (case sensitive/accent sensitive) collation. I am baffled by the suggestion of the DBA. In my past experience, I've never heard of such a wild idea. The DBA proposes the char(1) field type because of two reasons: 1) When an admin looks at the table, 'Y','N','y','n' are more obvious than 0 and 1. (But then again, if one does not know 0 & 1 (for bit field) then s/he shouldn't be admin in the first place)! 2) In HIS past projects, he used char(1) and no one complained. My problems are.. - This breaks all the queries/SQL that has been written to do true/false (0/1) comparison. - Unlike bit field (which only has two values), another isuse wih char is that unless someone explicitely looks at the constraint, there is no clea indication that the field only accepts Y & N. - Bit is CLR/SQL compliant. - Char is more expensive (size & equality comparison wise) than bit. - The mapping will have to be changed in my business objects from boolean to string or char. - UI will have to introduce some new logic for converting char/string to boolean for displaying radio/checkbox options .. :( So anyways ... any suggestions? or links to Microsoft/publication white papers? I'd really like have some articles to back my argument. Thanks, - Malhar -- modified at 17:23 Monday 10th September, 2007
Eh...So the fact that True/False in C# is 1 or 0 on the backend isn't enough to convince them? If you must have articles... http://msdn2.microsoft.com/en-us/library/aa664483(VS.71).aspx Explains how to use Boolean logic in C#, which translates to 1, 0 or null(unknown) Using anything other than a number or bit to denote true/false requires a lot of extra work on the developer at the expense of simply briefing everyone that 1 means yes and 0 means no. Christopher MCITP: Database Administrator Managing 48,000 production databases is a piece of cake...sure...yeah...that's what they told me in the interview.
-
I need some strong supportive arguments to use "bit" field type in a SQL Server 2000/2005 table to store boolean values. Our application is in WPF with C# & XAML. Our DBA wants to use char(1) field with constraints of 'Y','y','N','n' in the database which uses Latin1_General_CS_AS (case sensitive/accent sensitive) collation. I am baffled by the suggestion of the DBA. In my past experience, I've never heard of such a wild idea. The DBA proposes the char(1) field type because of two reasons: 1) When an admin looks at the table, 'Y','N','y','n' are more obvious than 0 and 1. (But then again, if one does not know 0 & 1 (for bit field) then s/he shouldn't be admin in the first place)! 2) In HIS past projects, he used char(1) and no one complained. My problems are.. - This breaks all the queries/SQL that has been written to do true/false (0/1) comparison. - Unlike bit field (which only has two values), another isuse wih char is that unless someone explicitely looks at the constraint, there is no clea indication that the field only accepts Y & N. - Bit is CLR/SQL compliant. - Char is more expensive (size & equality comparison wise) than bit. - The mapping will have to be changed in my business objects from boolean to string or char. - UI will have to introduce some new logic for converting char/string to boolean for displaying radio/checkbox options .. :( So anyways ... any suggestions? or links to Microsoft/publication white papers? I'd really like have some articles to back my argument. Thanks, - Malhar -- modified at 17:23 Monday 10th September, 2007
I think your best arguement is that instead of testing for true/false, you're going to start with 'y' and 'Y', but then someone will decide that "T" and "F" should also work. So after that change sneaks into code (and maybe gets released) there will be this big panic while everyone tries to figure out why the application is broken. Eventually someone will figure out what happened and 't' and 'T' will be added to the test(s which may occur in a lot of places). Then six months later after everyone has forgotten what happened last time, someone will decide the '0' and '1' should also work. Then the next year someone will decide that all you need to do to internationalize the application to Mexico or to comply with some state mandate of supporting Spanish, is to allow "Si" and "No" so now that test will have to look for 't', 'T', 'y', 'Y', '1', 'x', 'X' (someone will think of a check box sooner or later..), 's' and 'S'. ... And although this could gurantee you employment for the next several years, the risk is that enough customers get tired of the constant updates that require a patch a few weeks later that they'll find another vendor. And of course the costs of redeploying each patch is going to seriously eat into the Christmas party.
-
I need some strong supportive arguments to use "bit" field type in a SQL Server 2000/2005 table to store boolean values. Our application is in WPF with C# & XAML. Our DBA wants to use char(1) field with constraints of 'Y','y','N','n' in the database which uses Latin1_General_CS_AS (case sensitive/accent sensitive) collation. I am baffled by the suggestion of the DBA. In my past experience, I've never heard of such a wild idea. The DBA proposes the char(1) field type because of two reasons: 1) When an admin looks at the table, 'Y','N','y','n' are more obvious than 0 and 1. (But then again, if one does not know 0 & 1 (for bit field) then s/he shouldn't be admin in the first place)! 2) In HIS past projects, he used char(1) and no one complained. My problems are.. - This breaks all the queries/SQL that has been written to do true/false (0/1) comparison. - Unlike bit field (which only has two values), another isuse wih char is that unless someone explicitely looks at the constraint, there is no clea indication that the field only accepts Y & N. - Bit is CLR/SQL compliant. - Char is more expensive (size & equality comparison wise) than bit. - The mapping will have to be changed in my business objects from boolean to string or char. - UI will have to introduce some new logic for converting char/string to boolean for displaying radio/checkbox options .. :( So anyways ... any suggestions? or links to Microsoft/publication white papers? I'd really like have some articles to back my argument. Thanks, - Malhar -- modified at 17:23 Monday 10th September, 2007
Isn't it your job to specify the database? I'm an oracle guy, which has not boolean, but if you specify a boolean which SQL Server supports the DBA should use it. Go up the hierarchy until so that your boss can speak with the DBA boss and hash out the issue.
-
I need some strong supportive arguments to use "bit" field type in a SQL Server 2000/2005 table to store boolean values. Our application is in WPF with C# & XAML. Our DBA wants to use char(1) field with constraints of 'Y','y','N','n' in the database which uses Latin1_General_CS_AS (case sensitive/accent sensitive) collation. I am baffled by the suggestion of the DBA. In my past experience, I've never heard of such a wild idea. The DBA proposes the char(1) field type because of two reasons: 1) When an admin looks at the table, 'Y','N','y','n' are more obvious than 0 and 1. (But then again, if one does not know 0 & 1 (for bit field) then s/he shouldn't be admin in the first place)! 2) In HIS past projects, he used char(1) and no one complained. My problems are.. - This breaks all the queries/SQL that has been written to do true/false (0/1) comparison. - Unlike bit field (which only has two values), another isuse wih char is that unless someone explicitely looks at the constraint, there is no clea indication that the field only accepts Y & N. - Bit is CLR/SQL compliant. - Char is more expensive (size & equality comparison wise) than bit. - The mapping will have to be changed in my business objects from boolean to string or char. - UI will have to introduce some new logic for converting char/string to boolean for displaying radio/checkbox options .. :( So anyways ... any suggestions? or links to Microsoft/publication white papers? I'd really like have some articles to back my argument. Thanks, - Malhar -- modified at 17:23 Monday 10th September, 2007
Storing the data in bitfields simplifies the number of change requests that have to be made in a production environment. For instance, if the bitfield values are stored across char(1) fields, then when the application has to be updated to include the addition of say 10 new possible field values, 10 new char(1) fields will have to be created in the database also. When using just the bitfield (I prefer NUMBER or INTEGER in Oracle) all that is required to be changed is the code update to include the new fields, no database changes are required. 1. You should be able to retrieve and process data from the bitfields faster in your application than if they were spread out through multiple char(1) fields. 2. A single storage point for like or group data. 3. It can be read just as easily as char(1) fields by any user when directly querying the database using (what should be) built-in SQL Server bitwise functions. -- Chris
-
Harder why? Why not have views for the application that show bit types while the table has CHAR(1)? Why is the "database storage for the application" instead of the "application is a GUI for the database"?
Validation of the char(1) type will need to be performed (duplicated) in every tier of the application to make sure the app can make a boolean decision based on it. It needs to be Y, N and not null in every case. In my view, this is "harder" than using a boolean-compatible bit type as the database representation. Would you choose to put all your dates and numeric values in the database as char(n) fields? "The" application can only be a GUI for the database if two conditions exist. One is that all of the business logic exists only in the database. The other is that the application be the only app that uses the database. Business logic doesn't belong in your GUI. And T-SQL is not the most flexible tool around for handling business logic. But feel free to do whatever blows your skirt up. It's all ultimately bit values, no matter how you code it. Tim Bailey Energy Laboratories, Inc.
-
Validation of the char(1) type will need to be performed (duplicated) in every tier of the application to make sure the app can make a boolean decision based on it. It needs to be Y, N and not null in every case. In my view, this is "harder" than using a boolean-compatible bit type as the database representation. Would you choose to put all your dates and numeric values in the database as char(n) fields? "The" application can only be a GUI for the database if two conditions exist. One is that all of the business logic exists only in the database. The other is that the application be the only app that uses the database. Business logic doesn't belong in your GUI. And T-SQL is not the most flexible tool around for handling business logic. But feel free to do whatever blows your skirt up. It's all ultimately bit values, no matter how you code it. Tim Bailey Energy Laboratories, Inc.
-
I agree on all points although I'll add that SQL Server will use a whole byte for a bit field and so a dba might consider having only one bit field as wasteful or something. I think the original post stated char(1) so the end result would be comparable but using bit would allow for 7 more bit fields in the table without any row size increase but the same 7 extra char(1) would add 7 more bytes, no brainer for me. Personally I use bit for boolean every time.
Dave Thomson wrote:
I agree on all points although I'll add that SQL Server will use a whole byte for a bit field and so a dba might consider having only one bit field as wasteful or something. I think the original post stated char(1) so the end result would be comparable but using bit would allow for 7 more bit fields in the table without any row size increase but the same 7 extra char(1) would add 7 more bytes, no brainer for me. Personally I use bit for boolean every time.
You are exactly right but it depends on using SQL 2005 (from the Books Online (BOL)):
bit (Transact-SQL) "The Microsoft SQL Server 2005 Database Engine optimizes storage of bit columns. If there are 8 or less bit columns in a table, the columns are stored as 1 byte. If there are from 9 up to 16 bit columns, the columns are stored as 2 bytes, and so on. The string values TRUE and FALSE can be converted to bit values: TRUE is converted to 1 and FALSE is converted to 0."
MS-SQL does no such optimization on char columns. My other thoughts on this are as follows:* Y and y and N and n may make sense to your DBA, but if you were to hire / contract someone who is non-English speaking to audit the data, it may well be that they would expect something else. A bit-field has no such barrier, a 1 is a 1, and a 0 is a 0.
- If you want to search the table for all rows that match an intended value (true or false), it must do two comparisons (the Y and the y), forcing the SQL server to do more work.
- Y and N only have meaning if given context, as you said before, no context is offered by a constraint buried in the server. If a column is defined as DropShipYN, maybe a Y and N would make sense, but what if the column was defined as DropShip -- Does Y stand for Yonkers? Its a bit contrived but a bit column has no context issues, you automatically know its either 1, 0, or null.
- It may increase the size of your indexes. The reason for this is that you may be tempted to include the char field in a composite index, whereas a bit field cannot be included (BOL - CREATE INDEX).
- Char, nchar, varchar, and nvarchar are collation dependent, bit is not, unnecessarily complicating replication and third-party database integration issues (BOL).
- It hampers the use of OTS reporting packages, as you will likely need to do conversion from the char to a boolean for use with the tools built-in items (do the conv
-
:) .. it's not that bad! Our disagreement is on booleans. Though he wants me to textualize enums and store their text values as strings instead of storing the numeric representation. Currently, we don't use [flags] on enums and we are too scared of renaming the enum so it has been working out so far. Anyways, he's at a highler level than I am in the hierarchy of things so my voice has no opinion to the management. Furthermore, the "varchar" for boolean strangely made into the "SQL Standards" document in the company (before I joined) and even more strangely it got approved. So as of now, I look like a clueless idiot! This is so obvious, may be that's why I wasn't able to find any links. Any suggestion would be greatly helpful.
malharone wrote:
Anyways, he's at a highler level than I am in the hierarchy of things so my voice has no opinion to the management. Furthermore, the "varchar" for boolean strangely made into the "SQL Standards" document in the company (before I joined) and even more strangely it got approved. So as of now, I look like a clueless idiot!
Varchar for a boolean would always be wrong. The type should be char(1). So is it documented as char(1) or varchar(1). And if you have coding standards that specify a particular process and you have gotten to the point where you have obviously not followed the process then it demonstrates that the process is broken.