matt32 (3/30/2010)
the OPENROWSET within Eli Leibas SP to create a view from the output of another SP:-)http://www.sqlservercentral.com/articles/T-SQL/68233/
this is cool and i use it often
Let's take a look:
1. The article uses 'allow updates' - which does not even function in 2005 onward
2. Ad Hoc Distributed Queries is required to be ON - this is a security vulnerability and not recommended
4. The view created contains SELECT * - so can never use SCHEMABINDING
5. The view-creating procedure is named sp_
6. The SERVER=. syntax only works for a default instance
7. Object names are SYSNAME = NVARCHAR(128), not VARCHAR(80)
OPENROWSET-specific problems:
Since it is implemented in the query plan as a Remote Scan operator:
1. Fixed cardinality guess of 10,000 rows
2. No statistics are available to the optimizer
3. Destroys the ability of a VIEW to be expanded into the calling statement's execution plan
4. Subject to all the limitations of executing a procedure on a remote, linked server
...I could go on. Still think it is cool?
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi