Curiouse View to create for SQLServer 2005

  • Comments posted to this topic are about the item Curiouse View to create for SQLServer 2005

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

    S.

  • This was removed by the editor as SPAM

  • .. 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

  • 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)

  • 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

  • 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.

  • 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

  • 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)

  • 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

  • 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

  • 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

  • Using OPENROWSET that way is just wrong!!! 😀

  • Paul White NZ (3/30/2010)


    Using OPENROWSET that way is just wrong!!! 😀

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

  • 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

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

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