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

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 transactions, rolling back, 248
nested trigger database option, 248
nesting level, returning, 160, 248
@@NESTLEVEL function, 160, 248
network traffic, reducing with stored procedures, 128
NEWID() function, 307, 316
NOLOCK locking hint, 158
nonclustered indexes, 321, 324, 326
nonfatal internal error, 232
nonindexed columns, creating statistics on, 368
non-leaf-level pages, PAD_INDEX and, 332
nonrepeatable read, 408, 410
non-Unicode character data, data types for, 310
NORECOMPUTE option, adding to CREATE STATISTICS, 368
normal forms, levels of, 3
normalization, 3, 75, 80, 100
    decreasing the amount of redundant data, 98
    effect on database size, 94
    effect on table locks, 96
    impact on performance, 92
    reducing the number of NULL values, 98
    of tables, 21-22
normalized databases, 3, 96
normalizing a logical database design, 1
Northwind test database, 103
NOT NULL
    assigning to columns, 314, 316
    in a column definition, 307
    configuring a column as, 66
    defining a column as, 318
ntext data type, 310
NULL values, 98, 202
    assigning for a parameter, 230
    assigning to columns, 314
    in column definitions, 307
    domain integrity and, 48, 50, 52, 56, 58
    in IF clauses, 226
    normalization and, 96
    primary keys and, 24
    reducing the number of, 98
Number of Deadlocks/sec counter, 402
nvarchar data type, 310

O
object ID (OBJID) column, returned by sp_lock system stored procedure, 402
ODBC bulk copy API, 338, 342, 346
OLE DB data source and destination, using DTS with, 338
ON clause
    CROSS JOIN keyword and, 190
    in a DELETE statement, 170
    identifying columns that tables have in common, 182, 186
    in a trigger, 242, 244
one-to-many relationship, 8, 14
one-to-one relationship, 8, 14
OPENQUERY function, 277, 286
OPENROWSET function, 277, 282, 284
OPEN statement, opening and populating a cursor, 144
operating-system file name for a file, 302
operators, 193, 198
optimistic concurrency, 152
optimizer hints, 394
    decreasing logical reads, 388
    fine-tuning query performance, 381-82, 386
    index strategy and, 388
    reducing physical reads, 392
Oracle database, moving data to a SQL Server database, 340, 344
ORDER BY clause
    CREATE VIEW statement and, 212, 214
    specifying descending order, 254
    in a stored procedure, 224
"ORDER BY clause is invalid..." error message, 214
outer joins, 168, 179, 184, 188
OUTPUT keyword, 221
output parameters, 221, 228

P
package definition files, file extension for, 296
packages. See DTS packages
PAD_INDEX option, 332
page chain, scanning at the leaf level, 376, 378
pages
    displaying the density of, 375, 376
    returning the number placed into cache, 381, 384
    returning the number read from data cache, 381, 384
    returning the number read from disk, 381, 384
parameters
    assigning initial values for, 230
    defining in a CREATE PROCEDURE statement, 228, 230
    referring to missing, with a double dot (..), 114
    in stored procedures, 221
    stored procedures and, 128
    types of, 221
pass-through queries
    performing, 277
    specifying, 286
passwords
    logging onto a remote server with, 282
    mapping between servers, 280
    requiring to access a linked server, 280
.pdf file extension, 296
performance, 89. See also response time
    effect of normalization on, 92, 100
    maximizing for physical disks, 304
    tools for comparing, 363
Performance Monitor. See SQL Server Performance Monitor
permissions for dropping a database, 298
pessimistic concurrency, 152
phantoms, 408, 410
physical database design, 85
physical disks, placing data files and transaction log files on separate, 304
physical ordering in clustered indexes, 326
physical reads
    decreasing, 388, 392, 394
    returning, 274, 381, 384
positive (+) operator, 198
precedence for operators, 198
predefined traces, 413, 420, 424
PRIMARY clause, defining the primary filegroup, 302
primary data file, 293
primary filegroup, 302
PRIMARY KEY constraint, 61, 66, 318
    adding to a column, 307
    defining, 17, 328
primary keys, 6, 10, 17, 20
    creating, 316
    creating indexes on, 328
    defining columns as, 22, 318
    identifying, 17-29
    identity columns as, 314
    ID numbers as, 28
    NULL values and, 24
    system-generated values as, 24, 26
    unique indexes and, 78
primary SQL Server data file, file extension for, 296
PRINT keyword in an ELSE statement, 204
procedural data integrity, 45
procedure cache, storing execution plans in, 128
procedures. See stored procedures
Profiler. See SQL Server Profiler
provider name, required in OPENROWSET, 286

Q
queries
    avoiding those affecting many rows, 404
    excess joining of, 78
    fine-tuning the performance of, 386
    storing as objects in databases, 209
    storing predefined as objects, 94
query analysis applications, output intended for, 372, 374
Query Analyzer. See SQL Server Query Analyzer
query execution category for SET statements, 267
query execution plans
    evaluating and optimizing the performance of, 381-94
    practices for evaluating and optimizing, 361-62
query optimizer
    compared to optimizer hints, 382, 388
    forcing to read all rows in a table, 368
    overriding, 381, 386, 394
    uses of constraints, 61
query performance, fine-tuning, 394
query plans, 365
    creating for stored procedures, 128
    creating with WITH RECOMPILE, 234
    forcing the recompilation of, 234
    returning information about, 272
quotation marks in SET statements, 110

R
RAISERROR statement, 232
range lock, placing on the data set, 154
read-ahead count, effect of optimizer hints on, 392
read-ahead reads, 274
READ COMMITTED isolation-level option, 154
READ-ONLY keyword, 140, 146
read-only result set, declaring a cursor for, 137-38
read performance for DSS environments, 330
READ UNCOMMITTED isolation-level option, 154
recompiling stored procedures, 132, 234
records
    preventing updates on the same, 400
    uniquely identifying in tables, 28
    updating small groups of, 404
redundant data, decreasing the amount of, 98
referential integrity, 45
    business rules relating to, 48, 50, 52, 56, 58
    enforcing, 31, 34, 36, 38, 40, 42
relational database design
    fundamentals of, 29
    overview of, 15, 43, 73
remote computer, running bulk copy program (bcp) from, 345-46
remote data, accessing on an ad hoc basis, 282, 284
remote data sources, methods for accessing data on, 277
remote server
    linking to, 277
    processing queries on, 277
    querying data from, 282
remote stored procedures, 121, 221
REPEATABLE READ isolation-level option, 154
replication, 277
Resource column, returned by sp_lock system stored procedure, 402
response time. See also performance
    diagnosing slow, 401-2
result sets, 135
    adding extra summary rows to, 262
    changing the format for the display of, 200
    declaring cursors for read-only, 137-38
    eliminating duplicate rows from, 176
    fixing when the cursor is opened, 138
    inserting into tables, 126
    producing from joins, 179
    providing summary data in, 251-65
    of static cursors, 142
RETURN statement, executing to exit a query, 206
RIGHT OUTER JOIN clause, 184
right outer joins, 168, 179, 184, 188
right table, 179
@rmtpassword parameter, 280
@rmtsrvname parameter, 280
@rmtuser parameter, 280
roles, 130
ROLLUP operator. See WITH ROLLUP operator
ROUND operator, 214
@@ROWCOUNT function, 160
row count, setting, 267, 270
rows
    adding to an existing table, 163
    deleting all from a table, 170
    deleting from tables, 168
    deleting without a WHERE clause, 104
    displaying all in a table, 118, 274
    eliminating duplicate from the result set, 176
    identifying in a table, 17, 20, 26, 307
    inserting into tables, 166
    returning the number affected by the last statement, 160
    specifying the table from which to retrieve, 260
    updating in dynamic cursors, 140
rowset function, 193
rules, 70, 78

S
Sample 1-TSQL trace definition, 418
SAMPLE number PERCENT option, adding to CREATE STATISTICS, 368
scalability, 89, 100
scalar function, 193
scan count, 274
scan density statistic, 375, 376, 377, 378
scans, returning the number performed, 381, 384
schema locks, 397, 400, 406
Sch-M (schema modification) locks, 400, 406
Sch-S (schema stability) locks, 400
scope, defining for a cursor, 146
scripts, creating using Transact-SQL, 193-207
scrolling cursors, compared to forward-only, 135
scroll locks, freeing, 144
SCROLL option, 142
search condition
    retrieving specific rows based on, 163
    specifying for a group or an aggregate, 260
secondary data files, 293, 296
second normal form (2NF), 3
security, 89
    establishing between a local and a linked SQL Server, 279-80
    modes, 92, 100
