<aside> 👁️ Views are virtual tables that store a query. Temp tables store actual data temporarily.

</aside>

VIEWS

Create a View

CREATE VIEW Sales.V_Monthly_Summary AS
SELECT
    DATETRUNC(month, OrderDate) AS OrderMonth,
    SUM(Sales)                  AS TotalSales,
    COUNT(OrderID)              AS TotalOrders
FROM Sales.Orders
GROUP BY DATETRUNC(month, OrderDate);
GO

Query a View

SELECT * FROM Sales.V_Monthly_Summary;

Drop a View

IF OBJECT_ID('Sales.V_Monthly_Summary', 'V') IS NOT NULL
    DROP VIEW Sales.V_Monthly_Summary;
GO

Use Case 1 — Hide Complexity

CREATE VIEW Sales.V_Order_Details AS
SELECT
    o.OrderID, o.OrderDate,
    p.Product, p.Category,
    COALESCE(c.FirstName, '') + ' ' + COALESCE(c.LastName, '') AS CustomerName,
    c.Country AS CustomerCountry,
    COALESCE(e.FirstName, '') + ' ' + COALESCE(e.LastName, '') AS SalesName,
    o.Sales, o.Quantity
FROM Sales.Orders AS o
LEFT JOIN Sales.Products  AS p ON p.ProductID = o.ProductID
LEFT JOIN Sales.Customers AS c ON c.CustomerID = o.CustomerID
LEFT JOIN Sales.Employees AS e ON e.EmployeeID = o.SalesPersonID;
GO

Use Case 2 — Data Security (Row-Level Access)

-- EU team only sees non-USA orders
CREATE VIEW Sales.V_Order_Details_EU AS
SELECT ...
FROM Sales.Orders AS o
...
WHERE c.Country != 'USA';
GO

TEMPORARY TABLES

Temporary tables store data for the duration of a session. They are prefixed with #.

-- Step 1: Create temp table from existing table
SELECT * INTO #Orders FROM Sales.Orders;

-- Step 2: Clean data in temp table
DELETE FROM #Orders WHERE OrderStatus = 'Delivered';

-- Step 3: Load cleaned data into a permanent table
SELECT * INTO Sales.OrdersTest FROM #Orders;

Views vs Temp Tables vs CTEs