Technology With Vivek Johari

Getting Started with Adobe After Effects - Part 6: Motion Blur


	Syndicated Blogs

Upload Image

Close it

Select File

This blog contains various articles on the SQL Server. Also this blog contains various interview questions on the SQL Server. Through this blog I try to make the learning of SQL Server/ BI and SQL DBA very easy and in a quick time.


	Like

Invite Friends

Browse by Tags · View All

CodeProject	6
Sql Server	4
Having Clause	3
Interview Questions on sql server	3
SQLServerPediaSyndication	3
Happy New Year	1
Rebuild And Reorganization of Indexes	1
Replication in SQL Server	1
Fragmentation in SQL Server	1
Log Shipping in SQL Server	1

Archive · View All

February 2012	20
December 2012	5
April 2012	4
August 2012	2
November 2010	2
November 2012	1
June 2012	1
May 2011	1
November 2011	1
October 2011	1

Home · Most Liked · Most Discussed · Fans (27) · Preferences

Derived Table In Sql Server

Apr 15 2012 12:00AM by Vivek Johari

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.

Previous: Adding , Deleting and Updating a Column in a table
Next: Merge Command In Sql Server

Republished from Technology with Vivek Johari [10 clicks]. Read the original version here [4 clicks].

Vivek Johari

115 · 1% · 445

Liked

Lifesaver

Refreshed

Learned

Incorrect


Like this
Post this to my facebook wall
Tweet this
This was a true life saver
Thanks, this helped me to refresh my memory
Very informative, I just learned something new
I disagree, this information is incorrect


	Submit

1 Comments

Hi, There's a lot to say about derived tables, just thought I'd mention this since it's the same thing but written in a different way:

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

Here tbl1 is called a "common table expression" , using this can make the query a lot more readable because of the separation of the derived table from the rest, so you'll be using tbl1 just like it were a temporary table

Also, it would be a good idea to use a more logical name in stead of tbl1, e.g. TotalsPerMonth, so the query will look more like plain English

commented on Apr 21 2012 3:32AM

Technology With Vivek Johari

Derived Table In Sql Server

1 Comments

Your Comment