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
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.
-
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
The DBA appears to be either inexperienced or nuts. Perhaps heavily intoxicated. Or a combination of two of the three. But your way makes perfect sense.
malharone wrote:
- When an admin looks at the table, 'Y','N','y','n' are more obvious than 0 and 1.
That makes no sense what so ever...how is 4 characters (paying attention to capitalization) is easier to tell apart than two (1 and 0)? Having a capital and a lowercase next to each other can easily throw me off. And i'm wondering if a poorly written component added at a later time that doesn't check the value of the char entered at one point or another, could technically introduce a "typo" in the form of a different character and cause a bug breaking everything, since the older components may check the value going into the DB, but not comming from it, naively assuming the DB can be trusted. If a bit were to be used, that doesn't give such a choice. It's either a 1 or it's a 0. Roswell
"Angelinos -- excuse me. There will be civility today."
Antonio VillaRaigosa
City Mayor, Los Angeles, CA -
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 )
:) .. 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.
-
The DBA appears to be either inexperienced or nuts. Perhaps heavily intoxicated. Or a combination of two of the three. But your way makes perfect sense.
malharone wrote:
- When an admin looks at the table, 'Y','N','y','n' are more obvious than 0 and 1.
That makes no sense what so ever...how is 4 characters (paying attention to capitalization) is easier to tell apart than two (1 and 0)? Having a capital and a lowercase next to each other can easily throw me off. And i'm wondering if a poorly written component added at a later time that doesn't check the value of the char entered at one point or another, could technically introduce a "typo" in the form of a different character and cause a bug breaking everything, since the older components may check the value going into the DB, but not comming from it, naively assuming the DB can be trusted. If a bit were to be used, that doesn't give such a choice. It's either a 1 or it's a 0. Roswell
"Angelinos -- excuse me. There will be civility today."
Antonio VillaRaigosa
City Mayor, Los Angeles, CAHe'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/articles. Any suggestion would be greatly helpful.
-
The DBA appears to be either inexperienced or nuts. Perhaps heavily intoxicated. Or a combination of two of the three. But your way makes perfect sense.
malharone wrote:
- When an admin looks at the table, 'Y','N','y','n' are more obvious than 0 and 1.
That makes no sense what so ever...how is 4 characters (paying attention to capitalization) is easier to tell apart than two (1 and 0)? Having a capital and a lowercase next to each other can easily throw me off. And i'm wondering if a poorly written component added at a later time that doesn't check the value of the char entered at one point or another, could technically introduce a "typo" in the form of a different character and cause a bug breaking everything, since the older components may check the value going into the DB, but not comming from it, naively assuming the DB can be trusted. If a bit were to be used, that doesn't give such a choice. It's either a 1 or it's a 0. Roswell
"Angelinos -- excuse me. There will be civility today."
Antonio VillaRaigosa
City Mayor, Los Angeles, CARoswellNX wrote:
could technically introduce a "typo" in the form of a different character
Yes, one major reason to use bit is that any code that compiles, is going to be comparing 'valid' values.
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 )
-
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
" 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?
-
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 )
Or, worse, integers. The third-party products I have to use have so many different ways of doing the same things.
-
:) .. 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:
So as of now, I look like a clueless idiot!
Then show management this thread with lots of highly respected professionals disagreeing with your DBA.
Upcoming FREE developer events: * Glasgow: Agile in the Enterprise Vs. ISVs, db4o: An Embeddable Database Engine for Object-Oriented Environments, Mock Objects, SQL Server CLR Integration, Reporting Services ... My website
-
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
From SQL Server Books Online (SQL Server 2000 documentation): bit Consists of either a 1 or a 0. Use the bit data type when representing TRUE or FALSE, or YES or NO. For example, a client questionnaire that asks if this is the client's first visit can be stored in a bit column From SQL Server 2005 Documentation: 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. I don't think you're going to find much in the way of documentation to support your argument, since it's so obvious - kind of like asking why we don't store numbers in varchar fields... :doh: Good Luck :)
Sunrise Wallpaper Project | The StartPage Randomizer | The Windows Cheerleader
-
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:
So anyways ... any suggestions?
Yeah. Tell him that dba is only one letter away from daa - dumb-assed administrator. Marc
-
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 go with true/false. The other way has the potential to cause trouble down the road.
"Real programmers just throw a bunch of 1s and 0s at the computer to see what sticks" - Pete O'Hanlon
-
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
It looks like that DBA learned by using MS Access X| Access uses the Y/N values for its boolean fields.
Kelly Herald Software Developer
-
My limitied experiance has tought me that somethings are just not worth the effort of arguing about. A bit field would probably be better but his solution will work as well. Save your energy for bigger issues
I agree... just make sure you have sent an email etc to the appropriate people letting them know that you disagree with the design decision, but have been overruled by the senior person. Make sure you keep a copy yourself. That way, when it all turns to crap, you have *some* protection for your backside...
------------------------------------------- Don't walk in front of me, I may not follow; Don't walk behind me, I may not lead; Just bugger off and leave me alone!!
-
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
-
Or, worse, integers. The third-party products I have to use have so many different ways of doing the same things.
Or worse, store as integer where feb will forever have 28 days (calculating days in month on the spot using cases not a common function, and the code is spread out in ALL the modules, from A01.exe to A99.exe, plus SQL statement). Yeah, we have one of those. :)
-
malharone wrote:
So as of now, I look like a clueless idiot!
Then show management this thread with lots of highly respected professionals disagreeing with your DBA.
Upcoming FREE developer events: * Glasgow: Agile in the Enterprise Vs. ISVs, db4o: An Embeddable Database Engine for Object-Oriented Environments, Mock Objects, SQL Server CLR Integration, Reporting Services ... My website
Your right sir! Even a programmer like me also insist boolean simply because of its uses on the front end and ease of managiing inserts, updates and delete and most importantly selects. I have an impression that where expression on bit is faster then chars.
Jwalant Soneji (BE IT) (India) Mobile: +91 9969059127 At Winodows Live Spaces At Yahoo! 360 At BlogSpot
-
The DBA appears to be either inexperienced or nuts. Perhaps heavily intoxicated. Or a combination of two of the three. But your way makes perfect sense.
malharone wrote:
- When an admin looks at the table, 'Y','N','y','n' are more obvious than 0 and 1.
That makes no sense what so ever...how is 4 characters (paying attention to capitalization) is easier to tell apart than two (1 and 0)? Having a capital and a lowercase next to each other can easily throw me off. And i'm wondering if a poorly written component added at a later time that doesn't check the value of the char entered at one point or another, could technically introduce a "typo" in the form of a different character and cause a bug breaking everything, since the older components may check the value going into the DB, but not comming from it, naively assuming the DB can be trusted. If a bit were to be used, that doesn't give such a choice. It's either a 1 or it's a 0. Roswell
"Angelinos -- excuse me. There will be civility today."
Antonio VillaRaigosa
City Mayor, Los Angeles, CARoswellNX wrote:
naively assuming the DB can be trusted
:) We generally do that, and when found but, try to make the database value insertion more accurate and finding bug for the second time, checking values coming from db.
Jwalant Soneji (BE IT) (India) Mobile: +91 9969059127 At Winodows Live Spaces At Yahoo! 360 At BlogSpot