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.
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.
Read More about temp table read below post
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.
Read More Articles
- Linq to SQL Group by and Sum in Select | Linq To SQL Group By and Sum
- How send an HTTP POST request to a server from Excel using VBA?
- What is Difference between where and having clauses in SQL server
- How to Use EXCEPT Operator with an example in Sql
- How to use merge statement in the SQL server
- How to write Re-runnable SQL server query
- How to create Cursor in sql server with example
- How to generate random data in sql server for performance testing
- How to write subquery in select statement in Sql
- How to Log record changes in SQL server in an audit table
- Logon Trigger Example to Restrict Access in Sql Server
- DDL Triggers In Sql Server with Example | Database and Server Scoped Triggers
- How to achieve ACID properties with Example in Sql Server
- What is transaction in sql server with example
- Instead Of Delete Trigger In Sql Server With Example
- Instead Of Update Trigger In Sql Server With Example
- Instead Of Triggers in Sql server With Example
- After Update Trigger in Sql Server With Example
- Triggers in SQL server with real-time example
- C# -Saving a base64 string as an image into a folder on server in Web Api
- what are the advantages and disadvantages of indexes-Sql Server?
- Sql Server- Difference between unique and non unique index
- Temporary Tables in sql server with real time example
- Clustered and Non clustered index in sql server with real example
- How does Recursive CTE works in Sql server?
- How to update table using CTE in sql server
- How to use group by in SQL Server query?
- Sql Server pivot rows to columns
- What is CTE in sql server with example
- SQL Server – -Simple way to transpose rows into columns
- What is Sql Joins? With Realtime Examples
- Difference between Inner Join ,Left Join and Full Join-Sql
- How to get Employee manager hierarchy in Sql ?
- How to create Inline table-valued functions in SQL server
- Scalar User-defined functions In sql server-Step by Step
- [Solved]-How To Update a Table using JOIN in SQL Server?
- Create Stored procedure with Output and Input parameters in SQL
- [Solved]-Best Sql server query with pagination and count
- [Solved]- find records from one table which don’t exist in another Sql
- [Solved]-How to concatenate text from multiple rows into a single text string in SQL server?