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
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
- How to query values from xml nodes?
- Connecting to different networks to access database information
- Trying to sync data from third party api
- Get Last table from SQL Server Stored Procedure
- Can't view full-text catalogs in SQL Server 2008 with Advanced Services
- Efficient way of doing "insert only if node with specific value does not exist" for an xml column
- SQL Update from One Table to Another Based on a ID Match
- simplify if statement with stored procedure
- My tricky SQL Update query not working so well
- Check contraint for two columns
- Create user table name with hibernate
- Origin of warning message in sql server
- How do I split a database column in to separate, query-able values?
- Azure SQL - Automatic Tuning with Geo-replication - Server in unspecified state and query store has reached its capacity limit
- Query to loop through one table and insert incrementally by group into another
- Select all second highest values only from temp table
- Reduced Row size in SQL hasn't reduced table size
- How to debug stored procedures with print statements?
- What Are The Best Practices For String Data Field Sizes?
- SQL server Date = @date but executionplan xml show it in inequality
- Placing a database within WinForm project and licensing options
- The multi-part identifier "inserted.Id" could not be bound while creating trigger
- SQL Server 2012: How to find common values in a column
- Incorrect syntax near 'BEGIN'. in TSQL
- Pass a variable to fully qualified name as database in a SQL query
- How to compare Null values from the database column
- Rebuilding a full text index in a trigger
- sql query to export row values from one SQL Server database to another
- How to update every other row from select query?
- TSQL querying xml with namespace
- Select all rows within a date range, with at least one row occurring in the last given month
- SQL Server 2008 - Full Text Search
- Update with inner join, update 2 columns from both tables
- Pulling last week M-F range from any current week day
- select command for foreign key within same table
- Checking DB for Duplicate Data (C#)
- SQL trigger not working in Azure Elastic DB
- how to sort result by giving different weight to each column?
- SQL Server generated "Text File" data importing in to postgressql using copy command
- Casting String to Money Value in SQL Server
- SQL ' ' do not get escaped
- VarBinary(max) updates very slow on SQL Azure
- How do multiple on statements work in a single inner join?
- SQL dot notation
- Error when combining IIF and 'Is Null'
- Is there a way to update any combination of columns on a table?
- sql job to stop duplicates
- How can I speed up a query that uses a cursor?
- Unable to reset stored procedure cache in SQL Server Management Studio 2008
- How to instrument a SQL Server 2012 database
- The multi-part identifier "column name" could not be bound issue
- Sql Search based on condtions
- Strategy for transferring db data between sql servers
- SQL missing indexes from select Databases
- SQL Server Default Instance: If you change host machine's name
- SQL Server restore the schema only
- SQL Union query - Multiple tables
- How do I "Install" a SQL Server Database to a Remote Server Without Physical Access?
- Is there any way to return null in JSON_VALUE instead of empty in SQL Server
- Count Query in Select Statement
- Denormalization of a table (Pivot?)
- Update data in a cursor row not yet fetched sql server
- How to download SSIS Packages from SSISDB
- How to display data to row wise in sql?
- How can I get a list of the columns in a SQL SELECT statement?
- SQL Server: Determining games made more than 2 years ago
- In SQL Server, How Do I Select All the Things From Another Table's Matching *TWO* Columns?
- Parse SQL canonical date with c#
- Invalid column name for SQL Server update-database
- Sorting in SQL query in ascending order
- Change column name in SQL Server 2008
- Unable to find the requested .Net Framework Data Provider. It may not be installed. - when following mvc3 asp.net tutorial
- MS SQL Server bit column exported as boolean
- I have the same column in multiple tables, and want to update that column in all tables to a specific value. How can I do this?
- SQL queries to MSSQL contains pauses even with MARS enabled
- Returning values from a stored procedure
- SQL server - Statistics and Recompiling
- How to combine a linq query dynamically?
- Split string from execute result
- Can I check what T-SQL statement had been executed in the database?
- How to convert nvarchar column to datetime type in MS SQL SERVER 2008 R2
- issue while using union clause with order by
- SQL Server - Update a whole column using Order By
- SQL Server constraint on delete
- Forcing SQL Server to pre-cache entire database into memory
- Distinct count of users grouped by a column and distributed across bins
- select a record from each group if it has given value in column otherwise any one record
- Get identity of last inserted row in Google Apps Script
- How to simply generate the CREATE SQL script for a table and data?
- How to return a specific column as an element with FOR XML AUTO clause
- select Data for only week days and calculate difference
- Running a T-SQL script under different credentials using SQLCMD.exe utility
- MS SQL and Java: cannot find table variable during call stored procedure
- Joining 2 tables with multile rows
- Keep a transaction open on SQL Server with connection closed
- What does SUM(var1 + var2) return?
- How to select all columns for rows where I check if just 1 or 2 columns contain duplicate values
- Cannot add Service-based database
- SSRS: Partial re-use of a MDX query? OR MDX as Parameter?
- Datediff function results in overflow only in WHERE clause?
- Using a Temp Table in a Dynamic Pivot
- Accessing Remote MSSQL Server from hosted linux PHP
- How do I create a table based on another table
- How To create Audit table in sql server ? i need to create each audit table or can manage single table all audit
- Is it possible to make a substring out of a Guid in T-SQL?
- Subtract two columns in a query for 2 given types, otherwise leave as is
- How to rename database file programmatically?
- In statement alternative
- Edit all views and stored precedures, find and replace?
- Cannot insert duplicate key in object (GetReparentedValue / hierarchyid)
- Stored procedure error - Converting varchar to int
- MSSQL error: "Script level upgrade for database 'master' failed ... upgrade step 'msdb110_upgrade.sql' encountered error 200, state 7, severity 25."
- SQL view with count and group by
- Create table by passing name as early defined and value assigned variable in SQL Server 2012
- SQL server how to apply restriction for each different sets and put the value as a column?
- Can't see created tables in Object Explorer - Microsoft SQL Management Studio
- Get groups that are exactly equal to a table
- Desktop Applications Using Java Connected with SQL database server
- Extracting Username\password from text\properties file in VBScript
- tSQLt - Handling multiple SQL Server result sets
- Cannot connect to SQL Server using SqlClient in a desktop application
- Can I call a Web Service from SQL Server 2005?
- Selecting records in SQL table if either JOIN statement is satisfied
- T-SQL Pivot with Custom Column names
- sql - date not in format specified
- ASP.NET MVC 5, Oracle.ManagedDataAccess and SQL DB
- Using .NET SqlTypes with MySQL
- SQL response is treated as column name, rather than content in Java
- SQL Server: convert html tags
- Update Command Executenonquery - Syntax Error
- Cascade delete in Eclipselink in a self-referencing table
- SQL querying multiple tables in one query
- SQL Trigger - Commit Update and CATCH Block of Procedure
- Can anyone tell me what the mistake is in the following osql syntax?
- How to create table with Simple.Data?
- Object properties return zero instead of null on web form
- How to add a user in an SQL Server database with the same rights as in another one on the same server?
- Specify an index fill factor using Fluent API?
- Find Substring in SQL
- sql server friday date from week number
- iReport 2.0.4 Connection - ClassNotFoundException
- sql where ActionID in Case x=0 then id in (1,2,3,4) when x=1 then id in (1,2) when x=2 id in (3,4)
- How to allow special characters in JSON_VALUE
- Need help with SQL join to a function
- How to manage order by in SQL server as compare to Sybase?
- powershell oracle query - dynamically assign reader object member
- SQLServer Regexp empty string matching
- Variable as numeric sent to stored procedure (SQL Server 2005)
- How to assign string values to numeric values in a SQL View
- SQL behaviour when executing queries with conjunctions