SQL Query Where Column = '' returning Emoji characters 🎃 and 🍰

Question

Ok so I have a table with three columns:

Id, Key, Value

I would like to delete all rows where Value is empty (''). Therefore I wrote the query to select before I delete which was:

Select * from [Imaging.ImageTag] where [Value] = ''

all pretty standard so far...

Now heres the strange part. This query returned two rows shown below with commas seperating columns:

CE7C367C-5C4A-4531-9C8C-8F2A26B1B980,   ObjectType, 🎃 
F5B2F8A8-C4A8-4799-8824-E5FFEEDAB887,   Caption,    🍰

Why are these two rows matching on ''?

Extra Info

I am using Sql-Server, The [Value] column is of type NVARCHAR(300) and yes the table name really is [Imaging.ImageTag]

Answer 1

This is collation dependant.

Matches empty string

SELECT 1 where N'' = N'🍰'  COLLATE latin1_general_ci_as

Doesn't match empty string

SELECT 1 WHERE N'' = N'🍰'   COLLATE latin1_general_100_ci_as

The 100 collations are more up-to-date (though still not bleeding edge, they have been available since 2008) and you should use more modern collations unless you have some specific reason not to. The BOL entry for 100 collations specifically calls out

Weighting has been added to previously non-weighted characters that would have compared equally.

Answer 2

It's not an answer to your "why", but in terms of your overall goal, perhaps you should alter your strategy for searching for empty values:

Select * from [Imaging.ImageTag] where LEN([Value]) = 0

As per the comments (thanks Martin Smith for providing some copy/pastable emoji):

SELECT CASE WHEN N'' = N'🍰' then 1 else 0 end --returns 1, no good for checking

SELECT LEN(N'🍰') --returns 2, can be used to check for zero length values?