SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Beware of Mixing Collations - Part 1


Beware of Mixing Collations - Part 1

Author
Message
Greg Larsen
Greg Larsen
SSCarpal Tunnel
SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)

Group: General Forum Members
Points: 4433 Visits: 290
Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/glarsen/collate_part1.asp

Gregory A. Larsen, MVP
Dave Poole
Dave Poole
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16920 Visits: 3403
I've come across this problem in SQL 6.5.

Pre-SQL2000 my solution was to have a test machine that I could set up SQL Server 6.5 with whatever character set and sort order I needed to restore other peoples data, then bcp it across onto the production server.

Pure brute force, but strangely satisfying!

LinkedIn Profile
www.simple-talk.com
Steven.
Steven.
SSCommitted
SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)

Group: General Forum Members
Points: 1895 Visits: 247
Beware is right, but as long as it is known to the developers etc I have found it isn't too much of a problem.

I have had to mix collations on a sql 2000 server (Binary sort order server with dbs, plus a single non case sensitive db). Once you remember that tempdb is case sensitive etc it is not too much of a problem.

This has even had the bonus of helping enforce naming standards since tables in the other DB's and tempdb need to be exact.

Steven
Russel Loski
Russel Loski
SSCommitted
SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)

Group: General Forum Members
Points: 1768 Visits: 8907
Can mixed collations affect performance?

I created a new server and database with a different default collation when I took over our small SQL Server environment. In transferring the objects from the existing server to the new production server, I used the scripts created by EM. For some reason, EM puts collation statements on all character based fields.

We connect to SQL Server from Access and for several months the applications would crawl. One night I came in and changed all of the collations to use the defaults. That morning the applications ran real fast and it still runs well. I may have done something else as a side consequence, but the only thing I remember is changing the collations.

Is this a known issue? I don't have the lab facilities to test it.

Russ

Russel Loski

Russel Loski, MCSE Business Intelligence, Data Platform
dwestmore
dwestmore
Valued Member
Valued Member (70 reputation)Valued Member (70 reputation)Valued Member (70 reputation)Valued Member (70 reputation)Valued Member (70 reputation)Valued Member (70 reputation)Valued Member (70 reputation)Valued Member (70 reputation)

Group: General Forum Members
Points: 70 Visits: 1
I've had nasty problems with the collation settings. The strange thing is I had never explicitly done anything to mess about with collation settings, it seemed to happen spontaneously. I work in Italy, some kind of mess up between English and Italian versions of SQL and/or Windows.

I did find a script (I think on this site, don't ask me where) for easily changing collation settings on all tables in a DB, if anyone is interested I can send it.



tomstraetman
tomstraetman
Forum Newbie
Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)

Group: General Forum Members
Points: 3 Visits: 1
that would be great, can you send me the script to tom.straetman@pi.be

thx



Greg Larsen
Greg Larsen
SSCarpal Tunnel
SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)

Group: General Forum Members
Points: 4433 Visits: 290
I'd like that script. Can you send it to me at greg.larsen@doh.wa.gov


Gregory Larsen, DBA

If you looking for SQL Server Examples check out my website at http://www.geocities.com/sqlserverexamples

Gregory A. Larsen, MVP
Andy Warren
Andy Warren
One Orange Chip
One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)

Group: Moderators
Points: 25659 Visits: 2746
Maybe of you guys can either find or repost the script and post the link back here? Others will be interested Im sure.

Greg, have you looked to see what performance implications of specifying a collation in the join? I use the technique myself when I need a case sensitive join on data that is stored case insensitive.

Andy
http://www.sqlservercentral.com/columnists/awarren/

Andy
SQLAndy - My Blog!
Connect with me on LinkedIn
Follow me on Twitter
Greg Larsen
Greg Larsen
SSCarpal Tunnel
SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)

Group: General Forum Members
Points: 4433 Visits: 290
No I have not checked into the performance issues. I might be interesting to see if there are any issues here.

If I get the script I will make sure it gets posted.

Gregory Larsen, DBA

If you looking for SQL Server Examples check out my website at http://www.geocities.com/sqlserverexamples

Gregory A. Larsen, MVP
Kevin R. Zakoski
Kevin R. Zakoski
Valued Member
Valued Member (68 reputation)Valued Member (68 reputation)Valued Member (68 reputation)Valued Member (68 reputation)Valued Member (68 reputation)Valued Member (68 reputation)Valued Member (68 reputation)Valued Member (68 reputation)

Group: General Forum Members
Points: 68 Visits: 31
We've run into similar problems, but it may be more important to state that it also depends on where and how a #temp table is created in TempDB.

A 'Create Table #Temp' statement will default the tables collation to that of TempDB's. If you create the #temp table with a SELECT INTO #Temp from a DB with a different collation than TempDB, the #Temp table will have the collation of DB it was run from. The same holds true for regular tables you create outside your DB. When we ran into our problem, it wasn't apparent what exactly was occuring until we removed the '#' from the table name and created static tables which we could look at in more detail to resolve our problem.

My suggestion is, if you must use different collations on the same server, it's best to adopt a standard for creating #temp tables within your code. If you mix Creates and Select INTO for #Temp tables in your code you will eventually get burnt.



Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search