0

As per this link, I would like to understand the meaning of 

 Case-sensitivity in string comparison depends on the collating sequence used

As per the additional information retrieved, With this information from msdn, I could not understand the meaning of collation with these below statement:

A collation specifies the bit patterns that represent each character in a data set. Collations also determine the rules that sort and compare data. SQL Server supports storing objects that have different collations in a single database. For non-Unicode columns, the collation setting specifies the code page for the data and which characters can be represented. Data that is moved between non-Unicode columns must be converted from the source code page to the destination code page.

So, Can you help me understand the meaning/significance of collating sequence in database with an example?

Note: I am currently part of database intro course.

overexchange
  • 15,768
  • 30
  • 152
  • 347

1 Answers1

1

While creating database you might feel the need to store data in different language and different languages will have different number of characters with different sort order, so you might need some way to sort them accordingly, at that point we use collation. Collation controls the way string values are sorted. In TSQL you can define it using collate clause as explained here http://msdn.microsoft.com/en-us/library/ms184391.aspx

You can go through the documentation if you want to find out what are different configurations that are supported. If you don't define any collation while creating database it will pick the default collation from current sql server instance. You can also apply collation at database table or at a column and you can use collation while selecting data it will apply to the sort order.

Here is a related question that will help you understand further What does 'COLLATE SQL_Latin1_General_CP1_CI_AS' do?

Community
  • 1
  • 1
zash707
  • 245
  • 2
  • 11
  • If collation is different, Why does it affect comparing string values? I did not understand this point. For example: `SELECT * FROM MyTable1 M1 INNER JOIN MyTable2 M2 ON M1.Comments = M2.Comments` here `MyTable1` has column `Comments VARCHAR(100) COLLATE Latin1_General_CI _AS` and `MyTable2` has column `Comments VARCHAR(100) COLLATE SQL_Lati n1_General_CP1_CI_AS`. This query leads to error, so am still not clear, why `=` cannot happen when collation is different? Please help me!!! – overexchange Dec 29 '14 at 08:12
  • Different collation will store data in different way and it cant be compared unless you convert the collation to same on the fly. you can use a select statement using the different collation and you will see the data is in different format. Detail answer is here http://www.olcot.co.uk/sql-blogs/revised-difference-between-collation-sql_latin1_general_cp1_ci_as-and-latin1_general_ci_as – zash707 Dec 29 '14 at 08:29