THE SQL Server Blog Spot on the Web

Welcome to - The SQL Server blog spot on the web Sign in | |
in Search

SELECT Hints, Tips, Tricks FROM Hugo Kornelis WHERE RDBMS = 'SQL Server'

T-SQL User-Defined Functions: the good, the bad, and the ugly (part 1)

This blog has moved! You can find this content at the following new location:

Published Sunday, May 20, 2012 5:08 PM by Hugo Kornelis

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS



Hugo Kornelis said:

I guess I should add (thank you for pointing it out, Adam) that all of the above applies to user-defined function that are created in T-SQL. You can also create user-defined functions with .Net languages (CLR). I have not done much research into them yet, I do know that they usually perform better than their T-SQL counterparts.

I may do some investigation into CLR user-defined functions in the future, and if I do I'll write my findings in a blog post.

May 20, 2012 4:21 PM

Randall said:

Why hasn't Microsoft done anything to fix the problems with scalar T-SQL UDFs? They've had the opportunity in SQL Server 2008 and 2012. Why don't they care about SQL Server performance?

May 21, 2012 8:19 AM

Hugo Kornelis said:

@Randall: I would not go as far as to say that Microsoft "don't care about SQL Server performance". There have been many changes to help performance, just not in the area of scalar T-SQL UDFs.

I can't tell you why MS has chosen not to invest in the area of scalar T-SQL UDF performance. Only they can tell you. I can give you my best guess: they had more things they wanted to do than they had developer hours available, so they had to prioritize, and other issues were considered more important.

Personally, I'd *love* to see some work done here. Just adding a special syntax for an inline scalar UDF (similar to the inline table-valued UDF) would be a great addition. There is a very popular connect item for this (281 up votes; 2 down votes) at - if you haven't voted yet, do so now!

May 21, 2012 8:51 AM

Geoff said:

"Why hasn't Microsoft done anything to fix the problems with scalar T-SQL UDFs? They've had the opportunity in SQL Server 2008 and 2012. Why don't they care about SQL Server performance?"


There is no reason that this still has to be a problem. At the very least, SQL Server could inline the code when it is compiled or give you the option to have that happen.

I can't fathom why this hasn't been fixed. We've recently in-lined a bunch of functions to improve performance in a number of our procedures, and it would be much nicer to have the logic contained in one place.

May 21, 2012 2:31 PM

Hugo Kornelis said:

@Geoff: "We've recently in-lined a bunch of functions to improve performance in a number of our procedures, and it would be much nicer to have the logic contained in one place."

There is a way to do that. I plan to cover it in a future blog post, but if you're impatient you can also read about it here:

May 21, 2012 5:45 PM

GrumpyOldDBA said:

We've really known the issues with T-SQL UDFs since SQL 2000. It's a key point of much performance tuning to just remove UDFs. Typically queries which ran for hours now run in secs and with reduced io usually.

I did try a bunch of ( simple ) CLR functions to replace T-SQL functions but couldn't reproduce in test the issues I had in production so the results were inconclusive, some time I will have to revisit.

May 22, 2012 7:29 AM

Geoff said:

Hugo, thanks.

We can't use an inline TVF because we are still stuck on 2000 compatibility mode. We've made the changes to get us off of compatibility mode, but they have to be tested.

May 22, 2012 10:34 AM

Ted said:

SQL Server 2012 should make this easier with a DETERMINISTIC keyword for the function like Oracle has had for years.

May 25, 2012 12:00 PM

Hugo Kornelis said:

Ted, SQL Server uses WITH SCHEMABINDING to mark user-defined functions as deterministic, so no need for a DETERMINISTIC keyword.

The difference between SQL Server, apart from the term used, is that SQL Server can't force determinism on a function that is actually not deterministic. If what I just found on internet is correct, Oracle doesn't check; if you declare a non-deterministic function to be deterministic, you'll simply get unexpected results.

May 25, 2012 2:13 PM

Hugo Kornelis said:


I just found that I made a mistake with the rowcount in the table used for the demo. I corrected this by editing the text in blog post - everywhere where I first had 10,000 has been changed to 100,000; and all occurences of a million have been changed to read ten million. The actual code has not been changed; the text now accurately describes the actual number mof rows in the table,

My apologies for this stupid mistake.

May 25, 2012 2:21 PM

TheSQLGuru said:


There is no reason that this still has to be a problem. At the very least, SQL Server could inline the code when it is compiled or give you the option to have that happen.

I can't fathom why this hasn't been fixed. We've recently in-lined a bunch of functions to improve performance in a number of our procedures, and it would be much nicer to have the logic contained in one place.


VERY good reason:javascript:WebForm_DoPostBackWithOptions(new WebForm_PostBackOptions("ctl00$_$ctl00$_$ctl00$ctl00$bcr$ctl00$_$form$_$btnSubmit", "", true, "", "", false, true))  It is really, REALLY f'in HARD to get the engine to know about, understand, and integrate the virtually infinite array of code that could go into any UDF into the optimizer and engine of SQL Server.  Since there are known workarounds (often several) and SOOOOO many other things that need to be fixed/improved in the engine, integrating UDFs has and never will make the cut IMNSOH.  ESPECIALLY with Steve Balmer's "the cloud - we're ALL IN" mantra redirecting hundreds of thousands of developer hours to windows/sql azure.  :(

May 26, 2012 10:23 AM

Hugo Kornelis said:

Kevin, I totally agree with your remark if you mean that it would cost too many developer hours to have the optimizer automatically inline all scalar user-defined functions. However, if you think about extending the syntax to allow a special kind of inline functions that allows only a single expression (similar to how inline table-valued funcions allow only a single query) and inlining them automatically, that would cost far less developer hours, place the burden of rewriting functions to a single expression on us, but give us the possiblity to encapsulate logic without takinng a performance hit.

May 26, 2012 10:45 AM

SELECT Hints, Tips, Tricks FROM Hugo Kornelis WHERE RDBMS = 'SQL Server' said:

In a previous blog post , I demonstrated just how much you can hurt your performance by encapsulating

May 26, 2012 11:00 AM

TheSQLGuru said:

Still disagree.  I can write a thousand-line-long SELECT that returns a single scalar value.  Absolutely no way that can be effectively integrated.  You are also ignoring the optimization shortcoming entirely - as in how would an estimate of number of rows or value distribution possibly be extracted from arbitrary code, especially when such output is dependent on the per-row input from the outer calling query?

Perhaps you are speaking of a non-data-hitting scalar, something like RETURN int AS (@input + 3)?  Again I can make an arbitrarily complex statement that could take extraordinary efforts to get accurate statistics sucked out of it.  

May 26, 2012 12:07 PM

RBarryYoung said:

The strange thing is that Microsoft has had an object-type for inline scalar functions since SQL Server 2005, and they even document how they work in a query in every version of the doc since then.  They just never included the syntax to implement one.  Weird.

May 26, 2012 2:24 PM

a.m. said:

Kevin, I'm not sure what data access has to do with anything. The query optimizer generally has no problem inlining even large/complex correlated scalar subqueries or table expressions. I write large table expressions -- even scalar-valued -- all the time that the QO has absolutely no issue producing accurate statistics for.

And in cases where expressions can't be inlined, they're still much faster when evaluated via a nested subtree rather than in a separate context, as occurs with scalar UDFs.

... you can read my prior blog post on the topic, linked above in Hugo's response to Geoff, for more information.

May 26, 2012 2:46 PM

Hugo Kornelis said:

@RBarry: Interesting, I didn't know that. Can you create such objects through the object model, or can you only read them?

Kevin: It's not about the number of lines, or the number of values returned - it's about what goes between the BEGIN and END of the function definition. For the inline scalar function I would like to see added to the product, that would only be one statement: RETURN, followed by an expression that evaluates to a single scalar value. And that expression can be data-hitting (by including subqueries), or not.

Yes, you can make it complicated, and that may be hard to optimize. How does that differ from manually copying and pasting that same complicated expression in the query? I don't expect a query that uses an inline scalar function to perform better than one where I inline the expression myself, but it would be great if they would perform the same. That would be a huge step forward. And it is possible, since all it takes is for the parser to replace the reference to the function with its definition, as a sort of macro expansion. Exactly how currently views and inline table-valued function are already treated.

May 26, 2012 4:31 PM

Frantz said:

Nice article.

A fast way to optimize code that makes use of a scalar function in a situation where the logic is too complex to be re-written is to create a table with all the values and join it.  

It is not elegant and I don't know if it performs well under most situations but it helped us cut the execution time tremendously many times.  Something like:







'MaxTriple' = dbo.Triple(DataVal)




dbo.LargeTable) T) T


dbo.LargeTable D

ON D.DataVal = T.DataVal


May 26, 2012 6:40 PM

TheSQLGuru said:

Here are 2 exceptionally simple UDFs, one scalar and one TVF.  Both result in horrible estimates and suboptimal executions, including memory grants 2 and 3 times larger.  I am sure I am missing something Adam, but am not sure what it is.

USE AdventureWorks2008R2



--useful index

CREATE NONCLUSTERED INDEX [idx_fullname] ON [Person].[Person]


[LastName] ASC,

[FirstName] ASC




CREATE FUNCTION dbo.fn_FullName (@FirstName nvarchar(50), @MiddleName nvarchar(50), @LastName nvarchar(50))




  RETURN @LastName + N', ' + @FirstName + N' ' + ISNULL(@MiddleName, N'')



--show actual execution plan and not estimated/actual differences and the query shape/operations

SELECT dbo.fn_FullName(p.FirstName, p.MiddleName, p.LastName) as FullName,


      SUM((UnitPrice - UnitPriceDiscount) * OrderQty) AS TotalPrice

 FROM Sales.SalesOrderHeader oh

INNER JOIN Sales.SalesOrderDetail od ON od.SalesOrderID = oh.SalesOrderID

INNER JOIN Sales.SalesPerson sp ON sp.BusinessEntityID = oh.SalesPersonID

INNER JOIN Person.Person p ON p.BusinessEntityID = sp.BusinessEntityID

WHERE dbo.fn_FullName(p.FirstName, p.MiddleName, p.LastName) = N'Abbas, Syed E'

GROUP BY p.LastName, p.FirstName, p.MiddleName, od.ProductID

.13 duration, .15 cpu, 1231 reads, 2192 memory grant

significant disparity between estimated and actual rowcounts

SELECT p.LastName + N', ' + p.FirstName + N' ' + ISNULL(p.MiddleName, '') as FullName,


      SUM((UnitPrice - UnitPriceDiscount) * OrderQty) AS TotalPrice

 FROM Sales.SalesOrderHeader oh

INNER JOIN Sales.SalesOrderDetail od ON od.SalesOrderID = oh.SalesOrderID

INNER JOIN Sales.SalesPerson sp ON sp.BusinessEntityID = oh.SalesPersonID

INNER JOIN Person.Person p ON p.BusinessEntityID = sp.BusinessEntityID

WHERE p.LastName = N'Abbas'

  AND p.FirstName = N'Syed'

  AND p.MiddleName = N'E'

GROUP BY p.LastName, p.FirstName, p.MiddleName, od.ProductID

0 duration, 0 cpu, 63 reads, 1024 (default sized?) memory grant

CREATE FUNCTION dbo.tvf_FullName (@FirstName nvarchar(50), @MiddleName nvarchar(50), @LastName nvarchar(50))



  RETURN (SELECT @LastName + N', ' + @FirstName + N' ' + ISNULL(@MiddleName, N'') AS FullName)


SELECT tvp.FullName,


      SUM((UnitPrice - UnitPriceDiscount) * OrderQty) AS TotalPrice

 FROM Sales.SalesOrderHeader oh

INNER JOIN Sales.SalesOrderDetail od ON od.SalesOrderID = oh.SalesOrderID

INNER JOIN Sales.SalesPerson sp ON sp.BusinessEntityID = oh.SalesPersonID

INNER JOIN Person.Person p ON p.BusinessEntityID = sp.BusinessEntityID

CROSS APPLY dbo.tvf_FullName (p.FirstName, p.MiddleName, p.LastName) tvp

WHERE tvp.FullName = N'Abbas, Syed E'

GROUP BY tvp.FullName, od.ProductID

.13 duration, .15 cpu, 1231 reads, 3712 memory grant

significant disparity between estimated and actual rowcounts

May 27, 2012 4:00 PM

Dave Maiden said:

I personally think this started when Microsoft decided to create the .NET Framework and go OOP, I appreciate SQL as a language but have since moved onto VB.NET/C#, but still actively use SQL. When you compile your code within the Framework the COMPILER is designed to calibrate your code (and it varies between each language on the the result).

I assume (incorrectly) that the in-built SQL functions are written within the Framework, which would make more sense to me as a change in one function would not have a devastating affect. First problem LEGACY if there is a problem then they either change it or leave it, the latter being the problem for compatibility. I do however understand that legacy systems are in use and use some of the depreciated commands.

You should test CLRs, I use them all the time and in these days of Asynchronous results surely we should have a essential bible (framework) which everything relies on and is standardised throughout. For example DATEDIFF("D") is different to Microsoft.VisualBasic.DateDiff(DateInterval.Day) as it uses Timespan.


May 27, 2012 10:03 PM

Mr Tea said:

If I use timestamps e.g. SYSUTCDATETIME() instead of statistics time then I see a different picture. Its still bad in comparison but using STATISTICS TIME has a direct and fundamental effect on the observation.

My Figures are:

Using SYSUTCDATETIME with LargeTable

multiplication: ~24 ms

dbo.triple: ~250ms ms

Using STATISTICS TIME with LargeTable

multiplication: ~24 ms

dbo.triple: ~450ms ms

August 17, 2012 7:07 AM

Madhukar Sreeramoju said:

How to know the DB - Memory Consumption in Sql Server using the query.

December 17, 2012 12:59 AM

Michael said:

let A,B,x in R such that x>0 if A>B then xA> you should select 3*max(DataVal) instead of select max(3*DataVal)

January 7, 2013 12:49 PM

Vladimir said:

I found your post when I was searching the web for the answer to the question: IF I have a UDF call in the SELECT part and the same UDF call in the WHERE part, would optimiser be smart enough to call UDF only once.


dbo.PortalElevators.elevatorsId AS ElevatorID

, dbo.GetElevatorLastPlaybackStats(dbo.PortalElevators.elevatorsId) AS MaxT




dbo.GetElevatorLastPlaybackStats(dbo.PortalElevators.elevatorsId) IS NOT NULL

And you answered that it will call it twice. Alas. It is good to know and there is an easy  workaround.

But, I'd like to note that in my case use of UDFs made my queries significantly faster.

I'm using MS SQL 2008. I've been researching UDFs for a while trying to optimise my queries and discovered that everybody call UDFs evil.

What you and other authors say about UDF is true and helped me to understand how it all works, but still in my particular case use of UDFs made my query run significantly faster.

In short, I have a relatively large table with logs - around 10 million rows. These logs are events that happen at approximately 2000 computers. Essentially, TableLogs has these columns:

ID int not null (primary key, identity)

ComputerID int not null (foreign key to the table of computers)

EventDateTime datetime not null (when it happened)

EventDurationMSec int not null (event duration in milliseconds)

Usually event duration is 10 - 20 seconds, but when things go wrong it is less than 5 seconds. Events normally should happen non-stop. If there are no events for a while for a given computer - there is a problem with the computer.

So, I have a monitoring screen that shows 2000 rows - one row for each computer. For each computer I show the timestamp of the last event that was longer than 5 seconds. Result is ordered by the timestamp desc and at the top of the list are computers that have problems.

I could use this simple query:



   , max(EventDateTime) as LastTimeUp




   ComputerID = @ParamComputerID

   EventDurationMSec >= 5000

group by ComputerID

order by LastTimeUp desc;

But, no matter what indexes I created it was still scanning the whole table (or index, but still it was a scan with plenty of reads).

Then I created scalar UDF with one parameter GetLastTimeUp(@ParamComputerID):

select @Result = max(EventDateTime)

from TableLogs

where ComputerID = @ParamComputerID AND EventDurationMSec >= 5000;

I created filtered index

CREATE NONCLUSTERED INDEX [IX_ComputerID_EventDurationMSec] ON [dbo].[TableLogs]


[ComputerID] ASC,

[EventDateTime] ASC


WHERE ([EventDurationMSec]>=(5000))


And changed query to:



   ,dbo.GetLastTimeUp(ComputerID) as LastTimeUp

from TableComputers

order by LastTimeUp desc;

Each call to UDF now is not a scan of an index, it is an instant seek of a filtered index.

Yes, there are 2000 calls and 2000 seeks, but it is way better than scanning through 10 million of all old events, while I need only the latest event.

I would appreciate it if you could comment this. Thank you.

July 12, 2013 4:18 AM

Hugo Kornelis said:

Hi Vladimir,

That's a great example of why "it depends" is the most common answer when it comes to SQL Server questions. There are always exceptions to every rule.

For the record, I think the filtered index would have been useful for the original query as well (but see below). And to make it really work, some massaging and rewriting might have been a good idea. For instance (not sure if this will be best, just throwing out an idea as a starting point):

SELECT c.ComputerID,

  (SELECT MAX(l.EventDateTime)

   FROM   dbo.TableLogs AS l

   WHERE  l.ComputerID = c.ComputerID

   AND    l.EventDurationMSec >= 5000) AS LastTimeUp

FROM   dbo.TableComputers AS c


I expect this to use a scan of TableComputers and a loop join with a seek in the filtered index. (You may need to INCLUDE the EventDurationMSec column in the index, because of what Microsoft calls a "functionality gap" in the optimizer wrt filtered indexes; if you tried a filtered index for the original query and it was not selected, this is why). That's essentially the same as you now get with the UDF, but without the overhead.

On the other hand - I can imagine that the query is currenntly fast enough already that you have better things to spend your time on...

July 21, 2013 5:58 AM

Vladimir said:

Hi Hugo,

Thank you for reply.

Unfortunately, the query that you suggest still scans the index. It uses the filtered index, but scans it instead of seeking.

I made few scripts to generate test tables and test data and checked performance.

If you want, I can post the scripts.

I tried a slightly different approach and tried to use TOP() instead of MAX:



, (






EventDurationMSec >= 5000

AND TableLogs.ComputerID = TableComputers.ID


) as LastTimeUp

FROM TableComputers


And this query has the plan that I was looking for - it is a Nested loop left join between TableComputers and seek&top of index. Actual plan shows that actual number of rows and number of executions of Seek is 2048 - one for each computer ID.

I checked number of reads in profiler.

Variant with TOP has 6,797 reads.

Variant with UDF has 6,157 reads. Still slightly better.

I can only guess why.

With UDF there is scan of TableComputers (11 pages), then 2048 calls of UDF, 3 reads each:

2048*3+11 = 6155 (almost 6157 that profiler tells me)

With TOP there are 2048 seeks, 3 reads each = 6144, so 6797-6144 = 653 reads must be these nested loops that join TableComputers and results of index seek. With UDF there is no join.

But with UDF there is no chance to get parallel plan.

In my case the table is not too big, but if it grows to 100 million rows and optimiser would be smart enough to generate a parallel plan, it could become better than UDF.

So, my point is that UDF can be useful from the performance point of view. I think of it as using hints to optimiser in the query. Sometimes it could be difficult to get the plan you want and UDF could be one way to force it.

July 21, 2013 10:42 PM

Antman said:

Sure, Scalar UDFs are bad because they basically make everything RBAR (Row By Agonizing Row) - there is overhead in having SQL Server prepare and execute the UDF for each and every row.

