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!