--前提:
CREATE TABLE [c_py] (
[chn] [char] (2) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[py] [char] (1) COLLATE Chinese_PRC_CI_AS NOT NULL ,
PRIMARY KEY CLUSTERED
(
[chn]
) ON [PRIMARY]
) ON [PRIMARY]
GO
SET QUOTED_IDENTIFIER OFF
GO
INSERT c_py(chn,py) values("吖","A")
INSERT c_py(chn,py) values("八","B")
INSERT c_py(chn,py) values("嚓","C")
INSERT c_py(chn,py) values("咑","D")
INSERT c_py(chn,py) values("妸","E")
INSERT c_py(chn,py) values("发","F")
INSERT c_py(chn,py) values("旮","G")
INSERT c_py(chn,py) values("铪","H")
INSERT c_py(chn,py) values("丌","J")
INSERT c_py(chn,py) values("咔","K")
INSERT c_py(chn,py) values("垃","L")
INSERT c_py(chn,py) values("嘸","M")
INSERT c_py(chn,py) values("拏","N")
INSERT c_py(chn,py) values("噢","O")
INSERT c_py(chn,py) values("妑","P")
INSERT c_py(chn,py) values("七","Q")
INSERT c_py(chn,py) values("呥","R")
INSERT c_py(chn,py) values("仨","S")
INSERT c_py(chn,py) values("他","T")
INSERT c_py(chn,py) values("屲","W")
INSERT c_py(chn,py) values("夕","X")
INSERT c_py(chn,py) values("丫","Y")
INSERT c_py(chn,py) values("帀","Z")go
--问题代码:(都是函数)
--函数一:
create function f_ch2py(@chn nchar(1))
returns char(1)
as
begin
declare @py char(1) select top 1 @py = py
from c_py (nolock)
where chn <= @chn
order by chn COLLATE Chinese_PRC_CI_AS desc return(isnull(@py,''))
endgo
--函数二:
create function f_st2zjm(@st nvarchar(4000))
returns varchar(100) --总笔数,字母和数字按一笔算。
as
begin
declare @bh varchar(100)
declare @n int
set @n = 1
set @bh = ''
while @n <= len(@st) and @n < 31
begin
if unicode(substring(@st,@n,1)) between 48 and 57 --数字
set @bh = @bh + substring(@st,@n,1)
else if unicode(substring(@st,@n,1)) between 65 and 90 --大写字母
set @bh = @bh + substring(@st,@n,1)
else if unicode(substring(@st,@n,1)) between 97 and 122 --小写
set @bh = @bh + upper(substring(@st,@n,1))
else if substring(@st,@n,1) = '参' --多音字
set @bh = @bh + 'S'
else if unicode(substring(@st,@n,1)) between 19968 and 40869 --汉字
set @bh = @bh + dbo.f_ch2py(substring(@st,@n,1))
set @n = @n + 1
end
return(@bh)
end /*
问题:
select dbo.f_st2zjm(columnname) from tabname,记录结果一万四千多行,每列字符汉字平均15个。
上面这条语句在SQL2000下四秒钟,而在SQL2005下将近三分钟才能出来结果?而且SQL2005所在服务器的硬件比2000的好至少三倍。
应该不是硬件问题,因为我在很多的企业都证实过这个问题。
*/
CREATE TABLE [c_py] (
[chn] [char] (2) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[py] [char] (1) COLLATE Chinese_PRC_CI_AS NOT NULL ,
PRIMARY KEY CLUSTERED
(
[chn]
) ON [PRIMARY]
) ON [PRIMARY]
GO
SET QUOTED_IDENTIFIER OFF
GO
INSERT c_py(chn,py) values("吖","A")
INSERT c_py(chn,py) values("八","B")
INSERT c_py(chn,py) values("嚓","C")
INSERT c_py(chn,py) values("咑","D")
INSERT c_py(chn,py) values("妸","E")
INSERT c_py(chn,py) values("发","F")
INSERT c_py(chn,py) values("旮","G")
INSERT c_py(chn,py) values("铪","H")
INSERT c_py(chn,py) values("丌","J")
INSERT c_py(chn,py) values("咔","K")
INSERT c_py(chn,py) values("垃","L")
INSERT c_py(chn,py) values("嘸","M")
INSERT c_py(chn,py) values("拏","N")
INSERT c_py(chn,py) values("噢","O")
INSERT c_py(chn,py) values("妑","P")
INSERT c_py(chn,py) values("七","Q")
INSERT c_py(chn,py) values("呥","R")
INSERT c_py(chn,py) values("仨","S")
INSERT c_py(chn,py) values("他","T")
INSERT c_py(chn,py) values("屲","W")
INSERT c_py(chn,py) values("夕","X")
INSERT c_py(chn,py) values("丫","Y")
INSERT c_py(chn,py) values("帀","Z")go
--问题代码:(都是函数)
--函数一:
create function f_ch2py(@chn nchar(1))
returns char(1)
as
begin
declare @py char(1) select top 1 @py = py
from c_py (nolock)
where chn <= @chn
order by chn COLLATE Chinese_PRC_CI_AS desc return(isnull(@py,''))
endgo
--函数二:
create function f_st2zjm(@st nvarchar(4000))
returns varchar(100) --总笔数,字母和数字按一笔算。
as
begin
declare @bh varchar(100)
declare @n int
set @n = 1
set @bh = ''
while @n <= len(@st) and @n < 31
begin
if unicode(substring(@st,@n,1)) between 48 and 57 --数字
set @bh = @bh + substring(@st,@n,1)
else if unicode(substring(@st,@n,1)) between 65 and 90 --大写字母
set @bh = @bh + substring(@st,@n,1)
else if unicode(substring(@st,@n,1)) between 97 and 122 --小写
set @bh = @bh + upper(substring(@st,@n,1))
else if substring(@st,@n,1) = '参' --多音字
set @bh = @bh + 'S'
else if unicode(substring(@st,@n,1)) between 19968 and 40869 --汉字
set @bh = @bh + dbo.f_ch2py(substring(@st,@n,1))
set @n = @n + 1
end
return(@bh)
end /*
问题:
select dbo.f_st2zjm(columnname) from tabname,记录结果一万四千多行,每列字符汉字平均15个。
上面这条语句在SQL2000下四秒钟,而在SQL2005下将近三分钟才能出来结果?而且SQL2005所在服务器的硬件比2000的好至少三倍。
应该不是硬件问题,因为我在很多的企业都证实过这个问题。
*/
[chn] [char] (2) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[py] [char] (1) COLLATE Chinese_PRC_CI_AS NOT NULL ,
PRIMARY KEY CLUSTERED
(
[chn]
) ON [PRIMARY]
) ON [PRIMARY]
GO
SET QUOTED_IDENTIFIER OFF
GO
INSERT c_py(chn,py) values("吖","A")
INSERT c_py(chn,py) values("八","B")
INSERT c_py(chn,py) values("嚓","C")
INSERT c_py(chn,py) values("咑","D")
INSERT c_py(chn,py) values("妸","E")
INSERT c_py(chn,py) values("发","F")
INSERT c_py(chn,py) values("旮","G")
INSERT c_py(chn,py) values("铪","H")
INSERT c_py(chn,py) values("丌","J")
INSERT c_py(chn,py) values("咔","K")
INSERT c_py(chn,py) values("垃","L")
INSERT c_py(chn,py) values("嘸","M")
INSERT c_py(chn,py) values("拏","N")
INSERT c_py(chn,py) values("噢","O")
INSERT c_py(chn,py) values("妑","P")
INSERT c_py(chn,py) values("七","Q")
INSERT c_py(chn,py) values("呥","R")
INSERT c_py(chn,py) values("仨","S")
INSERT c_py(chn,py) values("他","T")
INSERT c_py(chn,py) values("屲","W")
INSERT c_py(chn,py) values("夕","X")
INSERT c_py(chn,py) values("丫","Y")
INSERT c_py(chn,py) values("帀","Z")
go
--问题代码:(都是函数)
--函数一:
create function f_ch2py(@chn nchar(1))
returns char(1)
as
begin
declare @py char(1) select top 1 @py = py
from c_py (nolock)
where chn <= @chn
order by chn COLLATE Chinese_PRC_CI_AS desc return(isnull(@py,''))
end go
--函数二:
create function f_st2zjm(@st nvarchar(4000))
returns varchar(100) --总笔数,字母和数字按一笔算。
as
begin
declare @bh varchar(100)
declare @n int
set @n = 1
set @bh = ''
while @n <= len(@st) and @n < 31
begin
if unicode(substring(@st,@n,1)) between 48 and 57 --数字
set @bh = @bh + substring(@st,@n,1)
else if unicode(substring(@st,@n,1)) between 65 and 90 --大写字母
set @bh = @bh + substring(@st,@n,1)
else if unicode(substring(@st,@n,1)) between 97 and 122 --小写
set @bh = @bh + upper(substring(@st,@n,1))
else if substring(@st,@n,1) = '参' --多音字
set @bh = @bh + 'S'
else if unicode(substring(@st,@n,1)) between 19968 and 40869 --汉字
set @bh = @bh + dbo.f_ch2py(substring(@st,@n,1))
set @n = @n + 1
end
return(@bh)
end go
select chn=dbo.f_st2zjm(chn),py=dbo.f_st2zjm(py) from c_py/*
chn py
---------- ----------
A A
B B
C C
D D
E E
F F
G G
H H
J J
K K
L L
M M
N N
O O
P P
Q Q
R R
S S
T T
W W
X X
Y Y
Z Z(23 row(s) affected)
*/drop function dbo.f_ch2py
drop function dbo.f_st2zjm
drop table c_py
而 sql2005装在了 奔三500M上