用于计算年龄段的SQL Server用户定义函数

如果性能很重要,那么标量函数不适合您。 内联表值函数(itvf)几乎总是性能更好。 将Alexei发布的内容转换为itvf使功能速度提高了6倍 在我的电脑上。让我演示一下。首先,这里有一个解决方案 CHOOSE . 我喜欢 CHOOSE 这是因为它更干净(但并不比一个老派的案例陈述快)。

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

请注意,我使用tinyint是因为我们不需要负数,256足以处理年龄(除非你谈论的是国家、恐龙骨骼等)。。。

现在,让我们将其重新编写为内联表值函数。

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+'))+']';

接下来是一些性能测试的示例数据。

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;

在测试之前,以下是您如何使用每个函数:

-- 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;

结果:

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]

现在是性能测试。

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

以下是结果。 同样,itvf版本的速度快了6倍!

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.

代替 DECLARE @Age_Group varchar 具有 DECLARE @Age_Group varchar(8) 并使您的函数返回 varchar(8) .

工作版本:

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

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
GO

SELECT  [dbo].[Agebracket](10) 

这是因为SQL Server假定VARCHAR=VARCHAR(1),甚至更糟的是,它会自动截断这些值。