CTEs, Local Temp Tables, Global Temp Tables, Table Variables, Subqueries, and Views in SQL Server
Understanding Data Storage and Query Techniques in MS SQL Server
Microsoft SQL Server provides several ways to store, manipulate, and query data temporarily or permanently. Understanding these options—CTEs (Common Table Expressions), local temporary tables, global temporary tables, table variables, subqueries, and views—can help you write efficient, maintainable, and scalable SQL queries.
In this guide, we’ll explore each of these concepts, when to use them, and their advantages and disadvantages.
1. Common Table Expressions (CTEs)
A CTE is a temporary result set defined within the execution scope of a SELECT, INSERT, UPDATE, or DELETE statement. It is created using the WITH keyword and is primarily used for better readability and recursive queries.
Syntax
WITH CTE_Name AS (
SELECT Column1, Column2
FROM TableName
WHERE Condition
)
SELECT * FROM CTE_Name;
Key Features
Exists only during the execution of the query.
Improves readability for complex queries.
Supports recursion (e.g., hierarchical data).
👋 Become 1% better at .NET Full Stack development every day.
👆 https://dotnet-fullstack-dev.blogspot.com/
â™» Restack it Vibe matches, help others to get it
Example: Recursive CTE
WITH EmployeeHierarchy AS (
SELECT EmployeeID, ManagerID, FullName
FROM Employees
WHERE ManagerID IS NULL
UNION ALL
SELECT e.EmployeeID, e.ManagerID, e.FullName
FROM Employees e
INNER JOIN EmployeeHierarchy eh ON e.ManagerID = eh.EmployeeID
)
SELECT * FROM EmployeeHierarchy;
2. Local Temporary Tables
Local temporary tables are created using the # symbol before the table name. They are stored in the tempdb database and are accessible only within the session that created them.
Syntax
CREATE TABLE #TempTable (
Column1 INT,
Column2 VARCHAR(50)
);
Key Features
Automatically dropped when the session ends.
Ideal for intermediate result storage in procedures or complex queries.
CREATE TABLE #TempTable (
ID INT,
Name NVARCHAR(50)
);
INSERT INTO #TempTable (ID, Name)
VALUES (1, 'John Doe'), (2, 'Jane Smith');
SELECT * FROM #TempTable;
3. Global Temporary Tables
Global temporary tables are similar to local temporary tables but are accessible across all sessions and users. They are created using the ## symbol before the table name.
Syntax
CREATE TABLE ##GlobalTempTable (
Column1 INT,
Column2 VARCHAR(50)
);
Key Features
Stored in the tempdb database.
Persist until the last session accessing it is closed.
CREATE TABLE ##GlobalTempTable (
ID INT,
Name NVARCHAR(50)
);
INSERT INTO ##GlobalTempTable (ID, Name)
VALUES (1, 'Alice'), (2, 'Bob');
-- Accessible across different sessions
SELECT * FROM ##GlobalTempTable;
4. Table Variables
Table variables are variables of type TABLE that are stored in memory and scoped to the batch, procedure, or function in which they are declared.
Syntax
DECLARE @TableVariable TABLE (
Column1 INT,
Column2 VARCHAR(50)
);
Key Features
Scoped to the current batch or procedure.
Does not support indexes (except for primary keys).
Suitable for small data sets due to in-memory storage.
DECLARE @TableVariable TABLE (
ID INT,
Name NVARCHAR(50)
);
INSERT INTO @TableVariable (ID, Name)
VALUES (1, 'Mark'), (2, 'Sarah');
SELECT * FROM @TableVariable;
5. Subqueries
A subquery is a query nested inside another query. Subqueries are often used to filter, calculate, or provide data to the outer query.
Key Features
Can be used in
SELECT,FROM, orWHEREclauses.May return a single value, a list, or a table.
Example: Subquery in WHERE Clause
SELECT EmployeeID, FullName
FROM Employees
WHERE DepartmentID IN (
SELECT DepartmentID
FROM Departments
WHERE DepartmentName = 'Sales'
);
Example: Subquery in SELECT Clause
SELECT EmployeeID,
(SELECT DepartmentName FROM Departments WHERE Departments.DepartmentID = Employees.DepartmentID) AS DepartmentName
FROM Employees;
6. Views
A view is a virtual table that is defined by a SELECT query. Views provide a way to simplify complex queries and abstract underlying table structures.
Syntax
CREATE VIEW ViewName AS
SELECT Column1, Column2
FROM TableName
WHERE Condition;
Key Features
Acts as a saved query.
Provides security by restricting access to the underlying tables.
Cannot store data; reflects the data in the underlying tables.
CREATE VIEW EmployeeView AS
SELECT EmployeeID, FullName, DepartmentName
FROM Employees
INNER JOIN Departments ON Employees.DepartmentID = Departments.DepartmentID;
SELECT * FROM EmployeeView;
Comparison of Features
When to Use Each Option
CTEs:
Recursive queries (e.g., hierarchical data).
Simplifying complex queries into readable blocks.
Local Temporary Tables:
Intermediate results within a single session.
Storing data for multiple operations in a query.
Global Temporary Tables:
Sharing temporary data across multiple sessions.
Rarely used due to potential conflicts between sessions.
Table Variables:
Lightweight, in-memory operations within a batch or procedure.
Suitable for small datasets or operations without indexes.
Subqueries:
Filtering or calculating values within a larger query.
Avoid for performance-critical scenarios (consider CTEs or joins instead).
Views:
Abstracting complex query logic.
Restricting access to underlying tables for security.
Conclusion
Each of these SQL Server features serves a unique purpose and offers different trade-offs. Understanding their strengths and limitations helps you choose the right tool for the job. For example:
Use CTEs for readability and recursion.
Leverage local temporary tables for intermediate data storage in complex queries.
Rely on views to simplify and standardize query logic.
Choose wisely to write cleaner, faster, and more maintainable SQL queries! 😊