![]() On a server that is case sensitive, both fail: Given this script (guess how the sensitive and non-sensitive databases have been setup!): ![]() The first question I had was what collaction is the actual T-Sql script compiled in, is it the database or server? ![]() Given how important this is I thought I would investigate it a little: I really wasn't too sure of the answer, I generally try to ensure that databases are in the company default collation although I realise that this is not always possible. I was asked an interesting question about collations in sql server recently about where variables in a script got their collation's from. Hi Kristen,That makes perfect sense, now that you put it that way! We do use some accented characters, and having used binary collation for most of our apps, uniqueness may extend to case for some values.I'll use your clever test (col1 = col1 COLLATE _BIN AND col1 != col1 COLLATE _CI_AS) to check our data for inconsistencies and potential problems.What collation variables take on in T-SQL Whereas the CI_AS collation will be case insensitive in that regard - you will know your own data as to which will perform correctly for you.You also need to consider what happens to accented characters (maybe you haven't got any) which will be treated as ordinary letters - E-acute and E for example.One approach might be to run some queries to see what slips through the net: SELECT TOP 1000 t1.col1, t2.col1FROM #temp1 t1, #temp2 t2WHERE t1.col1 = t2.col1 COLLATE Latin1_General_CI_AS AND t1.col1 t2.col1 COLLATE Latin1_General_BINSELECT TOP 1000 t1.col1, t2.col1FROM #temp1 t1, #temp2 t2WHERE t1.col1 = t2.col1 COLLATE Latin1_General_BIN AND t1.col1 t2.col1 COLLATE Latin1_General_CI_ASI don't think the second one is capable of finding any differences(I think you need to change your test data so you have a range of different capitalisations and some accents for t2.col1)You may also want to check the tables for duplicates in that column, forcing the collation you will use - they may be unique at Latin1_General_BIN but not using Latin1_General_CI_AS!Kristen So a difference of only an upper/lowercase letter will be treated as significant. CI_AS then your choice is based on:If you use the BIN collation you will be forcing a binary collation. " and WHY should I use one over the other? Or does it matter at all?"Arnold is your man for this question, but I reckon if you basically have the same "language" collation and the only difference is BIN v. Try:UPDATE t2SET t2.col2 = t1.col2FROM #temp1 t1, #temp2 t2WHERE t1.col1 = t2.col1 COLLATE Latin1_General_CI_AS If the source, or the target, collation should be specified when doing an update, I would like to know which is preferred and why.Thanks for your help!Regards,Daniel - return data to verify the updateSELECT t1.col1, t1.col2, t2.col2FROM #temp1 t1, #temp2 t2WHERE t1.col1 = t2.col1ĜOLLATE Latin1_General_BIN- clean upDROP TABLE #temp1GODROP TABLE #temp2GO The question is, which collation name should I use? WHERE t1.col1 = t2.col1ĜOLLATE Latin1_General_BINORWHERE t1.col1 = t2.col1ĜOLLATE Latin1_General_CI_AS.and WHY should I use one over the other? Or does it matter at all?Since this will be a "synchronization" of data, I have to do this in both directions (with of course some other criteria to identify which records to sync which way). Could you give me your opinions?First, a sample environment: - create tables for testingCREATE TABLE #temp1 ( col1 NVARCHAR(10)ĜOLLATE Latin1_General_BIN, col2 NVARCHAR(30)ĜOLLATE Latin1_General_BIN )GOCREATE TABLE #temp2 ( col1 NVARCHAR(10)ĜOLLATE Latin1_General_CI_AS, col2 NVARCHAR(30)ĜOLLATE Latin1_General_CI_AS )GO- insert sample dataINSERT INTO #temp1 ( col1, col2 )SELECT 'test1', 'This is test row 1'UNION ALLSELECT 'test2', 'This is test row 2'UNION ALLSELECT 'test3', 'This is test row 3'GOINSERT INTO #temp2 ( col1, col2 )SELECT 'test1', 'sample data item 1'UNION ALLSELECT 'test2', 'sample data item 2'UNION ALLSELECT 'test3', 'sample data item 3'GONow, the update statement: - updateUPDATE t2SET t2.col2 = t1.col2FROM #temp1 t1, #temp2 t2WHERE t1.col1 = t2.col1This of course fails, saying "Cannot resolve collation conflict for equal to operation." So I need to use the collation name in my WHERE clause to compare columns. ![]() Hello all,We have to synchronize data between databases with different collations, and I'm not sure what is the preferred syntax for the update statement. Proper use of COLLATE in UPDATE statement? We've got lots of great SQL ServerĮxperts to answer whatever question you can come up with.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |