SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Worst Practices - Making Databases Case Sensitive (Or Anything Else)


Worst Practices - Making Databases Case Sensitive (Or Anything Else)

Author
Message
Andy Warren
Andy Warren
SSCoach
SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)

Group: Moderators
Points: 18992 Visits: 2737
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

Andy
SQLAndy - My Blog!
Connect with me on LinkedIn
Follow me on Twitter
surjitmattu
surjitmattu
Forum Newbie
Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)

Group: General Forum Members
Points: 8 Visits: 1
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.



Andy Warren
Andy Warren
SSCoach
SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)

Group: Moderators
Points: 18992 Visits: 2737
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

Andy
SQLAndy - My Blog!
Connect with me on LinkedIn
Follow me on Twitter
VegaMachine
VegaMachine
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1130 Visits: 4
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
K. Brian Kelley
K. Brian Kelley
Keeper of the Duck
Keeper of the Duck (17K reputation)

Group: Moderators
Points: 17494 Visits: 1917
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
bk@warpdrivedesign.org
http://www.sqlservercentral.com/columnists/bkelley/

K. Brian Kelley
@‌kbriankelley
lfmn
lfmn
SSC Journeyman
SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)

Group: General Forum Members
Points: 85 Visits: 62
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.



David Benoit
David Benoit
SSCertifiable
SSCertifiable (5.6K reputation)SSCertifiable (5.6K reputation)SSCertifiable (5.6K reputation)SSCertifiable (5.6K reputation)SSCertifiable (5.6K reputation)SSCertifiable (5.6K reputation)SSCertifiable (5.6K reputation)SSCertifiable (5.6K reputation)

Group: General Forum Members
Points: 5586 Visits: 3650
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
Steve Jones
Steve Jones
SSC Guru
SSC Guru (107K reputation)SSC Guru (107K reputation)SSC Guru (107K reputation)SSC Guru (107K reputation)SSC Guru (107K reputation)SSC Guru (107K reputation)SSC Guru (107K reputation)SSC Guru (107K reputation)

Group: Administrators
Points: 107263 Visits: 19336
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
steve@dkranch.net

Follow me on Twitter: @way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
mcurnutt
mcurnutt
SSC Veteran
SSC Veteran (250 reputation)SSC Veteran (250 reputation)SSC Veteran (250 reputation)SSC Veteran (250 reputation)SSC Veteran (250 reputation)SSC Veteran (250 reputation)SSC Veteran (250 reputation)SSC Veteran (250 reputation)

Group: General Forum Members
Points: 250 Visits: 1
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_1



This 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.



Andy Warren
Andy Warren
SSCoach
SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)

Group: Moderators
Points: 18992 Visits: 2737
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

Andy
SQLAndy - My Blog!
Connect with me on LinkedIn
Follow me on Twitter
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search