Index

Symbols

16-table rule (queries), 152

+ (addition keyword), 74-75

* (asterisk), 27-28

@ (at sign), 240

= (equal sign), 143

> (greater than keyword), 52

>= (greater than or equal to keyword), 52

< (less than keyword), 52

<= (less than or equal keyword), 52

!= (not equal keyword), 52

< > (not equal keyword), 52

% (percent sign, wildcard character), 62-64

# (pound sign), 208

[ ] (square brackets, wildcard character), 64

_ (underscore, wildcard character), 64

A

ABSOLUTE (scollable cursor option), 287-288

absolute value (ABS function), 83

ad-hoc queries (COMPUTE clause), 114-115

adding error messages, 43

addition operator (+), 74-75

aggregates

AVG, 95

columns

multiple, 117-118

nonnumeric, 99-100

COMPUTE clause, 118

ad-hoc queries, 114-115

nesting, 113-114

COUNT, 96-97

COUNT(DISTINCT), 97

expressions, 101-102

filtering, 118-119

GROUP BY clause, 106-112

columns outside select list, 107

CUBE keyword, 110-112

ORDER BY clause, 107-108

ROLLUP keyword, 112

HAVING clause, 118-119

ISNULL(), 102-103

MAX, 95

MIN, 95

multiple, 95

null values, 94, 98-99, 102-103

scalar, 106, 318

subqueries (HAVING clause), 158-160

subtotals (COMPUTE BY clause), 115-117

SUM, 94-95

vector, 106, 318

WHERE clause, 100-101

worktables, 451-452

aliases

columns, 26-27

joins, 128

table, 129

allocation units, 300

altering table information (UPDATE statement), 182-187

defaults, 186-187

joins, 184

limits, 185-186

single rows, 184

alternate keys, 196

AND Boolean keyword, 54-56

ANSI (American National Standards Institute), 5

ANSI STYLE syntax joins, 124

benefits, 142

Cartesian products, 131

specifying conditions, 127

versus old style, 126-127

writing, 126

applications, MDI, 21

arguments, date functions, 84

AS keyword, 27

ascending order, sorting data (ORDER BY clause), 36-38

ASCII code, converting characters from, 82

ASCII string function, 82

assigning values (variables), 240-242

tables, 242

asterisk (*), 27-28

at sign (@), 240

attributes, see columns

audit trails, 419-420

available databases, 231

average values, parameter defaults, 340-341

averaging, 95

excluding rows, 99

AVG aggregate function, 95

B

batches

errors, 236

execution order, 234

GO command-line keyword, 235

INSERT statement, 236

multiple statements, 234-235

processing, 360

scripts, 237

transactions, 261-263

BEGIN keyword, 249-251

BEGIN TRAN[SACTION] (transaction control statement), 258

BETWEEN keyword, 58-60

NOT BETWEEN keyword, 59-60

bigpubs database (metis Web site), 304

binary data types, 70

inserting, 179

binary keywords, WHERE clause, 52

binary sort order, 34

case sensitivity, 63

bit data types, 173

inserting, 180

blanks in strings, 81

blocking chains, 439

boolean logic, 53-58

compound clauses, 54-56

keywords

AND, 55-56

NOT, 54

OR, 55

order, 56

brackets ([ ]), wildcard characters, 64

BREAK keyword, 252

built-in functions, 79

C

cache (procedure), 361

observing (dbcc memusage), 364

cardinality, 135

Cartesian products, 131-132

cascading deletes, 192, 262, 414-416

CASE expression, 428-454

crosstab reports, 163-166, 428-434

column evaluation, 165

input code, 164

join information, 164

row evaluation, 164

tips, 165-166

views, 432-433

function, 161

keywords

ELSE, 163

END, 163

THEN, 163

WHEN, 162-163

multiple updates, 283-284

views, 223-224

case sensitivity

column names, 171

searches, 63-64

changing database contexts

DB-Library, 231

ISQL/w, 231

ODBC, 231

char data type, 70

inserting, 179

CHAR string function, 82

characters

converting to ASCII code, 82

variables, converting from data types, 77

CHARINDEX string function, 82-83

check constraints versus triggers, 421

checkout systems (locking), 443

clauses

COMPUTE, 118

ad-hoc queries, 114-115

nesting, 113-114

COMPUTE BY, 115-118

multiple columns, 117-118

DISTINCT (worktables), 453

ELSE (IF flow control statements), 249

GROUP BY, 105-112

columns outside SELECT list, 107

CUBE keyword, 110-112

ORDER BY clause, 107-108

ROLLUP keyword, 112

HAVING, 118-120

versus WHERE, 120

ORDER BY, 58

COMPUTE BY clause, 115-118

GROUP BY clause, 107-108

sorting data, 33-39

order of evaluation, 119

WHERE, 31, 49-67

aggregates, 100, 101

compound, 54-56

equality conditions, 51-52

inequality conditions, 52

optimizing stored procedures, 375

retuning names, 32

SARGs, 312-314

syntax, 58

updating single rows, 184

versus HAVING, 120

WHERE CURRENT OF, 286

client cursors, 282-283

clients

SQL software, 8

installing, 8-9

ISQL/w, 9-11, 21-24

closing cursors, 282

clustered indexes, 198, 303-308, 324

versus nonclustered, 310, 323

CLUSTERED keyword, 195

COL LENGTH system function, 90

column level constraints, 195

columns

aliases, 26-27

audit trails, 419-420

combining, 76-79

data types, 70-73

creating tables, 172

defaults

inserting rows, 177

values, 175

definitions, 171

attributes, 170

data types, 172

null status, 172-173

derived, 416-419

fixed-length, rows, 73

identifying in joins, 125-127

identity, 174

DELETE statement, 189

inserting rows, 176-177

TRUNCATE TABLE statement, 189

listing (sp help), 32

multiple

COMPUTE BY clause, 117-118

SELECT statement, 24-25

sorting data (ORDER BY clause), 35-36

names, 171

nonnumeric aggregates, 99-100

null values, 170

qualifying names (joins), 129

retuning names (WHERE clause), 32

rows

adding, 75-76

inserting, 176-177

selecting all, 27-28

sorting (worktables), 453-454

syscolumns system table, 88

timestamp, 443-449

stored procedures, 449

TSEQUAL() function, 446-448

UPDATE() function, 409

combining, columns/constants, 76-79

commands, see clauses; functions; statements

comments

multiline, 354

return status values, 354

single-line, 355

COMMIT TRANSACTION (transaction control statement), 258

common keys, 124

column names, 143

comparing values lists, 60-61

compiling

stored procedures, 365-367

indexes, 372

complex joins, subqueries as, 157-158

composite keys, 130

compound clauses, WHERE, 54-56

COMPUTE BY clause, 115-118

multiple columns, 117-118

COMPUTE clause, 118

ad-hoc queries, 114-115

nesting, 113-114

conditions (WHERE clause), equality/inequality, 51-52

connection-specific global variables, 245-246

@@connections global variable, 246

constants

combining, 76-79

selecting, 74

constraints, 193-194

column/table level, 195

indexes, clustered/nonclustered, 198

maximum, 195

naming, 195

primary key, 193

reference, 193, 195

unique, 193, 196

contexts, see database contexts

CONTINUE keyword, 252

CONVERT() function, 76-79

formatting dates, 78-79

converting

characters to ASCII code, 82

data types to variable characters, 76-77

dates to strings, 84

numeric data to strings, 81-82

string case, 80

strings (long to short), 77

copying data between tables, 180-182

correlated subqueries, 160

cost (joins), 464-469

overriding optimizer, 468-469

cost-based optimizer, 311

COUNT aggregate function, 96-97

COUNT(*) query, 96

COUNT(DISTINCT) aggregate function, 97

counters, 242

triggers, 423-424

covering queries, 310

@@cpu busy global variable, 246

CREATE PROCEDURE statement, 329

syntax, 331

with parameters, 336

WITH RECOMPILE option, 367

CREATE TABLE statement, see tables, creating

CREATE TRIGGER statement, 400

CREATE VIEW statement, 216

syntax, 217

cross joins, 131-132

crosstab reports, 428-434

CASE expression

column evaluation, 165

input code, 164

join information, 164

row evaluation, 164

tips, 165-166

views, 432-433

CUBE keyword, 110-112

etymology, 120

CURRENT TIMESTAMP niladic function, 178, 443

CURRENT USER niladic function, 178

CURSOR keyword, 279

@@cursor rows global variable, 245

cursors

client, 282-283

closing, 282

creating, 278

deallocating, 282

example

closing, 282

deallocating, 282

declaring, 279-280

fetches, 280-281

opening, 280

fetches, 280-281

@@fetch status global variable, 281

insensitive, 279, 289

keysets, 281

limitations, 289-290

locking, 289

opening, 280

scroll option, 279, 287-288

scrollable, 279, 287-288

server, 282-283

syntax, DECLARE statement, 288

triggers, 290

two updates, 283-286

D

data

altering (result sets), 25-26

dictionaries, 88-89

integrity problems, multiuser editing, 435-449

sorting (ORDER BY clause), 33-34

ascending/descending order, 36-38

multiple columns, 35-36

ordinal method, 39

outside result set, 38-39

data types

binary, 70

bit, 72, 173

character, 70

columns, 70-73

converting, 76

to variable character, 77

creating tables, 172

datetime, 71

floating-point, 284-285

image, 73

inserting, 179

integer, 71

little float, 71

matching (subqueries), 155

money, 72

small integer, 72

smallmoney, 72

specifying length, 170

text, 72

timestamp, 443

tiny integer, 72

user-defined, 32

Database Consistency Checker (DBCC), 270

database contexts

changing, 230-231

DB-Library, 231

ISQL/w, 231

ODBC, 231

current, 228-229

database owner (dbo), 88

databases, 228

available, 231

master, 232

model, 232

msdb, 232

pubs, 10, 233

sysdatabases system table, 89

tempdb, 232

user-defined, 233

DATALENGTH system function, 87

date functions, 84-87

DATEADD(), 85

DATEDIFF(), 86

DATENAME(), 87

DATEPART(), 84-86

GETDATE(), 85

dates

converting to strings, 84

dateparts, 84-86

extracting, 86

difference calculation, 86

formatting, CONVERT function, 78-79

GETDATE() function, 78

searches. LIKE keyword, 64-65

datetime data types, 71

inserting, 179

DB NAME() function, 228-229

DB-Library, 40

changing database contexts, 231

DBCURSOR() function, 282

DBCC see Database Consistency Checker

dbcc memusage, 364

DBCURSOR() function, 282

dbo (database owner), 88

reserved names, 332

DBUSE() function, 231

deadlock, 273-274, 437

manual checkout solution, 443

multiuser editing, HOLDLOCK keyword, 437-441

persistent exclusive locks, 441-443

deallocating cursors, 282

debugging stored procedures, 354-357

comments, 354-355

message handling, 356-357

declarative referential integrity, see DRI

DECLARE statement, 240, 279

syntax, 244, 279

cursors, 288

DEFAULT keyword, 187

defaults, 31

columns

inserting rows, 177

values, 175

niladic functions, 178

UPDATE statement, 186-187

deferred insertion mode, 380-381

defining

attributes (columns), 170

column aliases, 26-27

error messages, 44

DELETE statement, 187-190

joins, 188

syntax, 190

versus DROP TABLE, TRUNCATE TABLE, 189

views, 188-189

deleting

cascading deletes, 192, 262, 414-416

foreign keys, 192

primary keys, 192

rows

DELETE statement, 187-188

views, 220

tables, 173, 189

derived columns, 416-419

descending order, sorting data (ORDER BY clause), 36-38

developing stored procedures, 329-331, 350

naming conventions, 330

dictionary case insensitive/sensitive sort order, 34

DIFFERENCE string function, 83

direct insertion mode, 380-381

direct updates in place, 406

disabling rows affected message, 25

disabling row count acknowledgment, 176

DISTINCT keyword, 155

versus WHERE EXISTS subquery, 161

worktables, 453

distributed transactions, 455

distribution pages, 313-314

double outer joins, 474

DRI (declarative referential integrity), 138, 190-203

constraints, 193-196

column/table level, 195

maximum, 195

naming, 195

reference, 195

unique, 196

example, 196-203

foreign keys, 192

primary keys, 191-192

triggers, 414

cascading deletes, 414-416

DROP operation, 173

DROP TABLE statement

versus DELETE, TRUNCATE TABLE, 189

DROP TRIGGER statement, 402

E

editing

multiuser

locking, 434-443

optimistic locking, 443-449

stored procedures, 334-335

ELSE clause

CASE expression, 163

IF flow control statements, 249

END keyword, 249-251

CASE expression, 163

Enterprise Manager (SQL-EM), 331

equal signs (=), 143

ER (entity relationship) diagrams, 135-138

foreign keys, 137

lines, 137

primary keys, 135

symbols, 137

@@error global variable, 245

error trap value 0, 395-396

stored procedures, 387-389

transactions, 259

errors

batches, 236

checking

procedure return status, 347-349

stored procedures, 387-389

transactions, 259-260

fatal, 42

handlers, 40

messages

adding new, 43

defining new, 44

list of common, 45-46

parameter defaults, 339

sysmessages system table, 89

triggers, 410

raising, 42-43

returning information (stored procedures), 395

user, 42

evaluation, order of (clauses), 119

exception handlers, 40

exclusive intent locks, 439

exclusive locks, 266-267

persistent, 441-443

EXECUTE PROC statement, 366-367

EXECUTE statement

running procedures, 334

syntax with parameters, 338

executing

partial queries (ISQL/w), 24

stored procedures, 363-364

triggers, 403-406

UPDATE statement, 404-405

execution order (batches), 234

expressions

aggregates, 101-102

data length, 90

extent locks, 269

extents, 301

extracting

dateparts, 86

strings, 80-81

F

fatal errors, 42

fetch, cursors, 280-281

@@fetch status global variable, 245, 281

fields, see columns

filtering aggregates, 118-119

FIRST (scollable cursor option), 287-288

floating-point data type, 284-285

flow control statements, 248

GOTO, 254

IF

BEGIN/END keywords, 249-251

ELSE clause, 249

logical shortcutting, 249

nesting, 254

RETURN, 253

WHILE, 251

BREAK keyword, 252

CONTINUE keyword, 252

forceplan option, 468-469

forcing join order, 468-469

foreign keys, 129-130, 191

deleting, 192

ER diagrams, 137

indexes, 198

inserting, 192

null values, 130

updating, 192

formatting dates (CONVERT function), 78-79

FROM clause, table order, 142

full outer joins, 474

functions

aggregate, 93

AVG, 95

COUNT, 96-97

COUNT(DISTINCT), 97

ISNULL(), 102-103

MAX, 95

MIN, 95

SUM, 94-95

built-in, 79

CONVERT(), 76-79

formatting dates, 78-79

date, 84-87

DATEADD(), 85

DATEDIFF(), 86

DATENAME(), 87

DATEPART(), 84-86

GETDATE(), 85

DB NAME(), 228-229

DBCURSOR(), 282

DBUSE(), 231

examples, 79

GETDATE(), 78

ISNULL(), 359

math functions, 83-84

ABS, 83

SIGN, 83

niladic, 178-179

CURRENT TIMESTAMP, 443

RAND(), 253

SQLUse(), 231

string, 80-83

ASCII, 82

CHAR, 82

CHARINDEX, 82-83

DIFFERENCE, 83

LOWER, 80

LTRIM, 81

PATINDEX, 82-83

REPLICATE, 83

REVERSE, 83

RIGHT, 80-81

RTRIM, 81

SOUNDEX, 83

SPACE, 83

STR, 81-82

SUBSTRING, 80-81

UPPER, 80

system, 87-90

COL LENGTH, 90

DATALENGTH, 87

OBJECT ID, 89

OBJECT NAME, 89

SUSER NAME, 88

USER NAME, 88

TSEQUAL(), 446-448

UPDATE(), 408-410

G-H

Geist, LLC, 11

GETDATE() function, 78, 85

global temporary tables, 214

global variables

@@connections, 246

@@cpu busy, 246

@@cursor rows, 245

@@error, 245

error trap value 0, 395-396

stored procedures, 387-389

transactions, 259

@@fetch status, 245, 281

@@identity, 201, 245

@@idle, 246

@@io busy, 246

@@max precision, 246

@@nestlevel, 245

@@pack received, 246

@@pack sent, 246

@@packet errors, 246

@@rowcount, 245

@@servername, 247

@@spid, 246

@@total read, 246

@@total write, 246

@@trancount, 246, 385

transaction control statements, 260

@@version

connection-specific, 245-246

server-specific, 246-247

GO keyword, 234-235

GOTO (flow control statement), 254

granularity (locks), 267-269

extent locks, 269

intent locks, 268

page locks, 268

row-level insert locks, 268

table locks, 268

greater than operator (>), 52

greater than or equal to operator (>=), 52

GROUP BY clause, 105-112

columns outside SELECT list, 107

CUBE keyword, 110-112

ORDER BY clause, 107-108

ROLLUP keyword, 112

group numbering, 351

grouping transactions, 263

handling debugging messages, 356-357

HAVING clause, 118-119

subqueries and aggregates, 159-160

evaluating, 160

including, 159

versus WHERE, 120

high throughput systems, 267

history, SQL, 4-6

holding shared locks, 270-271

HOLDLOCK keyword

optimizer hint, 271-272

persistent shared locks, 436-441

I

I/O (join order), 467

identity columns, 172-174

DELETE statement, 189

inserting rows, 176-177

TRUNCATE TABLE statement, 189

@@identity global variable, 201, 245

@@idle global variable, 246

IF (flow control statement)

BEGIN/END keywords, 249-251

ELSE clause, 249

logical shortcutting, 249

nesting, 254

image data type, 73

implicit transactions, 259

IN keyword, 60-61

common usage, 154

subqueries, 153-155

duplicate entries, 155

Incorrect syntax near ‘*’ error message, 45

indexes

clustered, 198, 303-308, 324

comparing clustered/nonclustered, 310, 323

compiling stored procedures, 372

distribution pages, 313-314

foreign keys, 198

join performance, 140

keys, 195

nonclustered, 198, 303-304, 308-310

leaf level, 308

optimization, 310, 315-317

cost-based optimizer, 311

joins, 463-467

overriding, 321-323, 468-469

SARGs, 312-314

showplan option, 317-319

statistics io option, 319-321

root pages, 307

sysindexes system table, 88

inequality keywords, 56-57

infinite loops, 252

inner joins, ANSI STYLE syntax, 127

inner tables, 139-140, 462

scan cost, 466-467

insensitive cursors, 279, 289

INSERT SELECT statement, local temporary tables, 211-212

INSERT statement, 175

batches, 236

default columns, 177

identity columns, 176-177

stored procedures, 378-382

optimization, 380-382

INSERT SELECT statements, 181-182

versus SELECT INTO, 182

inserted/deleted tables, triggers, 406-408

inserting

data types, 179

foreign keys, 192

modes, direct/deferred, 380-381

rows

default columns, 177

identity columns, 176-177

views, 218-220

installing SQL client software, 8-9

instances, see rows

INSTPUBS script, 11

integer data type, 71

integrated security (MS SQL Server 6.x), 10

integrity (declarative referential), 190-191

constraints, 193-196

example, 196-203

foreign keys, 192

primary keys, 191-192

intent locks, 268

shared, 438

interface (ISQL/w), 21

executing partial queries, 24

moving between tabs, 23

Query tab, 22

Results tab, 22

Statistics I/O tab, 23

invalid column name ‘UserID’ error message, 45

@@io busy global variable, 246

IS keyword, null values, 66

ISNULL() function, 102-103, 359

ISQL.EXE, 8

ISQL/w, 8

changing database contexts, 231

interface, 21-24

executing partial queries, 24

moving between tabs, 23

Query tab, 22

Results tab, 22

Statistics I/O tab, 23

running scripts, 356

starting, 9-11

problems, 11

J

joins, 124-126, 458

aliases, 128

ANSI STYLE syntax, 124

benefits, 142

specifying conditions, 127

versus OLD STYLE, 126-127

writing, 126

composite keys, 130

cost, 464-469

overriding optimizer, 468-469

cross, 131-132

ANSI STYLE syntax, 131

danger of, 132

OLD STYLE syntax, 131

defining relationships, 124

DELETE statement, 188

equals sign (=), 143

ER diagrams, 135-138

foreign keys, 137

lines, 137

primary keys, 135

symbols, 137

foreign keys, 129-130

null values, 130

full outer, 474

identifying

columns, 125-127

rows, 125

inner

ANSI STYLE syntax, 127

tables, 139-140, 462

keys, 124

shared multiple table joins, 140-141

maximums, 476

multiple table, 132

associative tables, 133

order of report, 134

providing relationships, 140

table order, 134

with shared join keys, 140-141

old style syntax, 124

benefits, 142

specifying conditions, 127

support, 126

versus ANSI STYLE, 126-127

writing, 126

one row to many rows, 126

one row to no rows, 125

one row to one row, 125

optimization, 463-469

cost, 464-467

overriding, 468-469

order, forcing, 468-469

outer, 469-471, 477

syntax, 470

tables, 139-140, 462

performance

indexes, 140

join order, 139

primary keys, 129-130

null values, 130

problems, 458-460

processing

logical model, 460-461

physical model, 462-463

qualifying column names, 129

query syntax, 125

referential integrity, 138-139

DRI, 138

enforcing, 138

relationships

cardinality, 135

many-to-many, 135

one-to-many, 135

one-to-one, 135

selectivity, 464

self, 471-477

specifying

join keys, 128

tables, 128

subqueries as, 156-158

complex joins, 157-158

mixing syntax, 167

tables, views, 218

UPDATE statements, 184

versus subqueries, 156

K

keys

alternate, 196

common, 124

column names, 143

composite, 130

foreign, 129-130, 191

deleting, 192

ER diagrams, 137

indexes, 198

inserting, 192

null values, 130

updating, 192

indexes, 195

join, 124

primary, 129-130, 191

deleting, 192

null values, 130

updating, 192

keysets (cursors), 281

keywords

AND, 56

AS, 27

BEGIN, 249-251

BETWEEN, 58-60

binary, 52

boolean, 53-54

order, 56

BREAK, 252

CASE, 163, 428, 454

crosstabs, 428-434

ELSE, 163

END, 163

multiple updates, 283-284

THEN, 163

views, 223-224

WHEN, 163

CLUSTERED, 195

CONTINUE, 252

CUBE, 110-112

etymology, 120

CURSOR, 279

DEFAULT, 187

DISTINCT, 155

versus WHERE EXISTS subquery, 161

END, 249-251

EXECUTE, running procedures, 334

GO, 234

HOLDLOCK, persistent shared locks, 436-441

IN, 60-61

common usage, 154

subqueries, 153-155

inequality, 56-57

IS, null values, 66

LIKE, 62-65

NONCLUSTERED, 195

NOT, 61

NOT BETWEEN, 59-60

OUTPUT, 342

ROLLUP, 112

etymology, 120

single result subqueries, 153

UNION, views, 221-222

VALUES, 176

see also clauses; functions; statements

L

LAST (scollable cursor option), 287-288

leaf level, 308

less than operator (<), 52

less than or equal operator (<=), 52

LIKE keyword, 62-65

date searches, 64-65

wildcard characters, 64

list of common error messages, 45-46

listed values, parameter defaults, 339-340

listing

columns (sp help), 32

tables

sp help, 29-30

sysobjects, 31

little float data type, 71

live locks, 439

local temporary tables, 208-213

INSERT SELECT statement, 211-212

names, 208-209

SELECT INTO statement, 210-213

local variables, 240-243

locking, 264-265, 449

blocking chains, 439

cursors, 289

deadlock, 273-274

exclusive intent locks, 439

granularity, 265-269

extent locks, 269

intent locks, 268

page locks, 268

row-level insert locks, 268

table locks, 268

holding shared locks, 270-271

live locks, 439

lock sleep state, 440

locktypes, 265-271

exclusive, 266-267

shared, 266, 270-271

update, 266

multiserver, 455

multiuser editing

manual checkout, 443

optimistic, 443-449

persistent exclusive locks, 441-443

persistent shared locks, 436-441

objects, syslocks system table, 89

optimizer hints, 271-273

HOLDLOCK, 271-272

shared intent locks, 438

sp lock procedure, 265-266

example, 269-270

sp who procedure, 269

spids, 265

update locks, 439

variables, 247-248

logical model (join processing), 460-461

logical reads, 321

logical shortcutting, 249

login

server, 88

syslogins system table, 89

loops, see flow control statements

LOWER string function, 80

LTRIM string function, 81

M

magic tables, 407

manual checkout systems (locking), 443

many-to-many relationships, 135

ER diagram symbol, 137

master database, 232

matching

datatypes (subqueries), 155

strings, 80

math functions, 83-84

ABS, 83

SIGN, 83

mathematical operations, result sets, 25-26

MAX aggregate function, 95

@@max precision global variable, 246

maximums, joins, 476

MDI applications, 21

messages

debugging, 356-357

errors

parameter defaults, 339

triggers, 410

handlers, 40

rows affected, 25

server, 39-42

metis technologies, llc, 11

Web site, 3, 12, 304

Microsoft SQL Server 6.5 Unleashed, 305

Microsoft SQL Server DBA Survival Guide, Second Edition, 305

MIN aggregate function, 95

model database, 232

models (join processing)

logical, 460-461

physical, 462-463

money data type, 72

inserting, 180

moving between tabs (ISQL/w), 23

MS SQL Server 6.x, 6

security, 10

msdb database, 232

multiline comments, 354

multiline tab format, 20

multiple statements (batches), 234-235

multiple table joins, 132

associative tables, 133

order of report, 134

providing relationships, 140

table order, 134

with shared join keys, 140-141

multipurpose procedures, 368-369

multiserver

locking, 455

work, 393

multiuser editing, locking

manual checkout, 443

optimistic, 443-449

persistent exclusive locks, 441-443

persistent shared locks, 436-441

N

naked rollbacks, 261

naming

columns, 171

returning (WHERE clause), 32

constraints, 195

local temporary tables, 208-209

objects, 229-230

procedures, 330

triggers, 402-403

nesting

aggregates (COMPUTE clause), 113-114

IF flow control statements, 254

transactions, 260-261

stored procedures, 384-385

triggers, 416

@@nestlevel global variable, 245

NEXT (scollable cursor option), 287

niladic functions, 79, 178

CURRENT TIMESTAMP, 443

NOCOUNT option, 176

NOLOCK (optimizer hint), 272

nonlogged operations, SELECT INTO statement, 181, 211

nonclustered indexes, 198, 303-304, 308-310

leaf level, 308

versus clustered, 310, 323

NONCLUSTERED keyword, 195

nonnumeric columns, aggregates, 99-100

NOT BETWEEN keyword, 59-60

NOT boolean keyword, 54, 61

not equal operator (!=), 52

not equal operator (< >), 52

null values, 65

aggregates, 94, 98-99, 102-103

creating tables, 172-173

IS keyword, 66

result sets, 66

rows, 66

sort order, 67

specifying acceptance (columns), 170

variables, 241

numeric data, converting to strings, 81-82

O

OBJECT ID system function, 89

OBJECT NAME system function, 89

objects

names, 229-230

sysobjects system table, 88

ODBC, changing database contexts, 231

old style syntax joins, 124

aliases, 128

benefits, 142

Cartesian products, 131

specifying conditions, 127

specifying tables, 128

support, 126

versus ANSI STYLE, 126-127

writing, 126

on-the-fly procedure execution, 373-374

one-line tab format, 19-20

one-to-many relationships/one-to-one relationships, 135, 142

ER diagram symbol, 137

opening cursors, 280

operations

DROP, 173

nonlogged, SELECT INTO, 181, 211

operators, see keywords

optimistic locking, 443-449

stored procedures, 449

TSEQUAL() function, 446-448

updating, 448

optimization

cost-based optimizer, 311

distribution pages, 313-314

joins, 463-469

cost, 464-467

overriding, 468-469

overriding, 321-323

SARGs, 312-314

showplan option, 317-319

statistics io option, 319-321

stored procedures, 362-363

INSERT statement, 380-382

problems, 367-369

temporary tables, 369-372

optimizer

deferred insertion mode, 380-381

locking hints, 271-273

HOLDLOCK, 271-272

options

forceplan, 468-469

NOCOUNT, 176

showplan, 362-364

optimization, 317-319

statistics io, optimization, 319-321

OR boolean keyword, 54-55

order (joins), forcing, 468-469

ORDER BY clause, 58

COMPUTE BY clause, 115-118

multiple columns, 117-118

GROUP BY clause, 107-108

sorting data, 33-39

ascending/descending order, 36-38

multiple columns, 35-36

ordinal method, 39

outside result set, 38-39

order of evaluation (clauses), 119

order of execution (batches), 234

order of operation, boolean expressions, 56

ordinal method (ORDER BY clause), 39

outer joins, 469-471, 477

full, 474

syntax, 470

outer tables, 139-140, 462

rows found, 466

scan cost, 465

OUTPUT keyword, 342

output parameters, 341-344, 349

RPCs, 344

overriding optimization, 321-323

P

@@pack received global variable, 246

@@pack sent global variable, 246

@@packet errors global variable, 246

page chains, 301

page locks, 268

page() DBCC command, 270

pages, 300

distribution, 313-314

extents, 301

PAGLOCK (optimizer hint), 272

parameters

output, 341-344, 349

RPCs, 344

stored procedures, 335-336

defaults, 338-341

passing, 336-337

quoted values, 338

syntax (CREATE PROCEDURE), 336

syntax (EXECUTE), 338

parent/child relationships, 471-475

parentheses (boolean expressions), 56

passing parameters to stored procedures, 336-337

passwords, syslogins system table, 89

PATINDEX string function, 82-83

percent sign (%) wildcard character, 62-64

performance

joins

indexes, 140

join order, 139

multiple table, 140

versus subqueries, 156

table order (FROM clause), 142

permanent temporary tables, 214-216

creating on startup, 215-216

versus normal tables, 215

persistent exclusive locks, 441-443

persistent shared locks, 436-441

physical model (join processing), 462-463

physical reads, 321

