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


Uses For Derived Tables


Uses For Derived Tables

Author
Message
Robert W Marda
Robert W Marda
SSCommitted
SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

Group: General Forum Members
Points: 1741 Visits: 111
Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/rmarda/derivedtablebasics.asp

Robert W. Marda
Billing and OSS Specialist - SQL Programmer
MCL Systems
David.Poole
David.Poole
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10039 Visits: 3337
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
Robert W Marda
Robert W Marda
SSCommitted
SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

Group: General Forum Members
Points: 1741 Visits: 111
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
Billing and OSS Specialist - SQL Programmer
MCL Systems
RyanRandall
RyanRandall
SSCrazy
SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)

Group: General Forum Members
Points: 2693 Visits: 4652
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.
Robert W Marda
Robert W Marda
SSCommitted
SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

Group: General Forum Members
Points: 1741 Visits: 111
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>Wink 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
Billing and OSS Specialist - SQL Programmer
MCL Systems
Robert W Marda
Robert W Marda
SSCommitted
SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

Group: General Forum Members
Points: 1741 Visits: 111
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
Billing and OSS Specialist - SQL Programmer
MCL Systems
Jim Crosby
Jim Crosby
Forum Newbie
Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)

Group: General Forum Members
Points: 1 Visits: 1

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

Jim Crosby


bustell
bustell
SSC Veteran
SSC Veteran (283 reputation)SSC Veteran (283 reputation)SSC Veteran (283 reputation)SSC Veteran (283 reputation)SSC Veteran (283 reputation)SSC Veteran (283 reputation)SSC Veteran (283 reputation)SSC Veteran (283 reputation)

Group: General Forum Members
Points: 283 Visits: 123

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
dterrie
dterrie
Grasshopper
Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)

Group: General Forum Members
Points: 23 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





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