Programming Microsoft SQL Server 2000 with Microsoft Visual Basic .Net - P3

Tham khảo tài liệu programming microsoft sql server 2000 with microsoft visual basic .net - p3, công nghệ thông tin, cơ sở dữ liệu phục vụ nhu cầu học tập, nghiên cứu và làm việc hiệu quả
Nội dung trích xuất từ tài liệu:
Programming Microsoft SQL Server 2000 with Microsoft Visual Basic .Net - P3 DROP VIEW vewEmailContacts GO --Create view to select all columns for --all rows from the EmailContacts table. CREATE VIEW vewEmailContacts AS SELECT * FROM EmailContacts GO --Select all columns for all rows from --the vewEmailContacts view. SELECT * FROM vewEmailContacts Con t r a st ing Un e n cr ypt e d a n d Encr ypt e d Vie w s Wit h m inor ext ensions, t he preceding sam ple can serv e as a t em plat e for t he creat ion of any v iew. The following script illust rat es one of t hese ext ensions. I t creat es a view in t he Chapt er04 dat abase t hat has t he Shippers t able in t he Nort hwind dat abase as it s base t able. While t he row source for a v iew can reside in anot her dat abase, t he CREATE VI EW st at em ent can creat e a v iew only in t he curr ent dat abase. Sim ilarly, t he DROP VI EW st at em ent can r em ove a view only from t he cur rent dat abase. An easy way t o r efer ence a row source fr om anot her SQL Serv er dat abase is t o use a t hree- part nam e. The first part refers t o t he alt ernat e dat abase nam e, Nort hwind in t his case. The second part designat es t he owner of t he obj ect prov iding t he row source. When t he row source ow ner is t he default dbo user, you can om it it s ex plicit designat ion ( as in t he follow ing script ) . The t hird nam e part denot es t he nam e of t he dat abase obj ect prov iding t he r ow source for a view. Figure 4- 1 shows t he r esult set from t he SELECT st at em ent based on t he vew Shippers v iew. Not ice t hat it m at ches t he values in t he Nort hwind..Shippers t able, which is t he source for t he v ew Shippers v iew. Not ice t hat unlik e t he fir st code sam ple, t his one doesn’t include a specific reference t o t he Chapt er04 dat abase. That ’s because Query Analyzer w ill cont inue t o use Chapt er04 unt il y ou specify a different dat abase wit h a new USE st at em ent . --CreatevewShippers --Search for, and remove if found, the --vewShippers view in the Chapter04 database. IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_NAME = ’vewShippers’) DROP VIEW vewShippers GO --Create a new version of the vewShippers --view in the Chapter04 database from the --Shippers table in the Northwind database. CREATE VIEW vewShippers AS SELECT * FROM Northwind..Shippers GO --Select all rows and columns from the --vewShippers view in Chapter04. SELECT * FROM vewShippersPlease purchase PDF Split-Merge on www.verypdf.com to remove this watermark. Figu re 4 - 1 . Th e r e su lt se t from a vie w ba se d on t h e Sh ip pe rs t a ble in t h e N or t h w in d da t ab ase . The ENCRYPTI ON at t r ibut e isn’t set by default . Set t ing encrypt ion doesn’t change t he r esult set from a SELECT st at em ent . I nst ead, it encodes t he T- SQL for a view’s definit ion. You can v erify t his by t r y ing t o display t he script for a view. The VI EW_DEFI NI TI ON colum n for t he I NFORMATI ON_SCHEMA.VI EWS v iew r et urns t he script for a v iew on each of it s r ows. The follow ing scr ipt dem onst rat es t he synt ax for inv ok ing t he ENCRYPTI ON at t ribut e. The script also dem onst rat es t he sy nt ax for ret urning t he script t hat defines a view . This script includes all com m ent s as well as t he operat ional T- SQL st at em ent s for creat ing t he v iew; t hese st at em ent s include t he CREATE VI EW st at em ent for generat ing a new v iew and t he SELECT st at em ent for defining a view’s result set . I n t his case, t he SELECT st at em ent is ident ical t o t he one in t he preceding v iew ...