Why SQL Server scans all rows when updating bit column even for primary key via linked server

Question

I am using simple update statement for specific primary key for SQL linked server as follow

UPDATE t
SET
    processed = 1,
    processed_on = GETDATE()
FROM [LINKED\SERVER].DATABASE.dbo.FileQueue t
WHERE t.FileId = '3b33eff6-fde1-4e8c-9c23-2dbd45f50222'

The both servers are SQL Server 2019. The table definition is

CREATE TABLE dbo.FileQueue
(
    FileId UNIQUEIDENTIFIER NOT NULL,
    Processed BIT NOT NULL,
    Processed_on DATETIME NULL
 CONSTRAINT PK_FileQueue PRIMARY KEY CLUSTERED 
 (
    FileId ASC
 )
)

The Processed column has bit type. The query is slow due to full table scan.

Why is this happening? When I remove the bit column from the statement, everything works fine as usual with reading and updating single remote row.

The Id column is the clustered primary key. I have a ton of tables with a similar key.

I tried with CONVERT or CAST function and the result is the same.

For query without bit column the execution plan is pretty well.

UPDATE t
SET
    --processed = 1,
    -- any other columns can be added to be updated except bit
    processed_on = GETDATE()
FROM [LINKED\SERVER].DATABASE.dbo.FileQueue t
WHERE t.FileId = 'ABD4442F-8560-43B5-8B04-000000B2A626'

Answer 1

Ideally, you would call a stored procedure on the remote server to make this modification, with the UUID as a parameter.

Meantime, try using a variable for the bit constant:

DECLARE @true bit = 1;

UPDATE t
SET processed = @true,
    processed_on = GETDATE()
FROM [LINKED\SERVER].DATABASE.dbo.Queue t
WHERE
    t.Id = CONVERT(uniqueidentifier, '3b33eff6-fde1-4e8c-9c23-2dbd45f50222');

Or, if your linked server is configured for RPC OUT, using EXECUTE...AT:

DECLARE 
    @id uniqueidentifier = {GUID '3b33eff6-fde1-4e8c-9c23-2dbd45f50222'},
    @processed bit = 1,
    @processed_on datetime = GETDATE();

EXECUTE 
(
    -- Parameterized statement
    N'
    UPDATE [DATABASE].dbo.Queue 
    SET processed = ?, 
        processed_on = ? 
    WHERE Id = ?',
    --- Parameter values
    @processed,
    @processed_on,
    @id
)
AT [LINKED\SERVER];

Plan captured at the remote server:

If you need it in a single local statement without RPC:

UPDATE t
SET processed = V.processed,
    processed_on = GETDATE()
FROM [LINKED\SERVER].DATABASE.dbo.Queue AS t
CROSS JOIN (VALUES(1)) AS V (processed)
WHERE
    T.Id = CONVERT(uniqueidentifier, '3b33eff6-fde1-4e8c-9c23-2dbd45f50222');

SQL Server uses a framework called Distributed Query (DQ) to convert your SQL to a form the remote server can understand. Some things in DQ work better than others. The bit data type has always a been a bit of an odd case.

It's a bug of course. If you feel like reporting it, you can do so via Microsoft Support in the usual way, or at https://aka.ms/sqlfeedback

Answer 2

In your query, SQL Server collects the whole remote table and then it applies locally the filter.

Use openquery in this way:

UPDATE OPENQUERY([LINKED\SERVER], 'select * from DATABASE.dbo.Queue t where WHERE t.Id = CONVERT(uniqueidentifier, ''3b33eff6-fde1-4e8c-9c23-2dbd45f50222'')')
SET
 processed = CAST(1 as bit),
 processed_on = GETDATE()

You can also use the execute statement in this way:

execute ('UPDATE t
SET
    processed = 1,
    processed_on = GETDATE()
FROM DATABASE.dbo.FileQueue t
WHERE t.FileId = ''3b33eff6-fde1-4e8c-9c23-2dbd45f50222''') at [LINKED\SERVER]

Ensure RPC Out is set to True on the Linked Server properties