create temp view

Question

create temp view

Donald Leaderach

SSC-Addicted

Points: 478
More actions
March 13, 2008 at 6:56 am

#95896

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.

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

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

Jeff Moden SSC Guru Points: 1004329 More actions · Answer 1

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.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 2

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 More actions · Answer 3

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

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 4

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: 398882 More actions · Answer 5

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

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Ian Yates SSCoach Points: 19738 More actions · Answer 6

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 More actions · Answer 7

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: 398882 More actions · Answer 8

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

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 9

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: 1004329 More actions · Answer 10

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.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004329 More actions · Answer 11

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.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 12

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 More actions · Answer 13

No create temporary view.

I tried temporary procedure.

Jeff Moden SSC Guru Points: 1004329 More actions · Answer 14

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.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)