|
|
 |
|
|
|
MCAD/MCSE/MCDBA Self-Paced Training Kit: Microsoft® SQL Server™ 2000 Database Design and Implementation, Exam 70-229, Second Edition
|
|
|
Author
|
|
Microsoft Corporation
|
|
|
Pages
|
880
|
|
Disk
|
1 Companion CD(s); 1 Evaluation CD(s)
|
|
Level
|
Int/Adv
|
|
Published
|
05/07/2003
|
|
ISBN
|
9780735619609
|
|
Price
|
$59.99
To see this book's discounted price, select a reseller below.
|
|
|
|
|
|
|
|
|
Index
Symbols
-- (comment characters), 82
/* . */ (comment characters), 83
A
accessing databases
determining needs for, 118-119, 673-693
modifying data. See modifying data in databases
retrieving data. See retrieving data from databases
account delegation, 434
active portion of transaction logs, 396
ActiveX Script task (DTS), 235
ad hoc computer names, 244
administration, automated, 37
administration architecture, 35
AFTER clause (CREATE TRIGGER statement), 321
AFTER triggers, 315-316
aggregate functions (Transact-SQL), 78
ALL keyword
DISABLE TRIGGER clause, 326
GRANT statement, 454
subqueries, 208, 213
ALTER statements, 66
ALTER DATABASE, 109, 142, 163
ALTER FUNCTION, 79
ALTER PROCEDURE, 286, 291
ALTER TABLE, 66, 70
CLUSTERED, NONCLUSTERED clauses, 370, 380
disabling triggers, 326
index creation, 381-382
ALTER TRIGGER, 323, 331-332
ALTER VIEW, 353, 355
amount of data, identifying, 117-118
Analysis Services system, 14
ANY keyword (subqueries), 208
API server cursors, 248, 251-252. See also cursors
APIs supported by SQL Server, 42
application development architecture, 41
application roles, 459, 689-693
application security, 442
applying transactions, 110, 394
AS keyword
CREATE TRIGGER statement, 322
GRANT statement, 454
SELECT statement, 196
ASC keyword (ORDER BY clause), 199
ascending sorts, query results, 199
atomicity of transactions, 403, 607
attributes, entity, 124-125
auditing, 441
authentication, 8, 435-437
administering and configuring, 449-451
modes (Windows vs. Mixed), 113
relational database engine, 13
sample problems, 673
authorization, 437-441, 461
administering and configuring, 451-453
permission administration and configuration, 453-457
Auto database options, 143
AUTO mode (XML), 259, 602
autocommit transactions, 407, 414
automated administration, 37
automatic checkpoints, 397
autonumbering records, 158-160
availability, 521
B
backing out of transactions, 111
BACKUP permission, 441
backups
architecture of, 38
databases, 143
transaction logs, 111
balanced trees (B-trees), 368
batches of Transact-SQL statements, executing, 88-92
bcp utility, 230-233, 237, 565, 571-576
BEGIN TRANSACTION statements, 404, 406
BEGIN.END blocks, 79, 82
bigint data type, 150
BINARY BASE64 option, 259, 260
binary data type, 149
Bit data type, 151
Books Online, 16
bound connections, 422
boundaries, transactions, 404
BREAK keyword, 82
breakpoints, setting in Transact-SQL, 55, 62
B-trees (balanced trees), 368
built-in functions (Transact-SQL), 77
bulk copy operations, 219, 230-233, 237-240, 565, 571-576
BULK INSERT statements, 230-233, 238, 571-576
business logic. See transactions and locking
business rules, 119
C
capturing events. See SQL Profiler
CASCADE keyword (REVOKE statement), 456
cascading referential integrity, 314, 316
chains of ownership, 445
changing data in databases. See modifying data in databases
char (character) data type, 149
CHECK constraints, 172, 183-185, 188-189. See also data integrity
adding (example), 188-189
disabling, 184
partitioning tables, 351
checkpoints, 396
client cursors, 248, 252. See also cursors
Client Network utility, SQL Server, 19
CLOSE statement (Transact-SQL), 249, 255
clustered indexes, 346, 369-370, 389, 657. See also indexed views
code reuse with stored procedures. See stored procedures
color coding for Transact-SQL code, 48
COLUMNPROPERTY statements, 159, 162
columns in database tables, 5. See also modifying data in databases; retrieving data from databases
adding, modifying, deleting, 162
autonumbering and identifier columns, 158-160
data integrity and constraints. See constraints on data; data integrity
database indexes. See indexes
default values, defining, 157-158
identifying column data types, 152-154
importing and exporting data. See exporting data; importing data
nullability, 100, 152, 155-156, 166, 171, 513
repeating, 101
updating values with triggers (example), 335-338
views. See views
COLUMNS_UPDATED clause (CREATE TRIGGER, ALTER TRIGGER), 331-332
command-prompt utilities, 17
commands in triggers, 332
comments (Transact-SQL), 82
commit phase, transactions, 411
COMMIT TRANSACTION statements, 404, 407
COMMIT WORK statements, 404, 407
comparison operators, subqueries with, 208, 213
compile errors, transactions and, 408-409
composite indexes, 371
concurrency control, 399, 405
optimistic vs. pessimistic, 399, 419
types of problems, 417-418
condition, joins. See joins to retrieve data
configuring authentication, 449-451
Connect To SQL Server dialog box (SQL Query Analyzer), 47
consistency of transactions, 403, 607
constraints on data, 127-128, 132, 171, 176-190
CHECK constraints, 183-185, 188-189
disabling, 184
partitioning tables, 351
column vs. table constraints, 176
FOREIGN KEY constraints, 163, 180-183, 187-188
designing (sample questions), 503-511
disabling, 182
IDENTITY properties, 159
NOT NULL definitions, 156, 171
PRIMARY KEY constraints, 163, 177-179, 185-186. See also data integrity
clustered indexes, 369
designing (sample questions), 503-511
index tuning, 479
UNIQUE constraints, 179-180
deleting tables, 163
designing (sample questions), 503-511
verifying addition of, 189
CONTINUE keyword, 82
control-of-flow elements (Transact-SQL), 81
copying in bulk. See bulk copy operations
covered queries, 371, 386, 659
CPU consumption, 480
Create Database Wizard, 140
Create Index wizard, 380
CREATE permission, 441
CREATE statements, 66
batching Transact-SQL statements, 89
CREATE DATABASE, 139, 143
CREATE FUNCTION, 79
CREATE INDEX, 343, 350, 380-381
CLUSTERED, NONCLUSTERED clauses, 370, 380
DROP_EXISTING clause, 381, 384, 390
CREATE PROCEDURE, 278-280
CREATE RULE, 172
CREATE TABLE, 66, 70, 161, 164-166
CLUSTERED, NONCLUSTERED clauses, 370, 380
index creation, 381-382
CREATE TRIGGER, 320-322, 331-332
CREATE VIEW, 348, 354
Create Stored Procedure wizard, 282
CUBE operator, 209
cursor data type, 151
Cursor database options, 143
cursors, 248-256
API server cursors, 248, 251-252
client cursors, 248, 252
controlling behavior of, 253
deleting data, 222
locking, 254
modifying data, 221
optimistic concurrency control, 399, 419
sample questions, 595-599
in stored procedures, 300
Transact-SQL server cursors, 248-250
custom server locking, 420
custom timeouts, 422-423, 427
customizing data, 344
D
data, identifying
how used, 118-119
requirements for, 117-118
data constraints. See constraints on data
data control language (DCL), 67-74
data customization, 344
Data Definition Language (DDL), 36, 65, 69-74
data encryption, 441
data formats, bcp utility, 232
data import/export architecture, 38. See also exporting data; importing data
data integrity, 169-175. See also constraints on data
DEFAULT definitions, 79, 157, 171
enforcing, 170-172
data types, 170
indexes, 172, 426. See also indexed views; indexes
triggers for. See triggers
implementing constraints, 176-190
NOT NULL definitions, 171
triggers, 313-314, 335
types of, 172-174
validation, 39, 313-314, 335
data manipulation language (DML), 36, 67, 69-74
data marts, 7
data models, logical, 124-135
identifying data constraints, 127-128, 132. See also constraints on data
identifying entities and attributes, 124-125, 495-501
identifying entity relationships, 126, 131, 489-520
data modification. See modifying data in databases
data retrieval. See retrieving data from databases
data security. See backups; security
data summaries. See summary reports
data synchronization. See replication
data transformations, 235
data types (Transact-SQL), 80, 148-154
determining (sample questions), 513-520
enforcing data integrity, 170
NOT NULL definitions, 171
system supplies, 148-152
user-defined, 152
data warehousing, 7
Analysis Services system, 14
architecture, 40
DTS with, 13, 38. See also DTS
Database Designer for creating tables, 161
database diagrams, generating, 106
database languages, 42
database queries. See retrieving data from databases
DATABASEPROPERTYEX statements, 141
databases, 5. See also tables in relational databases
access to, determining needs for, 118-119, 673-693
checking for duplicate records, 305
creating, 138-141, 143-158, 525-534
data integrity of. See data integrity
deleting, 143
designing, 96-107
English Query with, 14
entity relationships, 102-103, 106, 126, 131
importing and exporting data, 230-240
bcp utility and BULK INSERT statements, 230-233, 237-240, 571-576
DTS tools, 234-237
sample questions, 571-576
views for, 344
logical data models, 124-135
identifying data constraints, 127-128, 132. See also constraints on data
identifying entities and attributes, 124-125, 495-501
identifying entity relationships, 126, 131
logical database components, 24-26, 96
modifying, 141-147. See also modifying data in databases
sample questions, 525-534
setting options, 142
troubleshooting (sample creation), 559-563
monitoring. See SQL Profiler
normalization. See normalization
optimizing. See designing databases; tuning indexes
partitioning, 481-482
physical architecture, 27-30, 112, 521-563
planning, 108-114
environment considerations, 111
files and filegroups, 108-110, 138
identifying system requirements, 115-123
physical design, 112
security considerations, 113. See also security
size estimates, 111, 139
SQL Server 2000 installation, 112
transaction logs, 110
relational database engine, 11, 30-31
restoring to point of failure, 394
retrieval data from. See retrieving data from databases
security of. See security
server errors, testing for, 298-300, 614
SQL Server 2000
architecture for, 22-24
SQL Server 2000 installation, 112
synchronizing. See replication
tables, creating, 155-161
autonumbering and identifier columns, 158-160
default values, defining, 157-158
tables, managing, 162-164
troubleshooting activity (sample problems), 667-671
viewing information about, 141
Data-Driven Query task (DTS), 235
date and time data types, 149
datetime data type, 149
DBCC statements
DBCC DBREINDEX statement, 383
DBCC INDEXDEFRAG statements, 660
DBCC SHRINKDATABASE statements, 142, 398
DBCC SHRINKFILE statements, 142, 398
DBCC validation, 39
DB-Library client cursors, 252
DCL (data control language), 67-74
DDL (Data Definition Language), 36, 65, 69-74
deadlock management, 421-422
DEALLOCATE statement (Transact-SQL), 249, 255
debugging Transact-SQL, 55, 62
decimal data type, 150
DECLARE CURSOR statements (Transact-SQL), 249, 253, 254
DEFAULT definitions, 79, 157, 171. See also data integrity
default filegroups, 109, 142
DEFAULT keyword, 79
default values in records, 157-158
deferred name resolution, stored procedures, 283, 331
defragmenting indexes, 660
DELETE permission, 441
DELETE statements, 68, 73, 222-225, 583
executing singly, 88
invoking procedures after. See triggers
partitioned views, deleting data through, 361
Deleted logical table (triggers), 330
deleting. See also modifying data in databases
columns, 162
data, 221-223, 225, 360-361
data files, 147
databases, 143
indexes, 383, 390
roles, 457-458
rows. See DELETE statements
stored procedures, 287, 291
tables, 163, 222, 225
triggers, 325, 328
views, 354, 356
delimited identifiers (Transact-SQL), 76
DENY statements, 67, 457, 677-687
denying permissions, 440, 450, 677-687
deployment of databases, ease of, 6
DESC keyword (ORDER BY clause), 199
descending sorts, query results, 199
designing databases, 96-107
entity relationships, 102-103, 106, 126, 131
normalization. See normalization
Desktop Engine, SQL Server 2000, 9
deterministic functions, 80
Developer Edition, SQL Server 2000, 9
DISABLE TRIGGER clause (ALTER TABLE), 326, 328
disabling FOREIGN KEY and CHECK constraints, 182, 184
disabling triggers, 326, 328
disk partitioning, 481
DISTINCT keyword (SELECT statement), 195
distributed partitioned views, 351, 482
distributed queries, 39, 241-247
ad hoc computers, 244
linked servers, 242-243, 246, 577
OPENQUERY function, 243, 246, 577
OPENROWSET, OPENDATASOURCE functions, 244-245, 247
sample questions, 577-582
distributed transactions, 401, 411-412
Distributor server (replication), 40
DML (data manipulation language), 36, 67, 69-74
document handle, XML, 262
documentation for SQL Server 2000, 16
domain integrity, 173, 513-520
domain security, 434
DROP statements, 66, 73
DROP DATABASE, 145
DROP FUNCTION, 79
DROP INDEX, 383, 390
DROP PROCEDURE, 287, 291
DROP TABLE, 66, 222, 225
DROP TRIGGER, 325, 328
DROP VIEW, 354
DROP_EXISTING clause (CREATE INDEX), 384, 390
DTS (Data Transformation Services), 13, 38, 565
DTS Designer, 234
DTS Query Designer, 234
DTS Run utility, 234
importing and exporting data, 234-237
packages, 234-237
execution utilities, 234
workflow design, 237
sample questions, 571-576
duplicate records, checking for, 305
durability of transactions, 404, 607
E
editions of SQL Server 2000, 9
ELEMENTS option (SELECT statement), 260
encryption
data encryption, 441
packet encryption, 432
enforcing data integrity, 170-172
indexes, 172, 367-391. See also indexed views
creating, 378-382, 387, 389
custom server locking, 426
how best to use, 384-386
index architecture, 368-375
maintenance and administration, 382-384
performance of, 386
tuning, 478-481, 657-665
triggers for. See triggers
engine, database, 11, 30-31
English Query, 14
Enterprise Edition, SQL Server 2000, 9
Enterprise Manager, SQL Server, 18
databases, creating, 140, 146, 380
index management, 378-380
Index Tuning wizard, 480
security administration and configuration
authentication, 449
authorization, 451
permissions, 453
roles, 457, 458
stored procedures, 281, 286
tables, creating, 161
triggers, creating, 322
viewing database information, 141
entities
identifying, 124-125, 495-501
integrity, 173
keys. See constraints on data
relationships between, 102-103, 106, 126, 131
environment considerations in planning databases, 111
@@ERROR function, 298, 300, 614
error handling, stored procedures, 295-300, 303, 614
raising custom errors, 333, 615
transaction processing, 406, 408-409
Estimated Execution Plan tab (SQL Query Analyzer), 54, 63
estimating database size, 111, 139
auditing, 441
monitoring with SQL Profiler, 470
trigger events, 315
exact match queries, indexes and, 385
ExecIsStartup property (OBJECTPROPERTY function), 285
EXECUTE permission, 441
Execute SQL task (DTS), 235
EXECUTE (EXEC) statements, 49, 89, 283, 289, 456
executing stored procedures, 283-286, 289
executing Transact-SQL statements, 87-93
SQL Query Analyzer, 49, 62
stored procedures and triggers, 91-92, 172, 613. See also stored procedures; triggers
executing triggers, process of, 315
execution plan
analyzing (sample problems), 645-650
displaying, 51, 54, 63
index tuning, 478, 482
stored procedures and triggers, 92
viewing index properties, 374
Execution Plan tab (SQL Query Analyzer), 51
sample problems, 645-650
viewing index properties, 374
EXISTS keyword, subqueries with, 208, 214
EXPLICIT mode (XML), 260, 602
explicit transactions, 406, 413, 613
exporting data, 230-240
bcp utility and BULK INSERT statements, 230-233, 237-240
DTS tools, 234-237
export/import architecture, 38
sample questions, 571-576
views for, 344
expressions (Transact-SQL), 80
extended stored procedures, 272, 282
extensibility, 521
Extensible Markup Language. See XML
external data
accessing with distributed queries, 241-247, 577-582
ad hoc computers, 244
linked servers, 242-243, 246, 577
OPENQUERY function, 243, 246, 577
OPENROWSET, OPENDATASOURCE functions, 244-245, 247
importing and exporting, 230-240
bcp utility and BULK INSERT statements, 230-233, 237-240, 571-576
DTS tools, 234-237
views for, 344
F
fact tables, 14
federation of database servers, 351, 481
FETCH statements (Transact-SQL), 249, 250-255
fetching with cursors, 249, 250, 252-253, 255
filegroups, 108-110, 138, 142
files
partitioning, 481
planning databases, 108-110, 138
transaction logs. See transaction log files
fill factor, indexes, 372, 381, 541
FILLFACTOR keyword (CREATE INDEX), 381, 541
filtering tables for viewing. See views
firewalls, 433
flags, OPENXML function, 262
float (floating point) data type, 150
FOR clause (CREATE TRIGGER statement), 321
FOR XML clause, 258-260, 263, 601-606
FOREIGN KEY constraints, 180-183. See also data integrity
adding and deleting, 163, 187-188
deleting tables, 163
designing (sample questions), 503-511
disabling, 182
four-part linked server names, 243
FROM clause
DELETE statement, 222
REVOKE statement, 456
SELECT statement, 197, 203. See also joins to retrieve data
UPDATE statement, 220
full outer joins, 206, 212
full text query architecture, 33
full-text indexing, 375
functions, Transact-SQL, 77-80
in triggers, 332
user-defined, 79, 613
G
global temporary stored procedures, 271
globally unique identifiers, 158-160
GO command, 84, 89
goals, defining for database system, 116-117
GOTO keyword, 82
GRANT statements, 67, 71, 454-456, 677-687
granting permissions, 440, 450, 677-687
graphical utilities, 37
greater than (>) operator, subqueries with, 208
Grids tab (SQL Query Analyzer), 49
GROUP BY clause (SELECT statement), 198-201, 584
group permissions, 437-440, 446
administering and configuring roles, 457-460
sample questions, 689-693
H
HAVING clause (SELECT statement), 197-199
help files for SQL Server 2000, 16
heterogeneous data manipulation (sample questions), 577-582
hints, table-level locking, 424-428
HOLDLOCK hint, 424, 637
HTML (Hypertext Markup Language), 257
I
identification numbers for stored procedures, 277
identifier columns, 158-160
identifiers (Transact-SQL), 75-76, 98
identifying system requirements, 115-123
amount of types of data, 117-118
book shop scenario (example), 120-123
business rules, 119
how data are used, 118-119
system goals, 116-117
Identity Increment property, 159
IDENTITY properties, 159, 171. See also data integrity
Identity Seed property, 159
idoc (XML document handle), 262
IF.ELSE statements, 82
IGNORE_DUP_KEY keyword (CREATE INDEX), 381
image data
adding to databases, 218
updating in databases, 221
image data type, 149
implicit transactions, 409-410, 415
implied permissions, 441
Import/Export wizards, DTS, 234
importing data, 230-240
bcp utility and BULK INSERT statements, 230-233, 237-240, 571-576
DTS tools, 234-237
import/export architecture, 38
sample questions, 571-576
views for, 344
XML data, 257-265
IN keyword (subqueries), 207, 213
incomplete transactions, recovering, 394
Index Tuning wizard (SQL Server), 386, 661-665
indexed views, 343, 348-351
indexes, 172, 367-391
architecture of, 368-375
clustered vs. nonclustered, 369-370
updating when data change, 369
creating, 378-382, 387, 389
Manage Indexes option (Enterprise Manager), 378-380
Transact-SQL statements for, 380-382
custom server locking, 426
full-text indexing, 375
how best to use, 384-386
maintenance and administration, 382-384
deleting, 383, 390
rebuilding, 383
renaming, 384, 390
performance, 386
support, 33
tuning, 478-481
Index Tuning wizard, 478, 481-482
sample problems, 657-665
viewing information about, 373
views. See indexed views
INDEXPROPERTY function, 426
inline table functions (Transact-SQL), 79
inner joins, 204, 211
INSENSITIVE keyword (DECLARE CURSOR statement), 253
INSERT permission, 441
INSERT statements, 68, 72, 216-218, 583
disabling FOREIGN KEY and CHECK constraints, 182, 185
executing singly, 88
INTO keyword (optional), 68, 217
invoking procedures after. See triggers
partitioned views, adding data through, 360
SELECT subqueries in, 218
VALUES clause, 217-218, 223
Inserted logical table (triggers), 330
inserting data into databases, 216-219
bulk copying. See bulk copy operations
through views, 359, 360
installation of databases, ease of, 6
installing SQL Server, 112
INSTEAD OF clause (CREATE TRIGGER statement), 321
INSTEAD OF triggers, 315-316, 361, 616
Int (integer) data type, 150
integrating databases with other products, 8
integrity validation, 39, 313-314, 335
interactive mode, bcp utility, 233
INTO clause (SELECT statement), 197, 218, 224
INTO keyword (INSERT statement), 68, 217
I/O (input/output) architecture, 33, 112
IPSec (IP Security), 433
isolation of transactions, 403, 419-420, 423-424, 427, 607
isql, isqlw utilities (SQL Query Analyzer), 58
J
joins to retrieve data, 203-206
indexes with, 385
inner joins, 204, 211
outer joins, 205-206, 211-213
L
languages, database, 42
left outer joins, 205, 211
lengths of data types, 149
less than (<) operator, subqueries with, 208
linked servers, 242-243, 246, 577
local computer security, 434
local partitioned views, 351
local stored procedures, 271
local temporary stored procedures, 271
locking. See transactions and locking
@@LOCK_TIMEOUT function, 423
LOCK_TIMEOUT setting, 423
log files, partitioning, 481
log sequence numbers (LSNs), 395
logical data models, 124-135
identifying data constraints, 127-128, 132. See also constraints
on data
identifying entities and attributes, 124-125, 495-501
identifying entity relationships, 126, 131
sample questions, 489-520
logical database components, 24-26, 96
normalization. See normalization
transaction logs, 395
logical (pseudo) tables (triggers), 330-331
LSN (log sequence numbers), 395
M
maintainability, 521
Manage Indexes option (Enterprise Manager), 378-380
managing databases, ease of, 6
manipulating data. See modifying data in databases
many-to-many entity relationships, 103
mapping, specifying in OPENXML function, 262
Master database, 143, 270
memory, architecture of, 32
merge replication. See replication
Message Queue task (DTS), 235
Messages tab (SQL Query Analyzer), 54
Meta Data Services, 15
metadata, 15
Microsoft Distributed Transaction Coordinator (MS DTC), 411
Microsoft Management Console (MMC), 18
Microsoft Meta Data Services. See Meta Data Services
Microsoft search service, 20
Microsoft SQL Server 2000. See SQL Server 2000
minimal keys, 503
minimizing deadlock, 421
Mixed authentication, 113, 436, 673. See also authentication
MMC (Microsoft Management Console), 18
modifying data in databases, 216-226. See also retrieving data from databases
bulk copying. See bulk copy operations
changing and updating data, 219-221, 224
concurrency control, 399, 405
optimistic vs. pessimistic, 399, 419
types of problems, 417-418
deleting data, 221-223, 225, 360-361
heterogeneous data, 577-582
inserting data, 216-219
sample questions, 565-606
transactions. See transactions and locking
updating indexes, 369
views, 344, 357-361
modifying stored procedures, 286, 290
modifying triggers, 323, 327
modifying views, 353, 355
money data type, 150
monitoring databases. See SQL Profiler
MS DTC transactions, 412
MSDE (Desktop Engine), SQL Server 2000, 9
MSSQLServerOLSPService service, 20
multi-statement table functions (Transact-SQL), 79
N
names
ad hoc computers, 244
database tables, 163
databases, 142
deferred resolution, stored procedures, 283, 331
indexes, 384, 390
linked servers, 242
stored procedures, renaming, 286
triggers, 323, 327
views, 346, 353
navigating database objects, 55, 61
nchar data types, 151
nesting permissions, 445
nesting stored procedures, 300
network protocol security, 432-433
New Technology File System (NTFS), 434
NEWID function, 160
NOLOCK hint, 424, 640
nonclustered indexes, 369, 370, 657
creating, 387
rebuilding, 383
nondeterministic functions, 80
normalization, 5, 97-101
example of, 104
identifiers for tables, 98
nullable columns, avoiding. See nullability
repeated values or columns, 101
separating entities by type, 99
NOT EXISTS keywords, subqueries with, 209
NOT IN keyword (subqueries), 207
NOT NULL definitions, 156, 171. See also data integrity
ntext data, 151
adding to databases, 218
updating in databases, 221
NTFS (New Technology File System), 434
NULL keyword, 156
NULL values, testing for, 297
nullability, 100, 152, 155-156, 166, 171, 513
numbering records automatically, 158-160
numbers, precision and scale of, 149
numeric data type, 150
nvarchar data types, 151
O
OBDC Cursor Library, 252
Object Browser (SQL Query Analyzer), 55, 61, 374
object encryption, 441
object permissions, 440, 683-687
OBJECTPROPERTY function, 159, 272, 285
OLAP (Online Analytical Processing), 13, 40-41, 549
OLE DB providers, 241
OLTP (Online Transaction Processing), 13, 40, 549
ON clause (CREATE TRIGGER statement), 321
ON keyword (GRANT statement), 455
one-to-one entity relationships, 102
one-to-one many relationships, 103
Online Analytical Processing. See OLAP
online documentation for SQL Server 2000, 16
Online Transaction Processing. See OLTP
Open Search window (SQL Query Analyzer), 57, 61
OPEN statement (Transact-SQL), 249, 254
Open Table window (SQL Query Analyzer), 57
OPENDATASOURCE function, 244-245, 577
OPENQUERY function, 77, 243, 246, 577
OPENROWSET function, 244-245, 247, 577
OPENXML function, 260-263, 264, 577
operating system auditing, 441
operating system files, planning for, 108-111, 138
operating system security, 434
operators (Transact-SQL), 80
optimistic concurrency control, 399, 419
optimizing programming objects, 635-640
optimizing queries, 87
. See also indexes
options, database, 142
ORDER BY clause (SELECT statement), 195, 199, 200, 373, 584
osql utility (SQL Query Analyzer), 59
outer joins, 205-206, 211-213
ownership
databases, changing, 142
ownership chains, 445
tables, changing, 163
P
packages, DTS, 234-237
execution utilities, 234
workflow design, 237
packet encryption, 432
pad index, 373
PADLOCK hint, 424, 640
parameters, stored procedures
programming, 293-295
setting to NULL, 297
specifying for execution, 284-285
partitioned views, 343-345, 614
creating, 351-353
modifying data through, 360-361
sample questions, 549-557
partitioning databases, 481-482
pausing a trace, 473
PERCENT keyword (SELECT statement), 195
performance, 521
database monitoring. See SQL Profiler
encryption, 433
indexes, 386
partitioning databases, 481-482
sample problems, 641-671
stored procedures, 268
synchronizing data. See replication
transactions, 405
tuning, 478-481, 657-665
permissions, 432, 435-441, 461-462. See also DCL; security
administering and configuring authorization, 453-457
creating triggers, 321
granting. See GRANT statements
ownership chains, 445
revoking. See REVOKE statements
sample problems, 677-687
Personal Edition, SQL Server 2000, 9
pessimistic concurrency control, 399, 419
physical database architecture, 27-30, 112, 521-563
physical security, 432
planning databases, 108-114, 489-520
environment considerations, 111
files and filegroups, 108-110, 138
identifying system requirements, 115-123
physical design, 112, 521-563
security considerations, 113, 443-448. See also security
size estimates, 111, 139
SQL Server 2000 installation, 112
transaction logs, 110
precision of numbers, 149
predefined roles, 446
prepare phase, transactions, 411
PRIMARY KEY constraints, 177-179. See also data integrity
adding and deleting, 163, 185-188
clustered indexes, 369
deleting tables, 163
designing (sample questions), 503-511
index tuning, 479
primary system files, 108, 138
PRINT statements in triggers, precautions, 333
private temporary stored procedures, 271
privileges, authentication, 436
nesting, 445
ownership chains, 445
procedural data integrity, 313
processing Transact-SQL. See executing Transact-SQL statements
Profiler. See SQL Profiler
pseudo tables (triggers), 330-331
Public role, 455, 684
Publisher server (replication), 40
Q
Query Analyzer, SQL, 20, 46-64
isql, isqlw utilities, 58
Object Browser, 55, 61, 374
Open Search window, 57, 61
Open Table window, 57
osql utility, 59
Query window, 47-54
sample problems, 645-650
stored procedures, modifying, 286, 289
Transact-SQL Debugger, 55, 62
Query Designer, DTS, 234
query optimizer, 87. See also indexes
query processor architecture, 32
Query window (SQL Query Analyzer), 47-54
querying databases. See retrieving data from databases
R
RAISERROR system command, 333, 615
range queries, indexes and, 385
RAW mode (XML), 259, 601
RDBMS (relational database management system), 4
READCOMMITTED hint, 425
READPAST hint, 425
READUNCOMMITTED hint, 425
real data type, 150
rebuilding indexes, 383
recovering transactions, 110. See also transactions and locking
Recovery database options, 143
referential integrity, 173
registering extended stored procedures, 282
regular identifiers (Transact-SQL), 76
relational database engine, 11, 30-31
relational database management system (RDBMS), 4
relational databases, 5. See also databases
database languages, 42
English Query with, 14
normalization. See normalization
synchronizing. See replication
remote procedure call (RPC) encryption, 432
remote stored procedures, 273
removing data. See deleting
renaming. See names
REPEATABLEREAD hint, 425
repeated values or columns in tables, 101
replication, 13, 38, 549-557
architecture of, 40
disabling FOREIGN KEY and CHECK constraints, 183, 185
resource locking. See transactions and locking
restarting a trace, 473
restore architecture, 38
restoring lost databases, 394
restricting access. See permissions; security
Results tab (SQL Query Analyzer), 50
retrieving data from databases, 194-202. See also importing data; modifying data in databases
bulk copying. See bulk copy operations
cursors for. See cursors
distributed queries, 241-247, 577-582
ad hoc computers, 244
linked servers, 242-243, 246, 577
OPENQUERY function, 243, 246, 577
OPENROWSET, OPENDATASOURCE functions, 244-245, 247
joins, 203-206
indexes with, 385
inner joins, 204, 211
outer joins, 205-206, 211-213
sample questions, 565-606
stored procedures for, 301-302
subqueries, 206-209
adding data to databases, 218
comparison operators with, 208, 213
EXISTS and NOT EXISTS with, 208, 214
IN and NOT IN with, 207, 213
summary reports, 209-210
CUBE operator, 209
ROLLUP operator, 210, 214
views, 357-364
modifying data, 357-361
viewing data, 357
XML data, 257-265
return code for errors, 295
RETURN statements, 82, 295-300, 303
reusing code with stored procedures. See stored procedures
REVOKE statements, 67, 72, 456, 677-687
revoking permissions, 440, 450, 677-687
RIDs (row IDs), 370
right outer joins, 205, 212
role-based permissions, 437-440, 446, 689-693
administering and configuring roles, 457-460
predefined roles, 446
ROLLBACK statements, 394, 407
ROLLBACK TRANSACTION command, 333
transaction boundaries, 404
rolling forward and back, 110-111, 394
ROLLUP operator, 210, 214
row IDs (RIDs), 370
@@ROWCOUNT function, 332
ROWGUIDCOL property, 160, 163
ROWLOCK hint, 425
rowpattern (XPath expression), 262
rows in database tables, 5
autonumbering, 158-160
data integrity of. See data integrity
deleting. See DELETE statements
duplicated, checking for, 305
exporting data. See importing data
fetching with cursors. See fetching with cursors
importing data. See exporting data
modifying data. See retrieving data from databases
retrieving data. See modifying data in databases
truncating (deleting all), 222, 315
views. See views
rowset functions (Transact-SQL), 77
rowset schema declarations, 263
RPC (remote procedure call) encryption, 432
rules, 172
run-time errors, transactions and, 408-409
S
scalability, 7, 11, 521
scalar functions (Transact-SQL), 78, 79
scale of numbers, 149
schema declaration, 263
SCHEMABINDING clause, 346, 538
scripts, Transact-SQL, 92
SCROLL keyword (DECLARE CURSOR statement), 253
search service, 20
searching database servers, 57, 61
searching for records. See indexes
secondary system files, 108, 138
Secure Socket Layer (SSL), 432
security, 113, 431-464, 521
auditing, 441
authentication. See authentication
authorization, 437-441, 451-453, 461
backups. See backups
data integrity validation. See validating data integrity
designing database security, 443-448, 673-693
encryption
object encryption, 441
packet encryption, 432
implementation and administration, 449-464
authentication, 449-451
authorization, 451-453
permissions, 453-457
roles and role membership, 457-460
overview of, 432-442
relational database engine, 13
stored procedures, 270
transaction logs, 110
SELECT permission, 441
SELECT statements, 68, 73, 194-202, 583
cursor locking, 254
executing, 87-88
joins, 203-206
indexes with, 385
inner joins, 204, 211
outer joins, 205-206, 211-213
results set, viewing. See views, accessing data
retrieving XML data, 257-260
ELEMENTS option, 260
FOR XML clause, 258-260, 263, 601-606
stored procedures, getting information on, 277
subqueries, 206-209
adding data to databases, 218
comparison operators with, 208, 213
EXISTS and NOT EXISTS with, 208, 214
IN and NOT IN with, 207, 213
summary reports, 209-210
CUBE operator, 209
ROLLUP operator, 210, 214
syntax of
AS keyword, 196
DISTINCT keyword, 195
FROM clause, 197, 203
GROUP BY clause, 198-201, 584
HAVING clause, 197-199
INTO clause, 197, 218, 224
ORDER BY keywords, 195, 199, 200, 373, 584
PERCENT keyword, 195
Top n keywords, 195, 583
WHERE clause, 197-199, 203
in triggers, precautions, 333
Send Mail task (DTS), 235
serializability, 419-420
SERIALIZABLE hint, 425
Server Agent, SQL, 19
server cursors, 248-252, 595-599. See also cursors
server errors, testing for, 298-300, 614
server federation, 351, 481
server locking. See transactions and locking
Server Network utility, SQL Server, 19
Service Manager, SQL Server, 20
SET clause (UPDATE statement), 220
SET IMPLICIT_TRANSACTIONS statements, 404, 409
SET LOCK_TIMEOUT statement, 423
SET NOCOUNT statements, 84
SET TRANSACTION ISOLATION LEVEL statement, 423
SET XACT_ABORT command, 406, 616
SGML (Standard Generalized Markup Language), 257
Show Execution Plan. See execution plan, displaying
shrinking databases, 142, 398
shrinking transaction logs, 398, 402
SHUTDOWN statements, 396
SIDs (security identifiers), 434
single Transact-SQL statements, executing, 87-88
size, databases, 111, 139
changing, 146
identifying amount of data, 117-118
shrinking databases, 142, 398
size, indexes, 372, 381, 541
smalldatetime data type, 150
smallint binary data type, 150
smallmoney data type, 150
snapshot replication, 549-557
SOME keyword (subqueries), 208
sorting indexes, 368, 373
sorting results from database queries, 199, 200
SORT_IN_TEMPDB keyword (CREATE INDEX), 381
sp_addapprole procedure, 458
sp_addlogin procedure, 451
sp_addmessage procedure, 333
sp_addrole procedure, 457
sp_addrolemember procedure, 458
sp_addsvrrolemember procedure, 458
sp_bindrule procedure, 172
sp_changedbowner procedure, 142
sp_changeobjectowner procedure, 163
sp_dboption procedure, 142
sp_denylogin procedure, 450
sp_depends procedure, 162, 287
sp_dropapprole procedure, 458
sp_droplogin procedure, 451
sp_droprole procedure, 458
sp_droprolemember procedure, 459
sp_executesql procedure, 636
sp_grantdbaccess procedure, 452
sp_grantlogin procedure, 450
sp_help procedure, 71, 104, 162, 373
sp_helpdb procedure, 141
sp_helpindex procedure, 373, 375
sp_helptrigger procedure, 325
sp_indexoption procedure, 426
sp_procoption procedure, 285
sp_rename procedure, 163, 286, 325, 384
sp_renamedb procedure, 142
sp_revokedbaccess procedure, 451, 452
sp_revokelogin procedure, 450
sp_serveroption procedure, 352
sp_setapprole procedure, 442, 459
sp_table_privileges procedure, 270
sp_who procedure, 270
sp_xml_preapredocument procedure, 261
sp_xml_removedocument procedure, 261
special data types, 151
SQL (Structured Query Language), 5
database options, 143
query processor architecture, 32
SQL Profiler, 19, 468-477
alternative monitoring tools, 468-469
events, data columns, filters, 470-471
running a trace, 471-475
sample problems, 651-656, 667-671
SQL Query Analyzer, 20, 46-64
isql, isqlw utilities, 58
Object Browser, 55, 61, 374
Open Search window, 57, 61
Open Table window, 57
osql utility, 59
Query window, 47-54
sample problems, 645-650
stored procedures, modifying, 286, 289
Transact-SQL Debugger, 55, 62
SQL Server 2000, 4-10
architecture of, 22-43
administration, 35, 37
application development, 41
database architecture, 22-24
logical database components, 24-26, 96
physical database architecture, 27-30, 521-563
relational database engine, 30-31
transactions, 34, 394-402
components of, 11-21
executing stored procedures when starting, 285-286
features and editions of, 6-9
installation, 112
security services. See security
SQL Server Agent, 19
SQL Server Client Network utility, 19
SQL Server Enterprise Manager, 18
creating databases, 140, 146, 380
creating tables, 161
Database Designer. See Database Designer
for creating tables
index management, 378-380
Index Tuning wizard, 480
security administration and configuration
authentication, 449
authorization, 451
permissions, 453
roles, 457, 458
stored procedures, 281, 286
triggers, creating, 322
viewing database information, 141
SQL Server Network utility, 19
SQL Server Service Manager, 20
SQL-92 isolation levels, 419-420
SQL-DMF (SQL Distributed Management Framework), 36
SQL_variant data types, 151
SSL (Secure Socket Layer), 432
stable keys, 503
Standard Edition, SQL Server 2000, 9
starting stored procedures automatically, 285-286
State database options, 143
statement permissions, 440, 677-687
statements, Transact-SQL. See executing Transact-SQL statements; Transact-SQL language
statistics on indexes, 386
Statistics tab (SQL Query Analyzer), 53
stopping a trace, 473
stored procedures, 91-92, 172, 613, 268-275. See also triggers
creating, 277-282, 287
cursors in, 300
deferred name resolution, 283, 331
deleting, 287, 291
executing, 283-286, 289
how stored, 276-277
modifying, 286, 290
programming, 293-308
data retrieval methods, 301-302
nesting procedures, 300
parameters and variables, 293-295
RETURN statement and error handling, 295-300, 303
security considerations, 270
troubleshooting and optimizing, 635-640
types of, 270-273
viewing contents of, 274
viewing list in Master database, 273
stored queries. See views
Structured Query Language. See SQL
subqueries, 206-209
adding data to databases, 218
comparison operators, 208, 213
EXISTS and NOT EXISTS with, 208, 214
IN and NOT IN with, 207, 213
Subscriber server (replication), 40
summary reports, 209-210
CUBE operator, 209
ROLLUP operator, 210, 214
surrogate keys, 503
synchronizing data. See replication
syntax of Transact-SQL. See Transact-SQL language, syntax
SysComments table, 277
sysindexes system table, 374
SysObjects table, 277
system commands in triggers, 332
system files, planning for, 108-111, 138
system goals, 116-117
system integration, 8
system requirements, identifying, 115-123
book shop scenario (example), 120-123
system stored procedures, 270, 273. See also entries at sp_
system-generated keys, 503
system-supplied data types, 148-152
T
Table data types, 151
Table Designer, 161
table functions (Transact-SQL), 79
table-level locking hints, 424-426, 428
tables in relational databases, 5
autonumbering and identifier columns, 158-160
columns in. See columns in database tables
creating, 70, 155-161
sample questions, 535-548
troubleshooting (sample questions), 559-563
data integrity and constraints. See constraints on data; data integrity
default values, defining, 157-158
deleting, 163, 222, 225
identifiers for, 98
importing and exporting data. See exporting data; importing data
indexes of. See indexes
modifying, 70, 162-163
sample questions, 535-548
troubleshooting (sample creation), 559-563
modifying data. See retrieving data from databases
navigating with Object Browser, 55, 61
partitioning, 351
relationships between, 102-103, 106, 126, 131
retrieving data. See modifying data in databases
rows in. See rows in database tables
truncating (deleting all rows), 222, 315
viewing information about, 162
views of. See views
TABLOCK hint, 425, 640
TABLOCKX hint, 425
tasks, DTS, 235
TempDB database, 271
templates, trace, 473, 474
temporary stored procedures, 271-272
text data, 149, 218, 221
thread deadlock, 421-422
time data types, 149
timeouts, customizing, 422-423, 427
timestamp data type, 151
tinyint data type, 150
TO keyword (DENY statement), 457
tools. See utilities, SQL Server 2000
TOP n keyword (SELECT statement), 195, 583
trace data. See SQL Profiler
Trace tab (SQL Query Analyzer), 52, 651-656
trace templates, 473, 474
tracing Transact-SQL statements, 52, 651-656
transaction log files, 108, 110, 138, 394-398
architecture, 34, 395, 397
backups, 111
shrinking, 398, 402
write-ahead logs, 395
transactions and locking, 394-402. See also transaction log files
concurrency control, 399, 405
optimistic vs. pessimistic, 399, 419
types of problems, 417-418
cursor locking, 254
distributed transactions, 401, 411-412
locking architecture, 399-400
managing transactions, 403-406
replication. See replication
rolling forward and back, 110, 394
sample problems, 607-640
server locking, managing, 417-428
custom, 420
deadlock management, 421-422
isolation levels and serialization, 403, 419-420, 423-424, 427, 607
table-level hints, 424-426, 428
timeouts, customizing, 422-423, 427
types of transactions, 406-409
Transact-SQL Debugger (SQL Query Analyzer), 55, 62
Transact-SQL language, 36, 42, 65-74. See also SQL Query Analyzer
color-coding in SQL Query Analyzer, 48
debugging, 55, 62
distributed transactions, 411
executing. See executing Transact-SQL statements
implicit transactions, 409
sample questions, 583-594
scripts, 92
security administration and configuration
authentication, 450-451
authorization, 452-453
permissions, 454-457
statements, 65, 69-74. See also DCL; DDL; DML
syntax, 75-86
comments, 82
control-of-flow elements, 81
data types. See data types
functions and expressions, 77-80
identifiers, 75-76, 98
operators, 80, 81
variables, 76
Transact-SQL server cursors, 248-250. See also cursors
controlling behavior of, 253
fetching rows, 249, 250, 252-253, 255
sample questions, 595-599
Transform Data task (DTS), 235
transformations, DTS, 235
triggers (Transact-SQL), 91-92, 172, 311-338, 613, 625
creating
CREATE TRIGGER statement, 320-322, 331-332
Enterprise Manager for, 322
data integrity, 313-314, 335
events, 315
execution process, 315
modifying or deleting, 323-326, 327, 361
programming, 330-338
common tasks, 333-335
disallowed statements within, 333
pseudo (logical) tables, 330-331
syntax, commands, functions, 331-333
troubleshooting and optimizing, 635-640
troubleshooting
database activity (sample problems), 667-671
object creation, 559-563
programming objects, 635-640
TRUNCATE TABLE statements, 222, 315
truncating transaction logs, 397
tuning indexes, 478-481
Index Tuning wizard, 478-482
sample problems, 657-665
type/instance distinction, 16
U
unicode data types, 151
UNION ALL statement, 360, 539
UNION operator, 344, 539
unique clustered indexes, 346, 369-370, 371, 657. See also indexed views
UNIQUE constraints, 179-180, 503-511. See also data integrity
deleting tables, 163
unique identifiers, 158-160
uniqueidentifier data type, 151, 160
UPDATE clause (CREATE/ALTER TRIGGER), 331-332
UPDATE permission, 441
UPDATE statements, 68, 73, 219-221, 224, 583
APIs and cursors with, 221
disabling FOREIGN KEY and CHECK constraints, 182, 185
executing singly, 88
FROM clause, 220
invoking procedures after. See triggers
partitioned views, modifying data through, 361
SET clause, 220
WHERE clause, 220
updating data in databases, 219-221, 224
UPDLOCK hint, 425
URLs (uniform resource locators), 42
USE keyword, 62
user interface tools, 18
user permissions, 437-440, 446, 457-460, 689-693. See also permissions
user-defined data types, 152
user-defined filegroups, 109
user-defined functions (Transact-SQL), 79, 613, 635-640
user-defined integrity, 174
User-to-Activity Map, 443, 444
utilities, SQL Server 2000, 16-21
command-prompt tools, 17
graphical tools, 37
user interface tools, 18
V
validating data integrity, 39, 313-314, 335
VALUES clause (INSERT statement), 217-218, 223
varbinary data type, 149
varchar data type, 149
variables (Transact-SQL), 76, 293-295
views, 341-364
accessing data, 357-364
creating, 346-353, 354
sample questions, 535-548
troubleshooting (sample questions), 559-563
indexed, 343, 348-351
modifying, 357-361, 535-548
modifying or deleting, 353-354, 355
partitioned, 343-345, 351-353, 614
modifying data through, 360-361
sample questions, 549-557
scenarios for using, 343-345
troubleshooting and optimizing, 559-563, 635-640
virtual log files, 397
virtual tables. See views
volume of data, identifying, 117-118
W
WAITFOR keyword, 82
warehousing. See data warehousing
WHERE clause
DELETE statement, 222
SELECT statement, 197-200, 203. See also joins to retrieve data
UPDATE statement, 220
WHILE keyword, 82
wildcard queries, indexes and, 385
Windows account authentication, 450
Windows Authentication. See authentication
Windows CE Edition, SQL Server 2000, 9
WITH APPEND clause, 320
WITH clause (OPENXML function), 263
WITH ENCRYPTION clause, 441
WITH GRANT OPTION clause, 454
wizards, SQL Server 2000, 21
write-ahead transaction logs, 395
X
XACT_ABORT setting, 406, 616
XLOCK hint, 425
XML (Extensible Markup Language), 6, 257-265
retrieving data with FOR XML clause, 258-260, 601-606
retrieving data with OPENXML function, 260-263, 264, 577
retrieving data with SELECT statement, 257-260
XMLDATA keyword, 260, 602
XPath expression (rowpattern), 262
XPath language, 43
xp_loginconfig procedure, 441
Last Updated: May 15, 2003
|
