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


SQL - Derived Tables


SQL - Derived Tables

Author
Message
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
daninmanchester (1/16/2008)

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

Sorry, my english is very poor, and sometimes I don't understand what is the point of the problem, so please be patient to meSmile. In this case I had compare IO, TIME statistics and execution plans. YEAR() function and explicit date comparing in this case looks identical. So the question is: why use YEAR() function? I think that this looks clearer to me, and it uses only one parameter (2006 in this example).
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
Using columns in functions means SQL Server can no longer do a seek; instead it must do a scan.

If there was a unique index on CustomerID, orddate.

Say you had 1 million rows per year between 2000 and 2007 for customerid = 1234

Say you wanted the counts for customerid 1234 for year 2007

Using the function Year( orddate ) = 2007 means that all the rows from 2000 for that customerid in the index need to be searched because SQL Server does not know the result until it has read the data and performed the function on the column.

Using >= '20070101' and < '20080101' allows SQL Server to just seek straight to the start 20070101 and read from there.
Jimi Meyer
Jimi Meyer
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1581 Visits: 802
As others have said, this is generally a good article and the technique is very valuable, but you have many errors. The characterization of the locking in the temp table example is simply wrong. It implies that access to tempdb is single threaded. It fails to mention that there are locks in the database providing the data (shared, but still locks). "Everything happens in memory" only if the data being referenced is already there.
mkheli@gmail.com
mkheli@gmail.com
SSC Rookie
SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)

Group: General Forum Members
Points: 35 Visits: 38
Hi
You could update on driven table but in other way,
You were trying :
Update T SET Id=0 FROM (SELECT * FROM tt1) AS T
but you could use this as follow , will work fine :

update t set id =0
from tt1 as T where ...

You could add you where clause too, you could do that with complex driven tables too.

Mnaouar Ben Khelifa
www.mnaouar.bestilan.com
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: 340047 Visits: 42625
Hey Prashant...

You remember I said "don't let it dampen your writing spirit"? Here's why... like I said, you have some good information in your article and people have already cited your article as an example in at least one of the posts... see the following URL for what I'm talking about...

http://www.sqlservercentral.com/Forums/Topic441707-5-1.aspx#bm443531

--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
mcieslinski
mcieslinski
SSC Journeyman
SSC Journeyman (82 reputation)SSC Journeyman (82 reputation)SSC Journeyman (82 reputation)SSC Journeyman (82 reputation)SSC Journeyman (82 reputation)SSC Journeyman (82 reputation)SSC Journeyman (82 reputation)SSC Journeyman (82 reputation)

Group: General Forum Members
Points: 82 Visits: 38
Thanks for this article. I learned something new and I will be using derived tables in the future. I would not consider myself an expert but I am wondering if you could handle this scenario using a case statement as follows:

SELECT C.CustomerID,
C.CompanyName,
Sum(Case when YEAR(O.OrderDate) = 1996 then 1 else 0 end) AS TotalOrders
FROM Customers C
LEFT OUTER JOIN Orders O ON
C.CustomerID = O.CustomerID

My questions are:
1. would this produce the correct results?
2. are there efficiency/index issues with this approach?

Thanks for your help
jldsql
jldsql
SSC Rookie
SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)

Group: General Forum Members
Points: 45 Visits: 353
Microsoft documentation suggest that both derived tables and temporary table
will first use memory cache if available.

It then will use the temp database as needed in both cases depending on available cache.

I think the advantages and disadvantages are more dependant on other issues
such as indexing etc than the fact of using the temp database.

This is a confusing and often misrepresented area of discussion.

Jim
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: 340047 Visits: 42625
jldsql (1/16/2008)
Microsoft documentation suggest that both derived tables and temporary table will first use memory cache if available.

Jim


Jim... First, I absolutely agree with what you said... But, I also believe that when you're going to blow up someone's article by dropping the "Microsoft documentation" bomb on them, the courteous thing to do is to provide at least one reference URL (or BOL hint) that supports your claim that "Microsoft said"... like I did in my first post on this thread.

--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
sing4you
sing4you
Ten Centuries
Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)

Group: General Forum Members
Points: 1030 Visits: 2093
Thanks for the article. I know I don't use derived tables enough and now I feel armed to do so.
Reginald J Ray Jr
Reginald J Ray Jr
SSC-Enthusiastic
SSC-Enthusiastic (189 reputation)SSC-Enthusiastic (189 reputation)SSC-Enthusiastic (189 reputation)SSC-Enthusiastic (189 reputation)SSC-Enthusiastic (189 reputation)SSC-Enthusiastic (189 reputation)SSC-Enthusiastic (189 reputation)SSC-Enthusiastic (189 reputation)

Group: General Forum Members
Points: 189 Visits: 55
Derived tables are cool as far as being able to write elegant code and reduce the quantity of code. I started using them for these reasons.
I have been removing them since I realized that they are performance dogs for decent-sized tables. Table variables or temporary tables are much better from a performance aspect.
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