If you right click a database and view the properties, you can look for the collation setting as shown in the figure. The highlighted ‘CI’ represents the database is case-insensitive. Suppose if it is ‘CS’ then your database is case sensitive.
For example, the following query returns different results in different collation settings.
SELECT * FROM TestTab WHERE CharCol LIKE N'abc'
Following query creates a column with ‘Case In-sensitive’
CREATE TABLE TestTab
(PrimaryKey int PRIMARY KEY,
CharCol char(10) COLLATE French_CI_AS
1. At what level you can specify the COLLATE clause. Choose all correct answers.
a. Creating or altering a database.
b. Creating or altering a table column.
c. Casting the collation of an expression.
d. All of the above.
2. What function is used to retrieve a list of all the valid collation names for Windows collations and SQL collations
3. Which of the following datatypes are used with COLLATE clause.
a. char, varchar, text
b. nchar, nvarchar, and ntext
c. int, smallint, tinyint
d. datetime, money
3. (a), (b)
Or Hide Answers (X).
Reference: Books on line: COLLATE (Transact-SQL)