Use of Collation in MS SQL
Collation is about comparison between characters. It defines a set of rules to compare characters of a character set.A SQL Server collation defines how the database engine stores and operates on character and Unicode data. After data has been moved into an application, however, character sorts and comparisons done in the application are controlled by the Windows locale selected on the computer.
The physical storage of character strings in Microsoft SQL Server 2000 is controlled by collations. A collation specifies the bit patterns that represent each character and the rules by which characters are sorted and compared.
For checking database collation we can write the following query
SELECT CONVERT (varchar, SERVERPROPERTY('collation'))
By changing the collation of database we can change the working of functions in database. For example :- we have employee table and in that there are column as
employee(empid,empname,salary,department)
Now we want to fetch all empname which in capital letter? Now if we use upper() function in where condition,it will convert all empname in uppercase and we get all the record of the table.
The answer to this question is that we can do it by changing collation of database from the default collation which is used.This can done by writing following query.
SELECT * FROM employee WHERE empname COLLATE Latin1_General_CS_AS = UPPER(empname)
Now this will return empname in uppercase only and we fulfill our objective.
I hope this article will help you.