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

Chia sẻ: Thanh Cong | Ngày: | Loại File: PDF | Số trang:50

Thêm vào BST

Báo xấu

115
lượt xem 20
download

Download Vui lòng tải xuống để xem tài liệu đầy đủ

Tham khảo tài liệu 'programming microsoft sql server 2000 with microsoft visual basic .net - p2', 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ả

Chủ đề:

Bình luận(0) Đăng nhập để gửi bình luận!

Lưu

Nội dung Text: Programming Microsoft SQL Server 2000 with Microsoft Visual Basic .Net - P2

infor m at ion about views as well as t ables. Specify a TABLE_TYPE colum n value of VI EW in t he WHERE clause for a SELECT st at em ent t o ret urn only v iews. Wit h t he COLUMNS v iew of t he I NFORMATI ON_SCHEMA, you can ret urn inform at ion about colum ns in a dat abase. The t hird bat ch illust rat es t his app- licat ion. I t also r eveals a new sy nt ax for specify ing t he dat abase ser ving as t he source for t he v iew. Not ice t hat t he specificat ion of t he v iew nam e has t hr ee part s. The first of t hese is t he dat abase nam e— Chapt er02. Designat ing a dat abase nam e as t he first part r em ov es t he need t o designat e a dat abase cont ext w it h a USE st at em ent . This is because no m at t er w hat dat abase cont ext t he st at em ent ex ecut es, it always ext ract s infor m at ion fr om t he dat abase— t hat is, t he first part of t he I NFORMATI ON_SCHEMA view nam e. The second and t hird part s follow t he conv ent ion for t he pr eceding bat ches except for t he nam e of t he specific I NFORMATI ON_SCHEMA v iew ( COLUMNS) . The sam ple also includes a WHERE clause t o reference a part icular t able— in part icular, Em ailCont act s. Wit hout t he WHERE clause, t he T- SQL st at em ent in t he bat ch will ret ur n inform at ion for all t he colum ns w it hin t he Chapt er02 dat abase, including t hose from syst em and user - defined t ables. The final bat ch shows t he I NFORMATI ON_SCHEMA sy nt ax for r eport ing about t he keys in a dat abase. These include t he pr im ar y k eys, for eign k eys, and unique keys. The inform at ion is r eally about t he colum ns on w hich an applicat ion defines it s keys. As w it h t he pr eceding bat ch, t his sam ple rest r ict s t he r esult only t o keys for t he Em ailCont act s t able. --INFORMATION_SCHEMA_Samples --List databases on current server. USE master SELECT * FROM INFORMATION_SCHEMA.SCHEMATA GO --List user-defined tables in Chapter02 database. USE Chapter02 SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE NOT(SUBSTRING(TABLE_NAME,1,3) = ’sys’ OR SUBSTRING(TABLE_NAME,1,3) = ’dtp’) GO --List all columns in EmailContacts table. SELECT * FROM Chapter02.INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = ’EmailContacts’ GO --List data on columns constrained as keys in --the EmailContacts table. SELECT * FROM Chapter02.INFORMATION_SCHEMA.KEY_COLUMN_USAGE WHERE TABLE_NAME = ’EmailContacts’ GO Figur e 2- 2 displays an excerpt from t he r esult set for t he preceding script . The ret ur n for each bat ch begins wit h a new set of colum n headers. The list of dat abases includes our user - defined dat abase, Chapt er02, along w it h t he t wo SQL Ser ver sam ple dat abases, pubs and Nort hwind, as well as t he four syst em dat abases. The second header shows j ust one r ow for t he lone t able in Chapt er02. The t hird header r ows rev eal t he nam es of t he four colum ns wit hin t he Em ailCont act s t able. This view pr ov ides m uch addit ional inform at ion about each colum n, such as it s nullabilit y, dat a t ype, and relat ed set t ings, including it s precision and scale if appropriat e. The row for t he last set of colum n headers prov ides inform at ion about t he lone k ey for t he Em ailCont act s t able. This is t he t able’s pr im ar y k ey . Each k ey has a nam e, w hich appears in t he Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
CONSTRAI NT_NAME colum n. Because our synt ax for t he creat ion of t he t able didn’t specify a nam e for t he pr im ar y k ey , t he last row of out put in Figure 2- 2 shows t he syst em - generat ed nam e for t he t able’s pr im ary key in t he CONSTRAI NT_NAME colum n. A subsequent sam ple in t he “ Scr ipt ing Key s and I ndexes” sect ion illust rat es t he synt ax for assigning a specific nam e t o a pr im ar y key . Figu r e 2 - 2 . Sa m ple ou t pu t from a se t of fou r T- SQL ba t ch e s illust r a t in g t h e b eh avior of I N FORM ATI ON _ SCH EM A vie w s. I NFORMATI ON_SCHEMA offers m any m or e v iew s besides t hose illust rat ed in t he preceding four bat ches. For exam ple, you can gat her inform at ion about check const raint s for colum n v alues, t able const raint s, st ored pr ocedur es, and user- defined funct ions. Refer t o t he “I nform at ion Schem a View” t opic in Book s Online for an overv iew of t he I NFORMATI ON_SCHEMA views along w it h links defining t he result set for each t ype of view available. W or k ing w it h Colum n D a t a Type s The “Cr eat ing a Table” sect ion int r oduced t he CREATE TABLE st at em ent synt ax and dem onst rat ed how t o declar e t ypical syst em dat a t ypes such as int and nvarchar. Apply ing t his fram ework w ill enable y ou t o assign t he ot her dat a t ypes t o colum ns as well. I n spit e of t he sim plicit y of t he ov erall approach, t her e are special issues for som e dat a t ypes, and one dat a t ype hasn’t been cov ered yet . This sect ion r ev iews t hese issues. Com p ar in g t im e st am p an d da t e t im e D at a Typ e s Those w ho ar e m igrat ing t o SQL Ser ver m ay be confused at first by t he t im e- st am p dat a t ype and whet her it has anyt hing t o do w it h dat et im e dat a ( it doesn’t ) . The row version alias for t im est am p act ually sum m ar izes t he purpose of Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
t he t im est am p dat a t ype m ore precisely. This m ay be one r eason w hy Micr osoft plans t o use t he r owv er sion nam e m or e prom inent ly in t he fut ur e. The follow ing scr ipt cont rast s t he t im est am p and dat et im e dat a t ypes. The cont rast r elies on t wo t ables, t 1 and t 2, each w it h t hr ee colum ns, col1, col2, and col3. The col1 colum n has an int dat a t ype and offers a value for program m at ically populat ing r ows in each t able. The col2 and col3 colum ns populat e aut om at ically . The dat a t ype for col2 is dat et im e, but it has a DEFAULT const raint t hat assigns t he cur r ent t im e aut om at ically . Users and your applicat ion’s code can override t his default value. The t im est am p dat a t ype also aut om at ically populat es col3 in bot h t ables. Howev er , for t his dat a t ype, only SQL Ser ver updat es t he value. This occurs wit h t he insert ion of a new row or t he revision of any value in an ex ist ing row . Aft er creat ing t he t 1 and t 2 t ables, t he scr ipt does a couple of operat ions t o cont rast t im est am p and dat et im e dat a t ypes. The script insert s a recor d int o each t able w it h a delay of 1 second bet w een each insert ion. The WAI TFOR DELAY st at em ent act ually suspends t he operat ion of SQL Ser ver for t he durat ion of it s argum ent . Ther efore, t he insert ion for t able t 2 can occur m or e t han 1 second aft er t he insert ion for t able t 1 because SQL Ser ver r equires t im e t o per for m t he operat ion. Aft er running a SELECT query t o show t he colum n values in t ables t 1 and t 2, t he script next updat es t he value of col1 in t able t 2. Then it reruns t he SELECT quer y t o dem onst rat e t he im pact of t he operat ion on t he colum n values in t he sam ple. At t he sam ple’s conclusion, t he script r em ov es t he t 1 and t 2 t ables from t he Chapt er02 dat abase. --CompareTimestampToDatetime --Execute statements after USE from Chapter02 database. USE Chapter02 --Create two tables named t1 and t2. CREATE TABLE t1 ( col1 int, col2 datetime DEFAULT GETDATE(), col3 timestamp ) CREATE TABLE t2 ( col1 int, col2 datetime DEFAULT GETDATE(), col3 timestamp ) GO --Insert a row in tables t1 and t2 with --a one-second delay between tables. INSERT INTO t1 (col1) VALUES (1) WAITFOR DELAY ’00:00:01’ INSERT INTO t2 (col1) VALUES (1) GO --Run queries on tables t1 and t2. SELECT ’t1’ AS ’Table Name’, * FROM t1 SELECT ’t2’ AS ’Table Name’, * FROM t2 GO --Update column col1 in table t2. UPDATE t2 SET col1 = col1 + 2 GO --Re-run queries on tables t1 and t2. Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
SELECT ’t1’ AS ’Table Name’, * FROM t1 SELECT ’t2’ AS ’Table Name’, * FROM t2 GO --Drop tables t1 and t2. DROP TABLE t1 DROP TABLE t2 GO Figur e 2- 3 shows t he Result s pane fr om Query Analyzer for t he pr eceding script . The col2 value for t he second row is 1 second plus a SQL Serv er clock t ick ( 3 m illiseconds) behind t he col2 value for t he first r ow. This clock t ick is t he t im e t hat it t ak es t o com plet e t he r ow insert ion for t able t 2. The col3 values for t he first and second rows ar e displaced by 1. Because t he insert ion for t able t 2 occurr ed im m ediat ely aft er t he one for t able t 1, t his is appropr iat e. I f ot her insert ions t ook place bet ween t he init ial insert ion for t able t 1 and t able t 2, t he difference in t he binary value for col3 w ould be great er . The updat e of col1 for t able t 2 dem onst rat es t his point . The second pair of rows in Figur e 2- 3 also displays t he colum n values for t ables t 1 and t 2 aft er an updat e t o col1 in t able t 2. I n t he case of t able t 1, t he col3 value r em ains unalt er ed. How ev er , t he col3 value for t able t 2 gr ows by 1 fr om it s init ial value aft er t he insert ion. This incr eased v alue r eflect s t he im pact of t he updat e t o col1 in t able t 2. While t he second pair of r ows var ies from t he first pair for col3 in Figure 2- 3, t he col2 values are ident ical bet w een t he first and second pair of rows. This is because updat ing v alues of ot her colum ns has no im pact on t he dat et im e values in col2, but updat ing any v alue in a r ow does im pact t he value of t he t im est am p colum n value in t he row . N ot e You can hav e j ust one colum n per t able w it h a t im est am p dat a t ype. Figu r e 2 - 3 . Sa m p le ou t pu t con t r a st in g t h e beh avior of d a t e t im e an d t im e st a m p d a t a t ype s. Usin g sql_ va ria n t Da t a Typ e V alu e s The sql_v ar iant dat a t y pe is t he only dat a t ype t hat let s y ou st or e differ ent dat a t ypes in t he sam e colum n. This capabilit y is useful for st oring a collect ion of values in a colum n in w hich y ou don’t k now in advance what t ypes of values you’ll Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
hav e. This can ar ise in a sit uat ion in which y ou let a user define values on an ad hoc basis. Consider a t able t hat st or es m iscellaneous infor m at ion about cont act s. Som et im e your applicat ion m ay need t o st or e a m oney dat a t ype, anot her t im e a user m ay want t o specify a dat e, and in y et ot her cases, your applicat ion m ay need t o designat e a var iable- lengt h charact er value. This kind of scenar io is t ypical of sit uat ions in w hich y our applicat ion needs t o charact er ize elem ent s but t he com plet e set of elem ent s and t heir at t r ibut es isn’t known at t he t im e t hat you dev elop t he applicat ion. The follow ing scr ipt assigns a set of ext ended propert ies t o a t able of cont act s ident ified by a Cont act I D colum n. Not ice t hat t he CREATE TABLE st at em ent uses t hr ee colum ns t o charact er ize t he cont act s. The m ost im port ant colum n is PropValue, w hich has a sql_var iant dat a t ype. This colum n st ores t he act ual value t hat charact er izes a cont act . I n som e cases, t he cont act charact er ist ic is a m onet ary value, in ot her cases it is a dat e, and in st ill ot her cases it is a st ring value, such as t he nam e of a fav or it e sport or st or e. Pr opI D and PropNam e describe t he charact erist ic for t he cont act . Pr opNam e m ak es it easy t o follow what t he Pr opValue colum n values describe w it hout r equir ing anot her t able t o decode t he PropI D colum n values. A subsequent sam ple w ill ret urn t o t he Cont act Ext Pr ops t able and link it t o ot her t ables cont aining cont act and propert y nam es. I n addit ion, t hat sam ple w ill add a pr im ary key t o t he t able. These refinem ent s ar en’t necessary t o dem onst rat e t he behavior of sql_var iant dat a t ypes. The I NSERT I NTO st at em ent s t hat add v alues t o t he Pr opValue colum n use CAST funct ions t o est ablish sub dat a t ypes w it hin t he sql_var iant colum n. This isn’t st rict ly necessary, but t he CAST funct ion confir m s t he abilit y of t he sql_var iant dat a t ype t o accept m ult iple ot her dat a t ypes. --SQL_variantSample --Execute statements after USE from Chapter02 database. USE Chapter02 GO --Remove prior version of ContactExtProps if it exists. IF EXISTS ( SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = ’ContactExtProps’ ) DROP TABLE ContactExtProps GO --Create ContactExtProps with four columns. CREATE TABLE ContactExtProps ( ContactID int NOT NULL, PropID int NOT NULL, PropName nvarchar(20), PropValue sql_variant ) GO --Populate ContactExtProps with values. INSERT INTO ContactExtProps VALUES(1, 1,’Birthday’, CAST(‘9/9/1944’ AS datetime)) INSERT INTO ContactExtProps VALUES(1, 2, ’Salary’, CAST(50000 AS money)) INSERT INTO ContactExtProps VALUES(1, 3, ’Bonus’, CAST(30000 AS money)) Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
INSERT INTO ContactExtProps VALUES(1, 4, ’Favorite Sport’, ’Boxing’) INSERT INTO ContactExtProps VALUES(2, 1, ’Birthday’, CAST(‘1/1/1950’ AS datetime)) INSERT INTO ContactExtProps VALUES(2, 2, ’Salary’, CAST(60000 AS money)) INSERT INTO ContactExtProps VALUES(2, 3, ’Bonus’, CAST(40000 AS money)) INSERT INTO ContactExtProps VALUES(2, 5, ’Favorite Store’, CAST(‘Tailspin Toys’ AS nvarchar(2 0))) GO --Select all records with a Favorite Store property. SELECT ContactID, PropName, PropValue FROM ContactExtProps WHERE PropName = ’Favorite Store’ GO --Select Salary and Bonus properties and add one to --money data type for Salary and Bonus properties. SELECT ContactID, PropName, Cast(PropValue AS money)+1, PropValue FROM ContactExtProps WHERE PropID >=2 and PropID =2 and PropID
Usin g Com pu t e d Colum n s in Tab le s A com put ed colum n adds a v irt ual colum n t o a t able based on an expression t hat draws on one or m or e ot her colum ns w it hin t he t able. You can specify a com put ed colum n w it h a CREATE TABLE ( or an ALTER TABLE) st at em ent . You can use a com put ed colum n in a SELECT list , a WHERE clause, or an ORDER BY clause. I n addit ion, com put ed colum ns can part icipat e in t he definit ion of an index or prim ar y k ey . You can also use a com put ed colum n in t he definit ion of a UNI QUE const raint . When y ou’r e using a com put ed colum n t o help define a prim ary k ey or an index , t he expression m ust be det erm inist ic. I n ot her words, t he expression m ust generat e t he sam e r esult all t he t im e based on t he sam e input . An expr ession based on GETDATE isn’t appropriat e for a com put ed colum n t hat w ill serve as a colum n for an index . This is because t he result w ill change each t im e y ou open t he t able. Despit e t he wide range of uses for com put ed colum ns, t her e are sev eral circum st ances in w hich you cannot use t hem . For exam ple, you cannot specify nullabilit y for com put ed colum ns. This is because SQL Ser ver aut om at ically det erm ines w het her a com put ed colum n is null based on it s input and t he expression for com bining t he com put ed colum ns in quest ion. Ev en non- nullable input s can generat e null r esult s if an expression generat es an underflow or ov erflow. I n addit ion, y ou cannot specify input s or m odify t he cont ent s of colum ns w it h I NSERT I NTO or UPDATE st at em ent s. Yet anot her applicat ion t hat doesn’t perm it t he use of com put ed colum ns is t hat w hich defines FOREI GN KEY and DEFAULT const raint s. The follow ing scr ipt sam ple illust rat es t he sy nt ax for specify ing a com put ed colum n and shows an exam ple of how t o use it . The CREATE TABLE st at em ent designat es t hr ee colum ns for t he Proj ect edDeliver yDat es t able. The fir st colum n is aut oincrem ent ing, w it h default set t ings for t he I DENTI TY colum n pr oper t y. The second colum n has a dat et im e dat a t ype for accept ing order dat es. The t hird colum n is a com put ed colum n. The expression for t he colum n uses t he Dat eAdd funct ion t o com put e a proj ect ed deliv er y dat e based on t he t able’s OrderDat e colum n. N ot e The I DENTI TY pr opert y perm it s y ou t o set t he seed value and t he st ep value for an aut oincr em ent ing series. I t s default seed and st ep v alues ar e bot h 1. You can specify alt er nat e seed and st ep v alues by adding parent heses aft er t he key word. For ex am ple, use I DENTI TY( 100, 10) t o specify a Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
series t hat st ar t s at 100 and progresses in st eps of 10. --ComputedColumnSample --Execute statements after USE from Chapter02 database. USE Chapter02 GO --Remove prior version of ProjectedDeliveryDates if it exists. IF EXISTS ( SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = ’ProjectedDeliveryDates’ ) DROP TABLE ProjectedDeliveryDates --Create ProjectedDeliveryDates with three columns. CREATE TABLE ProjectedDeliveryDates ( OrderID int IDENTITY Not Null PRIMARY KEY, OrderDate datetime Not Null, ProjectedDeliveryDate AS DateAdd(day, 10, OrderDate) ) GO --Populate ProjectedDeliveryDates. INSERT INTO ProjectedDeliveryDates Values(GetDate()) INSERT INTO ProjectedDeliveryDates Values(‘9/1/01’) --Display date and time for projected delivery. SELECT OrderID, OrderDate, ProjectedDeliveryDate FROM ProjectedDeliveryDates --Display just date for projected delivery. SELECT OrderID, OrderDate, LEFT(ProjectedDeliveryDate,12) AS ’ProjectedDeliveryDate’ FROM ProjectedDeliveryDates GO Aft er insert ing order dat es based on eit her t he GETDATE funct ion or a st ring represent ing a dat e, t he script queries t he Pr oj ect edDeliv er yDat es t able wit h t wo separat e SELECT quer ies. The first SELECT query st at em ent dem onst rat es t he com put ed colum n as part of t he list for t he st at em ent . For t his st at em ent , t he Proj ect edDeliv eryDat e colum n displays bot h t he dat e and t he t im e. How ev er, your applicat ion m ay r equir e j ust t he dat e. The second query st at em ent shows how t o crop t he t im e value out of t he display. Figur e 2- 5 present s t he out put from bot h SELECT st at em ent s. Figu r e 2 - 5 . Th is e x am ple sh ow s t h e u se of a com pu t e d colu m n t o disp la y a pr oj ect e d d a t e for t h e de live ry of a n or de r in eit h er of t w o r ep re se n t at ion s—on e t h at in clu de s a t im e a n d an ot h er t h a t sh ow s on ly a da t e. Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
Addin g Che ck Con st r a int s Check const raint s ar e am ong t he m ost sim ple of t he const raint t ypes available t o dat abase dev elopers and adm inist rat ors. Basically, a check const raint allows you t o r est r ict t he values ent er ing a colum n— som ewhat in t he way t hat dat a t ype specificat ions do. ( Users cannot ent er a charact er st ring int o a colum n wit h an int dat a t ype.) However, check const raint s base t heir r est r ict ion on a Boolean expression t hat evaluat es t o Tr ue or False. The const raint expr ession can draw on one or m or e colum n values fr om t he t able t o w hich it applies. A colum n const raint applies t o an individual colum n, and a t able const raint r efer ences t wo or m or e colum ns. The value False for t he expression v iolat es t he const raint . SQL Ser ver rej ect s t he insert ion of a r ecord wit h a value t hat violat es a const raint . You can use t his behav ior t o m aint ain t he int egr it y of t he colum n values in t he t ables of your dat abase applicat ions. The follow ing scr ipt has t hr ee bat ches of st at em ent s. First t he script adds a colum n check const raint t o t he Em ailCont act s t able init ially generat ed in t he “Cr eat ing a Table” sect ion. The first bat ch also t est s t he const raint by at t em pt ing t o insert a r ow w it h a colum n value t hat v iolat es t he const raint . I n t he second bat ch, t he script shows how t o disable a const raint . This bat ch at t em pt s t o insert t he sam e record t hat failed in t he first bat ch, but t his t im e t he insert ion succeeds. The t hird bat ch dr ops t he const raint fr om t he Em ailCont act s t able and delet es t he record added in t he second bat ch. You can use t he ALTER TABLE st at em ent t o add a colum n check const raint t o a t able, such as Em ailCont act s. The ALTER TABLE st at em ent perm it s t he m odificat ion of a t able aft er it s creat ion. Besides adding check const raint s, you can add ot her const raint s, such as pr im ary or for eign k eys, and new colum ns. To add a const raint , use t he ADD k eyw ord follow ed by CONSTRAI NT. You can opt ionally assign a const raint nam e. Specify ing a const raint nam e is par t icular ly conv enient if your applicat ion has a need t o disable or rem ov e a const r aint . I f y ou don’t explicit ly nam e y our const raint s, SQL Ser ver aut om at ically assigns a nam e. The CHECK k eyword specifies t he t y pe of const raint . Finally , t he expr ession t railing t he CHECK keyw ord r epr esent s t he condit ion for w hich t he check const raint t est s. I n t he sam ple scr ipt , t he const raint evaluat es t he Em ail1 value t o ensure t hat it cont ains t he @ sym bol. E- m ail addresses t hat don’t include t his sym bol ar e inv alid. --ColumnCheckConstraintSample USE Chapter02 --Add CHECK constraint to require at --least one @ in Email1. ALTER TABLE EmailContacts ADD CONSTRAINT ch_EmailContacts_Email1_for@ CHECK (CHARINDEX(‘@’,Email1)0) --Test constraint with an Email1 value Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
--that contains no @; the INSERT statement fails. INSERT INTO EmailContacts VALUES (3,’Karl’, ’Doe1’, ’Doe1.hlcofvirginia.com’) GO --Disable the constraint. ALTER TABLE EmailContacts NOCHECK CONSTRAINT ch_EmailContacts_Email1_for@ --Test the disabled constraint with an Email1 value --that contains no @; the INSERT statement succeeds. INSERT INTO EmailContacts VALUES (3,’Karl’, ’Doe1’, ’Doe1.hlcofvirginia.com’) GO --Drop the constraint and delete bad Email1 row. ALTER TABLE EmailContacts DROP CONSTRAINT ch_EmailContacts_Email1_for@ DELETE FROM EmailContacts WHERE LastName = ’Doe1’ GO Scr ipt ing Ke ys a n d I n de x e s This sect ion dr ills dow n on t echniques for script ing pr im ary k eys, for eign k eys, and indexes in y our t ables. Each t opic begins w it h a br ief descr ipt ion of background issues befor e t he discussion of a sam ple or t w o t hat illust rat e t ypical uses for t he t opic. Prim a r y Keys Prim ar y k eys hav e t w o especially dist inct iv e feat ur es. First , each row m ust hav e a unique pr im ary k ey value. Second, no pr im ary k ey value can be null— ev en if it is t he only null record in a t able. I t is com m on, but not m andat or y, t o base pr im ary keys on a single colum n wit h an I DENTI TY pr opert y set t ing. A pr im ary key can span m ult iple colum ns. Each pr im ar y k ey cr eat es an index . An index is a dat abase obj ect t hat support s fast access t o t he r ows wit hin a t able or v iew. Any one SQL Serv er t able can have up t o 250 index es, but only one of t hese can be clust er ed. A clust er ed index physically orders t he records for a t able in st or age according t o t he index values. Because a clust ered index can speed perform ance so m uch, you should reserv e t he clust er ed index so t hat it serv es your applicat ion’s m ost heav ily used look up requir em ent . You can m ake eit her t he index for t he pr im ary key or anot her index t he clust er ed index for a t able. Wit h a st andard SQL Serv er inst allat ion, a pr im ary key declarat ion m ak es t he pr im ary key clust er ed by default . How ev er , you can explicit ly declare a prim ary key as nonclust ered. As m ent ioned prev iously, t he pr im ar y k ey can hav e it s nam e assigned eit her by t he syst em or by a user . The follow ing script sam ple r e- creat es t he Em ailCont act s t able. I f y ou check t he sam ple in t hat sect ion, y ou w ill observ e t hat t he prim ary key declarat ion doesn’t include a nam e for t he prim ary k ey. The follow ing script re- creat es t he generat ion of t he Em ailCont act s t able, but t his sam ple does explicit ly nam e t he pr im ary key. The sam ple also dem onst rat es t he use of t he sp_pk eys syst em st ored procedure— once befor e dr opping t he first version of t he Em ailCont act s t able and a second t im e aft er cr eat ing a new v ersion of t he t able wit h a user- defined nam e for t he prim ar y k ey . The sp_pkeys syst em st or ed procedur e has a result set w it h a separat e r ow for each colum n in t he prim ary Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
key . The colum ns of t he r esult set r eport such it em s as t he dat abase nam e, t he t able nam e, and t he pr im ary k ey nam e. The prim ary k ey declarat ion in t his sect ion per form s ident ically t o t he one in t he “Cr eat ing a Table” sect ion ex cept for t he assignm ent of a nam e t o t he prim ary key . I n t his inst ance, t he sam ple uses t he CONSTRAI NT keyword. This is opt ional for a prim ary k ey, but it s use can rem ind you t hat t he pr im ary key is a m em ber of t he fam ily of const raint s, including check const raint s and foreign k ey const raint s. The nam e for t he pr im ary k ey appears im m ediat ely aft er t he CONSTRAI NT keyword. The follow ing scr ipt also explicit ly declar es t he prim ary key as clust er ed. You can replace t he k eyw ord CLUSTERED w it h NONCLUSTERED t o av oid physically ordering t he records in t he t able according t o Cont act I D values. --CreateEmailContactsTableWithPKName --Execute statements after USE from Chapter02 database. USE Chapter02 GO --Print primary key columns and remove prior version --of EmailContacts, if the table exists. IF EXISTS ( SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = ’EmailContacts’ ) BEGIN EXEC sp_pkeys ’EmailContacts’ DROP TABLE EmailContacts END --Create EmailContacts with three columns while --explicitly assigning a name to the primary key. CREATE TABLE EmailContacts ( ContactID int Not Null CONSTRAINT pk_EmailContacts_ContactID PRIMARY KEY CLUSTERED, FirstName nvarchar(20), LastName nvarchar(35), Email1 nvarchar (255) ) GO --Populate EmailContacts and run a SELECT query INSERT INTO EmailContacts VALUES(1,’Rick’, ’Dobson’, ’rickd@cabinc.net’) INSERT INTO EmailContacts VALUES(2,’Virginia’, ’Dobson’, ’virginia@cabinc.net’) SELECT * FROM EmailContacts GO --List primary key columns in EmailContacts. EXEC sp_pkeys ’EmailContacts’ Figur e 2- 6 shows t he out put from t he pr eceding script . The result s below t he first and t hird colum n headers reveal t he out put fr om t he sp_pkeys syst em st ored procedur e befor e and aft er t he nam ing of t he pr im ary k ey . The first set of colum n headers shows t he syst em defined nam e for t he pr im ary k ey. The t hird set of colum n headers shows t he out put from t he sp_keys st or ed pr ocedur e aft er t he assignm ent of a nam e t o t he pr im ar y k ey . Not ice how t he PK_NAME colum n value Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
in t he last r ow of Figure 2- 6 m at ches t he nam e assigned t o t he pr im ary key in t he preceding script . Figu r e 2 - 6 . Sa m ple ou t pu t de m on st ra t ing pr im a ry k e y n a m e s a ssig n e d by t h e syst e m ( t op r ow ) a n d by t h e pr ece d ing scr ip t ( bot t om row ) . Recall t hat t he “Using sql_var iant Dat a Type Values” sect ion init ially creat ed t he Cont act Ext Pr ops t able. When it was creat ed in t hat sect ion, t he script didn’t creat e a pr im ary key for it . I n addit ion, t he Cont act Ext Pr ops t able includes a colum n, PropI D, designed t o link t o anot her t able t hat defines nam es t o m at ch t he PropI D values. The next script cr eat es a t able, Ext Props, t hat m at ches t he PropI D int values w it h nam es in a colum n of var iable- lengt h charact er st rings. The script t hen pr oceeds t o use t he sp_pk eys syst em st or ed pr ocedur e t o det erm ine w het her a pr im ary key colum n is already in t he Cont act Ext Pr ops t able. A value of 0 for @@ROWCOUNT specifies no pr im ary k ey . I f t he value is great er t han 0, t he pr ocedur e drops t he ex ist ing pr im ar y k ey. Next t he pr ocedure uses an ALTER TABLE st at em ent t o creat e a new pr im ary k ey based on t wo colum ns— Cont act I D and PropI D. This prim ary key desi- gnat ion perm it s each cont act t o hav e m ult iple propert ies but no m or e t han one set t ing for any one pr opert y. The for eign k ey sam ple in t he next sect ion w ill dem onst rat e how t o link t he Cont act Ext Pr ops t able t o t he Em ailCont act s and Ext Pr ops t ables. --CreateExtProps --Execute statements after USE from Chapter02 database. USE Chapter02 GO --Remove prior version of ExtProps, if it exists. IF EXISTS ( SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = ’ExtProps’ ) DROP TABLE ExtProps GO --Create ExtProps. CREATE TABLE ExtProps ( PropID int, PropName nvarchar(20), ) --Populate ExtProps with values. INSERT INTO ExtProps VALUES(1, ’Birthday’) INSERT INTO ExtProps VALUES(2, ’Salary’) INSERT INTO ExtProps Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
VALUES(3, ’Bonus’) INSERT INTO ExtProps VALUES(4, ’Favorite Sport’) INSERT INTO ExtProps VALUES(5, ’Favorite Store’) GO --Drop primary key for ContactExtProps. EXEC sp_pkeys ContactExtProps, dbo, Chapter02 IF @@ROWCOUNT > 0 ALTER TABLE ContactExtProps DROP CONSTRAINT pk_ContactExtProps_ContactID_PropID GO --Add Primary Key based on ContactID and PropID. ALTER TABLE ContactExtProps ADD CONSTRAINT pk_ContactExtProps_ContactID_PropID PRIMARY KEY NONCLUSTERED ( ContactID, PropID ) --List primary key columns in ContactExtProps. EXEC sp_pkeys ’ContactExtProps’ The preceding script closes by inv ok ing t he sp_pkeys syst em st ored pr ocedur e. The out put from t he procedur e appears in Figur e 2- 7. Not ice t hat it cont ains t wo rows— one for each colum n t hat cont r ibut es t o t he pr im ary key for t he Cont act Ext Pr ops t able. Figu r e 2 - 7 . Ou t p ut fr om t h e sp_ pk eys syst e m st or ed pr oced u r e t h a t sh ow s a pr im ar y k ey d efin ed on t w o colu m n s. Fore ig n Keys Foreign keys ar e colum n values in one t able t hat point t o t he pr im ary k ey or unique k ey in anot her t able. Specifying a for eign k ey enforces refer ent ial int egr it y bet ween t he t wo t ables. Refer ent ial int egr it y r equir es all new r ecords added t o t he t able wit h t he for eign k ey t o m at ch eit her a prim ary or a unique key value in t he ot her t able if it isn’t null. You can opt ionally specify act ions t o occur when y ou updat e or rem ov e a pr im ary or unique k ey in t he t able on t he ot her end of t he for eign k ey relat ionship. Specifically, you can cascade t he change fr om t he t able wit h t he pr im ary or unique k ey t o t he one w it h t he for eign k ey. Alt er nat ively, y ou can choose no act ion t o occur in t he t able wit h t he foreign k ey as a consequence of updat es t o t he t able wit h t he pr im ary or unique k ey. The follow ing scr ipt adds a couple of for eign k eys t o t he Cont act Ext Props t able. The first foreign key uses t he Cont act I D in t he Cont act Ext Props t able t o refer t o t he prim ary key for t he Em ailCont act s t able. The second for eign k ey uses t he Cont act Ext Pr ops t able v ia it s PropI D colum n values t o r efer t o t he Ex t Pr ops t able. Because t he Ext Props t able doesn’t init ially hav e a pr im ary or a unique k ey, t he t able cannot part icipat e in a foreign key r elat ionship. Ther efor e, t he script first adds const raint s t o t he PropI D colum n in Ext Props so t hat it serv es as t he t able’s prim ary k ey. Then it declar es t he for eign k ey r elat ionship bet w een t he Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
Cont act Ext Pr ops t able and t he Ext Pr ops t able. Alt hough t he first for eign key doesn’t declare any cascading act ion, t he declarat ion for t he second foreign key specifies cascading updat es. The script sam ple illust rat es t he sy nt ax for designat ing cascading updat es in it s declarat ion. Aft er t he second foreign k ey declarat ion, t he script t est s t he cascading updat e behav ior by m ak ing a change t o a Pr opI D value in t he Ex t Props t able and t hen v er ify ing t hat t he updat e cascades t o t he corr esponding PropI D value in t he Cont act Ext Pr ops t able. The script sam ple concludes by rest oring t he values and t he dat abase design t o t heir form er st at e befor e t he addit ion of eit her for eign k ey. This m ak es it possible t o r erun t he script wit hout any m anual set up act ivit y bet ween runs. You add a for eign k ey t o a t able as a const raint . The synt ax for perfor m ing t his t ask has at least t hr ee st eps, and it can hav e m or e if you specify a cascading act ion. Begin t he foreign k ey declarat ion inside an ALTER TABLE st at em ent . Aft er you open t he ALTER TABLE st at em ent , t he first st ep is t o indicat e t hat you want t o add a const raint w it h t he ADD and CONSTRAI NT k ey words. You can, opt ionally, assign a nam e t o t he foreign k ey const raint . Next add t he FOREI GN KEY k ey word and follow it wit h parent heses cont aining t he nam es of t he colum ns from t he cur rent t able part icipat ing in t he r elat ionship. Third add REFERENCES as a keyword. Follow t his k eyw ord w it h t he nam e of t he t able t o which t he relat ionship refers. Then, in parent heses aft er t he t able nam e, add t he colum n nam es fr om t hat t able t hat part icipat e in t he r elat ionship. By default , updat e and delet e act ions don’t cascade fr om t he t able wit h t he unique key or prim ary key t o t he t able wit h t he for eign k ey . How ev er, y ou can opt ionally add an ON UPDATE or ON DELETE clause t o t he for eign k ey declarat ion. I nclude in eit her clause CASCADE t o t ransfer t he act ion from t he t able wit h t he prim ary or unique k ey t o t he one w it h t he foreign key. --ForeignKeysSamples --Beginning of first FOREIGN KEY sample. USE Chapter02 --Remove FOREIGN KEY constraint if it exists already. EXEC sp_fkeys @fktable_name = N’ContactExtProps’ IF @@ROWCOUNT > 0 BEGIN ALTER TABLE ContactExtProps DROP CONSTRAINT ContactExtProps_fkey_ContactID END --Then, add a new FOREIGN KEY constraint. ALTER TABLE ContactExtProps ADD CONSTRAINT ContactExtProps_fkey_ContactID FOREIGN KEY (ContactID) REFERENCES EmailContacts(ContactID) --Verify addition of new constraint. EXEC sp_fkeys @fktable_name = N’ContactExtProps’ --End of first FOREIGN KEY sample --Beginning of second FOREIGN KEY sample. --Convert PropID in ExtProps to NOT NULL. ALTER TABLE ExtProps ALTER COLUMN PropID int NOT NULL GO --Then, define a primary key on PropID. ALTER TABLE ExtProps ADD CONSTRAINT pk_PropID PRIMARY KEY CLUSTERED (PropID) GO Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
ALTER TABLE ContactExtProps ADD CONSTRAINT ContactExtProps_fkey_PropID FOREIGN KEY (PropID) REFERENCES ExtProps(PropID) ON UPDATE CASCADE --Verify addition of new constraint. EXEC sp_fkeys @fktable_name = N’ContactExtProps’ GO --List ExtProps and ContactExtProps rows before --update to ExtProps. SELECT * FROM ExtProps SELECT * FROM ContactExtProps --Then, make a change in ExtProps that --cascades to ContactExtProps. UPDATE ExtProps SET PropID = 50 WHERE PropID = 5 --List ExtProps and ContactExtProps rows after --update to ExtProps. SELECT * FROM ExtProps SELECT * FROM ContactExtProps GO --End of second FOREIGN KEY sample. --Do cleanup chores. --Start to restore by resetting PropID values. UPDATE ExtProps SET PropID = 5 WHERE PropID = 50 --Next, drop FOREIGN KEY constraints. ALTER TABLE ContactExtProps DROP CONSTRAINT ContactExtProps_fkey_ContactID ALTER TABLE ContactExtProps DROP CONSTRAINT ContactExtProps_fkey_PropID --Then, drop PRIMARY KEY constraint first . ALTER TABLE ExtProps DROP CONSTRAINT pk_PropID --Finally, restore NULL setting for column. ALTER TABLE ExtProps ALTER COLUMN PropID int NULL GO --End of restore from second FOREIGN KEY sample. Figur e 2- 8 shows t wo excerpt s fr om t he pr eceding script ’s out put . The t op panel shows t he Ext Props t able rows ov er t he Cont act Ext Props t able r ows. This is befor e an updat e of t he PropI D value 5 t o a new value of 50 in t he Ex t Pr ops t able. The bot t om panel shows t he sam e t w o t ables aft er t he updat e of t he value in t he Ext Pr ops t able. Not ice t hat t he change t o t he Ext Props t able cascades t o t he Cont act Ext Props t able. Figu r e 2 - 8 . Th e t op an d bot t om pa n e ls sh ow t h e Ext Pr ops t a ble ove r t h e Con t a ct Ext Pr op s t a ble b efore a n d a ft er a ch a n ge t o t h e Ex t Pr op s t ab le . Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
I n de x e s Many dat abases can achieve perform ance gains t hrough t he addit ion of an index . I ndexes are gr eat at speeding look ups and sort s. On t he ot her hand, t her e are t im es w hen t he ov er head associat ed w it h m aint aining an index can slow an applicat ion. This is part icular ly t rue w hen one or m or e indexes over lap wit h a clust er ed pr im ar y k ey . Oft en dev elopers and adm inist rat ors hav e t o r esort t o t im ing r uns for t y pical t asks t o det erm ine t he best configurat ion of indexes for a dat abase applicat ion. Wit h t his in m ind, t he value of being able t o add and drop index es program m at ically is considerable as y ou perform y our t im ing r uns t o discer n t he opt im al index configurat ion. The last script in t his chapt er illust rat es sev eral t echniques for w ork ing wit h index es t hat y ou ar e lik ely t o find useful. The script begins by creat ing a user - defined st ored procedure, List UserDefinedI ndex es, t hat list s t he index es for user - defined t ables in a dat abase. ( You’ll r ead m uch m or e about st or ed procedures in Chapt er 4.) See Figur e 2- 9 for sam ple out put . This procedure draws on bot h t he sysobj ect s and sysindex es t ables— t wo syst em cat alog t ables. While y ou should generally av oid m anipulat ing syst em t ables, som e advanced dev eloper s find it useful t o do so. The Nam e colum n from t he sysobj ect s t able ( sysobj ect s.nam e) ret ur ns t he t able for an index , and t he Nam e colum n from t he sysindex es t able Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
( sysindex es.nam e) is t he nam e for a specific index in a t able ( if t her e is one) . The indid colum n present s t he index ident ifier colum n values. An indid value of 1 indicat es a clust er ed index, such as one cr eat ed wit h t he CREATE I NDEX st at em ent or one associat ed w it h a prim ary k ey . Values of indid bet ween 2 and 250 ar e for nonclust ered indexes. An indid value of 0 indicat es t her e is no clust er ed index for a t able. The indid colum n v alue also conv eys infor m at ion about t ables cont aining large dat a t ypes, such as t ext , nt ext , and im age. See t he “Table and I ndex Archit ect ur e” t opic in Books Online for addit ional det ail. N ot e I nst ead of using t he List User DefinedI ndexes st or ed procedure in t he scr ipt below, y ou can use t he syst em st or ed procedure sp_helpindex t o collect infor m at ion about indexes. This syst em st or ed procedure works sim ilarly t o sp_pkeys and sp_fkey s, but it pr ov ides infor m at ion for indexes. However, List User DefinedI ndexes gives you ex posur e t o t echniques for w ork ing w it h syst em cat alog t ables, which ar e a rich source of cont ent about a dat abase’s design. You can add an index t o a t able w it h t he CREATE I NDEX st at em ent . The list ing below init ially dem onst r at es t he synt ax for creat ing an index based on one colum n. Follow CREATE I NDEX wit h t he nam e of your index . Then follow t he index nam e w it h an ON clause. I n t he ON clause, include t he t able nam e wit h t he colum n or colum ns for t he index. Place t he colum n nam e in par ent heses aft er t he t able’s nam e. The sam ple illust rat es t he applicat ion of t he CREATE I NDEX sy nt ax t w ice. The first use of t he st at em ent is for adding an index based on t he Last Nam e colum n in t he Em ailCont act s t able. This exam ple dem onst rat es how t o use t he CREATE I NDEX st at em ent as described in t he pr eceding paragr aph. A second applicat ion of t he st at em ent shows how t o creat e a unique index based on t w o colum ns from t he Cont act Ext Pr ops t able— nam ely, Cont act I D and PropI D. The synt ax for t his exam ple uses t he UNI QUE k eyw ord. This k eyw ord is appr opriat e for a t able wit h a candidat e k ey because it specifies a second index t hat is unique for each record besides t he pr im ary key . I n ot her words, t he colum n( s) cont r ibut ing t o a unique index ar e candidat es for t he pr im ary key. By default , t he CREATE I NDEX st at em ent generat es nonclust ered indexes. Howev er , y ou can insert CLUSTERED aft er eit her CREATE or UNI QUE ( if it is pr esent ) t o m ak e a clust er ed index. Use t he DROP I NDEX st at em ent t o rem ov e a user - defined index ( for ex am ple, one you cr eat e w it h t he CREATE I NDEX st at em ent ) . The sy nt ax for t he DROP I NDEX st at em ent uses a t wo- part nam e t o designat e t he index t o dr op. The first part is t he t able nam e, and t he second part is t he index nam e. A per iod delim it s t he t wo part s. Our st or ed procedure list s t he index es for prim ary k eys and syst em - defined index es. You can delet e t he index for a pr im ar y k ey by dropping t he key. I f t he SQL Serv er set t ings for a ser ver perm it it , you can rem ove t he index for a for eign k ey dir ect ly from t he sysindex es t able. See t he “How t o set t he allow updat es opt ion ( Ent erpr ise Manager ) ” and “Err or 259” t opics in Books Online for m ore det ail on dir ect ly m anipulat ing syst em cat alog t ables, such as sysindexes. --IndexSamples USE Chapter02 --Create a stored procedure to list for user-defined --tables object name from sysobjects, and name and --indid from sysindexes. IF EXISTS(SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_NAME = ’ListUserDefinedIndexes’) DROP PROCEDURE ListUserDefinedIndexes Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
GO CREATE PROCEDURE ListUserDefinedIndexes AS SELECT sysobjects.id AS [sysobjects.id], sysindexes.id AS [sysindexes.id], sysobjects.name AS [sysobjects.name], sysindexes.name AS [sysindexes.name], sysindexes.indid FROM sysobjects INNER JOIN sysindexes ON sysobjects.id = dbo.sysindexes.id WHERE (LEFT(sysobjects.name, 3) ’sys’) AND (sysobjects.name N’dtproperties’) GO --List indexes data. EXEC ListUserDefinedIndexes --Create an Index for LastName in EmailContacts. CREATE INDEX ind_EmailContacts_LastName ON EmailContacts(LastName) --List indexes data. EXEC ListUserDefinedIndexes --Remove previously created index. DROP INDEX EmailContacts.ind_EmailContacts_LastName --Remove primary key for ContactExtProps based --on ContactID and PropID. EXEC sp_pkeys ContactExtProps, dbo, Chapter02 IF @@ROWCOUNT > 0 ALTER TABLE ContactExtProps DROP CONSTRAINT pk_ContactExtProps_ContactID_PropID GO --List indexes data. EXEC ListUserDefinedIndexes GO --Create an Index for LastName in EmailContacts. CREATE UNIQUE INDEX ind_ContactExtProps_ContactID_PropID ON ContactExtProps(ContactID, PropID) --List indexes data. EXEC ListUserDefinedIndexes GO --Attempt to enter a record with duplicate key values for --ContactID and PropID. INSERT INTO ContactExtProps Values (1, 1, ’Birthday’, ’9/9/1964’) GO --Remove previously created index. DROP INDEX ContactExtProps.ind_ContactExtProps_ContactID_PropID --List indexes data. EXEC ListUserDefinedIndexes GO --Insert and then delete record with duplicate values for --ContactID and PropID columns. INSERT INTO ContactExtProps Values (1, 1, ’Birthday’, ’9/9/1964’) Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
DELETE FROM ContactExtProps WHERE PropValue = ’9/9/1964’ GO Besides synt ax issues, t he pr eceding sam ple script illust rat es design issues for wor k ing w it h index es, such as t est ing t he behavior of a unique index. To isolat e t he effect of t he index, t he script drops a prim ar y k ey t hat requir es uniqueness on t he sam e t wo colum ns as t he ind_Cont act Ext Props_Cont act I D_PropI D index. The t est for t he validit y of t his unique index is an at t em pt t o ent er a record wit h a duplicat e k ey v alue. Aft er failing, t he script dr ops t he unique index and confirm s t hat y ou can add t he record if t he unique index isn’t present ; t he script closes by rem ov ing t he t est r ecor d. Figur e 2- 9 shows an ex cerpt from t he beginning of t he script w it h t he out put from t he first t wo uses of t he List UserDefinedI ndex es st ored pr ocedur e. The m ain point t o t ak e away from t he out put is t hat t he first list ing of index es doesn’t include a reference t o ind_Em ailCont act s_Last Nam e, but t he second one does. I n bet w een t he t wo r uns of t he List UserDefinedI ndex es st ored pr ocedure, t he script inv ok es t he CREATE I NDEX st at em ent t o gener at e t he index . The t w o result set s also show t he index es for clust ered and nonclust ered prim ar y k eys. For ex am ple, pk_Em ailCont act s_Cont act I D is a clust er ed pr im ary key; not ice t hat it s indid value is 1. The index for t he nonclust er ed pr im ary key , pk_Cont act Ext Pr ops_Cont act I D_PropI D, has an indid value of 2. Finally, t he _WA_Sys_PropI D_77BFCB91 index is for a foreign k ey fr om a preceding sam ple. SQL Ser ver didn’t rem ove t he index when t he script dr opped t he key. Figu re 2 - 9 . Th e se t w o re su lt set s iden t ify t h e a ddit ion of a n inde x, ind _ Em a ilCon t act s_ Last N am e , t o t h e Em a ilCon t act s t a ble . By con t ra st in g t h e fir st w it h t h e se con d list in g, you can se e t h e e ffe ct of t h e CREATE I N DEX st a t em e n t for ind _ Em a ilCon t act s_ La st N am e . Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
Cha pt e r 3 . Pr ogr a m m ing D a t a Acce ss w it h T- SQL This chapt er present s T- SQL program m ing t echniques for dat a access. You can use t hese t echniques in m any env ironm ent s— in Query Analyzer , encapsulat ed wit hin v iews, in st or ed procedur es and user - defined funct ions— and in Visual Basic .NET. When y ou finish wor k ing t hrough t his chapt er, y ou should possess a foundat ion for ext ract ing precisely t he dat a y ou need fr om a SQL Serv er dat abase for any applicat ion. The obj ect iv e of t his chapt er is t o dem y st ify T- SQL dat a access t echniques so t hat you can creat e T- SQL SELECT st at em ent s as easily as you used t o w rit e DAO and ADO dat a access code. Alt hough t he chapt er assum es you’r e wor k ing in Query Analyzer, t he t echniques y ou lear n w ill apply equally w hen y ou use T- SQL st at em ent s in Visual Basic .NET. The chapt er begins by int r oducing t he SELECT st at em ent and describing how t o filt er colum ns and rows from a row source, such as a t able. Next t he chapt er focuses on t echniques for aggr egat ing dat a acr oss a whole r ow source as well as specific groups w it hin t he row source. The chapt er explor es part icular t echniques for m oney and dat et im e var iables, and t he dat et im e t opic gains a sect ion of it s own. The concluding sect ion exam ines ways of com bining r ow sources wit h j oins and subquer ies. I f you hav e had difficult y underst anding j oins befor e, spend som e t im e w it h t he script sam ples in t he chapt er and t he accom panying com m ent ary t o build y our grasp of t his im port ant capabilit y. N ot e By t he t erm r ow source, I refer t o a collect ion of rows from a dat abase. Alt hough t his can be a t able, it can also be a view based on one or m or e t ables. I n addit ion, a row sour ce can be t he r esult set gener at ed by a st ored pr ocedure or a t able- valued user- defined funct ion. The T- SQL sam ples for t his chapt er ar e available in an .sql file on t he com panion disk . You can use t he script s as st art ing point s for your own cust om ext rapolat ions of t he t echniques. You can r un all t he sam ples fr om Query Analyzer if y ou have t he Nort hwind and pubs dat abases inst alled on a SQL Ser ver inst ance t o w hich y ou can connect . I n t r odu ct ion t o D a t a Acce ss w it h T- SQL Creat ing efficient , speedy, and flex ible dat a access solut ions for SQL Ser ver dat a will inev it ably involve pr ogram m ing T- SQL. I n part icular, you w ill r equir e a firm foundat ion in t he design of SELECT st at em ent s. This sect ion int roduces t he SELECT st at em ent by review ing it s archit ect ur e. You’ll find code sam ples designed t o illust rat e t he basic operat ion of t he st at em ent ’s m ain elem ent s, including t he SELECT list as well as t he FROM and WHERE clauses, and you’ll be int r oduced t o t he t opic of calculat ed colum ns. Ov e r vie w of t h e SELECT St a t e m e n t Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.