seed for an identity column, 314
SELECT clause
    COUNT aggregate function in, 256
    using the SUM aggregate function to provide totals, 258
SELECT...INTO statement, 240, 342
SELECT list, 214
    defining, 260
    excluding columns from, 224
    in a SELECT statement, 163
SELECT permissions, 130
SELECT statement, 135, 200
    assigning a value to a local variable, 112, 114
    components of, 163
    containing an OPENQUERY function, 286
    in a CREATE PROCEDURE statement, 224
    defining search conditions, 206
    within an EXECUTE statement, 116
    FROM OPENROWSET clause within, 282, 284
    generating a result set, 126
    including a DISTINCT keyword, 176
    introducing with the AS keyword, 212
    nested, 172
    retrieving data from a SQL Server database, 163
    retrieving rows from a database, 342
    returning rows from a table, 166
    specifying in DECLARE CURSOR, 144
    in a stored procedure, 132
    TOP n keyword in, 254
    in a view, 216
    with a WHERE clause specifying a date range, 174
SELECT subquery, 166
self-joins, 179, 188, 190
sequential numeric values, creating columns with, 307
SERIALIZABLE isolation-level option, 154
server and database activity, monitoring, 390
server process ID (SPID) column, returned by sp_lock system stored procedure, 402
servers
    establishing security between, 280
    linking, 277
    monitoring activities of, 298, 413
Service Manager, 298
session-level options, 267
SET clause, 172, 226
SET LOCK_TIMEOUT statement, 156
SET ROWCOUNT statement, 267, 270
SET SHOWPLAN_ALL statement, 267, 361, 362, 365-66, 372
SET SHOWPLAN_TEXT statement, 267, 272, 361, 362, 365, 374
sets of values, 176
SET statements
    altering current session handling, 267
    assigning values to local variables, 110, 112
    assigning values to variables, 193
    categories of, 267
    defining values for local variables, 118
    functions provided by, 267
    quotation marks in, 110
SET STATISTICS IO statement, 267, 274, 361, 381, 384
SET TRANSACTION ISOLATION LEVEL statement, 154
severity level in a RAISERROR statement, 232
shared intent lock, 406
shared locks, 154, 397, 400
shared table locks, directing SQL Server to use, 158
SHOWPLAN_ALL option, 372, 374
    enabled for the current session only, 372
    with SET STATISTICS IO, 274
SHOWPLAN_TEXT option, 372, 374
slash (/), as division operator, 198
sort order, specifying in columns returned in a SELECT statement, 224
sp_addlinkedsrvlogin system stored procedure, 277, 280
sp_addtype system stored procedure, 312
sp_depends system stored procedure, 290
sp_executesql stored procedure, 107, 118
sp_fulltext_catalog system stored procedure, 349, 352
sp_fulltext_column system stored procedure, 352
sp_fulltext_database system stored procedure, 349, 352
sp_fulltext_table system stored procedure, 352
sp_help_fulltext_tables system stored procedure, 291
sp_lock system stored procedure, 361, 402
"sp_" prefix, 121
sp_tableoption stored procedure, 400, 406
sp_unbindrule system stored procedure, 78
SPID (server process ID) column, returned by sp_lock system stored procedure, 402
SQL (structured query language), 103
SQL-92 settings category for SET statements, 267
SQL-92 synonyms, 310
SQL batches, evaluating, 269-70
SQL Server
    security modes, 92, 100
    viewing locking information, 422
SQL Server 7.0 Books Online. See Microsoft SQL Server 7.0 Books Online
SQL Server components, starting, stopping, and pausing, 298
SQL Server computer, ad hoc monitoring of, 418, 422
SQL Server databases. See databases
SQL Server engine. See engine events, monitoring
SQL Server Enterprise Manager, 298, 390
    Current Activity Window, 361, 402, 416, 418, 422
    populating full-text indexes, 356
    propagating full-text changes, 356
SQL Server error, 232
SQL Server events. See events
SQL Server Performance Monitor, 298, 390, 416, 418, 422
SQL Server Profiler, 298, 361, 390, 413
    Create Trace Wizard, 420, 424
    creating a workload for Index Tuning Wizard, 418
    creating traces, 363, 413
    monitoring engine events, 416, 422
SQL Server Query Analyzer, 298
    executing a batch with DBCC SHOWCONTIG, 375-76, 377-78
    executing a batch with SET SHOWPLAN_ALL ON, 371-72
    executing a batch with SET SHOWPLAN_TEXT ON, 373-74
    executing a batch with SET STATISTICS IO ON, 383-86, 391-94
    Graphical Showplan option, 362, 381, 390
    issuing full-text queries, 356
SQL Server Service Manager, 298
statement block, executing repeatedly, 196
statements
    building dynamically, 107
    dynamic, 107
    encapsulating for repeated execution, 221
static cursors, 135, 138
    compared to dynamic, 138
    defining, 142
STATIC keyword, 142
statistical information
    creating about column data, 361
    creating about the distribution of column data, 368
    updating, 369-70
    updating manually, 366
statistics
    updating automatically, 368
    updating by creating an index, 370
    updating manually, 370
statistics category for SET statements, 267
statistics group (collection), 368
Status column, returned by sp_lock system stored procedure, 402
stored procedures, 64, 68, 103, 121, 130, 221
    creating, 223-30
    creating query plans for, 128
    evaluating, 131-32
    EXECUTE permissions for, 124
executing, 124
    executing infrequently, 234
    executing with EXECUTE...WITH RECOMPILE, 234
    practices for accessing, manipulating, and managing data, 104-5
    recompiling, 132
    reducing network traffic with, 128
    tracing, 390
    types of, 121, 221
strings, executing, 118
structured query language (SQL), 103
style option, specifying, 200
subtraction (­) operator, precedence of, 198
SUM aggregate function, 258, 260, 262
summary data, creating result sets providing, 251-65
syscomments entries, encrypting, 209
sysdatabases system table in the master database, 293
system data type, 307
system-generated sequential numeric values, 29
system-generated value, using as the primary key, 24, 26
system stored procedures, 121, 221. See also stored procedures

T
table aliases, specifying in self-joins, 190
table integrity. See entity integrity
table-level CHECK constraint, 70
table-level locking hints, 158
TABLE LOCK ON BULK LOAD option, 400, 406
table locks, effect of normalization on, 96, 98
tables
    accessing with stored procedures, 130
    clustered indexes and, 321
    combining data from multiple, 179
    creating, 12, 307, 318
    creating indexes for, 323-24, 325-26
    creating statistics on, 368
    deleting all rows from, 170
    deleting rows from, 168
    denormalizing, 75, 80
    displaying all rows of, 118
    displaying every combination of all rows in, 188
    displaying fragmentation information about, 375, 376
    generating the number of items in, 126
    identifying scans of large, 424
    inserting data into from an external source, 335
    inserting result sets into, 126
    inserting rows into, 166
    joining to each other, 188, 190
    marking or unmarking for full-text indexing, 352
    normalizing, 5-6, 9-10, 21-22
    one-to-one relationship between, 8
    practices for creating and defining columns within, 290
    practices for populating from an external data source, 291
    referring to columns from another, 172
    uniquely identifying records in, 28
    uniquely identifying rows in, 17, 26
    updating, 172, 404
table scan, specifying, 381, 386
TABLOCK locking hint, 158, 400, 406
TABLOCKX locking hint, 158
tempdb database, building the result set of a static cursor in, 138
temporary stored procedures, 121, 221
text data, querying, 349
text data type, 310
text files, 337-38
"Textual Searches on Database Data Using Microsoft SQL Server 7.0" white paper, 359
"Textual Searches on File Data Using Microsoft SQL Server 7.0" white paper, 359
third normal form (3NF), 3
timeout period, indicating in the SET LOCK_TIMEOUT statement, 156
timestamp data type, DEFAULT constraint and, 61
tinyint data type, 312
tools
    for creating databases, 293
    for diagnosing locking problems, 361
    for maintaining databases, 361
TOP n keyword in a SELECT statement, 254
trace data, saving to a table, 420, 424
Trace Properties dialog box, 413
traces, 413
    capturing data about engine events, 416
    creating, 422
    creating directly in SQL Server Profiler, 413
    creating for all tables, 424
    creating predefined, 413, 424
    creating with Create Trace Wizard, 413
    creating with SQL Server Profiler, 418
@@TRANCOUNT function, 160
transaction integrity, 397, 408, 410
transaction locks, holding, 144
transaction log, providing statistics about the use of space for, 300
transaction log file, 293
transaction manager, 149
transaction mode, 149
transaction processing, 397
transactions, 149
    maintaining records of, 12
    managing multiple, 397
    overwriting, 408
    returning the number of active, 160
    serializing, 397
    types of, 149
