Use the INTERSECT operator in a case-sensitive manner [duplicate]

Question

This question already has answers here:

Case sensitive search in WHERE clause (13 answers)

Check case-sensitivity (6 answers)

Write a case sensitive WHERE CLAUSE (1 answer)

Closed 6 months ago.

I have a number of system-versioned tables in my database. Microsoft notes here that...

When you run any data modification queries on a temporal table, the Database Engine adds a row to the history table, even if no column values change.

To avoid unnecessarily creating a row in the history table, I use the INTERSECT operator to confirm that data has actually changed to a table row prior to running an UPDATE command. The query looks something like this:

 UPDATE [TemporalTable]
 SET [Col1] = b.[Col1]...
 ,[Coln] = b.[Coln]
 FROM [TemporalTable] a
 JOIN [UpdateTable] b
 ON a.[Id] = b.[Id]
 WHERE NOT EXISTS (SELECT a.[Col1]...
 ,a.[Coln]
 INTERSECT
 SELECT b.[Col1]...
 ,b.[Coln])

The problem is that the INTERSECT operator is case-insensitive, but I need it to be case-sensitive. For example, if a column of data was "hello there" and it is being changed to "Hello there", the temporal table currently remains unchanged. I'd like it to update to reflect the change in case.

I did a little research and found that the collation of the data might be the place to start. This was a helpful article. However, knowing so little about this, I was wondering how this should be used to achieve my intended goal? Should I change the default collation of my entire database? Or the default collation of specific tables/columns? Or should I use the COLLATE clause in each query to change the data each time I use the INTERSECT operator?

I don't want to cause unintended effects by making permanent changes to my database or table columns; however, I wasn't sure how performant it would be to run the COLLATE clause each time I use this method for updating my system-versioned tables (which is often).

Any suggestions are appreciated. Thank you in advance.

• Thom A: "The problem is that the INTERSECT operator is case-insensitive" No it's not, it's "Collation sensitive": db<>fiddle
• Dale K: You have to decide where is the best place to impose case sensitivity, whether column or query comes down to your requirements.
• siggemannen: Upvoted for creative use of INTERSECT operator!
• Dale K: As with all database design decisions, design your database to meet your requirements, then address performance if its an issue. But we can't decide whats best for you.
• Charlieface: Better idea is to change the collation of the column, if you always want to differentiate that way. Only use COLLATE in the query if it's a one-off.

Answer 1

I would suggest using COLLATE directly in query, to prevent modifying structures, which might affect other processed accessing the DB at that time.

So, I have prepared sample script to present how you could use COLLATE inline in query

CREATE TABLE #t1 (
 [txt] NVARCHAR(100)
)
CREATE TABLE #t2 (
 [txt] NVARCHAR(100)
)
INSERT INTO #t1 VALUES ('hello world')
INSERT INTO #t2 VALUES ('HELLO WORLD')
-- Here I used sample CS (case sensitive) collation
-- output is empty
SELECT txt COLLATE Latin1_General_CS_AS 
FROM #t1
INTERSECT
SELECT txt
FROM #t2

• siggemannen: It's interesting this works, i thought both sides needed collation, otherwise one would get a mismatching collation error?
• Thom A: No, if one is expression is explicitly collated, then it is applied to any expressions are used against it, @siggemannen.
• siggemannen: @ThomA i didn't know that, thanks!