DB Case Sensitivity, unexpected/surprising behaviour
-
I'm surprised, that the COLLATION used in a DB Create Statement does have influence in executing SQL statements. Following I testet: A. Insensitive: Creating a DB explicitely "Case Insensitive" CREATE DATABASE CIDB COLLATE Latin1_General_CI_AI; GO USE CIDB; CREATE TABLE TEST (ID INTEGER); SELECT * FROM TEST; select * FROM TEST; SELECT * FROM Test; select * FROM Test; :) --> Everything works like expected, MSSQL does not bother about "TEST" or "Test". B. Sensitive: Creating a DB explicitely "Case Sensitive" CREATE DATABASE CSDB COLLATE Latin1_General_CS_AI; GO USE CSDB; CREATE TABLE TEST (ID INTEGER); SELECT * FROM TEST; Everyting up to here is working fine. But ... SELECT * FROM Test; ??--> fails with error message "Msg 208, Level 16, State 1, Line 1 Invalid object name 'Test'." ... obviously because the database is created "Sensitive". What's really surprising for me, that the collation sequence now also makes the SQL Language a case sensitive, not the language it self but the objects (table-, field- names and I assume also constraint names). Am I the only one who is surprised by this? Thank you in advance for some high lighting comments.
-
I'm surprised, that the COLLATION used in a DB Create Statement does have influence in executing SQL statements. Following I testet: A. Insensitive: Creating a DB explicitely "Case Insensitive" CREATE DATABASE CIDB COLLATE Latin1_General_CI_AI; GO USE CIDB; CREATE TABLE TEST (ID INTEGER); SELECT * FROM TEST; select * FROM TEST; SELECT * FROM Test; select * FROM Test; :) --> Everything works like expected, MSSQL does not bother about "TEST" or "Test". B. Sensitive: Creating a DB explicitely "Case Sensitive" CREATE DATABASE CSDB COLLATE Latin1_General_CS_AI; GO USE CSDB; CREATE TABLE TEST (ID INTEGER); SELECT * FROM TEST; Everyting up to here is working fine. But ... SELECT * FROM Test; ??--> fails with error message "Msg 208, Level 16, State 1, Line 1 Invalid object name 'Test'." ... obviously because the database is created "Sensitive". What's really surprising for me, that the collation sequence now also makes the SQL Language a case sensitive, not the language it self but the objects (table-, field- names and I assume also constraint names). Am I the only one who is surprised by this? Thank you in advance for some high lighting comments.
It might seem odd at first glance, but it makes sense when you consider the fact that the names of all the objects within the database are stored in system tables within the database itself. If the default collation for the database is case-sensitive, then the collation for all of the system tables is case-sensitive. So
SELECT * FROM sys.tables WHERE name = 'Test'isn't going to match a row wherename = 'TEST'. Whilst Microsoft could have chosen to make the system tables always case-insensitive, that would have been a breaking change. Earlier versions of SQL Server didn't allow you to mix collations within a single database, so introducing that change in a later version could have broken existing databases or scripts. There are effectively two "solutions":- Always create your databases using a case-insensitive collation, and use a case-sensitive collation on individual columns;
- Always write your scripts using the case of the table and columns names as defined when they were created;
SQL Server Case Sensitive Collations and DMVs[^]
"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer
-
It might seem odd at first glance, but it makes sense when you consider the fact that the names of all the objects within the database are stored in system tables within the database itself. If the default collation for the database is case-sensitive, then the collation for all of the system tables is case-sensitive. So
SELECT * FROM sys.tables WHERE name = 'Test'isn't going to match a row wherename = 'TEST'. Whilst Microsoft could have chosen to make the system tables always case-insensitive, that would have been a breaking change. Earlier versions of SQL Server didn't allow you to mix collations within a single database, so introducing that change in a later version could have broken existing databases or scripts. There are effectively two "solutions":- Always create your databases using a case-insensitive collation, and use a case-sensitive collation on individual columns;
- Always write your scripts using the case of the table and columns names as defined when they were created;
SQL Server Case Sensitive Collations and DMVs[^]
"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer
-
It might seem odd at first glance, but it makes sense when you consider the fact that the names of all the objects within the database are stored in system tables within the database itself. If the default collation for the database is case-sensitive, then the collation for all of the system tables is case-sensitive. So
SELECT * FROM sys.tables WHERE name = 'Test'isn't going to match a row wherename = 'TEST'. Whilst Microsoft could have chosen to make the system tables always case-insensitive, that would have been a breaking change. Earlier versions of SQL Server didn't allow you to mix collations within a single database, so introducing that change in a later version could have broken existing databases or scripts. There are effectively two "solutions":- Always create your databases using a case-insensitive collation, and use a case-sensitive collation on individual columns;
- Always write your scripts using the case of the table and columns names as defined when they were created;
SQL Server Case Sensitive Collations and DMVs[^]
"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer
Quote:
that would have been a breaking change
After thinking again and again about it (I'm an old man and need therefor more time), is it not MS who is breaking the SQL Standard with this? Not a big thing, only a thought. Finally, it is like it is (with MSQL) and I have to live with it.
-
Quote:
that would have been a breaking change
After thinking again and again about it (I'm an old man and need therefor more time), is it not MS who is breaking the SQL Standard with this? Not a big thing, only a thought. Finally, it is like it is (with MSQL) and I have to live with it.
0x01AA wrote:
is it not MS who is breaking the SQL Standard with this?
Yes. It looks like SQL92 says that object names should be case-insensitive unless they are quoted. I guess they decided the dangers of making the change outweighed the inconvenience of not following the standards.
"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer
-
0x01AA wrote:
is it not MS who is breaking the SQL Standard with this?
Yes. It looks like SQL92 says that object names should be case-insensitive unless they are quoted. I guess they decided the dangers of making the change outweighed the inconvenience of not following the standards.
"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer
