filtering (where condition) against a uniqueidentifier or bit columns cannot
be applied on the remote server. Instead, all rows in the remote server's
table will be brought across the wire, and the filter condition will be
applied locally.
This can be demonstrated by declaring a view
CREATE VIEW ActionItems AS SELECT * FROM
RemoteServer.dbo.Database.ActionItems
that queries the table
CREATE TABLE [ActionItems1] (
[ActionItemID] [uniqueidentifier] NOT NULL CONSTRAINT
[DF_ActionItems1_ActionItemID] DEFAULT (newid()),
[AssociatedEmployeeID] [uniqueidentifier] NULL ,
[AssociatedActionItemID] [uniqueidentifier] NOT NULL , --clustered
[ParentActionItemID] [uniqueidentifier] NULL ,
CONSTRAINT [PK_ActionItems1_1] PRIMARY KEY NONCLUSTERED
(
[ActionItemID]
)
)
Now, i will try to filter by a uniqueidentifier:
SELECT ActionItemID FROM ActionItems
WHERE ActionItemID = '28E5D33A-8946-48A6-BEFC-A0C07914D440'
StmtText
------
|--Filter(WHERE
[Jango].[CasinoHR].[dbo].[ActionItems1].[ActionItemID]=28E5D33A-8946-48A6-BEFC-A0C07914D440))|--Remote Query(SOURCE
Jango), QUERYSELECT Tbl1001."ActionItemID"Col1003 FROM "CasinoHR"."dbo"."ActionItems1" Tbl1001))
And as expected and documented, all rows are returned, then a filter is
applied to get the one row.
But wait! Now i will try to filter by a uniqueidentifier:
SELECT ActionItemID FROM ActionItems
WHERE AssociatedEmployeeID = '4C5CD857-8DD4-4279-B017-7CF1C49B23D9'
StmtText
--------
|--Remote Query(SOURCE
Jango), QUERYSELECT Tbl1001."ActionItemID"Col1004 FROM "CasinoHR"."dbo"."ActionItems1" Tbl1001 WHERE
Tbl1001."AssociatedEmployeeID"=?))
And not as expected, and opposite as documented, only the matching rows are
returned - and the filter is applied on the remote server.
But wait! Now i will try to filter by a uniqueidentifer:
SELECT ActionItemID FROM ActionItems
WHERE AssociatedEmployeeID is not null
StmtText
-------
|--Filter(WHERE
[Jango].[CasinoHR].[dbo].[ActionItems1].[AssociatedEmployeeID]<>NULL))|--Remote Query(SOURCE
Jango), QUERYSELECTTbl1001."AssociatedEmployeeID" Col1005,Tbl1001."ActionItemID" Col1003 FROM
"CasinoHR"."dbo"."ActionItems1" Tbl1001))
And as expected and documented, all rows are returned, then a filter is
applied to get the one row.
But wait! Now i will try to filter by a uniqueidentifier:
SELECT ActionItemID FROM ActionItems
WHERE AssociatedActionItemID = 'C8A32A8F-2AF8-4B4A-9954-27274F4F6B01'
StmtText
-------
|--Remote Query(SOURCE
Jango), QUERYSELECT Tbl1001."ActionItemID"Col1004 FROM "CasinoHR"."dbo"."ActionItems1" Tbl1001 WHERE
Tbl1001."AssociatedActionItemID"=?))
And not as expected, and opposite as documented, only the matching rows are
returned - and the filter is applied on the remote server.
So what's up? Either SQL Server can pass a filter condition on a
uniqueidentifier field to the remote server, or it cannot. Which is it? The
documentation says it cannot. The documentation does not say that it cannot
except for sometimes - who's logic isn't apparent or logical.
Oops, DPW should be DPV
Distributed Partitioned Views
sql
