Case Sensitivity of SQL Server

The database which is created on SQL Server is not case sensitive by default. For example, the returned results of "select * from xxxtable where xxxname='tim'" and "select * from xxxtable where xxxname='TIM'" are exactly the same.

Check whether the database is case sensitive or not

For an already created database, you can check whether it is case sensitive or not. We will take SQL Server 2005 for example, open SQL Server Management Studio, select this database and select "Properties" from the right click menu to open the "Database Properties" dialog box. Find the database "Collation" properties as shown in the figure.

"Chinese_PRC_" in the Collation properties of "Chinese_PRC_CI_AS" means the mainland simplified UNICODE collation. "CI" (Case Insensitivity, insensitive) or "CS" (Case Sensitivity, sensitive) respectively specifying whether to distinguish case sensitivity. "AI" (Accent Insensitivity, insensitive) or "AS" (Accent Sensitivity, sensitive) respectively specifying whether to distinguish accents.

Create a new database of case sensitive

When creating a new database on SQL Server, its case sensitive option, that is, Collation properties is "case insensitive" by default. If you want to create a case sensitive database, you can explicitly specify the required Collation properties when creating.

We will take SQL Server 2005 Management Studio Express for example, if you want to create a case sensitive database, please pay attention to modify the Collation properties in the "Options" tab from "" to "Chinese_PRC_CS_AS".

Conversion of database case sensitive

The case sensitive of SQL Server database can be converted. Before the conversion, pay attention to disconnect all the accesses to the database, otherwise the conversion will be a failure. Go to the SQL Server Management Studio Express, run the following commands, and then you can convert the database case sensitive.

Set SQL script case insensitive: ALTER DATABASE [DatabaseName] COLLATE Chinese_PRC_CI_AI

Set SQL script case sensitive: ALTER DATABASE [DatabaseName] COLLATE Chinese_PRC_CS_AI

Case sensitive problems of Turkish

Please note that if the SQL Server database is in Turkish, for "I" and "i", as well as "I" and "ı" (without a point), they are always case sensitive. For other letters, it will be in accordance with the database Collation properties to distinguish case sensitive.

For example, even if the database has been set case insensitive (Turkish_CI_AS), it will return different results for "select * from xxxtable where xxxname='iii'" and "select * from xxxtable where xxxname='III'", because they contained "I" and "i".

However, for this "Turkish_CI_AS" case insensitive database, the two sentences, "select * from xxxtable where xxxname='AAA'" and "select * from xxxtable where xxxname='aaa'" will return the same result, because they don't contain "I" and "i".

Solution

If the database you created is case sensitive, then AAMail mail account must be case sensitive as well, otherwise, AAMail mail account's authentication will be failed, which will cause the sending and receiving mails problems. The solution is as follows.

1) Check the case sensitive database.

2) In the "Database Properties" dialog box, set the "Collation" properties as "Chinese_PRC_CI_AS" to be case insensitive, please refer to the above "Check whether the database is case sensitive or not" for information.

3) After the settings is complete, AAMail mail account will be case insensitive.