Different character collation types across source and produc

Xanadu · Post by **Xanadu** » Fri Aug 13, 2004 7:48 am

I posted this in another EPM specific thread too..but reposting it to hear from others..(i am not sure how many clicked on that thread after seeing that specific subject

)

Wouldn't it be a problem if character datatypes in source and target follow different collation types ? Is there any advantage of using one over the other ?
In my implementation, all the target/staging tables use Latin1_General_BIN as collation type where as the sources use SQL_Latin1_General_CP1_CI_AS. This sometimes causes problems in user defined queries when doing the joins. (Collation conflict..)
Any one faced similar problem ?

~Xan

ray.wurlod · Post by **ray.wurlod** » Fri Aug 13, 2004 9:06 am

It shouldn't be a problem, as you're not comparing or sorting or joining during the load process.

Xanadu · Post by **Xanadu** » Fri Aug 13, 2004 9:31 am

not during the load but even the staging tables have a different collation type...so this might be a problem during the transformation right ?
and also Ray.....one question..is there any particular reason why DWH tables contain char instead of varchar ?

thanks Ray

ray.wurlod wrote:It shouldn't be a problem, as you're not comparing or sorting or joining during the load process.

ray.wurlod · Post by **ray.wurlod** » Fri Aug 13, 2004 9:19 pm

Q: is there any particular reason why DWH tables contain char instead of varchar ?

A1: There's the obvious one; whoever issued the CREATE TABLE command put them there.

A2: In some databases (probably most) extracting CHAR is far more efficient than extracting VARCHAR, at a physical level, and definitely far more efficient for loading. Some databases (transparently) put all the VARCHAR columns at the end of the physical record.

A3: Some databases, particularly older versions, do not support VARCHAR. For example, and from memory, VARCHAR support was only introduced into Red Brick at version 6, and that reluctantly.