谈谈SQL数据库中滥用临时表、排序的解决方案优化（举例：汉字转拼音函数）_SQL Server_数据库_程序员俱乐部

游标、临时表、触发器、COLLATE等等……
无可厚非、这些都是好东西，我为什么今天要花时间来写这些东西呢？
是因为我发现慢慢的很多人用久了这些东西之后会形成一种习惯，不管解决什么问题动不动都会把它们搬出来，由此我看到了很多漂亮的代码在性能效率面前却显得不那么优秀。

好了废话不多说开始进入正题吧。

今天的案例

场景：

需要通过用户输入的姓名关键字来搜索用户。用户输入关键字'x'来搜索用户（数据来源于表[Name字段中]或内存[List<UserInfo>]中）

要求：

得到的结果排序应为：

x

xia

xiao

yx

即：

1. 包含x字母的结果均应显示出来
2. 首字母匹配的结果应该排在前面(如x开头)
3. 在条件2相同的前提下更短的结果应排在前面(如x排在xia前面)

补充：

如果能一起解决中文问题最好，如搜索'x'

得到的结果排序应为：

x

xiani

夏荣

肖小笑

杨星

即将汉字的拼音首字母纳入在内，不知SQL Server是否支持这一特性的搜索？

?

感谢[学习的脚步]这位网友提出来的问题

其实要解决这个问题不难，无非就是汉字转拼音首字母

---------------------------------------------------------------------------------------------

先给出解决方案一

---------------------准备工作 开始-------------------------------
if?object_id('zhuisuos')is?not?null
drop?table?zhuisuos
go

create?table?zhuisuos
(
name?varchar(100)
)
insert?into?zhuisuos?values('追索')
insert?into?zhuisuos?values('追索2')
insert?into?zhuisuos?values('xia')
insert?into?zhuisuos?values('dxc')
insert?into?zhuisuos?values('x')
insert?into?zhuisuos?values('xx')
insert?into?zhuisuos?values('xiani')
insert?into?zhuisuos?values('yx')
insert?into?zhuisuos?values('夏荣')
insert?into?zhuisuos?values('肖小笑')
insert?into?zhuisuos?values('杨星')
go
-------------------------------------------------------------------------------
--建立汉字转拼音首字母函数
if?object_id('fn_getpy1')is?not?null
drop?function?fn_getpy1
go

GO
create???function???[dbo].fn_getpy1
(@str???nvarchar(4000))?
returns???nvarchar(4000)?
as?
begin?
declare???@str_len???int,@result???nvarchar(4000)?
declare???@zhuisuo???table
(firstspell???nchar(1)?? collate?? Chinese_PRC_CI_AS,
letter???nchar(1))?
set?@str_len=len(@str)
set?@result=?'?'?
insert???into???@zhuisuo
(firstspell,letter)?
????select???'吖?',?'A?'???union???all???select???'八?',?'B?'???union???all?
????select???'嚓?',?'C?'???union???all???select???'咑?',?'D?'???union???all?
????select???'妸?',?'E?'???union???all???select???'发?',?'F?'???union???all?
????select???'旮?',?'G?'???union???all???select???'铪?',?'H?'???union???all?
????select???'丌?',?'J?'???union???all???select???'咔?',?'K?'???union???all?
????select???'垃?',?'L?'???union???all???select???'嘸?',?'M?'???union???all?
????select???'拏?',?'N?'???union???all???select???'噢?',?'O?'???union???all?
????select???'妑?',?'P?'???union???all???select???'七?',?'Q?'???union???all?
????select???'呥?',?'R?'???union???all???select???'仨?',?'S?'???union???all?
????select???'他?',?'T?'???union???all???select???'屲?',?'W?'???union???all?
????select???'夕?',?'X?'???union???all???select???'丫?',?'Y?'???union???all?
????select???'帀?',?'Z?'?
????while???@str_len>?0?
????begin?
????????select???top???1???@result=letter+@result,@str_len=@str_len-1?
????????????from???@zhuisuo?????
????????????where?? firstspell?<=substring(@str,@str_len,1)?
????????????order???by?? firstspell???desc?
????????if???@@rowcount=0?
??????????select???@result=substring(@str,@str_len,1)+@result,@str_len=@str_len-1?
????end?
????return(@result)?
end
---------------------准备工作 结束-------------------------------

--正式查询
declare?@str?varchar(10)
set?@str='x'
create?table?#result
(name?varchar(100)?null,id?int?null,lens?int?null)

insert?into?#result?
select?name,1,len(name)?from?zhuisuos
where?name?like?@str+'%'

insert?into?#result
select?name,2,len(name)?from?zhuisuos
where?name?like?'%'+@str+'%'?and?name?not?like?@str+'%'

insert?into?#result
select?name,3,len(name)?from?zhuisuos
where?dbo.fn_getpy1 (name)?like?@str+'%'?and?name?not?like?@str+'%'?and?name?not?like?'%'+@str+'%'

insert?into?#result
select?name,4,len(name)?from?zhuisuos
where?dbo.fn_getpy1 (name)?like?'%'+@str+'%'?and?dbo.fn_getpy1 (name)?not?like?@str+'%'
??and? name?not?like?@str+'%'?and?name?not?like?'%'+@str+'%'

select?name?from?#result
order?by?id,lens
drop?table?#result

这个解决方案已经满足查询要求

其它都不管 我们重点来看看这次写的这个函数

象这样的汉字转拼音函数在网上一搜一大把 今天我就要举例几个方案让大家对优化及开销有个清楚的概念

解决方案一写的函数实在是太糟糕了（以上及接下来举出的案例并无冒犯任何雷同及原创代码之意，还请多多包涵）

?

为什么这么说呢

这是它的执行计划

它用了临时表并且排序

表插入开销0.01? 表扫描开销0.003 表排序0.011

估计总开销0.0246

实际执行：我拿1万行数据调用此函数花了我20几秒、一个查询操作你愿意等20多秒吗

所以看到这样的执行计划实在很抱歉

解决方案二

create?function?[dbo].[fn_getpy2](@Str?varchar(500)='')
returns?varchar(500)
as
begin
declare?@strlen?int,@return?varchar(500),@ii?int
declare?@n?int,@c?char(1),@chn?nchar(1)

select?@strlen=len(@str),