Thursday, July 29, 2010

Questions based on Collation settings

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.


Following query creates a column with ‘Case In-sensitive’

(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)


  1. Bala,

    Thanks for your effort to start such a blog.
    Suggestion from my side, in addition to the multiple choice qustions above, can we have the basic definition of the concept 'Collation in SQL' and some examples for real time usage of the same.

  2. Thanks Arun. Will do that.

    What is collation?
    A collation specifies the bit patterns that represent each character. It also specifies the rules that are used to sort and to compare the characters. A collation has the following characteristics:

    * Language
    * Case sensitivity
    * Accent sensitivity
    * Kana sensitivity

    To know the collation that a server is currently using, you can run the sp_helpsort system procedure in SQL Query Analyzer.

  3. Real Time issues:

    Convert DB Collation Sort Type in SQL 2005 for MS Dynamics GP [microsoft.public.sqlserver.server]

    HAfandi Wrote:


    we have ms dynamics gp with SQL 2005, our database collation type is

    we want to change the collation to
    Accent-sensitive sort type

    is there any way to convert the database collation sort type without
    damaging the data (specially the arabic language fields)

    Hasan Afandi


    Adams Qu [MSFT]:

    Dear Hasan,

    Thank you for posting here.

    Regarding your question, you could use the COLLATE clause on an ALTER
    DATABASE to change the default collation of the database, but there are
    some limitations to this as well and really should only be used if the text
    data columns are Unicode types, because changing the collation of any text,
    varchar and char fields that have no explicit column collation will alter
    the way the data's encoding is interpreted, resulting in a form of
    corruption of any characters that are out of the ASCII range. Usually, it's
    not recommended to change database collation directly.

    For the security reason, it is recommended that you firsts BACKUP the
    Database and then change the default collation of the database via the
    following SQL command:


    To change the collation on the specific column, please run the following

    Alter table SysTotalizedValue
    Alter column vVar COLLATE

    Alternatively, we can also attempt to use DTS/SSIS to change collation of
    the database (as suggested by Urid). For the detail steps, please refer to
    the following KB article:

    How to transfer a database from one collation to another collation in SQL

    NOTE: This KB article also applies to SQL Sever 2005