The Wildest Database Setup I’ve Seen
I love my job, I really do. This place has the nicest people I've worked with in a long time. It's great. But it's not perfect. Case in point: I was working on trying to figure out a production problem yesterday where a few INSERT statements into a MS SQL Server database was throwing up exceptions about primary key violation and this, in turn, was causing SELECT statements to fail.
I can't blame this place for MS SQL Server's problem of locking out the SELECT statements when it's upset about primary key violations on INSERT statements. Face it - the database should just fail the transaction and then go on about it's business. There's no need to have locks that are going to gum up the works for SELECT statements. That's Microsoft's fault.
But the problem that really led to this was the fact that I had part of a primary key was 'PHL' and another row had 'phl'. Now, to every other database I've ever used these are distinct values. The datatype is a simple varchar(16), and it's always been unique to me. But not this database.
No, it was a design choice to make all keys (primary and foreign) as well as indexes to be case insensitive. Amazing. This would be OK if they forced all the varchar data to be stored upper-case, but they don't, and it isn't. Which means that every stored procedure or client code needs to deal with converting the data coming out to the proper case. Again, possible, but why?
The closest thing I got to an answer was that historically, this matches the way the data was handled in the flagship app from years gone by. OK, I can possibly see that, but not really. If you're going to invest in a serious relational database, then why on earth tie it's hands with something like case-insensative keys?
I've put code into the applications that deal with the database to make sure that things are all uppercased, but it's still a potential problem for me until I get this code through Q/A and out into production.