What Does 'Collate Sql_Latin1_General_Cp1_Ci_As' Do?

Chloe Bennett
Chloe Bennett
• • 9 min read

I Have an Sql Query to Create the Database in Sqlserver as Given Below: Create Database Yourdb on ( Name = 'Yourdb_Dat', Filename = 'C: \Program...

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
on
( 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;
go

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?

5 Answers

It sets how the database server sorts (compares pieces of text). in this case:

SQL_Latin1_General_CP1_CI_AS

breaks up into interesting parts:

  1. latin1 makes the server treat strings using charset latin 1, basically ascii
  2. CP1 stands for Code Page 1252
  3. CI case insensitive comparisons so 'ABC' would equal 'abc'
  4. AS accent sensitive, so 'ü' does not equal 'u'

P.S. For more detailed information be sure to read @solomon-rutzky's answer.

9

Please be aware that the accepted answer is a bit incomplete. Yes, at the most basic level Collation handles sorting. BUT, the comparison rules defined by the chosen Collation are used in many places outside of user queries against user data.

Must Read

If "What does COLLATE SQL_Latin1_General_CP1_CI_AS do?" means "What does the COLLATE clause of CREATE DATABASE do?", then:

The COLLATE {collation_name} clause of the CREATE DATABASE statement specifies the default Collation of the Database, and not the Server; Database-level and Server-level default Collations control different things.

Server (i.e. Instance)-level controls:

  • Database-level Collation for system Databases: master, model, msdb, and tempdb.
  • Due to controlling the DB-level Collation of tempdb, it is then the default Collation for string columns in temporary tables (global and local), but not table variables.
  • Due to controlling the DB-level Collation of master, it is then the Collation used for Server-level data, such as Database names (i.e. name column in sys.databases), Login names, etc.
  • Handling of parameter / variable names
  • Handling of cursor names
  • Handling of GOTO labels
  • Default Collation used for newly created Databases when the COLLATE clause is missing

Database-level controls:

  • Default Collation used for newly created string columns (CHAR, VARCHAR, NCHAR, NVARCHAR, TEXT, and NTEXT -- but don't use TEXT or NTEXT) when the COLLATE clause is missing from the column definition. This goes for both CREATE TABLE and ALTER TABLE ... ADD statements.
  • Default Collation used for string literals (i.e. 'some text') and string variables (i.e. @StringVariable). This Collation is only ever used when comparing strings and variables to other strings and variables. When comparing strings / variables to columns, then the Collation of the column will be used.
  • The Collation used for Database-level meta-data, such as object names (i.e. sys.objects), column names (i.e. sys.columns), index names (i.e. sys.indexes), etc.
  • The Collation used for Database-level objects: tables, columns, indexes, etc.

Also:

  • ASCII is an encoding which is 8-bit (for common usage; technically "ASCII" is 7-bit with character values 0 - 127, and "ASCII Extended" is 8-bit with character values 0 - 255). This group is the same across cultures.
  • The Code Page is the "extended" part of Extended ASCII, and controls which characters are used for values 128 - 255. This group varies between each culture.
  • Latin1 does not mean "ASCII" since standard ASCII only covers values 0 - 127, and all code pages (that can be represented in SQL Server, and even NVARCHAR) map those same 128 values to the same characters.
Chloe Bennett

Chloe Bennett

Culture, Media & Entertainment Columnist
Instagram Twitter Pinterest

Chloe Bennett explores the intersection of pop culture, streaming entertainment, digital trends, and contemporary lifestyle. Her weekly commentary reaches thousands of culture enthusiasts.