Uses For Derived Tables

  • Robert W Marda

    SSChampion

    Points: 13413

    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

    SSC Guru

    Points: 75109

    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.

  • Robert W Marda

    SSChampion

    Points: 13413

    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

    SSChampion

    Points: 13623

    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

    SSChampion

    Points: 13413

    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

  • Robert W Marda

    SSChampion

    Points: 13413

    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

    Valued Member

    Points: 59

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

    Jim Crosby

  • bustell

    Ten Centuries

    Points: 1209

    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

    SSC Veteran

    Points: 299

    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 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply