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)
Bala,
ReplyDeleteThanks 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.
Thanks Arun. Will do that.
ReplyDeleteWhat 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.
Real Time issues:
ReplyDeleteConvert DB Collation Sort Type in SQL 2005 for MS Dynamics GP [microsoft.public.sqlserver.server]
HAfandi Wrote:
Hi,
we have ms dynamics gp with SQL 2005, our database collation type is
Arabic_Bin
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)
thanks
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:
ALTER DATABASE COLLATE
To change the collation on the specific column, please run the following
command:
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
Server
http://support.microsoft.com/default.aspx?scid=kb;en-us;325335
NOTE: This KB article also applies to SQL Sever 2005