When writing queries, we often require an intermediate table to store results which will be used later in the query. One of the more common ways to achieve this is with a temporary table.
SELECT Column1
, Column2
INTO #MyNewTable
FROM Table1
When I see these in a code review, I usually ask if the temp table can be replaced with a table variable or a common table expression. Both of these tend to have better performance for most queries. Also, because they can only be used in the current query, there's less risk of side effects which make code difficult to maintain.
So if this was the original query.
SELECT Column1
, Column2
INTO #MyTemp
FROM Example
WHERE Column3 = 0
SELECT *
FROM #MyTemp
DROP TABLE #MyTemp
The alternative using table variables would be.
DECLARE @tableVar TABLE (
Col1 int,
Col2 varchar(50)
)
INSERT INTO @tableVar
SELECT Column1
, Column2
FROM Example
WHERE Column3 = 0
SELECT *
FROM @tableVar
And this is the CTE version.
WITH
cteVar
AS (
SELECT Column1
, Column2
FROM Example
WHERE Column3 = 0
)
SELECT
*
FROM cteVar
These changes, for the most part, do not impact the functionality of the query. They're proposed to increase performance, readability, maintainability, etc. So make sure you test and review your changes with others.