sql70 431实验考题文档格式.docx

sql70 431实验考题文档格式.docx

《sql70 431实验考题文档格式.docx》由会员分享，可在线阅读，更多相关《sql70 431实验考题文档格式.docx（20页珍藏版）》请在冰点文库上搜索。

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：

Theonlynon-systemdatabaseontheserverisAdventureWorks.

Anewwrittencompanypolicystatusthatnestedtriggerexecutionisnotallowed.

YouneedtoensurethatthispolicyisimplementedbymodifyingtheappropriateserveranddatabasesettingsintheDatabasePropertiesdialogboxortheServerPropertiesdialogbox,orbothofthesedialogboxes,inSQLServerManagementStudio（SSMS）.

1.以windows身份验证登入SQLVSCORERTM，确认SQLserverAgent已正常启。

右击SQL选择属性。

2。

在弹出的属性窗口中，在Server属性中的高级设置选项中,在Miscellaneous下的AllowTriggerstoFireOthers状态设置成False

SIMULATION5：

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：

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:

E:

RAID0

YouneedtocreatetheSalesdatabasebyusingtheNewDatabasedialogbox.

1.以windows身份验证登入SQLVSCORERTM，确认SQLserverAgent已正常启.右击database选择NewDatabase.

.

2.在弹出的NewDatabase窗口上,选择General页面，数据库名改成Sales,数据库文件栏中的InitialSize一列中数据大小设为10,000（MB）,日志大小设为3,000（MB）.Path一列中数据文件路径设为:

E:

\SQL,日志文件路径设为:

\SQL.

3.点选Options页面，在备份模式Recoverymodel下拉一栏中，选择Simple模式。

SIMULATION9：

TheAdventureWorksdatabaseistheonlynon-systemdatabaseontheserver.TransactionlogbackupsarepartofthebackuproutinefortheAdventureWorksdatabase.

Eachnight,severalSQLServerIntegrationServices（SSIS）packagesareruntoimport5millionrowsofdata.Youneedtominimizethelogspacethatisusedbytheseimportoperations.YoumustmodifytheappropriateserveranddatabasesettingsbyusingtheDatabasePropertiesdialogboxattheServerPropertiesdialogbox,orbothofthesedialogboxes,inSQLServerManagementStudio（SSMS）.

以windows身份验证登入SQLVSCORERTM，确认SQLserverAgent已正常启动.选择Database下的右击AdventureWorksDW节点的属性在弹出的窗口里，选择Options页面，选修改备份模式Recoverymodel为Bulk-logged模式

SIMULATION10：

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。