Create a table using collation Latin1_General_CI_AS and add some data to it IF EXISTS(SELECT 1 FROM sys.tables WHERE Name = 'MyTable2') IF EXISTS(SELECT 1 FROM sys.tables WHERE Name = 'MyTable1') For example, if we run the following SQL batch. ![]() But just because they appear to be the same, you shouldn't freely mix them up within your database or instance. This query would lead you to believe that the two collations are the same with regard to the language/locale and you would expect to get identical results when using either collation. The "Version" column denotes which SQL version the collation was introduced with. We can see that the code page, language code identifier and comparison style are all identical. SELECT 'Latin1_General_CI_AS' AS 'Collation',ĬOLLATIONPROPERTY('Latin1_General_CI_AS', 'CodePage') AS 'CodePage',ĬOLLATIONPROPERTY('Latin1_General_CI_AS', 'LCID') AS 'LCID',ĬOLLATIONPROPERTY('Latin1_General_CI_AS', 'ComparisonStyle') AS 'ComparisonStyle',ĬOLLATIONPROPERTY('Latin1_General_CI_AS', 'Version') AS 'Version' SELECT 'SQL_Latin1_General_CP1_CI_AS' AS 'Collation',ĬOLLATIONPROPERTY('SQL_Latin1_General_CP1_CI_AS', 'CodePage') AS 'CodePage',ĬOLLATIONPROPERTY('SQL_Latin1_General_CP1_CI_AS', 'LCID') AS 'LCID',ĬOLLATIONPROPERTY('SQL_Latin1_General_CP1_CI_AS', 'ComparisonStyle') AS 'ComparisonStyle',ĬOLLATIONPROPERTY('SQL_Latin1_General_CP1_CI_AS', 'Version') AS 'Version' This function takes in a two parameters, the first is the collation name and the second is the property you require from the collation. I'm going to use the SQL function COLLATIONPROPERTY to find out a little bit more about how the collation is defined within SQL server. SQL_Latin1_General_CP1_CI_AS vs Latin1_General_CI_AS ![]() So if you are interested in researching it further, this SQL 2005 white paper is a very good starting point for those wishing to dig much deeper into the technology. SQL server collations have evolved greatly since version 6.5 and the subject matter itself is far too vast for one blog. You can query the available collations by using the built in table function fn_helpcollations() e.g SELECT * FROM fn_helpcollations() Just by using a different collation on the column, we get completely different results when the data is ordered by that column, so getting the collation correct is fundamental if you require your data to sorted and compared correctly. INSERT INTO MyTable2 (Comments) VALUES ('Colima') INSERT INTO MyTable2 (Comments) VALUES ('Chiapas') Create a second table using collation Traditional_Spanish_CI_AS and add some data to itĬomments VARCHAR(100) COLLATE Traditional_Spanish_CI_AS INSERT INTO MyTable1 (Comments) VALUES ('Colima') INSERT INTO MyTable1 (Comments) VALUES ('Chiapas') To demonstrate: -Create a table using collation Latin1_General_CI_AS and add some data to itĬomments VARCHAR(100) COLLATE Latin1_General_CI_AS And if using the traditional Spanish collation "ch" would be sorted at the end of a list of words beginning with "c". For example if you was to use a Lithuanian collation, the letter "Y" would appear between "I" and "J" if sorted. The rules for sorting data vary depending on the language and locale. A collation itself specifies the rules for how strings of character data are sorted and compared. You can download the SQL script for the examples in this blog here. I most certainly did many years ago when I first started to use SQL server and saw the infamous "cannot resolve the collation conflict between xx and xx" error far too many times! Collations are one area that some new SQL techies get tripped up on early on. Being fairly methodical though, afterwards I spent a little bit of time ensuring that what I had said was correct, but when I researched the subject matter a little further, I was very surprised at what I found and as such decided to write this article to share some of those findings with you. I was recently asked this question and at the time I answered off the top of my head that there is no functional difference between the two collations in terms of how the data is sorted, but you still shouldn't intermix them freely within your database. Here is a new revised article that explains character expansions and how it can affect the comparison of data with these two collations. ![]() So apologies to the community with regard the first article that I published as some of the original content could have been misleading. Even though I thought that I was very meticulous in my research, something was pointed out to me that I was totally unaware of - Unicode character expansions. This is a revised post of an original article that I first published in December.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |