A
collation in SQL Server is set of rules that govern the proper use of
characters and alphabets of any language (Polish, Greek) that you select during
installation or while creating the database in existing sql server. Few example
of Collations used in SQL Server are given below
Since collation reflects and follows the rules depending upon the language, region and sorting pattern, it is very important to select right collation in SQL Server. I usually divide collation in two basic configuration parts, and you will see why I have divided it in two parts.
a)
SQL_Latin1_General_CP1_CI_AS
b)
SQL_Latin1_General_CP1250_CI_AS
c)
SQL_Latin1_General_CP1256_CI_AS
Why collation is important in SQL Server?Since collation reflects and follows the rules depending upon the language, region and sorting pattern, it is very important to select right collation in SQL Server. I usually divide collation in two basic configuration parts, and you will see why I have divided it in two parts.
1-
SQL Server default Collation
2-
SQL Server Database Collation
When you install SQL Server default
or Named instance, during installation, you can select appropriate collation
that would be SQL Server default collation. When I say SQL Server default
collation, it means whenever you will create any database with default setting
on existing sql server, it will default that database’s collation to SQL Server
default collation also known as model database collation (in some communities)
as all the databases are created based on model database settings in SQL
Server.
How can you find SQL Server Default Collation?
Method 1.
a)
Connect to SQL Server database
engine
b)
Right click on the server and go to
properties
c)
Find the column showing Server
Collation
 |
| Fig 1- How to find default collation of an existing SQL Server |
 |
| Fig 2- How to find default collation of an existing SQL Server |
Method 2.
Run the below T-SQL command in
master database
SELECT
CONVERT (varchar, SERVERPROPERTY('collation'));
How to find Databases collation?
Method 1.
Run the below T-SQL command in
master database
SELECT name, collation_name FROM
sys.databases
Method 2.
a) Right click on the database
b) Go to properties of the database
c) Click on option
d) First column reflects the current
collation of database.
Note: If database collation is not set while
creating the database, it will be the server default collaction, collation of
databases can be changed after you create a database using above method as
well.
Nice tip. Keep sharing such ideas! Make sure to check this onlineconvertfree
ReplyDeleteExcellent explanation sir
ReplyDelete