Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 


 
     
Recent Posts
   
Popular Topics
Home   
Search
    Members    Calendar    Who's On

Home » Article Discussions » Article Discussions by Author » Discuss content posted by Matthias Kroll » Curiouse View to create for SQLServer 2005
Add to briefcase
18 posts, Page 1 of 2
12»»

Curiouse View to create for SQLServer 2005 Expand / Collapse
Rate Topic
Display Mode
Topic Options
Author
Message
matt32
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
Fal
Posted Tuesday, February 09, 2010 11:09 PM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: 2 days ago @ 12:43 AM
Points: 584, Visits: 1,574
An interesting question, but a very roundabout way of asking "Does SQL Server 2005 support OPTION (...) hints within Views?"

S.
Post #862952
Stewart "Arturius" Campbell
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 @ 8:26 AM
Points: 3,164, Visits: 4,344
Good question.

____________________________________________
Space, the final frontier? not any more...
All limits henceforth are self-imposed.
“libera tute vulgaris ex”
Post #862955
matt32
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
vk-kirov
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, June 13, 2013 8:50 PM
Points: 3,208, Visits: 4,178
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
sjimmo
Posted Wednesday, February 10, 2010 6:53 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, June 14, 2013 7:34 AM
Points: 2,865, Visits: 2,472
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
SanjayAttray
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: Friday, March 15, 2013 2:43 PM
Points: 3,924, Visits: 1,554
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
Chad Crawford
Posted Wednesday, February 10, 2010 8:04 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: 2 days ago @ 1:31 PM
Points: 2,558, Visits: 17,421
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
john.arnott
Posted Wednesday, February 10, 2010 8:25 AM


UDP Broadcaster

UDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP Broadcaster

Group: General Forum Members
Last Login: Monday, June 10, 2013 7:59 PM
Points: 1,491, Visits: 3,010
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
SQLRNNR
Posted Wednesday, February 10, 2010 9:16 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Yesterday @ 3:33 PM
Points: 18,858, Visits: 12,443
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 2008

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
18 posts, Page 1 of 2
12»»

Permissions Expand / Collapse

 
 
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.