transactions category for SET statements, 267
transaction tables, uniquely identifying rows in, 20
transaction times, deadlocks and, 160
Transact-SQL batches
    evaluating, 109-12, 117-18
    setting database names in, 110
Transact-SQL cursors. See cursors
Transact-SQL language, 103
Transact-SQL scripts, evaluating, 201-2, 205-6
Transact-SQL statements
    detailed reference explaining, 119
    evaluating, 139-40
    issuing full-text queries, 356
    practices for manipulating data in a database, 104
    practices for querying a database, 103-4
    storing on the server, 221
    writing and executing, 193-207
triggers, 64, 152, 237
    activating with data modification statements, 242, 244
    compared to constraints, 36, 38, 42
    containing a DELETE statement, 240
    evaluating, 241-46
    firing when records are deleted, 244
    firing when records are updated, 242
    modifying a table, 241-44
    practices for enforcing data integrity in a SQL Server database, 105
    types of, 237
Type column, returned by sp_lock system stored procedure, 402

U
unbinding rules, 78
uncommitted dependency, 408, 410. See also dirty reads
Unicode data, data types supporting, 310
Unicode string literal, notation for, 118
UNIQUE constraint, 61, 66, 72, 328
unique identifier, 324
uniqueidentifier data type
    creating a unique value for, 316
    specifying along with the NEWID() function, 307
unique index, 17, 78, 324, 326
UNIQUE keyword, using to create clustered indexes, 324
UPDATE keyword, adding to the FOR clause in a trigger, 242, 244
update locks, 158, 397, 400, 406
update page lock, promoting to an exclusive page lock, 400
UPDATE permissions, 130
updates
    overwriting, 408
    preventing on the same record, 400
UPDATE statement, 172
    including table-level locking hints in, 158
    issuing on a view, 218
    with joins and subqueries, 163
    updating views, 216
UPDATE STATISTICS statement, 240, 366, 370
UPDATE trigger, 36, 68, 152, 237, 246
UPDLOCK locking hint, 158
USE keyword, defining which database to use, 212
user activity, creating a trace of all, 416
user connections, monitoring, 361, 402
user-defined data types, 289, 307, 312
user-defined error message, evaluating, 232
user-defined integrity, 45
    business rules relating to, 52, 54, 56, 58
user-defined stored procedures, 121, 221, 226, 234
user-defined transactions, 149
user interaction, deadlocks and, 160
user name, logging onto a remote server with, 282
@useself parameter, 280
USE statement
    within an EXECUTE statement, 116
    making a database current, 332

V
values
    rounding, 214
    sets of, 176
varchar data type, 200, 310
variable-length, non-Unicode data, data types supporting, 310
variable-length Unicode data, data type supporting, 310
variables
    defining in a DECLARE statement, 110
    initializing in a CREATE PROCEDURE statement, 226, 228
views, 94, 130, 209
    altering previously created, 300
    executing data modification statements against, 218
    modifying data in, 209
    practices for querying and manipulating data in a SQL Server database, 105
updating, 216

W
WHERE clause
    containing a nested SELECT statement, 172
    in a DELETE statement, 170
    deleting rows without, 104
    preceding by an associated FROM clause, 172
    in a SELECT statement, 163
    specifying a date range, 174
    specifying a search condition, 214
    in a stored procedure, 132
WHERE CURRENT OF clause in a cursor, 140
WHILE clause, executing a statement block repeatedly, 196
WHILE loop, controlling the execution of statements in, 193
WHILE statement, 193
Windows NT Authentication Mode, 92, 100
Windows NT Performance Monitor, integration of SQL Server features into, 298, 390, 416, 418, 422
WITH CHECK OPTION clause
    using with a view, 130
    verifying modifications made through views, 209, 218
WITH CUBE operator
    compared to WITH ROLLUP, 264
    with the GROUP BY clause, 251
WITH ENCRYPTION clause, 209
WITH RECOMPILE keyword, creating a new query plan, 234
WITH ROLLUP operator
    compared to WITH CUBE, 264
    with the GROUP BY clause, 251, 262
workload, creating for Index Tuning Wizard, 418

X
X locks. See exclusive locks