Curiouse View to create for SQLServer 2005

Question

Curiouse View to create for SQLServer 2005

matt32

SSC Veteran

Points: 264
More actions
February 9, 2010 at 9:37 pm

#216507

Comments posted to this topic are about the item Curiouse View to create for SQLServer 2005
February 9, 2010 at 11:21 pm

This was removed by the editor as SPAM

Viewing 15 posts - 1 through 15 (of 17 total)

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

Fal Hall of Fame Points: 3143 More actions · Answer 1

An interesting question, but a very roundabout way of asking "Does SQL Server 2005 support OPTION (...) hints within Views?" 😉

S.

matt32 SSC Veteran Points: 264 More actions · Answer 2

.. 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:-P

vk-kirov SSCertifiable Points: 7686 More actions · Answer 3

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)

sjimmo SSChampion Points: 11139 More actions · Answer 4

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

SanjayAttray SSChampion Points: 13157 More actions · Answer 5

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.

Chad Crawford SSChampion Points: 11610 More actions · Answer 6

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

john.arnott SSChampion Points: 11882 More actions · Answer 7

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)

SQLRNNR SSC Guru Points: 281334 More actions · Answer 8

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, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

sjimmo SSChampion Points: 11139 More actions · Answer 9

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

matt32 SSC Veteran Points: 264 More actions · Answer 10

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 :w00t:

Regards Matthias

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

Using OPENROWSET that way is just wrong!!! 😀

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

john.arnott SSChampion Points: 11882 More actions · Answer 12

Paul White NZ (3/30/2010)
Using OPENROWSET that way is just wrong!!! 😀

Uh..... wrong thread, maybe? What OPENROWSET?

matt32 SSC Veteran Points: 264 More actions · Answer 13

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