Derived Table In Sql Server

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

About vivekjohari

Database Consultant with more than 11.5 years of experience in database designing & programming and DBA related activities.  Had good experience on different databases like SQL Server, MySQL & Oracle, Azure SQL &  Big Data.
This entry was posted in SQL Derived Table, SQL Server and tagged , . Bookmark the permalink.

31 Responses to 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. Pingback: top producer Tallahassee

  4. Pingback: crypto

  5. Pingback: istanbul escort

  6. Pingback: hidden wiki onion url

  7. Pingback: Top Rank promotions

  8. Pingback: magnetic cornhole board cover

  9. Pingback: บาคาร่า

  10. Pingback: 실시간바카라

  11. Pingback: Barber Shop in Irmo South Carolina

  12. Pingback: Home Health Care in Wilmington NC

  13. Pingback: Totojitu

  14. Pingback: 바카라사이트

  15. Pingback: interqq

  16. Pingback: huong dan dang ky w88

  17. Pingback: honda qq

  18. Pingback: Black ops 4 - 2 KILLS 1 SHOT

  19. Pingback: 918kiss/scr888/918kiss apk/918kiss ios/918kiss 2020/918kiss download

  20. Pingback: เงินด่วน

  21. Pingback: w88th

  22. Pingback: indobola

  23. Pingback: w88

  24. Pingback: Website

  25. Pingback: maailmanlaajuinen

  26. Pingback: Empire Market

  27. Pingback: Dream Market

  28. Pingback: indonesiaresep.id

  29. Pingback: rubiks.glove.co.il

  30. Pingback: Apollon Market

  31. Pingback: w88 casino

Leave a Reply