inform ation about views as well as tables. Specify a
TABLE_TYPE colum n value of VI EW in the WHERE clause for
a SELECT st at em ent t o return only views.
Wit h t he COLUMNS view 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-
lication. I t also r eveals a new sy nt ax for specifying t he database serving as the
source for t he view. Not ice t hat t he specificat ion of t he view nam e has t hr ee
parts. The first of t hese is t he dat abase nam e— Chapt er02. Designat ing a
database nam e as t he first part rem ov es t he need to designat e a dat abase
cont ext w it h a USE st at em ent . This is because no m at ter what dat abase cont ext
the st at em ent execut es, it always ext ract s infor m at ion from t he dat abase— that
is, the first part of t he I NFORMATI ON_SCHEMA view nam e. The second and t hird
part s follow t he convent ion for t he preceding bat ches except for t he nam e of t he
specific I NFORMATI ON_SCHEMA view ( COLUMNS) . The sam ple also includes a
WHERE clause t o reference a part icular table— in particu lar, Em ailCont act s.
Wit hout t he WHERE clause, t he T- SQL st at em ent in the bat ch will ret urn
inform at ion for all t he colum ns w it hin t he Chapt er02 dat abase, including t hose
from sy st em and user- defined t ables.
The final bat ch shows t he I NFORMATI ON_SCHEMA syntax for r eporting about t he
keys in a dat abase. These include t he prim ar y k eys, for eign k eys, and unique
keys. The inform at ion is really 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 restrict s t he result only t o keys
for t he Em ailContact s table.
--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

shows t he syst em -generat ed nam e for t he table’s prim ary key in t he
CONSTRAI NT_ NAME colum n. A subsequent sam ple in t he “ Script ing Keys and
I ndexes” sect ion illust rat es t he sy nt ax for assigning a specific nam e to a prim ary
key.
Figure 2 - 2 . Sa m ple out pu t from a se t of four T- SQL ba tche s illust r at in g
t he b eh avior of I NFORM ATI ON _ SCH EM A view s.

I NFORMATI ON_ SCHEMA offers m any m ore v iews besides those 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 values, t able const raints, st ored procedures, and user-
defined funct ions. Refer t o t he “I nform at ion Schem a View” t opic in Books Online
for an overv iew of t he I NFORMATI ON_SCHEMA views along wit h links defining t he
result set for each t ype of view available.
W ork ing w it h Colum n Da t a Type s
The “Creat ing a Table” sect ion int roduced t he CREATE TABLE st at em ent sy nt ax
and dem onst rated how t o declar e t ypical sy st em dat a t ypes such as int and
nvarchar. Apply ing this fram ework w ill enable you to assign t he ot her dat a types
to colum ns as well. I n spit e of t he sim plicit y of t he ov erall approach, t here are
special issu es for som e data t ypes, and one dat a t ype hasn ’t been covered yet .
This sect ion r eviews t hese issu es.
Com p aring t im e st am p an d da tet im e Dat a Types
Those w ho are m igrat ing t o SQL Server m ay be confused at first by t he tim e-
st am p dat a t ype and w het her it has anyt hing t o do w it h datet im e dat a ( it
doesn’t ). The rowversion alias for t im est am p act ually sum m arizes the purpose of
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
the t im est am p dat a t ype m ore precisely. This m ay be one reason why Micr osoft
plans to use t he rowv ersion nam e m ore prom inent ly in t he fut ure.
The following script cont rast s t he t im est am p and dat et im e dat a t ypes. The
cont rast relies on t wo t ables, t 1 and t 2, each w it h t hree colum ns, col1, col2, and
col3. The col1 colum n has an int dat a t ype and offers a value for

(
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.

types in t he sam e colum n. This capability is useful for st oring a collect ion of
values in a colum n in which you don’t know in advance what types of values you’ll
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
have. This can arise in a sit uat ion in which you let a user define values on an ad
hoc basis.
Consider a t able t hat st or es m iscellaneous inform at ion about cont act s. Som et im e
your applicat ion m ay need t o st ore 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 yet ot her cases, your application m ay need t o
designat e a variable- lengt h charact er value. This kind of scenar io is t ypical of
sit uat ions in which your application needs to charact erize elem ent s but t he
com plet e set of elem ent s and their at t ribut es isn’t known at t he t im e t hat you
develop t he applicat ion.
The follow ing script assigns a set of ext ended propert ies t o a t able of cont act s
ident ified by a Cont actI D colum n. Not ice t hat t he CREATE TABLE st at em ent uses
t hr ee colum ns t o charact erize t he cont act s. The m ost im port ant colum n is
PropValue, w hich has a sql_variant dat a t ype. This colum n st ores t he act ual value
t hat charact erizes 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 favorit e sport or st ore. PropI D and PropNam e
describe t he charact erist ic for t he cont act . PropNam e m ak es it easy t o follow
what t he PropValue colum n values describe wit hout r equiring 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 ExtProps table and link it t o ot her tables cont aining cont act and propert y
nam es. I n addit ion, t hat sam ple w ill add a prim ary key t o t he t able. These
refinem ent s aren’t necessary t o dem onst rat e t he behavior of sql_variant dat a
t ypes.
The I NSERT I NTO st at em ent s t hat add values to t he Pr opValue colum n use CAST
funct ions to est ablish sub dat a t ypes w it hin t he sql_variant 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_variant
dat a t ype t o accept m ult iple ot her dat a t ypes.

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 <=3
GO

--This SELECT fails because sql_variant doesn’t implicitly

draws on one or m ore other colum ns wit hin the table. You can specify a
com puted colum n wit 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 addition, com puted colum ns can part icipat e in t he definition of an index
or prim ary key. You can also use a com put ed colum n in t he definit ion of a
UNI QUE const raint. When you’re using a com put ed colum n t o help define a
prim ary key or an index, t he expression m ust be det erm inist ic. I n ot her words,
the expression m ust generat e t he sam e result all t he tim e based on t he sam e
input . An expression based on GETDATE isn’t appropriat e for a com put ed colum n
that will serve as a colum n for an index. This is because t he result will change
each t im e you open t he t able.
Despit e the wide range of uses for com put ed colum ns, t here are sev eral
circum st ances in which you cannot use t hem . For exam ple, you cannot sp ecify
nullabilit y for com put ed colum ns. This is because SQL Ser ver autom 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. Even non- nullable
input s can generat e null r esult s if an expression generat es an underflow or
ov erflow. I n addition, you 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 ents. Yet another applicat ion t hat
doesn’t perm it t he use of com put ed colum ns is that which defines FOREI GN KEY
and DEFAULT const raint s.
The follow ing script sam ple illust rat es t he sy ntax for sp ecifying 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 edDeliveryDat es table. The first colum n is
autoincr em ent ing, wit h default set tings for t he I DENTI TY colum n pr oper ty. The
second colum n has a dat et im e dat a t ype for accept ing order dat es. The third
colum n is a com put ed colum n. The expression for t he colum n uses the Dat eAdd
funct ion to com pute a proj ect ed delivery dat e based on t he t able’s OrderDat e
colum n.
N ot e

--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 Proj ect edDeliveryDat es table wit h t wo
separat e SELECT queries. The first SELECT query st at em ent dem onst rat es t he
com puted colum n as part of t he list for t he st at em ent. For this st at em ent, t he
Proj ect edDeliveryDat e colum n displays bot h t he dat e and t he t im e. However,
your applicat ion m ay require j ust t he dat e. The second query stat em ent shows
how t o cr op t he t im e value out of t he display. Figure 2- 5 present s the output
from bot h SELECT st at em ent s.
Figure 2 - 5 . Th is exam ple sh ow s t he u se of a com pu t ed colu m n to disp la y
a pr oj ect e d d at e for t he de livery of an or de r in eit he r of t w o
re pre se nt at ions—on e t ha t inclu des a t im e a nd an ot h er t h at show s on ly a
date .
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.

The CHECK keyword specifies t he t ype of const raint. Finally, t he expression
trailing t he CHECK keyw ord repr esents t he condit ion for which t he ch eck
const raint t est s. I n t he sam ple script , t he const raint evaluat es t he Email1 value
to ensure t hat it cont ains t he @ sy m bol. E-m ail addresses t hat don’t include t his
sym bol are invalid.
--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

the sy st em or by a user . The follow ing script sam ple re-creat es t he Em ailCont act s
table. I f you check t he sam ple in t hat sect ion, you w ill observe t hat the prim ary
key declarat ion doesn’t include a nam e for t he prim ary key. The follow ing script
re- creat es t he generat ion of t he Em ailCont act s table, but t his sam ple does
explicit ly nam e t he prim ary key. The sam ple also dem onst rat es t he use of t he
sp_pkeys syst em st ored procedure— once before dropping t he first version of the
Em ailCont act s t able and a second t im e aft er creat ing a new version of the t able
wit h a user- defined nam e for t he prim ary key. The sp_pkeys syst em st ored
procedure has a result set w it h a separat e row for each colum n in t he prim ary
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
key. Th e colum ns of t he resu lt set report such it em s as t he dat abase nam e, t he
table nam e, and t he prim ary key nam e.
The prim ary key declaration in t his sect ion perform s ident ically to t he one in t he
“Creating 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 the CONSTRAI NT keyword. This is opt ional
for a prim ary key, but it s use can rem ind you t hat t he prim ary key is a m em ber
of t he fam ily of const raints, including check const raint s and foreign key
const raint s. Th e nam e for t he prim ary key appears im m ediat ely aft er t he
CONSTRAI NT keyword. The follow ing scr ipt also explicit ly declares t he prim ary
key as clust ered. You can replace t he k eyword CLUSTERED wit h NONCLUSTERED
to avoid physically ordering t he records in t he t able according t o Cont actI 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

--List primary key columns in EmailContacts.
EXEC sp_pkeys ’EmailContacts’

Figure 2- 6 sh ows the out put from t he preceding script . Th e results below the first
and t hird colum n headers reveal t he output from t he sp_pkeys syst em st ored
procedure before and after t he nam ing of t he prim ary key . The first set of colum n
headers shows the system defined nam e for t he prim ary key. The t hird set of
colum n headers shows t he out put from t he sp _keys st ored pr ocedure after t he
assignm ent of a nam e t o the prim ary 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 row of Figure 2-6 m at ches t he nam e assigned t o t he prim ary key in the
preceding scr ipt .
Figure 2 - 6 . Sa m ple out pu t de m on st ra t ing pr im ary key nam es a ssign ed by
the syste m ( t op r ow ) a nd by the pr ece ding scr ip t ( bot t om r ow ) .

Recall t hat t he “Using sql_var iant Dat a Ty pe Values” sect ion init ially creat ed t he
Cont act ExtProps table. 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 Props t able includes a
colum n, PropI D, designed t o link to anot her t able t hat defines nam es t o m at ch
the PropI D values. The next scr ipt cr eat es a table, ExtProps, t hat m at ches t he
PropI D int values with nam es in a colum n of variable- lengt h charact er st rings.
The script t hen proceeds to use t he sp_pkeys syst em st or ed pr ocedure t o
det erm ine w het her a prim ary key colum n is already in t he Cont act ExtProps t able.
A value of 0 for @@ROWCOUNT specifies no prim ary key. I f t he value is greater
than 0, t he procedure drops the ex ist ing pr im ary k ey. Next t he procedure uses an
ALTER TABLE st atem ent t o creat e a new pr im ary key 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
have m ult iple propert ies but no m ore t han one setting for any one propert y. The
foreign key sam ple in t he next sect ion w ill dem onst rat e how t o link t he
Cont act ExtProps table t o the Em ailCont act s and Ext Pr ops t ables.

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 scr ipt closes by invoking t he sp_pkeys sy st em st ored procedure.
The out put from t he procedure appears in Figure 2- 7. Not ice t hat it cont ains t wo
rows— one for each colum n t hat cont ribut es t o t he prim ary key for t he
Cont act ExtProps table.
Figure 2 - 7 . Ou t put from t he sp_ pkeys syst em stored procedur e th a t

st at e befor e t he addit ion of eit her for eign k ey. This m akes it possible t o rerun t he
script w it hout any m anual set up act ivit y bet w een runs.
You add a foreign k ey t o a t able as a const raint . The synt ax for perform ing t his
t ask has at least t hree st eps, and it can have m ore if you specify a cascading
act ion. Begin t he foreign key 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 to indicate t hat you want
t o add a const raint w it h t he ADD and CONSTRAI NT keywords. You can,
optionally, assign a nam e t o t he foreign key const raint . Next add t he FOREI GN
KEY keyword 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. Th ird add REFERENCES as
a keyword. Follow t his keyw ord w it h t he nam e of t he t able t o which the
relat ionsh ip refers. Then, in parent heses after t he t able nam e, add t he colum n
nam es from t hat t able t hat part icipat e in t he relat ionship. By default , updat e and
delet e act ions don’t cascade from 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 key. How ever, y ou can opt ionally add an ON UPDATE or
ON DELETE clause t o t he foreign key 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 key t o
t he one with 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


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

indexes program m at ically is considerable as you perform your t im ing runs t o
discern t he opt im al index configurat ion.
The last script in this chapter illust rat es several t echniques for work ing w it h
indexes t hat you are likely t o find useful. The scr ipt begins by creat ing a user-
defined stored procedure, List UserDefinedI ndexes, t hat list s the indexes for user -
defined t ables in a dat abase. ( You’ll r ead m uch m ore about st or ed procedures in
Chapt er 4.) See Figure 2- 9 for sam ple out put . This procedure draws on bot h t he
sysobj ect s and sy sindexes tables— t wo sy st em catalog t ables. While you should
generally av oid m anipulat ing syst em tables, som e advanced developers find it
useful to do so. The Nam e colum n from t he sysobj ect s t able ( sysobj ect s. nam e)
ret ur ns the t able for an index, and t he Nam e colum n from t he sysindexes table
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
(sysindex es.nam e) is the nam e for a specific index in a table ( if t here is one) . The
indid colum n presents t he index ident ifier colum n values. An indid value of 1
indicates a clust ered index, such as one creat ed wit h t he CREATE I NDEX
st at em ent or one associat ed w it h a prim ary key. Values of indid bet ween 2 and
250 are for nonclust ered indexes. An indid value of 0 indicates t here is no
clust ered index for a table. The indid colum n value also conveys inform at ion
about t ables containing large dat a t ypes, such as t ext , ntext , and im age. See t he
“Table and I ndex Archit ect ur e” t opic in Books Online for additional det ail.
N ot e
I nstead of using the List UserDefinedI ndexes stored
procedure in t he script below, you can use t he sy st em st ored
procedure sp_helpindex to collect inform at ion about indexes.
This syst em st ored procedure works sim ilarly to sp_pkeys
and sp_fkeys, but it provides information for indexes.
However, List UserDefinedI ndexes gives you exposure t o
techniques for w orking wit h syst em catalog tables, which are
a rich source of cont ent about a database’s design.
You can add an index t o a table w it h t he CREATE I NDEX st at em ent . The list ing

--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



--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 preceding sam ple script illust rat es design issues for
wor king wit h indexes, such as t est ing t he behavior of a unique index. To isolat e
the effect of t he index, t he scr ipt drops a prim ary key t hat requires uniqueness on
the sam e t wo colum ns as the ind_Cont act ExtProps_Cont act I D_PropI D index. The
test 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 key value. After failing, t he script drops t he unique index and confirm s
that you can add t he record if t he unique index isn’t present ; t he script closes by
rem oving t he t est recor d.
Figure 2- 9 sh ows an ex cerpt from t he beginning of t he script w it h t he output from
the first t wo uses of t he List UserDefinedI ndexes st ored procedure. The m ain point
to t ake 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_EmailCont act s_Last Nam e, but t he second one does. I n bet ween
the t wo runs of t he List UserDefinedI ndexes st ored procedure, t he scr ipt invokes
the CREATE I NDEX st at em ent t o generat e t he index . The t wo result set s also
show t he indexes for clust ered and nonclust ered prim ary keys. For exam ple,
pk_Em ailCont act s_Cont act I D is a clust er ed prim ary key; not ice t hat it s indid
value is 1. The index for the nonclust ered pr im ary key,
pk_Contact ExtProps_Cont act I D_PropI D, has an indid value of 2. Finally, t he

and subqueries. I f you hav e had difficult y underst anding j oins befor e, spend
som e t im e wit h t he scr ipt sam ples in the chapt er and t he accom panying
com m ent ary t o build your grasp of t his im port ant capabilit y.
N ot e
By the term row source, I refer t o a collect ion of rows from a
dat abase. Although this can be a t able, it can also be a view
based on one or m ore t ables. I n addit ion, a row source can
be t he result set generated by a st ored procedure or a t able-
valued user- defined funct ion.
The T- SQL sam ples for t his chapter are available in an .sql file on t he com panion
disk . You can use the script s as start ing point s for your own cust om
ext rapolat ions of t he t echniques. You can run all t he sam ples from Query
Analyzer if y ou have the Nort hwind and pubs dat abases inst alled on a SQL Ser ver
inst ance t o which you can connect . I nt roduct ion to Da ta Access w it h T- SQL
Creat ing efficient , speedy, and flex ible dat a access solut ions for SQL Server dat a
will inev it ably involve program m ing T- SQL. I n part icular, you will r equire 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 reviewing it s archit ect ure. 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 roduced to t he t opic of calculat ed colum ns.
Ov erview of t he SELECT St a t em ent
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.