I have a table with a VARCHAR column and an index on it. Whenever a SELECT COUNT(*) is done on this table that has a check for COLUMN = N'' OR COLUMN IS NULL it returns double the number of rows. SELECT * with the same where clause will return the correct number of records.
After reading this article: https://sqlquantumleap.com/2017/07/10/impact-on-indexes-when-mixing-varchar-and-nvarchar-types/ and doing some testing I believe the collation of the column and the implicit conversion isn't the fault (at least not directly). The collation of the column is Latin1_General_CI_AS.
The database is on SQL Server 2012, and I've tested on 2016 as well.
I've created a test script (below) that will demonstrate this problem. In doing so, I believe that it may be related to data paging, as it needed a bit of data in the table for it to occur.
CREATE TABLE [dbo].TEMP
(
ID [varchar](50) COLLATE Latin1_General_CI_AS NOT NULL,
[DATA] [varchar](200) COLLATE Latin1_General_CI_AS NULL,
[TESTCOLUMN] [varchar](50) COLLATE Latin1_General_CI_AS NULL,
CONSTRAINT [PK_TEMP] PRIMARY KEY CLUSTERED ([ID] ASC)
)
GO
CREATE NONCLUSTERED INDEX [I_TEMP_TESTCOLUMN] ON dbo.TEMP (TESTCOLUMN ASC)
GO
DECLARE @ROWS AS INT = 40;
WITH NUMBERS (NUM) AS
(
SELECT 1 AS NUM
UNION ALL
SELECT NUM + 1 FROM NUMBERS WHERE NUM < @ROWS
)
INSERT INTO TEMP (ID, DATA)
SELECT NUM, '1234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901324561234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890'
FROM NUMBERS
SELECT @ROWS AS EXPECTED, COUNT(*) AS ACTUALROWS
FROM TEMP
GO
SELECT COUNT(*) AS INVALIDINDEXSEARCHCOUNT
FROM TEMP
WHERE (TESTCOLUMN = N'' OR TESTCOLUMN IS NULL)
GO
DROP TABLE TEMP
I'm able to modify the database to some extent (I won't be able to change data, or change the column from allowing NULL), unfortunately I am not able to modify the code doing the search, can anyone identify a way to get the correct COUNT(*) results returned?