View or Temporary Table - which to use in MS SQL Server?

sql sql-server view temp-tables
ruedi picture ruedi · Jun 3, 2013 · Viewed 40.4k times · Source

I have a problem to decide whether to use a view or a temp table.

I have a stored procedure that i call from program. In that SP i store the result of a long query in a temp table, name the columns and make another queries on that table store the results in labels or a gridview or whatever and drop the Temp Table. I could also store the query-result in a view and make queries on that view. So what is better or in what case do i HAVE to use a VIEW/ Temp Table.

According to my research a view has the benefit of: Security, Simplicity and Column Name Specification. My temporary table fulfills all that too (according to my opinion).

Answer

Gordon Linoff picture Gordon Linoff · Jun 3, 2013

If the query is "long" and you are accessing the results from multiple queries, then a temporary table is the better choice.

A view, in general, is just a short-cut for a select statement. If does not imply that the results are ever run and processed. If you use a view, the results will need to be regenerated each time it is used. Although subsequent runs of the view may be more efficient (say because the pages used by the view query are in cache), a temporary table actually stores the results.

In SQL Server, you can also use table variables (declare @t table . . .).

Using a temporary table (or table variable) within a single stored procedure would seem to have few implications in terms of security, simplicity, and column names. Security would be handled by access to the stored procedure. Column names are needed for either solution. Simplicity is hard to judge without more information, but nothing sticks out as being particularly complicated.

Related questions

How can I check if a View exists in a Database?

I have some SQL code that needs to be executed if a certain View exists in a database. How would I go about checking if the View exists? EDIT: The DBMS being used is Microsoft SQL Server

Read More
How to make CREATE OR REPLACE VIEW work in SQL Server?

CREATE OR REPLACE VIEW doesn't seem to work in SQL Server. So how do I port CREATE OR REPLACE VIEW to work on SQL Server? This is what I'm trying to do: CREATE OR REPLACE VIEW data_VVVV AS SELECT …

Read More
Drop view if exists

I have script where I want to first drop view and then create it. I know how to drop table: IF EXISTS (SELECT * FROM sys.tables WHERE name = 'table1' AND type = 'U') DROP TABLE table1; so I did the …

Read More