What is Collation?
1. Each database, or character column has a default
collation(database_default). A collation is a definition for how each character is
represented, stored and compared. Part of the collation setting, known as case
sensitivity.
2. An upper case “A” comparing with a low case “a” is either same or not.
3. If the collation is default(database_default) then the two letters are SAME
/NOT SAME.
DEFAULT COLLATION :
A.Creating a Table
CREATE TABLE Tbl_SQLServer
(Slno INT,
Name VARCHAR(25))
Go
B.Inserting Records
INSERT Tbl_SQLServer VALUES(1,'Table')
INSERT Tbl_SQLServer VALUES(2,'table')
INSERT Tbl_SQLServer VALUES(3,'Stored Procedure')
INSERT Tbl_SQLServer VALUES(4,'Cursor')
INSERT Tbl_SQLServer VALUES(5,'Trigger')
Go
C.Camparing Data
SELECT * FROM Tbl_SQLServer WHERE Name ='Table'
Go
D.Returning Result
Slno Name
1 Table
2 table
CASE-SENSITIVE COLLATION :
A.Creating a Table
CREATE TABLE Tbl_SQLServer
(Slno INT,
Name VARCHAR(25) COLLATE Latin1_General_Cs_As)
Go
B.Inserting Records
INSERT Tbl_SQLServer VALUES(1,'Table')
INSERT Tbl_SQLServer VALUES(2,'table')
INSERT Tbl_SQLServer VALUES(3,'Stored Procedure')
INSERT Tbl_SQLServer VALUES(4,'Cursor')
INSERT Tbl_SQLServer VALUES(5,'Trigger')
Go
C.Camparing Data
SELECT * FROM Tbl_SQLServer WHERE Name ='Table'
Go
D.Returning Result
Slno Name
1 Table
In the above result 'Table' and 'table' data are different based one it's CASE
Sensitive.
APPLYING DEFAULT COLLATION :
Alter Table Tbl_SQLServer Alter Column Name VARCHAR(25) COLLATE database_default
LIST OF COLLATION NAME:
To retrieve a list of all the valid collation names using System function.
SELECT * FROM ::fn_helpcollations()
Name Description
Albanian_BIN Albanian, binary sort
... ...
Pandian S
|
| Author: karthi 25 Feb 2007 | Member Level: Bronze Points : 0 |
i need more details in sqlserver . Please forward it sir
|
| Author: Nagarajan 06 Mar 2007 | Member Level: Bronze Points : 0 |
clearly i understood concept of collate in sqlserver.once again thanks for this article and author
|