MCSE Readiness Review, Exam 70-029 : Designing and Implementing Databases with Microsoft SQL Server Book

Index

Numbers and Symbols
* (asterisk)
    as multiplication operator, 198
    in a DELETE statement, 168
    in a SELECT list, 224
    in a SELECT statement, 212
@@ (at at symbols), preceding global variables prior to SQL Server 7.0, 112
@ (at symbol), preceding local variables, 110, 114, 118
@@ CONNECTIONS function, 160
.. (double dot), referring to missing parameters, 114
/ (division) operator, 198
@@ ERROR function, returning a value of 0, 230
@locallogin parameter, 280
­ (minus sign)
    as negative unary operator, 198
    as subtraction operator, 198
+ (plus sign)
    as addition operator, 198
    as positive operator, 198
@@NESTLEVEL function, 160, 248
@@ROWCOUNT function, 160
@rmtpassword parameter, 280
@rmtsrvname parameter, 280
@rmtuser parameter, 280
/ (slash), as division operator, 198
@@TRANCOUNT function, 160
@useself parameter, 280
­1 setting in the SET LOCK_TIMEOUT statement, 156
1NF (first normal form), 3
2NF (second normal form), 3
3NF (third normal forma), 3

A
Access. See Microsoft Access database
"Accessing Heterogeneous Data with Microsoft SQL Server 7.0" white paper, 347
account number, SQL Server-generated, 26
active locks, 361, 402
Actual Count value, 375, 376, 377, 378
addition (+) operator, 198
aggregate functions, 193, 251
AllowsNull values, 202
ALTER DATABASE statement, 300
ALTER VIEW statement, 300
ANSI standard style option, 200
API functions in cursors, 140
ASCII text files, 337-38
AS keyword, 212
asterisk (*)
    as multiplication operator, 198
    in a DELETE statement, 168
    in a SELECT list, 224
    in a SELECT statement, 212
attributes of files and filegroups, modifying, 300
availability, 89, 92, 100
average bytes free statistic, 375, 376
average page density, 375, 376

B
BACKUP statement, 358
basic locks, 397
batches. See Transact-SQL batches
bcp utility. See bulk copy program (bcp)
BEGIN...END block, 193, 204
Best Count value, 375, 376, 377, 378
BETWEEN operator, 174
Boolean search condition, specifying in a constraint, 61, 64, 68, 70
BREAK statement inside a statement block, 193
bulk copy program (bcp), 277, 335, 338, 342, 346
BULK INSERT statement, 335, 338, 342
bulk update locks, 397, 400, 406
business rules
    for data integrity, 2, 45-59
    for domain integrity, 48, 52, 54
    for entity integrity, 48, 50, 56, 58
    for referential integrity, 48, 56, 58
    for user-defined integrity, 52, 54, 56, 58

C
Cartesian product result set, 188
catalogs. See full-text catalogs
character-based data, querying, 289, 349
character strings, executing, 110, 116
char data type, 310, 312
CHECK constraints, 34, 61, 64, 307
    column-level, 70, 318
    defining an allowable return date, 54
    query performance and, 80
    referencing other columns, 48
    table-level, 70
clustered indexes, 321, 324
    compared to nonclustered, 326
    in normalized databases, 98
    performance of, 326
clustered index scan, 381
column-definition section
    in a CREATE DATABASE statement, 307
    in a CREATE TABLE statement, 318
column definition values, 307
column integrity. See domain integrity
column-level CHECK constraint, 70, 318
column names, 307, 318
COLUMNPROPERTY function, 202
columns
    assigning a null value to, 314
    assigning NOT NULL to, 314
    calculating summary values on, 251
    configuring to contain system-generated values, 26
    creating indexes on frequently accessed, 321, 328
    creating with system-generated sequential numeric values, 307
    defining in CREATE DATABASE, 307
    determining whether to index specific, 328
    excluding from a SELECT list, 224
    multivalue, 10
    naming, 307, 318
    preventing users from accessing, 246
    specifying for return by a query, 260
    specifying participation in full-text indexing, 352
comparison operator in inner joins, 179
comparisons, performing with conditional operators, 163
compilation of a stored procedure, 234
composite index, 324, 326
COMPUTE BY clause, CREATE VIEW statement and, 212, 214
COMPUTE clause, CREATE VIEW statement and, 212, 214
concurrency impact of table locks, reducing, 96, 98
concurrency problems, 407-10
concurrent transactions, 149
    preventing access to a resource by, 400
    supporting, 397
conditional operators, 163
conditions in IF statements, 204, 206
@@CONNECTIONS function, 160
connections, returning the number of, 160
constraints, 17, 61
    classes of, 61
    compared to triggers, 36, 38, 42
    enforcing data integrity, 98, 100
    incorporating into the logical database design, 2
    maintainability and, 100
CONTINUE statement, inside a statement block, 193
control of flow language elements, 193
CONVERT clause, 200
COUNT aggregate function, 256
COUNT clause in a stored procedure, 132
CREATE DATABASE statement
    allocating transaction log space with a LOG ON clause, 302
    ensuring data integrity, 307
CREATE INDEX statement, FILLFACTOR option with, 330
CREATE PROCEDURE statement, 224
    defining parameters, 228, 230
    including an IF clause, 226
    initializing variables, 226, 228
CREATE PROCEDURE...WITH RECOMPILE statement, 234
CREATE STATISTICS statement, 361
    evaluating, 367-68
    FULLSCAN option, 368
    NORECOMPUTE option, 368
    SAMPLE number PERCENT option, 368
CREATE TABLE statement, 196, 307, 318
Create Trace Wizard, 363, 413, 420, 424
CREATE VIEW statement, 212, 214, 300
CROSS JOIN keyword, ON clause and, 190
cross joins, 179-80, 188
CUBE operator. See WITH CUBE operator
Current Activity Window in SQL Server Enterprise Manager, 361, 402, 416, 418, 422
cursors, 135, 152
    deallocating references to, 144
    declaring, 144
    defining the scope of, 146
    methods for requesting, 135
    populating with result sets, 144
    retrieving specific rows from, 144
    syntax of, 136
    types of, 135
customer ID number, using as the primary key, 28

D
data
    accessing in a SQL Server database, 107
    ensuring the reliability of, 397
    exporting to a text file from a table, 337-38
    modifying with views, 209
    querying from a remote server, 282
    transferring and managing, 342
database design. See logical database design
database files, 293
database ID (DBID) column, returned by sp_lock system stored procedure, 402
database maintenance, 361
database management systems (DBMSs), 335
database name, setting in a Transact-SQL batch, 110
database role, 130
databases, 289
    accessing data in, 107
    adding or removing files and filegroups, 300
    adding user-defined, 312
    creating, 301-2
    decreasing the size of, for quick recovery, 94
    dropping, 298
    estimating the size of, 94
    implementing full-text searches in, 354
    initializing full-text indexing on, 352
    maintaining, 361
    maximizing the performance of, 304
    minimizing deadlocks in, 160
    monitoring activity, 298
    monitoring performance of, 423-24
    naming, 302
    normalizing, 75, 80
    practices for creating and modifying, 289-90
    practices for populating tables in, from an external data source, 291
    quality of, 89, 100
    tools for creating, 293
    tuning, 419-20
data files
    importing and exporting, 338
    shrinking in a database, 300
data integrity, 45
    compromising with concurrency problems, 408
    enforcing, 17, 98, 237
    ensuring, 70, 307-8
    identifying business rules for enforcing, 2
    methods for enforcing, 45
    types of, 2, 45
data marts, consolidating data to build, 344
data modification statements
    executing against views, 218
    types of, 242, 244
Data Transformation Services (DTS), 277, 289, 335-36, 338, 340, 342
    moving data between an Oracle database and a SQL Server database, 340, 344
    referencing global variables, 112
data types
    assigning to local variables, 114
    for columns, 307, 318
    detailed reference explaining, 119
    naming, 312
    specifying, 200
data warehouses, consolidating data to build, 344
date and time category for SET statements, 267
DATEPART function, 174
date range, specifying in a WHERE clause, 174
DBCC SHOWCONTIG statement, 361, 362, 365, 376, 384
    statistics returned by, 377, 378
