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

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss Content Posted by Andy Warren » Worst Practices - Making Databases Case...

86 posts, Page 1 of 9

1 2 3 4 5 »»»

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

Rate Topic

Display Mode

Topic Options

Author

Message

Andy Warren

Posted Saturday, October 20, 2001 12:00 AM

SSCertifiable

Group: Moderators
Last Login: Thursday, May 09, 2013 12:38 PM
Points: 6,462, Visits: 1,384

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
SQLShare - Learn One New Thing Each Day
SQLAndy - My Professional Blog
Connect with me on LinkedIn
Follow me on Twitter

Post #1365

surjitmattu

Posted Monday, October 29, 2001 2:34 AM

Forum 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

Andy Warren

Posted Monday, October 29, 2001 5:45 AM

SSCertifiable

Group: Moderators
Last Login: Thursday, May 09, 2013 12:38 PM
Points: 6,462, Visits: 1,384

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
SQLShare - Learn One New Thing Each Day
SQLAndy - My Professional Blog
Connect with me on LinkedIn
Follow me on Twitter

Post #22771

VegaMachine

Posted Monday, October 29, 2001 6:26 AM

Ten 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

K. Brian Kelley

Posted Monday, October 29, 2001 6:50 AM

Keeper of the Duck

Group: Moderators
Last Login: Yesterday @ 4:47 PM
Points: 6,584, Visits: 1,788

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

lfmn

Posted Monday, October 29, 2001 7:17 AM

SSC 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

David Benoit

Posted Monday, October 29, 2001 7:21 AM

SSCrazy

Group: General Forum Members
Last Login: Yesterday @ 7:56 AM
Points: 2,063, Visits: 3,441

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

Steve Jones - SSC Editor

Posted Monday, October 29, 2001 10:18 AM

SSC-Dedicated

Group: Administrators
Last Login: Yesterday @ 6:14 PM
Points: 31,421, Visits: 13,734

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

mcurnutt

Posted Monday, October 29, 2001 11:32 AM

Valued Member

Group: General Forum Members
Last Login: Friday, September 07, 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

Andy Warren

Posted Monday, October 29, 2001 11:41 AM

SSCertifiable

Group: Moderators
Last Login: Thursday, May 09, 2013 12:38 PM
Points: 6,462, Visits: 1,384

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
SQLShare - Learn One New Thing Each Day
SQLAndy - My Professional Blog
Connect with me on LinkedIn
Follow me on Twitter

Post #22778

« Prev Topic | Next Topic »

86 posts, Page 1 of 9

1 2 3 4 5 »»»

Permissions