[ Pobierz całość w formacie PDF ]
.EXEC sp_fkeys @fktable_name = N ContactExtPropsGO--List ExtProps and ContactExtProps rows before--update to ExtProps.SELECT * FROM ExtPropsSELECT * FROM ContactExtProps--Then, make a change in ExtProps that--cascades to ContactExtProps.UPDATE ExtPropsSET PropID = 50 WHERE PropID = 5--List ExtProps and ContactExtProps rows after--update to ExtProps.SELECT * FROM ExtPropsSELECT * FROM ContactExtPropsGO--End of second FOREIGN KEY sample.--Do cleanup chores.--Start to restore by resetting PropID values.UPDATE ExtPropsSET PropID = 5 WHERE PropID = 50--Next, drop FOREIGN KEY constraints.ALTER TABLE ContactExtPropsDROP CONSTRAINT ContactExtProps_fkey_ContactIDALTER TABLE ContactExtPropsDROP CONSTRAINT ContactExtProps_fkey_PropID--Then, drop PRIMARY KEY constraint first.ALTER TABLE ExtPropsDROP CONSTRAINT pk_PropID--Finally, restore NULL setting for column.ALTER TABLE ExtPropsALTER COLUMN PropID int NULLGO--End of restore from second FOREIGN KEY sample.Figure 2-8 shows two excerpts from the preceding script s output.The top panelshows the ExtProps table rows over the ContactExtProps table rows.This isbefore an update of the PropI D value 5 to a new value of 50 in the ExtPropstable.The bottom panel shows the sam e two tables after the update of the valuein the ExtProps table.Notice that the change to the ExtProps table cascades tothe ContactExtProps table.Figure 2 - 8.The top and bottom panels show the ExtProps table over theContactExtProps table before and after a change to the ExtProps table. I ndexesMany databases can achieve perform ance gains through the addition of an index.I ndexes are great at speeding lookups and sorts.On the other hand, there aretim es when the overhead associated with m aintaining an index can slow anapplication.This is particularly true when one or m ore indexes overlap with aclustered prim ary key.Often developers and adm inistrators have to resort totim ing runs for typical tasks to determ ine the best configuration of indexes for adatabase application.With this in m ind, the value of being able to add and dropindexes program m atically is considerable as you perform your tim ing runs todiscern the optim al index configuration.The last script in this chapter illustrates several techniques for working withindexes that you are likely to find useful.The script begins by creating a user-defined stored procedure, ListUserDefinedI ndexes, that lists the indexes for user-defined tables in a database.(You ll read m uch m ore about stored procedures inChapter 4.) See Figure 2-9 for sam ple output.This procedure draws on both thesysobjects and sysindexes tables two system catalog tables.While you shouldgenerally avoid m anipulating system tables, som e advanced developers find ituseful to do so.The Nam e colum n from the sysobjects table (sysobj ects.nam e)returns the table for an index, and the Nam e colum n from the sysindexes table (sysindexes.nam e) is the nam e for a specific index in a table (if there is one).Theindid colum n presents the index identifier colum n values.An indid value of 1indicates a clustered index, such as one created with the CREATE I NDEXstatem ent or one associated with a prim ary key.Values of indid between 2 and250 are for nonclustered indexes.An indid value of 0 indicates there is noclustered index for a table.The indid colum n value also conveys inform ationabout tables containing large data types, such as text, ntext, and im age.See the Table and I ndex Architecture topic in Books Online for additional detail.NoteInstead of using the ListUserDefinedI ndexes storedprocedure in the script below, you can use the system storedprocedure sp_helpindex to collect information about indexes.This system stored procedure works sim ilarly to sp_pkeysand sp_fkeys, but it provides information for indexes.However, ListUserDefinedI ndexes gives you exposure totechniques for working with system catalog tables, which area rich source of content about a database s design.You can add an index to a table with the CREATE I NDEX statem ent.The listingbelow initially dem onstrates the syntax for creating an index based on onecolum n.Follow CREATE I NDEX with the nam e of your index.Then follow theindex nam e with an ON clause.I n the ON clause, include the table nam e with thecolum n or colum ns for the index.Place the colum n nam e in parentheses after thetable s nam e.The sam ple illustrates the application of the CREATE I NDEX syntax twice.The firstuse of the statem ent is for adding an index based on the LastNam e colum n in theEm ailContacts table.This exam ple dem onstrates how to use the CREATE I NDEXstatem ent as described in the preceding paragraph.A second application of thestatem ent shows how to create a unique index based on two colum ns from theContactExtProps table nam ely, ContactI D and PropI D
[ Pobierz całość w formacie PDF ]