• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 49
  • Last Modified:

Contained Database Collations

hi experts

i am Reading about Contained Database Collations, but i do not understand
Contained Database Collations
and
Collations of Tempdb
0
enrique_aeo
Asked:
enrique_aeo
  • 3
  • 2
1 Solution
 
Jason clarkDBA FreelancerCommented:
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
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
 
enrique_aeoAuthor Commented:
can you give me an example in T-SQL?
0
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

 
Vitor MontalvãoMSSQL Senior EngineerCommented:
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
 
enrique_aeoAuthor Commented:
This problem is solved with: CATALOG_DEFAULT?
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now