|
|
 |
|
|
|
MCSE Training Kit (Exam 70-229): Microsoft® SQL Server™ 2000 Database Design and Implementation
|
|
|
Author |
|
Microsoft Corporation
|
|
|
Pages |
688
|
|
Disk |
1 Companion CD(s)
|
|
Level |
All Levels
|
|
Published |
04/25/2001
|
|
ISBN |
9780735612488
|
|
ISBN-10 |
0-7356-1248-X
|
|
Price(USD) |
$59.99
To see this book's discounted price, select a reseller below.
|
|
|
|
|
|
|
|
|
Index
A
access
cursor data retrieval, 263–267
API server cursors, 266–267
client cursors, 267
controlling behavior, 268–269
exercise scenario, 269–271
fetching and scrolling, 268
locking, 269
Transact-SQL Server cursors, 264–266
data, 206
exercise scenario, 223–228
FROM clause, 209
GROUP BY clause, 210–211
HAVING clause, 209–211
INTO clause, 209
joins, 215–218
ORDER BY clause, 211
SELECT clause, 207–208
SELECT statement, 206–207, 211–214
subqueries, defining, 218–221
WHERE clause, 209–211
external data
ad hoc computer names, 259–260
distributed queries, 256–257, 260–262
XML data, 272–273
BINARY Base64 option, 275
ELEMENTS option, 275
exercise scenario, 279–280
FOR XML clause, 273–275
OPENXML function, 275–278
XMLDATA keyword, 275
active transaction logs, 424–425
ad hoc computer names (accessing external data), 259–260
OPENDATASOURCE function, 260
OPENROWSET function, 259–260
adding
data to databases, 229–232
bulk copy operations, 232
INSERT statement, 229–231
SELECT subquery, 231
SELECT…INTO statement, 231
through views, 381
WRITETEXT statement, 232
security roles, 487–488
administration architecture, 33–38
automation, 35
backup/restore architecture, 36
data definition language, 34
data import/export architecture, 36
data integrity validation, 37
data manipulation language, 34
graphical tools, 34
OLAP, 39
OLTP, 38
replication architecture, 37–38
SQL-DMF (Distributed Management Framework), 34
stored procedures, 34
AFTER clause, 341
aggregate functions, 80
ALTER TABLE statement, 67
altering triggers, 343–344
Analysis Services, 12
APIs
application development, 40
deleting data from databases, 235–236
implicit transactions, 439
modifying data in databases, 234
server cursors, 266–267
restrictions, 267
applications
customizing, English Query, 12–13
development architecture, 39–41
APIs/URLs, 40
database languages, 40–43
security, 471–472
roles, activating, 490
architecture
administration, 33–38
automation, 35
backup/restore architecture, 36
data definition language, 34
data import/export architecture, 36
data integrity validation, 37
data manipulation language, 34
graphical tools, 34
OLAP, 39
OLTP, 38
replication architecture, 37, 38
SQL-DMF (Distributed Management Framework), 34
stored procedures, 34
databases, 20–28
collations, 23–24
files and filegroups, 26
objects, 22–23
pages and extents, 25–26
security mechanisms, 24
space allocation and reuse, 26
tables and indexes, 27–28
transition logs, 28
relational databases, 28–33
full-text query architecture, 31–32
input/output architecture, 31
memory architecture, 30
query processor architecture, 30–33
Relational Database Engine, 29
server net-libraries, 28
TDS (Tabular Data Stream), 28
transitions architecture, 32–33
SQL Server 2000, 20
administration, 33–38
application development, 39–41
database, 20–28
relational database, 28–33
AS clause, 341
AS keyword, 208
attributes, entity, identifying, 129–131
relationships between entities, 131–132
auditing, 471
authentication, 118, 465–467
configuring, 479–481
with Enterprise Manager, 479–480
with Transact-SQL, 480–481
authorization, 467–471
configuring, 481–483
with Enterprise Manager, 481–482
with Transact-SQL, 482–483
groups and roles, 468–470
object and statement permissions, 470–471
permission states, 470
AUTO mode (FOR XML clause), 274–275
autocommit transactions, 436–438
compile and run-time errors, 436–438
automated administration architecture, 35
autonumbering database tables, 166–169
B
backup/restore architecture, 36
basic views, modifying data, 380–382
adding data, 381
changing data, 381–382
deleting data, 382
batches (Transact-SQL statements), 91–93
batch processing, 92–93
GO command, 91–92
bcp command prompt utility (importing and exporting data), 244–247
data formats, 246–248
DTS packages, 248–251
DTS tools, 248
behavior, cursor, 268–269
BINARY Base64 option (XML data retrieval), 275
binary data types, 156
Books Online, 14
boundaries, transaction, specifying, 433
built-in functions (Transact-SQL), 79–80
aggregate, 80
rowset, 79
scalar, 80
built-in wizards (user interface tools), 19
bulk copy operations (adding data to databases), 232
bulk copying, 36
BULK INSERT statement (importing and exporting data), 244–247
data formats, 246–248
DTS packages, 248–251
DTS tools, 248
business rules, database system, identifying, 123–127
C
caching stored procedures, 296–297
calling procedures for execution, 299–300
changing data (through views), 381–382
character data types, 156
CHECK constraints, 194–196
creating, 195–196
disabling, 196
checkpoints (active transaction logs), 424–425
clauses
FROM, 209
GROUP BY, 210–211
HAVING, 209–211
INTO, 209
ORDER BY, 211
SELECT
accessing data, 207–208
AS keyword, 208
DISTINCT keyword, 207
Select list information, types of, 208
TOP n keyword, 207–208
trigger
AS clause, 341
CREATE TRIGGER clause, 340
FOR, AFTER, and INSTEAD OF clauses, 341
ON clause, 340
UPDATE, 351–352
FROM, 233–234
SET, 233
WHERE, 233
WHERE, 209–211
XML data retrieval (FOR XML), 273–275
Client Network utility (user interface tools), 17
client cursors, 267
clustered indexes, 393–394
creating (exercise scenario), 413–416
clustered servers, partitioning, 515
distributed partitioned views, 515
clustered tables, 27
coding efficient transactions, 434
collations, database, 23, 24
color coding (Query window Editor pane), 48–49
columns
choosing (SQL Profiler), 502–503
database tables
identifier columns, 166–169
nullability, 163–165
nullable (database design), 104–105
command prompt tools, 15–16
commands (programming triggers), 351–353
Transact-SQL precautions, 353
UPDATE clauses, 351–352
comments (Transact-SQL), 84–85
comparison operators (subqueries), 220
compile errors (autocommit transactions), 436–438
components
logical database
collations, 23–24
objects, 22–23
security mechanisms, 24
SQL Server 2000, 9
Analysis Services, 12
Books Online, 14
DTS, 11
English Query, 12–13
Meta Data Services, 13–14
Relational Database Engine, 9–10
replication, 10–11
composite indexes, 395–396
concurrency controls (transactions), 427
optimistic, 427
pessimistic, 427
concurrency problems (locking), 446–447
inconsistent analysis (nonrepeatable reads), 447
lost updates, 446
phantom reads, 447
uncommitted dependency (dirty read), 447
configuring
authentication
with Enterprise Manager, 479–480
with Transact-SQL, 480–481
authorization
with Enterprise Manager, 481–482
with Transact-SQL, 482–483
permissions
with Enterprise Manager, 483–484
with Transact-SQL, 484–487
SQL Server 2000 (upgrade wizard), 4
connections, DTS (importing and exporting data), 250–251
constraints
data integrity, 132–139, 187–188
CHECK, 194–196
exercise scenario, 196–201
FOREIGN KEY, 191–194
PRIMARY KEY, 188–190
UNIQUE, 190–191
enforcing data integrity, 181–182
context (CREATE PROCEDURE statement), 294–295
control-of-flow language elements (Transact-SQL), 83–84
controlling traces (SQL Profiler), 505–506
replaying traces, 506
CREATE DATABASE statement, 145–146
Create Database wizard, 146
CREATE INDEX statement (creating indexed views), 372
CREATE PROCEDURE statement, 294–299
caching stored procedures, 296–297
context, 294–295
Create Stored Procedure wizard, 298–299
encrypting stored procedures, 296–297
Enterprise Manager, 297–298
extended stored procedures, creating, 299
grouping stored procedures, 296–297
temporary stored procedures, 295
Create Stored Procedure wizard, 298–299
CREATE TABLE statement, 67, 169
CREATE TRIGGER clause, 340
CUBE operator (summarizing data), 221–222
cursors
accessing data, 263–267
API server cursors, 266–267
client cursors, 267
controlling behavior, 268–269
exercise scenario, 269–271
fetching and scrolling, 268
locking, 269
Transact-SQL Server cursors, 264–266
deleting data from databases, 235–236
modifying data in databases, 234
programming stored procedures, 318–320
data access methods, 318–326
customizing
applications (English Query), 12–13
locking, 449–456
deadlocks, 450–451
for indexes, 455–456
isolation levels, setting, 453
table-level locking, 453–455
timeouts, 451–455
D
data
accessing, 206
exercise scenario, 223–228
FROM clause, 209
GROUP BY clause, 210–211
HAVING clause, 209–211
INTO clause, 209
joins, 215–218
ORDER BY clause, 211
SELECT clause, 207–208
SELECT statement, 206–207, 211–214
subqueries, defining, 218–221
WHERE clause, 209–211
adding to databases, 229–232
bulk copy operations, 232
INSERT statement, 229–231
SELECT subquery, 231
SELECT…INTO statement, 231
WRITETEXT statement, 232
constraints on, identifying, 132–139
cursor access, 263–267
API server cursors, 266–267
client cursors, 267
controlling behavior, 268–269
exercise scenario, 269–271
fetching and scrolling, 268
locking, 269
Transact-SQL Server cursors, 264–266
customizing (views), 366
data purpose, identifying, 123
data types, 155
enforcing integrity, 180–181
identifying (exercise), 160–162
system-supplied, 155–160
user-defined, 160
database system, identifying, 122
deleting from databases, 235–236
APIs and cursors, 235–236
DELETE statement, 235
TRUNCATE TABLE statement, 236
external
ad hoc computer name access, 259–260
distributed query access, 256–257, 260–262
importing and exporting, 36, 244
bcp command prompt utility, 244–247
BULK INSERT statement, 244–247
exercise scenario, 251–254
views, 366
integrity, 180
constraints, 187–201
domain, 183
enforcing, 180–182, 184–185
entity, 183
extending with triggers, 331–332
procedural integrity, 331–332
referential, 183
user-defined, 184
validation, 37
marts (Analysis Services), 12
Meta Data Services, 13–14
modifying, 232–234, 384–386
basic views, 380–382
partitioned views, 382–383
when INSTEAD OF trigger is used, 383–389
partitioned, combining with views, 366–367
replication, 10–11
stored procedures, 284–286
creating, 292–299
cursors, 318–320
deleting, 303–304
executing, 299–302
exercise scenario, 289–326
extended, 288–289
local, 287
modifying, 302–303
nesting procedures, 318
performance, 284–285
programming, 285, 310–318
remote, 289
security, 286
system, 286–287
temporary, 287–288
summarizing, 221–223
CUBE operator, 221–222
ROLLUP operator, 222–223
viewing with views, 379
warehouses (Analysis Services), 12
XML, retrieving, 272–273
BINARY Base64 option, 275
ELEMENTS option, 275
exercise scenario, 279–280
FOR XML clause, 273–275
OPENXML function, 275–278
XMLDATA keyword, 275
data control language, see DCL
data definition language, see DDL
data manipulation language, see DML
Data Transformation Services, see DTS
Database Designer, 169
databases, 3, 100–101
accessing data, 206
exercise scenario, 223–228
FROM clause, 209
GROUP BY clause, 210–211
HAVING clause, 209–211
INTO clause, 209
joins, 215–218
ORDER BY clause, 211
SELECT clause, 207–208
SELECT statement, 206–207, 211–214
subqueries, defining, 218–221
WHERE clause, 209–211
adding data, 229–232
bulk copy operations, 232
INSERT statement, 229–231
SELECT subquery, 231
SELECT…INTO statement, 231
WRITETEXT statement, 232
architecture, 20–28
collations, 23–24
files and filegroups, 26
objects, 22–23
pages and extents, 25–26
security mechanisms, 24
space allocation and reuse, 26
tables and indexes, 27–28
transition logs, 28
creating, 144–146
CREATE DATABASE statement, 145–146
Create Database wizard, 146
Enterprise Manager, 146
exercise scenario, 150–154
data integrity, 180
constraints, 187–201
cursor access, 263–271
domain, 183
enforcing, 180–182, 184–185
entity, 183
referential, 183
user-defined, 184
data types, 155
identifying (exercise), 160–162
system-supplied, 155–160
user-defined, 160
data warehousing, 5–6
deleting, 150, 235–236
APIs and cursors, 235–236
DELETE statement, 235
TRUNCATE TABLE statement, 236
designing, 102–111
normalizing design, 101–105
nullable columns, avoiding, 104–105
physical design, 116
repeating values/columns, avoiding, 105
table identifiers, 102–103
table storage parameters, 103–104
environments, 115–116
physical design, 116
sizing databases, 115–116
external data access
ad hoc computer names, 259–260
distributed query access, 256–257, 260–262
files and filegroups, 112–114
default filegroups, 113–114
design rules, 113
importing and exporting data, 244
bcp command prompt utility, 244–247
BULK INSERT statement, 244–247
exercise scenario, 251–254
installing SQL Server, 116–117
locking
concurrency problems, 446–447
customizing, 449–456
exercise scenario, 456–457
isolation levels, 448–449
optimistic concurrency, 448
pessimistic concurrency, 448
logical data models, 129
contraints on data, identifying, 132–139
entities and attributes, 129–132
exercise scenario, 134–139
modifying, 148–149
setting options, 149
modifying data, 232–234
APIs and cursors, 234
exercise scenario, 236–239
UPDATE statement, 232–234
UPDATETEXT statement, 234
WRITETEXT statement, 234
monitoring (SQL Profiler), 500–509
optimizing with partitioning, 511, 514–518
relational, 3, 28–33
entity relationships, 105–111
full-text query architecture, 31–32
input/output architecture, 31
memory architecture, 30
query processor architecture, 30, 32–33
Relational Database Engine, 29
server net-libraries, 28
tables, 3
TDS (Tabular Data Stream), 28
transitions architecture, 32–33
Relational Database Engine, 9–10
security, 117–118, 462
applications, 471–472
auditing, 471
authentication, 465–467, 479–481
authentication modes, 118
authorization, 467–471, 481–483
design exercise, 477
design plan requirements, 473–474
domain, 464
implementation exercise, 490–494
levels, 117
local computer, 465
nesting, 475
network protocol, 462–464
object encryption, 471
ownership chains, 475–476
permissions, 476–477, 483–487
physical, 462
planning, 117
roles, 487–494
users, groups, and roles, 476
sizing, 115–116
stored procedures, 284–286
creating, 292–299
cursors, 318–320
deleting, 303–304
executing, 299–302
exercise scenario, 289–291, 304–308, 320–326
extended, 288–289
local, 287
modifying, 302–303
nesting procedures, 318
performance, 284–285
programming, 285, 310–318
remote, 289
security, 286
system, 286–287
temporary, 287–288
triggers, 331–359
summarizing data, 221–223
CUBE operator, 221–222
ROLLUP operator, 222–223
system integration, 6
system requirements, identifying, 119
amount and types of data, 122
business rules, identifying, 123–127
data purpose, identifying, 123
scenario exercise, 124–127
system goals, 120–121
tables, 163–170
autonumbering, 166–169
column nullability, 163–165
CREATE TABLE statement, 169
creating and managing (exercise), 172–175
Database Designer, 169
default values, defining, 165–166
deleting, 172
Enterprise Manager, 169
identifier columns, 166–169
modifying, 171–172
viewing information, 170–171
transactions, 422, 432–435
autocommit, 436–438
concurrency controls, 427
distributed, 440–441
distributed architecture, 429
efficient coding, 434
explicit, 435–436, 442–445
implicit, 438–440
locking architecture, 427–429
log architecture, 422–427, 429–430
logs, 114–115
processing errors, 435
specifying boundaries, 433
Transact-SQL statements, 433–434
viewing information, 146–148
views, 364–365
combining partitioned data, 366–367
creating, 368–378
customizing data, 366
deleting, 376
focusing on specific data, 365–366
importing and exporting data, 366
modifying, 375
modifying data, 380–386
simplifying data manipulation, 366
viewing data, 379
XML data, retrieving, 272–273
BINARY Base64 option, 275
ELEMENTS option, 275
exercise scenario, 279–280
FOR XML clause, 273–275
OPENXML function, 275–278
XMLDATA keyword, 275
date and time data types, 157
DCL (data control language), 68–69
DDL (data definition language), 67–68
deadlocks, 450–451
decimal data types, 157
DEFAULT definitions (enforcing data integrity), 181
default filegroups, 113–114
default values
database tables, defining, 165–166
programming stored procedures, 315–316
deferred name resolution (creating stored procedures), 299
defining, default values (database tables), 165–166
definitions
DEFAULT, 181
NOT NULL, 181
DELETE events, triggers, 333–337
DELETE statement, 70
deleting data from databases, 235
modifying data with partitioned views, 383
deleted pseudo tables (programming triggers), 350–351
deleting
data in databases, 235–236
APIs and cursors, 235–236
DELETE statement, 235
through views, 382
TRUNCATE TABLE statement, 236
databases, 150
tables, 172
indexes, 408
security roles, 487–488
stored procedures, 303–304
views, 376
exercise scenario, 376–378
delimited identifiers, 78
DENY statement, 68
configuring permissions, 487
designing
databases, 102–111
default filegroups, 113–114
files and filegroup rules, 113
normalizing design, 101–105
nullable columns, avoiding, 104–105
physical design, 116
repeating values/columns, avoiding, 105
table identifiers, 102–103
table storage parameters, 103–104
security plans
nesting, 475
ownership chains, 475–476
permissions, 476–477
requirements, 473–474
users, groups, and roles, 476
Desktop Engine (SQL Server 2000), 7
determinism (Transact-SQL functions), 82
Developer Edition (SQL Server 2000), 7
developing applications (architecture), 39–41
APIs/URLs, 40
database languages, 40–43
disabling
CHECK constraints, 196
FOREIGN KEY constraints, 194
triggers, 344–349
disk partitioning, 514
DISTINCT keyword, 207
distributed partitioned views, 515
distributed queries, 36
accessing external data, 256–257
ad hoc computer names, 259–260
exercise scenario, 260–262
four-part names, 258
linked servers, 257
OPENQUERY function, 258–259
distributed transactions, 429, 440–441
Transact-SQL statements, 441
DML (data manipulation language), 69–70
domain data integrity, 183
domain security layer, 464
DROP TABLE statement, 68
dropping triggers, 344–349
DTS, 11, 36
OLAP (Online Analytical Processing), 11
DTS tools (importing and exporting data), 248
packages, 248–251
E
Editor pane (Query window), 48–49
color coding, 48–49
Transact-SQL statement, executing, 49
ELEMENTS option (XML data retrieval), 275
encryption
objects, 471–472
stored procedures, 296–297
enforcing data integrity, 180–182
constraints, 181–182
data types, 180–181
DEFAULT definitions, 181
exercise scenario, 184–185
IDENTITY properties, 181
indexes, 182
NOT NULL definitions, 181
rules, 182
triggers, 182
English Query, 12–13
Enterprise Edition (SQL Server 2000), 7
Enterprise Manager
configuring
authentication, 479–480
authorization, 481–482
permissions, 483–484
creating
database tables, 169
databases, 146
stored procedures, 297–298
triggers, 342
user interface tools, 16
entities, attributes, identifying, 129–132
relationships between, 105–111, 131–132
many-to-many, 107
one-to-many, 107
one-to-one, 106–107
environments, 115–116
physical design, 116
sizing databases, 115–116
error handling (programming stored procedures), 312–318
NULL parameter settings, 315–316
testing, 316–318
Estimated Execution Plan tab (Results pane), 55
events, choosing (SQL Profiler), 502–503
executing
stored procedures, 299–302
at SQL Server startup, 301–302
calling procedures, 299–300
specifying parameters and values, 300–301
Transact-SQL statements, 89
batches, 91–93
exercise, 70–75
scripts, 94
single statements, 89–90
stored procedures and triggers, 93–94
trigger events, 333–337
Execution Plan tab (Results pane), 51–52
EXISTS keyword (subqueries), 221
EXPLICIT mode (FOR XML clause), 275
explicit transactions, 435–436
exercise scenario, 442–445
exporting data, 244
bcp command prompt utility, 244–247
BULK INSERT statement, 244–247
exercise scenario, 251–254
views, 366
expressions (Transact-SQL), 82–83
operators, using in, 82–83
extended stored procedures, 288–289
creating, 299
Extensible Markup Language, see XML data
extents (physical database architecture), 25, 26
external data (distributed query access), 256–257
ad hoc computer names, 259–260
exercise scenario, 260–262
four-part names, 258
linked servers, 257
OPENQUERY function, 258–259
F
federated servers, partitioning, 515
fetching (cursor data access), 268
filegroups
database, 112–114
default filegroups, 113–114
design rules, 113
physical database architecture, 26
files
database, 112–114
default filegroups, 113–114
design rules, 113
database architecture, 26
partitioning, 514
fill factor (indexes), 396–397
filters, choosing (SQL Profiler), 502–503
flags, mapping (XML), 277–278
floating point data types, 157
FOR clause, 341
FOR XML clause (XML data retrieval), 273–275
AUTO mode, 274–275
EXPLICIT mode, 275
RAW mode, 274
FOREIGN KEY constraints, 191–194
creating, 192–194
disabling, 194
formats, data (bcp command prompt utility), 246–248
four-part names (distributed queries), 258
FROM clause
accessing data, 209
UPDATE statement, 233–234
full outer joins, 218
full-text indexing, 399
full-text query architecture, 31–32
functions
programming triggers, 351–353
Transact-SQL precautions, 353
UPDATE clauses, 351–352
Transact-SQL, 79–82
built-in, 79–80
determinism, 82
user-defined, 81
G
GAM (Global Allocation Map), 26
Global Allocation Map, see GAM
globally unique identifiers, 168–169
GO command (Transact-SQL statement batches), 91–92
goals, database system, identifying, 120–121
GRANT statement, 68
configuring permissions, 484–486
graphical interfaces (creating indexes), 403–405
graphical tools (administration architecture), 34
Grids tab (Results pane), 49–50
GROUP BY clause (accessing data), 210–211
grouping stored procedures, 296–297
groups
authorization security, 468–470
database security, 24
H–I
HAVING clause (accessing data), 209–211
heaps, 27
identifier columns (database tables), 166–169
globally unique identifiers, 168–169
IDENTITY property, 167–168
identifiers
globally unique, 168–169
table (database design), 102–103
Transact-SQL, 77–78
regular, 77–79
IDENTITY properties
enforcing data integrity, 181
identifier columns, creating, 167–168
implicit transactions, 438–440
import/export data architecture, 36
importing (data), 244
bcp command prompt utility, 244–247
BULK INSERT statement, 244–247
exercise scenario, 251–254
views, 366
IN and NOT IN subqueries, 219–220
inconsistent analysis (nonrepeatable reads), 447
indexed views, creating, 370–373
CREATE INDEX statement, 372
indexes, 392–393
clustered, 393–394
exercise scenario, 413–416
composite, 395–396
creating, 403–408
graphical interfaces, 403–405
Transact-SQL statements, 405–408
customizing locking for, 455–456
deleting, 408
enforcing data integrity, 182
exercise scenario, 399–402
fill factor and pad index, 396–397
full-text indexing, 399
nonclustered, 394–395
optimizing (Index Tuning wizard), 511–518
performance, 412–416
physical database architecture, 27–28
rebuilding, 409
renaming, 410
sort order, 397
uniqueness, 395
viewing information, 397–399
when to index (choices), 410–412
inner joins (accessing data), 216–217
input/output architecture, 31
INSERT events (triggers), 333–337
INSERT statements, 69, 229–231
adding data to databases (INSERT…VALUES statement), 230–231
modifying data (partitioned views), 382–383
inserted pseudo tables (programming triggers), 350–351
inserting data into databases, 229–232
bulk copy operations, 232
INSERT statement, 229–231
SELECT subquery, 231
SELECT…INTO statement, 231
WRITETEXT statement, 232
installing SQL Server 2000, 4–5, 116–117
upgrade wizard, 4
INSTEAD OF clause, 341
INSTEAD OF trigger (modifying data with views), 383–389
integer data types, 157
integrity
data, 180
constraints, 187–201
domain, 183
enforcing, 180–182, 184–185
entity, 183
extending with triggers, 331–332
referential, 183
user-defined, 184
validation, 37
interface tools
users, 16–22
built-in wizards, 19
Client Network utility, 17
Enterprise Manager, 16
Profiler, 17
Query Analyzer, 18–19
Server Agent, 17
Server Network utility, 17
Service Manager, 18
INTO clause (accessing data), 209
isolation levels
locking (SQL-92), 448–449
setting, 453
isql Command Prompt utility, 60
isqlw Command Prompt utility, 59–60
J
joins (accessing data), 215–218
inner joins, 216–217
outer joins, 217–219
K
keywords
AS, 208
DISTINCT, 207
EXISTS (subqueries), 221
NOT EXISTS (subqueries), 221
TOP n, 207–208
XMLDATA, 275
L
languages
database (application development), 40–43
SQL (Structured Query Language), 3–4
left outer joins, 217
limitations, triggers, 332
linked servers (distributed queries), 257
local computer security layer, 465
local stored procedures, 287
locking
concurrency problems, 446–447
inconsistent analysis (nonrepeatable reads), 447
lost updates, 446
phantom reads, 447
uncommitted dependency (dirty read), 447
cursors, 269
customizing, 449–456
deadlocks, 450–451
for indexes, 455–456
isolation levels, setting, 453
table-level locking, 453–455
timeouts, 451–452
exercise scenario, 456–457
isolation levels (SQL-92), 448–449
optimistic concurrency, 448
pessimistic concurrency, 448
log files, 26
logical data models (databases), 129
contraints on data, identifying, 132–139
entities and attributes, 129–132
exercise scenario, 134–139
logical database components
collations, 23–24
objects, 22–23
security mechanisms, 24
logical transaction logs, 423–424
logins (database security), 24
logs, transaction, 422–427
checkpoints and active log portion, 424–425
exercise scenario, 429–430
logical, 423–424
physical architecture, 425–426
shrinking, 426–427
truncating logs, 425
write-ahead, 423
lost updates (concurrency problems), 446
Next
Last Updated: Friday, July 6, 2001 |
