Sql server面试题1.docx
- 文档编号:11634087
- 上传时间:2023-03-29
- 格式:DOCX
- 页数:27
- 大小:39.18KB
Sql server面试题1.docx
《Sql server面试题1.docx》由会员分享,可在线阅读,更多相关《Sql server面试题1.docx(27页珍藏版)》请在冰豆网上搜索。
Sqlserver面试题1
【SQLServer面试题(实际应用,分页管理)】
管理结果集的分页
问:
我怎样才能使用SQLServer对结果集的分页进行管理?
答:
您经常需要使用一次一页的形式来显示一个结果集,并保证用户可以轻松查看各个结果集页面,特别是您在为Web站点开发程序的时候。
虽然您可以使用ADORecordset对象对结果集进行分页,但是这种解决办法不具有伸缩性。
为了解决伸缩性问题,您需要在结果集中包括一个具有唯一ID的列,例如在表中包括一个主键。
以下代码介绍了一个简单的例子,它使用两个存储过程在各个页面间进行导航:
CreatePROCEDUREspGetNextPage
@idvarchar(11)='0',
@rowsint=0
AS
SETNOCOUNTON
SETROWCOUNT@rows
Select
a.au_id,
a.au_fname+''+au_lnameASname
FROM
authorsa
Where
a.au_id>@id
orDERBY
a.au_id
SETROWCOUNT0
SETNOCOUNTOFF
GO
CreatePROCEDUREspGetPrevPage
@idvarchar(11)='0',
@rowsint=0
AS
SETNOCOUNTON
SETROWCOUNT@ROWS
Select
a.au_id,
a.au_fname+''+au_lnameASname
INTO
#temp
FROM
authorsa
Where
a.au_id<@id
orDERBY
a.au_iddesc
SETROWCOUNT0
Select
*
FROM
#temp
orDERBY
au_id
SETROWCOUNT0
SETNOCOUNTOFF
本示例所使用的样本数据来自pubs数据库,您可以对authors表进行分页。
如果想返回第一页的前两行数据,您可以使用带有以下参数的spGetNextPage存储过程:
EXECspGetNextPage@id='0',@rows=2
spGetNextPage过程将返回authors表的前两个作者:
172-32-1176JohnsonWhiteMarjorieGreen
如需返回接下来的两个作者,您可以将后一行的ID传递给spGetNextPage:
EXECspGetNextPage@id='213-46-8915',@rows=2
结果页显示:
238-95-7766CherylCarsonMichaelO'Leary
如果想移动到先前页面,您可以使用第一行的ID调用spGetPrevPage:
EXECspGetPrevPage@id='238-95-7766',@rows=2
结果将显示了您在前面看到的第一个页面。
使用这种方法的一个缺点是具有唯一ID的列决定了结果集的顺序。
在本文的情况下,au_id字段必须在作者姓名字段的前面。
【SQLServer面试题(实际应用)】
排序顺序会影响系统性能吗?
问:
一些资料介绍说,在SQLServer6.5中,排序方式会对系统性能造成影响,但是我并没有发现有针对SQLServer2000或SQLServer7.0的类似说法。
我想知道二分法检索对SQLServer2000程序到底有何意义。
SQLServer2000所支持的各种排序方式之间在执行性能上存在哪些差异呢?
答:
请记住:
各类排序方式间的性能差异受制于SQLServer在数据排序和数据比较上所花费的CPU周期数量。
排序速度快并不代表性能就一定会得到改善。
事实上,选择较为“快速”的排序方法(例如二分法)可能会导致应用程序在其它方面的性能降低。
例如,假设您有一个查询,该查询通过last_name字段进行搜索。
二分法排序的规则规定:
Smith并不等于smith。
您的应用程序开发人员可以通过要求所有数菥笮葱问交蛐⌒葱问嚼词迪终飧鲆滴衤呒5牵导实慕饩霭旆ㄊ嵌运阉鞅碇械乃惺菔褂肬PPER()或者LOWER()函数。
但不幸的是,如果类似UPPER()这样的函数对一个经过索引的列进行了操作,SQLServer将不再能够使用索引搜索数据。
在这种情况下,通过使用二分法所获得的所有性能提升都将被花费在全表扫描上的巨大性能降低所抵消。
所以,您无需考虑不同排序方法间可能存在的微小性能差异,您也无需费神考虑何种排序方法最能满足程序开发人员和最终用户的需要。
—SQLServerMVPs
--------------------------------------------------------------------------
日志文件的增长和DBCCDBREINDEX
问:
我有一个30GB的数据库,我使用完全恢复模式。
无论什么时候,只要我使用数据库一致性检查程序(DBCC)语句DBCCDBREINDEX对特定的大型数据表进行重新索引,我都要将恢复模式改为Bulk_Logged,在重新索引过程完成后再改回完全模式。
我希望这样做能够避免事务日志文件急剧增长,但是随后的日志文件备份工作量却非常大--有大约15GB。
从逻辑上说,数据库中的数据在重新索引后同原先并没有什么不同,只是索引重新进行了组织,那么为什么日志文件还是那样大呢?
我怎样才能避免日志文件出现这样的急剧增长呢?
答:
是的,在重新索引前后的数据是完全一样的,但是索引却全面进行了更新。
当您执行DBCCDBREINDEX命令的时候,SQLServer的日志仅仅记录了扩展盘区的分配情况(8页面单位),而不是记录了每一行或者每一页所发生的变化。
这种类型的日志记录方式避免了物理文件由于系统故障而遭到破坏,并且将更详细的日志记录对系统吞吐量产生的影响降低到最小。
当您备份日志文件的时候,SQLServer必须对分配在扩展盘区中的页面进行备份,以便保持数据库备份和日志备份的一致性。
如果SQLServer不备份这些页面,您将不能够切换回完全恢复模式,除非你进行一次完整的数据库备份。
您必须能够从最近一次的完全备份、任何差异备份以及任何更新的事务日志备份中对数据库进行恢复。
【SQLServer面试题】
向被复制的表添加列
问:
我们常常向重复的表添加列。
怎样才能在不重新初始化整个发布的情况下添加一个列?
答:
在SQLServer2000中,您可以使用sp_repladdcolumn存储过程向重复的表添加列,而不需要重新初始化整个发布,因为存储过程会自动在订阅服务器上添加该列。
例如,如果pubs数据库中的authors表已发布,您可以通过执行以下存储过程将newcol整数列添加到该表中:
sp_repladdcolumn@source_object='authors'
@column='newcol'
@typetext='INT'
@publication_to_add='
请注意,您只能使用sp_repladdcolumn存储过程向重复的表添加新列,而不能用它来管理表中现有的列。
如需从已发布表中删除已有的列,您可以使用sp_repldropcolumn存储过程。
—MicrosoftSQLServer开发团队
--------------------------------------------------------------------------
将代码页从SQLServer7.0改变到SQLServer2000
问:
我注意到,SQLServer7.0默认安装的代码页设置是SQL_Latin1_General_CP1_CI_AS,但是SQLServer2000的默认代码页是Latin1_General_CI_AS。
在我需要将SQLServer7.0数据库恢复到SQLServer2000之中时,默认代码页发生的变化会造成很大的差异。
我应该如何适应这种改变?
答:
SQLServer正在逐渐取消对原有的自定义代码页的支持,而转变为同Microsoft?
Windows代码页相匹配,Windows代码页是您在设置Windows区域ID时所选择设定的。
同代码页相关的更多信息,请参阅SQLServer2000在线图书中的“字符数据的存储方式”一文。
当您将SQLServer7.0升级到SQLServer2000之后,在默认情况下,您仍然可以保留现有的排序方式和代码页设置。
但是,当您进行了一次干净的SQLServer2000安装时,您将使用新的排序方式和代码页。
如果您将某个数据库附加到一个SQLServer2000实例中,SQLServer将保留该数据库在创建时所使用的排序规则。
换句话说,如果您将一个SQLServer7.0数据库附加到SQLServer2000之中,你就可以保留SQLServer7.0代码页。
由于SQLServer现在使用了新的默认代码页,我们建议:
如果您想通过干净安装和数据库的附加(attch)操作进行升级,您可以在安装时选择同您的SQLServer7.0排序规则相匹配的排序规则。
您可以通过执行一次升级来确定该排序规则,例如,对pubs数据库进行一次升级,使用SERVERPROPERTY(Collation)函数运行一个查询,然后看看所得到的是何种排序规则。
Microsoft之所以对默认代码页进行修改,主要是为了减少SQLServer和Windows代码页的不兼容而引起的种种问题。
【SQL面试题】
假设只有一个table,名为pages,有四个字段,id,url,title,body。
里面储存了很多网页,网页的url地址,title和网页的内容,然后你用一个sql查询将url匹配的排在最前,title匹配的其次,body匹配最后,没有任何字段匹配的,不返回。
就是上面这道面试题,让我想了一个下午,在网上找资料,最后用下面方法实现
SELECT*
FROMpagewhereurllike'%baidu%'ortitlelike'%baidu%'orlike''
ORDERBYCHARINDEX('baidu',url)DESC,CHARINDEX('baidu',title)DESC,
CHARINDEX('baidu',body)DESC
但我感觉这种方法并不是最简单的,后来把这个方法发给面试的人,他给我了一种更简单方法,只要用基本的Sql语句就可以实现。
代码如下
selecta.[id],a.markfrom
(
select[page].[id],100asmarkfrom[page]where[page].[url]like'%baidu%'
union
select[page].[id],50asmarkfrom[page]where[page].[title]like'%baidu%'
union
select[page].[id],10asmarkfrom[page]where[page].[body]like'%baidu%'
)asaorderbymarkdesc
用union实现联合查询,在每个查询语句中定义一个临时变量mark并给mark赋值,在最后的输出时采用mark来排序,这样实现真的好简单。
其实这都考验我们对Sql的编成思想。
【SqlServer面试题(实际应用四)】
使用视图控制用户对数据访问
问:
我的MicrosoftAccess2000应用程序由后端的SQLServer2000数据库写入数据。
为防止Access的用户看到SQLServer2000表中的全部数据,我想使用一种只允许用户浏览授权数据行的视图。
可以创建一种限制用户访问SQLServer数据的视图吗?
答:
可以。
如果每位用户以唯一的用户ID登录到Access,您就可以创建一种限制用户访问SQLServer数据的视图。
以下的示例语句就可以创建这样一种视图:
CreateVIEWv_dataAS
Select
FROMdbo.mytableASa
INNERJOINdbo.authtableASb
ON(a.Pkey=b.DataKey
ANDb.userid=suser_sname())
该视图按userid限制用户的访问权。
它要求您保存一份与数据表(mytable)中特定主键相匹配的用户名的表(authtable)。
如果您的情况相对比较简单——您无需管理多个用户的行访问权,则您可以将userid列插入到数据表中,如下列代码所示:
CreateVIEWv_dataAS
Select
FROMdbo.mytableASa
Wherea.userid=suser_sname()
—MicrosoftSQLServer开发团队
--------------------------------------------------------------------------
检查端口号
问:
我创建了一个使用TCP/IP网络图书馆的服务器别名。
客户端服务器别名被配置为动态确定所使用的端口。
怎样才能找出客户所使用的端口以便我设置自己的防火墙?
答:
客户端用于联络SQLServer实例的端口与该实例用于监听客户端请求实例的端口是一样的。
您有几种方法来确定正被使用的端口号。
请记住,当服务器首次被安装时,SQLServer动态地选择端口。
以后每次服务器暂停工作和重新启动时使用的端口号都是一样的。
动态端口分配是一次性的事件。
要查看正被SQLServer实例使用的端口号,可以选择以下的任何一种方法:
打开Server网络实用工具,点击启用的协议列表中TCP/IP条目的属性。
查看特定实例的错误日志。
您会在错误日志中看到与下面相似的条目:
SQLServerlisteningon127.0.0.1:
1362.
冒号后的四位数是SQLServer正用于监听IP地址的端口,冒号左边是详细的信息。
查看注册表。
根据您安装和升级SQLServer的方式,具体的注册表子键会有所不同,但您会看到和下面相似的键:
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\
InstanceName\MSSQLServer\SuperSocketNetLib\Tcp\
InstanceName是您正在使用的SQLServer实例的名称(对于默认实例,InstanceName为MSSQLServer)。
该键有一个名为TcpPort的条目,其中包含了正在被使用的端口号。
您需要这个端口号来设置客户端和服务器之间的防火墙。
客户端向SQLServer的UDP1434端口发送特殊的请求,以确定使用哪一个端口。
SQLServer返回可用的服务器实例的列表,以及每个实例正在使用的端口。
如果屏蔽了UDP1434端口,则通过防火墙与SQLServer实例的连接将无法实现。
--------------------------------------------------------------------------
如何识别真实和自动创建的索引?
问:
我发现sysindexes索引表中的很多条目并不是我自己创建的。
听说它们并不是真正的索引,而是SQLServer查询优化器自动创建的统计。
怎样才能识别哪些是真正的索引,哪些是SQLServer自动创建的统计呢?
答:
按照默认设置,如果表中的某列没有索引,则SQLServer会自动为该列创建统计。
然后,查询优化器评估该列中数据分布范围的统计信息,以选择一个更为有效的查询处理方案。
分辨自动创建的统计很简单,在SQLServer7.0和SQLServer2000中,自动创建的统计的前缀为_WA_Sys。
您还可以使用INDEXPROPERTY()函数的IsAutoStatistics属性来区分一个索引是真正的还是自动创建的统计,让SQLServer优化器选择需要创建的统计。
您还可以为您管理的数据库启用“自动创建统计表”选项。
很多人忽略了下面的明显的结论。
自动创建统计的存在意味着某个真正的索引可能会从中受益。
请考虑下列代码的输出:
USEtempdb
GO
IFOBJECTPROPERTY(OBJECT_ID('dbo.orders'),'IsUserTable')=1
DropTABLEdbo.orders
GO
Select*INTOtempdb..ordersFROMnorthwind..orders
GO
Select*FROMtempdb..ordersWhereorderid=10248
GO
Select*FROMtempdb..sysindexesWhereid=object_id('orders')
ANDnameLIKE
'_wa_sys%'
GO
该代码在tempdb中复制Northwindorders表,选择一行,然后检查SQLServer是否添加了一个统计。
很显然,该表没有OrderId列的索引,所以SQLServer自动创建了名为_WA_Sys_OrderID_58D1301D的统计。
OrderId列统计表的存在表明Northwindorders表将得益于附加的索引。
以下查询显示了为数据库中每个用户表自动创建的统计的数量,该数据库至少有一个自动创建的统计。
Select
object_name(id)TableName
count(*)NumberOfAutoStats
FROM
sysindexes
Where
OBJECTPROPERTY(id,N'IsUserTable')=1
ANDINDEXPROPERTY(id,name,'IsAutoStatistics')=1
GROUPBY
object_name(id)
orDERBY
count(*)DESC
并不是所有的统计都可被真正的索引所替代。
在某些情况下,SQLServer会为一个表自动创建超过50个统计。
很明显,这些表的索引策略很差劲。
对表及自动创建的与之相关联的统计的快速记数可以帮助您确定哪些表需要索引。
【SqlServer面试题(实际应用三)】
Ghost记录清理和602错误
问:
最近,在使用SQL事件探查器监视我的SQLServer数据库时,我定期收到“Error:
602,Severity:
21,State:
13”信息。
在SQLServer企业管理器的进程信息窗口中,我找到了一个名为“GhostRecordCleanup”(幻影记录清除)的后台进程,并且该命令由用户系统所引发。
我使用DBCCCHECKDB检查我的数据库,但是没有发现任何异常。
请问什么是GhostRecordCleanup进程?
什么是602错误消息?
答:
在从数据库中删除行、页或扩展盘区时,SQLServer会将这些对象标记为“幻影”(表示删除操作有待执行),并在稍后使用后台任务清除这些对象,该进程就是GhostRecordCleanup。
GhostRecordCleanup改善了Delete命令的性能,因为SQLServer无需立即执行物理清除操作。
如需了解出现602错误的原因,您可以在SQLServer错误日志中查找提供ObjectID的一行信息。
然后,您可以使用该ObjectID跟踪到导致该错误的数据库或对象。
如果该对象仍然存在(例如,它不是诸如临时表的临时对象),您可以对其运行DBCCCHECKTABLE,并通过查询sysindexes验证该对象并未受损。
如果对象未受损,则SQLServer错误地发出了602错误消息。
多数情况下,SQLServer发布了错误的消息。
如需了解602错误所代表的意思,请访问Microsoft产品支持服务网站并搜索“错误消息”和602。
搜索将返回一系列说明如何解决602错误及其含义的文章。
—SQLServer开发团队
--------------------------------------------------------------------------
使用扩展属性创建数据字典工具
问:
我正在寻找可以帮助我维护一个SQLServer数据字典的共享软件或低成本工具。
我使用过具备该功能的高级数据建模工具。
但是,我现在的公司有许多SQLServer数据库,但是没有数据字典,并且也没有购买价格不菲的工具的打算。
您知道有哪些给“穷人”用的数据字典工具吗?
答:
尽管可能有免费或低成本的共享软件工具,但我对它们不是很熟悉。
我建议您尝试使用SQLServer2000的扩展属性。
在SQLServer2000中,Microsoft添加了扩展属性,用于帮助用户在多个数据库对象上定义和操作用户定义的属性。
您可以使用这些用户定义的属性向您的数据库添加元数据,并使用该数据库创建一个直接集成在SQLServer中的“土制”数据字典。
您可以使用系统存储过程sp_addextendedproperty、sp_updateextendedproperty及sp_dropextendedproperty来管理这些属性。
此外,您可以使用系统函数fn_listextendedproperty()检索现有的属性值。
Microsoft使用扩展属性来写入和管理描述值,它与SQLServer企业管理器表设计视图中的列相关联。
请注意,使用这些存储过程和fn_listextendedproperty()函数创建和管理扩展属性并不是一件轻而易举的事。
SQLServer在线书籍中名为“属性管理”的主题提供了有关扩展属性的基本信息。
幸运的是,SQLServer杂志上有几篇非常不错的相互文章。
有关使用扩展属性的详细信息,请参阅MichelleA.Poolet的“一个商业元数据知识库”,WilliamVaughn的“管理扩展属性”,以及“技巧:
管理基于SQLServer2000扩展属性的数据字典”。
这三篇文章都解释了如何使用扩展属性,并且每篇文章都提供了一个解释器,供您编写自己的“穷人”数据字典之用。
—SQL
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- Sql server面试题1 server 试题