FIX: Incorrect behavior when you use memory-optimized tables with "where exists" statement in SQL Server 2016

Assume that you are using Microsoft SQL Server 2016. 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.

This issue is fixed in the following cumulative update for SQL Server:

       Cumulative Update 5 for SQL Server 2016 SP1

Microsoft has confirmed that this is a problem in the Microsoft products that are listed in the "Applies to" section.

Learn about the terminology that Microsoft uses to describe software updates.