create temp view

  • Donald Leaderach

    SSC-Addicted

    Points: 478

    Is it possible to create a temporary view from within a stored procedure for the life of the stored procedure. I do have a way of doing it now where I create the view and at the end it is removed but there is an issue with giving the user dbo authority in order to do this. Plus it phyiscally write and removes the view.

    Creating Temp views allows for the more flexiblilty vs creating numerous permanent views.

    Any help would be appreciated.

  • Jeff Moden

    SSC Guru

    Points: 993924

    No... no way to create a temp view. Creating a TempTable to hold what the TempView would have contained is the next best thing and pretty darned fast to boot.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

    Helpful Links:
    How to post code problems

  • John Mitchell-245523

    SSC Guru

    Points: 148246

    Jeff Moden (3/13/2008)


    No... no way to create a temp view. Creating a TempTable to hold what the TempView would have contained is the next best thing and pretty darned fast to boot.

    Jeff, what if the view/temp table were SELECT * FROM MyTable, and MyTable contained 10,000,000 rows? If the query executed against that temp table were SELECT * FROM #View WHERE ID = 10000000, then that's an awful lot of lot of work to populate the temp table to be only selecting one row. I know that's an extreme example, but wouldn't Donald be better of looking at using a CTE if possible?

    John

  • Donald Leaderach

    SSC-Addicted

    Points: 478

    could you explain how a CTE would work. I failed to mention that this is for SQL 2000.

  • John Mitchell-245523

    SSC Guru

    Points: 148246

    Ah. Please take care to post in the correct forum. There are no CTEs in SQL Server 2000. Would you be able to use a subquery instead? It's messier but it may be the best option.

    John

  • Grant Fritchey

    SSC Guru

    Points: 395320

    What would a "temporary" view do that a query doesn't?

    ----------------------------------------------------
    The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood...
    Theodore Roosevelt

    The Scary DBA
    Author of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd Edition
    Product Evangelist for Red Gate Software

  • Ian Yates

    SSCoach

    Points: 19738

    I suspect you're after syntactic convenience which is why you want a view rather than repeating the same snippet of SQL in several places. Unfortunately, unless you use a temp table, you will probably need to repeat the code.

    Also, I have to ask, if you're creating a view in the proc then presumably the view's code is dynamic so you would be using dynamic SQL. Is that right?

    Finally, you could create the view in the user's schema rather than in the dbo schema....

  • Donald Leaderach

    SSC-Addicted

    Points: 478

    My goal was to create a temp view instead of having to create many,many permanent views.This temp view would be be used in a query built from the same stored procedure. developing this in our test environment works great! but upon moving it to the Production environment it did not work due to the restriction on the database user. Access to the database from the program goes through only one user ID. I had to give it dbo access in order to create the temp view.

    I appreciate all of the help but I think I am going to have to go back to the drawing board and come up with a different plan.

    Thanks.

  • Grant Fritchey

    SSC Guru

    Points: 395320

    Based on the description I still don't understand what the temporary view was doing for the query in question.

    ----------------------------------------------------
    The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood...
    Theodore Roosevelt

    The Scary DBA
    Author of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd Edition
    Product Evangelist for Red Gate Software

  • Matt Miller (4)

    SSC Guru

    Points: 124168

    I suspect you haven't tested this with concurrent users/sessions... The "first" session will do great - and the other sessions will fall apart because the "first" view is there. Or - the last session will come by and start dropping views while the other sessions are trying to use them.

    Go dynamic SQL if you have, or leverage temp tables. The view concept will bring you heartaches, IMO.

    ----------------------------------------------------------------------------------
    Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

  • Jeff Moden

    SSC Guru

    Points: 993924

    John Mitchell (3/13/2008)


    Jeff Moden (3/13/2008)


    No... no way to create a temp view. Creating a TempTable to hold what the TempView would have contained is the next best thing and pretty darned fast to boot.

    Jeff, what if the view/temp table were SELECT * FROM MyTable, and MyTable contained 10,000,000 rows? If the query executed against that temp table were SELECT * FROM #View WHERE ID = 10000000, then that's an awful lot of lot of work to populate the temp table to be only selecting one row. I know that's an extreme example, but wouldn't Donald be better of looking at using a CTE if possible?

    John

    Obviously, there would have to be some constraints in play. It all "depends". Of course, if the table exists and you're only selecting one row, why do you need a view to begin with?

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

    Helpful Links:
    How to post code problems

  • Jeff Moden

    SSC Guru

    Points: 993924

    Folks, just to be clear, you might be able to create a permanent view on the fly and drop it at the end of the proc, but there is no such thing as a "temporary view" with a leadin "#" sign similar to a temporary table. It just ain't happenin'...

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

    Helpful Links:
    How to post code problems

  • John Mitchell-245523

    SSC Guru

    Points: 148246

    Jeff Moden (3/13/2008)


    Of course, if the table exists and you're only selecting one row, why do you need a view to begin with?

    As I mentioned, that's an extreme example. But in general I think it's better not to have to materialise the data in the view just in case you don't come close to using it all. That's why I suggested the CTE or subquery approach.

    John

  • jawahar11088

    Newbie

    Points: 7

    No create temporary view.

    I tried temporary procedure.

  • Jeff Moden

    SSC Guru

    Points: 993924

    jawahar11088 - Sunday, May 20, 2018 7:42 AM

    No create temporary view.

    I tried temporary procedure.

    And???

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

    Helpful Links:
    How to post code problems

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

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