Worst Practices - Making Databases Case Sensitive (Or Anything Else)
-
That was all that was in the book. I've never read anything else on the issue (although I've looked). Most of what I've found has been opinion rather than fact oriented. I've also never done any comparison tests. The shops I've worked in were either case-sensative or not and they had no intentions of switching, so I didn't see the need to do any performance testing on my own....since it wasn't billable 🙂
quote:
Do they give any details? It makes sense that it would be faster, but how it affects overall query time/performance?Andy
http://qa.sqlservercentral.com/columnists/awarren/
-
Not sure if anyone monitors this feedback discussion any longer, but still. I am not sure as to whether I prefer case-sensitive or case-insensitive behavior on database level. I understand and agree on most of the problems you describe, but 'case-sensitive evangelist' could probably persuade me just as well with their advantages and disadvantages.
I'd like to add one reason why using a case-sensitive database (and downwards) is not a WP to me (but maybe not a BP as well). It is SQL-92 standard. I like to stick to the standards as much as possible, so therefore I like having case-sensitive turned on. Also, some other RDBMS (following the standard) has case-sensitive turned on by default or even as the only 'option', so moving your database (and application) to these RDBMS would be much easier if you've used case-sensitive all along.
With SQL Server 2000's functionality to set collation for each individual column I think I prefer having the default setting to case-sensitive, but change it for those columns where I want to have case-insensitive data. Also, just as with [ NULL | NOT NULL ], I try to always specify the collation to use in the table/column specification.
Chris Hedgate @ Apptus Technologies (http://www.apptus.se)
-
My $0.02 worth...
Kalen Delaney, in _Inside SQL Server 2000_, recommends that all development environments be case-sensitive, and all end-user environments be case-insensitive. Her view is that anything that will run in a case-sensitive environment will also run in a case-insensitive environment, but not vice versa.
As a rule of thumb, I agree. However, if someone purposefully sets out to write code that will run in a case-sensitive environment but breaks if run case-insensitively, it can be done. What if the developers code a temp table with two different column names, 'abc' and 'ABC'? Or if a query joining two tables, which SELECTs column 'abc' from one table and 'ABC' on the other table, fails to qualify the column name with the table name or alias? My guess is that the code will break.
One of the previous remarks in this thread (justinj's) complains about vendors who require case sensitivity. I concur with his complaint, but would like to point out that this hasn't stopped our shop from running two such applications as case-insensitive. The vendors whine, but when pressed cannot come up with a compelling reason why we should adopt case-sensivity in our environment. So far, we haven't had any problems, and this has been going on for two years.
I have a great deal of respect for Oracle's DBMS, and on this issue (as many others) I think they probably have the best compromise: they have taken the options away. In Oracle, all SQL, PL/SQL, object names, etc. are case-insensitive, and all actual data are case-sensitive. Applications may break if coded and tested inadequately, but nothing is going to break because of a hard-to-change database option.
In regard to the remarks that case-sensitivity makes one a better coder, this begs the question: what does "better" mean? If I dance the jitterbug around the edge of a cliff, one can argue it makes me a more careful dancer. On the other hand, what efforts am I squandering in my attempts to avoid a spill, that could have been applied instead to making me a better dancer in other respects?
Someone who is better at coding in a case-sensitive environment is not necessarily a better coder in general, they just know from experience how to avoid the pain caused by [opinion mode ON] an ill-advised choice. "Doctor, it hurts when I bang my head against this stone wall." "Well, then, don't bang your head against that stone wall." Even if it does make your head tougher.
Edited by - Lee Dise on 08/07/2002 08:00:15 AM
-
Sticking to the SQL92 standard is interesting. Not compelling to me, but interesting! We're SQL Server only with no plans of changing, so I don't feel bad at all about leveraging any functionality they give me. If the day comes when we have to move for some reason, it would almost have to be to a platform that supports both options or we'd be years re-writing apps and reports and everything.
Out of everything we run we have two tables that use case sensitive values, in both cases because we are using tables built for third party apps.
Andy
-
Regarding the comment that db objects are case-insensitive in Oracle: they are not. In fact, Oracle by *default* puts objects into upper-case, both at creation and reference time. This gives the illusion that the object names are case-insensitive. But remember: those object names are *data* in the system catalog, and data in Oracle is *always* case-sensitive.
For example, you can do this in Oracle:
create table "abc" (x number);
create table "ABC" (x number);
Of course, to reference a table that you create using what Oracle calls non-standard identifiers, such as "abc", you will need to use those nifty double-quotes.
This really came to the forefront for a client of mine who had used DTS to transfer lots of stuff from SQL Server to Oracle. DTS did its job to perfection, maintaining the mixed-case object names from the SQL Server. But this poor guy was nearly suicidal, because he could see object names like "Orders" in his Oracle database, but he couldn't access them! Of course he had tried every imaginable capitalization (and failed) and had tried to rename the tables (and failed, because he couldn't reference the table!). When I showed him that he could just double-quote the object names, you could almost see the tears of joy.
Re-cloaking,
Chris
-
My only food for thought is if you control the sql server environment.
My old company started out doing non case sensitivity. Fine for about 1 year. We had a product installed at the client site.
Our next sale.. gave us a client with an existing SQL Server install. I think it was 7.0 at the time. Another product/application had the customer install sql server as case sensitive.
None of our scripts worked on this case sensitive db().
It took about 2 weeks to go back and make our scripts case sensitive.
Case sensitive scripts will work on non-case sensitive sql servers. But not the other way around.
I now develop on a case sensitive sql server. If I need non case sensitive needs, I use explict calls.
Also, on the subject of not being able to control the installation method, you may want to specify your own collate option, to override the default install.
Check this example (that works on the sql server , no matter what the case sensitivity), that has the user.uniqueid as non senstive, but the password IS sensitive.
SELECT
userid
from
users WITH (NOLOCK)
where
LTRIM(RTRIM(UPPER(uniqueid))) = LTRIM(RTRIM(UPPER(@strUniqueID)))
and LTRIM(RTRIM(loginpassword)) = LTRIM(RTRIM(@strPassword))
COLLATE Latin1_General_CS_AI
/*
THIS COLLATE OPTION ENSURES CASE SENSITIVITY on the password column.
The UPPER takes care of getting
rid of case sensitivity on the Unique ID
SEE THESE functions for additional help
SELECT *
FROM ::fn_helpcollations()
fn_helpcollations ()
sp_helpsort
*/
In my example, I use UPPER to make the uniqueid non case sensitive, but here, the password IS SENSITIVE.
This code works correctly no matter how the client installed sql server. Because I explicitly call the COLLATE method.
..
-
I like Kalen Delaney's idea to use case sensitivity in a development environment.
I inherrited a database that isn't particularly well normalised and is case sensitive.
The problem comes at the user entry stage where things that should be enforced as drop down boxes are in fact free text boxes.
This means that we have a huge WHERE clause where every field is wrapped in a LOWER statement.
If we remove the LOWER statement and force all the entries to be a consistent case then the query run-time drops from approximately 6 seconds down to 1 second.
From what I can tell removing the LOWER statement allows the index to do its job rather than scanning the table.
-
One reason databases SHOULD be case sensitive: Development! Several years ago (back in the days of SQL 6.5), I worked for an HR systems vendor in Denver. The first time I tried to install some of my updates to the company's software on a salesperson's laptop, I discovered the reason for case sensitivity, IN SPADES. This salesperson had a case-sensitive installation of SQL Server on her machine, so of course virtually NONE of my code, which I'd developed on a case-INsensitive database, would work.
When it comes to developing database software that will eventually be migrated to an unknown customer database environment, do yourself a favor, and develop it in a case sensitive environment. The rigor and discipline forced on the developer by the database itself will save countless hours (and headaches!) in the long run.
- Matt
Kindest Regards,
Matt Gauch
-
SQL must take the strain to compare strings in upper and lower case if the database is not case sensitive. This make it easy for us to handle but difficult for the server.
The only reason I can think of for going case sensitive. Can anyone give us a benchmark on a large database with and without case sensitivity?
-
I can certainly understand and agree with the point of view that case sensitivity, certainly on a whole database, is generally a bad idea. I think however, that the issue is closely related to another which is that as long as you choose an option (i.e. case sensitivity, collation, naming convention etc) it is an agreed standard which is aggressively enforced across servers/databases/organisation.
-
Well it's an interesting argument and I'll admit that we've switched it off where I currently work... however, I don't agree with you! It's very easy to do a non-case sensitive comparison and as for the "problem" with object naming that can surely be avoided using a good naming convention?
Isn't the question which is easier/more readable - a case-sensitive comparison on a non-case sensitive database or a non-case sensitive comparison on a case-sensitive database?
My own feeling is that a programming language should model (i'm not sure that's the right word?!) the full complexity and provide programming options (ucase etc) to simplify the reading of the data if required.
Added to that the fact that every modern language is case-sensitive and you have to ask why? When I logged on this morning my password was case-sensitive.. etc
Thanks for the article any way - it's way more interesting than what I'm up to at the moment!
-
Sometimes there isn't a choice. With the Oracle/PeopleSoft products case sensitivity is mandatory.
-
The pain really kicks in when you set up the sqlserver to take a binary sort, but set up a database to be CI_AI ... fun with variables and temp tables abounds.
Other than that, I spent the first year of sql server on a CI_AS and learned the way of slop. Then I moved to a financial db binary sorted and moaned and moaned for a good couple of months. Then I just got used to it. Now I usually go for the binary sort.
Sloppy cased code now irritates me, I feel the case sensitive/binary approach gives much cleaner, disciplined code. There is no real drawback to it. You just need to remember when selecting your data types to select the collation as part of that.
Also beware of applying functions to the wrong side of a where clause - eg upper() as you may suddenly find your indexes useless
-
Last company I worked at ENFORCED CASE-SENSITIVITY to INCREASE PROFIT!
They reasoned that the SAME userid coming from the SAME phone number for the SAME client even though spelled AJ v. Aj v. aJ was 3 DIFFERENT ACCOUNTS and charged the CLIENT thusly.
I was NOT a fan of this NOR did I condone or approve it. Happily, I am no longer there and can now develop MORAL applications....
Good Hunting!AJ Ahrens
webmaster@kritter.net -
When I first started at one job, the "dba" role had been given to the chief software architect. He only had one test-duction area. If an object was in ALL CAPS - it was production. Otherwise, it was test.
How's that for efficient use of case sensitivity?!
Viewing 15 posts - 31 through 45 (of 85 total)
You must be logged in to reply to this topic. Login to reply