Post reply

Using the new -- WITH -- IN SQL2005

  • February 26, 2006 at 10:18 pm

    #171246

    Hello all,

    I have been reading about this new little feature the sql2005 offers and i would like for someone to help me better understand when to use this type of code.. I have got a small understanding of this but while sifting through the BOL i will need a little human interaction for this one... If someone could give me a scenario of when to use this for a certain solution..

    Thanks!

    Erik

     

    Examples

    A. Creating a simple common table expression

    The following example shows the number of employees reporting directly to each manager at Adventure Works Cycles.

    USE AdventureWorks;GOWITH DirReps(ManagerID, DirectReports) AS (    SELECT ManagerID, COUNT(*)     FROM HumanResources.Employee AS e    WHERE ManagerID IS NOT NULL    GROUP BY ManagerID)SELECT ManagerID, DirectReports FROM DirReps ORDER BY ManagerID;GO

    Dam again!

  • February 27, 2006 at 5:02 am

    #623418

    Hi,

    Basically the above is an example to show the idea of CTE's;

    It shows simply how to refer to a nested query through a CTE.

    Even simpler would be to say;

    WITH cte_alias (column_aliases)

    AS (nested query)

    SELECT * FROM cte_alias

    When would you use it? Well you wouldn't.

    The point of CTE's is to make long, complex queries easier to write.

    If you can imagine a result set that is built up from several queries to that same nested query, you can see why it would be useful.

  • February 27, 2006 at 9:16 am

    #623481

    Thanks for the response!

    I can see the value in this new CTE, another thing that i think is neat is how the CTE can referr to itself more than once....

     

    erik

    Dam again!

Viewing 3 posts - 1 through 3 (of 3 total)

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