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

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

By Andy Warren, (first published: 2001/10/29)

This week marks the fourth installment in this series. If you haven't read the others, here they are:

Worst Practices - Part 1 of a Very Long Series!
Worst Practices - Objects Not Owned by DBO
Worst Practices - Not Using Primary Keys and Clustered Indexes

So far we've covered some interesting ideas and had some great reader feedback. This week I'd like to discuss a topic suggested by reader (and columnist!) Mindy Curnutt - making an entire database case sensitive. Can anyone argue that case sensitivity doesn't add a layer of complexity? Writing good software using whatever language you prefer is hard enough without adding this to the problem!

Before we can mark using case sensitivity as a totally bad practice, we have to look at why you would use it. For example, we have an application where I work that is case sensitive, it distinguishes between "FL" and "Fl" as codes for Florida in a lookup table. Since we only want one code for Florida, how would we enforce that? By setting a unique index! Now we can have FL or Fl, but not both. Even though the application is case sensitive, there is no need to make the column (or table, or database) case sensitive. Or it there?

What if....we already had values in the table that keep us from adding a unique index (Feeling my pain? This is a real example!)? Should we now throw in the towel and look at using case sensitive data? I really really don't like that option, so let's look at one other idea - we could code insert and update triggers that would do a case sensitive check of the table and roll back any change that would violate our "rule" of not adding any "duplicates". In other words, don't make the problem any worse than it is.

Another option would be to remove all the "duplicates" so that a unique index could be used. I consider this the right way to fix it, but whether you can depends on a lot of factors specific to your situation.

If you encountered this issue in SQL 7, your choices would be to use the trigger technique or make the entire database case sensitive. Which way you went would probably depend on the scope. If you had a hundred tables that would require these triggers, it might well be smarter, less work, more efficient to use case sensitivity. Now that SQL 2000 allows tables or even columns to be case sensitive, we can apply a lot more granular fix.

I know you're thinking - it solves the problem, why not use it?

At the column level, I can't argue with that. Well I could, but I won't. It's a nice clean solution. Maybe even at the table level if you had a situation that required it. But make a whole database case sensitive? No way! It's not just the data that is case sensitive then, it's everything - including objects. A couple articles back I discussed why not having all objects owned by dbo just made your life complicated (you could have dbo.lookup, andy.lookup, etc). Make your database case sensitive and you can have this:


And a few more besides. As I said in the beginning, isn't writing good code hard enough without adding that to the mix? One argument for using case sensitive environments is that it should be faster since the CPU doesn't have to do this behind the scenes:

Select * from table where state='fl'

Probably at a very low level gets executed like this:

select * from table where upper(state)=upper('fl')

I'm sure that takes a CPU cycle or two, but is it worth the savings for that small increase. I have to imagine that both the hardware and SQL is pretty well optimized for this operation. Even if you do manage some savings, is it worth the additional coding complexity? What complexity you ask? I'd be willing to be that  in a lot of case sensitive environments, you end up doing this:

Select * from table where upper(state)='FL'

Why? Because your lookup table correctly only have one code for Florida! Now think about this - suppose I give you the requirement to make the state lookup codes unique, regardless of case - how will you accomplish that? Now you have to override a case sensitive column to make it case INSENSITIVE! Can't use an index! We're back to the trigger!

Did you know that both Access and VB treat data as case sensitive? Try it for yourself and see by running this code:

Dim sItem1 As String
Dim sItem2 As String

sItem1 = "a"
sItem2 = "A"

If sItem1 = sItem2 Then
    MsgBox "Matched"
    MsgBox "Didnt match"
End If

If you change the comparison to 'ucase$(sItem1)=ucase$(sItem2)' it will work every time. Oh wait, you say it worked correctly in Access without doing the ucase$ conversion? Access 2000 (and I believe Access 97 as well) adds the statement "Option Compare Database" to every module. Comment that out and try it! VB also offers support for making comparisons case insensitive, take a look at this link about using Option Compare Text.

An interesting topic isn't it? Imagine using a language that is case sensitive - Java, Javascript, even C# I think. XML is case sensitive!

People aren't computers. We don't work well with case sensitive data or development environments. Let the computer do the heavy lifting and hide that complexity. Think long and hard about your alternatives before you make anything case sensitive.

In closing, I realize that not everyone will agree with me. Whether you do or not, I bet you've got an opinion on the subject. How about sharing that with our readers - let them see both sides of the discussion and you're the one that can make that happen! Your comments do get read - this article is a result of one of them!

Total article views: 28345 | Views in the last 30 days: 2
Related Articles

How to change the database server for case sensitive

How to change the database server for case sensitive


Case sensitive database instance

everything seems to be case sensitive, why is this ?


Space Sensitivity in SSIS Lookups

It's been well-documented through myriad blogs and forum posts about the case sensitivity of the com...


Exporting Access database

Exporting Access database


Last database access

Find last database access