What are Collation in Sql Server

Cursor- Sql Server
Cursor- Sql Server
0
(0)

collation | Set collation in Sql Server | Check collation in Sql Server

Collation is a set of rules that are predefined in SQL Server that determine how the data in SQL Server are stored, retrieved and compared.
3 main Collations.
— SQL_Latin1_General_CP1_CI_AS  –is case insensitive, the default one
— SQL_Latin1_General_CP1_CS_AS –is case sensitive
— SQL_Latin1_General_CP437_BIN — binary
 
Set Collate at Database level:
create database dbcommerce COLLATE SQL_Latin1_General_CP1_CI_AS

Set Collate at Column level:
CREATE TABLE Vendor (VendorID int, Name varchar(50) COLLATE SQL_Latin1_General_CP437_BIN)

Check Collate at Database level:
A. At Instance Level
Right-click on a database>> property menu >> check the extended property.
OR
SELECT SERVERPROPERTY(‘collation’)

 

B. At Table Level
Right-click on a table >> property menu >> check the extended property.
sp_help Alert

 

C. At Column Level
Table in Design View >> Properties of Column >> set Collation under Table Designer Group.

USING COLLATION WHILE QUERY TABLE

SELECT * FROM book WHERE subject_code COLLATE SQL_Latin1_General_CP1_CS_AS ='BK001'

JOINING TWO COLUMNS OF A DIFFERENT COLLATION

SELECT * FROM Products P INNER JOIN
Category P ON P.Code=C.Code collate SQL_Latin1_General_CP1_CI_AS

How useful was this post?

Click on a star to rate it!

Average rating 0 / 5. Vote count: 0

No votes so far! Be the first to rate this post.

Be the first to comment

Leave a Reply

Your email address will not be published.


*


two + 7 =