How to create Derived Tables in SQL Server

Опубликовано: 10 Март 2021
на канале: BeardedDev

7,724

131

Another video brought to you by BeardedDev, bringing you tutorials on Data Engineering, Business Intelligence, T-SQL Programming and Data Analysis.

If you like the video you can support me on Patreon, / beardeddev

In this SQL tutorial I answer the below questions:
What are derived tables?
When should I use a derived table?

I talk you through how to create derived tables and what there typical use cases are, in the examples I also demonstrate common errors you might get or see when working with derived tables.

Finally I show the execution plans of the queries and explain how derived tables are executed inline and what that actually means.

Code Samples - this is not complete but you can copy and paste and change as necessary

SELECT
AVG(Total)
FROM
(
SELECT
CustomerID,
SUM(TotalDue) AS Total
FROM Sales.SalesOrderHeader
GROUP BY CustomerID
) AS D

-- executed inline
SELECT
CustomerID,
SUM(TotalDue) AS Total
FROM Sales.SalesOrderHeader
GROUP BY CustomerID;

SELECT
CustomerID,
SUM(TotalDue) AS Total
FROM
(
SELECT
CustomerID,
TotalDue
FROM Sales.SalesOrderHeader
) AS D
GROUP BY CustomerID;