SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


SQL - Derived Tables


SQL - Derived Tables

Author
Message
Prashant Pandey
Prashant Pandey
Old Hand
Old Hand (337 reputation)Old Hand (337 reputation)Old Hand (337 reputation)Old Hand (337 reputation)Old Hand (337 reputation)Old Hand (337 reputation)Old Hand (337 reputation)Old Hand (337 reputation)

Group: General Forum Members
Points: 337 Visits: 128
Comments posted to this topic are about the item SQL - Derived Tables

Thanks,
Prashant
Rob Hershfield
Rob Hershfield
SSC-Enthusiastic
SSC-Enthusiastic (128 reputation)SSC-Enthusiastic (128 reputation)SSC-Enthusiastic (128 reputation)SSC-Enthusiastic (128 reputation)SSC-Enthusiastic (128 reputation)SSC-Enthusiastic (128 reputation)SSC-Enthusiastic (128 reputation)SSC-Enthusiastic (128 reputation)

Group: General Forum Members
Points: 128 Visits: 86
I try to use derived tables whenever possible, but I recently improved performance by changing a derived table to a temp table. It was large and joined on a non-index column so I found that a temp table with an index on the join fields.



David A. Long
David A. Long
Hall of Fame
Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)

Group: General Forum Members
Points: 3445 Visits: 237
Great article.

We have been using the derived table as a great complex query design tool:

Break the complex query into derived tables, generally by the specific table's restrictions.

Then JOIN the individual derived table queries into the complex query.

BTW here id the fix for the original query:

SELECT C.CustomerID, C.CompanyName
, COUNT(O.OrderID) AS TotalOrders
FROM Customers C
LEFT OUTER JOIN Orders O ON C.CustomerID = O.CustomerID
AND O.OrderDate >= '19960101'
AND O.OrderDate < '19970101'
GROUP BY C.CustomerID, C.CompanyName

The last step is to evaluate for optimization.

A suggestion is no try and not use functions in the where clause as that can cause any available index to be skipped.

Andy



corey lawson
corey lawson
UDP Broadcaster
UDP Broadcaster (1.4K reputation)UDP Broadcaster (1.4K reputation)UDP Broadcaster (1.4K reputation)UDP Broadcaster (1.4K reputation)UDP Broadcaster (1.4K reputation)UDP Broadcaster (1.4K reputation)UDP Broadcaster (1.4K reputation)UDP Broadcaster (1.4K reputation)

Group: General Forum Members
Points: 1443 Visits: 588
"Theta Joins" also can be used.

For the given example in the article, the results should match for this query:

select c.*, o.*
from customers as c
left join orders as o
on c.customerid = o.customerid and year(o.orderdate) = 1996
--where
-- ...
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (340K reputation)SSC Guru (340K reputation)SSC Guru (340K reputation)SSC Guru (340K reputation)SSC Guru (340K reputation)SSC Guru (340K reputation)SSC Guru (340K reputation)SSC Guru (340K reputation)

Group: General Forum Members
Points: 340605 Visits: 42644
...and everything happens in memory instead of a combination of memory and disk.

...while a temporary table exists until the server is brought down and also it uses lot of disk space than a derived table in the temdb database.


You wrote a very good introduction to the use of Derived Tables... but you really need to check your resources on the two statements above....

First, Derived Tables are NOT memory only constructs... just like any query, if they run out of room in memory, they WILL make a thing called a "Working" table and you can frequently see them in the text version of execution plans. Guess where those bad boys live... you guessed it... memory if they fit and if they don't, TEMPDB!

Also, your information about Temp Tables living only on disk is dead wrong. Please read the following Microsoft provided URL...

http://support.microsoft.com/default.aspx?scid=kb;en-us;305977&Product=sql2k

... and pay particular attention to where it states "If memory is available, both table variables and temporary tables are created and processed while in memory (data cache). "

You also need to check Books Online for what the persistance of a Temp Table actually is... you DON'T need to bring the server down to get rid of a Temp Table. Wink

Don't let this dampen your writing spirit... you provided a really good intro to derived tables... I just had to correct your statements about temp tables. In fact, there are some occasions where a Temp Table will blow the doors off of derived tables... usually in a multi-table-joined update when aggragates are necessary. They work pretty well as a substitute for CTE's, too!

--Jeff Moden

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
tony rogerson
tony rogerson
SSC-Enthusiastic
SSC-Enthusiastic (146 reputation)SSC-Enthusiastic (146 reputation)SSC-Enthusiastic (146 reputation)SSC-Enthusiastic (146 reputation)SSC-Enthusiastic (146 reputation)SSC-Enthusiastic (146 reputation)SSC-Enthusiastic (146 reputation)SSC-Enthusiastic (146 reputation)

Group: General Forum Members
Points: 146 Visits: 76
Derived tables are a great performance optimisation tool but always check because the derived table approach is not always the best and temporary tables are sometimes a necessary approach.

The derived table itself is just expanded into the main query (just like a view) this means that there are no statistics available for the derived table.

Statistics are held for the temporary table; you can also create indexes on temporary tables.

I use derived tables a lot, well, one hell of a lot to be honest and they are a good structure but also look at CTE's which is what I'm using more - they can be referenced more like a virtual table in your queries.

Tony.
pain_killer
pain_killer
SSC Journeyman
SSC Journeyman (76 reputation)SSC Journeyman (76 reputation)SSC Journeyman (76 reputation)SSC Journeyman (76 reputation)SSC Journeyman (76 reputation)SSC Journeyman (76 reputation)SSC Journeyman (76 reputation)SSC Journeyman (76 reputation)

Group: General Forum Members
Points: 76 Visits: 28
David A. Long (1/16/2008)
Great article.

BTW here id the fix for the original query:

SELECT C.CustomerID, C.CompanyName
, COUNT(O.OrderID) AS TotalOrders
FROM Customers C
LEFT OUTER JOIN Orders O ON C.CustomerID = O.CustomerID
AND O.OrderDate >= '19960101'
AND O.OrderDate < '19970101'
GROUP BY C.CustomerID, C.CompanyName

Andy


I think it also do the job:

SELECT C.CustomerID, C.CompanyName
, COUNT(O.OrderID) AS TotalOrders
FROM Customers C
LEFT OUTER JOIN Orders O ON C.CustomerID = O.CustomerID AND
YEAR(O.OrderDate) = 1996
--AND O.OrderDate >= '19960101'
--AND O.OrderDate < '19970101'
GROUP BY C.CustomerID, C.CompanyName

OR:

SELECT C.CustomerID, C.CompanyName, COUNT(O.OrderID) AS TotalOrders
FROM Customers C LEFT OUTER JOIN Orders O ON
C.CustomerID = O.CustomerID
WHERE YEAR(O.OrderDate) = 1996
GROUP BY ALL C.CustomerID, C.CompanyName
daninmanchester
daninmanchester
SSC-Addicted
SSC-Addicted (488 reputation)SSC-Addicted (488 reputation)SSC-Addicted (488 reputation)SSC-Addicted (488 reputation)SSC-Addicted (488 reputation)SSC-Addicted (488 reputation)SSC-Addicted (488 reputation)SSC-Addicted (488 reputation)

Group: General Forum Members
Points: 488 Visits: 58
I would tend to put some of the criteria in the FROM clause in a lot of cases rather than move straight to derived tables. Although in complex situations derived tables can help simplify things. But if the derived table is of particular value and reusable i will often put it in a view anyway.

pain_killer , the point about NOT using the Year() function is that it will make better use of any index.

Dan Bayley
affordable website design UK
Free Google and Yahoo sitemap generator
humbleDBA
humbleDBA
Ten Centuries
Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)

Group: General Forum Members
Points: 1360 Visits: 1509
I'm afraid I'm with Jeff Moden and Tony Rogerson on this.

I think this is a good example of needing to be careful and understand the implications of what you are saying, and to have covered your research well. There is plenty of info out there on this area from 'good' sources to allow you to draw a conclusion(s) that you can back up (right or wrong, subjectively of course Wink )

That said though, I would like to mirror Jeff's comments of not letting the replys dampen your spirit. You have put yourself forward for peer review and from my own experiences its a great way to gain experience and knowledge, and to improve, though it can seem quite painful along the route Crying .
Josep
Josep
Mr or Mrs. 500
Mr or Mrs. 500 (574 reputation)Mr or Mrs. 500 (574 reputation)Mr or Mrs. 500 (574 reputation)Mr or Mrs. 500 (574 reputation)Mr or Mrs. 500 (574 reputation)Mr or Mrs. 500 (574 reputation)Mr or Mrs. 500 (574 reputation)Mr or Mrs. 500 (574 reputation)

Group: General Forum Members
Points: 574 Visits: 4680
And why not using this sentence:

SELECT C.CustomerID, C.CompanyName,
COUNT(O.OrderID) AS TotalOrders
FROM Customers C LEFT OUTER JOIN Orders O ON
C.CustomerID = O.CustomerID AND (YEAR(O.OrderDate) = 1996 OR O.OrderDate IS NULL)

GROUP BY C.CustomerID, C.CompanyName
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search