{"id":636,"date":"2006-05-30T14:31:00","date_gmt":"2006-05-30T14:31:00","guid":{"rendered":"\/blogs\/kimberly\/post\/Changing-Database-Collation-and-dealing-with-TempDB-Objects.aspx"},"modified":"2013-01-11T23:12:18","modified_gmt":"2013-01-12T07:12:18","slug":"changing-database-collation-and-dealing-with-tempdb-objects","status":"publish","type":"post","link":"https:\/\/www.sqlskills.com\/blogs\/kimberly\/changing-database-collation-and-dealing-with-tempdb-objects\/","title":{"rendered":"Changing Database Collation and dealing with TempDB Objects"},"content":{"rendered":"

\nOK, so… I don't know how many of you use different collations but if you do then you know that there are two truths:\n<\/p>\n

\n

\n\t1) They're very flexible
\n\t2) They can cause you a bit of grief (changing collations and tempdb)\n\t<\/p>\n<\/blockquote>\n

\nFlexibility<\/strong>\n<\/p>\n

\nAs of SQL Server 2000 (or heck, maybe it was 7.0?), database collations could be changed at installation OR set\/changed later. You can set the collation when a database is created (if not set, the database will use the server's default). You can set the collation when a table is created (if not set, the table will use the database's default). You can set the collation when a query is executed (which doesn't really make sense unless it's in a WHERE clause or ORDER by clause). And – you can set the collation in a view or stored procedure to do things like case sensitive searching – on the fly. However, neither of these will perform well over large results sets (at least not without indexes) so, I'd be careful of doing any adHoc changes to collations (even in views\/sps – without appropriate indexes)!\n<\/p>\n

\nAnyway, the key point is that they're very flexible. In many international databases\/localized databases, column collation differs by table (in order to do efficient sorting, etc.) and different language data may be separated (either with a column that described which language\/country code is used OR in different tables).\n<\/p>\n

\nGrief in Changing Database Collations<\/strong>\n<\/p>\n

\nActually, changing database collation is *very* simple. Literally, it only takes an ALTER DATABASE to do. For example, the following code runns flawlessly:\n<\/p>\n

<\/p>\n

\n

\n\tUSE master
\n\tgo\n\t<\/p>\n

\t<\/p>\n

\n\tDROP DATABASE TestCollation
\n\tgo\n\t<\/p>\n

\t<\/p>\n

\n\tCREATE DATABASE TestCollation
\n\tCOLLATE SQL_Latin1_General_CP1_CI_AS
\n\tgo\n\t<\/p>\n

\t<\/p>\n

\n\tsp_helpdb TestCollation
\n\tgo\n\t<\/p>\n

\t<\/p>\n

\n\tALTER DATABASE TestCollation
\n\tCOLLATE Latin1_General_CS_AS_KS_WS
\n\tgo\n\t<\/p>\n

\t<\/p>\n

\n\tsp_helpdb TestCollation
\n\tgo\n\t<\/p>\n<\/blockquote>\n

\nBUT… if you go from case sensitive to case insensitive… be careful! It is important to realize that ALL of your tables AND data will need to be checked against the new collation. In fact, changing database collation will not be allowed if the objects\/data would no longer adhere to your unique constraints, etc. Check out this more complete script (ChangingDatabaseCollation.sql (2.85 KB)<\/a>), if you want to see what happens.\n<\/p>\n

\nGrief with temporary objects<\/strong>\n<\/p>\n

\nSo.. the other area (and this seems to be the one where everyone has trouble), is with temporary objects. If you create a temp table and your database has a different collation other than TempDB (which has the same collation as the system – based on installation), then comparisons\/lookups\/joins – may have problems. A simple trick to get around this is to use database_default. Check out this sample and you'll see how it works:\n<\/p>\n

<\/p>\n

\n

\n\tCREATE DATABASE Test
\n\tCOLLATE Icelandic_BIN
\n\tgo\n\t<\/p>\n

\t<\/p>\n

\n\tUSE Test
\n\tgo\n\t<\/p>\n

\t<\/p>\n

\n\tCREATE TABLE #test1
\n\t(
\n\t   col1 varchar(12)
\n\t)
\n\tgo\n\t<\/p>\n

\t<\/p>\n

\n\tCREATE TABLE #test2
\n\t(
\n\t   col1 varchar(12) COLLATE database_default
\n\t)
\n\tgo\n\t<\/p>\n

\t<\/p>\n

\n\tUSE Tempdb
\n\tgo\n\t<\/p>\n

\t<\/p>\n

\n\tCREATE TABLE #test3
\n\t(
\n\t   col1 varchar(12) COLLATE database_default
\n\t)
\n\tgo\n\t<\/p>\n

\t<\/p>\n

\n\tsp_help 'tempdb..#test1' — Will use TempDB's collation
\n\texec sp_help 'tempdb..#test2' — Will use Test's collation (Icelandic BIN)
\n\texec sp_help 'tempdb..#test3' — Will use TempDB's collation
\n\tgo\n\t<\/p>\n<\/blockquote>\n

\nSo simple, so obvious… and, well – I just found out about that one?! I used to recommend that you explcitly <\/em>set the collation for every column. Now, that still works – but, it doesn't offer you any flexbility. So, you could get around that with dynamic string execution but that can also get very complicated, very quickly. So… database_default is a VERY simple and clean way of doing this.\n<\/p>\n

\nHave fun,
\nkt<\/p>\n","protected":false},"excerpt":{"rendered":"

OK, so… I don't know how many of you use different collations but if you do then you know that there are two truths: 1) They're very flexible 2) They can cause you a bit of grief (changing collations and tempdb) Flexibility As of SQL Server 2000 (or heck, maybe it was 7.0?), database collations […]<\/p>\n","protected":false},"author":7,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[65,75,78],"tags":[],"class_list":["post-636","post","type-post","status-publish","format-standard","hentry","category-sql-server-2005","category-tempdb","category-tips"],"_links":{"self":[{"href":"https:\/\/www.sqlskills.com\/blogs\/kimberly\/wp-json\/wp\/v2\/posts\/636","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.sqlskills.com\/blogs\/kimberly\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.sqlskills.com\/blogs\/kimberly\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/kimberly\/wp-json\/wp\/v2\/users\/7"}],"replies":[{"embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/kimberly\/wp-json\/wp\/v2\/comments?post=636"}],"version-history":[{"count":0,"href":"https:\/\/www.sqlskills.com\/blogs\/kimberly\/wp-json\/wp\/v2\/posts\/636\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.sqlskills.com\/blogs\/kimberly\/wp-json\/wp\/v2\/media?parent=636"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/kimberly\/wp-json\/wp\/v2\/categories?post=636"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/kimberly\/wp-json\/wp\/v2\/tags?post=636"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}