How to optimize the abuse of temporary tables and sorting in SQL database
This article introduces how to optimize the abuse of temporary tables and sorting in SQL database. The content is very detailed. Interested friends can use it for reference. I hope it will be helpful to you.
Cursors, temporary tables, triggers, COLLATE, etc.
It is understandable that these are all good things. Why should I take the time to write these things today?
Because I found that many people slowly use these things for a long time and form a habit of moving them out no matter what problems they solve, so I see a lot of beautiful code that doesn't look so good in terms of performance efficiency.
All right, let's cut the crap and get down to business.
Today's case.
Scene:
You need to search for the user by the name keyword entered by the user. The user enters the keyword'x' to search for the user (data comes from table [Name field] or memory [List])
Request:
The order of the results should be:
X
Xia
Xiao
Yx
That is:
Results that include the letter x should be displayed.
The result of the first letter match should be at the top of the list (such as the beginning of x)
Shorter results should be ranked first under the same condition 2 (such as x ahead of xia)
Can you give me a solution of C # and SQL Server (2008)?
Add:
It is best if you can solve Chinese problems together, such as searching for'x'.
The order of the results should be:
X
Xiani
Xia Rong
Xiao Xiaoxiao
Yang Xing
To include the first letter of pinyin of Chinese characters, I wonder if SQL Server supports the search of this feature?
Thank you [the pace of learning] for the questions raised by this netizen
In fact, it is not difficult to solve this problem, it is nothing more than the conversion of Chinese characters to the first letter of Pinyin.
-
Give the first solution first.
-preparation begins--
If object_id ('zhuisuos') is not null
Drop table zhuisuos
Go
Create table zhuisuos
(
Name varchar (100)
)
Insert into zhuisuos values ('recourse')
Insert into zhuisuos values ('recourse 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 ('Xia Rong')
Insert into zhuisuos values ('Xiao')
Insert into zhuisuos values ('Yang Xing')
Go
-- establishing the first letter function of converting Chinese characters to Pinyin
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','A 'union all select' eight','B 'union all
Select 'ZAM','C 'union all select' union all select','D 'union all
Select 'hair','E 'union all select' hair','F 'union all
Select 'union all select','G 'hafnium','H 'union all
Select'no','J 'union all select' cut','K 'union all
Select 'union all select','L 'union all','M'
Select 'union all select','N 'Ooh','O 'union all
Select 'union all select','P 'seven','Q 'union all
Select 'union all select','R 'three','S 'union all
Select'he','T 'union all select' him','W 'union all
Select'Xi','X 'union all select' ya','Y 'union all
Select 'son','Z'
While @ str_len > 0
Begin
Select top 1 @ result=letter+@result,@str_len=@str_len-1
From @ zhuisuo
Where firstspell
