Set-Based Solution Possible?

  • I need to be able to summarize the data for a report ordered by StaffName allowing for multiple rows per Staff per Year

    Each column needs to be ordered in Ascending date order and can be blank if there was no review performed for this row and year

    I can filter out the desired years from actual data by " WHERE Year(ReviewDate) >= @iYear " {@iYear = Year(GetDate())-3}

    Note: my actual data is a StaffId Foreign Key - I just wrote this using a varchar field for demonstration purposes

    Also note: I'm not concerned with the StaffName showing or not showing per row; the report should handle that

    Desired Result Set:

    ***********************************************************

    StaffName 2006_Dates 2007_Dates 2008_Dates

    -------------------------------------------------------

    Blow, Joe 2006-03-23 2007-03-13 2008-01-24

    2006-07-18 2007-06-28

    2007-12-21

    Doe, Jane 2006-05-16 2007-03-12 2008-01-22

    2006-11-19 2007-06-11

    2007-12-16

    Dunno, I 2006-12-10 2007-07-05

    2007-12-18

    ***********************************************************

    I could insert all data into a temp table and loop through each row and add/update a row per Staff based on the date.

    However, is there any way I can do this using a SET BASED solution rather than a RBAR TEMP TABLE/CURSOR method?

    Thank you for any suggestions (or straight denials that it can actually be done).

    Here is a sample data script:

    Create Table #tTestData(

    StaffName varchar(40),

    ReviewDate smalldatetime

    )

    Insert Into #tTestData(StaffName, ReviewDate)

    Select 'Blow, Joe','2008-01-24' Union All

    Select 'Doe, Jane','2008-01-22' Union All

    Select 'Dunno, I', '2007-07-05' Union All

    Select 'Blow, Joe','2007-06-28' Union All

    Select 'Doe, Jane','2007-06-11' Union All

    Select 'Blow, Joe','2007-03-13' Union All

    Select 'Blow, Joe','2007-12-21' Union All

    Select 'Doe, Jane','2007-12-16' Union All

    Select 'Doe, Jane','2007-03-12' Union All

    Select 'Dunno, I', '2007-12-18' Union All

    Select 'Blow, Joe','2006-03-23' Union All

    Select 'Blow, Joe','2006-07-18' Union All

    Select 'Doe, Jane','2006-05-16' Union All

    Select 'Doe, Jane','2006-11-19' Union All

    Select 'Dunno, I', '2006-12-10'

    Select StaffName, ReviewDate

    From #tTestData

    Order By StaffName, ReviewDate

    Drop Table #tTestData


    In the beginning the Universe was created. This has made a lot of people very angry and has been widely regarded as a bad move.   Douglas Adams (1952-2001)

  • Yes, as single SQL statement can be written but I would be very concerned about resource utilization (e.g. the SQL would run like a pig).

    Please be sure to read Jeff Moden's article titled "Hidden RBAR: Triangular Joins" at http://www.sqlservercentral.com/articles/T-SQL/61539/

    This SQL ranks each staff member's review for a specific year and is a hidden RBAR. With SQL Server 2005/2008, better performance can be obtained using the rank window function.

    select#tTestData.StaffName

    ,#tTestData.ReviewDate

    ,count(*)as ReviewOrder

    from#tTestData

    join#tTestDataas PriorReview

    on PriorReview.StaffName = #tTestData.StaffName

    and PriorReview.ReviewDate <= #tTestData.ReviewDate

    whereyear(#tTestData.ReviewDate) = @iyear - 2

    andyear(PriorReview.ReviewDate) = @iyear - 2

    group by #tTestData.StaffName

    ,#tTestData.ReviewDate

    This SQL determines the maximum number of reviews for a staff member in any of the past three years:

    select StaffName

    ,CASE WHEN Year0Cnt > Year1Cnt and Year1Cnt > Year0Cntthen Year0Cnt

    when Year1Cnt > Year0Cnt then Year1Cnt

    else Year0Cnt

    end as StaffReviewCnt

    from(

    Select StaffName

    , SUM( case when year(ReviewDate) = @iyear - 2 then 1 else 0 end ) as Year2Cnt

    , SUM( case when year(ReviewDate) = @iyear - 1 then 1 else 0 end ) as Year1Cnt

    , SUM( case when year(ReviewDate) = @iyear - 0 then 1 else 0 end ) as Year0Cnt

    From #tTestData

    group By StaffName

    ) as StaffYearlyReviews

    This SQL enumerates each of the possible ranks up to four in any given year. A auxiliary numbers table would be a better solution - see http://sqlserver2000.databases.aspfaq.com/why-should-i-consider-using-an-auxiliary-numbers-table.html

    select 1 as RowNum union all select 2 union all select 3 union all select 4

    Finally, combine all the individual SQL statements:

    declare@iYear smallint

    set@iyear = 2008

    selectStaffReviews.StaffName

    ,Review2.ReviewDate

    ,Review1.ReviewDate

    ,Review0.ReviewDate

    from(select StaffName

    ,CASE WHEN Year0Cnt > Year1Cnt and Year1Cnt > Year0Cntthen Year0Cnt

    when Year1Cnt > Year0Cnt then Year1Cnt

    else Year0Cnt

    end as StaffReviewCnt

    from(

    Select StaffName

    , SUM( case when year(ReviewDate) = @iyear - 2 then 1 else 0 end ) as Year2Cnt

    , SUM( case when year(ReviewDate) = @iyear - 1 then 1 else 0 end ) as Year1Cnt

    , SUM( case when year(ReviewDate) = @iyear - 0 then 1 else 0 end ) as Year0Cnt

    From #tTestData

    group By StaffName

    ) as StaffYearlyReviews

    ) as StaffReviews

    join(select 1 as RowNum union all select 2 union all select 3 union all select 4 )

    as ReviewRow

    on ReviewRow.RowNum between 1 and StaffReviews.StaffReviewCnt

    left outer join

    (select#tTestData.StaffName

    ,#tTestData.ReviewDate

    ,count(*)as ReviewOrder

    from#tTestData

    join#tTestDataas PriorReview

    on PriorReview.StaffName = #tTestData.StaffName

    and PriorReview.ReviewDate <= #tTestData.ReviewDate

    whereyear(#tTestData.ReviewDate) = @iyear - 2

    andyear(PriorReview.ReviewDate) = @iyear - 2

    group by #tTestData.StaffName

    ,#tTestData.ReviewDate

    ) as Review2

    on Review2.StaffName= StaffReviews.StaffName

    and Review2.ReviewOrder = ReviewRow.RowNum

    left outer join

    (select#tTestData.StaffName

    ,#tTestData.ReviewDate

    ,count(*)as ReviewOrder

    from#tTestData

    join#tTestDataas PriorReview

    on PriorReview.StaffName = #tTestData.StaffName

    and PriorReview.ReviewDate <= #tTestData.ReviewDate

    whereyear(#tTestData.ReviewDate) = @iyear - 1

    andyear(PriorReview.ReviewDate) = @iyear - 1

    group by #tTestData.StaffName

    ,#tTestData.ReviewDate

    ) as Review1

    on Review1.StaffName= StaffReviews.StaffName

    and Review1.ReviewOrder = ReviewRow.RowNum

    left outer join

    (select#tTestData.StaffName

    ,#tTestData.ReviewDate

    ,count(*)as ReviewOrder

    from#tTestData

    join#tTestDataas PriorReview

    on PriorReview.StaffName = #tTestData.StaffName

    and PriorReview.ReviewDate <= #tTestData.ReviewDate

    whereyear(#tTestData.ReviewDate) = @iyear - 0

    andyear(PriorReview.ReviewDate) = @iyear - 0

    group by #tTestData.StaffName

    ,#tTestData.ReviewDate

    ) as Review0

    on Review0.StaffName= StaffReviews.StaffName

    and Review0.ReviewOrder = ReviewRow.RowNum

    The result set is

    Blow, Joe2006-03-23 00:00:002007-03-13 00:00:002008-01-24 00:00:00

    Blow, Joe2006-07-18 00:00:002007-06-28 00:00:00NULL

    Blow, JoeNULL2007-12-21 00:00:00NULL

    Doe, Jane2006-05-16 00:00:002007-03-12 00:00:002008-01-22 00:00:00

    Doe, Jane2006-11-19 00:00:002007-06-11 00:00:00NULL

    Doe, JaneNULL2007-12-16 00:00:00NULL

    Dunno, I2006-12-10 00:00:002007-07-05 00:00:00NULL

    Dunno, INULL2007-12-18 00:00:00NULL

    SQL = Scarcely Qualifies as a Language

  • Thank you - I thought there had to be a way but couldn't quite get there and wasn't given the time to work it out.

    Checking the execution plan I see why you have concerns about it being resource intensive. There are certainly a lot of table scans going on! Works nicely though and well thought out.

    I wrote a stored procedure after I posted using a couple of temp tables and single row processing. It's not Set based, but it does work and while there are a lot of queries involved they are all Clustered Index Scans for the most part returning a single row. Might be more maintainable for my replacement if I get hit by the proverbial I.T. bus.

    My actual data requirements are for the last 5 years of data per staff member, but thankfully this is a small company so really only talking between 50-60 staff members to report on so it's not like it's a huge server hit. I will give both methods a shot and the one which crosses the finish line first will get the job.

    Thank you for taking the time with this it is very much appreciated.

    Mike


    In the beginning the Universe was created. This has made a lot of people very angry and has been widely regarded as a bad move.   Douglas Adams (1952-2001)

  • I would do it using a user defined function. Some / Many may not consider this a SET based approach.

    PLEASE NOTE this example creates a permanat table and user defined function

    Create Table tTestData

    (

    StaffName varchar(40),

    ReviewDate smalldatetime

    )

    Insert Into tTestData(StaffName, ReviewDate)

    Select 'Blow, Joe','2008-01-24' Union All

    Select 'Doe, Jane','2008-01-22' Union All

    Select 'Dunno, I', '2007-07-05' Union All

    Select 'Blow, Joe','2007-06-28' Union All

    Select 'Doe, Jane','2007-06-11' Union All

    Select 'Blow, Joe','2007-03-13' Union All

    Select 'Blow, Joe','2007-12-21' Union All

    Select 'Doe, Jane','2007-12-16' Union All

    Select 'Doe, Jane','2007-03-12' Union All

    Select 'Dunno, I', '2007-12-18' Union All

    Select 'Blow, Joe','2006-03-23' Union All

    Select 'Blow, Joe','2006-07-18' Union All

    Select 'Doe, Jane','2006-05-16' Union All

    Select 'Doe, Jane','2006-11-19' Union All

    Select 'Dunno, I', '2006-12-10'

    GO

    CREATE FUNCTION dbo.udf_GetDates

    (

    @pStaffName VARCHAR(40),

    @pReviewDate DATETIME

    )

    RETURNS VARCHAR(2000)

    AS

    BEGIN

    DECLARE @RDates VARCHAR(2000)

    SELECT @RDates = COALESCE(@RDates + ', ', '') + CONVERT(VARCHAR, ReviewDate, 101)

    FROM

    tTestData

    WHERE

    StaffName = @pStaffName

    ANDDATEDIFF(YEAR, ReviewDate, @pReviewDate) = 0

    RETURN @RDates

    END

    GO

    SELECTStaffName,

    [2006 Reviews] = dbo.udf_GetDates(StaffName, '01/01/2006'),

    [2007 Reviews] = dbo.udf_GetDates(StaffName, '01/01/2007'),

    [2008 Reviews] = dbo.udf_GetDates(StaffName, '01/01/2008')

    FROM

    (

    Select DISTINCT StaffName

    From

    tTestData

    ) A

    GO

    DROP TABLE tTestData

    DROP FUNCTION udf_GetDates

    Regards,
    gova

  • Another interesting approach. Thank you. The data doesn't result in the format I need with this method however. It creates a nice comma-delimited dates per year instead.

    Actually though, I wanted to say I like the way you used COALESCE with the @RDates variable for the comma delimiter to handle the first iteration and subsequent appends. I never considered doing it that way. I am going to shamelessly incorporate that into my coding arsenal.

    Learn something new every day - right?

    Cheers, Mike


    In the beginning the Universe was created. This has made a lot of people very angry and has been widely regarded as a bad move.   Douglas Adams (1952-2001)

  • This returns exactly what you need.

    This script uses table Tally which contains sequential integer numbers.

    You may create it yourself or search this forum for one of scripts creating it.

    [Code]

    Create Table tTestData (

    StaffName varchar(40),

    ReviewDate smalldatetime

    )

    SET DATEFORMAT YMD

    Insert Into tTestData(StaffName, ReviewDate)

    Select 'Blow, Joe','2008-01-24' Union All

    Select 'Doe, Jane','2008-01-22' Union All

    Select 'Dunno, I', '2007-07-05' Union All

    Select 'Blow, Joe','2007-06-28' Union All

    Select 'Doe, Jane','2007-06-11' Union All

    Select 'Blow, Joe','2007-03-13' Union All

    Select 'Blow, Joe','2007-12-21' Union All

    Select 'Doe, Jane','2007-12-16' Union All

    Select 'Doe, Jane','2007-03-12' Union All

    Select 'Dunno, I', '2007-12-18' Union All

    Select 'Blow, Joe','2006-03-23' Union All

    Select 'Blow, Joe','2006-07-18' Union All

    Select 'Doe, Jane','2006-05-16' Union All

    Select 'Doe, Jane','2006-11-19' Union All

    Select 'Dunno, I', '2006-12-10'

    GO

    CREATE FUNCTION dbo.tReviewsOfYear

    (

    @ReviewYear SMALLDATETIME

    )

    RETURNS @reviews TABLE (

    ID INT IDENTITY(1,1) NOT NULL PRIMARY KEY,

    StaffName VARCHAR(40),

    ReviewNo int NULL,

    ReviewDate SMALLDATETIME NOT NULL

    )

    AS

    BEGIN

    --To make sure we deal with "year only" value

    SET @ReviewYear = DATEADD(YY, DATEDIFF(YY, 0, @ReviewYear), 0)

    DECLARE @StaffName VARCHAR(200)

    DECLARE @Count INT

    INSERT INTO @reviews (StaffName, ReviewDate)

    SELECT StaffName, ReviewDate

    FROM dbo.tTestData

    WHERE ReviewDate >= @ReviewYear

    ANDReviewDate < DATEADD(YY, 1, @ReviewYear)

    ORDER BY StaffName, ReviewDate

    -- Sequential update driven by PRIMARY KEY

    UPDATE R

    SET @Count = ReviewNo = CASE WHEN @StaffName = StaffName THEN @Count + 1 ELSE 1 END,

    @StaffName = StaffName

    FROM @reviews R

    RETURN

    END

    GO

    select CASE WHEN T.N = 1 THEN S.StaffName ELSE '' END,

    T.N, Y2006.ReviewDate [2006_Dates], Y2007.ReviewDate [2007_Dates], Y2008.ReviewDate [2008_Dates]

    from dbo.tTestData S

    INNER JOIN dbo.Tally T ON T.N > 0 AND T.N < 5000

    -- If you expect more than 5000 reviews per year per customer increase this number

    LEFT HASH JOIN dbo.tReviewsOfYear ('2006-01-01') Y2006 ON S.StaffName = Y2006.StaffName AND T.N = Y2006.ReviewNo

    LEFT HASH JOIN dbo.tReviewsOfYear ('2007-01-01') Y2007 ON S.StaffName = Y2007.StaffName AND T.N = Y2007.ReviewNo

    LEFT HASH JOIN dbo.tReviewsOfYear ('2008-01-01') Y2008 ON S.StaffName = Y2008.StaffName AND T.N = Y2008.ReviewNo

    WHERE Y2006.ReviewNo IS NOT NULL OR Y2007.ReviewNo IS NOT NULL OR Y2008.ReviewNo IS NOT NULL

    GROUP BY S.StaffName, T.N, Y2006.ReviewDate , Y2007.ReviewDate , Y2008.ReviewDate

    ORDER BY S.StaffName, T.N

    [/Code]

    _____________
    Code for TallyGenerator

  • i have a similar problem where setbased solution would speed things up dramatically:

    As part of a large report, I have to to calculate various expenses that have incurred.

    I achieve this by executing a separate storedproc which updates the temp tables that are created by the Main storedproc.

    There are 50 different Expense types with different selection criteria, so Ive created a table to keep all these expenses and there selection criteria.

    Ie:

    Insert Into fst_Expenses_Template(Descr, Where_SD_GB, Where_TRA)

    Select ‘Admin Fees’,’ E3_field = 132 ,’ E1_exp_level1 = 0132’ Union All

    Select ‘Broker fees’,’ E3_field = 135 ,’ E1_exp_level1 = 0135’ Union All

    Select ‘Legal Fees’,’ E3_field = 139 ,’ E1_exp_level1 = 0139’ Union All

    To process each expensetype, I repetitively read (in WHILE loop) from this Expenses "template" table and build up a query-string which will Update a separate temp table with the results. (see code below which ive simplified for readability).

    Could this be done set-based ?

    DECLARE @REP_count TinyInt ,

    @intRow TinyInt,

    @commonWhere_SD_GB Varchar (60),

    @commonWhere_TRA Varchar (60),

    @descr Varchar(50),

    @SQL Varchar(5000)

    INSERT INTO #EXPENSE_Tbl

    SELECT *

    FROM fst_Expenses_Template

    SET @REP_count = @@ROWCOUNT

    SET @intRow = 1

    /*-----------------------------------

    Loop for each of the 45 expense types

    -------------------------------------*/

    WHILE @intRow <= @REP_count

    BEGIN

    SELECT

    @descr = Descr,

    @commonWhere_SD_GB = Where_SD_GB,

    @commonWhere_TRA = Where_TRA

    FROM

    fst_Expenses_Template --#EXPENSE_Tbl

    WHERE

    Num = @intRow

    SET @SQL='

    UPDATE #EXPENSE_Tbl

    SET

    SD_GB_End =

    (

    SELECT SUM(gb_field_end)

    FROM

    fst_tbl_SD

    WHERE

    ' + @commonWhere_SD_GB + '

    ),

    SD_GB_Start =

    (

    SELECT SUM(gb_field)

    FROM

    fst_tbl_SD

    WHERE

    ' + @commonWhere_SD_GB + '

    ),

    TRA_G9 =

    (

    SELECT SUM(g9_income_base)

    FROM

    fst_tbl_Transactions

    WHERE

    ' + @commonWhere_TRA + '

    ),

    TRA_H5 =

    (

    SELECT SUM(h5_Income_FX_gl)

    FROM

    fst_tbl_Transactions

    WHERE

    ' + @commonWhere_TRA + '

    ),

    TRA_H77 =

    (

    SELECT SUM(h77_gls)

    FROM

    fst_tbl_Transactions

    WHERE

    ' + @commonWhere_TRA + '

    )

    WHERE

    #EXPENSE_Tbl.Descr = ''' + @descr + '''

    '

    Exec (@SQL)

    SET @intRow= @intRow + 1

    END

    /*--------------------

    Return the results

    -------------------- -*/

    .

  • Ok, Sergiy wins. Lightning fast and easily maintainable if required.

    I had to look at that function line a few times ... SET @Count = ReviewNo = CASE ... to figure out exactly what he did though. And once again I've never considered doing that.

    I'm learning just how much I don't know I guess!

    Thank you Sergiy


    In the beginning the Universe was created. This has made a lot of people very angry and has been widely regarded as a bad move.   Douglas Adams (1952-2001)

  • You welcome.

    This UPDATE has 2 other imprescriptible parts outside of the sattement itself:

    [Code]ORDER BY StaffName, ReviewDate[/Code]

    which inserts rows in the table in specific order and

    [Code]ID INT IDENTITY(1,1) NOT NULL PRIMARY KEY,[/Code]

    which holds the rows in the order they've been inserted.

    If you want to perform the same trick on a static table you need to have specifically crafted index and use WITH INDEX hint.

    _____________
    Code for TallyGenerator

  • Why use UPDATE at all? Or use a function? This is possible to do in a single query.

    And still have some options to include or exclude "non-valid" members!

    SELECTCASE WHEN u.theIndex = 0 THEN u.StaffName ELSE '' END AS StaffName,

    MAX(CASE WHEN u.ReviewDate LIKE '2006%' THEN u.ReviewDate ELSE '' END) AS [2006_Dates],

    MAX(CASE WHEN u.ReviewDate LIKE '2007%' THEN u.ReviewDate ELSE '' END) AS [2007_Dates],

    MAX(CASE WHEN u.ReviewDate LIKE '2008%' THEN u.ReviewDate ELSE '' END) AS [2008_Dates]

    FROM(

    SELECTt1.StaffName,

    CONVERT(CHAR(10), t1.ReviewDate, 120) AS ReviewDate,

    (

    SELECTCOUNT(*)

    FROM#TestData AS t2

    WHEREt2.StaffName = t1.StaffName

    AND YEAR(t2.ReviewDate) = YEAR(t1.ReviewDate)

    AND t2.ReviewDate < t1.ReviewDate

    AND t2.ReviewDate >= '20060101'

    AND t2.ReviewDate < '20090101'

    ) AS theIndex

    FROM#TestData AS t1

    /* Remove this comment to only display staff who has dates in valid range

    WHEREt1.ReviewDate >= '20060101'

    AND t1.ReviewDate < '20090101'

    */

    ) AS u

    GROUP BYu.StaffName,

    u.theIndex

    ORDER BYu.StaffName,

    u.theIndexYou have also the option to change AND t2.ReviewDate < t1.ReviewDate to AND t2.ReviewDate > t1.ReviewDate if you want the dates sorted ascending instead.


    N 56°04'39.16"
    E 12°55'05.25"

  • This is how you do it in SQL Server 2005 with all members includedSELECTCASE WHEN u.RecID = 1 THEN u.StaffName ELSE '' END AS StaffName,

    MAX(CASE WHEN u.ReviewDate LIKE '2006%' THEN u.ReviewDate ELSE '' END) AS [2006_Dates],

    MAX(CASE WHEN u.ReviewDate LIKE '2007%' THEN u.ReviewDate ELSE '' END) AS [2007_Dates],

    MAX(CASE WHEN u.ReviewDate LIKE '2008%' THEN u.ReviewDate ELSE '' END) AS [2008_Dates]

    FROM(

    SELECTStaffName,

    CASE

    WHEN ReviewDate >= '20060101' AND ReviewDate < '20090101' THEN CONVERT(CHAR(10), ReviewDate, 120)

    ELSE ''

    END AS ReviewDate,

    ROW_NUMBER() OVER (PARTITION BY StaffName, YEAR(ReviewDate) ORDER BY ReviewDate) AS RecID

    FROM#TestData

    ) AS u

    GROUP BYu.StaffName,

    u.RecID

    ORDER BYu.StaffName,

    u.RecID


    N 56°04'39.16"
    E 12°55'05.25"

  • And this how you do it in SQL Server 2005 with only valid member (member has a reviewdate in wanted range)SELECTCASE WHEN u.RecID = 1 THEN u.StaffName ELSE '' END AS StaffName,

    MAX(CASE WHEN u.ReviewDate LIKE '2006%' THEN u.ReviewDate ELSE '' END) AS [2006_Dates],

    MAX(CASE WHEN u.ReviewDate LIKE '2007%' THEN u.ReviewDate ELSE '' END) AS [2007_Dates],

    MAX(CASE WHEN u.ReviewDate LIKE '2008%' THEN u.ReviewDate ELSE '' END) AS [2008_Dates]

    FROM(

    SELECTStaffName,

    CONVERT(CHAR(10), ReviewDate, 120) AS ReviewDate,

    ROW_NUMBER() OVER (PARTITION BY StaffName, YEAR(ReviewDate) ORDER BY ReviewDate) AS RecID

    FROM#TestData

    WHEREReviewDate >= '20060101'

    AND ReviewDate < '20090101'

    ) AS u

    GROUP BYu.StaffName,

    u.RecID

    ORDER BYu.StaffName,

    u.RecID


    N 56°04'39.16"
    E 12°55'05.25"

  • Yet another method. Thanks Peter.

    The output results aren't exactly what I need (but I think you're editing the post as I type this because I've tried 2 different query posts of yours with different results)

    I'm still leaning towards Sergiy's methods here for a couple of reasons:

    a) It results in less table scans in the execution plan.

    b) I've already modified for my actual needs (5 years), written the report, and put it in production. :o)

    What I've really learned here is how many ways something like this can be achieved without resorting to procedural sql code. This is probably the biggest obstacle for a longtime coder like myself who is trying to improve SQL performance. Must-Not-Loop!

    Thanks again all, Mike


    In the beginning the Universe was created. This has made a lot of people very angry and has been widely regarded as a bad move.   Douglas Adams (1952-2001)

  • What? I have only two table scans. And if you index the #TestData table properly you will get two clustered index scans only!

    Peso with 32 reads

    |--Compute Scalar

    |--Stream Aggregate

    |--Compute Scala

    |--Sort

    |--Nested Loops

    |--Compute Scalar

    | |--Clustered Index Scan

    |--Compute Scalar

    |--Stream Aggregate

    |--Clustered Index Scan

    Sergiy with 4385 reads

    |--Compute Scalar

    |--Sequence

    |--Table-valued function

    |--Table-valued function

    |--Table-valued function

    |--Sort

    |--Filter

    |--Hash Match

    |--Hash Match

    | |--Hash Match

    | | |--Nested Loops

    | | | |--Clustered Index Scan

    | | | |--Table Spool

    | | | |--Index Seek

    | | |--Clustered Index Scan

    | |--Clustered Index Scan

    |--Clustered Index Scan

    |--Clustered Index Insert

    |--Compute Scalar

    |--Top

    |--Clustered Index Scan

    |--Clustered Index Update

    |--Compute Scalar

    |--Top

    |--Clustered Index Scan

    That TABLE SPOOL looks nasty to me. And 10 clustered index scans?


    N 56°04'39.16"
    E 12°55'05.25"

  • Whoops...Sorry, forgot to index my test data :blush:


    In the beginning the Universe was created. This has made a lot of people very angry and has been widely regarded as a bad move.   Douglas Adams (1952-2001)

Viewing 15 posts - 1 through 15 (of 38 total)

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