Worst Practices - Making Databases Case Sensitive (Or Anything Else)
-
Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/awarren/worstpracticesmakingdatabasescasesensitiveoranythi.asp>http://www.sqlservercentral.com/columnists/awarren/worstpracticesmakingdatabasescasesensitiveoranythi.asp
-
At last! Someone who has the same manic hatred of case-sensitivity as I do! Not just in SQL Server, mind you, but all programming languages. No one but no one has given me a convincing argument for it so why are all 'modern' languages case sensitive?? It just seems a very geeky thing to do.
-
A geeky thing indeed! Now there are 2 of us, are there more who'd like to join this rebellion?
Thanks for the feedback!
Andy
-
Make that 3. Although I am curious as to why modern languages that claim advanced superiority over older languages are forced into using case sensitivity. Is it so they can double their language commands by having upper and lower case commands? Whatever the case, I want in your club!
- Vega -
How about apps that REQUIRE case-sensitive databases that are on the open market? There's a certain HR package that I've grown to loathe that requires case sensitivity. Grrr!
K. Brian Kelley
http://www.sqlservercentral.com/columnists/bkelley/
K. Brian Kelley
@kbriankelley -
I think that your opinion on this issue is related to the type of system you learned on. I was trained on a case-sensitive system which eventually grew to 12 mirrored databases with approx 400 tables and 3/4 TB of data. I am now working on a non-case sensitive system and frankly prefer case sensitive. Even though I agree that it is sometimes easier to work on non case sensitive, I think that it promotes sloppy work habits. In a case sensitive environment you code much more carefully. Also, although there is not a lot of data on the subject, from my research, I've read that a case sensitive database can be up to 40% faster than non case sensitive.
Although I prefer case sensitive databases (and all projects that I have control over use them), I don't think that I'll ever win converts from the "I hate case sensitive" camp.
-
I inherited this pain on two servers and I could never begin to understand why it would come into being in the first place. Andy - I appreciate your real world application for the "need" in some circumstances. However, it does not take away from the disgust that I have for case sensitivity! Makes my life extremely difficult!!!
David
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
-
I'm not sure this is a worst practice for a couple of reasons. I tend to agree that a complete DB is a bad idea in almost all cases, especially since SQL 2000 allows single tables or columns. However, if you code to a db that requires this, you may have no choice.
1. Searching is difficult, but we can handle this in code (search "SQL" & "sql" & "Sql" & ..), but it is complicated and will impact performance. Of course, we could always us the VB approach and UPPER() everything, however, I tend to agree it is unnecessary. Users (most users) who are not programmers or techical users, will enter data and assume that "Steve Jones" == "STEVE JONES", even in other languages. If not, they will become confused and have problems. However, it is OUR job to code to handle this.
2. Case Sensitivity is good for data quality. It ensures that names are entered correctly.
3. Performance. definitely lower, but probably not noticable in 99.9% of servers. If it is, you probably need a server upgrade.
As far as programming languages go, I don't think that it is a big deal. ALL languages should force you to declare variables. If you declare @Counter, @COUNTER, @counter and use them in the same module, that's your fault.
I agree it makes things more complicated, but disagree that it affects the quality of code. People write crappy code no matter what language. That's the programmers fault. Case sensitivity doesn't affect that.
Of course, time to completion is an important factor. I like the flexibility of having constants in UPPER CASE and variables in lower case. I'd rather have more tools in the development environment to catch errors and allow me to decide if it's an error rather than blanketly handling things in one way or another.
Steve Jones
-
I'd like to join the club!
As I mentioned to Andy, I have been working on a database that has all objects NOT owned by DBO and has case sensitivity turned on. I do have to admit that the ordeal has caused my coding to become more stringent -
for instance, now I am in the habit of doing the following:
- tables, stored procedures, column names, etc are CAPITAL letters only.
- all logins, (potential owners), whatever you want to call them, are lowercase.
- All calls to tables, stored procedures, etc. have to have the owner declared - as in
select * from owner.TABLE_NAME
- All variables and table aliases are lowercase, while field aliases are UPPERCASE - as in
create procedure owner.PROCEDURE_1
@variable_1 int
as
select count(t1.PK_FIELD) as T1_QTY
from owner.TABLE_1 t1 INNER JOIN
owner.TABLE_2 t2 on t1.PK_FIELD= t2.FK_FIELD
where t2.FILTER_FIELD = @variable_1This may all seem a little crazy, for instance, why not just have all the logins, variables, parameters, etc be UPPERCASE? I guess that would work even better! The problem is that the logins were all lowercase (and so the object owner was already lowercase), etc. My point is that I have been FORCED to develop coding standards simply so that I could code at all.
One thing that is really annoying about using case sensitivity is that whenever you 'borrow' code from someone - say from the scripts section of a website, IT NEVER WORKS! And you spend time debugging it, and working through the problems. Especially painful are canned sprocs like sp_MSforeachtable
where the sproc has mixed case in it!I've learned to use better coding practices from the experience, but that is the only good thing I can think of to say.
-
Thanks to all for their comments so far. I can see where "better" tools might help, but I'd still vote against. Mindy, your comment about not being able to "borrow" code is a great point - we'd have to have another tool just to fix the code we borrowed.
I think Steve will be hosting the worst practices articles next month - it'll be his turn in the barrel!
Andy
-
Well..well...So we have people hating case sensitivity...
I would like to add something here...
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. A closer look at the system - I mean in the design level - would probably tell you more. If the situation says 'CaseSensitive' then the particular coulmn should be and if it says 'iTsOk' then it's ok.
More... if your product is going to be developed across different languages...it's lot more than just advisable to have case insensitive data base. People who are working in case insensitive environment would naturally think "select * from contact" would eventually result in "SELECT * FROM CONTACT". Not always they sense that the statement inside the quotes go into a different environment where case matters a lot. While flowing through the code...It's not possible for them to think all these...they have enough things to worry about.
AS for them... they just poured the midnight's oil helping there code find the DB server at '192.168.0.21' and just today morning they found that they have mistakenly typed '192.168.0.2l'...( btw... why did you hard code the server's IP address on the first place!??!!)
The above just put one more thing in picture. Many of the IDEs use CourierNew where 'l' and '1' look all the same. If I have a column say 'Col1' (btw... don't tell me that u really named a column as 'Col1'!) one might think it as 'Co11' or 'Coll'...that gives enough confusion. So - for those from the case sensitive camp - can you agree 'COL1' would be better.
And finally...for those who "Just wonder why modern languages are case sensitive!"...I would like to tell my personal opinion...I understand the program much. More, it makes way for some meaningful variable names and things like that...though forcibly.
imagine this...
if( strString.isEmpty() )
//it is empty.
else
// it is not empty.
does it not look like a sentence rather than scaring people with all upper case instructions...people those have seen the good old days of BASIC might agree with me.
but still some of the new programmers think using a BP is quite labour intensive...as for them..they'll know the real picture in the near future.
I am wedded with case sensitive programing...I love that...No question of a divorce.
Different Programing languages are for different purposes...and based on that, they are case sensitive or otherwise. But databases are common to all. They should easily integrate into any front end code. Hence I strongly feel that Case Insensitive Database is much more accesible.
Edited by - rajbow on 10/31/2001 12:32:40 AM
Edited by - rajbow on 10/31/2001 12:33:41 AM
~*~~*~~*~~*~~*~~*~~*~~*~~*~~*~~*~~*~~*~~*~~*~~*~~*~~*~~*~~*~
It's the music that matters for a soulful experience... not the instrument.
~*~~*~~*~~*~~*~~*~~*~~*~~*~~*~~*~~*~~*~~*~~*~~*~~*~~*~~*~~*~ -
-
I agree! Case sensitivity is a huge pain. Maybe it is a conspiracy by all the UNIX geeks to force this on us 🙂
Converting between upper and lower case is so easy to do at the app level that I think it is foolish to have an entire database that is case sensitive. How hard is it to code your app to convert state abbreviations to uppercase? Not very!
-
Thanks for joining in Rick. We appreciate the comments!
Andy
-
Dear Andy/Steve,
I have been following the article right from the start and find it really excellent. I am new to this site and not aware much about it. I have a request...
I would love to look forward for the BP/WP suggestions to be extended out to general programing as well (obviously) in the general section...
It might include topics on indenting habits, assertion of pointers etc... as this would be useful for many of the new people...
Actually, this can also be database centric... I find people ignoring Exception Handling and such things which is very essential especially when using components like the OLEDB...
could you, Andy, or Steve, who's going to continue ahead with the article...throw some light in that side. I would love to hear that...
Regards,
Raj.
PS: Thanks for ur remark Steve.
~*~~*~~*~~*~~*~~*~~*~~*~~*~~*~~*~~*~~*~~*~~*~~*~~*~~*~~*~~*~
It's the music that matters for a soulful experience... not the instrument.
~*~~*~~*~~*~~*~~*~~*~~*~~*~~*~~*~~*~~*~~*~~*~~*~~*~~*~~*~~*~
where the sproc has mixed case in it!
Viewing 15 posts - 1 through 15 (of 85 total)
You must be logged in to reply to this topic. Login to reply