Help with SQL Server (NOT A programming question) [modified]
-
Use bit, it's the right thing to do. Come to think of it, when I see a bit value it says true or false, not 1 or 0.
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
-
malharone wrote:
Furthermore, the "varchar" for boolean strangely made into the "SQL Standards" document in the company
- Leave that company as soon as possible 2) Using char(1) for a boolean is stupid enough, but using varchar is even worse: varchars are introduced by two bytes used to specify the actual length of the data in the row, so a varchar(1) is actually consuming 3 bytes, not 1 as you would probably expect; on the other hand, bit fields use just 1 bit (and, if memory doesn't fail me, Sql Server can group them in bytes) 3) Using [var]char introduce wide possibilities for unnecessary errors and runtime exceptions.
Marco Turrini
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.
-
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 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 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 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.
Believe it or not, I do agree with you;) Just a little thing: in my extremely humble opinion (well, if I were actually so humble, I would not express my opinion) building an application requires both the skills of the programmer and of a dba (a better dba would be welcome) balancing each other: if an application were totally led by a dba, or a programmer, problems would be just around the corner. (By the way, I'm playing both roles in my company:cool: I always have another "side of me" to point the finger to when something goes wrong:))
Marco Turrini
-
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 )
Christian Graus wrote:
Let me guess, he stores dates as strings too, right ?
....with more than one microsoft certifications, right?
Developer Fantasies
-
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
For more ammo to fire at the DBA do the following: Register for free at http://www.sqlservercentral.com[^] Then search for "bit vs tinyint vs char(1)" It contains lots more views from other people that could help you.:-D Good Luck:laugh:
-
Believe it or not, I do agree with you;) Just a little thing: in my extremely humble opinion (well, if I were actually so humble, I would not express my opinion) building an application requires both the skills of the programmer and of a dba (a better dba would be welcome) balancing each other: if an application were totally led by a dba, or a programmer, problems would be just around the corner. (By the way, I'm playing both roles in my company:cool: I always have another "side of me" to point the finger to when something goes wrong:))
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.
-
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 have seen this is some of the older sql server databases I have worked with (going back to 6.5). It is possibly a hangover from SQL Server 7 where you could not create an index or use GROUP BY on a bit, which you can in 2000+. I assume this contributed to DBA 'best practice' of avoiding bit.
-
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
Your DBA is correct - using a CHAR(1) is better than using a Bit field (sorry)!!! Here's the rationale: 1) Bit fields are not transportable - so when you move to Oracle you'll be able to do so with much less work. 2) Your CHAR(1) field can be marked as NULL (if desired). 3) Much easier for anyone querying the database with a non SQL-Server tool to get the results (go ahead and see what a product like Business Objects (or whatever it's called today) does with a bit field. 4) Bit fields are not part of the ANSI standard SQL. 5) Many CASE tools and Database Design tools do not support bit fields I disagree with your DBA on one point though! Limit the character to only the uppercase version of 'Y' or 'N' - even though SQL-Server is normally case-insensitive (unless you explicitly tell it not to be) many other database systems are not.
-
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
If you want to "fight it out", go ahead. You're right, and you'll probably waste a lot of time and lose in the end. Would your DBA allow you to use a view? I believe your idea of using a bit is best, but it does violate your company's (dubious) standards. Why not create something like: CREATE MyView AS SELECT Field1, Field2, CAST(CASE WHEN (Flag = 'y' OR Flag = 'Y') THEN '1' ELSE '0' END AS Bit) [Flag] FROM dbo.MyTable Then your DBA can have it their way, and you can too.
-
Or, worse, integers. The third-party products I have to use have so many different ways of doing the same things.
HAH! I can beat that. Here, at my current job, we use LONG for T/F, ID fields, and properties that store a dropdown list's index! Good thing memory is cheap! And...before you ask, yes, the application runs like crap!
JamminJimE Microsoft Certified Application Developer
Why are we still calling it Common Sense when it's just not that common? -
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.