Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12345»»»

Worst Practices - Making Databases Case Sensitive (Or Anything Else) Expand / Collapse
Author
Message
Posted Saturday, October 20, 2001 12:00 AM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: Moderators
Last Login: Today @ 6:56 AM
Points: 6,804, Visits: 1,934
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
Post #1365
Posted Monday, October 29, 2001 2:34 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, May 10, 2002 12:00 AM
Points: 2, 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.




Post #22770
Posted Monday, October 29, 2001 5:45 AM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: Moderators
Last Login: Today @ 6:56 AM
Points: 6,804, Visits: 1,934
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
Post #22771
Posted Monday, October 29, 2001 6:26 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, December 15, 2010 1:20 PM
Points: 1,060, 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
Post #22772
Posted Monday, October 29, 2001 6:50 AM


Keeper of the Duck

Keeper of the Duck

Group: Moderators
Last Login: Friday, November 14, 2014 7:14 AM
Points: 6,625, Visits: 1,876
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, CISA, MCSE, Security+, MVP - SQL Server
Regular Columnist (Security), SQLServerCentral.com
Author of Introduction to SQL Server: Basic Skills for Any SQL Server User
| Professional Development blog | Technical Blog | LinkedIn | Twitter
Post #22773
Posted Monday, October 29, 2001 7:17 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, December 17, 2008 7:38 AM
Points: 29, 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.




Post #22774
Posted Monday, October 29, 2001 7:21 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 8:02 AM
Points: 2,049, Visits: 3,596
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
SQL Tentmaker
“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
Post #22775
Posted Monday, October 29, 2001 10:18 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Today @ 10:36 AM
Points: 31,362, Visits: 15,823
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
Post #22776
Posted Monday, October 29, 2001 11:32 AM


Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Friday, September 7, 2007 8:30 AM
Points: 74, 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.




Post #22777
Posted Monday, October 29, 2001 11:41 AM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: Moderators
Last Login: Today @ 6:56 AM
Points: 6,804, Visits: 1,934
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
Post #22778
« Prev Topic | Next Topic »

Add to briefcase 12345»»»

Permissions Expand / Collapse