MSSQL网页编程之数据库查询了局的静态排序

优化的SQL查询算法，有效地提高查询速度静态|排序|数据|数据库
在大众旧事组中，一个常常呈现的成绩是“如何才干依据传送给存储历程的参数前往一个排序的输入？”。在一些高程度专家的匡助之下，我收拾出了这个成绩的几种办理计划。

1、用IF...ELSE实行事后编写好的查询


　　关于年夜多半人来讲，起首想到的做法大概是：经由过程IF...ELSE语句，实行几个事后编写好的查询中的一个。比方，假定要从Northwind数据库查询失掉一个货主（Shipper）的排序列表，收回挪用的代码以存储历程参数的情势指定一个列，存储历程依据这个列排序输入了局。Listing1显现了这类存储历程的一个大概的完成（GetSortedShippers存储历程）。


【Listing1:用IF...ELSE实行多个事后编写好的查询中的一个】


CREATEPROCGetSortedShippers

@OrdSeqASint

AS


IF@OrdSeq=1

SELECT*FROMShippersORDERBYShipperID

ELSEIF@OrdSeq=2

SELECT*FROMShippersORDERBYCompanyName

ELSEIF@OrdSeq=3

SELECT*FROMShippersORDERBYPhone



　　这类办法的长处是代码很复杂、很简单了解，SQLServer的查询优化器可以为每个SELECT查询创立一个查询优化企图，确保代码具有最优的功能。这类办法最次要的弱点是，假如查询的请求产生了改动，你必需修正多个自力的SELECT查询——在这里是三个。


2、用列名字作为参数

　　别的一个选择是让查询以参数的情势吸收一个列名字。Listing2显现了修正后的GetSortedShippers存储历程。CASE表达式依据吸收到的参数，断定SQLServer在ORDERBY子句中利用哪个列值。注重，ORDERBY子句中的表达式并未在SELECT清单中呈现。在ANSISQL-92尺度中，ORDERBY子句中不同意呈现没有在SELECT清单中指定的表达式，但ANSISQL-99尺度同意。SQLServer一向同意这类用法。


【Listing2：用列名字作为参数，第一次实验】


CREATEPROCGetSortedShippers

@ColNameASsysname

AS


SELECT*

FROMShippers

ORDERBY

CASE@ColName

WHENShipperIDTHENShipperID

WHENCompanyNameTHENCompanyName

WHENPhoneTHENPhone

ELSENULL

END



　　如今，我们来试一下新的存储历程，以参数的情势指定ShipperID列：


EXECGetSortedShippersShipperID



　　此时统统一般。可是，当我们视图把CompanyName列作为参数挪用存储历程时，它不再无效：


EXECGetSortedShippersCompanyName



　　细心看一下毛病信息：


Server:Msg245,Level16,State1,ProcedureGetSortedShippers,Line5

SyntaxerrorconvertingthenvarcharvalueSpeedy

Expresstoacolumnofdatatypeint.



　　它显现出，SQLServer试图把“SpeedyExpress”（nvarchar数据范例）转换成一个整数值——固然，这个操纵是不成能乐成的。呈现毛病的缘故原由在于，依照“数据范例优先级”划定规矩，CASE暗示式中最高优先级的数据范例决意了表达式前往值的数据范例。“数据范例优先级”划定规矩能够在SQLServerBooksOnline（BOL）找到，它划定了int数据范例的优先级要比nvarchar数据范例高。后面的代码请求SQLServer依照CompanyName排序输入，CompanyName是nvarchar数据范例。这个CASE表达式的前往值多是ShipperID（int范例），多是CompanyName（nvarchar范例），或Phone（nvarchar范例）。因为int范例具有较高的优先级，因而CASE表达式前往值的数据范例应当是int。


为了不呈现这类转换毛病，我们能够实验把ShipperID转换成varchar数据范例。接纳这类办法以后，nvarchar将作为最高优先级的数据范例被前往。Listing3显现了修正后的GetSortedShippers存储历程。

