SQL - Derived Tables

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss content posted by Prashant Pandey » SQL - Derived Tables

35 posts, Page 2 of 4

««1 2 3 4 »»»

SQL - Derived Tables

Rate Topic

Display Mode

Topic Options

Author

Message

pain_killer

Posted Wednesday, January 16, 2008 5:43 AM

Forum 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

tony rogerson

Posted Wednesday, January 16, 2008 5:55 AM

Grasshopper

Group: General Forum Members
Last Login: Friday, March 09, 2012 3:05 AM
Points: 13, Visits: 57

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

Jimi Meyer

Posted Wednesday, January 16, 2008 6:19 AM

UDP Broadcaster

Group: General Forum Members
Last Login: 2 days ago @ 10:39 AM
Points: 1,462, Visits: 427

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

mkheli@gmail.com

Posted Wednesday, January 16, 2008 6:24 AM

Forum Newbie

Group: General Forum Members
Last Login: Friday, July 17, 2009 9:36 AM
Points: 1, Visits: 35

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

Jeff Moden

Posted Wednesday, January 16, 2008 6:43 AM

SSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 7:36 PM
Points: 32,931, Visits: 26,820

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."

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Post #443590

mcieslinski

Posted Wednesday, January 16, 2008 6:47 AM

Grasshopper

Group: General Forum Members
Last Login: Thursday, March 25, 2010 1:05 PM
Points: 12, Visits: 37

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

jldsql

Posted Wednesday, January 16, 2008 6:48 AM

Forum Newbie

Group: General Forum Members
Last Login: Friday, May 10, 2013 10:35 AM
Points: 1, Visits: 190

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

Jeff Moden

Posted Wednesday, January 16, 2008 7:24 AM

SSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 7:36 PM
Points: 32,931, Visits: 26,820

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

Post #443626

sing4you

Posted Wednesday, January 16, 2008 7:33 AM

SSC-Addicted

Group: General Forum Members
Last Login: 2 days ago @ 7:58 AM
Points: 447, Visits: 1,695

Thanks for the article. I know I don't use derived tables enough and now I feel armed to do so.

Post #443634

Reginald J Ray Jr

Posted Wednesday, January 16, 2008 7:57 AM

Grasshopper

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 »

35 posts, Page 2 of 4

««1 2 3 4 »»»

Permissions