0

I installed the SQL Server 2019 instance for our ERP with the default French_CI_AS collation.

The ERP uses Latin1_general_BIN and doesn't seem to have any problems. I only realised the potential issue when writing a query with an explicit temporary table.

Would the mismatch have any impact on queries which perform a table spool?

1

1 Answer 1

2

I believe the more likely potential problem you could run into would be a collation mismatch error. That would occur if the ERP software executes a query that does something like a JOIN or concatenation of columns between a string column in a temp table and a string column in a table in the app database (assuming that they query predicate or expression isn't using an explicit COLLATE option, or if the temp table wasn't created specifying a collation for string columns).

Example Setup

SET ANSI_NULLS, QUOTED_IDENTIFIER ON;

IF(DB_ID(N'_BIN_Test') IS NULL)
BEGIN
  PRINT 'Creating test DB...';
  CREATE DATABASE _BIN_Test COLLATE Latin1_General_BIN;
END;
GO

USE [_BIN_Test];
GO

-- DROP TABLE #Test_Temp;
CREATE TABLE #Test_Temp
(
  [ID] INT NOT NULL IDENTITY(1, 1) PRIMARY KEY,
  [Col1] VARCHAR(128) NOT NULL INDEX [ix_#Test_Temp_Col1]
);
INSERT INTO #Test_Temp ([Col1])
  SELECT [name]
  FROM   master.sys.columns;
GO

-- DROP TABLE dbo.Test_Local;
CREATE TABLE dbo.Test_Local
(
  [ID] INT NOT NULL IDENTITY(1, 1) PRIMARY KEY,
  [Col1] VARCHAR(128) NOT NULL INDEX [ix_Test_Local_Col1]
)
INSERT INTO dbo.Test_Local ([Col1])
  SELECT [name]
  FROM   master.sys.columns;
GO

TEST 1: Collation mismatch error

SELECT TOP (100) *
FROM   dbo.Test_Local tl
INNER JOIN #Test_Temp tt
        ON tt.[Col1] = tl.[Col1];
/*
Msg 468, Level 16, State 9, Line XXXX
Cannot resolve the collation conflict between "Latin1_General_BIN"
and "SQL_Latin1_General_CP1_CI_AS" in the equal to operation.
*/

A performance problem would only potentially occur if the ERP software creates an index on a VARCHAR column (that uses a SQL Server collation -- name starting with SQL_) in a temp table and then does a filter (e.g. WHERE predicate) or string expression involving an NVARCHAR string literal or variable, as that could cause an implicit conversion of the string column in temp table.

TEST 2: Implicit Conversion prevents Index Seek

SELECT [Col1]
FROM   #Test_Temp
WHERE  [Col1] = 'name'; -- VARCHAR literal -> Index Seek

SELECT [Col1]
FROM   #Test_Temp
WHERE  [Col1] = N'name'; -- NVARCHAR literal -> Index Scan

For a more detailed look at this "implicit conversion" performance problem, please see my blog post: Impact on Indexes When Mixing VARCHAR and NVARCHAR Types


It's also possible that there could be some unexpected behavior if string literals and/or variables are being compared to string columns in any temp tables. In this particular case, with the application DB using a binary collation, and the high likelihood of string columns in the tables within the application DB inheriting that binary collation, I think it's safe to assume that there would be an expectation that filtering on string literals and variables will be handled in a binary fashion (i.e. case-sensitive, practically speaking), whether the table is local to the app DB or a temp table. However, by default, string columns in temp tables inherit the collation of tempdb, which in this scenario is case-insensitive.

TEST 3: Unexpected behavior

SELECT tl.[Col1]
FROM   dbo.Test_Local tl
WHERE  tl.[Col1] = 'GUID'
-- 0 rows (expected due to binary collation)

SELECT tt.[Col1]
FROM   #Test_Temp tt
WHERE  tt.[Col1] = 'GUID'
-- 2 rows (UNexpected due to binary collation of App DB)

This is not an issue for table variables as variables are local to the DB that is active when they are created.

For a pedantic look at why binary collations are not actually case-sensitive, please see my blog post: No, Binary Collations are not Case-Sensitive


All of the above being said, even if the application is doing any of these three things with temp tables, it's possible that the software has avoided the noted issues by not relying on the default collation of tempdb. It's possible that the developers of the software were aware of the issue and forced the desired collation (using the COLLATE keyword followed by DATABASE_DEFAULT or Latin1_General_BIN), either when creating temp tables, or in any queries involving any of those three scenarios.

More Setup:

-- DROP TABLE #Test_TempDD;
CREATE TABLE #Test_TempDD
(
  [ID] INT NOT NULL IDENTITY(1, 1) PRIMARY KEY,
  [Col1] VARCHAR(128) COLLATE DATABASE_DEFAULT -- OR: Latin1_General_BIN
            NOT NULL INDEX [ix_#Test_TempDD_Col1]
);
INSERT INTO #Test_TempDD ([Col1])
  SELECT [name]
  FROM   master.sys.columns;
GO

TEST 4: Explicit column collation

-- Test 1 query:
SELECT TOP (100) *
FROM   dbo.Test_Local tl
INNER JOIN #Test_TempDD tt
        ON tt.[Col1] = tl.[Col1];
-- no error


-- Test 2 query:
SELECT [Col1]
FROM   #Test_TempDD
WHERE  [Col1] = N'name';
-- Index Seek

TEST 5: Explicit predicate collation

-- Test 1 query:
SELECT TOP (100) *
FROM   dbo.Test_Local tl
INNER JOIN #Test_Temp tt
        ON tt.[Col1] = tl.[Col1] COLLATE Latin1_General_BIN;
-- no error


-- Test 3 query:
SELECT tt.[Col1]
FROM   #Test_Temp tt
WHERE  tt.[Col1] = 'GUID' COLLATE Latin1_General_BIN
-- 0 rows

Please note that the performance issue / index scan (shown in "Test 2") cannot be fixed by using the COLLATE keyword in a query. This is due to the problem being the order of the data within the index (which is set before the query executes and isn't going to change).


I'm guessing that most likely you will be fine, assuming that the ERP software did not explicitly state in their documentation or installation process that you must install into an instance using the Latin1_General_BIN collation. My assumption is based on the expectation that, without such a warning / requirement, most of the ERP software's customers are probably installing that software into instances using a non-Latin1_General_BIN collation and would have certainly encountered this problem before. You can even look to see if that software has an associated help / FAQ page or customer / community forum where such a question may have already been at least asked, and if so, hopefully answered.

6
  • 1
    Hmm I think OP is at greater risk for collation issues than your last paragraph portrays, IMO (having worked with a similar situation). If the ERP system expects Latin1_general_BIN, a case sensitive collation, and OP installed it to a case insensitive collation, there might be keys and unique indexes on columns that are liable to user entry. An end user may enter an Invoice # for example as both 123A and 123a which is valid in the ERP system by design, but would fail in OP's case insensitive database instance by violating a unique constraint.
    – J.D.
    Commented Jul 10 at 13:09
  • 1
    @J.D. and Solomon, the ERP supplier has no information on the problem and is perhaps unaware of its existence (otherwise they might have told me which collation to use for the installation). They were responsible for creating their database and so they have no collation problem internally. They access the database through a translation layer and seem unikely to use tempdb intentionally. One module has been working correctly for 3 years and the full system for 6 months.
    – grahamj42
    Commented Jul 10 at 15:22
  • 1
    @grahamj42 I may have misinterpreted your original post, and took it to mean that the database that the ERP system uses was installed to a different, case insensitive collation, despite being designed for a case sensitive one. Rather, it sounds like you mean the ERP's database system installed to the proper collation, and only tempdb differs. In which case, Solomon's feedback is probably pretty fair. Much lower risk for issue in that case, though there may be the occasional one, should their code ever use tempdb.
    – J.D.
    Commented Jul 10 at 15:27
  • 1
    @J.D. Yes, I assumed that the vendor DB is Latin1_General_BIN and tempdb is French_CI_AS based on the second paragraph of the question stating that the ERP DB uses Latin1_General_BIN and that there's a potential issue with tempdb. However, misinterpretation or not, your (first) comment made me think of a potential behavioral issue. I will update my answer in the morning with that info. Thanks! Commented Jul 11 at 6:43
  • 1
    @SolomonRutzky Looks good! You have my upvote, cheers! 🙂
    – J.D.
    Commented yesterday

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.