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
-
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 feel for you. I'm having the exact same battle on my project. Sadly, it al comes back to "Oh, well, it's done now, let's move on." Good luck winning your battle, though if your DBA is looked upon as an expert, you may want to take the advice of others and save your energy for the bigger battles (such as making every column of the table but one part of the primary key....oh, the fun I'm having with that one).
-
malharone wrote:
I am baffled by the suggestion of the DBA. In my past experience, I've never heard of such a wild idea.
This is not the first time i've heard of this. I'd not jump to the conclusion that the DBA is dumb. Atleast 2 good reasons for this are 1) A single BIT column in a table would still take 1 byte of storage. It's only when there are multiple BIT columns does SQL Server put those into a single byte
malharone wrote:
When an admin looks at the table, 'Y','N','y','n' are more obvious than 0 and 1.
- This depends on who's ultimately going to access the DB and it's contents. I've seen applications that give end users a Query Analyzer type UI where they can construct SQL queries on their own to retrieve data. In such cases, a 'Y' or 'N' would be more meaningful to the operator, who might not be aware of the BIT column or it's intricacies. 3) This is a long shot, but still applicable. SQL Server 2005 implicitly converts True to 1 and False to 0, while all application data layers actually pass 0 or 1 when accessing a BIT column. This is ambiguous in itself. You could either send a True or 1, and if an application is DB incongnizant, this can lead to breakage. 4) Even BIT variables declared in T-SQL are implicitly converted to an INT with check clause. This is because the DB engine cannot store a domain value in a single BIT. The best arguments you can put forth are 1) BIT's are faster in seek and scans, because they fall under the "Precise" data types. 2) They are language, collation independent 3) They're implicitly constrained to accept only 2 values. If the check clause in the char column is dropped accidentally, the values get screwed up. 4) You can apply boolean operators on them without conversion. This is extremely useful if you're dealing with time series data and use a lot of "where" conditions in your SQL Good Luck.
SG Cause is effect concealed. Effect is cause revealed.
I can see the dba's argument. One client I worked with had an SQL database queried by many sales type staff with simple queries. The concept of boolean true false was waaaaayyy over their heads. Big bosses said "It has to come back with a Yes or a No or Active / InActive" (not just a Y or N) But space was not a problem - luckily. Lena
-
One consideration may be indexability of the bit vs. char field. I don't believe SQL Sever allows indexing of bit fields, but char fields ma be indexed. Howeever, I think that if the SQL Engine determines that there are there are very few distinct values in the database, it will end up doing a table scan anyways. This may be why bit fields are not indexable, since there are only two values. Spare me the flames if you are tempted, I know I just skimming some ideas. My question to you guys wouldbe, "How does one efficiently index a table on a bit field?" Perhaps the bit field is called "ActiveFlag" and we want to indetifiy the small percentage of active records. I expect someone would say to create another table, but there is a lot of admin and programming overhead to manage another identical table. I have conidered created another table just to store the PKs of the original table's records that are active and done a join to that small table to identify the active records. Any thoughts on all of this? I've also heard arguments to avoid using the bit data type because it is not std and therefore there wouldbe a higher conversion cost should the app be converted to Oracle, for example, which does not support bit fields. I think Oracle people tend to use the char 1 Y/N approach although our DBA doesn't even add a "Y or N only" constraint because it would "slow down the database". BY a micro hair, I bet, at the cost of increased integrity risk. Personally, my experience tells me that the highest cost occurs from the database getting invalid data put into it. As a result, I always select my data types as narrow as possible and use constraints to ensure integrity. The Constraint part is not favored by our DBAs. I also like to use FKs, but our DBAs reject them for performnce reasons, too. That, I can understand a little better but I still feel that FKs make up for it with increase data integrity, it helps to self document the database and it helps in graphical query construction by automatically adding joins. comments are encouraged.
dokmanov wrote:
One consideration may be indexability of the bit vs. char field
That's correct; Sql Server can't index bit fields. I don't know if this is a real issue: I guess most of the time it shouldn't be, but "most of the time" doesn't mean "always" (if my English is not getting worse than ever). In my experience, bit fields are more useful to "filter" data rather than "sort" it (I'm sure that, no more than one hour from now, my boss will ask me a "little" modification which will really need a sorting on a bit field).
dokmanov wrote:
we want to indetifiy the small percentage of active records
Well, a simple WHERE clause should be enough. Surely is better than having a referenced table, unless you want to use arbitrary values (and these values can be edited, added and removed - with all the risks related to this) By the way, the other side of the small percentage of active records is a huge percentage of inactive ones (i.e., with the same index value).
dokmanov wrote:
Personally, my experience tells me that the highest cost occurs from the database getting invalid data put into it.
I couldn't agree more with you!
dokmanov wrote:
I also like to use FKs, but our DBAs reject them for performnce reasons, too.
Yes, but they exists and if used properly can do a great work for you. Usually they are preferable when values are not known by the programmers, but by the user (I don't know how people will categorize books, for instance) rather than to narrow the possible values, because a table can be edited and therefore there's the possibility that someone adds a '@' or a '^' to your 'Y' and 'N' table.
Marco Turrini
-
One consideration may be indexability of the bit vs. char field. I don't believe SQL Sever allows indexing of bit fields, but char fields ma be indexed. Howeever, I think that if the SQL Engine determines that there are there are very few distinct values in the database, it will end up doing a table scan anyways. This may be why bit fields are not indexable, since there are only two values. Spare me the flames if you are tempted, I know I just skimming some ideas. My question to you guys wouldbe, "How does one efficiently index a table on a bit field?" Perhaps the bit field is called "ActiveFlag" and we want to indetifiy the small percentage of active records. I expect someone would say to create another table, but there is a lot of admin and programming overhead to manage another identical table. I have conidered created another table just to store the PKs of the original table's records that are active and done a join to that small table to identify the active records. Any thoughts on all of this? I've also heard arguments to avoid using the bit data type because it is not std and therefore there wouldbe a higher conversion cost should the app be converted to Oracle, for example, which does not support bit fields. I think Oracle people tend to use the char 1 Y/N approach although our DBA doesn't even add a "Y or N only" constraint because it would "slow down the database". BY a micro hair, I bet, at the cost of increased integrity risk. Personally, my experience tells me that the highest cost occurs from the database getting invalid data put into it. As a result, I always select my data types as narrow as possible and use constraints to ensure integrity. The Constraint part is not favored by our DBAs. I also like to use FKs, but our DBAs reject them for performnce reasons, too. That, I can understand a little better but I still feel that FKs make up for it with increase data integrity, it helps to self document the database and it helps in graphical query construction by automatically adding joins. comments are encouraged.
You are right in that SQL Server will do a table scan if there are too few distinct values. The performance you would get on indexing the column that has 10,000 rows with 7,000 as one value and the other 3,000 with the other value does not justify the performance lost during inserts and updates. As for Oracle dot supporting a bit or boolean field for databases, you can actually use number(1) with a constraint of a 1 or 0. Granted, the constraint will slow down the database, but then you are forced to add the overhead of constraint checking to the application. But that does not keep a DBA or a developer working on the table from corrupting the data. So I guess it would really depend on the application and can you afford to have the data corrupted by a single field? I know people that get audited by the FDA can not afford that, so they take the hit at the database level. If the data is skewed in anyway, all of the data is no good. To shoot off on the side note about foreign keys, I agree that they are absolutely needed. The idea of them is to keep data integrity. So if you are taking too much of a performance hit with foreign keys, why not consider denormalizing your tables? I myself have had several battles with co-workers that normalize their tables to fourth normal form. I have see performance hits because I have to join six tables just to get a user's address (yes, six tables, and don't ask). After talking with a former systems engineer of IBM for over thirty five years and he even said, people normalize to the point where performance is sacrificed. We are in the business to make software work in a timely manner for the user. Theory and practicality generally do not exist on the same level. Is it good design to take a table to fourth or fifth normal form? Yes, absolutely, but is it practical? Generally no, it creates too many tables that have to be inner joined. These are just my thoughts and if anyone has any comments, by all means, post them.
-
:) .. 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.
Do you have dbo rights on db. Or are you sharing db with others and have more restrictive rights then dbo?
MrPlankton
-
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
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
-
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.
-
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.