Need idea about COLLATE

I need help regarding what is a collate and where to use.If any examples with INNER JOIN it could be more helpful....

Here is an example. Collate ensures that the CustomerID is compared based on the same collation.

Code Snippet

select c.customer_full_name, count(*) cnt
from customers c join orders o

on c.CustomerID collate Latin1_General_CS_AS = o.CustomerID collate Latin1_General_CS_AS

|||

thanks,

It could be helpfull if u explain what is a collate why we should go for collate|||

Depending on the collation of a table/database, queries can give different results or may not even work at all (in the case of a join between 2 columns with different collations).

Check out Books Online:

http://msdn2.microsoft.com/en-us/library/ms143726.aspx

HTH!

|||

HI,

Could you give example of different collation of table like table structure so that i can work and clarify.

Why i need structure is in the below example i placed "join" in place collate it showed error.when i replaced it to collate it works fine with SQL2000..need help.

Select
c.Version_id,wit.catalog_tour_id,wit.contentsite_id,wit.sequence,wit.dayrange,wit.short_description,wit.long_description,
'Photon',getdate(),'Photon',getdate(),0,wit.itinerary_image,wit.itinerary_image_description
From webeftours..TourItinerary wit(NoLock)
Inner Join CatalogTour c
on wit.Catalog_Tour_id = c.Catalog_tour_id collate SQL_Latin1_General_CP1_CI_AI
WHERE wit.catalog_tour_id in (
'GSJ D'
)

|||

From Books Online:

Code Snippet

USE tempdb
GO

CREATE TABLE TestTab (
id int,
GreekCol nvarchar(10) collate greek_ci_as,
LatinCol nvarchar(10) collate latin1_general_cs_as
)
INSERT TestTab VALUES (1, N'A', N'a')
GO
SELECT *
FROM TestTab
WHERE GreekCol = LatinCol
GO
SELECT *
FROM TestTab
WHERE GreekCol = LatinCol COLLATE greek_ci_as

I would suggest in your case, the defaut collation of the database webeftours is different and incompatible with the database containing CatalogTour.

You can change the collation of a column using ALTER TABLE. You can also change the default COLLATION of a database (ie when a collation is not specified in DDL statements, it takes the database default) though only new objects will have the new collation. You'd have to change existing objects manually.

|||

Thanks,

When i tried to create the table it throws an error(Server: Msg 170, Level 15, State 1, Line 3
Line 3: Incorrect syntax near 'collate'.)..need help

|||

Can you post the statement you are trying to execute?

|||

use scratch

go
CREATE TABLE TestTab (
id int,
GreekCol nvarchar(10) collate greek_ci_as ,
LatinCol nvarchar(10) collate latin1_general_cs_as
)

|||

What version of sql server are you running that query on? If 2000 or above, can you check what the result of:

sp_dbcmptlevel 'scratch'

If its 70 or below then that may be your problem.

|||Result i got is"The current compatibility level is 70."|||

Ok, you need to update your compatibility level.

If you are running SQL2000 run:

sp_dbcmptlevel 'scratch', 80

This should solve the syntax problem.

Good luck!

|||

Collate was introduced in sql2k. So, you'd want to set your data compatibility to 80.

Code Snippet

exec sp_dbcmptlevel 'your_db_name', 80