In the interests of DRY (Don't Repeat Yourself) have you considered keeping your algorithms inside UDFs but rewriting them as Inline Table Valued Functions for speed?

For example:

if exists(select * from sys.objects where object_id=object_id(N'dbo.TripleITVF'))

drop function dbo.TripleITVF


create function dbo.TripleITVF(@Input int)

returns table as

return select [Triple] = 3 * @Input;


Usage comparison:

set statistics time on;

set statistics io on;

select [MaxTriple]=max(dbo.Triple(DataVal)) from dbo.LargeTable;

select [MaxTriple]=max(3 * DataVal) from dbo.LargeTable;

select [MaxTriple]=max(Triple) from dbo.LargeTable cross apply dbo.TripleITVF(DataVal);

set statistics io off;

set statistics time off;

For your example there is no performance difference between inlining the algorithm inside the max() aggregate and using the ITVF version of the UDF.

August 15, 2013 10:55 PM

Mark said:

"But SQL Server isn’t a traditional programming language."

Not to nit pick but SQL Server is not a language at all. I know that Microsoft developers have developed this annoying habbit recently where they don't even bother to say the word "Server" anymore, not to mention that 99.999999% don't know the difference between Sequel and SQL.

August 27, 2013 3:23 PM

Hugo Kornelis said:

Mark: You can pick all you want, but do allow me to nit back at you, okay?

You are totally right about the messed up sentence. I am pretty sure I thought "T-SQL isn't a traditional programming language", but my fingers apparently had a mind of their own, and I didn't notice. I guess I need a better proofreader.

Leaving out the word "Server" is indeed incredibly annoying; I hate it for a multitude of reasons. The most practical being that I lost count of the number of times I see a job ad for a "SQL expert", and don't know if they mean me, or if the content of the ad will reveal it's actually PL/SQL or MySQL they are looking for,

Third: "Sequel" - are you talking about the '70s language developerd by IBM that was later renamed to SQL for trademark reasons, about the MySQL database management product for Mac OS X, or about the "official" pronounciation of SQL in SQL Server (but not in SQL, the language).

August 27, 2013 3:40 PM

Hugo Kornelis said:

Antman: You are way ahead of me with that comment. (My own fault, I guess; I should have finished the series before giving you that chance).

After covering scalar UDF (the bad), I plan to move on to multi-statement UDFs (the ugly) - and then wrap it up with the good: inline UDFs. You took away all the tension. (I still plan to write those other parts - when I've got time again)

August 27, 2013 3:42 PM

Sam said:


It is really a nice post and helped me to understand the difference between UDF and TVF. However I am relatively new in T-SQL programming. if any one can help me here....

I have a below UDF which is being called by some procedure, is taking huge time. After reading this post I understand that using UDF is bad and it hits the performance. Is anyone can help me here to change below UDF into TVF or suggest me any other alternate code.


Create function [dbo].[ufnGetOrgHierarchyByPassingOrgUnitTypeId](@OrgUnitID INT,@OrgUnitTypeId as INT)    

returns varchar(max)    



DECLARE @UnitType INT        

DECLARE @DeptCode VARCHAR(100)            

 ,@FuncCode VARCHAR(100)            

 ,@BUCode VARCHAR(100)            

 ,@SPUCode VARCHAR(100)            

 ,@SubSegCode VARCHAR(100)            

 ,@SegCode VARCHAR(100),        

 @FirstOrgUnitID INT ,    

 @OutputString varchar(max)      

 SET @FirstOrgUnitID = @OrgUnitID        

--SET @OrgUnitID =(select OrgUnitID FROM GiftLocalThreshold )        

DECLARE @DeptID INT =0            

 ,@FuncID INT =0            

 ,@BUID INT =0            

 ,@SPUID INT =0            

 ,@SubSegID INT =0            

 ,@SegID INT =0            

---- Use SET clause to initialize the value instead of SELECT keyword

SELECT @DeptCode = ''            

 ,@FuncCode = ''            

 ,@BUCode = ''            

 ,@SPUCode = ''            

 ,@SubSegCode = ''            

 ,@SegCode = ''          

SELECT @UnitType = OrgUnitTypeID  FROM [GPD_OrgUnitDetails] WHERE OrgUnitID = @OrgUnitID        

DECLARE @NextLevelunitType INT = 0            

SELECT @NextLevelunitType = @UnitType

DECLARE @Code VARCHAR(15) = ''            

IF @NextLevelunitType = 6            


 SELECT @DeptCode = OrgUnitDesc + case when isnull(Active,0)=0 then ' (Inactive)' else '' end,@DeptID=@OrgUnitID            

  ,@Code = ParentCode            

 FROM [GPD_OrgUnitDetails]            

 WHERE OrgUnitID = @OrgUnitID;            

 SELECT @NextLevelunitType = OrgUnitTypeID            

  ,@OrgUnitID = OrgUnitId            

 FROM [GPD_OrgUnitDetails]            

 WHERE Code = @Code            


IF @NextLevelunitType = 5            


 SELECT @FuncCode = OrgUnitDesc+ case when isnull(Active,0)=0 then ' (Inactive)' else '' end,@FuncID=@OrgUnitID            

  ,@Code = ParentCode            

 FROM [GPD_OrgUnitDetails]            

 WHERE OrgUnitID = @OrgUnitID            

 SELECT @NextLevelunitType = OrgUnitTypeID            

  ,@OrgUnitID = OrgUnitId            

 FROM [GPD_OrgUnitDetails]          

 WHERE Code = @Code            


IF @NextLevelunitType = 4            


 SELECT @BuCode = OrgUnitDesc + case when isnull(Active,0)=0 then ' (Inactive)' else '' end          

  ,@Code = ParentCode,@BUID=@OrgUnitID            

 FROM [GPD_OrgUnitDetails]            

 WHERE OrgUnitID = @OrgUnitID            

 SELECT @OrgUnitID = OrgUnitId            

  ,@NextLevelunitType = OrgUnitTypeID            

 FROM [GPD_OrgUnitDetails]            

 WHERE Code = @Code            


IF @NextLevelunitType = 3            


 SELECT @SPUCode = OrgUnitDesc  + case when isnull(Active,0)=0 then ' (Inactive)' else '' end          

  ,@Code = ParentCode            

  ,@NextLevelunitType = OrgUnitTypeID,@SPUID=@OrgUnitID            

 FROM [GPD_OrgUnitDetails]            

 WHERE OrgUnitID = @OrgUnitID          

 SELECT @OrgUnitID = OrgUnitId,@NextLevelunitType = OrgUnitTypeID            

 FROM [GPD_OrgUnitDetails]            

 WHERE Code = @Code            


IF @NextLevelunitType = 2            


 SELECT @SubSegCode = OrgUnitDesc + case when isnull(Active,0)=0 then ' (Inactive)' else '' end          

  ,@Code = ParentCode,@SubSegID=@OrgUnitID            

 FROM [GPD_OrgUnitDetails]            

 WHERE OrgUnitID = @OrgUnitID        

 SELECT @NextLevelunitType = OrgUnitTypeID            

  ,@OrgUnitID = OrgUnitId            

 FROM [GPD_OrgUnitDetails]            

 WHERE Code = @Code            


IF @NextLevelunitType = 1            


 SELECT @SegCode = OrgUnitDesc  + case when isnull(Active,0)=0 then ' (Inactive)' else '' end          

  ,@Code = ParentCode,@SegID=@OrgUnitID            

 FROM [GPD_OrgUnitDetails]            

 WHERE OrgUnitID = @OrgUnitID        

 SELECT @NextLevelunitType = OrgUnitTypeID            

  ,@OrgUnitID = OrgUnitId            

 FROM [GPD_OrgUnitDetails]            

 WHERE Code = @Code            


-- SELECT        

--@OutputString = ( @SegCode+  case when(@SubSegCode!='')  then '/'+@SubSegCode      

--   else '' end      

--   +      

--   case when(@SPUCode!='') then  '/'+@SPUCode      

--   else  '' end    

--   +      

--   case when(@BUCode!='') then '/'+@BUCode      

--   else  ''end    

--   +      

--   case when(@FuncCode!='')  then '/'+@FuncCode      

--   else ''end    

--   +      

--   case when(@DeptCode!='')  then  '/'+@DeptCode      

--   else  '' end  )    

if (@OrgUnitTypeId=1)    

Select @OutputString = @SegCode    

else if (@OrgUnitTypeId=2)    

Select @OutputString = @SubSegCode    

else if (@OrgUnitTypeId=3)    

Select @OutputString = @SPUCode    

else if (@OrgUnitTypeId=4)    

Select @OutputString = @BUCode    

else if (@OrgUnitTypeId=5)    

Select @OutputString = @FuncCode    


Select @OutputString = @DeptCode    

return   @OutputString  ;    



July 14, 2014 3:03 PM

john said:

Hi Hugo

Thanks for the article and have a quick question about schema binding..

I came here when troubleshooting one of our queries taking longer they should and its the issue with scalar UDF our query uses.

as per Adam mechanic's article quoted here, i added schema binding and query performed with same speed as inline code.

But question is , in your example thats not the case so am bit confused whether commit my changes or should i still use inline code. there are lot of places this function being used so i want to avoid if thats not an issue.

Thanks for the advise.

October 2, 2014 8:27 PM

Hugo Kornelis said:

Hi John,

Thanks for your comment!

I am not aware of any Adam Machanic article that recommends schemabinding as a performance measure. And I would be surprised, becuase I am also not aware of any performance benefit of schemabinding. Schemabinding is a method you can use to ensure that objects used in a view or function cannot be changed as long as the view/function exists, which prevennts some bugs. It is also a requirement for indexed views - maybe some confusion comes from this, because indexed views can sometimes help performance and they do need schhemabinding.

But as far as I know, schemabinding by itself does not help performance. I can't explain why you saw performance gain, but I guess you just were lucky.

If possible, change the UDF to an "inline table-valued function", you will probably gain a performance benefit. But beware: ff the current UDF is scalar, this will require some changes to the calling code as well.

October 3, 2014 2:55 AM

Michael said:

Hi Hugo,

What a great post! I thank you for all your valuable advices.

It should be added to the growing list of "what every developer should know about MS SQL Server" ;)

.NET developers are often focused on patterns and best practices to build their applications and forget to care about querying data efficiently. I think that a lot of developers rely on their knowledge in their programming language (like C#) and will apply the same principles - like you mentioned, encapsulation - on their databases... without ever thinking that it could be wrong! I was one of those. Now I am aware of it. Again, thanks a lot.

October 11, 2014 5:30 PM

Lars Haggqvist said:

All very interesting but rather academic. SQL as a language is based on a misunderstood technology. Mr Codd's original theory was never properly implemented (else why do we have a DISTINCT keyword). Therefore SQL is necessarily flawed.

October 19, 2014 8:36 AM

Hugo Kornelis said:

Hi Lars,

Thanks for your comment!

I must admit that I don't really understand it, though. Yes, I will immediately agree that SQL is not a true implementation of the relational model as proposed by Codd. But for me, that leads to the reverse of your conclusion: until we see an implementation of a true relational database that reaches a coverage of at least 5% of the current market for SQL-based databases, I would consider discussions about true relational theory academic. (Not that there's anything wrong with that, a lot of practical progress starts with academic discussion).

The blog post you are commenting on does not pretend to be purely relational. It is, or maybe I should say it tries to be, a very pragmatic approach to a pragmatic problem in a language that may not be as Codd intended it, but is still widely used in many systems. Any blog post that tries to solve practical problems in actual database systems is, in my opinion, anything but academic.

Sorry for the rant. :)

October 19, 2014 4:48 PM

ahmad ali said:

upload a user defined scalar function

December 5, 2014 10:36 AM

aaa said:


February 12, 2015 3:47 AM

Darek said:

@Lars Haggqvist: "else why do we have a DISTINCT keyword"... Well, we do have it because even though primary keys guarantee uniqueness of a row (as the theory requires), you cannot (and should not!) guarantee that every attribute will always be unique. That would be a pretty useless theory, wouldn't it?

March 21, 2016 6:38 AM

Tom said:

I'm surprised that there seems to be so little curiosity about why SQL Server has such horrific performance with scalar UDFs, compared to TVFs, when the computational work performed is identical.

August 4, 2017 11:39 AM

Leave a Comment


About Hugo Kornelis

Hugo is co-founder and R&D lead of perFact BV, a Dutch company that strives to improve analysis methods and to develop computer-aided tools that will generate completely functional applications from the analysis deliverable. The chosen platform for this development is SQL Server. In his spare time, Hugo likes to visit the SQL Server newsgroups, in order to share and enhance his knowledge of SQL Server.
Privacy Statement