ASP网页编程之SQL Server Views
缺乏可以共同遵循的行业标准，ASP还处在发展初期，大家对它的理解不同，如产品和服务标准，收费标准等，不利于行业的健康发展。serverbegin:SQLServerViews
Postedbyscotton2004年11月28日
AnRDBMSusesaviewtocreateavirtualtable.Thecarefuluseofviewscanimprovetheinteractionbetweena.NETapplicationandtheunderlyingdata.InthisarticlewewilldiscussviewsinMicrosoftSQLServer,includingbestpracticesforcreatingandusingviews.
InSQLServeraviewrepresentsavirtualtable.Justlikearealtable,aviewconsistsofrowswithcolumns,andyoucanretrievedatafromaview(sometimesevenupdatedatainaview).Thefieldsintheview’svirtualtablearethefieldsofoneormorerealtablesinthedatabase.Youcanuseviewstojointwotablesinyourdatabaseandpresenttheunderlyingdataasifthedatawerecomingfromasingletable,thussimplifyingtheschemaofyourdatabaseforusersperformingad-hocreporting.Youcanalsouseviewsasasecuritymechanismtorestrictthedataavailabletoendusers.Viewscanalsoaggregatedata(particularlyusefulifyoucantakeadvantageofindexedviews),andhelppartitiondata.Inthisarticlewewilllookatthesedifferenttypesofviewtoseewhenwecantakeadvantageofaviewforourapplication.
SampleView
ThesampledatabaseNorthwindinSQLServerhasanumberofviewsinstalledbydefault.Oneexampleisthe“CurrentProductList”view,shownhere.
SELECT
Product_List.ProductID,Product_List.ProductName
FROM
ProductsASProduct_List
WHERE(Product_List.Discontinued=0)
FrominsideanapplicationwecanissuethefollowingSQLquerytoretrieveasetofrecordsrepresentingactiveproducts.
SELECTProductID,ProductNamefrom
TheviewhascreatedanewvirtualtablebyusingrecordsfromtheProductstableandapplyingasmallpieceoflogic(afilterontheDiscontinuedfield).Youcouldusetheviewinsideofaqueryfromyourapplication,orastoredprocedure,orevenfrominsideanotherview.Viewsareasimplebutpowerfulabstraction.Youcanpushquerycomplexity,likefilterandjoinstatements,intoaviewtopresentasimplermodelofthedatawithoutsacrificingthedatabasedesignorintegrity.
Weoftendescribeaviewasavirtualtablebecausethedatabasedoesnotstoretheviewdata.Instead,whenweretrievedatafromaviewthedatabaseenginerecreatesthedatausingtheSELECTstatementsintheview’sdefinition.Sincethedatabaseonlystoresadefinitionoftheview,andnotthedata,thereisnosignificantcostinspaceforusingaview,althoughthereisanexceptiontothisrulewewilldiscusslaterinthearticle.NotealsothatthedatabaseenginesqueryoptimizercanoftencombinethedefinitionoftheviewwiththeSQLqueriesinteractingwiththeviewtoprovideanefficientqueryplan(inotherwords,thedatabaseenginemightnotneedtoperformtheentireSELECToperationintheviewifitknowstheouterquerywillfilteroutadditionalrecords).
WhenToUseAView
Youneedtohaveagoalinmindwhencreatingaview.Thereareanumberofscenarioswhereyouwillwanttolookforaviewasasolution.
Tohidethecomplexityoftheunderlyingdatabaseschema,orcustomizethedataandschemaforasetofusers.
Tocontrolaccesstorowsandcolumnsofdata.
Toaggregatedataforperformance.
Let’stakealookateachofthesescenarios.
ComplexityandCustomization
Takingcareofcomplexjoinsandfilteringrulesinsideofaviewcanbenefitotherusers.Asanexample,considerthefollowingviewfromtheNorthwinddatabase.
CREATEVIEW"OrderDetailsExtended"AS
SELECT
"OrderDetails".OrderID,
"OrderDetails".ProductID,
Products.ProductName,
"OrderDetails".UnitPrice,
"OrderDetails".Quantity,
"OrderDetails".Discount,
(CONVERT(money,("OrderDetails".UnitPrice*Quantity*(1-Discount)/100))*100)ASExtendedPrice
FROM
Products
INNERJOIN
"OrderDetails"ON
Products.ProductID="OrderDetails".ProductID
Abusinessuserwithanad-hocreportingtoolcantakeadvantageoftheaboveviewinbuildingcustomizedreportstosupporthergoals.Shecanusetheviewtoseeallofthedetailsaboutanorderwithoutfindingthetablestojoinforproductandorderinformation,andwithoutperformingthecalculationforthepricediscount.Notonlydoesthismakethedatabaseeasierfortheenduser,butitalsoallowsaDBAtomakechangestot</p>强大的可扩展性。ASP具有强大的扩展性，可以实现与多种网络、硬件设备的连接：通过专用的通讯线路远程接入企业；通过远程拨号服务器为远程拨号客户提供服务；通过WAP为移动电话互联网客户服务。 用户端的浏览器不需要提供任何别的支持，这样大提高了用户与服务器之间的交互的速度。 Application:这个存储服务端的数据，如果不清除，会直到web应用程序结束才清除(例如重启站点) ASP（ActiveServerPages）是Microsfot公司1996年11月推出的WEB应用程序开发技术，它既不是一种程序语言，也不是一种开发工具，而是一种技术框架，不须使用微软的产品就能编写它的代码，能产生和执行动态、交互式、高效率的站占服务器的应用程序。 Request:从字面上讲就是“请求”，因此这个是处理客户端提交的东东的,例如Resuest.Form，Request.QueryString,或者干脆Request("变量名") 运用经典的例子。并且自己可以用他来实现一些简单的系统。如果可以对他进行进一步的修改，找出你觉得可以提高性能的地方，加上自己的设计，那就更上一个层次了，也就会真正地感到有所收获。 那么，ASP.Net有哪些改进呢？ ASP（ActiveServerPages）是Microsfot公司1996年11月推出的WEB应用程序开发技术，它既不是一种程序语言，也不是一种开发工具，而是一种技术框架，不须使用微软的产品就能编写它的代码，能产生和执行动态、交互式、高效率的站占服务器的应用程序。 不能只是将它停留在纸上谈兵的程度上。
页:
[1]