February 1, 2002 at 12:00 am
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
February 4, 2002 at 5:34 am
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.
February 4, 2002 at 7:51 am
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
February 4, 2002 at 9:08 am
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.
February 4, 2002 at 10:26 am
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
Billing and OSS Specialist - SQL Programmer
MCL Systems
February 5, 2002 at 9:57 am
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
February 6, 2004 at 2:39 am
Thanks. Just what I was looking for. It cleared up an issue I was currently working on.
Jim Crosby
February 6, 2004 at 5:19 am
In example 2, couldn't this be used also?
DECLARE @orderby int
SET @orderby = 1
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
February 6, 2004 at 11:35 am
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
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply