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 Tuesday, February 09, 2010 9:37 PM
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
Comments posted to this topic are about the item Curiouse View to create for SQLServer 2005
Post #862923
Posted Tuesday, February 09, 2010 11:09 PM


SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Sunday, January 19, 2014 5:00 PM
Points: 605, Visits: 1,690
An interesting question, but a very roundabout way of asking "Does SQL Server 2005 support OPTION (...) hints within Views?"

S.
Post #862952
Posted Tuesday, February 09, 2010 11:21 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 6:40 AM
Points: 3,683, Visits: 4,818
Good question.


____________________________________________
Space, the final frontier? not any more...
All limits henceforth are self-imposed.
“libera tute vulgaris ex”
Post #862955
Posted Wednesday, February 10, 2010 12:47 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
.. it doesn't .. but you can create a sp instead when you want to use cte.
Normal OPTION () hints can only be used within select's as i know
Post #862980
Posted Wednesday, February 10, 2010 2:24 AM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Thursday, April 10, 2014 7:08 AM
Points: 3,448, Visits: 4,406
matt32 (2/10/2010)
but you can create a sp instead when you want to use cte

... or you can create a view without OPTION clause and add OPTION to the SELECT statement
SELECT *
FROM month_since_200701
OPTION(MAXRECURSION 0)

Post #863022
Posted Wednesday, February 10, 2010 6:53 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Wednesday, April 16, 2014 11:52 AM
Points: 2,919, Visits: 2,511
I thought it was a good question. It caused me to think and read a couple of times to make sure of what I was reading. It also caused me to remember something that I hadn't though of in a long time thus refreshing my memory.

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 #863187
Posted Wednesday, February 10, 2010 7:02 AM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: 2 days ago @ 11:58 AM
Points: 3,924, Visits: 1,588
sjimmo (2/10/2010)
"it doesn't .. but you can create a sp instead when you want to use cte"

Matt32 - The question was about a view.

Why do so many complain about a question that is not there? If the question is for a view, why do we not address it from that aspect?


I felt it is a good question. But Matt is not complaining here. He is giving an alternative to use CTE OPTION in proc instead of view.


SQL DBA.
Post #863198
Posted Wednesday, February 10, 2010 8:04 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, April 04, 2014 8:25 AM
Points: 2,602, Visits: 17,845
I got it right, but for the wrong reason. Is it my imagination or is the UNION ALL in the wrong spot? Shouldn't it go between the SELECTs?

Chad
Post #863266
Posted Wednesday, February 10, 2010 8:25 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Tuesday, January 21, 2014 8:55 AM
Points: 1,520, Visits: 3,035
Chad Crawford (2/10/2010)
I got it right, but for the wrong reason. Is it my imagination or is the UNION ALL in the wrong spot? Shouldn't it go between the SELECTs?

Chad
It's in the right place. The UNION ALL is already between the two SELECTs within the CTE. That's what enables the recursive nature of the CTE -- having that anchor point defined (the 2007-01-01)
Post #863297
Posted Wednesday, February 10, 2010 9:16 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 10:42 AM
Points: 20,460, Visits: 14,086
The union all is properly placed. The formatting of the question is a little awkward - so it looks wrong at first glance.


Thanks (Matt) for the question.




Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp
Post #863373
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse