HOCSQL

HOCSQL - HC SQL SERVER 20001MC LCOverview of SQL Server 2000

Info iconThis preview shows page 1. Sign up to view the full content.

View Full Document Right Arrow Icon
This is the end of the preview. Sign up to access the rest of the document.

Unformatted text preview: HC SQL SERVER 20001MC LCOverview of SQL Server 2000 ........................................................................................... 2Transact SQL ...................................................................................................................... 7Backup And Restore SQL Server ..................................................................................... 24Stored Procedure and Advanced T-SQL........................................................................... 41Triggers And Views.......................................................................................................... 51http://ebooks.vdcmedia.comHC SQL SERVER 20002Overview of SQL Server 2000 c v hiu bi vit ny bn phi c kin thc cn bn v SQL v Access DatabaseGii Thiu SQL Server 2000SQL Server 2000 l mt h thng qun l c s d liu (Relational DatabaseManagement System (RDBMS) ) s dng Transact-SQL trao i d liu giaClient computer v SQL Server computer. Mt RDBMS bao gm databases,database engine v cc ng dng dng qun l d liu v cc b phn khcnhau trong RDBMS.SQL Server 2000 c ti u c th chy trn mi trng c s d liu rtln (Very Large Database Environment) ln n Tera-Byte v c th phc vcng lc cho hng ngn user. SQL Server 2000 c th kt hp "n " vi ccserver khc nh Microsoft Internet Information Server (IIS), E-CommerceServer, Proxy Server....SQL Server c 7 editions:Enterprise : Cha y cc c trng ca SQL Server v c th chytt trn h thng ln n 32 CPUs v 64 GB RAM. Thm vo n c ccdch v gip cho vic phn tch d liu rt hiu qu (Analysis Services)Standard : Rt thch hp cho cc cng ty va v nh v gi thnh r hnnhiu so vi Enterprise Edition, nhng li b gii hn mt s chc nngcao cp (advanced features) khc, edition ny c th chy tt trn hthng ln n 4 CPU v 2 GB RAM.Personal: c ti u ha chy trn PC nn c th ci t trn huht cc phin bn windows k c Windows 98.Developer : C y cc tnh nng ca Enterprise Edition nhng cch to c bit nh gii hn s lng ngi kt ni vo Server cng mtlc.... y l edition m cc bn mun hc SQL Server cn c. Chng tas dng edition ny trong sut kha hc. Edition ny c th ci trnWindows 2000 Professional hay Win NT Workstation.Desktop Engine (MSDE): y ch l mt engine chy trn desktop vkhng c user interface (giao din). Thch hp cho vic trin khai ngdng my client. Kch thc database b gii hn khong 2 GB.http://ebooks.vdcmedia.comHC SQL SERVER 2000Win CE : Dng cho cc ng dng chy trn Windows CE3Trial: C cc tnh nng ca Enterprise Edition, download free, nhng giihn thi gian s dng.Ci t SQL Server 2000 (Installation)Cc bn cn c Developer Edition v t nht l 64 MB RAM, 500 MB hard disk c th install SQL Server. Bn c th install trn Windows Server hayWindows XP Professional, Windows 2000 Professional hay NT Workstation nhngkhng th install trn Win 98 family.V mt trong nhng c im ca cc sn phm Microsoft l d install nn chngti khng trnh by chi tit v cch install hay cc bc install m ch trnh bycc im cn lu khi install m thi. Nu cc bn gp tr ngi trong vic installth c th a ln forum hi thm. Khi install bn cn lu cc im sau: mn hnh th hai bn chn Install Database Server. Sau khi install xongSQL Server bn c th install thm Analysis Service nu bn thch. mn hnh Installation Definition bn chn Server and Client Tools.Sau bn nn chn kiu Custom v chn tt c cc b phn ca SQL Server.Ngoi ra nn chn cc gi tr mc nh (default) mn hnh Authentication Mode nh chn Mixed Mode . Lu v SQLServer c th dng chung ch bo mt (security) vi Win NT v cng c thdng ch bo mt ring ca n. Trong Production Server ngi ta thngdng Windows Authetication v an ton cao hn v d dng cho ngi qunl mng v c cho ngi s dng. Ngha l mt khi bn c chp nhn(authenticated) kt ni vo domain th bn c quyn truy cp d liu (accessdata) trong SQL Server. Tuy nhin ta nn chn Mixed Mode d dng cho vichc tp.Sau khi install bn s thy mt icon nm gc phi bn di mn hnh, ychnh l Service Manager. Bn c th Start, Stop cc SQL Server services ddng bng cch double-click vo icon ny.Mt cht kin thc v cc Version ca SQL ServerSQL Server ca Microsoft c th trng chp nhn rng ri k t version 6.5.Sau Microsoft ci tin v hu nh vit li mt engine mi cho SQL Server7.0. Cho nn c th ni t version 6.5 ln version 7.0 l mt bc nhy vt. Cmt s c tnh ca SQL Server 7.0 khng tng thch vi version 6.5. Trong khihttp://ebooks.vdcmedia.comHC SQL SERVER 20004 t Version 7.0 ln version 8.0 (SQL Server 2000) th nhng ci tin ch yu lm rng cc tnh nng v web v lm cho SQL Server 2000 ng tin cy hn.Mt im c bit ng lu version 2000 l Multiple-Instance. Ni cho dhiu l bn c th install version 2000 chung vi cc version trc m khng cnphi uninstall chng. Ngha l bn c th chy song song version 6.5 hoc 7.0vi version 2000 trn cng mt my (iu ny khng th xy ra vi cc versiontrc y). Khi version c trn my bn l Default Instance cn version2000 mi va install s l Named Instance.Cc thnh phn quan trng trong SQL Server 2000SQL Server 2000 c cu to bi nhiu thnh phn nh Relational DatabaseEngine, Analysis Service v English Query.... Cc thnh phn ny khi phi hpvi nhau to thnh mt gii php hon chnh gip cho vic lu tr v phn tchd liu mt cch d dng.Relational Database Engine - Ci li ca SQL Server:y l mt engine c kh nng cha data cc quy m khc nhau di dngtable v support tt c cc kiu kt ni (data connection) thng dng caMicrosoft nh ActiveX Data Objects (ADO), OLE DB, and Open DatabaseConnectivity (ODBC). Ngoi ra n cn c kh nng t iu chnh (tune up) v dhttp://ebooks.vdcmedia.comHC SQL SERVER 20005nh s dng thm cc ti nguyn (resource) ca my khi cn v tr li tinguyn cho h iu hnh khi mt user log off.Replication - C ch to bn sao (Replica):Gi s bn c mt database dng cha d liu c cc ng dng thngxuyn cp nht. Mt ngy p tri bn mun c mt ci database ging y htnh th trn mt server khc chy bo co (report database) (cch lm nythng dng trnh nh hng n performance ca server chnh). Vn lreport server ca bn cng cn phi c cp nht thng xuyn m botnh chnh xc ca cc bo co. Bn khng th dng c ch back up and restoretrong trng hp ny. Th th bn phi lm sao? Lc c ch replication caSQL Server s c s dng bo m cho d liu 2 database c ng b(synchronized). Replication s c bn k trong bi 12Data Transformation Service (DTS) - Mt dch v chuyn dch data v cnghiu quNu bn lm vic trong mt cng ty ln trong data c cha trong nhiu nikhc nhau v cc dng khc nhau c th nh cha trong Oracle, DB2 (caIBM), SQL Server, Microsoft Access....Bn chc chn s c nhu cu di chuyndata gia cc server ny (migrate hay transfer) v khng ch di chuyn bn cnmun nh dng (format) n trc khi lu vo database khc, khi bn s thyDTS gip bn gii quyt cng vic trn d dng nh th no. DTS s c bnk trong bi 8.Analysis Service - Mt dch v phn tch d liu rt hay ca MicrosoftD liu (Data) cha trong database s chng c ngha g nhiu nu nh bnkhng th ly c nhng thng tin (Information) b ch t . Do Microsoftcung cp cho bn mt cng c rt mnh gip cho vic phn tch d liu tr nnd dng v hiu qu bng cch dng khi nim hnh khi nhiu chiu (multidimension cubes) v k thut "o m d liu" (data mining) s c chng tigii thiu trong bi 13.English Query - Mt dch v m ngi Vit Nam chc l t mun dng :-) (?)y l mt dch v gip cho vic query data bng ting Anh "trn" (plainEnglish).Meta Data Service:Dch v ny gip cho vic cha ng v "xo nu" Meta data d dng hn. Thth Meta Data l ci g vy? Meta data l nhng thng tin m t v cu trc cadata trong database nh data thuc loi no String hay Integer..., mt ct nohttp://ebooks.vdcmedia.comHC SQL SERVER 20006 c phi l Primary key hay khng....Bi v nhng thng tin ny cng ccha trong database nn cng l mt dng data nhng phn bit vi data"chnh thng" ngi ta gi n l Meta Data. Phn ny chc l bn phi xem thmtrong mt thnh phn khc ca SQL Server sp gii thiu sau y l SQLServer Books Online v khng c bi no trong lot bi ny ni r v dch vny c.SQL Server Books Online - Quyn Kinh Thnh khng th thiu:Cho d bn c c cc sch khc nhau dy v SQL server th bn cng s thybooks online ny rt hu dng v khng th thiu c( cho nn Microsoft miho phng nh km theo SQL Server).SQL Server Tools - y l mt b ngh ca ngi qun tr c s d liu(DBA )i ch nu k chi tit ra th hi nhiu y cho nn bn cn c thm trong booksonline. y ngi vit ch k ra mt vi cng c thng dng m thi.u tin phi k n Enterprise Manager. y l mt cng c cho tathy ton cnh h thng c s d liu mt cch rt trc quan. N rt huch c bit cho ngi mi hc v khng thng tho lm v SQL.K n l Query Analyzer. i vi mt DBA gii th hu nh ch cncng c ny l c th qun l c mt h thng database m khng cnn nhng th khc. y l mt mi trng lm vic kh tt v ta c thnh bt k cu lnh SQL no v chy ngay lp tc c bit l n gipcho ta debug my ci stored procedure d dng.Cng c th ba cn phi k n l SQL Profiler. N c kh nng "chp"(capture) tt c cc s kin hay hot ng din ra trn mt SQL server vlu li di dng text file rt hu dng trong vic kim sot hot ngca SQL Server.Ngoi mt s cng c trc quan nh trn chng ta cng thng hay dngosql v bcp (bulk copy) trong command prompt.Tm li trong bi ny chng ta do qua mt vng tm hiu v SQL Server.Trong bi sau chng ta cng s tip tc do chi thm mt cht vi TransactSQL trc khi i su vo cc ti khc.http://ebooks.vdcmedia.comHC SQL SERVER 20007Transact SQLGii Thiu S Lc V Transact SQL (T-SQL)Transact-SQL l ngn ng SQL m rng da trn SQL chun ca ISO(International Organization for Standardization) v ANSI (American NationalStandards Institute) c s dng trong SQL Server khc vi P-SQL (ProceduralSQL) dng trong Oracle.Trong bi ny chng ta s tm hiu s qua v T-SQL. Chng c chia lm 3nhm:Data Definition Language (DDL):y l nhng lnh dng qun l cc thuc tnh ca mt database nh nhngha cc hng hoc ct ca mt table, hay v tr data file ca mtdatabase...thng c dngCreate object_NameAlter object_NameDrop object_NameTrong object_Name c th l mt table, view, stored procedure, indexes...V d:Lnh Create sau s to ra mtCompanyID,CompanyName,ContacttabletnImportersvi3ctUSE NorthwindCREATE TABLE Importers(CompanyID int NOT NULL,CompanyName varchar(40) NOT NULL,Contact varchar(40) NOT NULL)Lnh Alter sau y cho php ta thay i nh ngha ca mt table nh thm(haybt) mt ct hay mt Constraint...Trong v d ny ta s thm ct ContactTitlevo table ImportersUSE Northwindhttp://ebooks.vdcmedia.comHC SQL SERVER 20008ALTER TABLE ImportersADD ContactTitle varchar(20) NULLLnh Drop sau y s hon ton xa table khi database ngha l c nhngha ca table v data bn trong table u bin mt (khc vi lnhDelete ch xa data nhng table vn tn ti).USE NorthwindDROP TABLE ImportersData Control Language (DCL):y l nhng lnh qun l cc quyn truy cp ln tng object (table, view,stored procedure...). Thng c dng sau:GrantRevokeDenyV d:Lnh sau s cho php user trong Public Role c quyn Select i vi tableCustomer trong database Northwind (Role l mt khi nim ging nh WindowsGroup s c bn k trong phn Security)USE NorthwindGRANT SELECTON CustomersTO PUBLICLnh sau s t chi quyn Select i vi table Customer trong databaseNorthwind ca cc user trong Public RoleUSE NorthwindDENY SELECTON CustomersTO PUBLICLnh sau s xa b tc dng ca cc quyn c cho php hay t chi trc USE NorthwindREVOKE SELECTON CustomersTO PUBLIChttp://ebooks.vdcmedia.comHC SQL SERVER 20009Data Manipulation Language (DML):y l nhng lnh ph bin dng x l data nh Select, Update, Insert,DeleteV d:SelectUSE NorthwindSELECT CustomerID, CompanyName, ContactNameFROM CustomersWHERE (CustomerID = 'alfki' OR CustomerID = 'anatr')ORDER BY ContactNameInsertUSE NorthwindINSERT INTO TerritoriesVALUES (98101, 'Seattle', 2)UpdateUSE NorthwindUPDATE TerritoriesSET TerritoryDescription = 'Downtown Seattle'WHERE TerritoryID = 98101DeleteUSE NorthwindDELETE FROM TerritoriesWHERE TerritoryID = 98101V phn ny kh cn bn nn chng ti thit ngh khng cn gii thch nhiu.Ch trong lnh Delete bn c th c ch From hay khng u c.Nhng m chng ta s chy th cc v d trn u? chy cc cu lnh th d trn bn cn s dng v lm quen vi QueryAnalyserhttp://ebooks.vdcmedia.comHC SQL SERVER 200010C Php Ca T-SQL:Phn ny chng ta s bn v cc thnh phn to nn c php ca T-SQLIdentifiersy chnh l tn ca cc database object. N dng xc nh mt object. (Ch khi ni n Object trong SQL Server l chng ta mun cp n table, view,stored procedure, index.....V hu nh mi th trong SQL Server u c thitk theo kiu hng i tng (object-oriented)). Trong v d sau TableX,KeyCol, Description l nhng identifiersCREATE TABLE TableX(KeyCol INT PRIMARY KEY, Description NVARCHAR(80))C hai loi Identifiers mt loi thng thng (Regular Identifier) v mt loigi l Delimited Identifier, loi ny cn c du "" hay du ngn cch.Loi Delimited c dng i vi cc ch trng vi t kha ca SQL Server(reserved keyword) hay cc ch c khong trng.http://ebooks.vdcmedia.comHC SQL SERVER 200011V d:SELECT * FROM [My Table]WHERE [Order] = 10Trong v d trn ch Order trng vi keyword Order nn cn t trong dungoc vung .Variables (Bin)Bin trong T-SQL cng c chc nng tng t nh trong cc ngn ng lp trnhkhc ngha l cn khai bo trc loi d liu trc khi s dng. Bin c btu bng du @ (i vi cc global variable th c hai du @@)V d:USE NorthwindDECLARE @EmpIDVar INTSET @EmpIDVar = 3SELECT * FROM EmployeesWHERE EmployeeID = @EmpIDVar + 1Functions (Hm)C 2 loi hm mt loi l built-in v mt loi user-definedCc hm Built-In c chia lm 3 nhm:Rowset Functions : Loi ny thng tr v mt object v ci x nh mt table. V d nh hm OPENQUERY s tr v mtrecordset v c th ng v tr ca mt table trong cu lnh Select.Aggregate Functions : Loi ny lm vic trn mt s gi tr vtr v mt gi tr n hay l cc gi tr tng. V d nh hm AVG str v gi tr trung bnh ca mt ct.Scalar Functions : Loi ny lm vic trn mt gi tr n v trv mt gi tr n. Trong loi ny li chia lm nhiu loi nh nhcc hm v ton hc, v thi gian, x l kiu d liu String....V dnh hm MONTH('2002-09-30') s tr v thng 9.Cc hm User-Defined (c to ra bi cu lnh CREATE FUNCTION v phnbody thng c gi trong cp lnh BEGIN...END) cng c chia lm ccnhm nh sau:Scalar Functions : Loi ny cng tr v mt gi tr n bng culnh RETURNS.Table Functions : Loi ny tr v mt tablehttp://ebooks.vdcmedia.comHC SQL SERVER 200012Data Type (Loi D Liu)Cc loi d liu trong SQL Server s c bn k trong cc bi sauExpressionsCc Expressions c dng Identifier + Operators (nh +,-,*,/,=...) + ValueCc thnh phn Control-Of FlowNh BEGIN...END, BREAK, CONTINUE, GOTO, IF...ELSE, RETURN, WHILE.... Xinxem thm Books Online bit thm v cc thnh phn ny.Comments (Ch Thch)T-SQL dng du -- nh du phn ch thch cho cu lnh n v dng /*...*/ ch thch cho mt nhmThc Thi Cc Cu Lnh SQLThc thi mt cu lnh n:Mt cu lnh SQL c phn ra thnh cc thnh phn c php nh trn bi mtparser, sau SQL Optimizer (mt b phn quan trng ca SQL Server) s phntch v tm cch thc thi (Execute Plan) ti u nht v d nh cch no nhanh vtn t ti nguyn ca my nht... v sau SQL Server Engine s thc thi v trv kt qu.Thc Thi mt nhm lnh (Batches)Khi thc thi mt nhm lnh SQL Server s phn tch v tm bin php ti u chocc cu lnh nh mt cu lnh n v cha execution plan c bin dch(compiled) trong b nh sau nu nhm lnh trn c gi li ln na th SQLServer khng cn bin dch m c th thc thi ngay iu ny gip cho mt batchchy nhanh hn.Lnh GOLnh ny ch dng gi mt tn hiu cho SQL Server bit kt thc mt batchjob v yu cu thc thi. N vn khng phi l mt lnh trong T-SQL.Tm li trong phn ny chng ta tm hiu v Transact- SQL l ngn ng chnh giao tip vi SQL Server. Trong bi sau chng ta s tip tc bn v cu trcbn trong ca SQL Server .http://ebooks.vdcmedia.comHC SQL SERVER 200013Design and Implement a SQL Server DatabaseCu Trc Ca SQL ServerNh trnh by cc bi trc mt trong nhng c im ca SQL Server2000 l Multiple-Instance nn khi ni n mt (SQL) Server no l ta nin mt Instance ca SQL Server 2000, thng thng l Default Instance.Mt Instance ca SQL Server 2000 c 4 system databases v mt hay nhiu userdatabase. Cc system databases bao gm:Master : Cha tt c nhng thng tin cp h thng (system-levelinformation) bao gm thng tin v cc database khc trong hthng nh v tr ca cc data files, cc login account v cc thitt cu hnh h thng ca SQL Server (system configurationsettings).Tempdb : Cha tt c nhng table hay stored procedure c tmthi to ra trong qu trnh lm vic bi user hay do bn thn SQLServer engine. Cc table hay stored procedure ny s bin mt khikhi ng li SQL Server hay khi ta disconnect.Model : Database ny ng vai tr nh mt bng km (template)cho cc database khc. Ngha l khi mt user database c to rath SQL Server s copy ton b cc system objects (tables, storedprocedures...) t Model database sang database mi va to.Msdb : Database ny c SQL Server Agent s dng hochnh cc bo ng v cc cng vic cn lm (schedule alerts andjobs).Cu Trc Vt L Ca Mt SQL Server DatabaseMi mt database trong SQL Server u cha t nht mt data file chnh(primary), c th c thm mt hay nhiu data file ph (Secondary) v mttransaction log file.Primary data file (thng c phn m rng .mdf) : y l filechnh cha data v nhng system tables.Secondary data file (thng c phn m rng .ndf) : y l fileph thng ch s dng khi database c phn chia cha trnnhiu da.Transaction log file (thng c phn m rng .ldf) : y l fileghi li tt c nhng thay i din ra trong mt database v chay thng tin c th roll back hay roll forward khi cn.http://ebooks.vdcmedia.comHC SQL SERVER 200014Data trong SQL Server c cha thnh tng Page 8KB v 8 page lin tc tothnh mt Extent nh hnh v di y:Trc khi SQL Server mun lu data vo mt table n cn phi dnh ring mtkhong trng trong data file cho table . Nhng khong trng chnh l ccextents. C 2 loi Extents: Mixed Extents (loi hn hp) dng cha dataca nhiu tables trong cng mt Extent v Uniform Extent (loi thun nht)dng cha data ca mt table. u tin SQL Server dnh cc Page trongMixed Extent cha data cho mt table sau khi data tng trng th SQLdnh hn mt Uniform Extent cho table .Nguyn Tc Hot ng Ca Transaction Log Trong SQL ServerTransaction log file trong SQL Server dng ghi li cc thay i xy ra trongdatabase. Qu trnh ny din ra nh sau: u tin khi c mt s thay i datanh Insert, Update, Delete c yu cu t cc ng dng, SQL Server s ti(load) data page tng ng ln memory (vng b nh ny gi l data cache),sau data trong data cache c thay i(nhng trang b thay i cn gi ldirty-page). Tip theo mi s thay i u c ghi vo transaction log file chonn ngi ta gi l write-ahead log. Cui cng th mt qu trnh gi l CheckPoint Process s kim tra v vit tt c nhng transaction c commited(hon tt) vo da cng (flushing the page).http://ebooks.vdcmedia.comHC SQL SERVER 200015Ngoi Check Point Process nhng dirty-page cn c a vo da bi mt Lazywriter. y l mt anh chng lm vic m thm ch thc gic v qut qua phndata cache theo mt chu k nht nh sau li ng yn ch ln qut ti.Xin gii thch thm mt cht v khi nim transaction trong database. Mttransaction hay mt giao dch l mt lot cc hot ng xy ra c xem nhmt cng vic n (unit of work) ngha l hoc thnh cng ton b hoc khnglm g c (all or nothing). Sau y l mt v d c in v transaction:Chng ta mun chuyn mt s tin $500 t account A sangaccount B nh vy cng vic ny cn lm cc bc sau:1.Tr $500 t account A2.Cng $500 vo account BTuy nhin vic chuyn tin trn phi c thc hin di dng mttransaction ngha l giao dch ch cxem l hon tt (commited) khi c hai bc trn u thc hin thnhcng. Nu v mt l do no ta chc th thc hin c bc 1 (chng hn nh va xong bc 1 th incp hay my b treo) th xem nh giaodch khng hon tt v cn phi c phc hi li trng thi ban u(roll back).Th th Check Point Process hot ng nh th no c th m bo mttransaction c thc thi m khng lm "d" database.http://ebooks.vdcmedia.comHC SQL SERVER 200016Trong hnh v trn, mt transaction c biu din bng mt mi tn. Trc nmngang l trc thi gian. Gi s mt Check Point c nh du vo thi imgia transaction 2 v 3 nh hnh v v sau s c xy ra trc khi gp mtCheck point k tip. Nh vy khi SQL Server c restart n s da trn nhngg ghi trong transaction log file phc hi data (xem hnh v).iu c ngha l SQL Server s khng cn lm g c i vi transaction 1 v tithi im Check point data c lu vo da ri. Trong khi transaction 2v 4 s c roll forward v tuy c commited nhng do s c xy ra trcthi im check point k tip nn data cha kp lu vo da. Tc l da trnnhng thng tin c ghi trn log file SQL Server hon ton c y c s vit vo da cng. Cn transaction 3 v 5 th cha c commited (do b downbt ng) cho nn SQL Server s roll back hai transaction ny da trn nhng gc ghi trn log file.Cu Trc Logic Ca Mt SQL Server DatabaseHu nh mi th trong SQL Server c t chc thnh nhng objects v d nhtables, views, stored procedures, indexes, constraints.... Nhng system objectstrong SQL Server thng c bt u bng ch sys hay sp. Cc objects trn sc nghin cu ln lt trong cc bi sau do trong phn ny chng ta chbn s qua mt s system object thng dng trong SQL Server database mthi.http://ebooks.vdcmedia.comHC SQL SERVER 200017Mt s Sytem objects thng dng:System Stored Procedureng dngSp_help ['object']Cung cp thng tin v mt database object (table, view...) hay mt datatype.Sp_helpdb ['database']Cung cp thng tin v mt database c th no .Sp_monitorCho bit bn rn ca SQL ServerSp_spaceused ['object','updateusage' ]Cung cp thng tin v cc khong trng c s dng cho mt objectno Sp_who ['login']Cho bit thng tin v mt SQL Server userV d:sp_helpdb 'Northwind' s cho kt qu c dng nh bng di ynamedb_sizeownerdbidcreatedstatus .....------------------------------------------------------------------------------------------------------------------------------- ------Northwind3.94 MBsa6Aug 6 2000Status=ONLINE,Updateability=READ_WRITE, .....stored procedure sp_spaceused nh v d sauUSE NorthwindGosp_spaceused 'Customers's cho bit thng tin v table Customer:namerowsreserveddataindex_sizeunused------------------------------------- -----------------------------------------Customers91104 KB24 KB80 KB0 KBTo Mt User DatabaseChng ta c th to mt database d dng dng SQL Server Enterprise bngcch right-click ln trn "database" v chn "New Database" nh hnh v sau:http://ebooks.vdcmedia.comHC SQL SERVER 200018Sau chng ta ch vic nh tn ca database v click OK.Ngoi ra i khi chng ta cng dng SQL script to mt database. Khi taphi ch r v tr ca primary data file v transaction log file.V d:USE masterGOCREATE DATABASE ProductsON( NAME = prods_dat,FILENAME = 'c:\program files\microsoft SQLserver\mssql\data\prods.mdf',SIZE = 4,MAXSIZE = 10,FILEGROWTH = 1)GOTrong v d trn ta to mt database tn l Products vi logical file name lprods_dat v physical file name l prods.mdf, kch thc ban u l 4 MB vdata file s t ng tng ln mi ln 1 MB cho ti ti a l 10 MB. Nu ta khnghttp://ebooks.vdcmedia.comHC SQL SERVER 200019ch nh mt transaction log file th SQL s t ng to ra 1 log file vi kchthc ban u l 1 MB.Lu :Khi to ra mt database chng ta cng phi lu mt s im sau: i vi cch thng nh m vn tc ca server khng thuc loi nhy cm thchng ta thng chn cc gi tr mc nh (default) cho Initial size,Automatically growth file. Nhng trn mt s production server ca cc hthng ln kch thc ca database phi c ngi DBA c lng trc tytheo tm c ca business, v thng thng ngi ta khng chn Autogrowth(tng tng trng) v Autoshrink(t ng nn). Cu hi c t ra y l vsao ta khng SQL Server chn mt gi tr khi u cho datafile v sau khicn th n s t ng ni rng ra m li phi c lng trc? Nguyn nhn lnu chn Autogrowth (hay Autoshrink) th chng ta c th s gp 2 vn sau:Performance hit: nh hng ng k n kh nng lm vic caSQL Server. Do n phi thng xuyn kim tra xem c khongtrng cn thit hay khng v nu khng n s phi m rngbng cch dnh thm khong trng t da cng v chnh qu trnhny s lm chm i hot ng ca SQL Server.Disk fragmentation : Vic m rng trn cng s lm cho datakhng c lin tc m cha nhiu ni khc nhau trong da cngiu ny cng gy nh hng ln tc lm vic ca SQL Server.Trong cc h thng ln ngi ta c th d on trc kch thc ca databasebng cch tnh ton kch thc ca cc tables, y cng ch l kch thc con m thi (xin xem "Estimating the size of a database" trong SQL BooksOnline bit thm v cch tnh) v sau thng xuyn dng mt s cu lnhSQL (thng dng cc cu lnh bt u bng DBCC .Phn ny s c bn quatrong cc bi sau) kim tra xem c khong trng hay khng nu khng tac th chn mt thi im m SQL server t bn rn nht (nh ban m hay saugi lm vic) ni rng data file nh th s khng lm nh hng tiperformance ca Server.Ch gi s ta dnh sn 2 GB cho datafile, khi dng Window Explorer xem tas thy kch thc ca file l 2 GB nhng data thc t c th ch chim vi chcMB m thi.Nhng im Cn Lu Khi Thit K Mt DatabaseTrong phm vi bi ny chng ta khng th ni su v l thuyt thit k databasem ch a ra mt vi li khuyn m bn nn tun theo khi thit k.http://ebooks.vdcmedia.comHC SQL SERVER 200020Trc ht bn phi nm vng v cc loi data type. V d bn phi bit r skhc bit gia char(10), nchar(10) varchar(10), nvarchar(10). Loi d liuChar l mt loi string c kch thc c nh ngha l trong v d trn nu dataa vo "This is a really long character string" (ln hn 10 k t) th SQL Servers t ng ct phn ui v ta ch cn "This is a". Tng t nu string a vonh hn 10 th SQL s thm khong trng vo pha sau cho 10 k t. Ngcli loi varchar s khng thm cc khong trng pha sau khi string a vo thn 10. Cn loi data bt u bng ch n cha d liu dng unicode.Mt lu khc l trong SQL Server ta c cc loi Integer nh : tinyint,smallint, int, bigint. Trong kch thc tng loi tng ng l 1,2,4,8 bytes.Ngha l loi smallint tng ng vi Integer v loi int tng ng viLong trong VB.Khi thit k table nn:C t nht mt ct thuc loi ID dng xc nh mt record ddng.Ch cha data ca mt entity (mt thc th)Trong v d sau thng tin v Sch v Nh Xut Bn c cha trongcng mt tableBooksBookID TitlePublisherPubStatePubCityPubCountry1Inside SQL Server 2000 Microsoft PressCABerkelyUSA2Windows 2000 ServerMABostonUSA3Beginning Visual BasicWrox6.0CABerkelyUSANew RidersTa nn tch ra thnh table Books v table Publisher nh sau:BooksBookIDTitlePublisherID1Inside SQL Server 2000P12Windows 2000 ServerP23Beginning Visual Basic 6.0P3http://ebooks.vdcmedia.comHC SQL SERVER 200021vPublishersPublisherID PublisherPubStatePubCityPubCountryP1Microsoft PressCABerkelyUSAP2New RidersMABostonUSAP3WroxCABerkelyUSATrnh dng ct c cha NULL v nn lun c gi tr Default chocc ctTrnh lp li mt gi tr hay ct no V d mt cun sch c th c vit bi hn mt tc gi v nh th tac th dng mt trong 2 cch sau cha data:BooksBookIDTitleAuthors1Inside SQL Server 2000John Brown2Windows 2000 ServerMatthew Bortniker, Rick Johnson3Beginning Visual Basic 6.0Peter Wright, James Moon, JohnBrownhayBooksBookID TitleAuthor1Author2Author3NullNullMatthewBortnikerRick JohnsonNullPeter WrightJames MoonJohn Brown1Inside SQL Server 2000 John Brown2Windows 2000 Server3Beginning Visual Basic6.0Tuy nhin vic lp i lp li ct Author s to nhiu vn sau ny.Chng hn nh nu cun sch c nhiu hn 3 tc gi th chng ta s gp phinphc ngay....Trong v d ny ta nn cht ra thnh 3 table nh sau:http://ebooks.vdcmedia.comHC SQL SERVER 200022BooksBookIDTitle1Inside SQL Server 20002Windows 2000 Server3Beginning Visual Basic 6.0AuthorsAuthIDFirst NameLast NameA1JohnBrownA2MatthewBortnikerA3RickJohnsonA4PeterWrightA5JamesMoonAuthorBookBookIDAuthID1A12A22A33A43A53A1Ngoi ra mt trong nhng iu quan trng l phi bit r quan h(Relationship) gia cc table:One-to-One Relationships : trong mi quan h ny th mthng bn table A khng th lin kt vi hn 1 hng bn table B vngc li.One-to-Many Relationships : trong mi quan h ny th mthng bn table A c th lin kt vi nhiu hng bn table B.Many-to-Many Relationships : trong mi quan h ny th mthng bn table A c th lin kt vi nhiu hng bn table B v mthng bn table B cng c th lin kt vi nhiu hng bn table A.Nh ta thy trong v d trn mt cun sch c th c vit binhiu tc gi v mt tc gi cng c th vit nhiu cun sch. Do mi quan h gia Books v Authors l quan h Many to Many.Trong trng hp ny ngi ta thng dng mt table trung gian gii quyt vn (table AuthorBook).http://ebooks.vdcmedia.comHC SQL SERVER 200023 c mt database tng i hon ho ngha l thit k sao cho data chatrong database khng tha khng thiu bn cn bit thm v cc th thutNormalization. Tuy nhin trong phm vi kha hc ny chng ti khng munbn su hn v ti ny, bn c th xem thm trong cc sch dy l thuyt cs d liu.Tm li trong bi ny chng ta tm hiu v cu trc ca mt SQL Serverdatabase v mt s vn cn bit khi thit k mt database. Trong bi sauchng ta s bn v Backup v Restore database nh th no.http://ebooks.vdcmedia.comHC SQL SERVER 200024Backup And Restore SQL ServerChin Lc Phc Hi D Liu (Data Restoration Strategy)C mt iu m chng ta phi ch l hu nh bt k database no cng cnc phc hi vo mt lc no trong sut chu k sng ca n. L mt ngiDatabase Administrator bn cn phi gim ti a s ln phi phc hi d liu,lun theo di, kim tra thng xuyn pht hin cc trc trc trc khi n xyra. Phi d phng cc bin c c th xy ra v bo m rng c th nhanhchng phc hi d liu trong thi gian sm nht c th c.Cc dng bin c hay tai ha c th xy ra l:a cha data file hay Transaction Log File hay system file b mtServer b h hngNhng thm ha t nhin nh bo lt, ng t, ha honTon b server b nh cp hoc ph hyCc thit b dng backup - restore b nh cp hay h hngNhng li do v ca user nh l tay delete ton b table chnghnNhng hnh vi mang tnh ph hoi ca nhn vin nh c a vonhng thng tin sai lc.B hack (nu server c kt ni vi internet).Bn phi t hi khi cc vn trn xy ra th bn s lm g v phi lun c binphp phng c th cho tng trng hp c th. Ngoi ra bn phi xc nhthi gian ti thiu cn phc hi d liu v a server tr li hot ng bnhthng.Cc Loi Backup c th hiu cc kiu phc hi d liu khc nhau bn phi bit qua cc loibackup trong SQL ServerFull Database Backups : Copy tt c data files trong mtdatabase . Tt c nhng user data v database objects nh systemtables, indexes, user-defined tables u c backup.http://ebooks.vdcmedia.comHC SQL SERVER 200025Differential Database Backups : Copy nhng thay i trong ttc data files k t ln full backup gn nht.File or File Group Backups : Copy mt data file n hay mt filegroup.Differential File or File Group Backups : Tng t nhdifferential database backup nhng ch copy nhng thay i trongdata file n hay mt file group.Transaction Log Backups : Ghi nhn mt cch th t tt c cctransactions cha trong transaction log file k t ln transaction logbackup gn nht. Loi backup ny cho php ta phc hi d liu trngc li vo mt thi im no trong qu kh m vn mbo tnh ng nht (consistent).Trong lc backup SQL Server cng copy tt c cc hot ng ca database k chot ng xy ra trong qu trnh backup cho nn ta c th backup trong khi SQLang chy m khng cn phi ngng li.Recovery ModelsFull Recovery Model : y l model cho php phc hi d liuvi t ri ro nht. Nu mt database trong mode ny th tt ccc hot ng khng ch insert, update, delete m k c insertbng Bulk Insert, hay bcp u c log vo transaction log file.Khi c s c th ta c th phc hi li d liu ngc tr li ti mtthi im trong qu kh. Khi data file b h nu ta c th backupc transaction log file th ta c th phc hi database n thiim transaction gn nht c commited.Bulk-Logged Recovery Model : mode ny cc hot ngmang tnh hng lot nh Bulk Insert, bcp, Create Index, WriteText,UpdateText ch c log minimum vo transaction log file cho bit l cc hot ng ny c din ra m khng log ton b chitit nh trong Full Recovery Mode. Cc hot ng khc nh Insert,Update, Delete vn c log y dng cho vic phc hi sauny.Simple Recovery Model : mode ny th Transaction Log Filec truncate thng xuyn v khng cn backup. Vi mode nybn ch c th phc hi ti thi im backup gn nht m khngth phc hi ti mt thi im trong qu kh.Mun bit database ca bn ang mode no bn c th Right-click ln mtdatabase no trong SQL Server Enterprise Manager chn Properties>Options->Recoveryhttp://ebooks.vdcmedia.comHC SQL SERVER 200026Tuy nhin c th ti y bn cm thy rt kh hiu v nhng iu trnh by trn. Chng ta hy dng mt v d sau lm r vn .V d:Chng ta c mt database c p dng chin lc backup nh hnh v sau:Trong v d ny ta schedule mt Full Database Backup vo ngy Ch Nht vDifferential Backup vo cc ngy th Ba v Th Nm. Transaction Log Backupc schedule hng ngy. Vo mt ngy Th Su "en ti" mt s c xy ra l a cha data file ca database b h v l mt DBA bn c yu cu phiphc hi d liu v a database tr li hot ng bnh thng. Bn phi lmsao?Trc ht bn phi backup ngay Transaction Log File (Trong v d nyTransaction Log File c cha trong mt a khc vi a cha Data File nnkhng b h v vn cn hot ng). Ngi ta cn gi file backup trong trnghp ny l " the tail of the log" (ci ui). Nu Log File c cha trn cng mta vi Data file th bn c th s khng backup c "ci ui" v nh vy bnphi dng n log file backup gn nht. Khi backup "ci ui" ny bn cn phidng option NO_TRUNCATE bi v thng thng cc Transaction Log Backups truncate(xo) nhng phn khng cn dng n trong transaction log file, l nhng transaction c commited v c vit vo database (cn gi linactive portion of the transaction log) gim kch thc ca log file. Tuy nhinkhi backup phn ui khng c truncate m bo tnh consistent (nhtqun) ca database.http://ebooks.vdcmedia.comHC SQL SERVER 200027K n bn phi restore database t Full Backup File ca ngy Ch Nht. N slm 2 chuyn : copy data, log, index... t a backup vo Data Files v sau sln lt thc thi cc transaction trong transaction log. Lu ta phi dng optionWITH NORECOVERY trong trng hp ny (tc l option th 2 "Leavedatabase nonoperational but able to restore additional transactionlogs" trong Enterprise Manager). Ngha l cc transaction cha hon tt(incomplete transaction) s khng c roll back. Nh vy database lc nys trong tnh trng inconsistent v khng th dng c. Nu ta chnWITH RECOVERY (hay "Leave database operational. No additionaltransaction logs can be restored " trong Enterprise Manager) th ccincomplete transaction s c roll back v database trng thi consistentnhng ta khng th no restore cc transaction log backup c na.Tip theo bn phi restore Differential Backup ca ngy Th Nm. Sau lnlt restore cc Transaction Log Backup k t sau ln Differential Backup cuicng ngha l restore Transaction Log Backup ca ngy Th Nm v "Ci ui".Nh vy ta c th phc hi data tr v trng thi trc khi bin c xy ra. Qutrnh ny gi l Database Recovery.Cng xin lm r cch dng t Database Restoration v Database Recoverytrong SQL Server. Hai t ny nu dch ra ting Vit u c ngha l phc hi cs d liu nhng khi c sch ting Anh phi cn thn v n c ngha hi khcnhau.Nh trong v d trn Khi ta restore database t mt file backup ngha l ch ngin ti to li database t nhng file backup v thc thi li nhng transaction c commit nhng database c th trong trng thi inconsistent v khngs dng c. Nhng khi ni n recover ngha l ta khng ch phc hi lidata m cn bo m cho n trng thi consistent v s dng c (usable).C th bn s hi consistent l th no? Phn ny s c ni r trong bi sauv Data Integrity. Nhng cng xin dng mt v d n gin gii thch. Trongv d v th no l mt transaction bi 3 : Gi s s tin $500 c tr khiaccount A nhng li khng c cng vo account B v nu database khngc qu trnh khi phc d liu t ng (automatic recovery process) ca SQLrollback th n s trng thi inconsistent. Nu database trng thi ging nhtrc khi tr tin hoc sau khi cng $500 thnh cng vo account B th gi lconsistent.Cho nn vic backup Transaction Log File s gip cho vic recovery data ti btk thi im no trong qu kh. i vi Simple Recovery Model ta ch c threcover ti ln backup gn nht m thi.http://ebooks.vdcmedia.comHC SQL SERVER 200028Nh vy khi restore database ta c th chn option WITH RECOVERY rollback cc transaction cha c commited v database c th hot ng bnhthng nhng ta khng th restore thm backup file no na, thng option nyc chn khi restore file backup cui cng trong chui backup. Nu chn optionWITH NORECOVERY cc transaction cha c commited s khng c rollback do SQL Server s khng cho php ta s dng database nhng ta c thtip tc restore cc file backup k tip, thng option ny c chn khi sau ta cn phi restore cc file backup khc.Khng l ch c th chn mt trong hai option trn m thi hay sao? Khng honton nh vy ta c th chn mt option trung lp hn l option WITH STANDBY(tc l option 3 "Leave database read-only and able to restore additionaltransaction logs" trong Enterprise Manager). Vi option ny ta s c lun ctnh ca hai option trn : cc incomplete transaction s c roll back mbo database consistent v c th s dng c nhng ch di dng Read-onlym thi, ng thi sau ta c th tip tc restore cc file backup cn li (SQLServer s log cc transaction c roll back trong undo log file v khi ta restorebackup file k tip SQL Server s tr li trng thi no recovery t nhng g ghitrn undo file). Ngi ta dng option ny khi mun restore database tr li mtthi im no (a point in time) nhng khng r l c phi l thi im mh mun khng, cho nn h s restore tng backup file dng Standby v kimchng mt s data xem c phi l thi im m h mun restore hay khng(chng hn nh trc khi b delete hay trc khi mt transaction no cthc thi) trc khi chuyn sang Recovery option.Backup DatabaseTrong phn ny chng ta s bn v cch backup database. Nhng trc htchng ta hy lm quen vi mt s thut ng dng trong qu trnh backup vrestore. C nhng t ta s nguyn ting Anh m khng dch.Thut NgGii ThchBackupQu trnh copy ton b hay mt phn ca database, transaction log, filehay file group hnh thnh mt backup set. Backup set c cha trn backupmedia (tape or disk) bng cch s dng mt backup device (tape drive name hayphysical filename)Backup DeviceMt file vt l (nh C:\SQLBackups\Full.bak) hay tape drive c th (nh\\.\Tape0) dng record mt backup vo mt backup media.Backup FileFile cha mt backup setBackup MediaDisk hay tape c s dng cha mt backup set. Backup media c th chanhiu backup sets (v d nh t nhiu SQL Server 2000 backups v t nhiuWindows 2000 backups).Backup SetMt b backup t mt ln backup n c cha trn backup media.http://ebooks.vdcmedia.comHC SQL SERVER 200029Chng ta c th to mt backup device c nh (permanent) hay to ra mtbackup file mi cho mi ln backup. Thng thng chng ta s to mt backupdevice c nh c th dng i dng li c bit cho vic t ng ha cngvic backup. to mt backup device dng Enterprise Manager bn chnManagement->Backup ri Right-click->New Backup Device. Ngoi rabn c th dng sp_addumpdevice system stored procedure nh v d sau:USE MasterGoSp_addumpdevice 'disk' , 'FullBackupDevice' , 'E:\SQLBackups\Full.bak' backup database bn c th dng Backup Wizard hoc click ln trn databasemun backup sau Right-click->All Tasks->Backup Database... s hinra window nh hnh v sau:Sau da ty theo yu cu ca database m chn cc option thch hp. Ta cth schedule cho SQL Server backup nh k.http://ebooks.vdcmedia.comHC SQL SERVER 200030Restore DatabaseTrc khi restore database ta phi xc nh c th t file cn restore. Ccthng tin ny c SQL Server cha trong msdb database v s cho ta bitbackup device no, ai backup vo thi im no. Sau ta tin hnh restore. restore bn Right-click->All Tasks->Restore database... s thy windownh hnh v sau:Nu bn restore t mt instance khc ca SQL Server hay t mt server khcbn c chn From device option v chn backup device (file backup) tngng .Lu nu bn mun overwrite database c sn vi data c backup bn c thchn option Force restore over existing database nh hnh v sau:http://ebooks.vdcmedia.comHC SQL SERVER 200031Bn c th chn leave database operational hay nonoperational ty theo trnghp nh gii thch trn.Tm li trong bi ny chng ta tm hiu mt cht l thuyt v backup vrestore database trong SQL Server. c th hiu r hn bn cn phi thc tphay lm th c thm kinh nghim. Trong bi sau chng ta s bn v tiData Integrity ngha l lm sao m bo data cha trong database l ng tincy v khng b "lng l" nh cch ni bnh dn m ti thng hay dng.Data Integrity and Advanced Query TechniqueNi n Data Integrity l ta ni n tnh ton vn ca mt database hay ni mtcch khc l data cha trong database phi chnh xc v ng tin cy. Nu datacha trong database khng chnh xc ta ni database mt tnh ton vn (lostdata integrity). Trong bi ny chng ta s bn qua cc phng php gi chodatabase c ton vn.http://ebooks.vdcmedia.comHC SQL SERVER 200032Cc Phng Php m Bo Data IntegritySQL Server dng mt s cch m bo Data Integrity. Mt s cch nhTriggers hay Index s c bn n trong cc bi sau tuy nhin trong phm vibi ny chng ta cng ni s qua cc cch trn.Data Type : Data type cng c th m bo tnh ton vn cadata v d bn khai bo data type ca mt ct l Integer th bnkhng th a gi tr thuc dng String vo c.Not Null Definitions : Null l mt loi gi tr c bit, n khngtng ng vi zero, blank hay empty string " " m c ngha lkhng bit (unknown) hay cha c nh ngha (undefined). Khithit k database ta nn lun cn thn trong vic cho php mt ctc Null hay Not Null v vic cha Null data c th lm cho mt sng dng vn khng xa l null data k lng b "t".Default Definitions : Nu mt ct c cho mt gi tr defaultth khi bn khng a vo mt gi tr c th no th SQL Server sdng gi tr mc nh ny. Bn phi dng Default i vi Not Nulldefinition.Identity Properties : Data thuc dng ID s m bo tnh duynht ca data trong table.Constraints : y s l phn m ta o su trong bi ny.Constraint tm dch l nhng rng buc m ta dng m botnh ton vn ca data. Constraints l nhng quy lut m ta p tln mt ct m bo tnh chnh xc ca d liu c nhp vo.Rules : y l mt object mang tnh backward-compatible ch yu tng thch vi cc version trc y ca SQL Server. Rulestng ng vi CHECK Constraint trong SQL Server 2000 nhngngi ta c xu hng s dng CHECK Constraint v n chnh xchn v c th t nhiu Constraints ln mt ct trong khi ch cmt rule cho mt ct m thi. Ch rule l mt object ring v sau lin kt vi mt ct no ca table trong khi CHECK constraintl mt thuc tnh ca table nn c th c to ra vi lnh CREATETABLE.Triggers : Mt loi stored procedure c bit c thc thi mtcch t ng khi mt table c Update, Insert, hay Delete. V dta mun khi mt mn hng c bn ra th tng s hng ha trongkho phi c gim xung (-1) chng hn khi ta c th dngtrigger m bo chuyn . Triggers s c bn k trong ccbi sau.Indexes : s c bn n trong bi ni v Indexes.http://ebooks.vdcmedia.comHC SQL SERVER 200033ConstraintsConstraints l nhng thuc tnh (property) m ta p t ln mt table hay mtct trnh vic lu d liu khng chnh xc vo database (invalid data). Thtra NOT NULL hay DEFAULT cng c xem l mt dng constraint nhng chngta khng bao gm hai loi ny y m ch trnh by 4 loi constraints lPrimary Key Constraint, Unique Constraint, Foreign Key Constraint v CheckConstraint.Primary Key Constraint:Mt table thng c mt hay nhiu ct c gi tr mang tnh duy nht xc nhmt hng bt k trong table. Ta thng gi l Primary Key v c to ra khi taCreate hay Alter mt table vi Primary Key Constraint.Mt table ch c th c mt Primary Key constraint. C th c nhiu cttham gia vo vic to nn mt Primary Key, cc ct ny khng th cha Null vgi tr trong cc ct thnh vin c th trng nhau nhng gi tr ca tt c cc ctto nn Primary Key phi mang tnh duy nht.Khi mt Primary Key c to ra mt Unique Index s c t ng to ra duy tr tnh duy nht. Nu trong table cha c Clustered Index th mt Unique+ Clustered Index s c to ra.C th to ra Primary Key Constraints nh sau:CREATE TABLE Table1(Col1 INT PRIMARY KEY,Col2 VARCHAR(30))hayCREATE TABLE Table1(Col1 INT,Col2 VARCHAR(30),CONSTRAINT table_pk PRIMARY KEY (Col1))Unique ConstraintBn c th to Unique Constraint m bo gi tr ca mt ct no khngb trng lp. Tuy Unique Constraint v Primary Key Constraint u m bo tnhduy nht nhng bn nn dng Unique Constraint trong nhng trng hp sau:http://ebooks.vdcmedia.comHC SQL SERVER 200034Nu mt ct (hay mt s kt hp gia nhiu ct) khngphi l primary key. Nn nh ch c mt Primary Key Constrainttrong mt table trong khi ta c th c nhiu Unique Constraint trnmt table.Nu mt ct cho php cha Null. Unique constraint c th pt ln mt ct cha gi tr Null trong khi primary key constraint thkhng.Cch to ra Unique Constraint cng tng t nh Primary Key Constraint ch victhay ch Primary Key thnh Unique. SQL Server s t ng to ra mt nonclustered unique index khi ta to mt Unique Constraint.Foreign Key ConstraintForeign Key l mt ct hay mt s kt hp ca nhiu ct c s dng pt mi lin kt data gia hai table. Foreign key ca mt table s gi gi tr caPrimary key ca mt table khc v chng ta c th to ra nhiu Foreign keytrong mt table.Foreign key c th reference (tham chiu) vo Primary Key hay ct c UniqueConstraints. Foreign key c th cha Null. Mc d mc ch chnh ca ForeignKey Constraint l kim sot data cha trong table c Foreign key (tc tablecon) nhng thc cht n cng kim sot lun c data trong table cha Primarykey (tc table cha). V d nu ta delete data trong table cha th data trong tablecon tr nn "m ci" (orphan) v khng th reference ngc v table cha. Do Foreign Key constraint s m bo iu khng xy ra. Nu bn mun deletedata trong table cha th trc ht bn phi drop hay disable Foreign key trongtable con trc.C th to ra Foreign Key Constraints nh sau:CREATE TABLE Table1(Col1 INT PRIMARY KEY,Col2 INT REFERENCES Employees(EmployeeID))hayCREATE TABLE Table1(Col1 INT PRIMARY KEY,Col2 INT,CONSTRAINT col2_fk FOREIGN KEY (Col2)REFERENCES Employees (EmployeeID))i khi chng ta cng cn Disable Foreign Key Constraint trong trng hp:http://ebooks.vdcmedia.comHC SQL SERVER 200035Insert hay Update: Nu data insert vo s vi phm nhng rngbuc c sn (violate constraint) hay constraint ca ta ch mun pdng cho data hin thi m thi ch khng phi data s insert.Tin hnh qu trnh replicate. Nu khng disable Foreign KeyConstraint khi replicate data th c th cn tr qu trnh copy datat source table ti destination table mt cch khng cn thit.Check ConstraintCheck Constraint dng gii hn hay kim sot gi tr c php insert vomt ct. Check Constraint ging Foreign Key Constraint ch n kim sot gitr a vo mt ct nhng khc ch Foreign Key Constraint da trn gi tr table cha cho php mt gi tr c chp nhn hay khng trong khi CheckConstraint da trn mt biu thc logic (logic expression) kim tra xem mtgi tr c hp l khng. V d ta c th p t mt Check Constraint ln ctsalary ch chp nhn tin lng t $15000 n $100000/nm.Ta c th to ra nhiu Check Constraint trn mt ct. Ngoi ra ta c th to mtCheck Constraint trn nhiu ct bng cch to ra Check Constraint mc table(table level).C th to ra Check Constraint nh sau:CREATE TABLE Table1(Col1 INT PRIMARY KEY,Col2 INTCONSTRAINT limit_amount CHECK (Col2 BETWEEN 0 AND1000),Col3 VARCHAR(30))Trong v d ny ta gii hn gi tr chp nhn c ca ct Col2 t 0 n 1000.V d sau s to ra mt Check Constraint ging nh trn nhng table level:CREATE TABLE Table1(Col1 INT PRIMARY KEY,Col2 INT,Col3 VARCHAR(30),CONSTRAINT limit_amount CHECK (Col2 BETWEEN 0 AND 1000))Tng t nh Foreign Key Constraint i khi ta cng cn disable CheckConstraint trong trng hp Insert hay Update m vic kim sot tnh hp l cadata khng p dng cho data hin ti. Trng hp th hai l replication.Mun xem hay to ra Constraint bng Enterprise Manager th lm nh sau:http://ebooks.vdcmedia.comHC SQL SERVER 200036Click ln trn mt table no v chn Design Table-> Click vo icon bnphi "Manage Constraints..."Advanced Query TechniquesTrong phn ny chng ta s o su mt s cu lnh nng cao nh SELECT,INSERT...C th ni hu nh ai cng bit qua cu lnh cn bn kiu nh "SELECT * FROMTABLENAME WHERE..." nhng c th c nhiu ngi khng bit n nhng tnhcht nng cao ca n.C php y ca mt cu lnh SELECT rt phc tp tuy nhin y ch trnhby nhng nt chnh ca lnh ny m thi:SELECT select_list[ INTO new_table ]FROM table_source [ WHERE search_condition ][ GROUP BY group_by_expression ][ HAVING search_condition ][ ORDER BY order_expression [ ASC | DESC ] ]Chng ta s ln lt nghin cu tng clause (mnh ) trong cu lnh ny.SELECT ClauseSau keyword (t kha) SELECT ta s c mt danh sch cc ct m ta munselect c cch nhau bng du ",". C 3 Keywords cn nhn mnh trong phnSELECT.Distinct : Khi c keyword ny vo th s cho kt qu cc ct khngtrng nhau. V d trong Orders table ca Norwind database(database mu di km vi SQL Server) cha gi tr trng lp(duplicate value) trong ct ShipCity. Nu ta mun select mt danhsch ShipCity trong mi city ch xut hin mt ln trong kt qunhn c ta dng nh sau:SELECT DISTINCT ShipCity, ShipRegionFROM OrdersORDER BY ShipCityTop n : Nu ta mun select n hng u tin m thi ta c thdng Top keyword. Nu c thm ORDER BY th kt qu s corder trc sau mi select. Chng ta cng c th select s hnghttp://ebooks.vdcmedia.comHC SQL SERVER 200037da trn phn trm bng cch thm Keyword Percent vo. V dsau s select 10 hng u tin theo th t:SELECT DISTINCT TOP 10 ShipCity, ShipRegionFROM OrdersORDER BY ShipCityAs : i khi chng ta mun cho SELECT statement d c hn mtcht ta c th dng mt alias (tc l t thay th hay t vit tt) vikeyword As hay khng c keyword As: table_name As table_aliashay table_name table_alias. V d:USE pubsSELECT p.pub_id, p.pub_name AS PubNameFROM publishers AS pNgoi ra trong Select list ta c th select di dng mt expression nh sau:SELECT FirstName + ' ' + LastName AS "Employee Name",IDENTITYCOL AS "Employee ID",HomePhone,RegionFROM Northwind.dbo.EmployeesORDER BY LastName, FirstName ASCTrong v d trn ta select ct "Employee Name" l sn phm ghp li ca ctFirstName v LastName c cch nhau bng mt khong trng. Mt gi trthuc loi identity lm ct "Employee ID". Kt qu s c sp theo th t tnh ti ln (ASC) (cn DESC l t ln ti nh) trong ct LastName c sptrc ri mi ti ct FirstName.The INTO ClauseINTO Clause cho php ta select data t mt hay nhiu table sau kt qu sc insert vo mt table mi. Table ny c to ra do kt qu ca cu lnhSELECT INTO. V d:SELECT FirstName, LastNameINTO EmployeeNamesFROM EmployersCu lnh tn s to ra mt table mi c tn l EmployeeNames vi 2 ct lFirstName v LastName sau kt qu select c t table Employers s cinsert vo table mi ny. Nu table EmployeeNames tn ti SQL Server s boli. Cu lnh ny thng hay c s dng select mt lng data ln tnhiu table khc nhau vo mt table mi (thng dng cho mc ch tm thi(temporary table)) m khi phi thc thi cu lnh Insert nhiu ln.http://ebooks.vdcmedia.comHC SQL SERVER 200038Mt cch khc cng select data t mt hay nhiu table v insert vo mt tablekhc l dng "Insert Into...Select...". Nhng cu lnh ny khng to ra mttable mi. Ngha l ta table phi tn ti trc. V d:INSERT INTO EmployeeNamesSELECT FirstName, LastNameFROM EmployersCh l khng c ch "Value" trong cu Insert ny.The GROUP BY and HAVING ClausesGROUP BY dng to ra cc gi tr tng (aggregate values) cho tng hngtrong kt qu select c. Ch c mt hng cho tng gi tr ring bit (distinct)ca tng ct. Cc ct c select u phi nm trong GROUP BY Clause. Hyxem v d phc tp sau:SELECT OrdD1.OrderID AS OrderID,SUM(OrdD1.Quantity) AS "Units Sold",SUM(OrdD1.UnitPrice * OrdD1.Quantity) AS RevenueFROM [Order Details] AS OrdD1WHERE OrdD1.OrderID in (SELECT DISTINCT OrdD2.OrderIDFROM [Order Details] AS OrdD2WHERE OrdD2.UnitPrice > $100)GROUP BY OrdD1.OrderIDHAVING SUM(OrdD1.Quantity) > 100Trong v d trn u tin ta select nhng order ring bit (distinct) t OrderDetails table vi gi > 100. Sau tip tc select OrderID, "Units Sold", Revenuet kt qu trn trong "Units Sold" v Revenue l nhng aggregate columns(cho gi tr tng mt ct ca nhng hng c cng OrderID). HAVING Clauseng vai tr nh mt filter dng lc li cc gi tr cn select m thi. HAVINGClause thng i chung vi GROUP BY mc d c th xut hin ring l.UNIONUninon keyword c nhim v ghp ni kt qu ca 2 hay nhiu queries li thnhmt kt qu.V d:Gi s c table1(ColumnA varchar(10), ColumnB int) v table2(ColumnCvarchar(10), ColumnD int). Ta mun select data t table1 v ghp vi data ttable2 to thnh mt kt qu duy nht ta lm nh sau:SELECT * FROM Table1UNION ALLSELECT * FROM Table2http://ebooks.vdcmedia.comHC SQL SERVER 200039Nu khng c keyword ALL th nhng hng ging nhau t 2 table s ch xuthin mt ln trong kt qu. Cn khi dng ALL th cc hng trong 2 table u ctrong kt qu bt chp vic lp li.Khi Dng Union phi ch hai chuyn: s ct select 2 queries phi bng nhauv data type ca cc ct tng ng phi compatible (tng thch).Using JOINSTrong phn ny chng ta s tm hiu v cc loi Join trong SQL Server. Bngcch s dng JOIN bn c th select data t nhiu table da trn mi quan hlogic gia cc table (logical relationships). C th tm tt cc loi Join thngdng bng cc hnh sau:Th t t tri sang phi: Inner Join, Left Outer Join, Right Outer Join, Full OuterJoinInner JoinsDng Inner Join select data t 2 hay nhiu tables trong gi tr ca cc ctc join phi xut hin c 2 tables tc l phn gch cho trn hnh. V d:SELECT t.Title, p.Pub_nameFROM Publishers AS p INNER JOIN Titles AS tON p.Pub_id = t.Pub_idORDER BY Title ASCLeft Outer JoinsDng Left Outer Join select data t 2 hay nhiu tables trong tt c ct bntable th nht v khng tn ti bn table th hai s c select cng vi cc gitr ca cc ct c inner join. S ct select c s bng vi s ct ca tableth nht. Tc l phn t mu trn hnh. V d:USE PubsSELECT a.Au_fname, a.Au_lname, p.Pub_nameFROM Authors a LEFT OUTER JOIN Publishers pON a.City = p.CityORDER BY p.Pub_name ASC, a.Au_lname ASC, a.Au_fname ASChttp://ebooks.vdcmedia.comHC SQL SERVER 200040Right Outer JoinsDng Right Outer Join select data t 2 hay nhiu tables trong tt c ctbn table th hai v khng tn ti bn table th nht s c select cng vicc gi tr ca cc ct c inner join. S ct select c s bng vi s ct catable th hai. Tc l phn t mu trn hnh. V d:USE PubsSELECT a.Au_fname, a.Au_lname, p.Pub_nameFROM Authors a RIGHT OUTER JOIN Publishers pON a.City = p.CityORDER BY p.Pub_name ASC, a.Au_lname ASC, a.Au_fname ASCFull Outer JoinsDng Full Outer Join select data t 2 hay nhiu tables trong tt c ct bntable th nht v th hai u c chn cc gi tr bn hai table bng nhau thch ly mt ln. Tc l phn t mu trn hnh. V d:USE PubsSELECT a.Au_fname, a.Au_lname, p.Pub_nameFROM Authors a FULL OUTER JOIN Publishers pON a.City = p.CityORDER BY p.Pub_name ASC, a.Au_lname ASC, a.Au_fname ASCCross JoinsDng Cross Join ghp data t hai table trong s hng thu c bng vi shng ca table th nht nhn vi s hng ca table th hai. V d:USE pubsSELECT au_fname, au_lname, pub_nameFROM authors CROSS JOIN publishersWHERE authors.city = publishers.cityORDER BY au_lname DESC l trong cu lnh ny khng c keyword "On".Mun hiu r hn v cc loi join bn cho chy th trn SQL Server v lm phnbi tp s 1.Tm li trong bi ny chng ta tm hiu data integrity trong SQL Server bngcch dng cc loi Constraint. Ngoi ra Chng ta cng bit qua v mt s kthut query nng cao. Sau bi hc ny cc bn cn lm bi tp s 1 h thngha li kin thc hc t bi 1 n bi 5 trc khi bn hc tip bi s 6. Khilm bi tp nh phi lm theo th t v tun th theo cc yu cu ca bi tpt ra. Khng nn b qua bc no.http://ebooks.vdcmedia.comHC SQL SERVER 200041Stored Procedure and Advanced T-SQLTrong bi ny chng ta s tm hiu mt s cch import v export data trong SQLServer. Sau s bn qua cc loi Stored Procedure v Cursor.S dng bcp v BULK INSERT import databcp l mt command prompt dng import hay export data t mt data file(Text file hay Excel File) vo SQL Server hay ngc li. Thng khi mun chuynmt s lng ln data t mt database system khc nh Oracle, DB2...sang SQLServer trc ht ta s export data ra mt text file sau import vo SQL Serverdng bcp command. Mt trng hp thng dng hn l ta export data t SQLServer sang mt Microsoft Excel file v Excel file ny c th l input cho mtprogram hay mt database system khc.Chng ta cng c th chuyn data vo SQL Server dng cu lnh BULKINSERT. Tuy nhin BULK INSERT ch c th import data vo trong SQLServer ch khng th export data ra mt data file nh bcp. c th insert data vo SQL Server Database, data file phi c dng bngngha l c cu trc hng v ct. Ch khi data c bulk copy (copy hng lotdng bcp hay BULK INSERT) vo mt table trong SQL Server th table phitn ti v data c cng thm vo (append). Ngc li khi export data ra mtdata file th mt file mi s c to ra hoc data file s b overwrite nu n tnti.C php y ca lnh bcp c th xem trong SQL Server Books Online. ych trnh by mt s v d n gin v cch s dng bcp command v BULKINSERT.V d 1: Gi s bn mun export data t table Orders trong PracticeDB (y ldatabase c to ra trong bi tp s 1 ) ra mt text file trong cc ct cphn cch bng du ";". Bn c th lm nh sau: m DOS command prompt vnh vo dng lnh sau:bcp PracticeDB..Orders out c:\Orders.txt -c T t;Trong v d trn ta mun bulk copy table Orders ra mt text file trong :http://ebooks.vdcmedia.comHC SQL SERVER 200042out: copy data t table hay view ra mt data file (c:\Orders.txt). Ngc li ta cth dng switch in import data t text file vo SQL Server.-c: bulk copy dng kiu d liu Character (Char) (nu khng ch r th SQLServer s dng "TAB" character (\t) phn nh cc ct v dng new linecharacter (\n) phn nh cc hng nh cc gi tr default).-t;: du ";" i sau switch "t" cho bit ta mun dng ";" phn nh cc ct(nu khng s dng gi tr mc nh nh trn)-T: dng (NT) Trust connection kt ni vi database. Ngha l nu user authenticated (cho php) vo c Windows system th ng nhin c sdng SQL Server m khng cn dng thm username v password no khc.V d 2: Thay v copy ton b table ta c th dng query select mt phndata v export ra text file nh sau:bcp "Select * From practiceDB..Orders" queryout c:\Orders.txt -c-SVinhtai -Usa -PabcTrong v d ny ta select ton b data trong Orders table ra mt text file dngquery v SQL Server authentication.queryout : cho bit y l mt query ch khng phi l table.-S : tn ca SQL Server (hay tn ca mt Instance)-U : SQL user name dng log on-P : password dng log on.V d 3 : dng BULK INSERT bulk copy data t text file vo SQL Serverdatabase. M Query Analyser (BULK INSERT l mt T-SQL command ch khngphi l mt command prompt utility) v nh vo cc dng sau :BULK INSERT PracticeDB..Orders FROM 'c:\Orders.txt ' WITH(DATAFILETYPE = 'CHAR')Trong v d trn DATAFILETYPE= 'CHAR' cho bit data c cha dng Chardata type. N mun dng data type dng unicode th dng 'WIDECHAR'Ch : Cc switch trong bcp command l case-sensitive. Ngha l ch hoa vch thng s c ngha khc nhau.http://ebooks.vdcmedia.comHC SQL SERVER 200043Distributed Queriesi khi chng ta mun select data t nhng database system khc nh MSAccess, Oracle, DB2... hay thm ch t mt SQL Server khc ta cn phi dngdistributed query. SQL Server s dng k thut OLEDB v cc API chuyn ccquery ny ti cc database system khc. C 2 cch truy cp vo cc databasesystem khc l dng LINKED SERVER v Ad Hoc Computer Name.Linked Server:Linked Server l mt server o c dng truy cp vo cc database systemkhc. Mt khi setup th ta c th query data dng four-part name :linked_server_name.catalog.schema.object_name . Trong catalog thngtng ng vi database name, Schema tng ng vi database owner vobject_name tng ng vi table hay view.V d: Gi s ta setup mt Linked Server vo Access database "PracticeDB.mdb"trong cc table u tng t nh PracticeDB database trong SQL Server(c to ra trong phn bi tp s 1).M Enterprise Manager -> Chn node Security ca local server ->Right-Click ln node Linked Server chn New Linked Server. Sau nhpvo tn ca Linked Server LinkedPracticeDB, trong phn Provider Name chnMicrosoft Jet 4.0 OLEDB Provider. Trong phn Data Source nhp vo v tr caAccess database (C:\PracticeDB.mdb) v click OK.Ta s c Linked Server tn LinkedPracticeDB xut hin di phn Security/LinkedServer. Gi s ta mun select data t Linked Server ny ta c th dng QueryAnalyser nh sau:Select * from LinkedPracticeDB...CustomersTrong v d trn ta dng tn ca Linked Server v theo sau l ba chm (v truy cp vo database ta phi dng four-part name nhng trong trng hp nyta dng default value nn khng cn cho bit tn ca Catalog v Schema nhngphi dng du chm phn bit tng phn).Ngoi cch trn ta c th dng pass-through query vi OPENQUERY functionnh sau:Select * from OPENQUERY(LinkedPracticeDB,'Select * fromCustomers')http://ebooks.vdcmedia.comHC SQL SERVER 200044Trong v d trn ta thy function OPENQUERY s tr v mt data set v c thnm sau keyword FROM nh mt table. Khi dng OPENQUERY function ta cncho bit tn ca Linked Server v query m ta mun thc hin.Lu : function trong SQL Server c dng tng t nh l stored procedure.Ad Hoc Computer NameNgoi cch dng Linked Server nh trnh by trn ta c th dng ad hoccomputer name (ad hoc ngha l lm thi, tm thi). Ngha l i vi nhngdatabase system m ta thng xuyn query th dng Linked Server cn i vinhng query lu lu mi dng n th ta c th select data bngOPENROWSET hay OPENDATASOURCE functionsV d: ta cng s select data t Access database nh trn dng OPENROWSETSelect * fromOPENROWSET('Microsoft.jet.oledb.4.0','C:\PracticeDB.mdb'; 'admin'; '',Customers)Trong v d trn khi dng OPENROWSET ta cn phi a vo tt c nhng thngtin cn thit connect vo database nh tn ca Provider, v tr ca file,username, password (trng hp ny khng c password) v tn ca table mta mun select. Mi ln ta thc thi cu lnh trn SQL Server u kim trasecurity trong khi nu dng Linked Server th ch kim ta mt ln m thi.OPENROWSET tng t nh OPENQUERY ch n tr v mt rowset v c tht vo v tr ca mt table trong cu lnh query.Ngoi cch dng trn ta cng c th dng OPENDATASOURCE query nhsau:Select * from OPENDATASOURCE('Microsoft.jet.oledb.4.0','Data Source = C:\PracticeDB.mdb; User ID= Admin; Password = ')...CustomersTrong v d trn ta thy OPENDATASOURCE tr v mt phn ca four-part name(ngha l tng ng vi tn ca Linked Server) cho nn ta phi dng thm badu chm.CursorsNu gii thch mt cch ngn gn th cursor tng t nh recordset hay datasettrong programming. Ngha l ta select mt s data vo memory sau c thln lt lm vic vi tng record bng cch Move Next...http://ebooks.vdcmedia.comHC SQL SERVER 200045C 3 loi cursors l Transact- SQL Cursors, API Cursors v Client Cursors. Trong Transact-SQL v API thuc loi Server Cursors ngha l cursors c load lnv lm vic bn pha server. Trong khun kh bi hc ny ta ch nghin cuTransact-SQL cursors.Transact-SQL cursors c to ra trn server bng cc cu lnh Transact-SQL vch yu c dng trong stored procedures v triggers. Trc ht hy xem quamt v d v cursor:DECLARE @au_lname varchar(40), @au_fname varchar(20)DECLARE Employee_Cursor CURSOR FORSELECT LastName, FirstName FROM Northwind.dbo.EmployeesOPEN Employee_CursorFETCH NEXT FROM Employee_Cursor INTO @au_lname, @au_fnameWHILE @@FETCH_STATUS = 0BEGINPRINT 'Author:' + @au_fname + ' ' + @au_lnameFETCH NEXT FROM Employee_Cursor INTO @au_lname,@au_fnameENDCLOSE Employee_CursorDEALLOCATE Employee_CursorTrong v d trn ta s select LastName v FirstName t Employees table caNorthwind database v load vo Employee_Cursor sau ln lt in tn ca ccemployee ra mn hnh. lm vic vi mt cursor ta cn theo cc bc sau:1. Dng cu lnh DECLARE CURSOR khai bo mt cursor. Khi khaibo ta cng phi cho bit cu lnh SELECTs c thc hin lydata.2. Dng cu lnh OPEN a data ln memory (populate data). ychnh l lc thc hin cu lnh SELECT vn c khai bo trn.3. Dng cu lnh FETCH ly tng hng data t record set. C thl ta phi gi cu lnh FETCH nhiu ln. FETCH tng t nh lnhMove trong ADO recordset ch n c th di chuyn ti lui bngcu lnh FETCH FIRST, FETCH NEXT, FETCH PRIOR, FETCH LAST,FETCH ABSOLUTE n, FETCH RELATIVE n nhng khc ch l nly data b vo trong variable (FETCH...FROM...INTOvariable_name). Thng thng ta FETCH data trc sau loopcho ti record cui ca Cursor bng vng lp WHILE bng cchkim tra global variable @@FETCH_STATUS (=0 ngha l thnhcng).http://ebooks.vdcmedia.comHC SQL SERVER 2000464. Khi ta ving thm tng record ta c th UPDATE hay DELETE tytheo nhu cu (trong th d ny ch dng lnh PRINT)5. Dng cu lnh CLOSE ng cursor. Mt s ti nguyn (memoryresource) s c gii phng nhng cursor vn cn c khai bov c th OPEN tr li.6. Dng cu lnh DEALLOCATE phng thch hon ton cc tinguyn dnh cho cursor (k c tn ca cursor).Lu l trong v d trn trc khi dng Cursor ta cng declare mt s variable(@au_fname v @au_lname) cha cc gi tr ly c t cursor. Bn c thdng Query Analyzer chy th v d trn.Stored ProceduresTrong nhng bi hc trc y khi dng Query Analyzer chng ta c th t tnv save cc nhm cu lnh SQL vo mt file di dng script c th s dngtr li sau ny. Tuy nhin thay v save vo text file ta c th save vo trong SQLServer di dng Stored Procedure. Stored Procedure l mt nhm culnh Transact-SQL c compiled (bin dch) v cha trong SQLServer di mt tn no v c x l nh mt n v (ch khngphi nhiu cu SQL ring l).u im Ca Stored ProcedureStored Procedure c mt s u im chnh nh sau:Performance : Khi thc thi mt cu lnh SQL th SQL Server phikim tra permission xem user gi cu lnh c c php thchin cu lnh hay khng ng thi kim tra c php ri mi to ramt execute plan v thc thi. Nu c nhiu cu lnh nh vy giqua network c th lm gim i tc lm vic ca server. SQLServer s lm vic hiu qu hn nu dng stored procedure vngi gi ch gi mt cu lnh n v SQL Server ch kim tra mtln sau to ra mt execute plan v thc thi. Nu storedprocedure c gi nhiu ln th execute plan c th c s dngli nn s lm vic nhanh hn. Ngoi ra c php ca cc cu lnhSQL c SQL Sever kim tra trc khi save nn n khng cnkim li khi thc thi.Programming Framework : Mt khi stored procedure c tora n c th c s dng li. iu ny s lm cho vic bo tr(maintainability) d dng hn do vic tch ri gia business rules(tc l nhng logic th hin bn trong stored procedure) vdatabase. V d nu c mt s thay i no v mt logic th tach vic thay i code bn trong stored procedure m thi. Nhnghttp://ebooks.vdcmedia.comHC SQL SERVER 200047ng dng dng stored procedure ny c th s khng cn phithay i m vn tng thch vi business rule mi. Cng ging nhcc ngn ng lp trnh khc stored procedure cho php ta a vocc input parameters (tham s) v tr v cc output parametersng thi n cng c kh nng gi cc stored procedure khc.Security : Gi s chng ta mun gii hn vic truy xut d liutrc tip ca mt user no vo mt s tables, ta c th vit mtstored procedure truy xut d liu v ch cho php user cs dng stored procedure vit sn m thi ch khng th"ng" n cc tables mt cch trc tip. Ngoi ra storedprocedure c th c encrypt (m ha) tng cng tnh bomt.Cc Loi Stored ProcedureStored procedure c th c chia thnh 5 nhm nh sau:1. System Stored Prcedure : L nhng stored procedure chatrong Master database v thng bt u bng tip u ng sp_ .Cc stored procedure ny thuc loi built-in v ch yu dng trongvic qun l database (administration) v security. V d bn c thkim tra tt c cc processes ang c s dng bi userDomainName\Administrators bn c th dng sp_who@loginame='DomainName\Administrators' . C hng trmsystem stored procedure trong SQL Server. Bn c th xem chi tittrong SQL Server Books Online.2. Local Stored Procedure : y l loi thng dng nht. Chngc cha trong user database v thng c vit thc hinmt cng vic no . Thng thng ngi ta ni n storedprocedure l ni n loi ny. Local stored procedure thng cvit bi DBA hoc programmer. Chng ta s bn v cch to storedprcedure loi ny trong phn k tip.3. Temporary Stored Procedure : L nhng stored proceduretng t nh local stored procedure nhng ch tn ti cho n khiconnection to ra chng b ng li hoc SQL Server shutdown.Cc stored procedure ny c to ra trn TempDB ca SQL Servernn chng s b delete khi connection to ra chng b ct t haykhi SQL Server down. Temporary stored procedure c chialm 3 loi : local (bt u bng #), global (bt u bng ##) vstored procedure c to ra trc tip trn TempDB. Loi localch c s dng bi connection to ra chng v b xa khidisconnect, cn loi global c th c s dng bi bt kconnection no. Permission cho loi global l dnh cho mi ngihttp://ebooks.vdcmedia.comHC SQL SERVER 200048(public) v khng th thay i. Loi stored procedure c to trctip trn TempDB khc vi 2 loi trn ch ta c th setpermission, chng tn ti k c sau khi connection to rachng b ct t v ch bin mt khi SQL Server shut down.4. Extended Stored Procedure : y l mt loi stored procedures dng mt chng trnh ngoi vi (external program) vn ccompiled thnh mt DLL m rng chc nng hot ng ca SQLServer. Loi ny thng bt u bng tip u ng xp_ .V d,xp_sendmail dng gi mail cho mt ngi no hayxp_cmdshell dng chy mt DOS command... V dxp_cmdshell 'dir c:\' . Nhiu loi extend stored procedurec xem nh system stored procedure v ngc li.5. Remote Stored Procedure : Nhng stored procedure gi storedprocedure server khc.Vit Stored ProcedureTn v nhng thng tin v Stored Procedure khi c to ra s cha trongSysObjects table cn phn text ca n cha trong SysComments table. V StoredProcedure cng c xem nh mt object nn ta cng c th dng cc lnh nhCREATE, ALTER, DROP to mi, thay i hay xa b mt stored procedure.Chng ta hy xem mt v d sau v Stored Procedure: to mt storedprocedure bn c th dng Enterprise Manager click ln trn StoredProcedure -> New Stored Procedure.... Trong v d ny ta s to ra mtstored procedure insert mt new order vo Orders table trong Practice DB. insert mt order vo database ta cn a vo mt s input nh OrderID,ProductName (order mn hng no) v CustomerName (ai order). Sau ta trv kt qu cho bit vic insert c thnh cng hay khng. Result = 0 l insertthnh cng.CREATE PROCEDURE AddNewOrder@OrderID smallint,@ProductName varchar(50),@CustomerName varchar(50),@Result smallint=1 OutputASDECLARE @CustomerID smallintBEGIN TRANSACTIONIf not Exists(SELECT CustomerID FROM Customers WHERE[Name]=@CustomerName)--This is a new customer. Insert this customer to the databaseBEGINSET @CustomerID= (SELECT Max(CustomerID) FROMCustomers)SET @CustomerID=@CustomerID+1http://ebooks.vdcmedia.comHC SQL SERVER 200049INSERT INTO CustomersVALUES(@CustomerID,@CustomerName)If Exists(SELECT OrderID FROM [Orders] WHEREOrderID=@OrderID)--This order exists and could not be added anymore so Roll backBEGINSELECT @Result=1ROLLBACK TRANSACTIONENDElse--This is a new order insert it nowBEGININSERT INTO[Orders](OrderID,ProductName,CustomerID)VALUES(@OrderID,@ProductName,@CustomerID)SELECT @Result=0COMMIT TRANSACTIONENDENDElse--The customer exists in DB go ahead and insert the orderBEGINIf Exists(SELECT OrderID FROM [Orders] WHEREOrderID=@OrderID)--This order exists and could not be added anymore so Roll backBEGINSELECT @Result=1ROLLBACK TRANSACTIONENDElse--This is a new order insert it nowBEGININSERT INTO[Orders](OrderID,ProductName,CustomerID)VALUES(@OrderID,@ProductName,@CustomerID)SELECT @Result=0COMMIT TRANSACTIONENDENDPrint @ResultReturn to ra mt stored procedure ta dng lnh CREATE PROCEDURE theo sau ltn ca n (nu l temporary stored procedure th thm du # trc tn caprocedure. Nu mun encrypt th dng WITH ENCRYPTION trc ch AS) v ccinput hoc ouput parameters. Nu l output th thm keyword OUTPUT ng sauparameter. Ta c th cho gi tr default cng lc vi khai bo data type caparameter. K t sau ch AS l phn body ca stored procedure.Trong v d trn trc ht ta khai bo mt bin @CustomerID sau bt umt transaction bng BEGIN TRANSACTION (ton b cng vic insert ny cthc hin trong mt Transaction ngha l hoc l insert thnh cng hoc lhttp://ebooks.vdcmedia.comHC SQL SERVER 200050khng lm g c- all or nothing). Trc ht ta kim tra xem ngi khch hng lngi mi hay c. Nu l ngi mi th ta "tin tay" insert vo Customers tablelun cn nu khng th ch insert vo Orders table m thi. Nu l ngicustomer mi ta ly CustomerID ln nht t Customers table b vo bin@CustomerID v sau tng ln mt n v dng cho vic Insert Customer dng k tip.Sau khi insert ngi customer mi ta tip tc cng vic bng vic kim tra xemOrder mun insert c tn ti cha (v nu order tn ti th khi insert SQLServer s bo li do OrderID l Primary key). Nu nh order trn v l do no c trong DB th ta roll back v tr kt qu =1 cn nu khng th ta insert mtorder mi vo v commit transaction vi kt qu tr v =0.Tng t nh vy nu ngi customer tn ti (sau ch else u tin) th tach vic insert order ging nh trn. Trong mi trng hp k trn ta u in rakt qu v return.V d trn y ch mang tnh hc hi cn trn thc t database c th phc tphn nhiu nn vic vit stored procedure i hi kin thc vng chc v SQL vk nng v programming.Mun hiu r hn v bi hc ny bn cn lm bi tp s 2.Tm li trong bi ny chng ta tm hiu mt s k thut import v exportdata . ng thi bit qua cc cch select data t cc database system khc dngdistributed query. Nhng quan trng nht v thng dng nht l cc storedprocedures. Bn cn hiu r vai tr ca stored procedure v bit cch to rachng.V kin thc v database ni chung v SQL Server ni ring kh rng nn trongkhun kh mt bi hc chng ti khng th trnh by cn k tng chi tit v ikhi c hi dn p cho nn bn cn c i c li nhiu ln nm c chnhv phi xem thm sch (nu khng c sch th phi xem thm SQL BooksOnline). Sau bi hc ny cc bn cn lm bi tp s 2 h thng ha li kinthc hc. Khi lm bi tp nn lm theo th t v tun th theo cc yu cuca bi tp t ra. Khng nn b qua bc no.http://ebooks.vdcmedia.comHC SQL SERVER 200051Triggers And ViewsTrong bi ny chng ta s tm hiu ng dng ca mt loi stored procedure cbit gi l Triggers v dng Views th hin data trong mt hay nhiu tablenh th no.TriggersTrigger l mt loi stored procedure c bit c execute (thc thi) mt ccht ng khi c mt data modification event xy ra nh Update, Insert hayDelete. Trigger c dng m bo Data Integrity hay thc hin cc businessrules no .Khi no ta cn s dng Trigger:Ta ch s dng trigger khi m cc bin php bo m dataintergrity khc nh Constraints khng th tha mn yu cu cang dng. Nn nh Constraint thuc loi Declarative DataIntegrity cho nn s kim tra data trc khi cho php nhpvo table trong khi Trigger thuc loi Procedural Data Integritynn vic insert, update, delete xy ra ri mi kch hot trigger.Chnh v vy m ta cn cn nhc trc khi quyt nh dng loi notrong vic m bo Data Integrity.Khi mt database c denormalized (ngc li qu trnhnormalization, l mt qu trnh thit k database schema sao chodatabase cha data khng tha khng thiu) s c mt s datatha (redundant ) c cha trong nhiu tables. Ngha l s cmt s data c cha cng mt lc hai hay nhiu nikhc nhau. Khi m bo tnh chnh xc th khi data cupdate mt table ny th cng phi c update mt cch tng cc table cn li bng cch dng Trigger.V d: ta c table Item trong c field Barcode dng xc nhmt mt hng no . Item table c vai tr nh mt cun catalogcha nhng thng tin cn thit m t tng mt hng. Ta c mttable khc l Stock dng phn nh mn hng c thc trong khonh c nhp v ny no c cung cp bi i l no, s lnghttp://ebooks.vdcmedia.comHC SQL SERVER 200052bao nhiu (tc l nhng thng tin v mn hng m khng th chatrong Item table c)...table ny cng c field Barcode xcnh mn hng trong kho. Nh vy thng tin v Barcode ccha hai ni khc nhau do ta cn dng trigger m bo lBarcode hai ni lun c synchonize (ng b).i khi ta c nhu cu thay i dy chuyn (cascade) ta c th dngTrigger bo m chuyn . Ngha l khi c s thay i no table ny th mt s table khc cng c thay i theo mbo tnh chnh xc. V d nh khi mt mn hng c bn i th slng hng trong table Item gim i mt mn ng thi tng shng trong kho (Stock table) cng phi gim theo mt cch tng. Nh vy ta c th to mt trigger trn Item table mi khimt mn c bn i th trigger s c kch hot v gim tng shng trong Stock table.c im ca Trigger:Mt trigger c th lm nhiu cng vic (actions) khc nhau v cth c kch hot bi nhiu hn mt event. V d ta c th vitmt trigger c kch hot bi bt k event no nh Update, Inserthay Delete v bn trong trigger ta s vit code gii quyt chotng trng hp.Trigger khng th c to ra trn temporary hay system table.Trigger ch c th c kch hot mt cch t ng bi mt trongcc event Insert, Update, Delete m khng th chy manuallyc.C th p dng trigger cho View.Khi mt trigger c kch hot th data mi va c insert haymi va c thay i s c cha trong Inserted table cndata mi va c delete c cha trong Deleted table. y l 2table tm ch cha trn memory v ch c gi tr bn trong triggerm thi (ngha l ch nhn thy v c query trong trigger mthi). Ta c th dng thng tin trong 2 table ny so snh datac v mi hoc kim tra xem data mi va thay i c hp l trckhi commit hay roll back. (Xem thm v d bn di)C 2 loi triggers (class) : INSTEAD OF v AFTER. Loi INSTEADOF s b qua (bybass) action kch hot trigger m thay vo s thc hin cc dng lnh SQL bn trong Trigger. V d ta c mthttp://ebooks.vdcmedia.comHC SQL SERVER 200053Update trigger trn mt table vi cu INSTEAD OF th khi tablec update thay v update SQL Server s thc hin cc lnh c vit sn bn trong trigger. Ngc li loi AFTER (loi defaulttng ng vi keyword FOR) s thc hin cc cu lnh bntrong trigger sau khi cc action to nn trigger xy ra ri.To Mt Trigger Nh Th No?C php cn bn to ra mt trigger c dng nh sau:CREATE TRIGGER trigger_nameON table_name or view_nameFOR trigger_class and trigger_type(s)AS Transact-SQL statementsNh vy khi to ra mt trigger ta phi ch r l to ra trigger trn table no vc trigger khi no (insert, update hay delete. Sau ch AS l cc cu lnh SQLx l cng vic.Ta hy nghin cu mt ng dng thc tin sau. Gi s ta vit mt applicationcho php user c th Insert, Update v Delete nhng thng tin nm trongdatabase. User ny thng l nhng ngi khng thng tho lm v computerm chng ti thng gi a l "b tm". Vo mt ngy p tri, "b tm" mtmy ti xanh n cu cu ta v l tay "delete" nhng thng tin kh quantrng v hy vng ta c th phc hi d liu dm. Nu chng ta khng phng xatrc khi vit application th coi nh cng v phng cu cha v data honton b delete.Nhng nu bn l mt "guru" bn s gt g "chuyn ny kh lm!" nhng sau bn ch tn vi pht ng h rollback. Mun lm c chuyn ny chngta phi dng mt "chiu" gi l Audit (kim tra hay gim st). Tc l ngoi cctable chnh ta s thm cc table ph gi l Audit tables. Bt k hot ng nong chm vo mt s table quan trng trong database ta u ghi nhn votrong Audit table. V d khi user update hay delete mt record trong table no th trc khi update hay delete ta s m thm di chuyn record sang Audittable ri mi update hay delete table chnh. Nh vy nu c chuyn g xy ra tac th d dng rollback (tr record v ch c).http://ebooks.vdcmedia.comHC SQL SERVER 200054V d:Ta c table Orders trong PracticeDB. audit cc hot ng din ra trn tableny ta to ra mt audit table vi tn Aud_Orders vi cc column ging y htvi Orders table. Ngoi ra ta thm vo 2 columns :Audit_Type : vi cc gi tr c th l 'I','U','D' ghi nhnrecord c Insert, Update hay DeleteDate_Time_Stamp : Data Type thuc loi DateTime dng ghi nhn thi im xy ra s thay i, c vai tr nh mt con du.(Nu trong mi trng nhiu user th ta thm mt column UserID ghi nhnuser no thay i).Sau ta s to ra 3 trigger dng cho vic audit nh sau:--Insert TriggerCREATE TRIGGER [AuditInsertOrders]ON [dbo].[Orders]FOR InsertASinsert into aud_orders select *,'I',getdate() From inserted--Update TriggerCREATE TRIGGER [AuditUpdateOrders]ON [dbo].[Orders]for UPDATEASinsert into aud_orders select *,'U',Getdate() from deleted--Delete TriggerCREATE TRIGGER [AuditDeleteOrders]ON [dbo].[Orders]FOR DELETEASinsert into aud_orders select *,'D',getdate() From deletedTrong v d trn khi user insert mt record th record mi va c insert snm trong inserted table nh trnh by phn trn. Do ta s select ttc cc column trong inserted table cng thm Audit Type "I" v dng hmGetDate() trong SQL Server ly system date time dng cho Date_Time_Stampcolumn, sau insert vo Aud_Orders table. Tng t vi trng hp Update vDelete, record c update hay delete nm trong deleted table.Nh vy tr li trng hp th d trn nu "b tm" yu cu ta c th vo tmkim trong audit table phc hi li record. Ngoi ra ta c th dng table ny tm ra th phm xo hay sa cha data khi cn thit.http://ebooks.vdcmedia.comHC SQL SERVER 200055 to ra hay xem mt trigger bng Enterprise Manager bn lm nh sau:Right-Click ln table m bn mun to trigger->All Tasks-> ManageTriggers.Lu : i Khi ta ch mun trigger thc s hot ng khi mt hay vi columnno c Update ch khng phi bt k column no. Khi ta c th dnghm Update(Column_Name) kim tra xem column no c b update haykhng.V d:To mt trigger cho Customer table. Bn trong Trigger (sau ch AS) ta c thkim tra xem nu column First_Name hay Last_Name b thay i th mi hnhng nu khng th khng lm g cIF UPDATE (first_name) OR UPDATE (Last_Name)BEGINDo some conditional processing when either of thesecolumns are updated.ENDNu mun kim tra nhiu columns ta c th dng hm khc lColumns_Updated() . Xin xem thm trong SQL Server Books Online bitthm chi tit v cch s dng.Viewsnh ngha mt cch n gin th view trong SQL Server tng t nh Querytrong Access database. View c th c xem nh mt table o m data ca nc select t mt stored query. i vi programmer th view khng khc chi sovi table v c th t v tr ca table trong cc cu lnh SQL. c im caView l ta c th join data t nhiu table v tr v mt recordset n. Ngoi rata c th "xo nu" data (manipulate data) trc khi tr v cho user bng cchdng mt s logic checking nh (if, case...).V d:Create View OrderReportAsSelect OrderID,(case when [Name] is null then 'New Customer'else [Name]end )As CustomerName,ProductName,DateProcessedFrom Customers Right Outer Join Orders onCustomers.CustomerID=Orders.CustomerIDhttp://ebooks.vdcmedia.comHC SQL SERVER 200056Trong v d trn ta ch yu tr v data t Orders table trong PracticeDB nhngthay v display CustomerID vn khng c nhiu ngha i vi user ta sdisplay tn ca customer bng cch join vi Customer table. Nu CustomerName l Null ngha l tn ca customer t order khng tn ti trong system.Thay v Null ta s display "New Customer" d nhn hn cho user.Ni chung cu lnh SQL trong View c th t rt n gin nh select ton bdata t mt table cho n rt phc tp vi nhiu tnh nng programming ca TSQL.View Thng c Dng Vo Vic G?View thng c s dng vo mt s cng vic sau:Tp trung vo mt s data nht nh : ta thng dng view select mt s data m user quan tm hay chu trch nhim v loib nhng data khng cn thit.V d: Gi s trong table ta c column "Deleted" vi gi tr l True hayFalse nh du mt record b delete hay khng. Vic ny i khic dng cho vic Audit. Ngha l trong mt ng dng no khiuser delete mt record no , thay v ta physically delete record tach logically delete bng cch nh du record l c "Deleted" phng user yu cu roll back. Nh vy ch yu ta ch quan tm ndata cha delete cn data c nh du deleted ch c khino cn roll back hay audit m thi. Trong trng hp ny ta c thto ra mt view select data m Deleted=False v lm vic ch yutrn view thay v ton b table.n gin ha vic x l data: i khi ta c nhng query rtphc tp v s dng thng xuyn ta c th chuyn n thnh Viewv i x n nh mt table, nh vy s lm cho vic x l data ddng hn.Customize data: Ta c th dng view lm cho users thy datat nhng gc khc nhau mc d h ang dng mt ngun dataging nhau. V d: Ta c th to ra views trong nhng thng tinv customer c th hin khc nhau ty login ID l normal userhay manager.Export v Import data: i khi ta mun export data t SQLServer sang cc ng dng khc nh Excel chng hn ta c thdng view join nhiu table v export dng bcp.http://ebooks.vdcmedia.comHC SQL SERVER 200057Khi s dng view ta c th select,insert, update, delete data bnh thng nh vimt table.V d:Select * From OrderReportWhere DateProcessed <'2003-01-01'Lu : Trong Enterprise Edition (v Developer Edition) ta c th to Index choView nh cho table. Index s c bn n trong cc bi sau.Mun hiu r hn v bi hc ny bn cn lm bi tp s 3.Nh vy trong bi ny chng ta tm hiu Trigger, View trong SQL Server vmt s ng dng ca n. Ni chung view thng c dng tru tng ha(abstract) hay lc raw data (data th) trc khi tr v cho user trong khi triggerthng c dng bo m tnh integrity ca database.Exercise 1: AdvancedQueryPlease follow those steps to practise:1. Create a new database called PracticeDB (using Enterprise Manager (EP) orQuery Analyser (QA))2. Create 2 tables and insert data as follows (use EP or QA) :CustomersCustomerID Name(Int)(nVarChar(50))1John Nguyen2Bin Laden3Bill Clinton4Thomas Hardy5Ana Tran6Bob CarrOrdersOrderID CustomerID ProductName(Int)(Int)(nvarchar(50))12Nuclear Bomb23Missile32Jet-108041Beers54Asian Food67Winehttp://ebooks.vdcmedia.comDateProcessed(datetime)2002-12-012000-03-022003-08-032001-05-122002-10-042002-03-08HC SQL SERVER 20005878Milk2002-05-023. Query data using Select statement with the following Join (use QA)a. Inner Joinb. Left Outter Joinc. Right Outer Joind. Full Outer Joine. Cross JoinThe result of the query must be displayed in the following formatOrderID CustomerName ProductName DateProcessed( Hints: Based on the data supplied and your knowledge about various kind of joinsguess the results then after each select compare it with your guessing to check if whatyou understand is correct. You also use As keyword or alias in the queries)4. Using Select * Into...From statement to create a new table called ProcessedOrders and populate the new table with the data selecting from Orders tableWhere DateProcessed is earlier than 2002-10-05. (use QA)5. Using Insert Into...Select statement to get the top 1 record from Orders tableand insert into the ProcessedOrders (use QA)6. Delete a record from ProcessedOrders where the date processed is 2002-10-04.(use QA)7. Using Union to merge the two data set from Orders and ProcessedOrders into onedata set. (use QA)8. Apply Constraints (use EP or QA)a.Apply the Primary Constraint to the ID column in the tablesb. Apply the Foreign Key Constraint in the Orders table.c. Apply the Check Constraint to the DateProcessed column so that the date iswithin 1970-01-01 2005-01-01 and try to insert invalid data to see if SQLrejects.d. Apply Unique Constraint to the CustomerName column of Customers9. Back up database and Restore to somewhere else (on a different server or on thesame server but with different database name) (use EP or QA)10. Truncate and Drop the ProcessedOrders table (use QA)http://ebooks.vdcmedia.comHC SQL SERVER 2000Exercise 2: Manipulate59Data and Stored ProcedurePlease follow those steps to practise:9. Use bcp to export all data from Orders table of PracticeDB to c:\Orders.txt (or toc:\Orders.csv)10. Change some data in the c:\Orders.txt and save. Then import to Orders table fromthe text file using bcp11. Import Orders.txt to Orders table using BULK INSERT12. Create a Linked Server LinkedPracticeDB which link to an Access databasePracticeDB.mdb (firstly you have to create an Access database similar toPracticeDB in SQL Server and input some data). Then do a select data using fourpart name and OPENQUERY13. Using ad hoc computer name with OPENROWSET andOPENDATASOURCE functions to select data from PracticeDB.mdb14. Create the following CursorDECLARE @au_lname varchar(40), @au_fname varchar(20)DECLARE Employee_Cursor CURSOR FORSELECT LastName, FirstName FROM Northwind.dbo.EmployeesOPEN Employee_CursorFETCH NEXT FROM Employee_Cursor INTO @au_lname, @au_fnameWHILE @@FETCH_STATUS = 0BEGINPRINT 'Author:' + @au_fname + ' ' + @au_lnameFETCH NEXT FROM Employee_Cursor INTO @au_lname, @au_fnameENDCLOSE Employee_CursorDEALLOCATE Employee_Cursor15. Create the following stored procedure and try to execute with some valuesCREATE PROCEDURE AddNewOrder@OrderID smallint,@ProductName varchar(50),@CustomerName varchar(50),@Result smallint=1 OutputASDECLARE @CustomerID smallintBEGIN TRANSACTIONIf not Exists(SELECT CustomerID FROM Customers WHERE[Name]=@CustomerName)BEGINhttp://ebooks.vdcmedia.comHC SQL SERVER 2000SET @CustomerID= (SELECT Max(CustomerID) FROMCustomers)SET @CustomerID=@CustomerID+1INSERT INTO CustomersVALUES(@CustomerID,@CustomerName)If Exists(SELECT OrderID FROM [Orders] WHEREOrderID=@OrderID)BEGINSELECT @Result=1ROLLBACK TRANSACTIONENDElseBEGININSERT INTO[Orders](OrderID,ProductName,CustomerID)VALUES(@OrderID,@ProductName,@CustomerID)SELECT @Result=0COMMIT TRANSACTIONENDENDElseBEGINIf Exists(SELECT OrderID FROM [Orders] WHEREOrderID=@OrderID)BEGINSELECT @Result=1ROLLBACK TRANSACTIONENDElseBEGININSERT INTO[Orders](OrderID,ProductName,CustomerID)VALUES(@OrderID,@ProductName,@CustomerID)SELECT @Result=0COMMIT TRANSACTIONENDENDPrint @ResultReturn9. Using VB 6 or VB.NET to execute the AddNewOrder stored procedure10. Using xp_cmdshell extended stored procedure to send a message (xp_cmdshellnet send Hello)http://ebooks.vdcmedia.com60HC SQL SERVER 200061Exercise 3: Triggers And ViewsPlease follow those steps to practise:16. Create 3 triggers to audit the changes to the Orders table.Tips: Create an audit table aud_Orders with the same colums as in theOrders table and 2 more colums AuditType(with values eitherI,U,D) and DateTimeStamp(which will record the date timestamp of changes) Create Update Triggers (Similar to Insert,Delete) : when a recordin the Orders table is updated the trigger will move the old recordto the audit table(record the date time and mark it as U)17. Create a view that shows all the orders with the following colums:OrderID,CustomerName,ProductName,DateProcessed,StatusBusiness rules:If CustomerName is a null value New Customer is returnedIf DateProcessed is later than current date return Pending, if DateProcessed isealier return History in Status colum.Tips:a. Using Case When ...Then statement in the viewb. Using Getdate() function to get the current date timehttp://ebooks.vdcmedia.com...
View Full Document

Page1 / 61

HOCSQL - HC SQL SERVER 20001MC LCOverview of SQL Server 2000

This preview shows document page 1. Sign up to view the full document.

View Full Document Right Arrow Icon
Ask a homework question - tutors are online