|
|
 |
|
|
|
Microsoft® SQL Server™ 2000 Programming Step by Step
|
|
|
Author
|
|
Rebecca Riordan
|
|
|
Pages
|
720
|
|
Disk
|
1 Companion CD(s)
|
|
Level
|
Intermediate
|
|
Published
|
11/29/2000
|
|
ISBN
|
9780735611429
|
|
Price
|
$49.99
To see this book's discounted price, select a reseller below.
|
|
|
|
|
|
|
|
|
Index
Note: Italicized page references indicate figures, tables, or code listings.
Symbols
+ (addition operator), 499–500
= (assignment operator), 507–9
* (asterisk), 218
& (bitwise AND), 505
^ (bitwise exclusive OR), 505
~ (bitwise NOT), 505
| (bitwise OR), 505
[] (brackets, delimiting), 492
[] (brackets, wildcard), 249
/*..*/ (comment operators), 497
+ (concatenation operator), 227, 507–9
/ (division operator), 499–500
-- (double dash), 497
= (equal to operator), 501–2
# (hash sign), 530
_ (underline), 248
@ (variable indicator), 611, 622
% (wildcard operator), 248
A
ABSOLUTE, 584–86, 595
absolute values, 513
Access Administrator role, 65
Access databases, importing, 378–88
access rights, 521
ADD, 455–56
adding. See also creating; inserting
columns to tables, 97–107, 455–56
columns using database diagrams, 197–99, 203
columns using Editor Pane, 432–34
database objects to Editor Pane, 430–34, 440
indexes from Execution Plan Pane, 476–78
rows, 212–13, 215, 325–43
rows to table-valued variables, 549–50, 552
tables to database diagrams, 189–91, 194–95, 202
views to queries, 313, 322
addition operator (+), 499–500
administration, 19
backing up databases, 20–29
Database Maintenance Plan Wizard, 30–40
AFTER triggers, 624–25, 627–30, 635
aggregate functions, 289, 293, 516–18
alerts, 15
ALL keyword, 277–80, 282
Alter command, 435
altering. See also editing
columns, 457
constraints, 453
objects, 451–59
schema from Execution Plane Pane, 490
views, 452–55
ALTER statements, 451–59, 470
COLUMN, 452, 457–59
DATABASE, 451
DROP, 458
FUNCTION, 451
PROCEDURE, 451
syntax, 451–52
TABLE, 451–52, 455–57
TRIGGER, 452
VIEW, 452–55
AND, 254–56, 503–4
application names, function returning, 526
arc functions, 513
arithmetic operators, 498–500
ASC, 239
assignment operator (=), 507–9
asterisk (*), 218
attaching databases, 397–99, 406
attributes, physical implementation, 92
authentication
login ID checking, 42
mode selection for logins, 45–46
SQL Server Authentication, 6–7, 10, 42–46
Windows 98, 42
Windows Authentication, 6–7, 10, 42, 45–46
AUTHORIZATION, 443
averages, 226, 346, 516
AVG, 516
INSERT statement, with, 226
UPDATE statement, with, 346
B
BACKUP command, 495
Backup Operator role, 65
backups, 20–29
appending, 26
BACKUP command, 495
Backup Database command, 28
Create Database Backup Wizard, 21–28
database, choosing a, 23
Database Maintenance Plan Wizard, 30–40
destination, choosing a, 25, 36–37
differential backups, 20
full backups, 20
media sets, 28
operations disabled during, 20
restoring, 29
type of, choosing, 24, 34
verification, 26–28
batches, 420
BEGIN ... END statements, 554
within user-defined functions, 642, 646, 657
with WHILE, 564–70
BETWEEN, 248, 250–51, 256
bigint, 93
binary, 94
binary operators, 496
binding defaults to columns, 172–74, 186
binding rules to columns, 179–81, 186
bit, 93
bit packing, 505
bitwise operators, 505–7
blocks, 554
Boolean expressions, 158
brackets, delimiting ([]), 492
brackets, wildcard ([] ), 249
BREAK, 566–67, 570
Bulk Insert Administrators role, 49
BULK INSERT command, 494
business rules, using triggers to implement, 623–24
C
calculated columns, creating, 226–30, 233
calling user-defined functions, 648–52, 657
cardinality, 496
cascading changes, 142
deletes, 360–61, 369–72, 376
enabling, 145
case, functions for converting, 524
CASE statements, 557–61, 570
CEILING, 513
changing database objects, 470. See also ALTER statements
char, 94
character columns, creating, 105–7
character data types, 106
CHECK, 452
check constraints, 157–58
altering, 453
Check Existing Data On Creation option, 160
CREATE TABLE statements, 444–48
creating, 158–61, 167
deleting, 165–67
modifying text, 161–63, 167
Object Browser display of, 426
renaming, 163–64, 167
script commands for, 435
temporary tables, 530
triggers as alternatives, 624
clauses, Transact-SQL
FROM (see FROM clause)
GROUP BY (see GROUP BY clause)
HAVING, 293–97
TOP n, 231–33, 326, 346
WHERE (see WHERE clauses)
Client Statistics Pane, 480–81
closing cursors, 579, 595
closure, 300
CLUSTERED, 444–45
clustered indexes, 116
Codd, E. F., 140
collapsing Console Tree items, 18
color coding in query window, 416
COLUMN, 452, 457–59
column aliases, 223–25, 233
columns
adding, 97–107, 432–34, 455–56
aliases, 223–25, 233
altering, 452–53, 457
binding defaults to, 172–74, 186
binding rules to, 179–81, 186
calculated, creating, 226–30, 233
changing which are indexed, 133–35
character, creating, 105–7
choosing subsets of with SELECT statement, 220–23, 233
COLUMNS_UPDATE function, 632
computed, altering, 452
computing with user-defined functions, 652–57
constraints, altering, 453
CREATE TABLE statement syntax, 443
creating with key constraints, 444–48
Criteria field, 246
database diagrams, adding with, 197–99, 203
data types, table of, 93–94
date, creating, 103–5
DEFAULT, altering, 453
default objects, 170–74, 186
default values, 107
deleting, 109–10, 458–59
descriptions, 98
dropping, 458–59
fixed-length, 106
GROUP BY field, 290
GUID, creating, 101–3
identity, creating, 99–100
image data type, 452
indexed, 133–35, 453
indexing, 120
length, returning, 519
managing, 107–10
names, returning, 519
ntext data type, 452
numeric, creating, 97–99
Object Browser display of, 426
precision, 98–99
PRIMARY KEY, altering, 453
properties, returning, 519
renaming, 107–9
renaming for queries, 233
replicated, altering, 452
ROWGUIDCOL, 452
scale, 99
selecting all from a table, 218–20
selection for views, 303
Sort Type field, 238
specifying, 259
statistics, altering, 453
text data type, 452
timestamp data type, 452
UPDATE function, 632–34
user-defined data types, 182, 184–86
variable-length, 106
COLUMNS_UPDATED function, 632
commands, menu
Alter, 435
Backup Database, 28
Create, 435
Delete, 435
Drop, 435
Execute, 435
Exit, 18
Insert, 435
isqlw, 410
Manage Index, 476–78, 490
New Database, 78
New Login, 43
Replace Template Parameters, 466–67, 470
Restore Database, 29
Return All Rows, 218
Return Top, 231
Script, table of, 435
Script Object To New Window As, 463
Select, 435–37
Show Client Statistics, 480–81
Show Execution Plan, 475
Show Server Trace, 479–80, 490
Update, 435
commands, Transact-SQL
ALTER (see ALTER statements)
BACKUP, 495
BULK INSERT, 494
CREATE (see CREATE statements)
Database Administration, 495
DDL, table of, 494
DEALLOCATE, 579–80
DELETE (see DELETE statements)
DENY, 495
DML, table of, 494
DML commands, table of, 494
DROP (see DROP statements)
FETCH, 580–89
GO, 420
GRANT, 495
INSERT (see INSERT statements)
KILL, 495
RESTORE, 495
REVOKE, 495
SELECT (see SELECT statements)
SET, 495
SHUTDOWN, 495
TRUNCATE TABLE, 372–76
UPDATE (see UPDATE statements)
UPDATE STATISTICS, 495
USE, 494
comment operators (/*..*/), 497
comments, 497–98
Common Objects folder, 426
comparison operators, 245, 501–2
composite indexes, 116, 128–31
concatenation operator (+), 227
conditional execution, 554–61, 570
configuration variables, 544, 551–52
connections, number of, 544
Console Tree, 4
collapsing items, 18
database objects, 16–17
expanding items in, 18
system databases, 14–15
constraints. See check constraints
CONTINUE, 568–70
controlling execution, 553–70
CASE statements, 557–61, 570
conditional execution, 554–61, 570
GOTO command, 562–64, 570
IF ... ELSE statements, 554–57, 570
labels, 562
loops, 564–70
WHILE loops, 564–70
Copy Database Wizard, 399–406
additional objects to copy, 404
destination selection, 402–3
opening, 400
source selection, 401–2
copying databases. See Copy Database Wizard
cosines, 513
cotangents, 513
COUNT, 289, 516
GROUP BY field, 291
HAVING clause, 294–97
INSERT statement, with, 226
UPDATE statement, with, 346
Create command, 435
Create Database Backup Wizard, 21–28
Create Database Diagram Wizard, 188–92
Create Database Wizard, 78–85
location, setting, 80–82
naming the database, 80
sizing options, 83
Wizard button, 78
Create Index Wizard
choosing tables, 118–19
field order, changing, 122
including columns in indexes, 120
naming indexes, 121
properties of indexes, setting, 121
starting, 116–18
Create Login Wizard, 43–51
authentication mode selection, 45–46
database access permissions, 48, 55
login ID selection, 46–47
password selection, 46–47
role assignment, 47–48, 54
starting, 44–45
Windows account ID assignment, 53–54
CREATE statements, 470
in BEGIN ... END blocks, 554
columns, 443–48
constraints, 444
FUNCTION, 642–48, 657
INDEX, 450–51
PROCEDURE, 609–22
scripting, 463–65
syntax, 442–43
TABLE, 443–48, 463–69
temporary tables, 530, 552
TRIGGER, 626–31, 637
VIEW, 306, 443, 448–50
Create View Wizard, 300–306
column selection, 303
database selection, 302
naming views, 305
object selection, 302–3
opening, 300–301
WHERE clause, 304
creating
character columns, 105–7
check constraints, 158–61, 167
column aliases, 223–25, 233
composite indexes, 128–31
cursors, 576–79, 595
database diagrams, 188–92, 202
database objects, 470
databases, new, 77–85, 89
date columns, 103–5
defaults, 170–72, 186
foreign key constraints, 447–48
full outer joins, 280
GROUP BY queries, 289–97
GUID columns, 101–3
identity columns, 99–100
indexes, 116–31, 138, 450–51, 476–78
inner joins, 260–68, 280
joins, 259–80
left outer joins, 269–75, 280
logins, 43–51, 74
numeric columns, 97–99
objects, 442–51
outer joins, 269–77, 280
primary key constraints, 444–46
primary key indexes, 122–24, 138
relationships, 142–46, 155
relationships in database diagrams, 201–3
right outer joins, 276–77, 280
rules, 176–79, 186
scripts, 421–22, 440
simple indexes, 125–28
stored procedures, 609–22
tables, 95–96, 463–65
tables with database diagrams, 198–201, 203
tables with key constraints, 444–48
with templates, 466–69
triggers, 626–31, 637
unions, 277–80
user-defined data types, 182–84, 186
user-defined functions, 641–48, 657
users, database, 61–62
variables, 546–47, 552
views, 300–309, 322, 448–50
criteria
deleting selected rows with, 364, 376
HAVING clause, 294–97
updating rows using, 350–51
cursor, 94
@@CURSOR_ROWS, 592–95
cursors, 572–95
ABSOLUTE, 584–86, 595
API, 572
client-side, 572
closing, 577, 579, 595
columns, selecting, 578
creating, 576–79, 595
@@CURSOR_ROWS, 592–95
CURSOR_STATUS function, 592–95
deallocating, 579–80, 595
DECLARE CURSOR command, 572, 576–79
defaults when creating, 577
defined, 92
deleting rows, 590, 595
dynamic, 575–77
FAST_FORWARD, 576–77
FETCH command, 580–89, 592, 595
fetching rows, 580–89
@@FETCH_STATUS, 592–95
firehose, 576, 588–89
FIRST, 584–85
forward-only, 574, 577
FORWARD_ONLY, 577
GLOBAL, 579
keyset, 574–77
LAST, 584, 595
LOCAL, 579
lock parameter, 577–87
membership, 574–75
monitoring, 592–95
NEXT, 586–89, 595
opening, 579, 595
OPTIMISTIC, 578
overhead from, 582
PRIOR, 586, 595
read-only, 477–78, 574, 577
reflecting changes in data, 573–74
RELATIVE, 586–89, 595
scrolling, 574, 577
SCROLL keyword, 577
SCROLL_LOCKS, 577–78
SELECT command with, 572, 576
sensitivity, 573–74
server-side, 572
SET as variables, 578–79
sp_cursor_list, 600
SQL-92 syntax, 576
static, 574, 577
syntax, 577
with triggers, 576
type selection, 577
types of, 573–76
FOR UPDATE clause, 577–78
updating, allowing, 574, 577–78
updating rows, 590–91, 595
user-defined functions, using with, 649
variables, 578–80
visibility, 577
CURSOR_STATUS function, 592–95
CURSOR variables, 578–80, 595
D
DATABASE, 442
Database Administration commands, 495
Database Creators role, 49
database diagrams, 187–203
Arrange Tables button, 193
columns, adding, 197–99, 203
creating, 188–92, 202
creating tables, 198–201
deleting tables from, 195–96, 203
detail level, changing, 192–94, 202
naming, 192
primary keys, setting, 200
rearranging, 192
relationships, creating, 201–3
saving changes, 197–98
schema maintenance with, 196–203
tables, adding, 189–91, 194–95. 202
Database folder, 14
Database Maintenance Plan Wizard, 30–40
backup plan, choosing, 34
destination of backups, choosing, 36–37
integrity checks, 30, 34
maintenance history log, 39
optimization information, 33
report generation, 38
schedule, setting, 35
starting, 32
transaction log option, 37–38
database object types, list of, 17
Database Owner role, 65
database roles
assigning, 61–64, 69–71
creating, 65–69, 74
deleting, 73–74
removing users, 71–74
databases
ALTER statement, 451
attaching, 397–99, 406
copying (see Copy Database Wizard)
deleting, 88
detaching, 396–97, 406
ID numbers, returning, 519
names, returning, 519
properties, returning, 519
renaming, 599
system, 15
database users. See users, database
Data Definition Administrator role, 65
data integrity, entity, 158
Data Reader role, 65
data retrieval. See queries
Data Transformation Services. See DTS (Data Transformation Services)
Data Transformation Services folder, 14
data types
character, 106
properties, returning, 519
returned by user-defined functions, 640
scalar, 543
sql_variant, 543
table of, 93–94
user-defined (see user-defined data types)
variables, 543
Data Writer role, 65
date columns, creating, 103–5
date functions, 510–12, 526
dates
@@DATEFIRST variable, 544
validity checks, 526
datetime data type, 94, 510
DAY function, 511
dBase databases, importing, 378
DDL (Data Definition Language), 441–42
commands, table of, 494
scripting from Object Browser, 463–65
deallocating cursors, 579–80, 595
decimal, 93
DECLARE CURSOR command, 572, 576–79
DECLARE statements, 543, 546–47, 552
default database files location, 80
DEFAULT keyword, 442
defined by user-defined functions, 655–56
INSERT statement, 326
user-defined functions parameters, 648
defaults, 169–70, 186
binding to columns, 172–74, 186
creating, 170–72, 186
unbinding, 174–75, 186
default values, column, 107
degrees, converting radians to, 513
Delete command, 435
deletes, cascading, 360–61, 369–72, 376
DELETE statements, 359–76, 590
AFTER, 624–25, 627
cascading deletes, 360–61, 369–72, 376
creating in Grid Pane, 361–65
editing in SQL Pane, 365–72
FROM clause with, 360, 367–69, 376
INSTEAD OF, 625–27
JOIN operator, 360
syntax, 360
WHERE clause with, 360, 365–67, 371, 376
deleting. See also dropping
check constraints, 165–67
columns, 109–10, 458–59
database objects, 470 (see also DROP statements)
database roles, 73
databases, 88
indexes, 135–38, 482–83
logins, 60–61
relationships, 152–55
rows, 359–76 (see also DELETE statements)
rows using cursors, 590
rows using FROM clause, 367–69, 376
rows using WHERE clause, 365–67, 376
selected rows, 361–65, 376
tables, 112–13
tables from database diagrams, 195–96, 203
TRUNCATE TABLE statements, 373–76
users, database, 63–64
views, 320–22
delimiters, 492
denormalizing, user-defined functions for, 641
DENY, 495
Deny Data Reader role, 65
Deny Data Writer role, 65
dependencies of objects, viewing before deleting tables, 111–12
DESC, 239–40
detaching databases, 396–97, 406
Details Pane, 4
diagrams. See database diagrams
differential backups, 20
Disk Administrators role, 49
displaying. See viewing
DISTINCT keyword, 281–88, 297
INSERT statement with, 226
UPDATE statement with, 346
division operator (/), 499–500
DML (Data Manipulation Language), 441–42, 494
domains, column, 157–58. See also check constraints
double dash (--), 497
Drop command, 435
dropping
columns, 458–59
indexes, 460–61
tables, 461–62
DROP statements, 458–62, 470
COLUMN, 458–59
INDEX, 460–61
syntax, 459
TABLE, 461–62
DTS (Data Transformation Services), 377–78
DTS Export Wizard, 388–96, 406
destinations, choosing for data, 390
formatting for destination, 393
opening, 388–89
selecting data sources, 389–91
DTS Import Wizard, 378–88, 406
Column Mappings and Transformations dialog box, 384
column size, setting, 385
data source specification, 380–82
destination, selecting, 382, 384
DTS packages, saving as, 386
OLE DB properties, setting, 382
opening, 379
queries within, 384
tables, importing with, 378–88
tables, selecting for copying, 383
dynamic cursors, 575–77
E
editing. See also altering
check constraint text, 161–63, 167
relationships, 146–49, 155
rows, 210–11, 215
Transact-SQL statements in Query Designer, 219, 221
views, 319–20, 322
ELSE, 554–57, 570
END, 554
with BEGIN (see BEGIN ... END statements)
with WHILE, 564–70
Enterprise Manager, 3–6
capabilities, list of, 4
Console Tree, 5, 6 (see also Console Tree)
deleting tables, 112–13
Details Pane, 5, 6
exiting, 17–18
importing data (see DTS Import Wizard)
login management, 56–61
Query Analyzer (see Query Analyzer)
registration of SQL Server, 7–12
renaming tables, 110–12
renaming views, 318, 322
starting, 4
Table Designer (see Table Designer)
views (see Create View Wizard; View Designer)
wizards, selecting, 31
entities, implementation as tables, 92
entity integrity, 158
equal to operator (=), 501–2
@@ERROR, 619
error message strings, 620
Excel spreadsheets, importing, 378
exchanging data with stored procedures, 598–99
Execute command, 435
EXECUTE statements, 600–608, 622, 649
executing
queries, 417–40, 472–78
scripts, 425
stored procedures, 600–608
user-defined functions, 648, 657
execution, controlling. See controlling execution
Execution Plan Pane, 472–78, 490
arrows, 474
indexes, adding, 476–78
row counts, 474
statistics, showing, 473
viewing plans, 474–76
Exit command, 18
exiting Enterprise Manager, 17–18
expanding items in Console Tree, 18
exponents of floating point values, 513
exporting data, 388–96
extended properties, 98
F
FALSE, 158, 501
FAST_FORWARD cursors, 576–77
FETCH command, 580–89, 592, 595
@@FETCH_STATUS, 592–95
firehose cursors, 576, 588–89
FIRST, 584–85
float, 93
FLOOR, 513
flow, control of. See controlling execution
FOREIGN KEY, 444, 447–48
foreign key constraints, 139, 447–48
foreign keys
modifying fields, 148
relationships, 142
selecting fields, 145
temporary tables with, 530
foreign key tables, 142, 360
FoxPro databases, importing, 378
FROM clause, 257–58
DELETE statements with, 360, 367–69, 376
GROUP BY clause, 289–97
INNER JOIN, 264, 280
LEFT OUTER JOIN, 269, 280
RIGHT OUTER JOIN, 276, 280
with SELECT statement, 218, 256
syntax, 258
UNION, 277–80
UPDATE statement, with, 346, 355–58
full backups, 20
full outer joins, 269, 280
FUNCTION, 442
functions, 509–28
aggregate, 516–18
ALTER statement, 451
CASE statements, 557–61, 570
COLUMNS_UPDATED, 632
complete list of, 510
CURSOR_STATUS, 592–95
date, 510–12, 526
DAY, 511
deterministic, 510
drag-and-drop, 430
GETDATE, 227, 230, 245, 511
LEFT, 245, 247–48, 426–27, 437–39
logical actions, 509
mathematical, 513–16
metadata, 519–21
non-deterministic, 510
NULL, 526
Object Browser display of, 426
parameters, 426–27
script commands for, 435
scripting, 437–40
security, 521–23
SIGN, 514
string, 523–26
system, 526–28
time, 510–12
user-defined (see user-defined functions)
using, 510–12
G
GETDATE function, 227, 230, 245, 511
global variables, referencing, 642
GO command, 420
GOTO command, 562–64, 570
GRANT, 495
GROUP BY clause, 281, 289–97
COUNT with, 289, 291
creating, 289–92
HAVING clause, 293–97
INSERT statement, with, 226
UPDATE statement, with, 346
groups, 521
growing the file, 86
growth percentage, setting, 86–87
GUID columns, 101–3
H
hash sign (#), 530
HAVING clause, 293–97
I
identity columns, 101–2
creating, 99–100
@@IDENTITY, 545
IF ... ELSE statements, 554–57, 570
image data type, 94, 452
importing
databases, 379, 406 (see also DTS Import Wizard)
tables, 378–88, 406
IN, 248, 251–52, 256
INDEX, 442, 460–61
indexes, 116
adding from Execution Plan Pane, 476–78
columns, changing, 133–35
composite, creating, 128–31
creating, 116–31, 138, 450–51
creating with user-defined functions, 639
deleting, 135–38, 482–83
deterministic functions, 510
displaying existing, 119
dropping, 460–61
DROP statements, 460–61
field order, changing, 122
Manage Index command, 476–78, 490
metadata functions for, 519
naming, 121
Object Browser display of, 426
primary key, creating, 122–24, 128
renaming, 131–33
script commands for, 435
tuning, 482, 485–86
unique, 127–28
views, 300
Index Tuning Wizard, 482–90
Informix databases, importing, 378
inline user-defined functions, 644–46, 657
INNER JOIN, 264, 280
inner joins. See joins, inner, creating
Insert command, 435
inserting
rows, 325–43
rows in tables (see INSERT statements)
INSERT INTO statements, 543–44
INSERT statements, 325–43
AFTER, 624–25, 627–30
column list, 326
columns, including all, 332–33
creating rows in Grid Pane, 326–30
DEFAULT keyword, 326, 334–35
editing in SQL pane, 330–35, 341–43
INSTEAD OF, 625–27, 630–31
NULL, 334–35
restrictions, 326
rows, inserting multiple, 335–43
rows, inserting specifying columns, 326–35, 343
SELECT statement with, 335–43
syntax, 226
VALUES, 326, 343
with views, 326
INSTEAD OF triggers, 625–27, 630–31
int, 93
integrated security. See Windows Authentication
integrity checks, 30, 34
isqlw command, 410
J
jobs, 15
joining tables. See joins
JOIN operator in DELETE statements, 360
joins, 257
adding tables to, 260–61, 265–66
All Row option, 274
conditions, 258
creating, 259–80
FROM clause, 257–58
indexed views, 300
inner, creating, 260–68, 280
left outer, creating, 269–75, 280
lines, selecting, 273
multiple table, creating, 265–68, 280
outer, creating, 269–77, 280
Properties dialog box, displaying, 273–74
right outer, creating, 276–77, 280
specifying objects, 259
syntax, 258
unions, 277–80
junction tables, 141
Next
Last Updated: Friday, July 6, 2001 |
