Temporary Table (TempTable), Partition By, Common Table Expression (CTE) and Views

A temporary SQL table, also known as a temp table, is a table that is created and used within the context of a specific session or transaction in a database management system. It is designed to store temporary data that is needed for a short duration and does not require a permanent storage solution.

What is difference between temp table and view in SQL?

Similar to Temp Tables, Views do not store the data itself but instead presents the result of a query as if it were a table. A very important distinction between Temp Tables and Views is that typically the creation of Views is limited to the Database Administrators.

EXAMPLE

CREATE TABLE EmployeeSalary(

EmployeeID                int,

JobTitle                     varchar(50),

Salary                           int

)

SELECT *

FROM EmployeeSalary

INSERT INTO EmployeeSalary

VALUES(

‘1009’, ‘Salesman’, ‘31000’)

Temp table

CREATE TABLE #tem_Empioyee(

EmplyeeID     int,

JobTitle         varchar(50),

Salary                int)

SELECT *

FROM #tem_Empioyee

INSERT INTO #tem_Empioyee

VALUES(

‘1001’, ‘HR’, ‘80000’

)

Used the existing table information from employee salary to fill into #tem_Employee table

INSERT INTO #tem_Empioyee

SELECT *

FROM EmployeeSalary

PARTITION BY

The ‘PARTITION BY’ clause in SQL is a subclause of the ‘OVER’ clause. It’s used to split a large table into smaller, more manageable partitions. Each partition is then processed for the function present in the ‘OVER()’ clause.

SELECT column_name, aggregate_function(column_name) OVER (PARTITION BY column_name)

FROM table_name;

Example of ‘PARTITION BY’

1). SELECT CookieName,Quantity,RevenuePerCookie,

Count(CookieName) OVER (PARTITION BY cookieName) AS TotalCookie

FROM [dbo].[Product]

JOIN [dbo].[Order_Product]

ON [dbo].[Order_Product].CookieID=[dbo].[Product].CookieID

2). SELECT CustomerName,OrderTotal,

Count(CustomerName) OVER (PARTITION BY CustomerName) As TotalCustomerName

FROM [dbo].[Customers]

JOIN [dbo].[Orders]

ON [dbo].[Orders].CustomerID=[dbo].[Customers].CustomerID

Difference between Partition by and group by

SELECT CookieName,Quantity,RevenuePerCookie,Count(CookieName)

FROM [dbo].[Product]

JOIN [dbo].[Order_Product]

ON [dbo].[Order_Product].CookieID=[dbo].[Product].CookieID

GROUP BY CookieName,Quantity,RevenuePerCookie

CTE

A Common Table Expression (CTE) is the result set of a query which exists temporarily and for use only within the context of a larger query. Much like a derived table, the result of a CTE is not stored and exists only for the duration of the query.

CTEs are much more readable than subqueries when you’re writing a complex report. A CTE can be used many times within a query, whereas a subquery can only be used once. This can make the query definition much shorter, but it won’t necessarily result in improved performance.

EXAMPLE;

WITH MYTempTable AS (

SELECT CookieName,Quantity,RevenuePerCookie,

Count(CookieName) OVER (PARTITION BY cookieName) AS TotalCookie

FROM [dbo].[Product]

JOIN [dbo].[Order_Product]

ON [dbo].[Order_Product].CookieID=[dbo].[Product].CookieID

)

SELECT *

FROM MYTempTable

VIEWS

Views are another type of virtual table that can be used in SQL. Views are similar to CTEs, but there are some important differences. First, views can be used by multiple users at the same time. Second, views can be used to restrict access to the underlying data, so only certain users can see certain information. And third, views can be used to perform operations like joins and aggregation.

EXAMPLE

CREATE VIEW

vw_Customers_and_Orders AS SELECT

C.CustomerName, Phone,

c.address, c.city, O.OrderDate, O.OrderTotal

FROM [dbo].[Customers]C

JOIN [dbo].[Orders]O 

ON O.CustomerID=C.CustomerID

GO

BEGIN

SELECT *

FROM vw_Customers_and_Orders