collation

Question

collation

vinod-328175

Old Hand

Points: 398
More actions
September 5, 2006 at 12:10 am

#170236

dear experts
while i'm learning BCP from books online, i got a doubt.
copying data between different collations.
what exactly the meaning of collation?
i tried in BOL. but because i'm a junior, i didnt got the good idea about collation.
please explain me
thankyou friends

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

SwePeso SSC-Dedicated Points: 39725 More actions · Answer 1

Collation is the set of characters used to represent your or any other's alphabet.

We in Sweden most often use the FINNISH_SWEDISH collation to be able to view our special characters, such as {å, ä, ö} and {Å, Ä, Ö}. We have 29 letters in our alphabet, where the english and americans has only 26 letters {a-z}. We have {a-z} plus {å}, {ä}, and {ö}.

Spanish speaking countries has their special characters such as {ñ}.

Other countries has other special symbols such as {Þ} (iceland) and {ü} (germany).

Does this answer your question?

N 56°04'39.16"
E 12°55'05.25"

Gift Peddie SSC Guru Points: 73570 More actions · Answer 2

In SQL server there is a separate collation for every language in Europe, six Chinese and the list goes on the reason is with column level collation you can render most langauges and avoid character conversion. That is when you data come out as ???. If you are moving data between columns you can use collation precedence where you can add the specific collation in your DML(data manupilation langauge) not just DDL(data definition language). All of the above is easy in SQL Server 2000/2005 because in SQL Server 7.0 you have to reinstall to change collation. Post again if you still have questions. Hope this helps.

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

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

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

Kind regards,

Gift Peddie

Kind regards,
Gift Peddie

homebrew01 SSC Guru Points: 55454 More actions · Answer 3

There are also different collations within English. For example, most of our databases use a collation that is case INSENSITIVE, but one of our databases requires a collation that is case SENSITIVE. That can cause difficulties when comparing data between the 2 databases, so we have to logically convert one or the other if we have a query that spans databases.

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 4

I've never seen such a need... Why do you have to put the whole db case sensitive?

homebrew01 SSC Guru Points: 55454 More actions · Answer 5

Our Financial package, Lawson, required that collation for their database. All our other databases use the "normal" collation. We have a few situations where we join tables between Lawson and another database. Then we have to specify a collation in our join statement something like this:

Select FldA, FldB from LawsonTable L

join RegularTable R on L.KeyFld = R.KeyFld collation Latin_1_General_Bin (or whatever it is)

I'm sure this scenario is rather rare, but it does happen.

Rudyx - the Doctor SSC-Forever Points: 43695 More actions · Answer 6

RGR, we have a few specific vendor supplied applications that require the entrie server, not just the database, to use Latin_1_General_Bin. These SQL Servers interface to AS400's and related applications from the same vendor. At the present time I have 4 SQL Servers and 3 applications that fit this model.

RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."

Gift Peddie SSC Guru Points: 73570 More actions · Answer 7

Quick answer BIN(binary sort order) is the fastest sort but case sensitivity is required. In a financial application it is a good thing to have albeit a pain to use.

Latin_1_General_Bin means English with binary sort order it improves performance of the application. Check out the stored procs and you will see all the Order By dependent on the BIN.

Kind regards,

Gift Peddie

Kind regards,
Gift Peddie