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
- Passing an "in" list via stored procedure
- SQLDataReader is not returning any data
- Is there any performance gain from CommandBehavior.SequentialAccess?
- SSIS Data Flow task providing poor performance when using a Where Clause
- Full text search with CONTAINS is very slow
- Products database design for product lines, categories, manufacturers, related software, product attributes, etc
- Fast way to copy rows from one table into another
- SQL Server and CLR, batching SqlFunction
- mssql-cli connect to local server server container error occurred during the pre-login handshake
- Query to get content older than 3 weeks
- Cancel long running query
- Find all tables that have ROWGUIDCOL
- SQL prints float with large decimal place
- Why Backup database is terminating abnormally in WPF with c#?
- Gaps and islands SQL error
- maintaining a custom auto increment column
- Accessing oracle database tables from ms access using pass through query
- Handling inheritance with overriding efficiently
- Executing same code via exec SP versus exec SP code in query window report same results but different execution times
- SqlDataReader not executing correctly in asp.net page using C#
- How to delete items from a select query?
- Special characters shows as "??" in php sqlsrv query
- 100 Join SQL query
- Decimal places not rounding correctly - is this a LINQ to SQL bug?
- Group a pivoted table
- Test connection failed in SSIS
- LINQ query for grouping a table based on the values from another table
- How to get last 24 hours from current time-stamp?
- Unknown number of child and grandchild elements in an XQuery
- Precision equal to scale in MS SQL-Server
- SQL Select where all joined values equal value
- Executed Stored Procedure using Dapper
- SQL query not running where column name is reserved word
- RODBC Temporary Table Issue when connecting to MS SQL Server
- Alter Non Clustered Index in SQL Server to Add more included columns
- How - create and use database directly after creation in SQL Server?
- Performance difference between Primary Key and Unique Clustered Index in SQL Server
- Why does my auto-incremented Id skip numbers in SQL Server?
- How do I publish an alteration to a replicated stored procedure (SQL 2000)?
- Using WCF service to handle SQL queries
- SQL Server 2008 - Does a trigger run with the same permissions as the login/user?
- select last row from details table for each user
- Show Row Count in SQL Profiler
- Pass array on to sql and insert it in link table with one value
- How to insert sequential numbers in primary key using select subquery?
- SQL Server named instance in Docker
- Please explain this nested query
- SQL Server: Create index on view
- Why does time conversion throw error ?
- SQL Server Management Studio error "Saved settings file cannot be found"
- Suspended Invoke-SQLcmd Query - Database in use
- Select query with constant variable
- Calculation to avoid negative value in database
- What goes between SQL Server and Client?
- Using Max with Date Values with or without the Nulls
- RIGHT\LEFT Join does not provide null values without condition
- Stored procedure parameter contains multiple values
- Bracketing where clause in multiple use of AND & OR
- SQL Server Import Wizard "workgroup information file" error when importing Access .mdb
- How to select group of codes and represent a specific weight in SQL?
- calling SP from within another SP gives different result
- How to keep history of SQL Server stored procedure revisions
- SQL hidden techniques?
- Hide other databases for particular user in azure sql server
- SQL Server removing entries with duplicated information
- How Entity Framework works
- How to get the user last login date and time in SQL Server?
- Using case when - Is this possible?
- What is livelock exactly? In SQL when/how does it happen? Anything T-SQL developer can do to avoid it?
- How to return only the year and month from a SQL Server DateTime datatype
- How to subtract two dates with criteria (if data is not existing in another table, use date today, if existing, use the date in there)?
- Transposing and identifying the cell with a flag
- Simple SQL Query to bring back null if no match found
- Replacing escape characters around table-names, column-names in an SQL query
- DACPAC not deploying to Azure SQL Database V12
- Write Access Client App for SQL Server 2005 Backend
- Delete records only older than 50 rows while grouping by 1 column
- How Bulk insert in Sql
- How much is the overhead of network traffic when I use SELECT FOR XML to transfer 5000 records
- Access 2007 SQL query to ignore 0 value
- select statement where clause two conditions
- Cascade delete in Eclipselink in a self-referencing table
- Join certain rows to the results
- Inserted records missing
- Comparing against database value
- Returning 1 or 0 instead of value or null in T-SQL
- Anybody know why SQL Server 2005 throws "'SQLOLEDB' failed with no error message available, result code: E_FAIL(0x80004005). "?
- Cannot connect to SQL Database on Docker
- Error connecting to MS SQL server
- DateDiff Or Function?
- SQL. How to insert one select into another select with the ignoring of duplicates and the with changing of Foreign Key in a copy
- Cross Tab - Storing different dates (Meeting1, Meeting2, Meeting 3 etc) in the same column
- Can't generate scripts from LocalDB in SQL Management Studio
- SQL Server Replication : Removing broken subscriptions at the publisher
- Get RETURN value from stored procedure in SQL
- Between two date , how can i take all date with one id
- SQL Azure IaaS encryption using Key Vault and a certificate
- Modify Stored procedure parameter value
- t-sql Different datatype possible in a case?
- Full Join diff left + right joint
- SSRS Report timing out after 10 minutes
- Express Router :id
- UNION ALL and NOT IN together
- Stored Procedure Default Value
- Python SQL update query: string or integer address expected instead of instance instance
- Adding one more join kills performance
- SQL Server Management Studio 2014. Object Model (Smo)
- Getting SQLFetch Return Code 1004 (Right Truncation) on a query with no parameters
- Store result set in memory over multiple queries?
- SQL Server Bug?
- How to fire trigger in SQL Server if in a same row 2 column value are same?
- Equivalent of this SQL query in LINQ using Lambda
- SQL - AND condition in WHERE clause
- Find all occurrences of sp_send_dbmail in a sql server 2008R2 database
- Dapper Dynamic Parameters with Table Valued Parameters
- on cascade delete on a table with two FK to the same table
- Calling Stored Procedure on Table Update T-SQL
- How to refresh the definition of a T-SQL user-defined function after a dependent object is redefined?
- Ruby on Rails and MSSQL Server: trying to insert rows with newid() default values
- T-SQL Value from 1st select on each row in 2nd select of a union all
- Is the inner SELECT statement replaceable by a JOIN statement?
- Fast way to "flatten" hierarchy table?
- How to import XML to SQL Server when base nodes repeat
- SQL Server AVG function oddity
- Connection to Microsoft SQL server error
- Connection.close() does not closes the connection, it make it sleep forever?
- Bulk import users into SQL Azure
- SQL Server: Calculate amortization
- Alternative to DISTINCT Function
- SQL SERVER 2012 datatype error
- Seek row in SQL Server
- Multi SQL statements executed via ADO.NET command object
- Using a Common Table Expression for a simple select
- MERGE DELETE only based on parent id
- How to configure Squirrel SQL for SQL Server Windows authentication
- Update ParentId of Records Where Three Conditions Met on SQL Server table
- Get count of total employees under each manager (SQL Server)
- Alter column charset and collation MS SQL 2005
- INSERT INTO specific ROW - Insert into specific columns of a specific row?
- SQL Server does not recognize table even after being refreshed
- Grid view not showing the first row ASP.NET , C# Sql server
- Check if the first or second condition exists
- Showing values in two different colums based off a joining ID
- Synchronization between two databases
- Failed password attempt - How do i code that in TSQL
- Recursively get nested URLs from database
- SQL selecting all that matches
- Create optional-optional relationship in EF6 with PK instead of FK
- Sql queries returning different results in different tabs
- How to calculate the average per day for different years