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
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.
-
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
Marco Turrini wrote:
- Using char(1) for a boolean is stupid enough,
Yet oddly enough many implementations use it quite successfully. Perhaps it is the users and not the idiom that is the problem.
Marco Turrini wrote:
- Using [var]char introduce wide possibilities for unnecessary errors and runtime exceptions.
You meant in terms of booleans and not in general correct? Given that the OP already mentioned that constraints are in use I can't see how there are going to be many problems introduced in the SQL code itself.
-
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
Jeremy Fowler wrote:
However, from the customer's point a view a 0 or 1 does not look nice in a report.
Except the OP suggested that enums are stored as numerics. So those don't show up in reports? And certainly reporting tools in general allow values to be mapped either in SQL itself or even in the the supported report language so that wouldn't seem to be a problem to me.
-
" 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. "
malharone wrote:
- In HIS past projects, he used char(1) and no one complained
Probably back before the advent of the bit type?
PIEBALDconsult wrote:
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.
Interesting. Which means in terms of performance that the bit must be extracted before doing any comparison. What happens if it is used in an index?
-
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.
-
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.
timbailey wrote:
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.
Huh? What do you mean by "application"? Certainly in the stored procedures that would be true. But it hardly the case in in say the C# or Java code that was accessing it. If every "tier" in there is managing booleans via Y/N because the database is then there is something seriously wrong with the design of the application. And that problem has nothing to do with how booleans are implemented in the database itself.
timbailey wrote:
Would you choose to put all your dates and numeric values in the database as char(n) fields?
Double huh? No language I know moves time values without conversion from the database to something else. In C# the precision of DateTime doesn't even match that of the equivalent SQL Server value. Using a string would allow one to preserve that. And what are you going to do if you want to preserve the time zone which SQL Server has absolutely zero support for? If you are unfortunate enough to be using C# it doesn't support timezones either so you would have no choice but to either use unmanaged code or to use a text field in some form.
-
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.
AKAJamie wrote:
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.
Did you see the post that indicated that the OP's organization has specifically documented the standards for databases and where it states what those specific standards are? And that obviously those standards have not been followed. To me that suggests that the process of the company is either flawed or that some group of cowboy programmer(s) have be avoiding the process. In either case neither case is it a matter of the DBA versus the programmer but rather one of what exactly the company is doing and is supposed to be doing in terms of process control.
-
Marco Turrini wrote:
- Using char(1) for a boolean is stupid enough,
Yet oddly enough many implementations use it quite successfully. Perhaps it is the users and not the idiom that is the problem.
Marco Turrini wrote:
- Using [var]char introduce wide possibilities for unnecessary errors and runtime exceptions.
You meant in terms of booleans and not in general correct? Given that the OP already mentioned that constraints are in use I can't see how there are going to be many problems introduced in the SQL code itself.
jschell wrote:
Yet oddly enough many implementations use it quite successfully.
They used to say the same for years expressed with two digits. Of course the problem is the user: the fact is you can't be assured your user (and I mean either the developer and the end user) are educated enough. Using a [var]char lets doors open for a end user or a developer to write "dirty" values: who said that the only two chars are to be 'Y'es and 'N'o; in Italy we say 'S'i and 'N'o, in France they say 'O'ui and 'N'on, in Germany 'J'a and 'N'ein, in Russia 'D'a and 'N'iet (I think) Why only 'Y' should be used for the True value? If you think this is irrilevant, I'm writing right now an application which is already shipped in Italian/Italy and will be in German/Italy and French/Algeria
jschell wrote:
Given that the OP already mentioned that constraints are in use I can't see how there are going to be many problems introduced in the SQL code itself.
Because you can't assume that nobody will ever access data with something else than your application (the Business Logic you implemented in your OP application). Besides Microsoft ones, at least two free tools come to my mind which let you access directly data. To avoid this, you have to implement several procedures; among these is the correct use of the proper data type. That said, of course there are lot of applications which use char to store boolean value. As far as I am concerned, they can store everything as [var]char: boolean, dates, enums, timestamps and currency values... it's someone else's database and application, and I will not have to mantain it, nor wish to.
Marco Turrini
-
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
Jeremy Fowler wrote:
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.
Please, don't misunderstand me but, just looking at the big picture, I can assure you that a char field is not necesseraly clearer to an end user than a bit one. I'm working with Italian, French, German and Arabic speaking end user and to very little of them a 'Y' would be a readable representation of a True value: you can't assume they studied English at school (e.g., in Italy a stident can study English or French) and anyway you can't bet they really know English (maybe it was centuries ago...); some of them would also be offended that my db doesn't allow them to store their national True value, but that's - partially - another story; a 0 or 1 field is much more ... neutral and doesn't leave room for misunderstanding or errors (once you have trained them, of course). As someone else stated, even enum values are stored as integer... or are you going to store their description? I don't think so...
Marco Turrini
-
Jeremy Fowler wrote:
However, from the customer's point a view a 0 or 1 does not look nice in a report.
Except the OP suggested that enums are stored as numerics. So those don't show up in reports? And certainly reporting tools in general allow values to be mapped either in SQL itself or even in the the supported report language so that wouldn't seem to be a problem to me.
jschell wrote:
Except the OP suggested that enums are stored as numerics. So those don't show up in reports?
Actually, the author said this in a latter post:
malharone wrote:
Though he wants me to textualize enums and store their text values as strings instead of storing the numeric representation.
Since he wants him to stored the text values as strings, this would fall inline with the requirements of easy to read reports.
jschell wrote:
And certainly reporting tools in general allow values to be mapped either in SQL itself or even in the the supported report language so that wouldn't seem to be a problem to me.
His company may require their customers to write their own reports. They may even provide some basic reporting utility to do this that might not have some of the advanced functions of Crystal or some of the other advanced reporting engines. Its a lot easier for a novice user to drag a field onto a report than have to resort to advanced features like functions to map a bit value to a readable format. The point I was trying to make is that we don't know the circumstances that already exist in this situation. There could be many reasons why someone would need to use a char datatype rather than a bit.
-
Jeremy Fowler wrote:
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.
Please, don't misunderstand me but, just looking at the big picture, I can assure you that a char field is not necesseraly clearer to an end user than a bit one. I'm working with Italian, French, German and Arabic speaking end user and to very little of them a 'Y' would be a readable representation of a True value: you can't assume they studied English at school (e.g., in Italy a stident can study English or French) and anyway you can't bet they really know English (maybe it was centuries ago...); some of them would also be offended that my db doesn't allow them to store their national True value, but that's - partially - another story; a 0 or 1 field is much more ... neutral and doesn't leave room for misunderstanding or errors (once you have trained them, of course). As someone else stated, even enum values are stored as integer... or are you going to store their description? I don't think so...
Marco Turrini
Well, we don't know if his company is an international company. They may only make an English version of the product and only sell to clients in the US. Also, the author did say in a later post that they store the text value of enums as strings.
malharone wrote:
Though he wants me to textualize enums and store their text values as strings instead of storing the numeric representation.
-
jschell wrote:
Yet oddly enough many implementations use it quite successfully.
They used to say the same for years expressed with two digits. Of course the problem is the user: the fact is you can't be assured your user (and I mean either the developer and the end user) are educated enough. Using a [var]char lets doors open for a end user or a developer to write "dirty" values: who said that the only two chars are to be 'Y'es and 'N'o; in Italy we say 'S'i and 'N'o, in France they say 'O'ui and 'N'on, in Germany 'J'a and 'N'ein, in Russia 'D'a and 'N'iet (I think) Why only 'Y' should be used for the True value? If you think this is irrilevant, I'm writing right now an application which is already shipped in Italian/Italy and will be in German/Italy and French/Algeria
jschell wrote:
Given that the OP already mentioned that constraints are in use I can't see how there are going to be many problems introduced in the SQL code itself.
Because you can't assume that nobody will ever access data with something else than your application (the Business Logic you implemented in your OP application). Besides Microsoft ones, at least two free tools come to my mind which let you access directly data. To avoid this, you have to implement several procedures; among these is the correct use of the proper data type. That said, of course there are lot of applications which use char to store boolean value. As far as I am concerned, they can store everything as [var]char: boolean, dates, enums, timestamps and currency values... it's someone else's database and application, and I will not have to mantain it, nor wish to.
Marco Turrini
Marco Turrini wrote:
They used to say the same for years expressed with two digits.
Interesting analogy which is really a bad one. Unless you are suggesting that true and false is going to "grow" in to a tri-state value. But of course a char field could handle that where a bit couldn't.
Marco Turrini wrote:
If you think this is irrilevant, I'm writing right now an application which is already shipped in Italian/Italy and will be in German/Italy and French/Algeria
First if you are talking about display values, such as in a GUI, then there is something wrong with your design. Secondly, are you claiming that every single value in your database is written in such a way that it is appropriate for other languages? How do you use enumerations? With a mapping table presumably. Which obviously would work with a char(1) as well and would be absolutely required for a bit field. And what happens in Italy when someone from France uses it in Italy? Is the Italian version of the MS SQL Server going to display the 'bit' in French? Perhaps by reading the mind of the user as they type at the keyboard? If you want to internationalize a product I suggest you start at the user interface and not attempt it at the instrinic data level.
Marco Turrini wrote:
Because you can't assume that nobody will ever access data with something else than your application (the Business Logic you implemented in your OP application). Besides Microsoft ones, at least two free tools come to my mind which let you access directly data. To avoid this, you have to implement several procedures; among these is the correct use of the proper data type.
What are you talking about? Unless you go spelunking through the binary files of the database your statement is pointless. I don't care how you access it the constraints will still be enforced. I can only guess that you are suggesting that the data type is propogated throughout an appplication - which is a poor practice that has nothing at all to do with bit/char.
-
Jeremy Fowler wrote:
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.
Please, don't misunderstand me but, just looking at the big picture, I can assure you that a char field is not necesseraly clearer to an end user than a bit one. I'm working with Italian, French, German and Arabic speaking end user and to very little of them a 'Y' would be a readable representation of a True value: you can't assume they studied English at school (e.g., in Italy a stident can study English or French) and anyway you can't bet they really know English (maybe it was centuries ago...); some of them would also be offended that my db doesn't allow them to store their national True value, but that's - partially - another story; a 0 or 1 field is much more ... neutral and doesn't leave room for misunderstanding or errors (once you have trained them, of course). As someone else stated, even enum values are stored as integer... or are you going to store their description? I don't think so...
Marco Turrini
Marco Turrini wrote:
Please, don't misunderstand me but, just looking at the big picture, I can assure you that a char field is not necesseraly clearer to an end user than a bit one. I'm working with Italian, French, German and Arabic speaking end user and to very little of them a 'Y' would be a readable representation of a True value: you can't assume they studied English at school (e.g., in Italy a stident can study English or French) and anyway you can't bet they really know English (maybe it was centuries ago...); some of them would also be offended that my db doesn't allow them to store their national True value, but that's - partially - another story; a 0 or 1 field is much more ... neutral and doesn't leave room for misunderstanding or errors (once you have trained them, of course). As someone else stated, even enum values are stored as integer... or are you going to store their description? I don't think so...
Interesting argument. However that entire argument is a marketing/business decision. NOT a technology one. So if you are concerned that customers might be offended by data in the database then marketing/business needs to drive that decision down to the technology level. And it is not and never will be a decision that a developer (in a company with marketing/sales) will or should make. Regardless of whether they are a DBA or not. Naturally one won't find a technological posting that supports that either.
-
Marco Turrini wrote:
They used to say the same for years expressed with two digits.
Interesting analogy which is really a bad one. Unless you are suggesting that true and false is going to "grow" in to a tri-state value. But of course a char field could handle that where a bit couldn't.
Marco Turrini wrote:
If you think this is irrilevant, I'm writing right now an application which is already shipped in Italian/Italy and will be in German/Italy and French/Algeria
First if you are talking about display values, such as in a GUI, then there is something wrong with your design. Secondly, are you claiming that every single value in your database is written in such a way that it is appropriate for other languages? How do you use enumerations? With a mapping table presumably. Which obviously would work with a char(1) as well and would be absolutely required for a bit field. And what happens in Italy when someone from France uses it in Italy? Is the Italian version of the MS SQL Server going to display the 'bit' in French? Perhaps by reading the mind of the user as they type at the keyboard? If you want to internationalize a product I suggest you start at the user interface and not attempt it at the instrinic data level.
Marco Turrini wrote:
Because you can't assume that nobody will ever access data with something else than your application (the Business Logic you implemented in your OP application). Besides Microsoft ones, at least two free tools come to my mind which let you access directly data. To avoid this, you have to implement several procedures; among these is the correct use of the proper data type.
What are you talking about? Unless you go spelunking through the binary files of the database your statement is pointless. I don't care how you access it the constraints will still be enforced. I can only guess that you are suggesting that the data type is propogated throughout an appplication - which is a poor practice that has nothing at all to do with bit/char.
jschell wrote:
Interesting analogy which is really a bad one. Unless you are suggesting that true and false is going to "grow" in to a tri-state value.
Stretching too much it's meaning, a tri-state could be handled by allowing null values; but I actually would never do that. The analogy referred to the fact that "it's always been like this" isn't a really string argument, IN MY PERSONAL AND HUMBLE OPINION, of course. But anyone is free to act as he likes, unless he hurts someone [else?]. There are lot of applications happily using char representation of data, some store directly the entire description of the enumeration or foreign keys (I've also seen date stored as char in the ISO format for sorting reason).
jschell wrote:
First if you are talking about display values, such as in a GUI, then there is something wrong with your design.
You can take for granted, but maybe for other reasons, since I think I didn't express properly: 1) If I need to display "custom" values, I use a foreign key on another table; what I call enumerations are values which are invariant, regardless of the culture: just as a very quick example I may have an enumeration which tells me if it's an invoice or a refund to guide the flow of the program; then I have a separate table to store descriptive values for documents: I may have several types of invoices with different description, all of type 0=Invoice, refunds of type 1, and so on (it's just a quick example, not a full analysis). In the terminology we use in our company enumerations are NOT stored anywhere - but we use this terminology just to clearly tell them from foreign table; they are usually shown - if they really are - just in applications accessed only to higly trained staff, usually ourselves (trained on our application, I mean, not generally, of course) . 2) I'm not claiming that every single value in my database is written in such a way that it is appropriate for other languages; my attempt is to avoid to be tied to a particular language/culture in the database
jschell wrote:
Is the Italian version of the MS SQL Server going to display the 'bit' in French?
Mais ouis, with a very naive accent:-D Of course, not, it shows just 0 and 1. But what happens in Italy when someone from France uses a char(1) in Italy, to see her O(uis) refused by the Italian constraint applied by the dba
-
jschell wrote:
Interesting analogy which is really a bad one. Unless you are suggesting that true and false is going to "grow" in to a tri-state value.
Stretching too much it's meaning, a tri-state could be handled by allowing null values; but I actually would never do that. The analogy referred to the fact that "it's always been like this" isn't a really string argument, IN MY PERSONAL AND HUMBLE OPINION, of course. But anyone is free to act as he likes, unless he hurts someone [else?]. There are lot of applications happily using char representation of data, some store directly the entire description of the enumeration or foreign keys (I've also seen date stored as char in the ISO format for sorting reason).
jschell wrote:
First if you are talking about display values, such as in a GUI, then there is something wrong with your design.
You can take for granted, but maybe for other reasons, since I think I didn't express properly: 1) If I need to display "custom" values, I use a foreign key on another table; what I call enumerations are values which are invariant, regardless of the culture: just as a very quick example I may have an enumeration which tells me if it's an invoice or a refund to guide the flow of the program; then I have a separate table to store descriptive values for documents: I may have several types of invoices with different description, all of type 0=Invoice, refunds of type 1, and so on (it's just a quick example, not a full analysis). In the terminology we use in our company enumerations are NOT stored anywhere - but we use this terminology just to clearly tell them from foreign table; they are usually shown - if they really are - just in applications accessed only to higly trained staff, usually ourselves (trained on our application, I mean, not generally, of course) . 2) I'm not claiming that every single value in my database is written in such a way that it is appropriate for other languages; my attempt is to avoid to be tied to a particular language/culture in the database
jschell wrote:
Is the Italian version of the MS SQL Server going to display the 'bit' in French?
Mais ouis, with a very naive accent:-D Of course, not, it shows just 0 and 1. But what happens in Italy when someone from France uses a char(1) in Italy, to see her O(uis) refused by the Italian constraint applied by the dba
Marco Turrini wrote:
Mais ouis, with a very naive accent Of course, not, it shows just 0 and 1. But what happens in Italy when someone from France uses a char(1) in Italy, to see her O(uis) refused by the Italian constraint applied by the dba
I believe you are missing the point. Data exists in three forms. 1. Application 2. User 3. Mixed. Application data is something that the user should never see. Does your delivery consist of nothing but SQL? Or is there C++/C#/Java/etc code? If something besides SQL then either it does not matter how you implement the boolean or there is seriously something wrong with your implentation. User data is that data that the user types in and which never impacts the functional flow (based on the data itself). When a user types in a customer name that is user data. You application doesn't make a functional flow decision on whether the customer's name is Fred or Francois. Mixed data is data that, due to BUSINESS NEEDS, is exposed to the customer yet impacts functional flow. If your application has a text box that allows the user to enter SQL then that could be consider a mixed view. And in that case would you expect "SELECT" to be required to be displayed in different languages? (I doubt ANSI-SQL does.) So far nothing you have said suggests that the booleans are anything but application data. Now if your deliverable is ONLY SQL and no other code then that might be of concern. Or if a marketing need drove this decision then that would be relevant as well. Other than that you are dealing with Application data. And you do NOT make application data language specific. It is vastly inefficient and is pointless. And arguing that somemone might see it is pointless - someone might decompile the code or might even purchase the source code but I doubt that you are putting comments your code in 50 different languages. And if you really are delivering just SQL and nothing else then an obviously solution would be to run the code base through an automatic processor to make it language specific. That would deal with all issues (which there would certainly be more than just boolean values.)
Marco Turrini wrote:
- I'd suggest you exactly the opposite, using n[var]char over [var]char and choosing carefully the collation, just to start off, or you could dive in some troubles.
Do you really think that no one has created language indepent applica
-
Marco Turrini wrote:
Mais ouis, with a very naive accent Of course, not, it shows just 0 and 1. But what happens in Italy when someone from France uses a char(1) in Italy, to see her O(uis) refused by the Italian constraint applied by the dba
I believe you are missing the point. Data exists in three forms. 1. Application 2. User 3. Mixed. Application data is something that the user should never see. Does your delivery consist of nothing but SQL? Or is there C++/C#/Java/etc code? If something besides SQL then either it does not matter how you implement the boolean or there is seriously something wrong with your implentation. User data is that data that the user types in and which never impacts the functional flow (based on the data itself). When a user types in a customer name that is user data. You application doesn't make a functional flow decision on whether the customer's name is Fred or Francois. Mixed data is data that, due to BUSINESS NEEDS, is exposed to the customer yet impacts functional flow. If your application has a text box that allows the user to enter SQL then that could be consider a mixed view. And in that case would you expect "SELECT" to be required to be displayed in different languages? (I doubt ANSI-SQL does.) So far nothing you have said suggests that the booleans are anything but application data. Now if your deliverable is ONLY SQL and no other code then that might be of concern. Or if a marketing need drove this decision then that would be relevant as well. Other than that you are dealing with Application data. And you do NOT make application data language specific. It is vastly inefficient and is pointless. And arguing that somemone might see it is pointless - someone might decompile the code or might even purchase the source code but I doubt that you are putting comments your code in 50 different languages. And if you really are delivering just SQL and nothing else then an obviously solution would be to run the code base through an automatic processor to make it language specific. That would deal with all issues (which there would certainly be more than just boolean values.)
Marco Turrini wrote:
- I'd suggest you exactly the opposite, using n[var]char over [var]char and choosing carefully the collation, just to start off, or you could dive in some troubles.
Do you really think that no one has created language indepent applica
jschell wrote:
I believe you are missing the point.
And I believe you are missing the point, the starting one, to be precise. I replied to Jeremy Fowler who stated that, looking at the big picture, a 'Y' or 'N' value is more understandable from the end-user point of view when printing reports. It wasn't me the one who suggested that boolean value where input and shown directly to the end-user. I just argued that one should not show directly boolean values (and someone else added enum values) to the end-user, for reasons I won't repeat. Although I appreciated your thouroghly lesson above, I can't understand what this has to do with defending the use of a char data type for boolean value: if you like it, you can use char data type in your database to express anything (date, currency, integer); then there's a layer which converts database data to the correct application data (and type). But if I have such a layer, why shouldn't I use a bit data type in the database to express boolean values? Much more than your lesson (with which - believe it or not - I agree much more than you think, but I suspect that we have seriuos communication problems), I find that using a char(1) could be preferable just in two circumstances: 1) you have to mantain the application in a database which doesn't support bit data type (someone else cited Oracle); but if you use a data access layer (call it ORM or whatever you like), this shouldn't be a real issue. 2) you want to build an index based on that value; but you may incur in a severe performance paylod.
Marco Turrini