rajesh

• Mar 7th, 2007

 

A derived table is one that is created on-the-fly using the SELECT statement, and referenced just like a regular table or view. Derived tables exist in memory and can only be referenced by the outer SELECT in which they are created. A simple use of a derived table is shown here.

SELECT * FROM (SELECT * FROM Sales) AS a

The inner SELECT produces a derived table and replaces a regular table or view. The key thing to remember when using derived tables is that you must always use an alias (e.g., AS a). The following shows the error produced when the alias is omitted.

SELECT * FROM (SELECT * FROM Sales) -- Results -- Server: Msg 170, Level 15, State 1, Line 3 Line 3: Incorrect syntax near ')'.

which is better view or derived tables?
i feel there is no difference.

When a view is referenced the SELECT of the view is
merged into the SELECT that refereces it, so it is really the same
thing.  Of course views save a lot of coding, once written are less
subject to errors, and can have permissions assigned.

but,

when considering logical data independence, security and 
 reusability as important aspects, using views may be preferred over writing 
 derived tables over and over again. 
 -- 

pat.mclean

 Profile Answers by pat.mclean Questions by pat.mclean

• Mar 23rd, 2007

 

Hi !

Just an addition,

1. Views play all their rollups at database level. Derived Tables will fetch values into BO's microcube & then will perform the rollup calculations, resulting more process time.

2.As earlier answer suggests, views follow vital security in complience with data design, managed by core database-guys. So it makes sure, the view design is much better & compatible, which may not be if it is a derived table, obviously not in all cases but still in certain scenarios.

I personally favor views over derived tables because they are faster than derived tables. hence improve query performance, compared to derived tables.

Regards,
Pat.

Pradeep Mishra

 Profile Answers by Pradeep Mishra 

• Jan 7th, 2010

 

Yes, it's too good.

But One more thing is there , We can use The @Function (@variable,@Prompt) in the derived Table ,but we can't do the Same thing in the Views.

So you can say that these are benefits are in the Derived Tables.