sql70 431实验考题.docx
- 文档编号:11579234
- 上传时间:2023-03-19
- 格式:DOCX
- 页数:20
- 大小:1.41MB
sql70 431实验考题.docx
《sql70 431实验考题.docx》由会员分享,可在线阅读,更多相关《sql70 431实验考题.docx(20页珍藏版)》请在冰豆网上搜索。
sql70431实验考题
SQLSERVER2005实验
准备实验环境:
一、执行以下代码,创建出模拟实验环境:
CREATEDATABASE[gsdtest]ONPRIMARY
(NAME=N'gsdtest',FILENAME=N'C:
\ProgramFiles\MicrosoftSQLServer\MSSQL.1\MSSQL\DATA\gsdtest.mdf',SIZE=3072KB,FILEGROWTH=1024KB)
LOGON
(NAME=N'gsdtest_log',FILENAME=N'C:
\ProgramFiles\MicrosoftSQLServer\MSSQL.1\MSSQL\DATA\gsdtest_log.ldf',SIZE=1024KB,FILEGROWTH=10%)
SIMULATION1:
Youareadatabaseadministratorforyourcompany. UsersreportthatinsertstatementsagainsttheviewnamedProduction.vExpensiveExpiredProductdonotsucceed.
Youconfirmthattheinsertstatementsthatarebeingusedarevalid.
TheonlytablethattheviewusesisnamedProduction.ExpiredProduct.
Thetablehasthefollowingdefinition:
CREATETABLEProduction.ExpiredProduct{
ExpiredProductlDINTIDENTITYCONSTRAINTPK_ExpiredProductPRIMARYKEY
NameNVARCHAR(50)NOTNULL
ListPriceMONEYNULL
}
Youneedtoensurethatinsertstatementscanbecompletesuccessfully.Youneedtoapplythefewestpossiblechanges,andyoumustuseSQLServerManagementStudio(SSMS).
步骤
操作
1,删除trigger
找到Production.ExpiredProduct打开trigger文件删除trigger
SIMULATION2:
Question:
Youworkasadatabaseadministratoratyourcompany.
UsersreportthattheyreceiveanerrormessagewhentheyexecuteaCLRuser-definedfunctionnamedProduction.ufnGetProductionInfointheTestKingdatabase.Theerrorisoccurringbecausethefunctionisbeingdeniedaccesstothelocalfilesystem.Youmustensurethattheleastpermissionspossibletosolvetheproblemareapplied.
YouneedtoallowthefunctiontoaccessthelocalfilesystemsbyusingSQLServer
ManagementStudio(SSMS).
Youmustapplytheminimumnumberofpossiblechanges.
操作步骤:
1.以windows身份验证登入SQLVSCORERTM,打开database选择AdventureWorks下的Programmablity,找到Fuctions节点下的Scalar-valued函数
2.首先找到函数dbo.ufnleadingZeros,右击选择查看ViewDenendencies从属
步骤
操作
3.修改“Assembly2”的属性设置
在Assemblies中找到“Assembly2”右击选择属性中的许可设置修改成Externalaccess
SIMULATION3:
Youareadatabaseadministratorforyourcompany.
YouneedtocreateamaintenanceplantorebuildindexesintheAdventureWorksdatabase.Theindexesmustberebuiltusingafillfactorof90.
Thefillfactormustbeappliedtoboththeleaflevelandthebalancedtreeoftheindex.Whilethemaintenanceplanisrunning,alltablesmustbeavailableforqueriestoberun.
YouneedtoaccomplishthistaskbycompletingtheDefineRebuildIndexTaskpageintheMaintenancePlanWizard.
操作步骤:
1.以windows身份验证登入SQLVSCORERTM,确认SQLserverAgent已正常启动.点选Management节点下的MantenancePlan
2.实行维护计划向导以及维护任务,找到MantenancePlan右击打开MantenancePlanWizardz。
3.在选择维护任务SeclectMaintenanceTasks一栏中选择Rebuildindex。
步骤
操作
4.定义重建目录任务
在RebuildindexTask对话框中,选择specific数据库并且其对象为tablesandviews.修改自由空间选项百分比为(100-90)%,在高级选项中选择Padindex和Keepindexonlinewhilereindexing
5.完成向导
默认设置点选NEXT完成维护计划向导
SIMULATION4:
Youareadatabaseadministratorforyourcompany.
Theonlynon-systemdatabaseontheserverisAdventureWorks.
Anewwrittencompanypolicystatusthatnestedtriggerexecutionisnotallowed.
YouneedtoensurethatthispolicyisimplementedbymodifyingtheappropriateserveranddatabasesettingsintheDatabasePropertiesdialogboxortheServerPropertiesdialogbox,orbothofthesedialogboxes,inSQLServerManagementStudio(SSMS).
操作步骤:
1.以windows身份验证登入SQLVSCORERTM,确认SQLserverAgent已正常启。
右击SQL选择属性。
2。
在弹出的属性窗口中,在Server属性中的高级设置选项中,在Miscellaneous下的AllowTriggerstoFireOthers状态设置成False
SIMULATION5:
Youareadatabaseadministratorforyourcompany.
AqueryagainsttheSales.SalesOrderDetailtableisperformingpoorly.Youmustcreateanindexthatwillcausethecostofthequerytobeaslowaspossible.Youmustminimizethespacethatisusedbytheindex.Theindexmustcontainasfewcolumnsaspossible.
Thereisanexistingclusteredindexonthetable.Thisindexmustnotbedropped.
Becausethetableisonaproductionserver,itisnotpossibletocreatedifferentindexesandtesttheirperformance.
YouneedtocreatetherequiredindexesbyusingtheNewIndexdialogbox.
步骤操作:
1.以windows身份验证登入SQLVSCORERTM,确认SQLserverAgent已正常启.展开AdventureWorks节点下的Tables找到Sales.SalesOrderDetail表格。
添写新的索引名,确保其未被使用
在索引类型一列中选择NONCLUSTERED项,
在索引关键列Indexkeycolumn点击‘添加’按钮
在如下所示的窗口中点选OrderQTy,点击OK完成。
接下来点击Include列,所示的关键列并没有包括在索引里,点击添加按钮.
在以下所示的对话框中,选择ProductID行
点选Ok完成.新的索引就已经创建。
SIMULATION6:
Youareadatabaseadministratorforyourcompany.
Usersreportthatqueriesthatareexecutedagainstsomeviewsarereturningincorrectresults.
Youinvestigateanddiscoverthattheproblemisbeingcausedbyoneormoreviews.
Youwanttoensurethateachoftheseviewsreturnstheresultsdescribedinthefollowingtable.
Exhibit:
Viewname
Requiredresult
HumanResources.vEmployeeTopVacationHours
ReturnstheemployeesthathavethehighestvalueintheVacationHoursfield
HumanResources.vEmployeeAverageOrMoreVacationHours
Returnstheemployeesthathaveavalueinthe
VacationHoursfieldthatisgreaterthanorequal
totheaverage
Production.vProductMediurnPrice
ReturnstheproductsthathaveavalueintheListPricefieldthatisgreaterthanorequalto10andlessthan20.
Poduct.vProductMini
ReturnstheproductsthathaveavalueintheNameFieldthatstartswiththetextMiniusingacasesensitivecomparison.
Youneedtoexaminethequeryineachоthefourviewsandthendeletethevieworviewsthatreturnincorrectresults.
Youmustnotdeleteviewsthatreturncorrectresults.
Answer:
Guys,whatistherealanswertothis?
Whichofthese4viewsshouldbedeletedduetotheirrespectivequeriesbeingwrong?
Doesanyoneknowwhichistherightanswer?
A).......TOP
(1)VacationHours….
B)……>=AVG(…).......
С)....>=10and<20.....
D)...namelike‘mini%’.......
JustDeletetheTOP
(1)View?
SIMULATION7:
Question
Youworkasadatabaseadministratoratyourcompany.YourcompanyhastwoSQL
Server2005ServersnamedSQL1andSQL2.
YouneedtoaddSQL2asalinkedserveronSQL1.AlltheloginsshouldbeabletoaccessSQL2throughSQL1AloginnamedApp2withthepassword“password”mustbeusedforthelinkedserverconnectiontoSQL2.Youmustapplythefewestpossiblesettings.YouneedtocreatethelinkedserverbyusingtheNewLinkedServerdialogboxonSQL1
步骤操作:
1.以windows身份验证登入SQLVSCORERTM,确认SQLserverAgent已正常启.选择ServerObjects下的LinkedServer节点,右击选择新的链接服务…
2.在弹出的窗口,创建一个新的链接窗口上,在Generally页面修改Linkedserver为:
SQL2.Servertype:
类型是SQLServer。
3.选择Securty页面,使用安全身份验证登入,设置远程用户名:
app2,密码为:
pssword。
SIMULATION8:
Youareadatabaseadministratorforyourcompany.YouneedtocreateanewdatabasenamedSales.
Thenewdatabasemustmeetthefollowingrequirements.
*Thedatafilemustbe10,000MBinsize
*Thelogfilemustbe3,000MBinsize.
*Bothfilesshouldhavethedefaultsettingsforautomaticfilegrowthandmaximumfilesize.
*Thetransactionlogmustbestoredonafault-tolerantvolume.
*Thedatafile,thetransactionlogfile,andtheWindowsinstallationmustallbelocatedondifferentvolumes.
*ThedatabasefilemustbestoredinafoldernamedSQLintherootofeachvolume.
*ThetransactionlogmustbesettobeautomaticallytruncatedTheavailablevolumesareshownintheexhibit.
Theavailablevolumesareshowninthefollowingtable.
Volume
RAIDlevel
Freespace
С:
(Windowsvolume)
RAID1
20GB
D:
RAID1
20GB
E:
RAID0
20GB
YouneedtocreatetheSalesdatabasebyusingtheNewDatabasedialogbox.
操作步骤:
1.以windows身份验证登入SQLVSCORERTM,确认SQLserverAgent已正常启.右击database选择NewDatabase.
.
2.在弹出的NewDatabase窗口上,选择General页面,数据库名改成Sales,数据库文件栏中的InitialSize一列中数据大小设为10,000(MB),日志大小设为3,000(MB).Path一列中数据文件路径设为:
E:
\SQL,日志文件路径设为:
D:
\SQL.
3.点选Options页面,在备份模式Recoverymodel下拉一栏中,选择Simple模式。
SIMULATION9:
Question:
Youareadatabaseadministratorforyourcompany.
TheAdventureWorksdatabaseistheonlynon-systemdatabaseontheserver.TransactionlogbackupsarepartofthebackuproutinefortheAdventureWorksdatabase.
Eachnight,severalSQLServerIntegrationServices(SSIS)packagesareruntoimport5millionrowsofdata.Youneedtominimizethelogspacethatisusedbytheseimportoperations.YoumustmodifytheappropriateserveranddatabasesettingsbyusingtheDatabasePropertiesdialogboxattheServerPropertiesdialogbox,orbothofthesedialogboxes,inSQLServerManagementStudio(SSMS).
操作步骤:
以windows身份验证登入SQLVSCORERTM,确认SQLserverAgent已正常启动.选择Database下的右击AdventureWorksDW节点的属性在弹出的窗口里,选择Options页面,选修改备份模式Recoverymodel为Bulk-logged模式
SIMULATION10:
Question:
YouworkasadatabaseadministratoratTestK.
YouneedtomovetheTestKingdatabasetoadifferentSQLServerassistance.Youmustperformabackupthatcontainsallofthechangessincethelasttransactionlogbackup.
Thesizeofthefilebackupmustbekepttoaminimum.Youarecurrentlyusinglogshipping,whichmustbenotbedisrupted
ThebackupfilemustbenamedAW.bak,anditmustbeplacedintheD:
\Datafolder.
YouneedtoperformthebackupbyusingtheBackUpDatabasedialogbox.
操作步骤:
1.以windows身份验证登入SQLVSCORERTM,确认SQLserverAgent已正常启动
右击AdenventureWorks出现的窗口Tasks里面选择BackUp…备份数据库
2.在备份数据库窗口中,选择Generally页面,
修改Origin选项中设置数据类型DataType为Transactionallog
在Destination一栏中选择现有的路径点击添加Agging,
输入一个新的地址路径:
D:
\Data\AW.bak,点击Ok完成
NextyouselectOption.
3.选择Option页面,在弹出的窗口中,如下图所示,选择overwriteallexistingbackupsets
以及verifybackupwhenfinished。
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- sql70 431实验考题 431 实验 考题