Menu
How can I check what the collation of a temporary table is?
I want to do this because I want to check what happens if I have database with a specific collation, different from the SQL Server instance and create a temporary table.
Let's say we have this scenario:
SQL Server 2008 - Latin1_General_CS_AS
Test Database - Estonian_CS_AS
Then create table #Test without specifying the collation. Which will be the collation of the table?
I think Estonian_CS_AS, but in the test I am doing is said Latin1_General_CS_AS. That's why I need to find a SQL statement to check this.
Note: from what I have read, I think that the collation of a temporary objects is defined by the tempdb collation. But if this is true, what defines its kind?
836
TestData as t LEFT JOIN #TempTable as tt ON t. ProductCode = tt. ProductCode COLLATE DATABASE_DEFAULT; DROP TABLE IF EXISTS #TempTable; CREATE TABLE #TempTable ( ProductCode VARCHAR(5) NOT NULL, ProductDesc VARCHAR(200) NULL ); INSERT INTO #TempTable ( ProductCode,ProductDesc) SELECT ProductCode,ProductDesc FROM dbo.
The SQL_Latin1_General_CP1_CI_AS collation is a SQL collation and the rules around sorting data for unicode and non-unicode data are different. The Latin1_General_CI_AS collation is a Windows collation and the rules around sorting unicode and non-unicode data are the same.
Default server-level collation is SQL_Latin1_General_CP1_CI_AS. If you are migrating databases from SQL Server to Managed Instance, check the server collation in the source SQL Server using SERVERPROPERTY(N'Collation') function and create a Managed Instance that matches the collation of your SQL Server.
The collate clause is used for case sensitive and case insensitive searches in the columns of the SQL server. There are two types of collate clause present: SQL_Latin1_General_CP1_CS_AS for case sensitive. SQL_Latin1_General_CP1_CI_AS for case insensitive.
tempdb is recreated when the server start and gets the collation from the model database.
Temp tables you create without specifying the collation will have the collation of tempdb. When creating temp table you can use database_default as collation to get the same collation as the current database.
131
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
