We now have just 'migrated' an SQL Server 2005 database from DEVEL into TEST. In some way throughout the migration process the DB was transformed from situation insensitive to sensitive - so most SQL queries broke spectacularly.
What I must know, is - what are the obvious good things about getting a situation sensitive schema?
NOTE: This means table names, column names, saved proc names etc. I'm not mentioning towards the really data being saved within the tables.
In the beginning inspection, I am unable to look for a justification that provides benefits over situation insensitivity.
I simply discovered why WE allow it to be situation sensitive. It's to make sure that whenever we deploy it around the client site, our DB works regardless if the client's SQL Server is to establish situation sensitive or otherwise.
That's one answer I wasn't expecting.
I truly can't think about worthwhile reason SQL identifiers ought to be situation sensitive. I'm able to think about one bad one, it is the one MySQL gives why their table names are situation sensitive. Each table is really a file on disk, your filesystem is situation-sensitive and also the MySQL devs didn't remember to
table_file = lc(table_name). This really is loads of fun whenever you move a MySQL schema to some situation-insensitive filesystem.
I'm able to think about one large reason they should not be situation sensitive.
Some schema author will probably be clever and choose that
this_table clearly means different things from
This_Table making individuals two tables (or posts). You may as well write "place bugs here" at that time within the schema.
Also, situation-insensitivity allows you become more significant inside your SQL to stress tables and posts versus instructions without having to be held as to the the schema author made the decision to complete.
SELECT this, that FROM Table;
Most languages available are situation-sensitive, much like most comparison calculations, most file systems, etc. Situation insensitivity is perfect for lazy customers. Even though it does makes things simpler to type, and does result in many variants of the identical names varying only by situation.
Personally, between (MyTable, mytable, myTable, MYTABLE, MYTable, myTABLE, MyTaBlE), I'd please want to see one universal version.
Not every parts of Unicode possess a bijective mapping between lower and upper-situation figures — as well as two teams of cases.
In individuals regions, "situation-insensitivity" is a touch meaningless, and most likely misleading.
That's about all I'm able to think about for the time being within the ASCII set, unless of course you would like Foo and foo to become different, I do not begin to see the point.
I love situation-sensitivity, mostly because that is what I am accustomed to from programming in Perl (and many every other language too). I love using StudlyCaps for table names and all sorts of lower situation with underscores for posts.
Obviously, many databases permit you to quote names to enforce casing, like Postgres does. That appears just like a reasonable approach too.
Situation insensitivity is really a blessing if you have designers that neglect to follow any kind of conventions when writing SQL or originate from development languages where situation insensitivity may be the norm for example VB.
In most cases I've found it simpler to cope with databases where there's no possibility that ID, id, and Id are distinct fields.
Apart from an individual preference for torture, I'd highly recommend you remain with situation insensitivity.
The only real database I ever done which was setup for situation sensitivity was Great Flatlands. I discovered needing to remember each and every casing of the schema namings was painful. I haven't had the privilege of dealing with newer versions.
Unless of course it's transformed and when my memory serves, the character of situation sensitivity you're talking about is decided at installation some time and is used to any or all databases. It had been the situation using the SQL Server installion that went the truly amazing Flatlands database I pointed out that databases on that installation were situation sensitive.
I actually do support for Sybase Advantage Database Server also it utilizes a flat extendable permitting DBF's in addition to our very own proprietary ADT format. The situation where I see situation sensitivity becoming an problem happens when using our Linux version from the server. Linux is really a situation sensitive OS so there's an option within our db to lowercase each and every call. This involves the table files be lower situation.