MS SQL Server Collation

View previous topic View next topic Go down

MS SQL Server Collation

Post  mugen_kanosei on Tue Mar 17, 2015 8:19 am

I've started migrating our data warehouse from Oracle to MS SQL and ran into an issue that bit me converting the ETL over to SSIS. It appears that out of the box, the database collation is case-insensitive. I wrote a query to presort some data and do a merge join on some other data. This bit me as the merge join transform IS case-sensitive and it failed to join some rows because it was not sorted as it expected. My question is, what is the standard practice for building data warehouse's on MS SQL? Should I create the database using a case-sensitive collation, only sort in the dataflow, or do what Andy Leong suggested here: http://sqlblog.com/blogs/andy_leonard/archive/2010/05/28/ssis-is-case-senstivie.aspx and use an UPPER() join column. But if I go with Andy's suggestion, how do you handle the case where the source system is case-sensitive and "Andy" is different from "AnDy"? I checked in the MDWT 2008R2 book, but could not find any reference to collation.

mugen_kanosei

Posts : 13
Join date : 2009-02-03
Age : 36
Location : Japan

View user profile

Back to top Go down

Re: MS SQL Server Collation

Post  hang on Wed Mar 18, 2015 9:00 pm

Case sensitivity could affect query result involving joins and delta detection in DW. But sometimes we do want most attribute values to be case insensitive, for fussy matching purpose, and "Andy" should be the same person as "AnDy" if all other identifying attributes match as well. I would treat case sensitive attributes individually by defining them using "COLLATE Latin1_General_CS_AS" option in CREATE statement. Once defined as case sensitive, all the joins should work without having anything extra in the queries.

hang

Posts : 528
Join date : 2010-05-07
Location : Brisbane, Australia

View user profile

Back to top Go down

View previous topic View next topic Back to top

- Similar topics

 
Permissions in this forum:
You cannot reply to topics in this forum