February 26, 2014 at 7:33 am
Hello everyone,
I have a SQL Server 2012 default instance setup in a virtual environment. We are performing an upgrade of a vendor’s application and we would like to move their database onto this server. When I asked the vendor if they supported SQL Server 2012, they said they do, but their database needs to be on its own instance. I questioned why and they said it is because their database uses a different collation than the default SQL Server collation. I am not 100% familiar with Server Collation vs. Database Collation, but to me this didn't make sense and I continued to ask why. I was thinking, “I thought the database collation option be different than the Server's Collation option?” They told me it was because their index maintenance tasks that they perform on their database will fail if the database collation is different than the server’s collation. Does this make sense? I do not have the code they use for their index maintenance so I am not sure 100% what they do. Could using the tempdb in their index maintenance be the reason the reason why Server collation might affect their index maintenance? Thank you.
February 26, 2014 at 9:28 am
If the server and database collation are different then anything created in tempdb will have the server collation which wont match the database collation. So you are right in think that their index maintenance probably uses tempdb.
here is a quick example
CREATE TABLE tbl(col1 varchar(10)COLLATE Latin1_General_BIN);
INSERT INTO tbl
VALUES('test');
CREATE TABLE #tbl(col1 varchar(10));
INSERT INTO #tbl
VALUES('test');
SELECT *
FROM tbl AS a
INNER JOIN #tbl AS b
ON a.col1 = b.col1;
DROP TABLE tbl;
DROP TABLE #tbl;
May 4, 2016 at 5:11 am
didnt understand examples
May 4, 2016 at 6:40 am
deepti.khatuja (5/4/2016)
didnt understand examples
His example gives you a way of seeing the type of issues you could have with differing collations.
If you don't understand enough of collation to understand the issue, then go here
https://msdn.microsoft.com/en-us/library/ms144260%28v=sql.105%29.aspx
OR
http://www.sqlservercentral.com/articles/Stairway+Series/72660/
May 4, 2016 at 8:01 am
deepti.khatuja (5/4/2016)
didnt understand examples
My example does make the assumption that server collation is not Latin1_general_BIN
you can check server collation with select SERVERPROPERTY('Collation') If this returns Latin1_general_BIN then change the collation after the collate clause to something different (ie SQL_Latin1_General_CP1_CI_AS).
when creating a table without specifying collation, the collation of character columns will be the collation of the database. Tempdb always has the same collation as server. So by specifying a collation for col1 in my permanent table that is not the server collation and then joining it to col1 in the temp table, a collation mismatch occurs
Msg 468, Level 16, State 9, Line 17
Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Latin1_General_BIN" in the equal to operation.
May 4, 2016 at 9:44 pm
To avoid problems with temp tables caused by server collation i always script them like this:
CREATE TABLE #tbl(
col1 varchar(10) COLLATE DATABASE_DEFAULT
);
_____________
Code for TallyGenerator
May 5, 2016 at 7:37 am
or use SELECT INTO:
SELECT <YourCols>
INTO #tbl
FROM <YourTables>
WHERE 1 = 0;
ALTER TABLE #tbl ADD PRIMARY KEY (<YourCol(s)>);
--etc
This has the advantage that datatype changes will also be picked up.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply