Posted by Erland Sommarskog on 04/30/07 21:26

B D Jensen (bjorn.d.jensen@gmail.com) writes:
> I made a comparison for the case of converting to tinyint and wrote a
> loop going from -1mio to +1mio using TSQL-function: 72 seconds using
> directly between (not in a function): 70 seconds ; it's in between ;^)
> using directly <= and >= : 67seconds
>
> So you are right not writing it in seperate function is faster (in
> this case),

I find it difficult to believe that there is any case where a scalar
T-SQL UDF would be faster.

Then again, with the numbers you present it's dubious whether you actually
have found a significant difference.

> (and CLR-function: 2min 27seconds)

With a more complex operation, you would have had a different outcome.
I once did a test where I had to convert zoned numbers with fixed
decimal from an AS400 system. In that case a CLR function was faster
than all T-SQL solutions. I think I have heard that when you have more
than four operations, the CLR pays off.

> Maybe it's one time only, but if you think you can reuse it, then at all
> there is less typing and more important: your code is much more
> readable, because it becomes shorter and much more natural too read.

Or you sit asking yourself "wonder what this function does".

> Again: it depends ;^)

True. That's the answer to almost all performance questions.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Back to original message]