Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 964
  • Last Modified:

how to call or update data through linked server

Hi,
I'm interested to know what other query that I can use to select or update data through a linkedserver, at of now I'm using below query through my linked server, but not so sure whether is good or bad way to select data

SELECT * FROM OPENQUERY(kiptox,'SELECT top 20000 * FROM northwind..Index_views_Custom')
0
motioneye
Asked:
motioneye
  • 3
  • 2
3 Solutions
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
there is nothing "wrong" with using openquery. good or bad... well, what are your alternatives? if you have no alternative, there is no "discussion" about it, only possibly if it is usable or not.
0
 
OtanaCommented:
You can also do it this way:

SELECT * FROM [SERVERNAME].[northwind].[Index_views_Custom]
0
 
motioneyeAuthor Commented:
Hi
Yes how about this one as per advised by Otana??

SELECT * FROM [SERVERNAME].[northwind].[Index_views_Custom]

as comparisons to use openquery with a normal select statement??
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
you need the 4 partname, then:
SELECT * FROM [SERVERNAME].[northwind]..[Index_views_Custom]
or

SELECT * FROM [SERVERNAME].[northwind].dbo.[Index_views_Custom]
0
 
motioneyeAuthor Commented:
Hi,
the reason why I asking what the benefit between two is bcoz when I anylze the query through estimated execution plan, a query with T-sql openquery performing better since the subtree cost are lower compare to the normal select.


Using openquery subtree cost =3.36333

StmtText                                                                                             EstimateCPU   AvgRowSize  TotalSubtreeCost
--------------------------------------------------------------------------------------------------   ------------- ----------- ----------------
SELECT * FROM OPENQUERY(ktopix,'SELECT top 20000 * FROM northwind..Index_views_Custom')              NULL          NULL        3.363333        
  |--Remote Scan(SOURCE:(Ktopix), OBJECT:(SELECT top 20000 * FROM northwind..Index_views_Custom))    3.363333      90          3.363333        

(2 row(s) affected)


another query with normal T-sql TotalSubtreeCost

StmtText                                                                                                                                                                                                          EstimateCPU   AvgRowSize  TotalSubtreeCost=6.676667
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- - ------------- ----------- ----------------

SELECT top 20000 * FROM [KYAHYA].[northwind].[DBO].[Index_views_Custom]                                                                                                                                           1 NULL        NULL      6.676667         N
  |--Remote Query(SOURCE:(KYAHYA), QUERY:(SELECT TOP 20000 "Tbl1002"."CustomID" "Col1004","Tbl1002"."CustomName" "Col1005","Tbl1002"."Phone" "Col1006" FROM "northwind"."DBO"."Index_views_Custom" "Tbl1002"))    6.676667      90          6.676667        

(2 row(s) affected)



any idea why it behave this such way?
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
I regulary see also that openquery performs better than the other code, and I guess this is because sql server will try to "pass-through" as much as possible to the remote server...
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now