Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 46
  • 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
Get quick recovery of individual SharePoint items

Free tool – Veeam Explorer for Microsoft SharePoint, enables fast, easy restores of SharePoint sites, documents, libraries and lists — all with no agents to manage and no additional licenses to buy.

 
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

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone 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