Symptoms
Assume that you are using Microsoft SQL Server 2016 or 2017. When you process memory-optimized data transactions by using memory-optimized table variables with the where exists statement, you may get a wrong result.
For example:
Step1: Create a memory-optimized database and table.
Create DATABASE demo
ALTER DATABASE demo ADD FILEGROUP demo_mod CONTAINS MEMORY_OPTIMIZED_DATA
ALTER DATABASE demo ADD FILE (name='demo_mod1', filename='C:\DATA\demo_mod1') TO FILEGROUP demo_mod
Use Demo
Go
CREATE TYPE dbo.IN_MEMORY_TABLE_TYPE AS TABLE
( source_col INT NULL,
target_col INT not NULL
INDEX ix_InMemoryTable NONCLUSTERED (target_col)
) WITH (MEMORY_OPTIMIZED = ON)
GO
Step2: Insert data and update data.
DECLARE @t dbo.IN_MEMORY_TABLE_TYPE
INSERT @t ( source_col, target_col ) VALUES (10, 0),(0, 0)
select * from @t
UPDATE r1 SET target_col = -1 FROM @t r1
WHERE EXISTS ( SELECT * FROM @t r2 WHERE r2.source_col > 0 )
SELECT * FROM @t
GO
Step3: Check results.
Actual Results: Not all rows of table variable @t get updated.
source_col | target_col
----------------------
10 | -1
0 | 0
The expected results: All rows should get updated to have target_col = -1.
source_col | target_col
----------------------
10 | -1
0 | -1.
Resolution
This issue is fixed in the following cumulative updates for SQL Server:
Status
Microsoft has confirmed that this is a problem in the Microsoft products that are listed in the "Applies to" section.
References
Learn about the terminology that Microsoft uses to describe software updates.