Menu
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?
255
Performance there is no impact other than the speed difference between the different collations.
Answer. CP1 stands for code page 1252 is a character encoding of the Western European Latin.
Collation provides the sorting rules, case, and accent sensitivity properties for the data in the database. For example, when you run a query using the ORDER BY clause, collation determines whether or not uppercase letters and lowercase letters are treated the same.
If you do not specify a collation, the column is assigned the default collation of the database. You can also use the database_default option in the COLLATE clause to specify that a column in a temporary table use the collation default of the current user database for the connection instead of tempdb.
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:
latin1 makes the server treat strings using charset latin 1, basically asciiCP1 stands for Code Page 1252CI case insensitive comparisons so 'ABC' would equal 'abc'AS accent sensitive, so 'ü' does not equal 'u'P.S. For more detailed information be sure to read @solomon-rutzky's answer.
111
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
