Curiouse View to create for SQLServer 2005

Question

Curiouse View to create for SQLServer 2005

Viewing 3 posts - 16 through 17 (of 17 total)

You must be logged in to reply to this topic. Login to reply

Paul White SSC Guru Points: 150467 More actions · Answer 1

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

matt32 SSC Veteran Points: 264 More actions · Answer 2

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?

Hi Paul,

for my needs it is a great opportunity to create a view from this sql

WITH mycte AS (SELECT cast ('2007-01-01' AS DATETIME) DateValue UNION ALL SELECT DateValue + 1 FROM mycte WHERE DateValue +

1 < getdate())

SELECT distinct YEAR(DateValue) MyYear,MONTH(DateValue) MyMonth

from mycte OPTION ( MAXRECURSION 0 )

and you shouldn't have a look at all without this context...

ok , Eli Laibas article is not perfect and error free, but who is that - you ?

so all your points will melt to 1 problem :

Ad Hoc Distributed Queries .. i can live with that at my systems

and thats why the solution is smart for my needs.

regards matthias

Paul White SSC Guru Points: 150467 More actions · Answer 3

matt32 (3/31/2010)
for my needs it is a great opportunity to create a view from this sql
WITH mycte AS (SELECT cast ('2007-01-01' AS DATETIME) DateValue UNION ALL SELECT DateValue + 1 FROM mycte WHERE DateValue +
1 < getdate())
SELECT distinct YEAR(DateValue) MyYear,MONTH(DateValue) MyMonth
from mycte OPTION ( MAXRECURSION 0 )

Hey, that's great, but only if performance is not important to you 🙂

For a good explanation of why RBAR techniques like this can really suck, see MVP Jeff Moden's article[/url] on this site.

For comparison, let's do the same thing, but in a set-based manner:

-- For demonstration purposes only

USE tempdb;

GO

-- Conditional object drops

IF OBJECT_ID(N'dbo.MonthYearList')

IS NOT NULL

DROP FUNCTION dbo.MonthYearList;

GO

IF OBJECT_ID(N'dbo.Numbers')

IS NOT NULL

DROP FUNCTION dbo.Numbers;

GO

-- An in-line function to create a numbers table on the fly

CREATE FUNCTION dbo.Numbers

(@N BIGINT)

RETURNS TABLE

WITH SCHEMABINDING

AS RETURN

WITH

N1 AS (SELECT N = 1 UNION ALL SELECT 1),

N2 AS (SELECT N = 1 FROM N1 T, N1),

N3 AS (SELECT N = 1 FROM N2 T, N2),

N4 AS (SELECT N = 1 FROM N3 T, N3),

N5 AS (SELECT N = 1 FROM N4 T, N4),

N6 AS (SELECT N = 1 FROM N5 T, N5),

NM AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 0)) AS N FROM N6)

SELECT N

FROM NM

WHERE @N >= 0

AND N <= @N

GO

-- An in-line function wrapping the set-based implementation

CREATE FUNCTION dbo.MonthYearList

(@From DATETIME)

RETURNS TABLE

WITH SCHEMABINDING

AS RETURN

SELECT the_year = YEAR(This.month_start),

the_month = MONTH(This.month_start)

FROM dbo.Numbers (DATEDIFF(MONTH, @From, CURRENT_TIMESTAMP) + 1)

CROSS

APPLY (SELECT DATEADD(MONTH, DATEDIFF(MONTH, 0, CURRENT_TIMESTAMP), 0)) Base (month_start)

CROSS

APPLY (SELECT DATEADD(MONTH, 1 - N, Base.month_start)) This (month_start)

WHERE This.month_start BETWEEN @From AND CURRENT_TIMESTAMP;

GO

-- Collect performance statistics

SET STATISTICS IO, TIME ON;

-- The RBAR method

WITH mycte AS (SELECT cast ('1753-01-01' AS DATETIME) DateValue UNION ALL SELECT DateValue + 1 FROM mycte WHERE DateValue +

1 < getdate())

SELECT distinct YEAR(DateValue) MyYear,MONTH(DateValue) MyMonth

from mycte OPTION ( MAXRECURSION 0 )

-- Set based

SELECT *

FROM dbo.MonthYearList ('17530101');

-- Stop recording statistics

SET STATISTICS IO, TIME OFF;

GO

-- Tidy up

DROP FUNCTION dbo.MonthYearList;

DROP FUNCTION dbo.Numbers;

Performance results:

Your method: 2152ms

Set-based: 168ms

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi