Differences Between Derived Table & CTE,Views,Temp Table and Temp Variable

 Welcome to Quickpickdeal,in this session, we will learn about Derived tables and common table expressions and compare them with other constructs available in SQL Server, such as views, table variables, and local and global temporary tables.

Let's begin with an example. We have a table named TblUniversity, which consists of University_Id and University name columns. Additionally, there's a table named TblStudent, which includes columns for Id, StudentName, gender, and University_Id.

Now, let’s say I want you to join these two tables and produce the output that you can see in the image below.

I want to retrieve the University name along with the total number of students within each University. However, the list should only include universities that have two or more students.

To achieve this output, we have several methods, but we will see how to do it using views. Here, we are creating a view named 'view_name'. We select the University name, University Id, and count(*) to calculate the number of students within each University.

Create view vWTotalStudent
as
Select UniversityName,University_Id, COUNT(*) as TotalStudnent
from TblStudent
join University
on TblStudent.University_Id = University.UnivtyId
group by UniversityName, University_Id

select * from vWTotalStudent

And we are giving it an alias called TotalStudent from TblStudent join with the University. Obviously, between these two tables, University_Id is the common column.

We are joining these two tables on that column, and you will have to group by University name, University_Id because we want the count. We have discussed views, joins, and group by in great detail in the post below.

So views are saved in the database and can be accessed by other queries and stored procedures. If you are using a view in other stored procedures or queries, you can easily utilize it. Read more about views.

• What is a view? Disadvantages & Advantages of views

But if you are using it just this one time in this query, then it doesn’t really make sense to create a view. Instead, we can make use of other constructs available in SQL Server such as CTEs, derived tables, temp tables, table variables, etc., which we will discuss now.

Let’s see how to achieve the same thing using temporary tables.

So obviously, the logic to calculate the University name and TotalStudent is exactly the same. We are going to use that with other constructs available in SQL Server.

Here, we are using temporary tables. The query is the same: select UniversityName, University_Id, count(*) as TotalStudent.

We are giving it an alias, and we are selecting these columns into #TempStudentCount. This is a temporary table.

You might be wondering, where did you define the columns that this temporary table has? When you use the SELECT INTO syntax, what’s going to happen behind the scenes is SQL Server will create those columns for us automatically. You don’t have to define the structure of the temporary table.

Select UniversityName,University_Id, COUNT(*) as TotalStudnent
into #TempStudentCount
from TblStudent
join University
on TblStudent.University_Id = University.UnivtyId
group by UniversityName, University_Id

Select UniversityName, TotalStudnent
From #TempStudentCount
where TotalStudnent >= 1

Drop Table #TempStudentCount

So we are selecting these columns into the #TempStudentCount temporary table. The rest of the logic is the same.

Remember, since this is a temporary table, it’s a good practice to drop the table after you have finished using it.

There are two types of temporary tables: local temporary tables and global temporary tables. Depending on the type of temporary table you’re creating, the scope differs.

Temporary tables are stored in tempDB. Local temporary tables are visible only in the current session and can be shared between nested stored procedures. This means if a temporary table is used in stored procedure A and stored procedure A calls stored procedure B, then the temporary table created in stored procedure A will be available in stored procedure B.

Local temporary tables are visible in the current session and can be shared between nested stored procedure calls. On the other hand, global temporary tables are visible to all sessions and are destroyed when the last connection referencing that table is closed.

Read more about temporary tables in the post below:

• Temporary Tables in sql server with real time example

Declare @TableStudentCount table
(UniversityName nvarchar(100),University_Id int, TotalStudnent int)

Insert @TableStudentCount
Select UniversityName, University_Id, COUNT(*) as TotalStudnent
from TblStudent
join University
on TblStudent.University_Id = University.UnivtyId
group by UniversityName, University_Id

Select UniversityName, TotalStudnent
From @TableStudentCount
where TotalStudnent >= 1

In this example, we are using a table variable. Just like temporary tables, table variables are also created in the temp DB. Most people think table variables are created in memory, but that is not true; both temporary tables and table variables are created in the temp DB.

The scope of a table variable is the batch, stored procedure, or statement block in which it is declared. However, the advantage of using table variables is that you can pass them as parameters between procedure calls.

Another advantage of using table variables is that you don’t have to drop them like temporary tables. However, there are several other differences between table variables and temporary tables as well.

All right, we can also achieve the same thing using derived tables.

Now, take a look at this. When we create a table variable, we have to define the columns and their data types explicitly. However, if you are using a derived table, you don’t actually define a table as such, you just give it an alias.

Select UniversityName, TotalStudnent
from
(
Select UniversityName, University_Id, COUNT(*) as TotalStudnent
from TblStudent
join University
on TblStudent.University_Id = University.UnivtyId
group by UniversityName, University_Id
)
as StudentCount
where TotalStudnent >= 1

Now, if you look at this, this query is enclosed within these brackets, and then you’re specifying 'as StudentCount'. So this entire thing from the first bracket to the last bracket is considered as a derived table.

With StudentCount
as
(
Select UniversityName, University_Id, COUNT(*) as TotalStudnent
from TblStudent
join University
on TblStudent.University_Id = University.UnivtyId
group by UniversityName, University_Id
)

Select UniversityName, TotalStudnent
from StudentCount
where TotalStudnent >= 1

So the exact same query that we have been working with, the query you can see here now, what we have done is we used the keyword 'WITH' and then given a name. This is nothing but the CTE name.

You can say a CTE is similar to a derived table, it is not stored as an object and remains only for the duration of the query.

So this is not being stored as a temporary table or a view, it only lasts for the duration of the query. Outside the context of this query, the CTE doesn’t really have any meaning.

Read More About CTE- What is CTE in sql server with example

Table Sql Script

/****** Object:  Table [dbo].[TblStudent]    Script Date: 10/12/2020 10:05:48 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[TblStudent](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [Name] [nvarchar](max) NOT NULL,
    [Gender] [nvarchar](50) NULL,
    [Marks] [float] NULL,
    [University_Id] [int] NULL,
 CONSTRAINT [PK_TblStudent] PRIMARY KEY CLUSTERED 
(
    [Id] ASC
)
)
GO
/****** Object:  Table [dbo].[University]    Script Date: 10/12/2020 10:05:48 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[University](
    [UnivtyId] [int] IDENTITY(1,1) NOT NULL,
    [UniversityName] [nvarchar](max) NULL,
 CONSTRAINT [PK_University] PRIMARY KEY CLUSTERED 
(
    [UnivtyId] ASC
)
)
GO
SET IDENTITY_INSERT [dbo].[TblStudent] ON 
GO
INSERT [dbo].[TblStudent] ([Id], [Name], [Gender], [Marks], [University_Id]) VALUES (1, 'Mark', 'Male', 500, 2)
GO
INSERT [dbo].[TblStudent] ([Id], [Name], [Gender], [Marks], [University_Id]) VALUES (3, 'Julia', 'Female', 800, 4)
GO
INSERT [dbo].[TblStudent] ([Id], [Name], [Gender], [Marks], [University_Id]) VALUES (4, 'Connor', 'Male', 700, 3)
GO
INSERT [dbo].[TblStudent] ([Id], [Name], [Gender], [Marks], [University_Id]) VALUES (5, 'Madeline', 'Female', 600, 6)
GO
INSERT [dbo].[TblStudent] ([Id], [Name], [Gender], [Marks], [University_Id]) VALUES (6, 'Easton', 'Male', 850, 5)
GO
INSERT [dbo].[TblStudent] ([Id], [Name], [Gender], [Marks], [University_Id]) VALUES (7, 'Gianna', 'Female', 950, 7)
GO
INSERT [dbo].[TblStudent] ([Id], [Name], [Gender], [Marks], [University_Id]) VALUES (8, 'Chase', 'Male', 400, 3)
GO
INSERT [dbo].[TblStudent] ([Id], [Name], [Gender], [Marks], [University_Id]) VALUES (9, 'Marry', 'Female', 850, 1)
GO
INSERT [dbo].[TblStudent] ([Id], [Name], [Gender], [Marks], [University_Id]) VALUES (10, 'Jacob', 'Male', 750, 2)
GO
INSERT [dbo].[TblStudent] ([Id], [Name], [Gender], [Marks], [University_Id]) VALUES (11, 'David', 'Male', 700, 3)
GO
SET IDENTITY_INSERT [dbo].[TblStudent] OFF
GO
SET IDENTITY_INSERT [dbo].[University] ON 
GO
INSERT [dbo].[University] ([UnivtyId], [UniversityName]) VALUES (1, 'Harvard University')
GO
INSERT [dbo].[University] ([UnivtyId], [UniversityName]) VALUES (2, 'University of Chicago')
GO
INSERT [dbo].[University] ([UnivtyId], [UniversityName]) VALUES (3, 'Stanford University')
GO
INSERT [dbo].[University] ([UnivtyId], [UniversityName]) VALUES (4, 'University of Oxford')
GO
INSERT [dbo].[University] ([UnivtyId], [UniversityName]) VALUES (5, 'The University of Arizona')
GO
INSERT [dbo].[University] ([UnivtyId], [UniversityName]) VALUES (6, 'The University of Melbourne')
GO
INSERT [dbo].[University] ([UnivtyId], [UniversityName]) VALUES (7, 'University of Cambridge')
GO
SET IDENTITY_INSERT [dbo].[University] OFF
GO