SQL语句精化实例.docx
《SQL语句精化实例.docx》由会员分享，可在线阅读，更多相关《SQL语句精化实例.docx（21页珍藏版）》请在冰豆网上搜索。
SQL语句精化实例
4.设有一个职员表为Customers,其上有客户姓名（Name），客户ID（ID）等列，表执行如下语句：
CREATECLUSTEREDINDEXidxONCustomers（Name）
得到以下错误
Cannotcreatemorethanoneclusteredindex
原因是什么，为什么为出错?
答:
说明表上已经有了聚集索引，只能在同一张表上创建一个聚集索引。
因为聚集索引会决定表的物理排列，由于只可能有一种排列方法，所以只能创建一个聚集索引。
5.设有一个职员表为Customers,其上有客户姓（LastName），名字（FirstName）客户ID（ID）等列，先在LastName和FirstName上创建一个称为idxNames的非聚集的复合索引，然后在客户ID上创建唯一的聚集索引uidxID，说明在创建聚集索引时非聚集索引会有什么变化
答:
创建聚集索引时非聚集索引将被重建，因为创建聚集索引将改变表中行的物理位置，而且在有聚集索引的表上的非聚集索引的B树的叶级存放的所有的鍵值和其对应的聚集索引的关键字，而之前的非聚集索引的B树的叶级存放的是所有的键值和其相对应的行ID。
因此要进行非聚集索引的重建。
6. 如果顾客表没有索引，SQLServer如何为客户EvaCorets查找行？
答:
SQLServer必须执行表扫描，读取表中的每一行来查找符合要求的行。
7. 一个表可以创建多少个聚集索引？
答:
一个。
聚集索引定义数据页的物理存储并且表中的数据只能存储在一个位置。
8. 在表已经有聚集索引时，非聚集索引如何识别父行？
在表没有聚集索引时，非聚集索引又如何识别数据行？
答:
在聚集索引存在时，非聚集索引为每一个被索引的行存储聚集索引。
在没有聚集索引时，非聚集索引存储文件ID、页码和数据行的RID。
9. 不包括索引的字段的扩展会导致页拆分，页拆分将把行移动到新的页中。
这种移动会对表中的非聚集索引产生什么样的影响？
答:
这对非聚集索引没有影响。
如果存储有聚集索引，聚集的值不会改变。
非聚集索引将继续指向行，因为聚集索引关键字没有改变。
如果没有聚集索引，在原记录的位置会留下一个转发指针指向新的记录。
在任一情况下，非聚集索引不需要改变。
10. 考虑在表中的companyname、lastname和firstname列上创建一个组合聚集索引。
在创建索引时，什么是应该考虑的重点，为什么要考虑？
还有更好的解决方法么？
答:
尽可能保持聚集索引键尽量的小。
大的聚集索引键会在所有的非聚集键上产生较大的影响。
聚集索引越大，其效率越低。
键值增加时，该值需要占有页上更多的空间，这样的页就只能容纳少量的键值，导致聚集索引树（B树）变得更大。
聚集索引越大（它就会有更多的非叶级），那么需要遍历索引树的I/O周期越长。
同样的，唯一的组合键最好作为非聚集索引或多索引来定义。
可以考虑更好的解决方法是在customerID列（如果存在）或lastname列上创建索引。
如果customerID列并不存在，应该考虑使用标识属性或添加包括有通过在行中抽取数据的不同部分而衍生出来的键值的新列。
创建并维护索引
1. 假设你负责一家公司的数据库管理。
用户向你抱怨查询据库Sales中的products表（表上建有idxProID索引）的速度太慢，你经过测试，发现可能是由于统计信息过时导致的。
为了使将来不再发生这种问题，你要用哪个语句保证整个数据库的统计自动更新。
ADBCCSHOW_STATISTICS（products,idxProID）
B UPDATESTATISTICS‘products’
C sp_autostats‘products‘，ON
DALTER DATABASE SalesSETAUTO_CREATE_STATISTICSON
答:
D
2. 已知数据库Sales中的products表上建有idxProID索引，你想知道这个索引是个聚集索引还是一个非聚集索引，可用以下哪个语句？
（不定项选择）
Asp_helpindexproductsidxProID
Bsp_helpidxProID
Csp_helpproducts
DUSESales
GO
SELECTindid
FROMdbo.sysindexes
WHEREname=‘idxProID’
答:
C
3. 在你管理的数据库中有一张名为products的表，在监测products表的磁盘I/O的时候，你怀疑表的索引存在很多的碎片。
已知products表在主键上有一个叫作idxProid的索引，另外还有nid1、nid2两个非聚集索引。
你想使用耗费最小资源的办法重建products表上的索引，
应使用以下哪种方法。
A
DBCCDBREINDEX（products）
B
ALTERTABLEproductsDROPCONSTRAINTidxProid
ALTERTABLEADDCONSTRAINTprimarykeyidxProid（...）
C
CREATEINDEXidxProidONproducts（...）WITHDROP_EXISTING
CREATEINDEXnid1ONproducts（...）WITHDROP_EXISTING
CREATEINDEXnid2ONproducts（...）WITHDROP_EXISTING
D
DROPINDEXproducts.idxProid
DROPINDEXproducts.nid1
DROPINDEXproducts.nid2
CREATEINDEXidxProidONproducts（...）
CREATEINDEXnid1ONproducts（...）
CREATEINDEXnid2ONproducts（...）
答:
A
4. 下面的一条SQL语句是用来创建一个索引的，试解释其作用。
CREATEUNIQUECLUSTEREDINDEXindex1
ONtable1（column1,column4DESC）
WITHPAD_INDEX,FILLFACTO=60,DROPEXISTING
答:
该语句将在名为table1表上的column1和column4上建立一个组合的唯一聚集索引。
另外column4上指定DESC表示在该行上的排序次序为降序。
另外在WITH中指定了FILLFACTO=60这将使索引的页级只有60%被填满，还指定了PAD_INDEX这将使索引的非页级也只有60%被填满。
最后WITH中还指定了DROP_EXISTING，这里如果原表中存在名为index1的索引，则它的特性将会上述语句所更改，使用这个选项的优点是我们不用删除一已存在的索引再重建它。
5. table1（存在于db1数据库上）上存在有一个index1索引，执行下列SQL语句
SELECTid,indid,reserved,used,origfillfactor,name
FROMdb1.dbo.sysindexes
WHEREname=‘index1’
返回
Id indid reserved used origfillfactor name
209452452 1 20 20 60 index1
（1row（s）affected）
试说明SQL语句和返回的结果.
答:
SQL语句在db1的系统索引表sysindexes查找名字为’index1’行的相关信息，也就是从系统索引表中找出索引index1的相关信息。
返回值中,id表示的是index1的ID值，indid为1表示该行是一个聚集索引的信息，reserved表示系统为索引分配的页面数，used表示该索引用的页面总数，origfillfactor表示索引创建时指定的FILLFACTO的值，默认为0，但在index1的创建中已指定了60，最后一列name表示索引名。
6.你是负责管理大型客户数据库的数据库管理员。
最近，当提交客户定单时，定单处理部门发现系统反映时间变慢。
你的经验告诉你在Orders和OrderDetails表中的索引是正确的。
是什么原因导致执行变慢了呢？
答:
索引统计可能没有被自动地维护，因此，随着数据的修改它将越来越过时。
FILLFACTOR选项需要被重新应用到为新的定单（行）分配的表和索引空间上，而新的定单是要插入到Orders和OrderDetails表中的。
7.SQLServer自动创建和更新统计信息有什么好处？
答:
让查询优化器自动创建和更新统计表来可以减少管理负担并增加查询性能。
8.你负责维护销售部门接受客户定单的数据库。
销售数据库执行性能差。
你的经理让你在两天内改善性能。
解决这个问题的最恰当工具是什么？
答:
用索引优化向导。
第一天，创建一个工作负荷文件用于记录一整天的用户活动。
在第二天，对工作负荷文件运行索引优化向导，查看索引分析并应用索引优化向导建议的索引。
实现视图
1. 你负责维护一个电信公司的数据库，不久前你在数据库上创建了一个视图vwOrders,并且在创建视图时使用了WITHSCHEMABINDING选项。
现在你要修改这个vwOrders视图，增加一个WITHCHECKOPTION的选项。
要求不能改动原有的选项，你要怎么做呢？
（双项选择）
A 删除vwOrders，并用WITHSCHEMABINDING和WITHCHECKOPTION选
项重建视图
B 删除vwOrders，并用WITHCHECKOPTION选项重建视图
C 改变视图，并用WITHSCHEMABINDING和WITHCHECKOPTION选项
D 改变视图，并用WITHCHECKOPTION选项
答:
AC
2. 你是公司的数据库管理员，有一天你要删除数据库中的一个视图vwOrders（创建时没有使用WITHENCRIPTION）时，系统提示存在其它视图依赖此视图，从而删除失败，你要怎么知道是哪个视图依赖这个视图呢？
（不定项选择）
A 通过查询系统表syscomments来得到视图的定义，从而得到依赖信息
B 使用sp_helptext‘vwOrders’来得到视图的定义，从而得到依赖信息
C 使用sp_depends‘vwOrders’，来得到依赖信息
D查询INFORMATION_SCHEMA.VIEW_TABLE_USAGE视图来得到依赖信息
答:
C
3. 用下述语句创建视图
CREATEVIEWProductsView
AS
SELECTProductID,Price,Company
FROMSupplier
INNERJOINProducts
ONSuppliers.ID=Products.SupplierID
ORDERBYProductID
出现错误，为什么，如何修改。
答:
因为在视图的创建中如果包含了ORDER BY子句，则要使用TOP语句才能生成视图。
设我们要选出全部符合条件的记录，语句可改为如下：
CREATEVIEWProductsView
AS
SELECTTOP100PERCENTProductID,Price,Company
FROMSupplier
INNERJOINProducts
ONSuppliers.ID=Products.SupplierID
ORDERBYProductID
4. 在SQLServer上的Northwind数据库上创建这样一个叫作vwCustomerOrders的视图，视图中使用SELECT语句在以Orders表中的订单ID、Customers数据表中的公司名称（CompanyName）的和联系名称（ContactName）,通过客户ID联接起来，并授于sales帐户在视图上的查询权限，写出创建视图，和授予权限的SQL语句，并说明sales帐户要具有在Orders和Customers表中的查询权限吗？
答:
语句如下：
USENorthwind
GO
CREATEVIEWvwCustomerOders
AS
SELECTo.OrderID,c.CompanyName,c.ContactName
FROMOrdersoJOINCustomersc
ONo.CustomerID=c.CustomerID
GO
GRANTSELECTONvwCustomerOdersTOsales
另外，sale帐户不用具有Orders和Customers表中的查询权限，也可以对视图进行查询。
5. 视图的优点是什么？
答:
通过使用视图用户可以把注意力放在需要的数据上，也可以使用户对数据的操作变得简单。
对用户来说，数据库和查询的复杂性被隐藏了，这样可以让用户看到更友好的名字。
通过只允许用户访问视图中数据的这种方法，视图提供了一种安全机制。
在视图上创建索引和通过视图分割数据可以优化性能。
6. 假设已经实现了联接Customer、Orders和OrderDetails表的查询，它列出了顾客订单的详细情况，例如物品的数量和要求的交货日期。
在顾客改变现有的订单时，雇员需要更新Orders表和OrderDetails表。
在不具有访问基表权限的情况下如何完成该任务？
答:
在查询上创建一个名为OrderDetailsView的视图。
在视图上授权RequiredDate和Quantity列的更新权限。
这样确保雇员只在Orders和OrderDetails表更新这些列。
7．在视图定义中使用WITHCHECKOPTION有什么益处？
答:
该选项强制视图上所有数据修改语句都要符合定义视图的SELECT中定义的准则。
8．在使用视图时应该考虑什么？
答:
视图中引用的对象在创建视图时进行验证。
为了使分配给视图的权限得到维护，可以修改视图。
删除或修改基础表时会影响视图，如果视图的所有者不是dbo用户，用户的名字必须作为视图名字的一部分来指定。
同一所有者必须拥有视图相关的所有对象以避免破坏所有权链。
复杂性的隐藏会产生难以确定原因的性能问题。
实现存储过程
1. 创建一个名为FindCustomer存储过程，可以用它来找出SQL SERVER中的northwind数据库的Customer表中，CustomerID为指定值（输入参数）的记录的ContactName字段的名称，然后调用这个存储过程，找出CustomerID为’thecr’的ContactName字段值，写出创建存储过程的SQL语句和调用的命令，以下选项哪一个是正确的。
A
创建语句：
USEnorthwind
GO
CREATEPROCEDUREdbo.FindCustomer
$CustomerIDchar（5）
LIKE
SELECTcontactName
FromCustomersWHERECustomerID=$CustomerID
调用语句
EXECnorthwind.dbo.FindCustomer
$CustomerID=‘thecr’
B
创建语句：
USEnorthwind
GO
CREATEPROCEDUREdbo.FindCustomer
@CustomerIDchar（5）
AS
SELECTcontactName
FromCustomersWHERE@CustomerID=CustomerID
调用语句
EXECnorthwind.dbo.FindCustomer
@CustomerID=‘thecr’
C
创建语句：
USEnorthwind
GO
CREATEPROCEDUREdbo.FindCustomer
@CustomerIDchar（5）
AS
SELECTcontactName
FromCustomersWHERECustomerID=@CustomerID
调用语句
EXECnorthwind.dbo.FindCustomer
CustomerID=‘thecr’
D
创建语句：
USEnorthwind
GO
CREATEPROCEDUREdbo.FindCustomer
@CustomerIDchar（5）
LIKE
SELECTcontactName
FromCustomersWHERECustomerID=@CustomerID
调用语句
EXECnorthwind.dbo.FindCustomer
@CustomerID=‘thecr’
答:
B
2. 你在northwind数据库中创建了一个名为overdueOrders的储存过程，而且没被加密。
那么以下哪些方法可以查看存储过程的内容。
（不定项选择）
AEXECsp_helptext'overdueOrders'
BEXECsp_helpoverdueOrders
CEXECsp_stored_procedures'overdueOrders'
DEXECsp_depends'overdueOrders'
E 查询syscomments系统表
F 查询sysobjects系统表
答:
AE
3. 创建一个名为FindCustomer1的存储过程，可以用它来找出SQLSERVER中的northwind数据库的Customer表中，CustomerID为指定值（输入参数）的记录的ContactName字段的名称，另外指定一个输出参数LineNum做为输出参数，还有必须在存储过程中判断CustomerID不能为空串，是的话要打印出出错信息，并返回错误值-1,如果查询成功在输出变量LineNum中保留选出的行数，然后返回值0。
写出相应的SQL语句.
答:
USENorthWind
GO
CREATEPROCFindCustomer3
@LineNumintOUTPUT,
@CustomerIDchar（5）
AS
IFLEN（@CustomerID）=0
BEGIN
PRINT'YoumustsupplyavalidCustomerID'
RETURN-1
END
SELECTcontactName
FromCustomersWHERECustomerID=@CustomerID
SET@LineNum=@@ROWCOUNT
RETURN0
4. 首先自定义一个错误号为50512的用户自定义错误，错误的严重级别为10,错误的文本消息为’Can’tfindthecustomerID.’，另外消息中还要加上表名和输入的CustomerID,并且当发生消息时将消息写入Microsoft®WindowsNT®应用程序日志中。
然后创建一个名为ExistCustomerID的存储过程，以用它来找出SQLSERVER中的northwind数据库的Customer表中，指定的CustomerID是否存在，如果存在返回0，如果不存在返回错误号50512，并将消息写入Microsoft®WindowsNT®应用程序日志中。
写出定义错误消息和创建存储过程的语句。
答:
定义自定义错误消息
EXECsp_addmessage
@msgnum=50512,
@severity=10,
@msgtext='Can’tfindthecustomerID:
%sattable%s.',
@with_log='true'
创建存储过程的语句如下
USENorthWind
GO
CREATEPROCExistCustomerID
@CustomerIDchar（5）
AS
SELECTCustomerID
FromCustomersWHERECustomerID=@CustomerID
IF@@ROWCOUNT=0
BEGIN
RAISEERROR（50512,10,1,@CustomerID,@DBNAME）
RETURN
END
RETURN0
5. 已经创建了一个从数据库中删除客户的存储过程。
在删除事务完成时，希望有一个自定义的错误信息写入Windows2000应用程序日志。
如何执行该任务？
答:
通过在sp_addmessage存储过程中指定@with_log参数创建一个自定义的错误信息。
删除事务提交后，在存储过程中调用RAISERROR语句来生成自定义的错误信息。
6. 希望工资管理部门的用户可以在payroll数据库中插入、更新和删除数据。
然而，不希望他们有访问基表的权限。
那么除了创建一个视图以外，还能如何实现该目标？
答:
创建实现单一任务的存储过程。
在存储过程中给工资管理部门的用户授予EXECUTE的权限。
7. 在数据库中必须修改一个存储过程，而有几个用户已被授予了执行该存储过程的权限。
执行哪个语句来完成修改而又不影响现有的权限？
答:
ALTERPROC。
如果执行DROPPROC和CREATEPROC语句来实现想要的修改，必须再次授予用户EXECUTE权限。
实现用户定义函数
1. 你是某大型商场的数据库开发人员，要实现对商品的销售情况的复杂统计。
这个统计每次根据用户提供的一个商品代号，访问一些表中的数据进行统计，最后返回一个值。
你要在SELECT、UPDATE和DELETE语句中使用这个计算的结果。
哪种实现方法最有效？
A.
升级会员 