Constant scan when querying an identity field over a linked server

When I perform a simple query over a linked server that is looking for a
specific identity value, SQL Server performs a constant scan instead of a
remote query. Running the same query locally gives expected results. Also,
forcing a data type conversion in the criteria gives expected results. And the problem only exists if the column is an identity field. For any other field, it works as expected.

Example problematic query:

Select MyID
From Server2.MyDB.dbo.MyTable
Where MyID = 1

0 records are returned, but a record with a MyID = 1 does exist.

The following works as expected:

Select MyID
From Server2.MyDB.dbo.MyTable
Where MyID Like 1

Select MyID
From Server2.MyDB.dbo.MyTable
Where Cast(MyID As varchar) = 1

Any ideas as to why this is happening and how to permanently prevent it?

Thanks!!

Work fine for me. Can you try updating the statistics of the table on the remote server.

Select ID
From MYCOMP.master.dbo.x
Where ID = 1

1 record. Remote Query

Select ID
From MYCOMP.master.dbo.x
Where ID Like 1

1 record. filter + remote query

Select ID
From MYCOMP.master.dbo.x
Where Cast(ID As varchar) = 1

1 record. filter + remote query

Table:

create table x (id int identity(1,1) primary key, name varchar(100), salary int)

Tried without PK and index. Same results.

Same results for me after updating the statistics. Both machines are SP3, by the way.


StmtText
---------------------------------------------------------------------------------
Select MyId
FROM Server2.MyDB.dbo.MyTable
Where MyId = 1


StmtText
-------------------
|--Constant Scan


StmtText
-----------------------------------------------------------------------------------------------

Select MyId
FROM Server2.MyDB.dbo.MyTable
Where MyId > 0 And MyId < 2


StmtText
-------------------
|--Constant Scan


StmtText
------------------------------------------------------------------------------------
Select MyId
FROM Server2.Server2.dbo.MyTable
Where MyId Like 1


StmtText
-------------------------------------------------------------------------------------------------------------------------------------
|--Filter(WHERE:(like(Convert([Server2].[MyDB].[dbo].[MyTable].[MyId]), '1', NULL)))
|--Remote Query(SOURCE:(Server2), QUERY:(SELECT Tbl1001."MyId" Col1003 FROM "Server2"."dbo"."MyTable" Tbl1001))

Any index on that table?


I am having sp3a on all boxes


Select ID
From MYCOMP.master.dbo.y
Where ID = 1

(1 row(s) affected)

StmtText
-------------------------------------------------------------------------------------------------------------------------------
|--Remote Query(SOURCE:(MYCOMP), QUERY:(SELECT Tbl1001."id" Col1003 FROM "master"."dbo"."y" Tbl1001 WHERE Tbl1001."id"=(1)))

Yes, 4 indexes. A PK index on the ID field in question. an index on the rowguid column used by replication, and 2 other indexes. I don't think it's anything that could be specific to this table. I've tried other tables and even other servers with the same results.

No matter which table I query, from which server, to which server, I get the same results.

Just for the heck of it, maybe you can try the query with "openquery" and see what happens.

It works correctly using OpenQuery.