pound sign (#), 208

primary key constraints, 193

primary keys, 129-130, 191

deleting, 192

ER diagrams, 135

null values, 130

updating, 192

PRINT statement, 243, 349

syntax, 45

variables, 248

PRIOR (scollable cursor option), 287

problems

data integrity, multiuser editing, 435-449

joins, 458-460

procedures

cache, 361

observing (dbcc memusage), 364

group numbering, 351

remote, 392-394

coding, 394

setup, 393

transactions, 394

sp addmessage, 43-44

sp help, 29-32, 328

indexes, 304

information, 331-332

listing triggers, 400

sp helpdb, 233

sp helptext

displaying trigger creation code, 401-402

information, 332-333

sp lock, 265-266

example, 269-270

persistent shared locks, 437-441

variables, 247-248

sp makestartup, 215-216

sp recompile, 372

sp rename, 329

sp who, 269

stored, 327-329

compiling, 365-367

debugging, 354-357

developing, 329-331, 350

editing, 334-335

error checking, 387-389

executing, 363-364

INSERT statement, 378-382

locations, 350

multipurpose, 368-369

on-the-fly execution, 373-374

optimistic locking, 449

optimizing, 361-363, 367-372, 375

output parameters, 341-344

parameters, 335-341

processing, 360-362, 365

raising errors, 42-43

recompiling, 372

return status, 345-349

returning error information, 395

running, 333-334

tracking time, 357-360

transactions, 382-387

versus triggers, 420, 423

see also triggers

syntax, 349

system, 389-392

processing

batches, 360

joins

logical model, 460-461

physical model, 462-463

stored procedures

compilation, 365-367

execution, 363-364

on-the-fly execution, 373-374

optimization, 361-363, 367-372, 375

recompiling, 372

pubs database, 10, 233

Q

queries, 13, 17

16-table rule, 152

ad-hoc, 114-115

cost, 315-317, 463

COUNT(*), 96

covering, 310

defined, 12

executing partial (ISQL/w), 24

formatting

multiline tab format, 20

one-line tab format, 19-20

white space, 19

reformatting, 476

subtotals/totals

COMPUTE BY clause, 115-118

COMPUTE clause, 113-115, 118

CUBE keyword, 110-112

GROUP BY clause, 105-109

HAVING clause, 118-119

ROLLUP keyword, 112

troubleshooting, 27

see also subqueries

Query tab (ISQL/w), 22

quoted identifier, 199

quoted values, parameters, 338

R

RAISERROR statement, 42-43, 349

syntax, 44-45

tracking time, 357-358

RAND() function, 253

RDBMS (relational database management system), 7

read-aheads, 321

records, see rows

reference constraints, 193-195

referential integrity (RI)

declarative (DRI), 138, 190-191

constraints, 193-196

example, 196-203

foreign keys, 192

primary keys, 191-192

triggers, 414-416

enforcing, 138

reformatting, 476

relational database management system see RDBMS

relationships

cardinality, 135

ER diagram symbols, 137

many-to-many, 135

one-to-many, 135, 142

one-to-one, 135, 142

parent/child, self-joins, 471-475

providing (multiple table joins), 140

RELATIVE (scollable cursor option), 287-288

remote procedure calls (RPCs), 344

remote procedures, 392-394

coding, 394

setup, 393

transactions, 394

remote servers, 344, 392

repeating string pattern, 83

replacing triggers, 402

REPLICATE string function, 83

reports (CASE expression)

creating, 161-163

crosstab, 163-166

reserved words, 199

restricting

rows, 118-119

maximum number, 174

views, 217-218

triggers, 422-423

result sets, 12-18

altering data, 25-26

crosstab reports, 428-434

views, 432-433

column aliases, 26-27

limiting rows, WHERE clause, 50-51

null values, 66

sorting data outside (ORDER BY clause), 38-39

Results tab (ISQL/w), 22

retrieving

multiple rows, 153-156

duplicate list items, 155

IN keyword, 153-155

matching datatypes, 155

no rows returned, 155

subquery syntax, 155

single results, 152-153

keywords, 153

subquery syntax, 153

RETURN (flow control statement), 253

return status (procedures), 345-349

setting, 347-349

values, 346

commenting out, 354

returning error information (stored procedures), 395

REVERSE string function, 83

RI, see referential integrity

RIGHT string function, 80-81

ROLLBACK [TRAN[SACTION]] [SAVEPOINT] (transaction control statement), 259

ROLLBACK TRANSACTION statement, triggers, 411-413

rollbacks, naked, 261

ROLLUP keyword, 112

etymology, 120

root pages, 307

row count acknowledgment, disabling, 176

row-level insert locks, 268

@@rowcount global variable, 245

rows

adding to tables (INSERT statement), 175

columns

adding together, 75-76

fixed-length, 73

deleting (DELETE statement), 187-188

views, 220

identifying in joins, 125

inserting (INSERT statement)

default columns, 177

identity columns, 176-177

views, 218-220

null values, 66

outer tables, 466

restricting, 118-119

maximum number, 174

views, 217-218

result sets, limiting, 50-51

selecting, WHERE clause, 49-67

size limitations, 173

unique IDs, triggers, 423-424

updating single, 184

verifying existence, 160

see also crosstab reports

rows affected message, disabling, 25

RPCs (remote procedure calls), 344

RTRIM string function, 81

running

scripts, ISQL/w, 356

stored procedures, 333

EXECUTE keyword, 334

S

SARGs (search arguments), 312-314

SAVE TRAN[SACTION] (transaction control statement), 259

scalar aggregates, 106, 318

scan cost, inner tables, 466-467

scripts, 237

INSTPUBS, 11

running, ISQL/w, 356

scrollable cursors, 279, 287-288

searches

case sensitivity, 63-64

dates, LIKE keyword, 64-65

values, ranges, 58

security, 88

integrated/standard (MS SQL Server 6.x), 10

SELECT INTO statement, 180-181

local temporary tables, 210-213

versus INSERT SELECT, 182

SELECT INTO/BULKCOPY option, 210

SELECT permission denied on column 'audflags' of object 'sysobjects' in database 'master' error message, 46

SELECT statement, 14, 17, 349

constant string selection, 74

listing triggers, 401

selecting all columns, 27-28

syntax, 18-21, 119

multiple columns, 24-25

SELECT INTO statement, local temporary tables, 211

selecting

constant strings, 74

rows, WHERE clause, 49-67

selectivity, 315-317

joins, 464

self-joins, 471-477

SEQUEL, 5

server-specific global variables, 246-247

@@servername global variable, 247

servers

cursors, 282-283

errors, 39-46

adding new messages, 43

defining new messages, 44

fatal, 42

list of common messages, 45-46

raising, 42-43

user, 42

login, 88

messages, 39-42

multiple, locking, 455

remote, 344, 392

SQL, 6-7

history, 6

storage, 300-302

allocation units, 300

extents, 301

page chains, 301

pages, 300

SESSION USER niladic function, 178

setting

procedure return status, 347-349

quoted identifier, 199

shared intent locks, 438

shared locks, 266

holding, 270-271

persistent, 436-441

short circuit boolean logic, 249

shortcutting (logical), 249

showplan option, 362-364

optimization, 317-319

SIGN math function, 83

single-line comments, 355

small integer data type, 72

small datetime data type, inserting, 179

small money data type, 72

inserting, 180

snapshot cursors, 279, 289

software

SQL clients, 8

installing, 8-9

ISQL/w, 9-11, 21-24

sort order, 34

null values, 67

sorting

CASE expression, 162

columns (worktables), 453-454

data (ORDER BY clause), 33-39

ascending/descending order, 36-38

multiple columns, 35-36

ordinal method, 39

outside result set, 38-39

SOUNDEX string function, 83

sp addmessage procedure, 43-44

sp help procedure, 29-32, 328

indexes, 304

information, 331-332

listing triggers, 400

sp helpdb procedure, 233

sp helptext procedure

information, 332-333

triggers, displaying creation code, 401-402

sp lock procedure, 265-266

example, 269-270

persistent shared locks, 437-441

variables, 247-248

sp makestartup procedure, 215-216

sp recompile procedure, 372

sp rename procedure, 329

sp who procedure, 269

SPACE string function, 83

specifying length (data types), 170

@@spid global variable, 246

spids, 265

SQL (Structured Query Language), 4, 7

client software, 8

installing, 8-9

ISQL/w, 9-11, 21-24

formatting queries

multiline tab format, 20

one-line tab format, 19-20

white space, 19

history, 4-6

servers, 6-7

SQL-92, 5

SQL-EM (SQL Enterprise Manager), 331

SQLUse() function, 231

square brackets ([ ]) wildcard characters, 64

standard security (MS SQL Server 6.x), 10

starting ISQL/w, 9-11

problems, 11

statements

CREATE PROCEDURE, 329

syntax, 331

syntax with parameters, 336

WITH RECOMPILE option, 367

CREATE TABLE, 170-173

CREATE TRIGGER, 400

CREATE VIEW, 216

syntax, 217

DECLARE, 240, 279

syntax, 244, 279, 288

DELETE, 187-190

joins, 188

syntax, 190

versus DROP TABLE, TRUNCATE TABLE, 189

views, 188-189

DROP TRIGGER, 402

EXECUTE, syntax with parameters, 338

EXECUTE PROC, WITH RECOMPILE option, 366-367

INSERT, 175

batches, 236

default columns, 177

identity columns, 176-177

stored procedures, 378-382

INSERT SELECT, 181-182

local temporary tables, 211-212

versus SELECT INTO, 182

multiple (batches), 234-235

PRINT, 243, 349

syntax, 45

variables, 248

RAISERROR, 42-43, 349

syntax, 44-45

tracking time, 357-358

ROLLBACK TRANSACTION, triggers, 411-413

SELECT, 14, 17, 349

constant strings, 74

listing triggers, 401

selecting all columns, 27-28

syntax, 18-25, 119

SELECT INTO, 180-181

local temporary tables, 210-213

versus INSERT SELECT, 182

TRUNCATE TABLE, 189

UPDATE, 182-184, 220

CASE keyword, 283-284

cursors, 283-286

defaults, 186-187

direct updates in place, 406

executing triggers, 404-405

joins, 184

limits, 185-186

optimistic locking, 448

persistent exclusive locks, 441-443

single rows, 184

syntax, 187

variables, 243-244

USE, 230-231

see also keywords

Statistics I/O tab (ISQL/w), 23

optimization, 319-321

storage, 300-302

allocation units, 300

page chains, 301

pages, 300

extents, 301

stored procedures, 327-329

debugging

comments, 354-355

message handling, 356-357

developing, 329-331, 350

naming conventions, 330

editing, 334-335

error checking, 387-389

INSERT statement, 378-382

optimization, 380-382

locations, 350

multipurpose, 368, 369

optimistic locking, 449

output parameters, 341-344

RPCs, 344

parameters, 335

defaults, 338-339, 340-341

passing, 336-337

quoted values, 338

syntax (CREATE PROCEDURE), 336

syntax (EXECUTE), 338

processing, 360

compilation, 365-367

execution, 363-364

on-the-fly execution, 373-374

optimization, 361-363, 367-372, 375

recompiling, 372

raising errors, 42-43

return status, 345-349

setting, 347-349

values, 346

returning error information, 395

running, 333-334

EXECUTE keyword, 334

tracking time, 357-360

elapsed, 358-360

transactions, 382-387

coding approaches, 385-387

nesting, 384-385

versus triggers, 420-423

see also triggers

STR string function, 81-82

string functions, 80-83

ASCII, 82

CHAR, 82

CHARINDEX, 82-83

DIFFERENCE, 83

LOWER, 80

LTRIM, 81

PATINDEX, 82-83

REPLICATE, 83

REVERSE, 83

RIGHT, 80-81

RTRIM, 81

SOUNDEX, 83

SPACE, 83

STR, 81-82

SUBSTRING, 80-81

UPPER, 80

strings

addition operator (+), 74-75

blanks, 81

case, converting, 80

constant strings, selecting, 74

converting

from dates, 84

from numeric data, 81-82

long to short, 77

extracting from strings, 80-81

matching, 80

repeating pattern, 83

reversing, 83

wildcard characters, 63

structured programming, cursors, 289

Structured Query Language, see SQL

style guide (creating tables), 171-172

subqueries

16-table rule, 152

aggregates, 158-160

HAVING clause, 159-160

benefits, 152

CASE expression, 161-166

function, 161

keywords, 163

simple method, 161-163

WHEN expressions in, 162

correlated, 160

joins, 156

complex, 157-158

mixing syntax, 167

levels, 152

no rows returned, 167

null returns, 167

order of resolution, 153

placement, 153

returning multiple rows, 156

duplicate list items, 155

IN keyword, 153-155

matching datatypes, 155

no rows returned, 155

syntax, 155

returning single results, 152

errors, 153

keywords, 153

syntax, 153

versus joins, 156

WHERE EXISTS, 160-161

versus DISTINCT keyword, 161

SUBSTRING string function, 80-81

subtotals

COMPUTE BY clause, 115-118

multiple columns, 117-118

COMPUTE clause, 113, 118

ad-hoc queries, 114-115

GROUP BY clause, 105-112

columns outside select list, 107

CUBE keyword, 110-112

ORDER BY clause, 107-108

ROLLUP keyword, 112

HAVING clause, 118-119

SUM aggregate function, 94-95

SUSER NAME system function, 88

sysindexes table, 308

sysmessages system table, 42

sysobjects system table, 31

procedure information, 332

triggers, 401

system functions, 87-90

COL LENGTH, 90

DATALENGTH, 87

OBJECT ID, 89

OBJECT NAME, 89

SUSER NAME, 88

USER NAME, 88

system procedures, 389-392

system tables, 88-89

sysindexes, 308

sysmessages, 42

sysobjects, 31

triggers, 401

triggers, 422

SYSTEM USER niladic function, 178

T

T-SQL, 5

table level constraints, 195

table locks, 268

tables

adding rows (INSERT statement), 175

aliases, 129

assigning values (variables), 242

common keys, 124

copying data between, 180-182

creating, 170-173

column definitions, 171-173

size limitations, 173

style guide, 171-172

deleting, 173, 189

deleting rows, 187-188

inner, 139-140, 462

scan cost, 466-467

inserted/deleted, triggers, 406-408

inserting rows (INSERT statement)

default columns, 177

identity columns, 176-177

joining

aliases, 128

ANSI STYLE syntax, 124-126

composite keys, 130

cross joins, 131-132

defining relationships, 124

DELETE statement, 188

equals signs (=), 143

ER diagrams, 135-138

foreign keys, 129-130

identifying columns, 125-127

identifying rows, 125

inner tables, 139-140

multiple, 132-134, 140-141

OLD STYLE syntax, 124-126

one-row-to-many-rows, 126

one-row-to-no-rows, 125

one-row-to-one-row, 125

outer tables, 139-140

performance, 139-140

primary keys, 129-130

qualifying column names, 129

query syntax, 125

referential integrity, 138-139

relationships, 135

specifying join keys, 128

specifying tables, 128

views, 218

listing (sp help procedure), 29-30

listing (sysobjects), 31

magic, 407

outer, 139-140, 462

rows found, 466

scan cost, 465

scans, 300

system, 88-89

sysindexes, 308

sysmessages, 42

sysobjects, 31, 401

triggers, 422

temporary

global, 214

local, 208-213

optimizing stored procedures, 369-372

permanent, 214-216

worktables, 450-454

updating information, 182-187

defaults, 186-187

joins, 184

limits, 185-186

single rows, 184

views, DELETE statement, 188-189

versus permanent temporary tables, 215

TABLOCK (optimizer hint), 272

TABLOCKX (optimizer hint), 272

tempdb database, 232

temporary tables

global, 214

local, 208, 212-213

INSERT SELECT statement, 211-212

names, 208-209

SELECT INTO statement, 210-213

optimizing stored procedures, 369-372

permanent, 214

creating on startup, 215-216

versus normal tables, 215

worktables, 450

aggregates, 451-452

DISTINCT, 453

sorting columns, 453-454

text data type, 72

THEN keyword, CASE expression, 163

time, tracking (stored procedures), 357-358

elapsed, 358-360

timestamp columns, 443-449

optimistic locking, stored procedures, 449

TSEQUAL() function, 446-448

timestamp data type, 443

tiny integer data type, 72

@@total read global variable, 246

@@total write global variable, 246

totalling, SUM function, 94-95

totals

COMPUTE BY clause, 115-118

multiple columns, 117-118

COMPUTE clause, 113-118

ad-hoc queries, 114-115

GROUP BY clause, 105-112

columns outside select list, 107

CUBE keyword, 110-112

ORDER BY clause, 107-108

ROLLUP keyword, 112

HAVING clause, 118-119

tracking time (stored procedures), 357-358

elapsed, 358-360

@@trancount global variable, 385

transaction control statements, 260

@@trancount global variable, 246

Transact-SQL (T-SQL), 5

transactions

batches, 261-263

example, 261-263

control statements, 258-259

distributed, 455

error checking, 259-260

grouping, 263

implicit, 259

INSERT statement, stored procedures, 378-380

nesting, 260-261

optimizing, 273

remote procedures, 394

stored procedures, 382-383

coding approaches, 385-387

nesting, 384-385

triggers, 399-400, 420

audit trails, 419-420

cursors, 290

derived columns, 416-419

displaying creation code (sp helptext), 401-402

dos and don’ts, 421

DRI, cascading deletes, 414-416

dropping, 402

error messages, 410

executing, 403-406

UPDATE statement, 404-405

listing (SELECT statement), 401

listing (sp help procedure), 400

naming, 402-403

nesting, 416

replacing, 402

restricted actions, 422-423

ROLLBACK TRANSACTION statement, 411-413

row IDs, 423-424

sysobjects system table, 401

system tables, 422

tables, inserted/deleted, 406-408

UPDATE() function, 408-410

versus check constraints, 421

versus stored procedures, 420, 423

see also stored procedures

troubleshooting

ISQL/w installation, 11

queries, 27

TRUNCATE TABLE statement, 189

truncation, 77

TSEQUAL() function, 446-448

tuples, see rows

types, see data types

U-V

underscore (_) wildcard character, 64

UNION keyword, views, 221-222

unique constraints, 193, 196

update locks, 266, 439

UPDATE statement, 182-183, 220

defaults, 186-187

direct updates in place, 406

executing triggers, 404-405

joins, 184

limits, 185-186

multiple updates

CASE keyword, 283-284

cursors, 283-286

optimistic locking, 448

persistent exclusive locks, 441-443

single rows, 184

syntax, 187

variables, 243-244

UPDATE() function, 408-410

updating

foreign keys, 192

primary keys, 192

views, 220

UPDLOCK (optimizer hint), 272

UPPER string function, 80

USE statement, 230-231

user errors, 42

user information, sysusers system table, 88

USER NAME system function, 88

USER niladic function, 178

user-defined data types, 31-32

user-defined databases, 233

values

assigning (variables), 240-242

tables, 242

average, parameter defaults, 340-341

comparing list, 60-61

listed parameter defaults, 339-340

null values, 65-67

procedure return status, 346

quoted (parameters), 338

ranges, 58

return status, commenting out, 354

VALUES keyword, 176

variable characters, converting from data types, 77

variables

assigning values, 240-242

tables, 242

global

@@connections, 246

@@cpu busy, 246

@@cursor rows, 245

@@error, 245, 259, 387-389, 395-396

@@fetch status, 245, 281

@@identity, 201, 245

@@idle, 246

@@io busy, 246

@@max precision, 246

@@nestlevel, 245

@@pack received, 246

@@pack sent, 246

@@packet errors, 246

@@rowcount, 245

@@servername, 247

@@spid, 246

@@total read, 246

@@total write, 246

@@trancount, 246, 260, 385

@@version, 247

connection-specific, 245-246

server-specific, 246-247

local, 240-243

null values, 241

PRINT statement, 248

sp lock procedure, 247-248

UPDATE statement, 243-244

vector aggregates, 106, 318

@@version global variable, 247

views, 216

CASE keyword, 223-224

crosstabs, 432-433

DELETE statement, 188-189

deleting rows, 220

example, 222-223

inserting rows, 218-220

joining tables, 218

restricting rows, 217-218

UNION keyword, 221-222

updating, 220

WITH CHECK OPTION parameter, 219-220

W

Web sites, metis, 3, 12, 304

WHEN keyword (CASE expression), 162-163

WHERE clause, 31, 49-67

aggregates, 100-101

binary keywords, 52

equality conditions, 51-52

excluding rows, 99

inequality conditions, 52

optimizing stored procedures, 375

syntax, 58

updating single rows, 184

versus HAVING, 120

WHERE clauses

compound, 54-56

SARGs, 312-314

WHERE CURRENT OF clause, 286

WHERE EXISTS test, 160-161

versus DISTINCT keyword, 161

WHILE (flow control statement), 251-252

BREAK keyword, 252

CONTINUE keyword, 252

white space, 19

wildcard characters

LIKE keyword, 62-65

strings, 63

WITH CHECK OPTION parameter (views), 219-220

WITH RECOMPILE option

CREATE PROC statement, 367

EXECUTE PROC statement, 366-367

words, reserved, 199

work, 463

worktables, 450-453

aggregates, 451-452

DISTINCT, 453

sorting columns, 453-454

X-Y-Z

XOR boolean keyword, 54