SQL Server user defined function to calculate age bracket

Question

I created a UDF to calculate age bracket in the database. i used the following codes

CREATE FUNCTION Agebracket(@Ages INT)
RETURNS VARCHAR
AS
BEGIN
DECLARE @Age_Group varchar 

SET @Age_Group = CASE WHEN @Ages BETWEEN 0 AND 9 THEN  '[0-9]'
                 WHEN @Ages BETWEEN 10 AND 19 THEN  '[10-19]'
                 WHEN @Ages BETWEEN 20 AND 29 THEN  '[20-29]'
                 WHEN @Ages BETWEEN 30 AND 39 THEN  '[30-39]'
                 WHEN @Ages BETWEEN 40 AND 49 THEN  '[40-49]'
                 WHEN @Ages BETWEEN 50 AND 59 THEN  '[50-59]'
                 WHEN @Ages BETWEEN 60 AND 69 THEN  '[60-69]'
                 WHEN @Ages BETWEEN 70 AND 79 THEN  '[70-79]'
                 WHEN @Ages BETWEEN 80 AND 89 THEN  '[80-89]'
                 WHEN @Ages BETWEEN 90 AND 99 THEN  '[90-99]'
                 WHEN @Ages>=100 THEN  '[100+]'  end  
RETURN @Age_Group
END

when i test with the example below:

SELECT  [dbo].[Agebracket](10)

The output came out as [.

Any idea on what i can do as i expect the output to be [10-19]

Alan Burstein · Accepted Answer

If performance is important then a scalar function is not for you. Inline table valued functions (itvf) almost always perform better. Turning what Alexei posted into an itvf makes the function 6 times faster on my pc. Let me demonstrate. First, here's a solution that uses CHOOSE. I like CHOOSE here because it's cleaner (but isn't any faster than an old-school CASE statement).

CREATE FUNCTION dbo.agebracket(@Ages tinyint) 
RETURNS VARCHAR(10) AS
BEGIN RETURN '['+(isnull(choose(@ages/10+1,'0-9','10-19','20-29','30-39',
             '40-49','50-59','60-69','70-79','80-89','90-99'),'100+'))+']' END

Note that I use tinyint because we don't want negative numbers and 256 is enough for handling age (unless you're talking about countries, dinosaur bones, etc)...

Now let's re-write this as an inline table valued function.

CREATE FUNCTION dbo.agebracket_itvf(@Ages tinyint) 
RETURNS TABLE AS RETURN 
SELECT ages = 
'['+(isnull(choose(@ages/10+1,'0-9','10-19','20-29','30-39',
             '40-49','50-59','60-69','70-79','80-89','90-99'),'100+'))+']';

Next some sample data for a performance test.

if object_id('tempdb..#ageList') is not null drop table #ageList;
GO
create table #ageList (age tinyint);
insert #ageList
select top (1000000) abs(checksum(newid())%100)+1
from sys.all_columns a, sys.all_columns b;

Before we test, here's how you use each function:

-- scalar version
select top(10) t.age, ages = dbo.agebracket(t.age)
from #ageList t;

-- itvf version
select top(10) t.age, fn.ages
from #ageList t
cross apply dbo.agebracket_itvf(t.age) fn;

Results:

age  ages
---- ----------
76   [70-79]
19   [10-19]
32   [30-39]
58   [50-59]
40   [40-49]
22   [20-29]
41   [40-49]
66   [60-69]
74   [70-79]
31   [30-39]    

age  ages
---- -------
76   [70-79]
19   [10-19]
32   [30-39]
58   [50-59]
40   [40-49]
22   [20-29]
41   [40-49]
66   [60-69]
74   [70-79]
31   [30-39]

Now the performance test.

print 'scalar version'+char(13)+char(10)+replicate('-',50);
go
declare @st datetime = getdate(), @x varchar(10);
select @x = dbo.agebracket(t.age)
from #ageList t
print datediff(ms,@st,getdate());
GO 3

print 'itvf version'+char(13)+char(10)+replicate('-',50);
go
declare @st datetime = getdate(), @x varchar(10);
select @x = fn.ages
from #ageList t
cross apply dbo.agebracket_itvf(t.age) fn
print datediff(ms,@st,getdate());
GO 3

Here's the results. Again, the itvf version was 6X faster!

scalar version
--------------------------------------------------
Beginning execution loop
2140
2167
2267
Batch execution completed 3 times.

itvf version 
--------------------------------------------------
Beginning execution loop
380
383
370
Batch execution completed 3 times.

SQL Server user defined function to calculate age bracket

Answers (2)

Related Questions