Worst Practices - Making Databases Case Sensitive (Or Anything Else)
-
I've just come to accept that SQL Server is insensitive
but still write my objects in a case sensitive manner. In the programming world, lower and upper cased names have different meanings which I understand and rely on. It keeps me from having to come up with another name for a local and module level object of the same type and use.SQL Servers insensitivity is used when I am just interacting with the db thru Query Analyzer. Then I don't pay attention to case, because I won't have to go back to that code later.
fInaLLY dON't you find it ironiC that every POSTer to thIs message BOard used proper cAsing when wrIting thEIr post, yet MOSt compLained abOUT case SENSItivity?
-
Case sensitivity is an enormous thorn in my side. I oversee a development team for an application that runs on either SQL 2000 or Oracle. Despite the fact that the Oracle specialists KNOW that Oracle is setup to be case sensitive by default, we have problems arise which are due to different programmers storing the "same" value in a column using a different case (i.e., "Student", "STUDENT", or "student"), but forget to convert to upper or lower case for comparisons. It's not as frequent as it once was, but it still happens occasionally, and I find myself imitating that Strother Martin line from "Butch Cassidy and the Sundance Kid" - "Morons... I've got morons on my team." The really funny thing is, while we have the SQL databases setup to be case insensitive (thank God), I don't see the same inconsistency when storing data into the SQL tables - the SQL team always seems to use consistent values (such as "Student").
Now if I can just get everyone to compare dates to dates instead of string representations of dates...
-
I don't have any stats on it either, but a data warehouse from a vendor I work with is case sensitive. They mentioned that speed was the deciding factor for the sort order choosen (binary), specially if you have 120+ GB in a single DB.
Maybe in the 6.5 or even 7.0 days it made a difference, but with the performance of 2K, and the current power of the CPUs and drives, don't think it makes a difference anymore.
Hope somebody has benchmarks or data about this. Will be nice to prove it.
-
Case is primarily a formatting function and seldom, if ever, affects meaning in real life, thus my database objects are almost always case insensitive. Formatting is the job of the application, meaning is the domain of the database, so unless the case determines meaning it is irrelevant to the database.
/*****************
If most people are not willing to see the difficulty, this is mainly because, consciously or unconsciously, they assume that it will be they who will settle these questions for the others, and because they are convinced of their own capacity to do this. -Friedrich August von Hayek
*****************/
-
my post got "swallowed up" twice...maybe it'll amble in later...
I wanted to say that though I belong to the same "detest, despise, loathe...." club as everyone else there was a time when I was working with a VERY SENIOR db developer who had a complete disregard for cases - "SeLEcT coL1, COl2, cOl3 FrOM TAbleNAmE" - used to drive me INSANE - I often (every working minute) wished then that our db was case sensitive - that'd have been such SWEET VENGEANCE!!!
Talk about "sloppy work habits"..
**ASCII stupid question, get a stupid ANSI !!!** -
> There's a certain HR package that I've grown to loathe that requires case sensitivity. Grrr!
I'm guessing that I could name that HR package. 🙂
Maybe I'm wrong about this, but it seems to me that from way back in the earliest Sybase versions, they used to tout case-sensitivity as the best performing collation. It didn't take long to figure out that such a performance gain was theoretical -- that is, what one gains in quicker lookups, one loses in having to wrap search arguments in UPPER or LOWER functions.
I got used to the way Oracle does it pretty quickly, way back when I was writing a lot of PL/SQL. The data are case-sensitive; the database object references are not. Maybe they've changed things, but at the time I don't think it was an option in Oracle -- that's the way it was done, period. If the situation can't be ideal, at least make it consistent. This might be one of those instances where SQL Server is just too flexible for its own good.
Personally, I don't think the situation was improved when SQL Server came out with dial-a-collation at the column level. I'm not sure there is anything in the world uglier, this side of a Goth beauty pageant, than having to qualify JOIN statements with COLLATE clauses when you're dealing with different collations.
-
I bet ANSI-C developers think were all crazy. But....
((a + A) - B++) *b.... now thats crazy! 🙂
It is nice to know if we have to for some reason store ANSI-C source in the a db we can just collate that table, not the whole object.
When every I have come across the collation demon, it has been in the case of an application port to SQL Server.
I believe there are some scientific purposes as well, but I agree with the Author, nice article.
-
most databases are moving steadily towards a more programmatic pardigm rather than just SQL querying. this is the principle behind the compiled .Net code in the upcoming versions of SQL Server.
case-sensitivity will enable the database to stay in step with the more so-called evolved languages such as C++ and Java.
the other option is to be case insensitive and suffer the slights of being called inferior AKA VBScript !!
-
Case-insensitivity is really just a case of taking the easy way out. A well designed database and app wouldn't let the user enter "Fl", they would pick the correct value from a picklist or UPPER() everything on comparison/insert for example. Does this seem like a intensive task to do for every comparison/insert? - What do you think sql does to acheive case insensitivity?
That said, I admin a slew of sql2k databases and none are case-sensitive. When modeling a new database for a new app I without a second thought create it as case-insensitive. I don't expect to win any argument as to case-sensitivty being better in any way since 100% of current and legacy vb/asp/.net apps I work with are case-insensitive. I just know it robs potential performance, and I feel like I'm being lazy or taking a shortcut when making things case insensitive.
Also, I don't doubt that certain ms components are completely case-insensitive and that using a case-sensitive model would involve extra operations at a cost to performance to compare the case of characters.
-Ken -
I am in agreement about Case Sensitivity!! Unfortunately I work with a group that needs to be able to handle Unicode data from around the world and we provide some data cleansing/ data matching tools. The programmers need to have data as Case Sensitive (something about issues with certain characters in binary format :w00t
. After banging my head against the wall for weeks if not months on end, I gave in and we now have Case Sensitive Databases for their use!!!! 
Fortunately - they are the only ones using this data!!!
And they will each be getting a copy of your article as soon as I can get to the printer room!!!!!! -
I think that things make more sense if we differentiate case sensitivity and programming is aided by using mixed case. But most humans don't work in mixed case well. They write it, but when they are searching or entering data and make a mistake, they'd prefer that "test" = "Test" = "TEST" = "TEst"
As far as programming, I tend to agree that hungarian notation is redundant, but it does serve to differentiate the code words from the variables and other constructs. It's nice to be able to easiliy pick out variables from other code, at least for me.
I prefer:
for iCounter = 1 to 10
next
as opposed to
for counter = 1 to 10
next
Course, I'd be happy if we just put the same lower case letter before every variable, regardless of type
-
Excellent article. Just one question - What if you're searching for Base64 encoded data in a column? By definition Base64 is case-sensitive. Of course you can turn off case insensitivity on a per-query basis as well by specifying the collation for just that query. This would keep you from having to make the whole database case sensitive.
Thanks
-
Regarding this statement: "cASE SEnSitiVITY is not good for the whole of the database. I do agree that it saves '40%' time... but should be used only for columns that actually require it."
Are you suggesting that we set a different collation for each column of each table in a single database? That seems a little much. But if Case Sensitivity for some data is what you want, why not just set the database collation to Case Insensitive, and then *you* can add the COLLATE clause to your Case Sensitive queries like so:
-- Returns all the Northwind Customers in Germany
SELECT * FROM Northwind..Customers WHERE Country = 'GermanY' COLLATE SQL_Latin1_General_CP1_CI_AS
-- Returns nothing since it's Case Sensitive collation and there's no country
-- called 'GermanY' in the Northwind DB, only 'Germany'
SELECT * FROM Northwind..Customers WHERE Country = 'GermanY' COLLATE SQL_Latin1_General_CP1_CS_AS
Just a thought.
-
I love reading the references to case-sensitivty being a problme due to dba's/dev's spelling columns as "CustomerID", "customerid", or different variants of it. In a case sensitive environment that's not any more or less a mistake than simply spelling the column name wrong. Case sensitivity doesn't make it the software's problem that the users are spelling field names wrong.
On another note, this is a perfect example of why having standards and best practices implemented in organization is a good thing. If your standards dictate that a column name will always be spelled in lower-case, then your developers/dba's should never create a mixed case column and should never mistakenly reference a column in mixed case.
-Ken -
I do so agree with the article, and wish that others in the community, namely those creating applications (that use databases) to sell. For instance, PeopleSoft. PeopleSoft told me that their mandatory case-sensitivity stems from their support of multiple platforms. I knid of think this is BS, since they MUST make some allowances for each platform they support, since each one has its own "features". I believe they should make the case-sensitive attribute only mandatory if that's all the platform supports.
I hate the case-senstive code and DB because I do use case, mainly TitleCase in my code and DB schemas for readability, and while I try to maintain that as I write code or and ad-hoc query, I know it doesn't matter to some, and would make their life much harder if they had to pay a lot of attention to case and aren't in the habit of doing so.
Another thing is the issue of all caps. When you have a case-sensitive system, unless lower-case is required, people tend to use all caps, which is annoying when you have learned that all caps is yelling, in geek terms.
Chris
Viewing 15 posts - 46 through 60 (of 85 total)
You must be logged in to reply to this topic. Login to reply