View or Temporary Table - Which to Use in Ms SQL Server

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

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.

Is it possible to create a temporary table in a View and drop it after select?

No, a view consists of a single SELECT statement. You cannot create or drop tables in a view.

Maybe a common table expression (CTE) can solve your problem. CTEs are temporary result sets that are defined within the execution scope of a single statement and they can be used in views.

Example (taken from here) - you can think of the SalesBySalesPerson CTE as a temporary table:

CREATE VIEW vSalesStaffQuickStats
AS
  WITH SalesBySalesPerson (SalesPersonID, NumberOfOrders, MostRecentOrderDate)
      AS
      (
            SELECT SalesPersonID, COUNT(*), MAX(OrderDate)
            FROM Sales.SalesOrderHeader
            GROUP BY SalesPersonID
      )
  SELECT E.EmployeeID,
         EmployeeOrders = OS.NumberOfOrders,
         EmployeeLastOrderDate = OS.MostRecentOrderDate,
         E.ManagerID,
         ManagerOrders = OM.NumberOfOrders,
         ManagerLastOrderDate = OM.MostRecentOrderDate
  FROM HumanResources.Employee AS E
  INNER JOIN SalesBySalesPerson AS OS ON E.EmployeeID = OS.SalesPersonID
  LEFT JOIN SalesBySalesPerson AS OM ON E.ManagerID = OM.SalesPersonID
GO

Performance considerations

Which are more performant, CTE or temporary tables?

What are the advantages of using a view over a temporary table in SQL Server

To discern, ask yourself if you need to reuse the information:

• a view is a glorified SELECT and it's used mainly for convenience
• you can materizalize a view, i.e. store it as a table and even index it. See this question
• use a temp table if you will not reuse the structure many times, like in a script that runs every now and then
• views will take space (especially if materialized) and having many views is difficult to maintain

Also note how temp tables are destroyed:

• if you create a temp table #tbl, it will be destroyed when it goes out of scope (e.g. at the end of the script).
• you can however create a temp table like ##tbl (with two #) and it will be destroyed when the connection ends.

Workaround for temporary Table in view SQL Server

You can call your split function directly on the base table using APPLY, meaning no temp table or loops are required:

SELECT r.id, s.data 
FROM  RootTable AS r
CROSS APPLY dbo.Split(dbo.DelineateEachNth(r.VeryLongText, 50, '$'), '$') AS s;

You may find the scalar function dbo.DelineateEachNth is a performance killer (as all scalar UDFs are), as such an alternative way to split the string is to use a tally table:

CREATE FUNCTION dbo.FixedLengthSplit
(
   @String      NVARCHAR(MAX),
   @Length      INT
)
RETURNS TABLE
WITH SCHEMABINDING AS
RETURN
(   WITH N1 AS (SELECT N FROM (VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1), (1)) n (N)),
    N2(N) AS (SELECT 1 FROM N1 a CROSS JOIN N1 b),
    N3(N) AS (SELECT 1 FROM N2 a CROSS JOIN N2 b),
    N4(N) AS (SELECT 1 FROM N3 a CROSS JOIN N3 b),
    Numbers (N) AS 
    (   SELECT TOP (CONVERT(INT, CEILING(1.0 * ISNULL(LEN(@String),1) / @Length))) 
                ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) - 1
        FROM n4
    )
    SELECT  ItemNumber = N + 1,
            Data = SUBSTRING(@String, 1 + (@Length * N), @Length)
    FROM    Numbers
);

Then your view is just:

SELECT *
FROM rootTable AS r
CROSS APPLY dbo.FixedLengthSplit(r.VeryLongString, 50) AS s;

How to see temp table created by code in sql server?

After the code has finished and the session is closed, the temporary table will cease to exist. If you want to see it in SQL Server Management Studio (SSMS), you need to keep the code session open until the data has been reviewed in SSMS.

Per Technet:

Global temporary tables are visible to any user and any connection
after they are created, and are deleted when all users that are
referencing the table disconnect from the instance of SQL Server.

As an alternative, there's a C# code option here that selects the data from the temporary table into a code variable for review in the code ... (and if the code is to exist, you could possibly write it to a file or review by another means) -- see: https://stackoverflow.com/a/6748570/3063884

When creating a SQL view can i have temp tables?

In SQL Server, you cannot have a temporary table in a view. As the documentation says:

The SELECT clauses in a view definition cannot include the following:

. . .

• A reference to a temporary table or a table variable.

However, you should be able to express your view using common table expressions (CTEs) and subqueries. If you really do find that you need temporary tables, then you can can use table variables and a table-valued function.

When should I use a table variable vs temporary table in sql server?

