Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12

Curiouse View to create for SQLServer 2005 Expand / Collapse
Author
Message
Posted Wednesday, February 10, 2010 10:18 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, July 14, 2014 9:47 AM
Points: 2,919, Visits: 2,525
Yes, it was me reading more into it - probably because it happens so often.

Sorry Matt


Steve Jimmo
Sr DBA
“If we ever forget that we are One Nation Under God, then we will be a Nation gone under." - Ronald Reagan
Post #863447
Posted Wednesday, February 10, 2010 10:51 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Friday, January 18, 2013 5:32 AM
Points: 61, Visits: 171
Hi Steve,
no problem , i haven't had the time to write a proper article on how to create a view with Option()
at the end of the statement .. but here is the short way.

1. Create a SP
2. Use Eli's SP to create a View
http://www.sqlservercentral.com/articles/T-SQL/68233/

Sometimes it is useful to use OPTION() to kick the optimizer into the right direction

This small calendar i used for reporting sale gaps ... nothing is more difficult than reporting data that not exists

Regards Matthias
Post #863476
Posted Tuesday, March 30, 2010 8:53 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: 2 days ago @ 6:32 PM
Points: 11,194, Visits: 11,140
Using OPENROWSET that way is just wrong!!!



Paul White
SQL Server MVP
SQLblog.com
@SQL_Kiwi
Post #892874
Posted Tuesday, March 30, 2010 9:35 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: 2 days ago @ 5:17 PM
Points: 1,521, Visits: 3,038
Paul White NZ (3/30/2010)
Using OPENROWSET that way is just wrong!!!
Uh..... wrong thread, maybe? What OPENROWSET?
Post #892952
Posted Tuesday, March 30, 2010 10:00 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Friday, January 18, 2013 5:32 AM
Points: 61, Visits: 171
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
Post #892982
Posted Tuesday, March 30, 2010 11:04 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: 2 days ago @ 6:32 PM
Points: 11,194, Visits: 11,140
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
SQL Server MVP
SQLblog.com
@SQL_Kiwi
Post #893072
Posted Wednesday, March 31, 2010 3:11 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Friday, January 18, 2013 5:32 AM
Points: 61, Visits: 171


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

Post #893569
Posted Wednesday, March 31, 2010 5:26 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: 2 days ago @ 6:32 PM
Points: 11,194, Visits: 11,140
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 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
SQL Server MVP
SQLblog.com
@SQL_Kiwi
Post #893651
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse