Solved

Contained Database Collations

Posted on 2016-11-19
6
31 Views
Last Modified: 2016-11-24
hi experts

i am Reading about Contained Database Collations, but i do not understand
Contained Database Collations
and
Collations of Tempdb
0
Comment
Question by:enrique_aeo
  • 3
  • 2
6 Comments
 
LVL 8

Expert Comment

by:Jason clark
ID: 41895331
0
 
LVL 48

Expert Comment

by:Vitor Montalvão
ID: 41895495
When a Contained database is created will get by default the same collation the SQL Server instance has so when you copy/move the Contained database to another SQL Server instance with another default collation it will keep the original collation so you just need to be aware of this and be careful when working with temporary tables since the last ones will have the default collation from the actual SQL Server instance and you might run in collation errors.
0
 

Author Comment

by:enrique_aeo
ID: 41896576
can you give me an example in T-SQL?
0
Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

 
LVL 48

Expert Comment

by:Vitor Montalvão
ID: 41897092
I'm not sure what kind of example are you looking for. This is like working with another regular database that has a different collation from the default so you'll need to explicitly convert to the necessary collation. In this case guarantee that you create the temporary objects with the same collation as the Contained Database:
CREATE TABLE #TempTable 
(
    Id INT,
    Name nvarchar(100) COLLATE Latin1_General_CI_AI
);  

Open in new window

0
 

Author Comment

by:enrique_aeo
ID: 41897727
This problem is solved with: CATALOG_DEFAULT?
0
 
LVL 48

Accepted Solution

by:
Vitor Montalvão earned 500 total points
ID: 41898681
This problem is solved with: CATALOG_DEFAULT?
There are alternative ways to solve this. My example above is to use the same collation as the Contained Database.
If you want to use the SQL Server instance default collation then you'll need to do it in an opposite way. For example:
USE ContainedDB_name
GO

CREATE TABLE #TempTable 
(
    Id INT,
    Name nvarchar(100) 
);  

SELECT *
FROM MyTable
WHERE Name IN (SELECT Name COLLATE Default_Collation_Name
               FROM #TempTable);

Open in new window

0

Featured Post

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Audit has been really one of the more interesting, most useful, yet difficult to maintain topics in the history of SQL Server. In earlier versions of SQL people had very few options for auditing in SQL Server. It typically meant using SQL Trace …
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
This video shows how to quickly and easily add an email signature for all users on Exchange 2016. The resulting signature is applied on a server level by Exchange Online. The email signature template has been downloaded from: www.mail-signatures…

856 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question