Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««1234»»»

SQL - Derived Tables Expand / Collapse
Author
Message
Posted Wednesday, January 16, 2008 5:43 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, November 16, 2012 4:53 AM
Points: 2, 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 me:). 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).
Post #443549
Posted Wednesday, January 16, 2008 5:55 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, July 28, 2014 1:28 AM
Points: 14, Visits: 70
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.



Post #443551
Posted Wednesday, January 16, 2008 6:19 AM
UDP Broadcaster

UDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP Broadcaster

Group: General Forum Members
Last Login: 2 days ago @ 3:55 PM
Points: 1,473, Visits: 575
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.
Post #443568
Posted Wednesday, January 16, 2008 6:24 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, January 8, 2014 2:03 PM
Points: 3, 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
Post #443572
Posted Wednesday, January 16, 2008 6:43 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 8:02 PM
Points: 35,397, Visits: 31,955
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #443590
Posted Wednesday, January 16, 2008 6:47 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, July 29, 2013 9:14 AM
Points: 12, 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
Post #443595
Posted Wednesday, January 16, 2008 6:48 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, August 25, 2014 12:25 PM
Points: 1, Visits: 220
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
Post #443596
Posted Wednesday, January 16, 2008 7:24 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 8:02 PM
Points: 35,397, Visits: 31,955
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #443626
Posted Wednesday, January 16, 2008 7:33 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: 2 days ago @ 12:32 PM
Points: 508, Visits: 2,057
Thanks for the article. I know I don't use derived tables enough and now I feel armed to do so.
Post #443634
Posted Wednesday, January 16, 2008 7:57 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, February 21, 2013 8:12 AM
Points: 17, 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.
Post #443664
« Prev Topic | Next Topic »

Add to briefcase ««1234»»»

Permissions Expand / Collapse