Forum Replies Created

Viewing 15 posts - 1,081 through 1,095 (of 2,463 total)

  • RE: Why Scalar Functions Can Be Costly

    patrickmcginnis59 10839 (1/4/2016)

    TheSQLGuru (1/4/2016)

    lucien.jacquet (1/4/2016)

    My point is not to contradict your assertion that scalar functions "can be" expensive, but to suggest that your example is a worst-case scenario. Is 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

    January 4, 2016 at 12:17 pm

    #1849308

  • RE: Why Scalar Functions Can Be Costly

    Phil Parkin (1/4/2016)

    Alan.B (1/4/2016)

    --

    For me, that's been the 0.0001% exception.

    Wow. That suggests you have written/tested 10,000 (or more) TVFs :w00t:

    I've been busy 😉

    "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

    January 4, 2016 at 12:08 pm

    #1849307

  • RE: the use of CTEs

    Except for recursive CTEs, CTEs and Subqueries are basically the same thing. I prefer CTEs for a couple things:

    First, for nested queries. CTEs make nested queries much more readable 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

    January 4, 2016 at 12:05 pm

    #1849304

  • RE: Why Scalar Functions Can Be Costly

    Eirikur Eiriksson (1/4/2016)

    Worse still are the multi-statement table-valued functions, in fact there is no excuse or justification for using those.

    I agree with this 99.9999% of the time but I did...

    "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

    January 4, 2016 at 11:16 am

    #1849292

  • RE: Return N rows

    You can also take a peek at this:

    http://m.sqlmag.com/t-sql/avoid-unnecessary-lookups-when-using-rownumber-paging

    "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

    January 4, 2016 at 7:31 am

    #1849219

  • RE: Why Scalar Functions Can Be Costly

    TheSQLGuru (1/4/2016)

    1) UDFs existed at least as early as SQL 2000: https://technet.microsoft.com/en-us/library/aa175085(v=sql.80).aspx

    2) This article fails to mention an EXCEPTIONALLY important flaw (among many others that aren't covered such as preventing...

    "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

    January 4, 2016 at 7:04 am

    #1849208

  • RE: Best solution for compounded update?

    robert-819720 (12/30/2015)

    Thank you so much Alan. A fascinating concept. I might be overly tired but just cannot figure out how to apply the concept to my situation. But will continue...

    "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

    December 31, 2015 at 5:30 pm

    #1848998

  • RE: Syntax error in recursive cte

    Luis Cazares (12/30/2015)

    The following code should be removed for it to work.

    SELECT CONVERT(date,[dates]) AS Dates

    FROM DailyDates

    OPTION (maxrecursion 0)

    ),

    However, there's a major problem. You have a rCTE that counts which...

    "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

    December 30, 2015 at 6:19 pm

    #1848871

  • RE: Best solution for compounded update?

    You can use the Quirky Update method discussed here: http://www.sqlservercentral.com/articles/T-SQL/68467/%5B/url%5D

    "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

    December 30, 2015 at 11:49 am

    #1848814

  • RE: Syntax error in recursive cte

    That END statement will cause an error if there is not a BEGIN statement somewhere before it within the same transaction. I'll just assume, however, that you have a BEGIN...

    "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

    December 30, 2015 at 10:55 am

    #1848803

  • RE: Help with a query, plz

    SQLisAwE5OmE (12/29/2015)

    Thnx again Jeff for trying. I am learning.

    You've come to the best place for people that are learning SQL IMHO.

    "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

    December 29, 2015 at 7:46 pm

    #1848669

  • RE: Other Tools to Develop SSIS Package

    Stylez (12/29/2015)

    Good Morning Guys,

    ...is there a substitute tool that i can use so i can create my SSIS package with only my SQL Server 2012?

    Depending on what you are trying...

    "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

    December 29, 2015 at 12:56 pm

    #1848610

  • RE: Help with a query, plz

    I'm guessing you would add this to the end of your query:

    WHERE Parent1_Gender = 'F' and Parent2_Gender = 'F'

    "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

    December 29, 2015 at 12:46 pm

    #1848604

  • RE: The Most Common Query Blunders...

    Eirikur Eiriksson (12/28/2015)

    Alan.B (12/28/2015)

    The most common/easiest to fix mistakes:

    Someone beat me to it but NOLOCK table hints are the #1 mistake I have seen. I would go so far as...

    "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

    December 28, 2015 at 2:23 pm

    #1848358

  • RE: The Most Common Query Blunders...

    The most common/easiest to fix mistakes:

    Someone beat me to it but NOLOCK table hints are the #1 mistake I have seen. I would go so far as to say it's...

    "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

    December 28, 2015 at 12:39 pm

    #1848337

Viewing 15 posts - 1,081 through 1,095 (of 2,463 total)