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

Welcome to Appsloveworld Technologies in the Session, we learn about Derived tables and common table expressions will compare these with other constructs that are available in SQL server like views, table variables, local and global temporary tables.

Let’s start with an example. I have a TblUniversity table which has got University_Id
and University name columns, TblStudent table here, which has got Id, StudentName, gender
and University_Id columns.

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

I want the University name and the total number of the student within that University. But then your list should only include those universities, which has got two or more students.

And obviously, to achieve this output, we have several ways, but then we will see how to do that using views.

so we are creating a view here, create view view_name “as” we are selecting the University name, University Id, and count (*) which would count the number of the student within that 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 TotalStudnent from TblStudent join with the University. And obviously between these two tables, University_Id the column that’s common.

we are joining those two tables on that column and you will have to group by University name, University_Id because we want the count and we have talked about views, Join and group by in very great detail in the below post.

So views get saved in the database and can be available to other queries and stored procedures as well. So if you are using view in other stored procedures and in other queries. Read More About Views.

What is a view? Disadvantages & Advantages of views

But then if you are using this as just this one time in this query, then then it doesn’t really make sense to have this view created. We can make use of other constructs that are available in SQL server like CTE, derived tables, temp tables, table variables, etc., which will be talking now.

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

So obviously, the logic to calculate the University name and TotalStudnent, it’s exactly the same. we are going to use that with other constructs that are available in the SQL server.

So here we are using temporary tables. so the query is the same select UniversityName, University_Id, count(*) as TotalStudnent.

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

So you might be wondering where did you define the columns that this temporary table has? when you use select into syntax what’s going to happen behind the scenes 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. You the rest of the logic is the same.

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

And remember, 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.

Meaning if I mean a temporary table in stored procedure A and if stored procedure A calls stored procedure B, then the temporary table that you have created and stored Procedure A will be available and stored procedure B.

So local temporary tables are visible in the current session and can be shared between nested stored procedure calls. whereas global temporary tables are visible to all the sessions and are destroyed when the last connection referencing that table is closed.

All right, now let’s look at how to do exactly the same thing, using a table variable.

Now, when we created the temporary table, we didn’t define the structure. The structure is automatically inferred based on the select statement that we are using, because you’re using select into, but then a menu using table variable. Here we are defining the structure explicitly.

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 at table variable are also created in the temp DB, most of the people think table variable is actually created in memory, which is not true, both temporary tables and table variables are created in the temp DB.

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

The advantage of using table variables is that you don’t have to drop it like template tables. But there are several other differences as well between table variables and temporary tables.

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

Now, look at this. When we created a table variable, we will have to define the columns and the data types for a table variable. But whereas if you are using a derived table, this table is derived so you don’t really 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 wrapped between these brackets, and then you’re saying 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, this query that you can see here now, what we have done is we used the keyword “WITH” and then you have 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

The post Differences Between Derived Table & CTE,Views,Temp Table and Temp Variable appeared first on Software Development | Programming Tutorials.

Ashok Patel

I'm a software engineer, having good experience in software programming web designing with great command on ASP.NET, React JS, Angular JS,.NET Core HTML5, JavaScript, T-SQL, JQuery. Also have great experience in Electronics and electrical engineers design.