DERIVED TABLE BASICS IN SQL SERVER 2005

A derived table is one that is created runtime using the select statement and referenced by the outer select statement like a normal table or a view.Derived tables are not saved permanently. Derived tables exists in memory of the SQL SERVER and can only be referred by the outer select in which they are created.

Example of a simple derived table is given below.

Select * from employee ( select top 5 * from employee ) as DerivedTable.

The inner query select produces a derived table and replaces a regular table.Important thing to notice here is whenever you are using a derived tables is that you must always use an alias. If you remove the alias the system will give you an error like incorrect syntax near ‘)’

You can also use derived tables to implement the database paging.Example is given below.

Declare @StartRowNum INT , @EndRowNum INT

SET @StartRowNum = 100

SET @EndRowNum  = 120

SELECT * FROM

(

SELECT id, EmpName , Salary , Row_number() over ( order by select 2 ) AS RowNumber From Employee

)

Employee

Where RowNumber >= @StartRowNum AND RowNumber <= @EndRowNum

You can also achieve the above functionality with common types expressions.( CTE )

Pawan Kumar

Pawankkmr@hotmail.com