【Listing3：用列名字作为参数，第二次实验】


ALTERPROCGetSortedShippers

@ColNameASsysname

AS


SELECT*

FROMShippers

ORDERBY

CASE@ColName

WHENShipperID

THENCAST(ShipperIDASvarchar(11))

WHENCompanyName

THENCompanyName

WHENPhone

THENPhone

ELSENULL

END



　　如今，假定我们再把三个列名字中的恣意一个作为参数挪用存储历程，输入了局看起来准确。看起来就象指定的列准确地为查询输入供应了排序尺度。但这个表只要三个货主，它们的ID分离是1、2、3。假定我们把更多的货主到场到表，如Listing4所示（ShipperID列有IDENTITY属性，SQLServer主动为该列天生值）。


【Listing4：向Shippers表拔出一些纪录】


INSERTINTOShippersVALUES(Shipper4,(111)222-9999)

INSERTINTOShippersVALUES(Shipper5,(111)222-8888)

INSERTINTOShippersVALUES(Shipper6,(111)222-7777)

INSERTINTOShippersVALUES(Shipper7,(111)222-6666)

INSERTINTOShippersVALUES(Shipper8,(111)222-5555)

INSERTINTOShippersVALUES(Shipper9,(111)222-4444)

INSERTINTOShippersVALUES(Shipper10,(111)222-3333)



　　如今挪用存储历程，指定ShipperID作为排序列：


EXECGetSortedShippersShipperID



　　表一显现了存储历程的输入。ShipperID即是10的纪录地位毛病，由于这个存储历程的排序输入是字符排序，而不是整数排序。依照字符排序时，10分列在2的后面，由于10的入手下手字符是1。


表一：纪录排序毛病的查询了局


ShipperIDCompanyNamePhone

1SpeedyExpress(503)555-9831

10Shipper10(111)222-3333

2UnitedPackage(503)555-3199

3FederalShipping(503)555-9931

4Shipper4(111)222-9999

5Shipper5(111)222-8888

6Shipper6(111)222-7777

7Shipper7(111)222-6666

8Shipper8(111)222-5555

9Shipper9(111)222-4444


为懂得决这个成绩，我们能够用前置的0补足ShipperID值，使得ShipperID值都有一样的长度。依照这类办法，基于字符的排序具有和整数排序一样的输入了局。修正后的存储历程如Listing5所示。十个0被置于ShipperID的相对值之前，而在了局中，代码只是利用最右侧的10个字符。SIGN函数断定在负数的后面加上加号（+）前缀，仍是在正数的后面加上负号（-）前缀。依照这类办法，输入了局老是有11个字符，包括一个“+”或“-”字符、前导的字符0和ShipperID的相对值。

【Listing5：用列名字作为参数，第三次实验】


ALTERPROCGetSortedShippers

@ColNameASsysname

AS


SELECT*

FROMShippers

ORDERBY

CASE@ColName

WHENShipperIDTHENCASESIGN(ShipperID)

WHEN-1THEN-

WHEN0THEN+

WHEN1THEN+

ELSENULL

END+

RIGHT(REPLICATE(0,10)+

CAST(ABS(ShipperID)ASvarchar(10)),10)

WHENCompanyNameTHENCompanyName

WHENPhoneTHENPhone

ELSENULL

END



　　假如ShipperID的值都是负数，加上标记前缀就没有需要，但为了让计划合用于尽量多的局限，本例加上了标记前缀。排序时“-”在“+”的后面，以是它能够用于正、正数混同排序的情形。


　　如今，假如我们用恣意三个列名字之一作为参数挪用存储历程，存储历程都可以准确地前往了局。RichardRomley提出了一种奇妙的处置办法，如Listing6所示。它不再请求我们弄分明大概触及的列数据范例。这类办法把ORDERBY子句分红三个自力的CASE表达式，每个表达式处置一个分歧的列，制止了因为CASE只前往一种特定命据范例的才能而招致的成绩。


【Listing6：用列名字作为参数，Romley提出的办法】


ALTERPROCGetSortedShippers

@ColNameASsysname

AS


SELECT*

FROMShippers

ORDERBY

CASE@ColNameWHENShipperID

THENShipperIDELSENULLEND,

CASE@ColNameWHENCompanyName

THENCompanyNameELSENULLEND,

CASE@ColNameWHENPhone

THENPhoneELSENULLEND



　　依照这类办法编写代码，SQLServer可以为每个CASE表达式前往得当的数据范例，并且无需举行数据范例转换。但应当注重的是，只要当指定的列不必要举行盘算时，索引才干够优化排序操纵。


3、用列号作为参数

　　就象第一个计划所显现地那样，你大概更喜好用列的编号作为参数，而不是利用列的名字（列的编号即一个代表你想要作为排序根据的列的数字）。这类办法的基础头脑与利用列名字作为参数的头脑一样：CASE表达式依据指定的列号断定利用哪个列举行排序。Listing7显现了修正后的GetSortedShippers存储历程。


【Listing7：用列号作为参数】


ALTERPROCGetSortedShippers

@ColNumberASint

AS


SELECT*

FROMShippers

ORDERBY

CASE@ColNumber

WHEN1THENCASESIGN(ShipperID)

WHEN-1THEN-

WHEN0THEN+

WHEN1THEN+

ELSENULL

END+

RIGHT(REPLICATE(0,10)+

CAST(ABS(ShipperID)ASvarchar(10)),10)

WHEN2THENCompanyName

WHEN3THENPhone

ELSENULL

END



　　固然，在这里你也能够利用Richard的办法，制止ORDERBY子句中列数据范例带来的成绩。假如要依据ShipperID排序输入，你能够依照上面的体例挪用修正后的GetSortedShippers存储历程：


EXECGetSortedShippers1


4、静态实行

　　利用静态实行手艺，我们可以更轻松地编写出GetSortedShippers存储历程。利用这类办法时，我们只需静态地机关出SELECT语句，然后用EXEC()命令实行这个SELECT语句。假定传送给存储历程的参数是列的名字，存储历程能够年夜年夜延长：


ALTERPROCGetSortedShippers

@ColNameASsysname

AS

EXEC(SELECT*FROMShippersORDERBY+

@ColName)



　　在SQLServer2000和7.0中，你能够用体系存储历程sp_ExecuteSQL替换Exec()命令。BOL申明了利用sp_ExecuteSQL比利用Exec()命令更有益的中央。一样平常地，假如满意以下三个前提，你可以在不授与存储历程所触及工具权限的情形下，授与实行存储历程的权限：起首，只利用DataManipulationLanguage（DML）言语（即SELECT，INSERT，UPDATE，DELETE）；其次，一切被援用的工具都有与存储历程一样的一切者；第三，没有利用静态命令。


　　下面的存储历程不克不及满意第三个前提。在这类情形下，你必需为一切必要利用存储历程的用户和组显式地授与Shippers表的SELECT权限。假如这一点能够承受的话，统统不存在成绩。相似地，你能够修正存储历程，使它承受一个列号参数，如Listing8所示。


【Listing8：用列号作为参数，静态实行（代码较长的办法）】


ALTERPROCGetSortedShippers

@ColNumberASint

AS


DECLARE@cmdASvarchar(8000)


SET@cmd=SELECT*FROMShippersORDERBY+

CASE@ColNumber

WHEN1THENShipperID

WHEN2THENCompanyName

WHEN3THENPhone

ELSENULL

END


EXEC(@cmd)



　　注重，当你利用了函数时，你应当在一个变量而不是EXEC()命令内机关SELECT语句。此时，CASE表达式静态地断定利用哪个列。另有一种更冗长的格局，T-SQL同意在ORDERBY子句中指定SELECT清单中列的地位，如Listing9所示。这类格局服从了SQL-92尺度，但ANSISQL-99尺度不撑持这类格局，以是最好不要利用这类格局。


【Listing9：列号作为参数，静态实行（代码较短的办法）】


ALTERPROCGetSortedShippers

@ColNumberASint

AS

DECLARE@cmdASvarchar(8000)

SET@cmd=SELECT*FROMShippersORDERBY+CAST(@ColNumberASvarchar(4))


EXEC(@cmd)


5、用户界说函数

　　假如你利用的是SQLServer2000，想要编写一个用户界说的函数（UDF），这个用户界说函数承受列的名字或编号为参数、前往排序的了局集，Listing10显现了年夜多半程序员当做第一选择的办法。


【Listing10：列名字作为参数，利用UDF】


CREATEFUNCTIONufn_GetSortedShippers

(

@ColNameASsysname

)

RETURNSTABLE

AS


RETURN

SELECT*

FROMShippers

ORDERBY

CASE@ColName

WHENShipperIDTHENCASESIGN(ShipperID)

WHEN-1THEN-

WHEN0THEN+

WHEN1THEN+

ELSENULL

END+

RIGHT(REPLICATE(0,10)+

CAST(ABS(ShipperID)AS

varchar(10)),10)

WHENCompanyNameTHENCompanyName

WHENPhoneTHENPhone

ELSENULL

END



　　可是，SQLServer不承受这个函数，它将前往以下毛病信息：


Server:Msg1033,Level15,State1,Procedureufn_GetSortedShippers,

Line24

TheORDERBYclauseisinvalidinviews,inlinefunctions,and

subqueries,unlessTOPisalsospecified.



　　注重毛病信息中的“unless”。SQLServer2000不同意在视图、嵌进式UDF、子查询中呈现ORDERBY子句，由于它们都应当前往一个表，表不克不及指定行的序次。但是，假如利用了TOP关头词，ORDERBY子句将匡助断定查询所前往的行。因而，假如指定了TOP，你还能够同时指定ORDERBY。因为在带有TOP的UDF中同意利用ORDERBY子句，你可使用一个技能：把“SELECT*”交换成“SELECTTOP100PERCENT*”。如许，你就可以够乐成地机关出一个承受列名字或编号为参数、前往排序了局的函数。


　　新机关的函数能够依照以下体例挪用：


SELECT*FROMufn_GetSortedShippers(ShipperID)



　　如今，你已懂得了几种用参数断定查询输入中纪录序次的办法。在编写那些同意用户指定查询了局排序尺度的列的使用程序时，你可使用本文先容的各类手艺，用列名字或编号作为参数，机关出利用CASE表达式和静态实行才能的各类计划。
恢复到之前的某个状态，是需要数据的。这数据可以是a)回滚步骤或者b)操作之前的数据状态原文。

仓酷云

分区表效率问题肯定是大家关心的问题。在我的试验中，如果按照分区字段进行的查询（过滤）效率会高于未分区表的相同语句。但是如果按照非分区字段进行查询，效率会低于未分区表的相同语句。

同样会为索引视图等应用带来麻烦。看看行级和事务级的快照数据放在tempdb中，就能感觉到目前架构的尴尬。

只能告诉你，学好数据库语言和原理，多见识几种数据库软件，比一棵树上吊死要好。

需要注意的一点，也是我使用过程中发现的一个问题。在建立function->schema->table后，如果在现有的分区表上建立没有显式声明的聚集索引时，分区表会自动变为非分区表。这一点很让我纳闷。

入门没那么困难，精通没那么容易

在select语句中可以使用groupby子句将行划分成较小的组，然后，使用聚组函数返回每一个组的汇总信息，另外，可以使用having子句限制返回的结果集。

一个百万级别的基本信息表A，一个百万级别的详细记录表B，A中有个身份证id，B中也有身份id；先要找出A中在B的详细记录。

XML字段类型更好的解决了XML数据的操作。XQuery确实不错，但是个人对其没好感。（CSDN的开发者应该是相当的熟了！）