Thunder Thunder - 1 year ago 106
SQL Question

What does 'COLLATE SQL_Latin1_General_CP1_CI_AS' do?

I have an SQL query to create the database in SQLServer as given below:

create database yourdb
( name = 'yourdb_dat',
filename = 'c:\program files\microsoft sql server\mssql.1\mssql\data\yourdbdat.mdf',
size = 25mb,
maxsize = 1500mb,
filegrowth = 10mb )
log on
( name = 'yourdb_log',
filename = 'c:\program files\microsoft sql server\mssql.1\mssql\data\yourdblog.ldf',
size = 7mb,
maxsize = 375mb,
filegrowth = 10mb )
COLLATE SQL_Latin1_General_CP1_CI_AS;

It runs fine.

While rest of the SQL is clear to be I am quite confused about the functionality of
COLLATE SQL_Latin1_General_CP1_CI_AS

Can anyone explain this to me? Also, I would like to know if creating the database in this way is a best practice?

Answer Source

It sets how the database server sorts. in this case:


breaks up into interesting parts:

  1. latin1 makes the server treat strings using charset latin 1, basically ascii
  2. CI case insensitive comparisons so 'ABC' would equal 'abc'
  3. AS accent sensitive, so 'ΓΌ' does not equal 'u'
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download