CTE - How to surpass the limit

  • UMG Developer (9/25/2010)


    One question, why does this version not have the "TableDefinition" in the "Microsoft.SqlServer.Server.SqlFunction" definition? Is it not required?

    It's only used by Visual Studio during auto-deployment (Professional Edition and above). It's not required if you're building the code and deploying manually.

  • UMG Developer (9/25/2010)


    What about the performance difference between passing in a NVARCHAR(MAX) and NVARCHAR(4000)?

    That can make a difference, yes. It used to be the case that SQLCLR functions that accepted a MAX data type could not use (SQL Server) parallelism, but that is no longer true, happily.

    You can change the T-SQL @Input parameter definition from NVARCHAR(MAX) to NVARCHAR(4000) without breaking anything. This will show the effect of non-MAX data types on the SQL Server side of things.

    ...maybe when the source string is that small using the tally method is better?)

    It depends on a number of factors, but from memory Tally is only faster in edge/trivial cases. Florian Reischl did some pretty comprehensive performance tests last year: http://florianreischl.blogspot.com/2009/09/high-performance-string-split-functions.html

  • Paul White NZ (9/25/2010)


    That can make a difference, yes. It used to be the case that SQLCLR functions that accepted a MAX data type could not use (SQL Server) parallelism, but that is no longer true, happily.

    When did it change? (Is it based on the version of Visual Studio, or SQL Server?)

  • UMG Developer (9/25/2010)


    When did it change? (Is it based on the version of Visual Studio, or SQL Server?)

    It was a SQL Server limitation. I don't recall exactly when it changed, but I remember being annoyed by the restriction when I was using SQL Server 2005 (early days) and it definitely works in current builds of SQL Server 2008, but beyond that I'm afraid I don't know.

  • Sorry... brain freeze... I already responded to this thread.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 5 posts - 46 through 49 (of 49 total)

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