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

,

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:

dbo.lookup
dbo.LOOKUP
dbo.LookUP
andy.lookup
andy.LOOKUP
andy.LookUP

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"

Else

    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!

Rate

4.5 (4)

Share

Share

Rate

4.5 (4)