The following query is part of a stored procedure that I currently have on a server instance B of SQL Server 2008. My data resides on Server instance A of SQL Server 2008 and B connects to A via a linked server called DataServer.
I modfied it in a way that eliminates OPENQUERY and also adjusts Index placement to as follows-
My questions are-
Q1. Which of the following is a good practise method, considering inserted data can be in the order of 50000+ in most cases
Q2. What is the specific significance of OPENQUERY thais is used here?
asked Sep 05, 2014 at 03:35 PM in Default
The question has been closed Mar 27, 2015 at 09:29 AM by nidheesh.r.pillai for the following reason:
The question is answered, right answer was accepted
Where you say "...Q1. Which of the following is a good practise..." do you mean "...which of the above is the best method..."? Without the benefit of being able to test, or know what your security setup is, I like the second method better. If you don't need dynamic SQL there's probably not much point in using it. Incidentally we're in the process of removing hard-coded linked server syntax from all of our code and replacing it with synonyms. The advantage of this is that when our friendly DBA comes to us one day and says "we're moving the db to a different server" it's not a disaster of broken code. We simply change the synonym and all the code can stay the same.
answered Sep 05, 2014 at 07:28 PM