Web Analytics Made Easy - Statcounter
Derived tables:-  Derived tables are the tables which are created on the fly with the help of the Select statement. It is different from the temporary table in the way that  in case of temporary table, first we have to create a temporary table,  insert the data into the table, select the data from the temporary table and then we have to drop the temporary table. But in case of derived table, SQL Server itself create and populate the table in the memory and we can directly use it. Also we don,t need to drop it. But it can only be referenced  by the outer Select query who created it. Also since it is reside in the memory itself, it is faster then Temporary tables which are created in the temp database.Suppose, there is a table say tbl_studentcoursereg to store the id of the students registered for various courses, registration month and date. Table structure is given below:-

create table tbl_studentcoursereg (id int identity(1,1),  studentid int, coursename nvarchar(100), Regmonth nvarchar(50), regdate datetime)

The insert command to populate this table with the data is given below:-

insert into tbl_studentcoursereg
values (1, ‘C#’, ‘JAN’,’01/01/2012′)

insert into tbl_studentcoursereg
values (2, ‘SQL’, ‘JAN’,’01/02/2012′)

insert into tbl_studentcoursereg
values (3, ‘C++’, ‘JAN’,’01/03/2012′)

insert into tbl_studentcoursereg
values (4, ‘C#’, ‘FEB’,’02/02/2012′)

insert into tbl_studentcoursereg
values (5, ‘C#’, ‘MAR’,’03/03/2012′)

insert into tbl_studentcoursereg
values (6, ‘JAVA’, ‘APR’,’04/04/2012′)

insert into tbl_studentcoursereg
values (7, ‘JAVA’, ‘APR’,’04/04/2012′)

insert into tbl_studentcoursereg
values (8, ‘ORACLE’, ‘APR’,’04/04/2012′)

insert into tbl_studentcoursereg
values (9, ‘C#’, ‘APR’,’04/23/2012′)

insert into tbl_studentcoursereg
values (10, ‘C#’, ‘MAY’,’05/05/2012′)

Now if we want to know in which month number of students registered for a particular months is greater than 2, the we can use the following query which uses the derived table.

select regmonth, totalstud, totalcourse from
(select  regmonth ,count(studentid) as totalstud ,
count(coursename)as totalcourse from tbl_studentcoursereg group by regmonth )as tbl1  where tbl1.totalstud>2

 

In the above query, the following is the derived table:-

 

(select  regmonth ,count(studentid) as totalstud ,
count(coursename)as totalcourse from tbl_studentcoursereg group by regmonth ) as tbl1

In case of derived table, we have to give the alias name to the derived table as tbl1 is the alias for the above mention derived table.

 

DMCA.com


Discover more from Technology with Vivek Johari

Subscribe to get the latest posts sent to your email.

By vivekjohari

I am currently working as a Senior Database Professional and have around 18 years of experience in database. Degree:- Master Degree in Computer(MCA) Certification course in Data Science & Machine Learning from Indian Institute of Technology (IIT), Delhi Work experience:- Designing of the database. Database Optimization. Writing Complex Stored Procedures,Functions,Triggers etc. Designing and developing SSIS & DTS packages. Designing SQL Reports using SSRS. Database Server Maintenance. Certification:- MCTS: DA-100: Analysing Data with Microsoft Power BI MCTS: DP-300: Administering Relational Databases on Microsoft Azure Microsoft certified Sql DBA in Sql server 2008 (MCTS). Microsoft certified BI professional in Sql server 2008 (MCTS). Oracle certified profession DBA in ORACLE 10g (OCP) certified profession DBA in ORACLE 9i (OCP) My other publication Technical Blog:- Technologies with Vivek Johari Guest Author and Blogger at sqlservercentral.com

31 thought on “Derived Table In Sql Server”
  1. One thing which is really important for programmers to understand in case of derived table is that, essentially derived tables are nothing but sub-queries which is given an alias name using AS keyword. While sub-queries are generally used in where clauses the derived tables are always used in from clause of a SQL query.

  2. Hi Vivek,

    Thanks for explaining the concept in an understandable manner.

    Also I would like to add a point here: Derived tables does not have scope like a batch or a session but it ends right after the execution within the outer query from which it is derived.

    Regards
    Varun

  3. … [Trackback]

    […] Here you can find 70410 more Information to that Topic: techmixing.com/2012/04/derived-table-in-sql-server.html […]

Leave a Reply

Discover more from Technology with Vivek Johari

Subscribe now to keep reading and get access to the full archive.

Continue reading