April 7, 2008 at 10:56 am
This issue has been discussed in this forum before in the context of SQL 2000:
http://www.sqlservercentral.com/Forums/Topic347922-5-1.aspx
Anyone want to add anything in terms of a SQL-2005 implementation?
I'm supposed to evaluate benefits/drawbacks of each in our environment.
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
April 7, 2008 at 11:14 am
So far as I know, the difference is that OpenQuery will only connect to linked servers, while OpenRowset can connect to anything that ODBC can read, whether it's linked or not. I don't think there's a significant performance difference, but I haven't tested them enough to be certain on that.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
April 7, 2008 at 11:26 am
OPENQUERY and OPENROWSET will likely give you the same performance. 4 part names with linked servers will be far more interesting when you do a join. If you do the join on the SQL server using 4 part names, the ODBC driver and SQL server have to do the join meaning you need to retrieve lots of unindexed data and join it together. In an OPENQUERY and OPENROWSET operation, if you do the join the the OPENQUERY statement, the originating system does the join before sending the data set to you.
So, the 4 part name will be very different depending on the situation.
I would tend to avoid sonmething that requires you to constantly hard-code a username and password into a lot of queries. What happens when the password changes? I would also try to avoid something that ends up with lots of T-SQL statements that are not checked by the parsing engine entil run time (all of the SQL you have in red in the OPENROWSET and OPENQUERY statements). However, the 4 part names can be an issue with joins.
If you are trying to build something that is dealing with compatibility with your old systems and conneting up data that you have yet to migrate to a new system, I would try to stick to linked server and 4 part names where you do not have major performance problems. Where you have performance problems, I would try to deal with it using VIEWS or procedures in the old system.
If you are trying to build something all new and find youself asking this question, I would try to build a system that never goes near a linked server OPENQUERY or OPENROWSET.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply