Is SQL syntax case sensitive?

Question

Is SQL case sensitive. I've used MySQL and SQL Server which both seem to be case in-sensitive. Is this always the case? Does the standard define case-sensitivity?

Answer 1

The SQL92 specification states that identifiers might be quoted, or unquoted. If both sides are unquoted then they are always case-insensitive, e.g. table_name == TAble_nAmE.

However quoted identifiers are case-sensitive, e.g. "table_name" != "TAble_naME". Also based on the spec if you wish to compare unqouted identifiers with quoted ones, then unquoted and quoted identifiers can be considered the same, if the unquoted characters are uppercased, e.g. TABLE_NAME == "TABLE_NAME", but TABLE_NAME != "table_name" or TABLE_NAME != "TAble_NaMe".

Here is the relevant part of the spec (section 5.2.13):

     13)A <regular identifier> and a <delimited identifier> are equiva-
        lent if the <identifier body> of the <regular identifier> (with
        every letter that is a lower-case letter replaced by the equiva-
        lent upper-case letter or letters) and the <delimited identifier
        body> of the <delimited identifier> (with all occurrences of
        <quote> replaced by <quote symbol> and all occurrences of <dou-
        blequote symbol> replaced by <double quote>), considered as
        the repetition of a <character string literal> that specifies a
        <character set specification> of SQL_TEXT and an implementation-
        defined collation that is sensitive to case, compare equally
        according to the comparison rules in Subclause 8.2, "<comparison
        predicate>".

Note, that just like with other parts of the SQL standard, not all databases follow this section fully. PostgreSQL for example stores all unquoted identifiers lowercased instead of uppercased, so table_name == "table_name" (which is exactly the opposite of the standard). Also some databases are case-insensitive all the time, or case-sensitiveness depend on some setting in the DB or are dependent on some of the properties of the system, usually whether the filesystem is case-sensitive or not.

Note that some database tools might send identifiers quoted all the time, so in instances where you mix queries generated by some tool (like a CREATE TABLE query generated by Liquibase or other DB migration tool), with hand made queries (like a simple JDBC select in your application) you have to make sure that the cases are consistent, especially on databases where quoted and unquoted identifiers are different (DB2, PostgreSQL, etc.)

Answer 2

I don't think SQL Server is case-sensitive, at least not by default.

When I'm querying manually via Management Studio, I mess up case all the time and it cheerfully accepts it:

select cOL1, col2 FrOM taBLeName WheRE ...

Answer 3

I found this blog post to be very helpful (I am not the author). Summarizing (please read, though):

...delimited identifiers are case sensitive ("table_name" != "Table_Name"), while non quoted identifiers are not, and are transformed to upper case (table_name => TABLE_NAME).

He found DB2, Oracle and Interbase/Firebird are 100% compliant:

PostgreSQL ... lowercases every unquoted identifier, instead of uppercasing it. MySQL ... file system dependent. SQLite and SQL Server ... case of the table and field names are preserved on creation, but they are completely ignored afterwards.

Answer 4

The SQL Keywords are case-insensitive (SELECT, FROM, WHERE, etc), but are often written in all caps. However in some setups table and column names are case-sensitive. MySQL has a configuration option to enable/disable it. Usually case-sensitive table and column names are the default on Linux MySQL and case-insensitive used to be the default on Windows, but now the installer asked about this during setup. For MSSQL it is a function of the database's collation setting.

Here is the MySQL page about name case-sensitivity

Here is the article in MSDN about collations for MSSQL

Answer 5

Identifiers and reserved words should not be case sensitive, although many follow a convention to use capitals for reserved words and Pascal case for identifiers.

See SQL-92 Sec. 5.2

Answer 6

My understanding is that the SQL standard calls for case-insensitivity. I don't believe any databases follow the standard completely, though.

MySQL has a configuration setting as part of its "strict mode" (a grab bag of several settings that make MySQL more standards-compliant) for case sensitive or insensitive table names. Regardless of this setting, column names are still case-insensitive, although I think it affects how the column-names are displayed. I believe this setting is instance-wide, across all databases within the RDBMS instance, although I'm researching today to confirm this (and hoping the answer is no).

I like how Oracle handles this far better. In straight SQL, identifiers like table and column names are case insensitive. However, if for some reason you really desire to get explicit casing, you can enclose the identifier in double-quotes (which are quite different in Oracle SQL from the single-quotes used to enclose string data). So:

SELECT fieldName
FROM tableName;

will query fieldname from tablename, but

SELECT "fieldName"
FROM "tableName";

will query fieldName from tableName.

I'm pretty sure you could even use this mechanism to insert spaces or other non-standard characters into an identifier.

In this situation if for some reason you found explicitly-cased table and column names desirable it was available to you, but it was still something I would highly caution against.

My convention when I used Oracle on a daily basis was that in code I would put all Oracle SQL keywords in uppercase and all identifiers in lowercase. In documentation I would put all table and column names in uppercase. It was very convenient and readable to be able to do this (although sometimes a pain to type so many capitals in code -- I'm sure I could've found an editor feature to help, here).

In my opinion MySQL is particularly bad for differing about this on different platforms. We need to be able to dump databases on Windows and load them into UNIX, and doing so is a disaster if the installer on Windows forgot to put the RDBMS into case-sensitive mode. (To be fair, part of the reason this is a disaster is our coders made the bad decision, long ago, to rely on the case-sensitivity of MySQL on UNIX.) The people who wrote the Windows MySQL installer made it really convenient and Windows-like, and it was great to move toward giving people a checkbox to say "Would you like to turn on strict mode and make MySQL more standards-compliant?" But it is very convenient for MySQL to differ so signficantly from the standard, and then make matters worse by turning around and differing from its own de facto standard on different platforms. I'm sure that on differing Linux distributions this may be further compounded, as packagers for different distros probably have at times incorporated their own preferred MySQL configuration settings.

Here's another SO question that gets into discussing if case-sensitivity is desirable in an RDBMS.