Your question shows you have succumbed to some of the common misconceptions surrounding table variables and temporary tables.

I have written quite an extensive answer on the DBA site looking at the differences between the two object types. This also addresses your question about disk vs memory (I didn't see any significant difference in behaviour between the two).

Regarding the question in the title though as to when to use a table variable vs a local temporary table you don't always have a choice. In functions, for example, it is only possible to use a table variable and if you need to write to the table in a child scope then only a #temp table will do
(table-valued parameters allow readonly access).

Where you do have a choice some suggestions are below (though the most reliable method is to simply test both with your specific workload).

1. If you need an index that cannot be created on a table variable then you will of course need a #temporary table. The details of this are version dependant however. For SQL Server 2012 and below the only indexes that could be created on table variables were those implicitly created through a UNIQUE or PRIMARY KEY constraint. SQL Server 2014 introduced inline index syntax for a subset of the options available in CREATE INDEX. This has been extended since to allow filtered index conditions. Indexes with INCLUDE-d columns or columnstore indexes are still not possible to create on table variables however.

2. If you will be repeatedly adding and deleting large numbers of rows from the table then use a #temporary table. That supports TRUNCATE (which is more efficient than DELETE for large tables) and additionally subsequent inserts following a TRUNCATE can have better performance than those following a DELETE as illustrated here.

3. If you will be deleting or updating a large number of rows then the temp table may well perform much better than a table variable - if it is able to use rowset sharing (see "Effects of rowset sharing" below for an example).
4. If the optimal plan using the table will vary dependent on data then use a #temporary table. That supports creation of statistics which allows the plan to be dynamically recompiled according to the data (though for cached temporary tables in stored procedures the recompilation behaviour needs to be understood separately).
5. If the optimal plan for the query using the table is unlikely to ever change then you may consider a table variable to skip the overhead of statistics creation and recompiles (would possibly require hints to fix the plan you want).
6. If the source for the data inserted to the table is from a potentially expensive SELECT statement then consider that using a table variable will block the possibility of this using a parallel plan.
7. If you need the data in the table to survive a rollback of an outer user transaction then use a table variable. A possible use case for this might be logging the progress of different steps in a long SQL batch.
8. When using a #temp table within a user transaction locks can be held longer than for table variables (potentially until the end of transaction vs end of statement dependent on the type of lock and isolation level) and also it can prevent truncation of the tempdb transaction log until the user transaction ends. So this might favour the use of table variables.
9. Within stored routines, both table variables and temporary tables can be cached. The metadata maintenance for cached table variables is less than that for #temporary tables. Bob Ward points out in his tempdb presentation that this can cause additional contention on system tables under conditions of high concurrency. Additionally, when dealing with small quantities of data this can make a measurable difference to performance.

Effects of rowset sharing

DECLARE @T TABLE(id INT PRIMARY KEY, Flag BIT);

CREATE TABLE #T (id INT PRIMARY KEY, Flag BIT);

INSERT INTO @T 
output inserted.* into #T
SELECT TOP 1000000 ROW_NUMBER() OVER (ORDER BY @@SPID), 0
FROM master..spt_values v1, master..spt_values v2

SET STATISTICS TIME ON

/*CPU time = 7016 ms,  elapsed time = 7860 ms.*/
UPDATE @T SET Flag=1;

/*CPU time = 6234 ms,  elapsed time = 7236 ms.*/
DELETE FROM @T

/* CPU time = 828 ms,  elapsed time = 1120 ms.*/
UPDATE #T SET Flag=1;

/*CPU time = 672 ms,  elapsed time = 980 ms.*/
DELETE FROM #T

DROP TABLE #T

Create VIEW using temp table

If you absolutely need to use a temp table, you can put it within a Stored procedure and then call/execute it. Something like the below might work.

 CREATE PROCEDURE dbo.UsingTempTables
 AS 
 BEGIN
 SET NOCOUNT ON;

CREATE TABLE #TempTable(ColumnA INT, ColumnB Varchar(50));

INSERT INTO #TempTable(ColumnA,ColumnB) VALUES(5,'ABC');
INSERT INTO #TempTable(ColumnA,ColumnB) VALUES(6,'DEF');

SELECT ColOne = ColumnA
    ,  ColTwo = ColumnB
    FROM #TempTable
 END;

Then use something like the below in the execute sql task in SSIS:

 EXEC('EXEC dbo.UsingTempTables') 
 WITH RESULT SETS
 ( ( ColOne INT, ColTwo VARCHAR(50)) )

FYI: Check out the below link for additional reference (the above answer is based on it) https://www.sqlservercentral.com/Forums/Topic1574990-364-1.aspx

---

Related Topics