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

Uses For Derived Tables Expand / Collapse
Author
Message
Posted Friday, February 1, 2002 12:00 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Tuesday, November 5, 2013 9:05 AM
Points: 976, Visits: 59
Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/rmarda/derivedtablebasics.asp



Robert W. Marda
SQL Programmer
Ipreo
Post #2451
Posted Monday, February 4, 2002 5:34 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 1:24 AM
Points: 2,908, Visits: 1,833
The only down side I have found with derived tables is that they are memory intensive.

This becomes an issue if you have an application that runs complicated queries for a couple of hundred users.




LinkedIn Profile
Newbie on www.simple-talk.com
Post #27326
Posted Monday, February 4, 2002 7:51 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Tuesday, November 5, 2013 9:05 AM
Points: 976, Visits: 59
Yes, derived tables require more memory than temp tables or possibly other ways to get the same reults. With enough memory you can take advantage of the derived tables without a problem. We have 2 production servers for our web site and 1 for our employees. Our SQL Servers that serve our web site have 2GB each and each one takes about half the load of our 700 plus clients. Our employees only production server has 1 GB of memory. So far we have not had a problem with memory.

Robert Marda




Robert W. Marda
SQL Programmer
Ipreo
Post #27327
Posted Monday, February 4, 2002 9:08 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Tuesday, May 29, 2012 11:22 AM
Points: 1,755, Visits: 4,652
Good article. I have 1 question...

Can someone please tell me what's wrong with this?


select
State,
UserCount = count(distinct c.UserID),
ComponentCount = count(u.LocationID)
from
#Locations l
inner join #Users u on u.LocationID = l.LocationID
inner join #Components c on c.UserID = u.UserID
group by
state


For me it gives...


State UserCount ComponentCount
----- ----------- --------------
MD 2 6
VA 2 4


I'm using SQL 2000.

Many Thanks.

Ryan.

Edited by - RyanRandall on 02/05/2002 11:10:31 AM



Ryan Randall

Solutions are easy. Understanding the problem, now, that's the hard part.
Post #27328
Posted Monday, February 4, 2002 10:26 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Tuesday, November 5, 2013 9:05 AM
Points: 976, Visits: 59
Excellent! That works and eliminates the need for a derived table in the first example I used in my article. I wish I saw that before writing my article. I'll need to modify it a bit though. I have used COUNT(DISTINCT <column_name>) in other situations, but it didn't occur to me to use it in this case. I hope you and others can still find benefit in the article.

Robert Marda




Robert W. Marda
SQL Programmer
Ipreo
Post #27329
Posted Tuesday, February 5, 2002 9:57 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Tuesday, November 5, 2013 9:05 AM
Points: 976, Visits: 59
Thanks! I'm glad you liked it. My goal is to deliver information that is useful in an easy to understand way. If I succeed in this then I am satisfied.

Robert Marda




Robert W. Marda
SQL Programmer
Ipreo
Post #27331
Posted Friday, February 6, 2004 2:39 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, June 26, 2006 5:33 PM
Points: 1, Visits: 1

Thanks. Just what I was looking for. It cleared up an issue I was currently working on.

Jim Crosby

Post #99013
Posted Friday, February 6, 2004 5:19 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, April 29, 2014 12:55 PM
Points: 169, Visits: 112

In example 2, couldn't this be used also?

DECLARE @orderby int

SET @orderby = 1

SELECT l.State, ComponentName
FROM #Locations l
INNER JOIN #Users u ON l.LocationID = u.LocationID
INNER JOIN #Components c ON c.UserID = u.UserID
GROUP By l.State, ComponentName
ORDER BY CASE @orderby
             WHEN 1 THEN l.State
             WHEN 2 THEN ComponentName
             END

I do like the changing of the order by with a variable.  Definitely a nice little trick.

Pat





Pat Buskey
Post #99032
Posted Friday, February 6, 2004 11:35 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, May 20, 2010 9:22 AM
Points: 11, Visits: 35

Robert,

As this thread shows there are often several valid approaches to generating a desired result set. In one of my applications, users often wish to include/exclude all members of a household when one or more member qualifies based on selected criteria. Below is a simple example that uses derived tables to find members of a household with an opinion ='No' where at least one other household member has an opinion of 'Yes':

-- paste into QA
CREATE TABLE test
(ID bigint identity primary key, FirstName varchar(15), LastName varchar(20), Household char(1), Opinion varchar(10))

INSERT INTO test (FirstName, LastName, Household, Opinion)
VALUES ('Joe', 'Jones', 'A','Yes')
INSERT INTO test (FirstName, LastName, Household, Opinion)
VALUES ('Sam', 'Jones', 'A','No')
INSERT INTO test (FirstName, LastName, Household, Opinion)
VALUES ('Mary', 'Jones', 'A','Yes')
INSERT INTO test (FirstName, LastName, Household, Opinion)
VALUES ('Joe', 'Smith', 'B','Yes')
INSERT INTO test (FirstName, LastName, Household, Opinion)
VALUES ('Sam', 'Smith', 'B','No')
INSERT INTO test (FirstName, LastName, Household, Opinion)
VALUES ('Mary', 'Smith', 'B','No')

/* statement shows fields from both 'test' and derived table
'hh' to illustrate the technique. Note that the DISTINCT keyword is used because each member of a household in 'hh' with an option of 'Yes' returns all records from 'test' where Opinion='No', resulting in duplicates.
*/

SELECT DISTINCT * FROM test
INNER JOIN test as hh ON test.Household = hh.Household
WHERE hh.Opinion='Yes' and test.Opinion='No'

-- David Terrie




Post #99156
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse