belle’s sql musings

Fixing Collation: The Experiments

Posted in Issues/Troubleshooting by belle on October 11, 2008

We’re trying to solve a few collation issues we have been having, and I had collation overload this week.

The Scenario
•    Some of our SQL Server instanced were installed using the default collation SQL_Latin1_General_CP1_CI_AS
•    We need to have case insensitive, accent insensitive – Latin1_General_CI_AI
•    We changed some of our database collations to Latin1_General_CI_AI

The Problems

•    We want to change the model database’s collation, so that all new databases get the collation we want
•    We want to change the tempdb collation, so that all temporary objects don’t have to use explicit collation

We use temporary tables in some of our important stored procedures and UDFs. It will be an issue if we have different collations between our user databases and tempdb. Yup, trying to do a query that involved these two tables will result in an error similar to:

Cannot resolve the collation conflict between “Latin1_General_CI_AI” and “SQL_Latin1_General_CP1_CI_AS” in the equal to operation. (Microsoft SQL Server, Error: 468)

Background Check (more…)

Advertisements

Operations That Heavily Stress Out TempDB/SQL Server

Posted in Issues/Troubleshooting by belle on July 23, 2008

Operations that heavily stress out tempdb

Excerpt:

These operations heavily use tempdb:
• Repeated create and drop of temporary tables (local or global). 
• Table variables that use tempdb for storage purposes.
• Work tables associated with CURSORS.
• Work tables associated with an ORDER BY clause.
• Work tables associated with an GROUP BY clause.
• Work files associated with HASH PLANS.

Source:

Concurrency Enhancements for the TempDB database
http://support.microsoft.com/default.aspx?scid=kb;en-us;328551

Tagged with: