CTE - How to surpass the limit

Question

CTE - How to surpass the limit

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

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

Paul White SSC Guru Points: 150467 More actions · Answer 1

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.

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

Paul White SSC Guru Points: 150467 More actions · Answer 2

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
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

UMG Developer SSChampion Points: 13482 More actions · Answer 3

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?)

Paul White SSC Guru Points: 150467 More actions · Answer 4

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.

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

Jeff Moden SSC Guru Points: 1004166 More actions · Answer 5

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)