SQL Server – Check whether the SQL server is configured for case sensitivity or not

Problem: How to check whether the SQL server is configured for case sensitivity or not?

Solution:

• Case sensitivity: By Case Sensitive, we mean case sensitivity of the objects in the database, i.e. the following two statements are not equal:-

SELECT * FROM TABLE

SELECT * FROM table 

• SQL server determines case sensitivity by COLLATION.

• Collation: Collation refers to a set of rules that determine how data is sorted and compared. Character data is sorted using rules that define the correct character sequence, with options for specifying case-sensitivity, accent marks, kana character types and character width.

• COLLATION can be set at various levels:-

1. Server-level
2. Database-level
3. Column-level
4. Expression-level

• Check Database Collation:-

          SELECT DATABASEPROPERTYEX (”, ‘Collation’)

          Result: SQL_Latin1_General_CP1_CI_AS (means Case Insensitive)

          Result: SQL_Latin1_General_CP1_CS_AS (means Case Sensitive)