DBCC SHRINKFILE statement, 300
DBCC SQLPERF statement, 300
DBID (database ID) column, returned by sp_lock system stored procedure, 402
DB-Library bulk copy API, 338, 342, 346
DBMSs (database management systems), 335
deadlocks
    avoiding with update locks, 400
    identifying the causes of, 416, 420
    minimizing, 160, 404
    practices for identifying the causes of, 363
DEALLOCATE statement, 144
decision support system. See DSS (decision support system)
declarative data integrity, 45
DECLARE CURSOR statement, 144
DECLARE statement
    declaring variables, 110
    defining a counter local variable, 196
    defining a customer ID local variable, 114
    defining local variables, 110, 112, 118, 193
DEFAULT constraint, 61, 64, 66, 72
    creating a default checkout date, 50
    creating a default return date, 52
DELETE keyword, specifying in a FOR clause for a trigger, 244
DELETE statement, 168
    asterisk (*) in, 168
    deleting rows from a table, 170
    including in a trigger, 240
    with joins and subqueries, 163
    removing all records from a table, 170
DELETE trigger, 34, 56, 237
denormalization, 75, 78, 80, 82, 94
denormalized databases, 3
    compared to normalized, 96
    NULL values and, 98
    redundant data and, 98
denormalizing a logical database design, 1
descending order, specifying in an ORDER BY clause, 254
dirty reads, 154, 408, 410. See also uncommitted dependency
DISTINCT keyword in a SELECT statement, 163, 176
distributed queries, 277
distributed transactions, 149
division (/) operator, 198
domain integrity, 45
    business rules relating to, 48, 52, 54
    enforcing, 34, 36, 38, 42
double dot (..), referring to missing parameters, 114
DROP DATABASE statement, 298
DROP STATISTICS statement, 370
DSS (decision support system)
    creating indexes suited to, 330
    improving the performance of a, 77-78
DTS. See Data Transformation Services (DTS)
DTS Export Wizard, 335-36, 338, 342
DTS Import Wizard, 335-36, 338, 342
DTS packages
    defining and scheduling, 338
    scheduling for later execution, 344
duplicates, eliminating from a SELECT statement result set, 163
dynamic cursors, 135, 138, 146
    compared to static, 138
    defining, 142
    making read-only, 140
    updating rows in, 140
DYNAMIC keyword, 140, 142
dynamic statements, 107

E
ELSE keyword, 193
ELSE statement, 204
engine events, monitoring, 416, 422
engine process events, tracking, 390
Enterprise Manager. See SQL Server Enterprise Manager
entity integrity, 45
    business rules relating to, 48, 50, 54, 56, 58
    enforcing, 17, 72, 318
@@ERROR function, returning a value of 0, 230
event class, 416, 422
events
    monitoring, 421-22
    saving in trace files and re-executing, 413
events and performance problems, identifying, 361
Event Viewer, 416, 422
exclusive locks, 400, 406
exclusive table locks, 158, 397
EXECUTE permissions, 124, 130
EXECUTE statement
    building statements dynamically, 107
    executing a character string, 110, 116
    executing stored procedures, 124, 226
    expressions and, 114
    statement argument of, 107
EXECUTE...WITH RECOMPILE statement, 234
execution context, 365
execution plans, 365
    components of, 365
    displaying a graphical representation of, 381
    displaying information about, 361, 365-66
    evaluating and optimizing the performance of, 365-78
    returning information about, 267, 272, 372, 374
EXISTS keyword in an IF statement, 204
explicit transactions, 149
Export Wizard. See DTS Export Wizard
extended stored procedures, 121, 221
extensibility, 89, 92
extensions. See file extensions
extents, 378
extent scan fragmentation statistic, 375, 376, 377, 378
extents scanned statistic, 375, 376
extent switches statistic, 375, 376, 377, 378
external data, exporting into tables, 289
external data source, populating databases from, 335

F
FETCH options, specifying the availability of, 142
FETCH statement, retrieving a specific row from a cursor, 144
file extensions, 293, 296
FILEGROWTH setting, defining in the PRIMARY clause, 302
files, 289. See also database files
FILLFACTOR option
    with CREATE INDEX, 330
    optimizing the performance of INSERT and UPDATE statements, 330
    using with PAD_INDEX, 332
first normal form (1NF), 3
fixed-length, Unicode character data, data types supporting, 310
FOR clause, defining trigger types, 242, 244
FOREIGN KEY constraint, 31, 61, 72
foreign keys, 31
    creating, 31
    creating indexes on, 328
    defining to be unique, 14
    designating columns in tables as, 8
    enforcing referential integrity, 70
    indexes and, 78, 80, 82
forward-only cursors, 135, 138
FORWARD_ONLY keyword, 135, 142
fragmentation information, 365, 375, 376, 377, 378, 384
free space, specifying for non-leaf-level index pages, 332
FROM clause, 163, 260
FROM OPENROWSET clause within a SELECT statement, 282, 284
FULL OUTER JOIN clause, compared to LEFT OUTER JOIN, 186
full outer joins, 168, 179, 184
full population of full-text indexes, 354
FULLSCAN option, adding to CREATE STATISTICS, 368
full-text catalogs, 352, 354
    creating separate for large tables, 354
    storing full-text indexes in, 349
full-text indexes, 289, 349
    creating, 349
    populating, 354
    repopulating, 358
Full-Text Indexing Wizard, 349
full-text queries, issuing, 349, 356
full-text searches, 289, 349
    implementing, 354, 355-56
    maximizing the precision of, 356
    practices for implementing, 291-92
functions, 112, 193
    detailed reference explaining, 119
    provided by SET statements, 267
    types of, 193
"Fundamentals of Relational Database Design," 15, 29

G
GETDATE() clause, 132
GETDATE() keyword, 200
GETDATE() value, 196
global cursor, 146
GLOBAL keyword, 146
globally unique identifier (GUID), 307, 316
global variables, 112
GO command
    including with SET SHOWPLAN_TEXT, 272
    separating a USE statement from a CREATE VIEW statement, 212
GRANT statement, 240
Graphical Showplan option in SQL Server Query Analyzer, 362, 381, 390
GROUP BY clause, 260
    adding summary rows for all possible column combinations in, 264
    organizing rows into groups, 251, 258
    WITH CUBE operator with, 251
    WITH ROLLUP operator with, 251, 262
GROUPING function, using with the WITH ROLLUP or WITH CUBE operator, 251
group values, summarizing with WITH ROLLUP, 262
growth increment, specifying for the primary filegroup, 302
GUID (globally unique identifier), 307, 316

H
HAVING clause, 251, 260
heterogeneous data, accessing and distributing, 347
HOLDLOCK locking hint, 158

I
identifier names, rules for, 312
Identify the Cause of a Deadlock option in Create Trace Wizard, 420
identity column, 307, 314
IDENTITY property
    DEFAULT constraint and, 61
    identifying identity columns, 314
ID numbers, using as primary keys, 28
IF clause in a CREATE PROCEDURE statement, 226
IF...ELSE blocks, 193, 204
IF statement, 193
    conditions in, 204, 206
    in an IF...ELSE block, 204
    including an EXISTS keyword, 204
IF UPDATE clause in an UPDATE trigger, 246
implicit transactions, 149
Import Wizard. See DTS Import Wizard
inconsistent analysis, 408, 410
incremental population of full-text indexes, 354
increment for an identity column, 314
INDEX(0) optimizer hint, 381, 386, 388, 394
INDEX(1) optimizer hint, 381, 386, 388, 394
indexes, 78, 82, 289, 321, 328
    building an optimal set of, 418
    creating for large tables, 323-24, 325-26
    creating to update statistics, 370
    in normalized databases, 96
    performance and, 92
    practices for creating and maintaining, 290-91
    types of, 321
INDEX keyword in optimizer hints, 381
index pages, specifying the FILLFACTOR for, 330
index strategy, optimizer hints and, 388
Index Tuning Wizard, creating a workload for, 417-18
information message, 232
initial values, assigning for parameters, 230
INNER JOIN clause, 182
inner joins, 179, 188
input/output (I/O), returning information about, 267, 274, 361, 384
input parameters, 128, 221
INSERT keyword, inserting in a FOR clause in a trigger, 242
INSERT...SELECT statement, 163, 166
INSERT statement
    inserting a result set into a table, 126
    using a SELECT statement within, 163
INSERT trigger, 38, 42, 68, 70, 237
    adding to roll back a transaction, 52, 54, 58
insufficient resources messages, 232
intent locks, 397, 406
internal unique identifier, 324
isolation level 0 locking, 154
isolation levels for locks, 160

J
JOIN clause, including the OPENROWSET function, 284
joins, 179
    deleting rows from tables, 168, 170
    types of, 179
junction table, 8, 14

K
keyset, 135, 142
keyset-driven cursor, 135, 138, 142
KEYSET keyword, 140, 142
key values in clustered indexes, 321, 324

L
.ldf file extension, 293, 296
leaf level in nonclustered indexes, 321
leaf-level pages
    PAD_INDEX and, 332
    specifying the FILLFACTOR for, 330
LEFT OUTER JOIN clause, compared to FULL OUTER JOIN, 186
left outer joins, 168, 179, 184, 188
left table, 179
legacy systems, applying normalization standards to, 3-4
Leland, Doug, "Microsoft SQL Server Fundamentals," 59
linked server
    querying, 286
    requiring a password to access, 280
Litwin, Paul, "Fundamentals of Relational Database Design," 15, 29
local cursor, 146
LOCAL keyword, 146
@locallogin parameter, 280
local variables, 193
    assigning data types to, 110, 114
    assigning values to, 110, 112
    beginning with @, 110, 114, 118
    defining, 110, 112, 114, 118, 196
    defining values for, 118
    inserting into a result table, 196
lock block, 402
Lock Blocks counter, 402
locking category for SET statements, 267
locking hierarchy, 406
locking hints, 158
locking problems
    diagnosing and resolving, 397-411
    practices for diagnosing and resolving, 362
    tools for diagnosing, 361
Lock Requests/sec counter, 402
locks, 96, 98, 149, 397
    categories of, 397
    isolation levels for, 160
    monitoring, 361, 402
    returning information about active, 402
    for special situations, 397
    types of, 400
Locks object, 402
log files, 293
    file extension for, 296
    items defining, 302
logical database design, 3, 85
    incorporating constraints into, 2
    normalizing, 3
    normalizing and denormalizing, 1
    practices for evaluating, 85-86
    practices for modifying, 86
logical data model, 1, 96
logical in-memory table, 237
logical name for a file, 302
logical ordering in nonclustered indexes, 326
logical reads
    decreasing the number of, 392
    decreasing with optimizer hints, 388
    increasing with optimizer hints, 394
    returning, 274, 381, 384
Logical Scan Fragmentation value, 377, 378
LOG ON clause, explicitly defining disk files to store database logs, 302
logon IDs, mapping between servers, 280
logons, creating or updating a mapping between, 280
lost updates, 408, 410

M
maintainability, 89, 100
maintenance of databases, 361
maintenance time, minimizing for full-text search, 356
many-to-many relationship, 8, 14
MAX aggregate function, including in an expression, 114
.mdf file extension, 293, 296
Memory Manager, monitoring overall server memory usage, 402
metadata, defining full-text catalogs, 358
Microsoft Access database, 282, 284
Microsoft Distributed Transaction Coordinator (MS DTC), 277
Microsoft.Jet.OLEDB.4.0, 282, 284
Microsoft Search service, 349, 358
Microsoft SQL Server. See SQL Server
Microsoft SQL Server 7.0 Books Online, 15, 29, 101, 119, 133, 147, 161, 219, 249, 305, 333, 347, 359, 379, 411
"Microsoft SQL Server Fundamentals," 59
milliseconds, setting for a lock to be released, 156
Mixed Mode, 92, 100
Mode column, returned by sp_lock system stored procedure, 402
monitor logon activity, tracing, 390
MS DTC (Microsoft Distributed Transaction Coordinator), 277
MS SQL Server Diagnostics, 15, 305
MSSQLServer service, 349
MS SQL Server Transact-SQL and Utilities Reference, 119, 287
multiplication (*) operator, 198
multivalue column, 10

N
1NF (first normal form), 3
2NF (second normal form), 3
3NF (third normal form), 3
N, notating a Unicode string literal, 118
nchar data type, 310
.ndf file extension, 293, 296
negative unary (­) operator, 198
nested subquery
    deleting records from a table, 168
    deleting rows from tables, 170
nested tr