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
a. fn_sqlvarbasetostr()
b. fn_my_permissions()
c. fn_helpcollations()
d. fn_dblog()
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
Show Answers:
Reference: Books on line: COLLATE (Transact-SQL)
