It’s always pain in the neck when it comes to case sensitive programming. A very common question would come across in every SQL server newbie: Is T-SQL case-sensitive?
The Answer is “NO”. The following two statements will produce the same results without any error.
i)
SELECT *
FROM SYS.OBJECTS
ii)
select *
from sys.objects
But will the following statements produce the same result?
SELECT *
FROM STUDENT
WHERE NAME = 'JOHN'
SELECT *
FROM STUDENT
WHERE NAME = 'john
The answer is “it depends on the collation” as our search string differs in case sensitivity.
In simple words collation in SQL server is a set of rules that determine how data is sorted and compared. We can make the search string as case sensitive in SQL server. Let’s check the collation of the current database.
SELECT DATABASEPROPERTYEX(DB_NAME(),'COLLATION')
The collation in my test database is,
SQL_Latin1_General_CP1_CI_AS
Here CI specifies Case Insensitive.
SP_HELP can be used to identify the table & column level collation.Some of the other possible collations are:
SQL_Latin1_General_Cp437_CS_AS (CS specifies case-sensitive).
SQL_Latin1_General_Pref_CP1_CI_AS
SQL_Hungarian_Cp1250_CI_AS
SQL_Croatian_Cp1250_CI_AS
SQL_Latin1_General_Cp1254_CI_AS
Our counterpart PL/SQL is also not case sensitive, so lower case letters are equivalent to corresponding upper-case letters except within string and character literals. This is what differentiates PL/SQL and T_SQL in case-sensitivity. The Collation can be changes as the project requires. But when its comes to whether to use all Caps or all smalls in T-SQL , its better be consistent throughout the scripts.It facilitates more readability.
Happy reading!
The Answer is “NO”. The following two statements will produce the same results without any error.
i)
SELECT *
FROM SYS.OBJECTS
ii)
select *
from sys.objects
But will the following statements produce the same result?
SELECT *
FROM STUDENT
WHERE NAME = 'JOHN'
SELECT *
FROM STUDENT
WHERE NAME = 'john
The answer is “it depends on the collation” as our search string differs in case sensitivity.
In simple words collation in SQL server is a set of rules that determine how data is sorted and compared. We can make the search string as case sensitive in SQL server. Let’s check the collation of the current database.
SELECT DATABASEPROPERTYEX(DB_NAME(),'COLLATION')
The collation in my test database is,
SQL_Latin1_General_CP1_CI_AS
Here CI specifies Case Insensitive.
SP_HELP can be used to identify the table & column level collation.Some of the other possible collations are:
SQL_Latin1_General_Cp437_CS_AS (CS specifies case-sensitive).
SQL_Latin1_General_Pref_CP1_CI_AS
SQL_Hungarian_Cp1250_CI_AS
SQL_Croatian_Cp1250_CI_AS
SQL_Latin1_General_Cp1254_CI_AS
Our counterpart PL/SQL is also not case sensitive, so lower case letters are equivalent to corresponding upper-case letters except within string and character literals. This is what differentiates PL/SQL and T_SQL in case-sensitivity. The Collation can be changes as the project requires. But when its comes to whether to use all Caps or all smalls in T-SQL , its better be consistent throughout the scripts.It facilitates more readability.
Happy reading!
3 comments:
Good post Magesh.
If we compare it with Oracle.. Oracle, by default is case sensitive whereas SQL Server installations by default are case-insensitive i.e. if you have a column called say FIRST_NAME and you want it to be unique, you can have SAM and sam as two values in it in Oracle where as in SQL Server, that will error out with a unique key violation...
False
The command i and ii also depend on the collation for object of your database.
Good post, you can also convert the varchar to varbinary to make sure it distinguish cases, or change the database collation
T-SQL Case sensitive queries
Greetings
Post a Comment