-
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))
Last edited by Rawhide; 12-02-2004 at 01:10 PM.
-
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(SOURCEMYCOMP), QUERYSELECT 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.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
|