What factors influence choice between executing remote query using linkedserver via sp_executesql vs using 3 piece identifier

Question

I'm writing a stored procedure that should insert into a local table values from a remote query.

The remote server is setup as a linked sever.

I have two options:

Option 1 is:

INSERT INTO LOCALTABLENAME
SELECT ...
FROM LINKEDSVRNAME.DBNAME.DBO.TBLBAME1
JOIN LINKEDSVRNAME.DBNAME.DBO.TBLBAME2
ON ..
WHERE ...

Option 2 is:

INSERT INTO LOCALTABLENAME
LINKEDSERVER.MASTER.DBO.SP_EXECUTESQL 'SELECT ...
 FROM DBNAME.DBO.TBLBAME1
 JOIN DBNAME.DBO.TBLBAME2
 ON ..
 WHERE ...
'

On what basis do I make this choice.

Option 3 is:

INSERT INTO LOCALTABLE
EXEC (
'SELECT ...
 FROM DBNAME.DBO.TBLBAME1
 JOIN DBNAME.DBO.TBLBAME2
 ON ..
 WHERE ...'
) AT [LINKEDSVRNAME];

Option 4 is: OPENQUERY (but this has a limitation of 8K characters in the query).

One of my observations is that - Options 2, 3, 4 seem to guarantee that the entire query will be executed on the target server (the linked server). Whereas option 1 could result in each table getting brought across to the other server, and then joined/filtered.

Answer 1

One of my observations is that - Options 2, 3, 4 seem to guarantee that the entire query will be executed on the target server (the linked server). Whereas option 1 could result in each table getting brought across to the other server, and then joined/filtered.

Due to this very reason, you can (using Option 2,3,4) get IDENT_CURRENT value on the linked server.In Option 1 you wont get IDENT_CURRENT because it exeute at local end.

Another and most important factor is PERFORMANCE.Executing query on remote require more Network I/O.

i) First your server will Open connection for remote Server.

ii) Query will be executed on Remote server.

iii) Data return to your local server

iv) Connection close

Linked Server is not as efficient as Web service for moving data to and fro.

You should mention your reqirement and amount of data involve in this operation