An Alternative to a Data Accessing Function
Published: 30th January 2015
Once again I’m going to refer to previous posts here as this is actually an extension of my previous two posts.

This was something I came across recently when working on a tuning project… the original code was very slow and this was quickly traced to a good old function.

Anyone who’s read my posts before probably knows I generally hate functions, and this situation hasn’t helped that. But at least there was a way out of this one, and one which reduced the duration and reads drastically in the process.

Obviously I can’t use the exact example I was dealing with so I’m going to invent a function to work with. It’s basically a very weird username generator.

I’m going to use AdventureWorks2012 and for each person registered in the system I’m going to generate them a username for a new system based on their first name, first 3 characters of their last name, then (bear with me) the last 5 digits of the product number ordered where the salesOrderDetailID equals their businessEntityID, and the floored unitPrice of said salesOrderDetailID.

I did tell you it was weird, but I wanted something that would be an in-line function yet would access multiple sets of data and involve temp tables etc (not strictly necessary, but I’m trying to over complicate it on purpose).

So here’s the function I came up with:

use AdventureWorks2012
go

if object_id('userNameGenerate') is not null drop function userNameGenerate
go

create function userNameGenerate(@personID int)
returns varchar(50)
begin
   return
  
(
      
select p.FirstName + left(p.LastName, 3) +
              
right(pp.ProductNumber, 5) +
              
convert(varchar, convert(int, floor(d.unitPrice)))
      
from Person.Person p
      
join sales.SalesOrderDetail d
      
on p.BusinessEntityID = d.SalesOrderDetailID
      
join Production.Product pp
      
on d.ProductID = pp.ProductID
      
where BusinessEntityID = @personID
  
)
end
go


Let’s run the following query just to see our output and the duration and reads that this function causes:

select BusinessEntityID, firstName, lastName, dbo.userNameGenerate(businessEntityID)
from person.person
go


So what’s the alternative?

Well, firstly this is effectively running on a row by row basis and SQL Server hates that. Therefore we need to change the code to run on a set basis. BUT we need this code to be centralized and re-usable, like a function, so that editing would be simple and only need doing once.

This could be done by placing the code into a table valued function, but those are just horrific (Google TVFs are evil and you’ll see plenty of hits… likely including some from this website).

Also you have the problem that there are restrictions on what can and can’t go into a TVF and with us trying to re-code a little to make the process more set based, this could get messy. Not in this example, but you can easily imagine other, more complex scenarios making this impossible.

So what’s the answer? Well… Use a stored procedure with a table type parameter.

We could re-write the function into the following set based stored procedure:

use AdventureWorks2012
go

if object_id('userNameGenerate') is not null drop procedure userNameGenerate
go

if exists
(
  
select name
  
from sys.table_types
  
where name = 'userListType'
)
begin
   drop type
userListType
end

create type
userListType as table
(
  
userID varchar(100)
)
go

create procedure userNameGenerate
(
  
@userList userListType readonly
)
as
   select
p.FirstName + left(p.LastName, 3) +
          
right(pp.ProductNumber, 5) +
          
convert(varchar, convert(int, floor(d.unitPrice)))
  
from Person.Person p
  
join @userList u
  
on p.BusinessEntityID = u.userID
  
join sales.SalesOrderDetail d
  
on p.BusinessEntityID = d.SalesOrderDetailID
  
join Production.Product pp
  
on d.ProductID = pp.ProductID
go
And now we can call it as follows:
declare @users userListType
insert into @users
select BusinessEntityID
from person.Person

exec userNameGenerate @users
go


We get exactly the same output, but let’s have a look at the duration and reads now?

Massively improved. And now we have re-usable, centralized code which is fast, efficient, and, unlike a function, will not prohibit parallelism if required.

I would definitely recommend that you look this trying this out in your own environments if you can.
Comments:
NB: Comments will only appear once they have been moderated.