Heterogeneous Query Internals

when executing a query joining a local table to a linked server's table, or when querying the linked server's table directly through a local session, does the engine "pass through" to the linked engine the query portion that can be addressed by that server?

for example. . .

select local.master, linked.detail
from mydb.dbo.mytable local inner join linked.yourdb.dbo.yourtable linked
on local.id = linked.id
where linked.lastname = 'smith'

is the engine smart enough pass the " linked.lastname = 'smith' " to the linked engine before doing the join?

or

select linked.*
from linked.yourdb.dbo.yourtable linked
where linked.lastname = 'smith'

does the engine pass the " linked.lastname = 'smith' " to the linked server for it to process?

any reference links would be helpful.

Blair,

Sometimes yes, sometimes no. You should be able to see the remote query by generating the estimated execution plan in SQL Server Management Studio, then hovering the mouse cursor over the remote query operator. The query processor certainly makes some attempt to remote the predicates, but for string equality, the processor can only do so if the remote server has a compatible collation. The query needs to evaluate = 'smith' according to the local collation, and the remote server may or may not be able to do so.

Steve Kass
Drew University
www.stevekass.com

HELP...Splitting a string in T-SQL

I have a situation where I am querying the master.dbo.sysaltfiles to return
the path to the datafiles. What I am really interested in is the path...not
the filenames.
Example:
select @.DataPath = FileName From master.dbo.sysaltfiles WHERE name =
@.CurrentDB
This returns: M:\Microsoft SQL Server\CurrentDB.mdf
What I need is just the path:
Example:
M:\Microsoft SQL Server\
I was looking for something like VB's split string or something to remove
the filename.
What can I do to achieve the desired result?
Thanks!
RonThere are plenty of string functions in T-SQL that will help with this.
Check out REVERSE, CHARINDEX and SUBSTRING.
"RSH" <way_beyond_oops@.yahoo.com> wrote in message
news:%23Yp4navNGHA.2884@.TK2MSFTNGP12.phx.gbl...
> I have a situation where I am querying the master.dbo.sysaltfiles to
> return the path to the datafiles. What I am really interested in is the
> path...not the filenames.
> Example:
> select @.DataPath = FileName From master.dbo.sysaltfiles WHERE name =
> @.CurrentDB
> This returns: M:\Microsoft SQL Server\CurrentDB.mdf
>
> What I need is just the path:
> Example:
> M:\Microsoft SQL Server\
> I was looking for something like VB's split string or something to remove
> the filename.
> What can I do to achieve the desired result?
>
> Thanks!
> Ron
>|||Excellent. If possible could you give me a sample of how to use them to
achieve what I'm going for?
Thanks,
Ron
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:OY9JLgvNGHA.3984@.TK2MSFTNGP14.phx.gbl...
> There are plenty of string functions in T-SQL that will help with this.
> Check out REVERSE, CHARINDEX and SUBSTRING.
>
> "RSH" <way_beyond_oops@.yahoo.com> wrote in message
> news:%23Yp4navNGHA.2884@.TK2MSFTNGP12.phx.gbl...
>|||Here is an example
declare @.c varchar(50)
select @.c ='M:\Microsoft SQL Server\CurrentDB.mdf'
select left(@.c,(len(@.C) -CHARINDEX('',reverse(@.c)))+1)
Just replace @.c with your field name
http://sqlservercode.blogspot.com/|||Thank you so much!
I'm under the gun so I needed to "learn" quickly. I appreciate your help!
Ron
"SQL" <denis.gobo@.gmail.com> wrote in message
news:1140535404.793871.132130@.g47g2000cwa.googlegroups.com...
> Here is an example
> declare @.c varchar(50)
> select @.c ='M:\Microsoft SQL Server\CurrentDB.mdf'
> select left(@.c,(len(@.C) -CHARINDEX('',reverse(@.c)))+1)
> Just replace @.c with your field name
> http://sqlservercode.blogspot.com/
>|||Hi There,
I hope this help to solve the problem.
Select identity(int,1,1) Seq into Seq From sysobjects
Select * from seq
Select Substring(String,1,Max(seq)) From
(
Select * From Seq ,(
Select 'c:\aa\bb\cc\dd\ee' As String) S
Where substring(String,seq,1) = '\'
) SS Group By String
Drop Table Seq
With Warm regards
Jatinder Singh