Index
# and ## prefix for local/global, 91–93, 312
+ operator, 35
@@CURSOR_ROWS automatic variable, 263, 268–269
@@DBTS automatic variable, 74–75
@@ERROR automatic variable, 103–104, 118, 288–289, 327–328
@@FETCH_STATUS automatic variable, 263, 269
@@IDENTITY automatic variable, 75
@@MAX_PRECISION automatic variable, 329
@@MICROSOFTVERSION automatic variable, 502
@@NESTLEVEL automatic variable, 324, 328, 331
@@OPTIONS automatic variable, 324
@@PROCID automatic variable, 324
@@ROWCOUNT automatic variable, 33, 118, 334
@@SPID automatic variable, 324
@@TRANCOUNT automatic variable, 21, 293, 295–296, 298–300, 324
@@VERSION automatic variable, 9
3GL versus SQL approach, medians, 180–181
A
ACID (atomic, consistent, isolated, durable) test, 283–284
Administrative T-SQL, 401–461
automatic variables (functions), 402, 404–405
DMO SQLServer and Transfer objects, 460–461
Enterprise Manager, 401–402, 464, 476
GUI administration, 401–402
scripting routines, 456–461
sp_generate_script, 456–460
status routines, 405–418
system stored procedures, 312–314, 402–403, 503–509
See also Catalog procedures; Maintenance routines; Status routines
Aggregate columns, 11
SELECT and, 147–150
statistical functions, 176
tables as arrays, 222–223
See also GROUP BY clause; HAVING clause
ALL keyword, 148–149
ALL predicate function, 140
Alt-F1 (help facility), 3
Alt-X (run query), 2
ALTER PROCEDURE, 307
ALTER TABLE...DISABLE TRIGGER, 331
ALTER TABLE...ENABLE TRIGGER, 331
Ambraise, Trace
on loyalty and servility, 229
ANSI
referential actions, 87
SQL and NULL, 79–81
SQL schema VIEWs, 165
ANSI/ISO automatic cursor closing, 274–276
ANSI/ISO SQL-92 join syntax, 13–16
ANSI/ISO SQL-92 standard, 1
ANSI_NULLS, 81–83, 164, 317, 319–320
ANSI_PADDING, 36–37
ANY predicate function, 140
ARITHIGNORE, 57
Arithmetic and dates, 26–27
Arrays, 220–228
aggregate functions, 222–223
band, 224
CASE expressions, 226–227
comparing arrays, 226–228
DATALENGTH(), 217
DELETE, 221
dimensions, 221–225
elements, modifying, 219
GROUP BY clause, 222–223, 225–226
IDENTITY_INSERT, 221
INSERT, 214
integrity, ensuring, 225
MAX(), 225–226
MIN(), 226
multidimensional arrays, 214
normalization and, 213
ORDER BY clause for sorting tables, 221
PRIMARY KEY constraint, 225
QUOTENAME(), 218
reshaping arrays, 225–226
result sets, multiple, 217
SELECT, 218
SET IDENTITY_INSERT, 221
single column, 224–225
sorting, 221–223
SPACE(), 219
as strings (big), 213–219
SUBSTRING(), 215
as tables, 220–228
title column, 225
transposing dimensions, 223–225
uneven (jagged) arrays, 214–215
UNIQUE KEY constraint, 225
UPDATE, 222–224
VIEW object, 225
WHERE clause, 219–223, 227–228
AS keyword, 20
ASC keyword, 426
Asynchronous cursors, 272–273, 281
Atomicity of change, 21
Atomicity of transactions, 284
authors table, 51–52, 249, 289
Automatic transaction management, 288–289
Automatic transactions, 285
Automatic variables (functions)
administrative T-SQL, 402, 404–405
defined, 9
stored procedures, 323–324
undocumented, 502
See also specific @@ automatic variables
Autostart procedures, 330
B
base_schema_ver column, 306
BCP (Bulk Copy Program), 65–66, 105–106, 344–346
BEGIN DISTRIBUTED TRANSACTION, 287
BEGIN TRAN, 22, 285–286, 288, 293, 301
BETWEEN clause, 12, 179–180, 357, 363
BETWEEN predicate function, 128–131
Binary large objects. See BLOBs
Bit indexes, 90
Bit masks, 66–67
Bitmaps, 66–67
Bits, 66–67
BLOBs (binary large objects), 59–66
caveats, 59–60
DATALENGTH(), 60
DEFAULT, 65
indexes, 90
LIKE predicate function, 60
NULL, 65
READTEXT, 60–62
retrieving BLOB data, 60–63
SELECT, 60–63
select into/bulk copy, 65–66, 453
SET TRANSACTION ISOLATION LEVEL, 62–63
STUFF(), 64
TEXTVALID(), 60
TRANSACTION ISOLATION LEVEL (TIL), 62–63
transaction log and BLOB updates, 65–66
UPDATE, 63
UPDATETEXT, 63–65
updating BLOB data, 63–66
UPDLOCK keyword, 64
WITH LOG option, 65–66
WRITETEXT, 63–65
Bok, Derek, on ignorance and education, 241
Bookmark lookup step, 365, 367, 388
Bulk Copy Program (BCP), 65–66, 105–106, 344–346
BULK INSERT
defined, 105–106
as maintenance routine, 455
performance tips, 344–346
transactions and, 287
C
Calendar building, 31–34
CASCADE, 87
CASE
arrays and, 226–227
catalog procedures, 432
cursors and, 260–261
dates and, 33–34
EXISTS predicate function and, 137
hierarchies, 250
for horizontal aggregates, 197–199
medians and, 181–183
SELECT and, 10–11
statistical functions, 174–175
tables as arrays, 226–227
UPDATE and, 109–110
views and, 169–170
Catalog procedures, 419–432
ASC keyword, 426
CASE expressions, 432
COLUMNPROPERTY(), 427
COLUMNS INFORMATION_SCHEMA view, 419
DATABASEPROPERTY(), 427
DESC keyword, 426
EXEC(), 427
INDEXPROPERTY(), 427
INFORMATION_SCHEMA view, 419
sp_dir, 421–427
sp_object, 427–432
sp_table, 419–421
sysname, 419
TYPEPROPERTY(), 427
CATALOGPROPERTY(), 465
Celko, Joe
on learning SQL, 1
on lost data, 163
on paperless office, 475
char versus varchar, 35–36
CHARINDEX(), 37
chartdepth, 246
Check query for syntax errors (Ctrl-F5), 2
CHECK_CONSTRAINTS, 106
Clipping, statistical functions, 185–186
Clustered indexes, 105, 363, 365, 367
COALESCE(), 78–79
Column
aliases, 20–21
contrived (virtual) columns, denormalization by, 370
lists selection, 8
nullability, 4–5
values, swapping with UPDATE, 112–113
COLUMNPROPERTY(), 427, 465, 513
COLUMNS INFORMATION_SCHEMA view, 419
Command batches versus transactions, 284–285
Commands, administrative T-SQL, 402, 404
Comment headers, 308–309
COMMIT, 21
COMMIT TRAN, 285–286, 288, 293–295, 301
Compilation of stored procedures, 305–306
Compound clauses, 351–352
Computational and derived fields, 120–121
COMPUTE BY, 29–30
Computed column indexes, 90
Computed columns, 370
Concatenation, strings, 35
CONCAT_NULL_YIELDS_NULL, 81
Conceptual entities, 13
Condition or criterion, joins, 13
Consistency of transactions, 284
Constrained regions, 205–206, 209–210
Constraints
BULK INSERT and, 106
UPDATE for, 109–111
CONTAINS predicate function, 464, 468–471
CONTAINSTABLE() rowset function, 472–474
Contrived (virtual) columns, denormalization by, 370
Control-of-flow statements, 324–325
Correlated subqueries, 29, 141–147, 188
Cost-based optimization, 385
COUNT(*), 80
COUNT(cl), 80
CREATE DATABASE, 3
CREATE PROCEDURE, 303, 305, 307–308
CREATE STATISTICS, 433
CREATE TRIGGER, 330
CREATE VIEW, 94
Cross-tabulations (pivot tables), 152–153
Ctrl-E (run query), 2
Ctrl-F5 (check query for syntax errors), 2
CUBE operator, 153–157
Cumulative aggregates, 195–196
CURSOR_CLOSE_ON_COMMIT, 274–275
Cursors, 251–281
@@CURSOR_ROWS automatic variable, 263, 268–269
@@FETCH_STATUS automatic variable, 263, 269
ANSI/ISO automatic cursor closing, 274–276
asynchronous cursors, 272–273, 281
CASE expressions, 260–261
INTO clause, 270
closing automatically, 274–276
configuring, 272–276
cursor data type, 277
cursor threshold option, 272
CURSOR_CLOSE_ON_COMMIT, 274–275
DEALLOCATE, 263, 272, 277, 279
DECLARE CURSOR, 263–268
defaulting to global or local cursors, 276
for dynamic queries, 258–261
DYNAMIC (sensitive) cursors, 253–256
FAST_FORWARD option, 280
FORWARD_ONLY (default) cursors, 253–255, 280
GROUP BY clause, 260–261
Halloween Problem, 280–281
IDENTITY_INSERT, 258
implicit cursor conversions, 264
INSENSITIVE cursors, 268
ISAMS and, 251–253
KEYSET cursors, 253–254, 257–258
LOCAL keyword, 267–268
locks, 272
NEXT option, 271
NULL, 261
output parameters, 322
performance tips, 279–281, 346–347
positioned modifications, 276
PRIMARY KEY constraint, 258
READ_ONLY option, 280
RELATIVE 0, 271–272
ROLLBACK, 274–276
for row-oriented operations, 258, 261–263
for scrollable forms, 258, 263
SELECT DISTINCT, 260
SET, 272
SET CURSOR_CLOSE_ON_COMMIT, 274–275
SET IDENTITY_INSERT, 258
sp_configure, 272
sp_cursor_list, 279
sp_describe_cursor, 279
sp_describe_cursor_columns, 279
sp_describe_cursor_tables, 279
STATIC cursors, 253–254, 256–257, 268
stored procedures, 279
strings and, 51
syntax, 263–272
types of, 253–258
UNIQUE KEY constraint, 258
FOR UPDATE clause, 264–265
updating, 276–277
use, appropriate, 258–263
value1 column, 260
WHERE CURRENT OF clause, 265, 276
customers table, 145–147
D
Data Definition Language (DDL), 85–95
# and ## prefix for local/global, 91–93, 312
ANSI referential actions, 87
bit indexes, 90
BLOBs (binary large objects) indexes, 90
CASCADE, 87
computer column indexes, 90
CREATE INDEX, 90–91
CREATE TABLE, 86–89
CREATE VIEW, 94
database context, changing temporarily, 94
default constraints, 87–89
DROP TABLE, 89–90
DROP_EXISTING, 91
foreign keys, 86–87
global temporary status tables, 92–93
global temporary stored procedures, 92
NO ACTION, 87
NULL exception, 87
object creation in other databases, 91
object naming and dependencies, 93–95
objects, dropping, 89–90
PAD_INDEX, 90–91
qualified object names, using, 91
referential integrity (RI), 86
SET DEFAULT, 87
SET NULL, 87
sp_depends, 94–95
sysdepends table, 94–95
tempdb, 91
temporary objects, 91–93
temporary stored procedures, 92
temporary table indexes, 94
temporary table name length, 92
TRUNCATE TABLE, 87
unique index requirement, 87
varchar default, 89
views, unusable, 94
Data directory, 3
Data functions, 518–519
Data Manipulation Language (DML), 97–118, 164. See also DELETE; INSERT; SELECT; UPDATE
Data scrubbing, 521–525
Data type conversion, 10
Data types, 23–75
@@DBTS automatic variable, 74–75
@@IDENTITY automatic variable, 75
bit masks, 66–67
bitmaps, 66–67
bits, 66–67
cursor variables, 68–73
DEALLOCATE, 70
DECLARE CURSOR, 69
FETCH, 71
global unique identifiers (GUID), 67–68
image column, 67
NEWID(), 67–68
OUTPUT parameter, 71
ROWGUIDCOL keyword, 68
scalar types, 23
selectivity of index, 66
SET, 69
sp_cursor_list, 71–72
sp_describe_cursor, 71
statblob column, 67
timestamps, 73–75
TSEQUAL(), 73
uniqueidentifer, 67–68
UPDATE, 73–74
USE tempdb, 75
WHERE clause, 73
See also BLOBs (binary large objects); Dates; Numerics; Strings
Data warehouse optimizations, 389–391
Database consistency checker commands. See DBCC
Database context, changing temporarily, 94
Database design performance tips, 338–340
DATABASEPROPERTY(), 427, 443, 513–514
DATALENGTH(), 60, 217, 518–519
Date, Chris, 77
DATEDIFF(), 26–27
Dates, 23–34
@@ROWCOUNT automatic variable, 33
arithmetic and, 26–27
calendar building, 31–34
CASE expressions, 33–34
FROM clause, 34
COMPUTE BY, 29–30
correlated subqueries, 29, 141–147
DATEADD(), 26
DATEDIFF(), 26–27
datetime types, 23–24
DAY(), 26
functions, 25–26
GETDATE(), 26
GROUP BY clause, 30
GROUPING(), 30–31
HAVING clause, 30–31
HOLIDAYS, 33–34
ISNULL(), 31
looping construct, need for, 33
MONTH(), 26
NULL, 31
problems, 24–25
reference dates, 24
ROLLUP, 30
SARGs, 362–364
smalldatetime types, 23
time gap determination, 27–31
UPDATE, 32–33
Y2K problems, 24–25
YEAR(), 26
DAY(), 26
DB: combo-box, 4
DBCC, undocumented, 488–499
DBCC ADDEXTENDEDPROC(), 488
DBCC ADDINSTANCE(), 488
DBCC BCPTABLOCK(), 489
DBCC BUFFER(), 489
DBCC BYTES(), 490
DBCC CALLFULLTEXT(), 490
DBCC CLEANBUFFERS(), 344
DBCC DBCONTROL(), 491
DBCC DBINFO(), 491
DBCC DBRECOVER(), 492
DBCC DBREINDEX(), 341–342, 437, 440
DBCC DBTABLE(), 492
DBCC DELETEINSTANCE(), 493
DBCC DES(), 493
DBCC DETACHDB(), 493
DBCC DROPCLEANBUFFERS, 493
DBCC DROPEXTENDEDPROC(), 493
DBCC ERRORLOG, 494
DBCC EXTENTINFO(), 494
DBCC FLUSHPROCINDB(), 348, 494
DBCC FREEPROCCACHE, 344, 348, 494
DBCC HELP, 488
DBCC IND(), 494–495
DBCC INPUTBUFFER(), 410
DBCC LOCKOBJECTSCHEMA(), 495
DBCC OPENTRAN(), 299
DBCC OUTPUTBUFFER(), 410
DBCC PERFMON(), 398
DBCC PROCCACHE(), 348
DBCC PRTIPAGE(), 497
DBCC PSS(), 410
DBCC RESOURCE, 497–498
DBCC SETINSTANCE(), 498–499
DBCC SHOWCONTIG(), 342
DBCC SQLPERF(), 398
DBCC TAB(), 499
DBCC UPDATEUSAGE(), 435–437
DBCC UPGRADEDB(), 499
DDL. See Data Definition Language
DEALLOCATE, 70, 263, 272, 277, 279
Debugging
stored procedures, 334–335
transactions, 299–300
Decimal type, 54
Declarative referential integrity (DRI) and triggers, 331
DECLARE, 34
Default constraints, DDL, 87–89
Default (FORWARD_ONLY) cursors, 253–255, 280
DEFAULT keyword, 98–99
DEFAULT VALUES form of INSERT, 100
Defaulting to global or local cursors, 276
Deferred updates, 108
DELETE, 114–118
arrays and, 221
overview, 7
performance tips, 346
rows affected by, limiting, 115–116
SELECT TOP n option, 115–116
TRUNCATE TABLE, 117–118
WHERE clause, 115
WHERE CURRENT OF clause, 116–117
Deleted tables and triggers, 331
Denormalization, 368–384
ALTER TABLE, 370
computed columns, 370
by contrived (virtual) columns, 370
CREATE TABLE, 370
DBCC PAGE(), 378
FILLFACTOR, 378
first column, 378–379
FirstIAM column, 378–379
guidelines, general, 369
by horizontal partitioning, 380–384
inline summarization, 371–374
m_slotcnt, 378
by redundant data, 370–371
root column, 378–379
sp_decodepagebin, 378–379
by summary tables, 371–374
by vertical partitioning, 374–380
Derived tables, 122–126, 167–168
DIFFERENCE(), 46–47
Differences, sets, 231–233
Dimensions, tables as arrays, 221–225
DISTINCT keyword, 148–149, 151, 189, 203, 232
Distributed transactions, 287
Division by zero, 57–58
DLLs (Dynamic Link Libraries), 47, 314–316
DML. See Data Manipulation Language
DMO SQLServer and Transfer objects, 460–461
DRI (declarative referential integrity) and triggers, 331
DROP INDEX, 437
DROP TABLE, 89–90
DROP_EXISTING, 91
Duplicates
INSERT for removing, 104–105
sets, 233
values, 184–185
Durability of transactions, 284
Dynamic Link Libraries (DLLs), 47, 314–316
Dynamic queries, cursors for, 258–261
DYNAMIC (sensitive) cursors, 253–256
Dynamic VIEWs, 168–170
E
Editor selection, 2–3
Elements, modifying, 219
employee_name, 246
Empty values versus missing values, 77
ENCRYPT(), 499
Encryption
views, 166
Enterprise Manager, 401–402, 464, 476. See also Administrative T-SQL
Environment options, stored procedures, 311–312
Environmental concerns of stored procedures, 317–320
ERRORLEVEL, 326
Errors
@@ERROR automatic variable, 103–104, 118, 288–289, 327–328
INSERT and, 103–104
stored procedures, 325–328
EXCEPT keyword, 231–232
EXEC()
catalog procedures, 427
status routines, 418
stored procedures, 305–307, 316–317
strings, 49–54
EXEC form of INSERT, 101–103, 317, 418, 453
Execution plans, stored procedures, 306–307
EXISTS predicate function, 133–138
IN and, 135–136
CASE expressions and, 137
FROM clause and, 137
HAVING clause, 133
joins and, 136–137
maintenance routine, 445
NOT EXISTS, 135
NULLs affect on, 134–135
result set emptiness, 137
WHERE clause, 133
Expressions and NULL, 78
Expressions selection, 9
Extended procedures, 102–103, 314–316, 503–509
Extremes, statistical functions, 197–199
F
F5 (run query), 2
False, 77–78
FAST_FORWARD option, 280
Faux (pseudo) procedures, 316
FETCH
cursors and, 263, 268–272, 277, 279
data types and, 71
FILLFACTOR, 378
Filtering data, 11–18
ANSI/ISO SQL-92 join syntax, 13–16
BETWEEN clause, 12
FROM clause, 15
conceptual entities, 13
CROSS JOIN, 17
FULL OUTER JOIN, 17
GROUP BY clause, 11
join condition or criterion, 13
joins, 13–17
left joins, 13–16
legacy join syntax, 13–16
multilevel joins, 14
normalization, 13
NULL, 13–17
outer joins, 13–17, 126–128, 232
physical entities, 13
RIGHT JOIN, 17
RIGHT OUTER JOIN, 16
Financial median, 182–183
first column, 378–379
FirstIAM column, 378–379
Floating point type, 54–57, 174
Flow control language, 324–325
FOR UPDATE clause, 264–265
Ford, Henry, on thinking, 401
FOREIGN KEY, 7
Foreign keys, 86–87
FORMATMESSAGE(), 519–520
FORMSOF() clause, 471
FORWARD_ONLY (default) cursors, 253–255, 280
FREETEXT predicate function, 468, 471
FREETEXTTABLE() rowset function, 464, 474
FROM clause. See also Rowset functions
dates and, 34
EXISTS predicate function and, 137
for filtering data, 15
FULL OUTER JOIN, 17
Full-text search, 463–474
CATALOGPROPERTY(), 465
COLUMNPROPERTY(), 465
CONTAINS predicate function, 464, 468–471
CONTAINSTABLE() rowset function, 472–474
Enterprise Manager, 464
FORMSOF() clause, 471
FREETEXT predicate function, 468, 471
FREETEXTTABLE() rowset function, 464, 474
ISABOUT(), 473–473
LIKE predicate function, 470
Microsoft Search, 463–464, 467
NET START, 467–468
predicates, 468–471
rowset functions, 471–474
sp_fulltext_catalog, 468
sp_fulltext_column, 468
sp_fulltext_table, 468
timestamp column, 464
Functions, 9
administrative T-SQL functions, 402, 404
date functions, 25–26
index functions, 515–518
string functions, 37–47, 519–521
See also Aggregate functions; Automatic variables (functions); Functions, obscure; Functions, undocumented; Numerics; Predicates
Functions, obscure, 511–521
COLUMNPROPERTY(), 513
data functions, 518–519
DATABASEPROPERTY(), 513–514
DATALENGTH(), 518–519
FORMATMESSAGE(), 519–520
GETANSINULL(), 511–512
GETDATE(), 512–513
HOST_NAME(), 512
identifier functions, 514–515
IDENT_INCR(), 514
IDENTITY(), 514–515
IDENTITYCOL, 514
IDENT_SEED(), 514
index functions, 515–518
INDEX_COL(), 515–516
INDEXPROPERTY(), 516–517
ISDATE(), 518
ISNUMERIC(), 518
NEWID(), 515
PARSENAME(), 520
property functions, 513–514
QUOTENAME(), 520–521
ROWGUIDCOL keyword, 515
STATS_DATE(), 517
status functions, 511–513
string functions, 519–521
SUSER_NAME(), 513
SUSER_SNAME(), 513
TYPEPROPERTY(), 514
USERNAME(), 512–513
Functions, undocumented, 499–502
ENCRYPT(), 499
GET_SID(), 499–500
OBJECT_ID(), 500
PWDCOMPARE(), 500–501
PWDENCRYPT(), 501
TSEQUAL(), 501–502
G
GET_SID(), 499–500
GG_TS_Log.LDF, 3
GG_TS.MDF, 3
Global (##) prefix, 91–93, 312
Global temporary status tables, 92–93
Global temporary stored procedures, 92
Global unique identifiers (GUID), 67–68
GO, 307
GROUP BY ALL, 150–151
GROUP BY clause, 150–157
CROSS JOIN, 156
CUBE operator, 153–157
cursors and, 260–261
date functions and, 30
DATEPART(), 153
DISTINCT keyword, 151
for filtering data, 11
HAVING clause and, 151–152, 157
hierarchies, 246–247
ORDER BY clause and, 151
pivot tables (cross-tabulations), 152–153
qtr column, 153
runs and sequences, 202, 204, 206, 208, 210–211
sales table, 155
stor_name column, 154
subqueries and, 147
for tables as arrays, 222–223, 225–226
type column, 154
yr column, 153
Grouping optimizations, 391
GUI administration, 401–402
GUID (global unique identifiers), 67–68
H
Halloween Problem, 108–109, 280–281
Hash joins, 386
Hash match operation, 359
aggregate functions, 149–150
clipping and, 186
date functions, 30–31
duplicate values and, 184
EXISTS predicate function and, 133
GROUP BY clause and, 151–152, 157
medians and, 184
overview, 19
runs and sequences, 206, 208–211
SELECT TOP option, 125
sets and, 230–231
subqueries, 147
UNION and, 230–231
views, 169
Help facility (Alt-F1), 3
Heuristics and Query Optimizer, 385
Hierarchies, 241–250
authors table, 249
CASE expressions, 250
CHAR(), 250
chartdepth, 246
employee_name, 246
GROUP BY clause, 246–247
IDENTITY(), 245
indented lists, 249–250
indenting hierarchies, 245–248
INSERT, 244
leaf nodes, listing, 248
multilevel hierarchies, 242–248
pubs database, 249
SELECT...INTO, 245
single level hierarchies, 241–242
Histograms, 193–194
Holaday, Thomas L.
on Init, Use, Destroy procedure calls, 213
on motivation, 283
on task completion, 119
HOLIDAYS, 33–34
Horizontal aggregates, 197–199
Horizontal partitioning, denormalization by, 380–384
HOST_NAME(), 512
I
Identifier functions, 514–515
IDENT_INCR(), 514
Identity columns, 106, 177–181
IDENTITYCOL, 514
IDENTITY_INSERT, 99–100, 221, 258
IDENT_SEED(), 514
IGNORE_DUP_KEYS option, 104–105, 525
image column, 67
Implicit cursor conversions, 264
Implicit transactions, 286, 288
IMPLICIT_TRANSACTIONS, 21, 286, 288
IN predicate function, 135–136, 138–139
Indented lists, 249–250
Indenting hierarchies, 245–248
Index
functions, 515–518
INSERT and indexes, 105
joins, 387–388
merging and intersection, 388
optimizations, 387–389
performance tips, 339–342
See also Full-text search
Index Tuning Wizard, 394–395
INDEX_COL(), 515–516
Inequalities, 360–361
INFORMATION_SCHEMA SCHEMATA view, 435
INFORMATION_SCHEMA view, 67, 165, 418–419, 435
INFORMATION_SCHEMA.KEY_COLUMN_USAGE, 516–517
INIT_SERVER.SQL, 448–453
Inline summarization, 371–374
INSENSITIVE cursors, 268
Insensitive (STATIC) cursors, 253–254, 256–257, 268
INSERT, 97–106
@@ERROR automatic variable, 103–104
arrays and, 214
BLOBs (binary large objects) and, 60, 63
Bulk Copy Program (BCP), 65–66, 105–106, 344–346
BULK INSERT, 105–106, 287, 344–346, 455
CHECK_CONSTRAINTS, 106
clustered indexes and, 105
constraints and BULK INSERT, 106
DEFAULT keyword, 98–99
DEFAULT VALUES form of INSERT, 100
for duplicate removal, 104–105
duplicate rows removing using, 104–105
errors and, 103–104
extended procedures, 102–103
hierarchies and, 244
identity columns and BULK INSERT, 106
IDENTITY_INSERT, 99–100, 221, 258
IGNORE_DUP_KEYS option, 104–105
indexes and, 105
KEEPIDENTITY keyword, 106
NULL, 98–100
numeric functions, 58
overview, 5–6
performance tips, 344
SELECT form of INSERT, 100–101
SET IDENTITY_INSERT, 99–100, 221, 258
stored procedures, 317
string functions, 52
strings as arrays, 214
triggers and BULK INSERT, 106
uniqueifier, 105
VALUES clause, 98
Inserted tables and triggers, 331
INSERT...EXEC, 101–103, 317, 418, 453
Integer type, 54
Integrity of tables as arrays, 225
Internals, stored procedures, 305–307
Intersections, sets, 234–235
Intervals, runs and sequences, 201, 210–212
ISABOUT(), 473
ISAMS and cursors, 251–253
ISDATE(), 518
ISNUMERIC(), 518
Isolation of transactions, 284
items table, 145–147
Iteration advantage of Query Optimizer, 384
Iteration tables, 525–526
J
Jagged (uneven) arrays, 214–215
JOIN, 202, 204, 206, 208, 210, 238
Joins, 13–17
ANSI/ISO SQL-92 join syntax, 13–16
conceptual entities, 13
condition or criterion, 13
CROSS JOIN, 17
EXISTS predicate function and, 136–137
FULL OUTER JOIN, 17
left joins, 13–16
legacy join syntax, 13–16
multilevel joins, 14
normalization, 13
NULL, 13–17
optimizations, 385–386
order, effect on, 126–128
OUTER JOINS, 13–17, 126–128, 232
physical entities, 13
RIGHT JOIN, 17
RIGHT OUTER JOIN, 16
subqueries versus, 142–144
See also Subqueries (subselect)
K
KEEPIDENTITY keyword, 106
Kenton, H.W.
on “Don’t fix it if it ain’t broke,” 23
on engineering’s artistic element, 463
on good engineering, 337
on intolerance, 511
on marketing, 251
on politicians versus engineers, 97
KEY_COLUMN_USAGE system view, 516–517
KEYSET cursors, 253–254, 257–258
L
Leaf nodes, listing, 248
Left joins, 13–16
Legacy join syntax, 13–16
Levenstein, Aaron, on statistics, 173
LIKE predicate function, 48–49, 60, 131–133, 470
LOCAL keyword, 267–268
Locks, 272
Logging, minimizing, 287
Looping construct, need for, 33
M
Maintenance routines, 432–455
BULK INSERT, 455
CREATE STATISTICS, 433
DATABASEPROPERTY(), 443
DBCC UPDATEUSAGE(), 435–437
DROP INDEX, 437
EXISTS predicate, 445
INFORMATION_SCHEMA SCHEMATA view, 435
INFORMATION_SCHEMA view, 435
INIT_SERVER.SQL, 448–453
INSERT...EXEC, 453
model database, 453
no_output option, 445
on_success_action, 453
PRIMARY KEY constraint, 437
SELECT *, 455
select into/bulk copy, 65–66, 453
sp_add_jobserver, 453
sp_attach_db, 448
sp_copyfile, 443–445
sp_dbbackup, 440–443
sp_dboption, 448
sp_generate_script, 453
sp_make_portable, 445–448
sp_readtextfile, 453–455
sp_rebuildindexes_all, 437–440
sp_update_stats_all, 432–435
sp_updateusage_all, 435–437
tempdb, 453
trunc.log on chkpt, 443
UNIQUE constraint, 437
UPDATE STATISTICS, 433, 517–518
xp_cmdshell, 443
Masks, 48–49
MAX(k1) query, 179
Medians, 177–185
CASE technique, 181–183
BETWEEN clause, 179–180
duplicate values, 184–185
financial median, 182–183
HAVING clause and duplicate values, 184
identity column technique, 177–181
MAX(k1) query, 179
SIGN(), 179–180
statistical median, 182
3GL versus SQL approach, 180–181
vector medians, 183
Merge joins, 385
Microsoft Search, 463–464, 467
Microsoft Transact-SQL. See Transact-SQL
Minimal perfect hashing function, 386
Missing values, 77–84
ANSI SQL and NULL, 79–81
ANSI_NULLS, 82–83
ANSI_WARNINGS, 80
COALESCE(), 78–79
CONCAT_NULL_YIELDS_NULL, 81
COUNT(*) function, 80
COUNT(cl) function, 80
empty values versus, 77
expressions and NULL, 78
False, 77–78
functions and NULL, 78–79
GETANSINULL(), 81
ISNULL(), 78–79
NULL, 77–78
“=NULL,” 81–82
problems with, 83–84
SET ANSI_NULL_DFLT_ON/_OFF, 5, 81
SET ANSI_NULLS, 81–83
SET ANSI_WARNINGS, 80
SET CONCAT_NULL_YIELDS_NULL, 81
stored procedures and NULL, 81–83
True, 77–78
truth tables, 77–78
Unknown, 77–78
See also NULL
model database, 453
Modes, statistical functions, 193
Monetary type, 58
Money, 58
MONTH(), 26
m_slotcnt, 378
Multidimensional arrays, 214
Multilevel hierarchies, 242–248
Multilevel joins, 14
“Multisets,” 233
N
National Committee on Information Technology Standards (NCITS H2), 1
Nested
loops, 385
stored procedures, 328
transactions, 293–296
triggers, 331
NET START, 467–468
NEXT option, 271
NO ACTION, 87
NOCOUNT ON, 312
Nonlogged operations, 285, 287, 301, 331
no_output option, 445
Normalization
of arrays, 213
of joins, 13
See also Denormalization
NOT NULL, 4–5
BLOBs (binary large objects), 65
cursors, 261
date functions, 31
exception, 87
EXISTS predicate function and, 134–135
expressions and, 78
filtering data, 13–17
INSERT, 98–100
joins, 13–17
numeric functions, 57
SQL and, 79–81
stored procedures and, 81–83, 321
string functions, 34
See also Missing values
“=NULL,” 81–82
Numerics, 54–59
ANSI_WARNINGS, 57
ARITHIGNORE, 57
CAST(), 58
CONVERT(), 58
decimal type, 54
division by zero, 57–58
floating point type, 54–57, 174
formatting, 58–59
INSERT, 58
integer type, 54
monetary type, 58
money, 58
NULL, 57
numeric type, 54
real type, 54
smallmoney, 58
STR(), 58–59
UPDATE, 58
nvarchar, 52
O
OBJECT_ID(), 500
OBJECT_NAME(), 418
OBJECTPROPERTY(), 314, 320, 427, 431, 465, 468, 479
O’Dell, Margaret, 38
ODS (Open Data Services), 47
OLE automation, 475–486
Enterprise Manager, 476
OBJECTPROPERTY(), 479
sp_displayoaerrorinfo, 479
sp_exporttable, 476–480
sp_getSQLregistry, 484–486
sp_importtable, 480–483
SP_OAGetErrorInfo, 479
ON clause, 204
on_success_action, 453
Open Data Services (ODS), 47
Optimizing code, transactions, 300–301
Oracle DECODE(), 11
ORDER BY clause, 159–161
catalog procedures, 426–427, 431
derived tables and, 168
GROUP BY clause and, 151
for horizontal aggregates, 198
overview, 19–20
IN predicate function and, 138–139
SELECT and, 121–122, 125, 159–161
sets, 236–237
for sorting tables, 221
views, 163–164
orders table, 145–147
OSQL, 3
OUTER JOINS, 13–17, 126–128, 232
OUTPUT keyword, 322
OUTPUT parameter, 71
Output parameters, 321–322
P
PAD_INDEX, 90–91
Parameters, stored procedures, 308, 320–323
PARSENAME(), 520
Partitioning
data using views, 170–172
denormalization by, 374–384
intervals, 211–212
Percent (%) wildcard, 12, 131–133
PERCENT keyword, 121
Perfect hashing function, 386
Perfmon (Performance Monitor), 397–398
Performance tuning, 337–399
Bulk Copy performance tips, 344–346
BULK INSERT performance tips, 344–346
cursor performance tips, 279–281, 346–347
database design performance tips, 338–340
DBCC CLEANBUFFERS(), 344
DBCC DBREINDEX(), 341–342
DBCC FLUSHPROCINDB(), 348
DBCC PERFMON(), 398
DBCC PROCCACHE(), 348
DBCC SHOWCONTIG(), 342
DBCC SQLPERF(), 398
DELETE performance tips, 346
guidelines, general, 337–338
index performance tips, 339–342
Index Tuning Wizard, 394–395
INSERT performance tips, 344
Perfmon (Performance Monitor), 397–398
Profiler tool, 396
SELECT performance tips, 342–344
stored procedures performance tips, 347–351
syscacheobjects table, 348–351
UPDATE performance tips, 346
user counters, 398
See also Denormalization; Query Optimizer; SARGs (search arguments)
Phantom rows, 292
Physical entities, 13
Pivot tables (cross-tabulations), 152–153
Plus sign (+) operator, 35
Positioned modifications, 276
Potpourri, 511–526
data scrubbing, 521–525
functions, obscure, 511–521
iteration tables, 525–526
Pound sign (# and ##) local/global prefix, 91–93, 312
Predicates, 128–140
ALL predicate function, 140
ANSI padding, 133
ANY predicate function, 140
CASE expressions and EXISTS predicate function, 137
FROM clause and EXISTS predicate function, 137
EXISTS predicate function, 133–138
IN and EXISTS predicate function, 135–136
functions, 128–140
joins and EXISTS predicate function, 136–137
LIKE predicate function, 131–133
NOT EXISTS, 135
NULLs affect on EXISTS predicate function, 134–135
optimizations, 391–393
ORDER BY clause and IN predicate function, 138–139
padding, 133
BETWEEN predicate function, 128–131
IN predicate function, 138–139
qty column, 134
result set emptiness and EXISTS predicate function, 137
sales table, 134
title_id column, 134–135
titles table, 134
See also Missing values
price field, 143
PRIMARY KEY constraint, 225, 258, 437
PRINT, 410
Procedures
extended procedures, 102–103, 314–316, 503–509
system procedures, 312–314, 402–403, 503–509
Profiler tool, 396
Property functions, 513–514
Pseudo (faux) procedures, 316
psrvproc->>m_pwchLangBuff column, 410
psrvproc->>srvio.outbuff column, 410
pubs database, 249
PWDCOMPARE(), 500–501
PWDENCRYPT(), 501
Q
qtr column, 153
Qualified object names, using, 91
Query Analyzer, 2–3
Query Optimizer, 384–393
Bookmark lookup step, 388
cost-based optimizations, 385
data warehouse optimizations, 389–391
grouping optimizations, 391
hash joins, 386
heuristics and, 385
index joins, 387–388
index merging and intersection, 388
index optimizations, 387–389
iteration advantage of, 384
join optimizations, 385–386
merge joins, 385
minimal perfect hashing function, 386
nested loops, 385
perfect hashing function, 386
predicate clause optimizations, 391–393
semantic optimization, 385
semijoins, 391
syntactic elements and, 385
See also SARGs (search arguments)
Query tree, 305
Querying data. See Query Optimizer; SELECT
QUOTED_IDENTIFIER, 164, 317–320
R
RAISERROR, 325–328
Rankings, statistical functions, 190–192
READ COMMITTED, 290–291
Read-only databases, 287
READ UNCOMMITTED, 289–290, 301
READ_ONLY option, 280
READTEXT, 60–62
Real type, 54
Recursion, 328–330
Recursive triggers, 331
Redundant data, denormalization by, 370–371
Reference dates, 24
Referential integrity (RI), 86
Regions within runs and sequences, 201, 203–210
Relational division, subqueries, 145–147
RELATIVE 0, 271–272
Relative condition regions, 204–205
Reserved words, 318–319
Reshaping arrays, 225–226
Result codes and meanings, 322–323
Result sets
EXISTS predicate function and, 137
multiple (arrays), 217
RETURN, 322–323
RI (referential integrity), 86
RIGHT JOIN, 17
RIGHT OUTER JOIN, 16
ROLLBACK TRAN, 286, 288–289, 293–299, 301, 331
ROLLBACK TRIGGER, 331
root column, 378–379
Row-oriented operations, cursors for, 258, 261–263
Row-positioning problems. See Medians
Rowset functions, 471–474
RTRIM(), 35
Run query shortcuts, 2
Running aggregates, 195–196
Runs and sequences, 207–212
ON clause, 204
constrained regions, 209–210
constraining region sizes, 205–206
COUNT(), 203
DATEADD(), 202
DISTINCT keyword, 203
every nth value sampling, 203
GROUP BY clause, 202, 204, 206, 208, 210–211
ISNULL(), 206
partitioned intervals, 211–212
region boundaries, 206–208
regions within, 203–207
relative condition regions, 204–205
sequences, 201–207
SIGN(), 203
SUBSTRING(), 203
time series, 201–203
Russell, Robert, 38
S
sales table, 134, 149, 155, 169, 291–292
SARGs (search arguments), 351–368
Bookmark lookup step, 365, 367
clustered indexes, 105, 363, 365, 367
compound clauses, 351–352
dates, 362–364
hash match operation, 359
index covering, 367
inequalities, 360–361
WHERE clause, 353, 356–357, 359, 363
Save command, 21
Save points, 296–297
SAVE TRAN, 296–297
Scalar types, 23
schema_ver column, 306
Scripting routines, 456–461
Scrollable forms, cursors for, 258, 263
SDI (SQL Server Debug Interface), 334–335
Search arguments. See SARGs
SELECT, 119–161
@@VERSION automatic variable, 9
aggregate columns, 11
aggregate functions, 147–150
ALL keyword, 148–149
arrays and, 218
automatic variables, 9
BLOBs (binary large objects) and, 60–63
CASE and, 10–11
CAST(), 10
column lists selection, 8
computational and derived fields, 120–121
CONVERT(), 10
correlated subqueries, 29, 141–147
COUNT(), 148
data type conversion, 10
derived tables, 122–126
DISTINCT keyword, 148–149
expressions selection, 9
functions, 9
HAVING clause, 125
INSERT and, 100–101
list, 120–121
MAX(), 148
MIN(), 148
overview, 7–11
PERCENT keyword, 121
performance tips, 342–344
sales table, 149
select into/bulk copy, 65–66, 453
SET ROWCOUNT, 121
STDDEV(), 148
STDDEVP(), 148
stor_id column, 148–149
strings as arrays, 218
SUM(), 148
title_id column, 148–149
type column, 158–159
UNION ALL, 158
UPPER(), 9
VAR(), 148
variables selection, 9
VARP(), 148
vector aggregates, 147
WITH TIES option, 121
See also GROUP BY clause; HAVING clause; Joins; Predicates; SARGs (search arguments); Subqueries (subselect)
SELECT * FROM, 123–124
SELECT DISTINCT, 260
Select into/bulk copy, 65–66, 453
DELETE, 115–116
HAVING clause, 125
overview, 121–122
sets, 235–236
statistical functions, 186–189, 193
UPDATE, 111–112
SELECT...INTO, 191, 245, 287, 301
Selectivity of index, 66
Self-joins, subqueries, 143–144
Semantic optimization, 385
Semijoins, 391
Sensitive (DYNAMIC) cursors, 253–256
Sequence tree, 305
Sequences, 201–207
Sequin SQL, 3
Server connection, 2
Server execution, 306–307
SET ANSI_NULL_DFLT_ON/_OFF, 5, 81
SET ANSI_NULLS, 81–83, 164, 317, 319–320
SET ANSI_PADDING, 36–37
SET ANSI_WARNINGS, 80
SET CONCAT_NULL_YIELDS_NULL, 81
SET CURSOR_CLOSE_ON_COMMIT, 274–275
SET DEFAULT, 87
SET IDENTITY_INSERT, 99–100, 221, 258
SET IMPLICIT_TRANSACTIONS, 286, 288
SET NOCOUNT ON, 312
SET NULL, 87
Set orientation and statistical functions, 173
SET QUOTED_IDENTIFIER, 164, 317–320
SET ROWCOUNT, 121, 188–189, 236
SET TRANSACTION ISOLATION LEVEL, 62–63, 284, 289–293
SET XACT_ABORT, 288–289
Sets, 229–239
differences, 231–233
DISTINCT keyword, 232
duplicates, 233
every nth row, 238
EXCEPT keyword, 231–232
GROUP BY clause, 238
GROUP BY clause and UNION, 156, 230–231
HAVING clause and UNION, 230–231
intersections, 234–235
JOIN, 238
“multisets,” 233
ORDER BY clause, 236–237
OUTER join, 232
SELECT TOP n option, 235–236
SET ROWCOUNT, 236
subsets, 235–238
TOP n option, 235–237
UNION ALL, 231
WHERE clause, 235
WITH TIES option, 236–237
Severity values of errors, 326
single column, 224–225
Single level hierarchies, 241–242
Single-user databases, 287
Sliding aggregates, 196–197
smalldatetime types, 23
Smallmoney, 58
Snapshot (STATIC) cursors, 253–254, 256–257, 268
Sorting arrays, 221–223
Soukup, Ron
on the end game, 201
on ports, 253
SOUNDEX(), 37–46
Source code, views, 166
SPACE(), 219
sp_active_processes, 405–410
sp_add_jobserver, 453
sp_attach_db, 448
sp_configure, 272
sp_copyfile, 443–445
sp_dbbackup, 440–443
sp_dboption, 268, 272, 276, 448
sp_decodepagebin, 378–379
sp_depends, 94–95
sp_describe_cursor_columns, 279
sp_describe_cursor_tables, 279
sp_dir, 421–427
sp_displayoaerrorinfo, 479
sp_executesql, 49–54
sp_exporttable, 476–480
sp_find_root_blocker, 414–415
sp_fulltext_catalog, 468
sp_fulltext_column, 468
sp_fulltext_table, 468
sp_generate_script, 453, 456–460
sp_getSQLregistry, 443, 453, 484–486
sp_importtable, 480–483
sp_lock_verbose, 416–418
sp_make_portable, 445–448
SP_OAGetErrorInfo, 479
sp_object, 427–432
sp_pass, 410–414
sp_procoption, 330
sp_readtextfile, 453–455
sp_rebuildindexes_all, 437–440
sp_recompile, 307
sp_spaceused, 313–314
sp_table, 419–421
sp_update_stats_all, 432–435
sp_updateusage_all, 435–437
sp_usage, 309–311
SQL Server Debug Interface (SDI), 334–335
SQL Server:Databases object, 300
Standard deviation, 176
statblob column, 67
State values of errors, 326
STATIC cursors, 253–254, 256–257, 268
Static values for UPDATE, 107
Statistical functions, 173–199
#valueset, 190–191
aggregate functions, 176
CASE expressions, 174–175
CASE for horizontal aggregates, 197–199
clipping, 185–186
correlated subqueries, 188
cumulative aggregates, 195–196
DISTINCT keyword, 189
efficiency concerns, 176
extremes, 197–199
floating point rounding errors, 54–57, 174
HAVING clause and clipping, 186
histograms, 193–194
horizontal aggregates, 197–199
modes, 193
ORDER BY clause for horizontal aggregates, 198
rankings, 190–192
ROWCOUNT, 188–189
running aggregates, 195–196
SELECT TOP n option, 186–189, 193
SELECT...INTO, 191
set orientation and, 173
SET ROWCOUNT, 188–189
sliding aggregates, 196–197
standard deviation, 176
STDDEV(), 176
STDDEVP(), 176
stored procedures for, 174
stratified histograms, 194
ties, handling, 191–192
VAR(), 176
variance, 176
VARP(), 176
WITH TIES option, 193
See also Medians
STATS_DATE(), 517
Status functions, 511–513
Status routines, 405–418
@spid variable, 415
CHAR(), 410
DBCC INPUTBUFFER(), 410
DBCC OUTPUTBUFFER(), 410
DBCC PSS(), 410
EXEC(), 418
INFORMATION_SCHEMA view, 418
INSERT...EXEC, 418
OBJECT_NAME(), 418
PRINT, 410
psrvproc->>m_pwchLangBuff column, 410
psrvproc->>srvio.outbuff column, 410
sp_active_processes, 405–410
sp_find_root_blocker, 414–415
sp_lock_verbose, 416–418
sp_pass, 410–414
See also Stored procedures
Stored procedures, 279, 303–335
# and ## prefix for local/global, 91–93, 312
@@ERROR automatic variable, 327–328
@@MAX_PRECISION automatic variable, 329
@@NESTLEVEL automatic variable, 324, 328, 331
@@OPTIONS automatic variable, 324
@@PROCID automatic variable, 324
@@ROWCOUNT automatic variable, 334
@@SPID automatic variable, 324
@@TRANCOUNT automatic variable, 21, 293, 295–296, 298–300, 324
advantages of, 304
ALTER PROCEDURE, 307
ALTER TABLE...DISABLE TRIGGER, 331
ALTER TABLE...ENABLE TRIGGER, 331
automatic variables (functions), 323–324
autostart procedures, 330
base_schema_ver column, 306
comment headers, 308–309
compilation, 305–306
control-of-flow statements, 324–325
CREATE PROCEDURE, 303, 305, 307–308
CREATE TRIGGER, 330
cursor output parameters, 322
debugging, 334–335
declarative referential integrity (DRI) and triggers, 331
deleted tables and triggers, 331
in Dynamic Link Libraries (DLLs), 314–316
environment options, 311–312
environmental concerns, 317–320
ERRORLEVEL, 326
errors, 325–328
execution plans, 306–307
extended procedures, 102–103, 314–316, 503–509
faux (pseudo) procedures, 316
flow control language, 324–325
GO, 307
INSERT, 317
inserted tables and triggers, 331
INSERT...EXEC, 317
internals, 305–307
nested triggers, 331
nesting stored procedures, 328
OUTPUT keyword, 322
output parameters, 321–322
performance tips, 347–351
query tree, 305
QUOTED_IDENTIFIER, 317–320
RAISERROR, 325–328
recursion, 328–330
recursive triggers, 331
reserved words, 318–319
result codes and meanings, 322–323
RETURN, 322–323
ROLLBACK TRAN, 331
ROLLBACK TRIGGER, 331
schema_ver column, 306
sequence tree, 305
server execution, 306–307
SET NOCOUNT ON, 312
SET QUOTED_IDENTIFIER, 317–320
severity values of errors, 326
size, maximum, 308
sp_helptext, 303–304
sp_procoption, 330
sp_recompile, 307
sp_spaceused, 313–314
sp_usage, 309–311
SQL Server Debug Interface (SDI), 334–335
state values of errors, 326
for statistical functions, 174
syscomments system table, 303
system procedures, 312–314, 402–403
temporary procedures, 312
triggers, 330–334
usage information in, 309–311
USE, 312
user execution, 305
user objects, 314
varying keyword, 322
WITH ENCRYPTION option, 312, 330
WITH LOG option, 326
WITH NOWAIT option, 326
WITH RECOMPILE option, 307, 316
WITH SETERROR option, 326–327
wrapper routines, 315
xp_logevent, 328
xp_varbintohexstr, 316
See also Undocumented T-SQL
stor_id column, 148–149
stor_name column, 154
STR(), 58–59
Stratified histograms, 194
Strings, 34–54
+ operator, 35
ALTER TABLE, 52–53
ANSI_PADDING, 36–37
authors table, 51–52
char versus varchar, 35–36
CHARINDEX(), 37
concatenation, 35
cursors, 51
DECLARE, 34
DIFFERENCE(), 46–47
in Dynamic Link Libraries (DLLs), 47
EXEC(), 49–54
FORMATMESSAGE(), 519–520
global temporary tables, 52
INSERT, 52
LIKE predicate function, 48–49
local temporary tables, 52
masks, 48–49
NULL, 34
nvarchar, 52
Open Data Services (ODS), 47
padding, 36–37
PARSENAME(), 520
PATINDEX(), 48–49
RTRIM(), 35
SET ANSI_PADDING, 36–37
SOUNDEX(), 37–46
sp_executesql, 49–54
SUBSTRING(), 49
unicode, 54
xp_sprintf, 47
xp_sscanf procedure, 47–48
Strings as arrays, 213–228
DATALENGTH(), 217
elements, modifying, 219
INSERT, 214
multidimensional arrays, 214
normalization and, 213
QUOTENAME(), 218
result sets, multiple, 217
SELECT, 218
SPACE(), 219
SUBSTRING(), 215
uneven (jagged) arrays, 214–215
WHERE clause, 219
STUFF(), 64
Subqueries (subselect), 140–147
COUNT(), 147
customers table, 145–147
GROUP BY clause, 147
HAVING clause, 147
items table, 145–147
joins versus, 142–144
MAX(), 141
NOT EXISTS, 146
orders table, 145–147
price field, 143
relational division, 145–147
self-joins, 143–144
title_id column, 141–142
titles table, 141–142
WHERE clause and, 141
ytd_sales field, 143
Subsets, 235–238
SUM(), 148
Summary tables, denormalization by, 371–374
SUSER_NAME(), 513
SUSER_SNAME(), 513
Swapping column values with UPDATE, 112–113
Syntactic elements and Query Optimizer, 385
syscacheobjects table, 348–351
syscomments system table, 303
sysdepends table, 94–95
sysname, 419
System procedures, 312–314, 402–403, 503–509
T
Table aliases, 21
Table creation, 3–5
Tables as arrays, 220–228
aggregate functions, 222–223
CASE expressions, 226–227
comparing arrays, 226–228
DELETE, 221
dimensions, 221–225
GROUP BY clause, 222–223, 225–226
IDENTITY_INSERT, 221
integrity, ensuring, 225
MAX(), 225–226
MIN(), 226
ORDER BY clause for sorting tables, 221
PRIMARY KEY constraint, 225
reshaping arrays, 225–226
SET IDENTITY_INSERT, 221
single column, 224–225
sorting, 221–223
title column, 225
transposing dimensions, 223–225
UNIQUE KEY constraint, 225
UPDATE, 222–224
VIEW object, 225
WHERE clause, 220–223, 227–228
Tables for UPDATE, 107–108
Temporary
objects, 91–93
table indexes, 94
table name length, 92
TEXTVALID(), 60
Thorpe, Danny
on programming without architecture, 303
on technique and technology, 487
3GL versus SQL approach, medians, 180–181
Ties, handling, 191–192
TIL (TRANSACTION ISOLATION LEVEL), 62–63, 284, 289–293
Time gap determination, 27–31
Time series, 201–203
timestamp column, 464
Timestamps, 73–75
title column, 225
title_id column, 134–135, 141–142, 148–149
titles table, 134, 141–142, 149, 158–159
TOP n option. See SELECT TOP n option
Trace flags, undocumented, 502–503
Transact-SQL, 1–22
Alt-F1 (help facility), 3
Alt-X (run query), 2
ANSI/ISO SQL standard, 1
ANSI_NULL_DFLT_ON, 5
AS keyword, 20
atomicity of change, 21
BEGIN TRAN, 22
FROM clause, 7, 9, 15, 21, 34, 126, 137
column aliases, 20–21
column nullability, 4–5
COMMIT, 21
CREATE DATABASE, 3
CREATE TABLE, 3–5
Ctrl-E (run query), 2
Ctrl-F5 (check query for syntax errors), 2
data directory, 3
DB: combo-box, 4
DESC keyword, 20
editor selection, 2–3
F5 (run query), 2
filtering data, 11–18
FOREIGN KEY, 7
GG_TS_Log.LDF, 3
GG_TS.MDF, 3
IMPLICIT_TRANSACTIONS, 21
joins, 13–17
OSQL, 3
Query Analyzer, 2–3
save command, 21
Sequin SQL, 3
server connection, 2
table aliases, 21
table creation, 3–5
transaction log, 3
transaction management, 21–22
TRUNCATE TABLE, 7
undo command, 21–22
USE dbname, 4
See also Administrative T-SQL; Arrays; Cursors; Data Definition Language (DDL); Data Manipulation Language (DML); Data types; Filtering data; Full-text search; Functions, obscure; GROUP BY clause; HAVING clause; Hierarchies; Missing values; NULL; OLE automation; ORDER BY clause; Performance tuning; Potpourri; Runs and sequences; SELECT; Sets; Statistical functions; Stored procedures; Transactions; Undocumented T-SQL; Views; WHERE clause
TRANSACTION ISOLATION LEVEL (TIL), 62–63, 284, 289–293
Transactions, 283–301
@@ERROR automatic variable, 288–289
@@TRANCOUNT automatic variable, 21, 293, 295–296, 298–300, 324
accidental ROLLBACKs, avoiding, 297–299
ACID (atomic, consistent, isolated, durable) test, 283–284
atomicity, 284
authors table, 289
automatic transaction management, 288–289
automatic transactions, 285
avoiding, 287
BEGIN DISTRIBUTED TRANSACTION, 287
BEGIN TRAN, 285–286, 288, 293, 301
BULK INSERT, 287
command batches versus, 284–285
COMMIT TRAN, 285–286, 288, 293–295, 301
consistency, 284
database consistency checker (DBCC) commands, 299–300
DBCC LOG(), 299–300
DBCC OPENTRAN(), 299
debugging, 299–300
distributed transactions, 287
durability, 284
implicit transactions, 286, 288
invalid syntax in, 299
isolation, 284
logging, minimizing, 287
management, 21–22
nested transactions, 293–296
nonlogged operations, 285, 287, 301, 331
optimizing code, 300–301
phantom rows, 292
qty column, 291
READ COMMITTED, 290–291
read-only databases, 287
READ UNCOMMITTED, 289–290, 301
ROLLBACK TRAN, 286, 288–289, 293–299, 301
sales table, 291–292
save points, 296–297
SAVE TRAN, 296–297
SET IMPLICIT_TRANSACTIONS, 286, 288
SET TRANSACTION ISOLATION LEVEL, 62–63, 284, 289–293
SET XACT_ABORT, 288–289
single-user databases, 287
SQL Server:Databases object, 300
syntax, 293–299
triggers and, 285
TRUNCATE TABLE, 287
types of, 285–287
user-defined transactions, 286, 288
write-ahead logging, 285
WRITETEXT/UPDATETEXT, 287
XACT_ABORT, 288–289
Transposing dimensions, 223–225
Triggers
BULK INSERT and, 106
stored procedures, 330–334
transactions, 285
UPDATE and, 109–110
True, 77–78
TRUNCATE TABLE, 7, 87, 117–118, 287
trunc.log on chkpt, 443
Truth tables, 77–78
U
Underscore (_) wildcard, 12, 131–133
Undo command, 21–22
Undocumented T-SQL, 487–509
DBCC commands, 488–499
defined, 487
functions and variables, 499–502
procedures (system and extended), 503–509
trace flags, 502–503
Uneven (jagged) arrays, 214–215
Unicode, 54
UNION
GROUP BY clause and, 156, 230–231
HAVING clause and, 230–231
UNION ALL
SELECT, 158
sets, 231
UNIQUE constraint, 437
Unique index requirement, 87
UNIQUE KEY constraint, 225, 258
uniqueidentifer data type, 67–68
uniqueifier, 105
Updatable VIEWs, 166–167
UPDATE, 107–114
arrays, 222–224
BLOBs (binary large objects), 63
CASE expressions and, 109–110
column values, swapping with, 112–113
constraints and, 109–111
data types, 73–74
date functions, 32–33
deferred updates, 108
Halloween Problem, 108–109
numeric functions, 58
performance tips, 346
qty column, 108
rows affected by, limiting, 111–112
SELECT TOP n option, 111–112
static values for, 107
swapping column values with, 112–113
tables as arrays, 222–224
tables for, 107–108
TOP n option, 111–112
triggers and, 109–110
WHERE CURRENT OF clause, 113–114
UPDATE STATISTICS, 433, 517–518
UPDATETEXT, 63–65
UPDLOCK keyword, 64
UPPER(), 9
Usage information in stored procedures, 309–311
USE, 312
USE tempdb, 75
User counters, 398
User-defined transactions, 286, 288
User execution, 305
User objects, 314
USERNAME(), 512–513
V
value1 column, 260
VALUES clause, 98
varchar default, 89
Variables
selection, 9
undocumented, 502
Variance, 176
varying keyword, 322
Vector aggregates, 147
Vector medians, 183
Vertical partitioning, denormalization by, 374–380
VIEW object, 225
Views, 163–172
ANSI SQL schema VIEWs, 165
ANSI_NULLS, 164
CASE expressions, 169–170
Data Manipulation Language (DML) restrictions, 164
derived tables, 167–168
dynamic VIEWs, 168–170
encryption, 166
GETDATE(), 169
HAVING clause, 169
INFORMATION_SCHEMA view, 67, 165, 418–419, 435
ORDER BY clause, 163–164
ORDER BY clause and derived tables, 168
partitioning data using, 170–172
QUOTED-IDENTIFIER, 164
restrictions, 163–164
sales table, 169
SET ANSI_NULLS, 164
SET QUOTED_IDENTIFIER, 164
source code, getting, 166
sp_helptext, 166
TOP n option, 163–164
unusable, 94
updatable VIEWs, 166–167
WHERE clause, 169
WITH CHECK option, 167
WITH ENCRYPTION option, 166
Virtual (contrived) columns, denormalization by, 370
W
data types, 73
DELETE, 115
EXISTS predicate function, 133
SARGs (search arguments), 353, 356–357, 359, 363
sets, 235
strings as arrays, 219
subqueries (subselect), 141
tables as arrays, 220–223, 227–228
views, 169
WHERE CURRENT OF clause
DELETE, 116–117
UPDATE, 113–114
WITH CHECK option, 167
WITH ENCRYPTION option, 166, 312, 330
WITH NO_INFOMSGS option, 488
WITH NOWAIT option, 326
WITH RECOMPILE option, 307, 316
WITH SETERROR option, 326–327
WITH TIES option, 121, 193, 236–237
Wrapper routines, 315
Write-ahead logging, 285
WRITETEXT, 63–65
WRITETEXT/UPDATETEXT, 287
X
XACT_ABORT, 288–289
xp_cmdshell, 443
xp_logevent, 328
xp_sprintf, 47
xp_sscanf procedure, 47–48
xp_varbintohexstr, 316
Y
Y2K problems, 24–25
YEAR(), 26
yr column, 153
ytd_sales field, 143
Z
Zero, division by, 57–58