Forum Replies Created

Viewing 15 posts - 1,711 through 1,725 (of 2,458 total)

  • RE: Stairway to Advanced T-SQL Level 3: Understanding Common Table Expressions (CTEs)

    arthur.bekker (3/18/2015)

    I think anyone who wants to use CTEs should read this article http://sqlblogcasts.com/blogs/tonyrogerson/archive/2008/05/17/non-recursive-common-table-expressions-performance-sucks-1-cte-self-join-cte-sub-query-inline-expansion.aspx....

    I showed the Execution Plan to this guy and he said that he wanted to "concrete" his...

    "I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

    -- Itzik Ben-Gan 2001

    March 18, 2015 at 2:43 pm

    #1784916

  • RE: Inline vs. Multi-Statement Table-Valued function, Temp Tables vs. CTEs?

    Katerine459 (3/18/2015)

    So, I've been reading the discussion on the latest Stairway article about CTEs (http://www.sqlservercentral.com/Forums/Topic1660966-34-1.aspx), and a bunch of people are talking about how CTEs are performance hogs. And now...

    "I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

    -- Itzik Ben-Gan 2001

    March 18, 2015 at 2:26 pm

    #1784907

  • RE: Stairway to Advanced T-SQL Level 3: Understanding Common Table Expressions (CTEs)

    jhood (3/18/2015)

    The recursive cte should never be used if you are concerned with performance.

    The recursion resolves row by row instead of sets. A while loop with temp tables...

    "I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

    -- Itzik Ben-Gan 2001

    March 18, 2015 at 12:43 pm

    #1784879

  • RE: Stairway to Advanced T-SQL Level 3: Understanding Common Table Expressions (CTEs)

    g.britton (3/18/2015)

    One other nice use: CTE as target table

    You can write this:

    WITH CTE AS

    (

    SELECT somecolums,....

    FROM sometable

    WHERE...

    "I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

    -- Itzik Ben-Gan 2001

    March 18, 2015 at 12:39 pm

    #1784877

  • RE: Stairway to Advanced T-SQL Level 3: Understanding Common Table Expressions (CTEs)

    chris.puncher (3/18/2015)

    Just worth pointing out that CTEs against large tables can be very slow; something I have had first hand experience of.

    Here is an alternative method that proved much more...

    "I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

    -- Itzik Ben-Gan 2001

    March 18, 2015 at 12:36 pm

    #1784875

  • RE: Stairway to Advanced T-SQL Level 3: Understanding Common Table Expressions (CTEs)

    Great article. One correction however:

    Emphasis mine...

    Below are some clauses that can't be used in a CTE:

    ORDER BY (unless used with TOP clause)

    INTO

    OPTION clause with query hints

    FOR XML

    FOR BROWSE

    Not true... You...

    "I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

    -- Itzik Ben-Gan 2001

    March 18, 2015 at 12:25 pm

    #1784871

  • RE: Need help with running total with thresholds

    Had a few moments to kill... Here's an example of how to solve this using the link that I provided.

    Note that I don't understand where, in row 1 of...

    "I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

    -- Itzik Ben-Gan 2001

    March 18, 2015 at 11:31 am

    #1784856

  • RE: Need help with running total with thresholds

    This article should help:

    Solving the Running Total and Ordinal Rank Problems[/url]

    "I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

    -- Itzik Ben-Gan 2001

    March 18, 2015 at 10:56 am

    #1784842

  • RE: IN vs EXISTS

    This is a very good article on this subject:

    http://sqlinthewild.co.za/index.php/2009/08/17/exists-vs-in/[/url]

    "I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

    -- Itzik Ben-Gan 2001

    March 15, 2015 at 1:10 pm

    #1784122

  • RE: Update incoming rows based on % calculation

    Here's a solution.

    I'm sure there is an easier way but this is a cursor/loop-free way that will work.

    /*Assumptions

    (1) There are alway 2 product names that are equal...

    "I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

    -- Itzik Ben-Gan 2001

    March 13, 2015 at 5:05 pm

    #1784013

  • RE: Update incoming rows based on % calculation

    I have a solution but need a little clarification...

    What exactly do you mean by

    Amount = or (within +/- 5%) of Amount

    For example, Let's say Amount1 is 1051 and...

    "I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

    -- Itzik Ben-Gan 2001

    March 13, 2015 at 4:04 pm

    #1784006

  • RE: The limits of SQL 2008 Replication

    For DR and Reporting purposes you old use replication but I don't think it's the best solution. You would have a lot of publishers to deal and a lot...

    "I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

    -- Itzik Ben-Gan 2001

    March 12, 2015 at 10:53 am

    #1783649

  • RE: Second Friday of every month

    First, Louis' solution is brilliant; I tried for a couple hours to come up with something faster (that does not utilize a calendar table) and failed.

    So here's my calendar...

    "I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

    -- Itzik Ben-Gan 2001

    March 11, 2015 at 7:26 pm

    #1783502

  • RE: Second Friday of every month

    dwain.c (3/11/2015)

    This is quite easy to do if you already have a calendar table that stores that information:

    Calendar Tables in T-SQL[/url]

    The GenerateCalendar FUNCTION included in that article shows how it...

    "I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

    -- Itzik Ben-Gan 2001

    March 11, 2015 at 6:00 pm

    #1783485

  • RE: Second Friday of every month

    andyscott (3/11/2015)

    How about this group of ctes? The first cte creates a table of Dates for the current year, the second cte expands these to add the Year, Month and...

    "I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

    -- Itzik Ben-Gan 2001

    March 11, 2015 at 3:46 pm

    #1783462

Viewing 15 posts - 1,711 through 1,725 (of 2,458 total)