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:
Cumulative Update 1 for SQL Server 2017
Cumulative Update 5 for SQL Server 2016 SP1
About cumulative updates for SQL Server:
Each new cumulative update for SQL Server contains all the hotfixes and all the security fixes that were included with the previous cumulative update. Check out the latest cumulative updates for SQL Server:
Latest cumulative update for SQL Server 2017
Latest cumulative updates for SQL Server 2016
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.