Index
Symbols | A | B | C | D | E | F | G | H | I | J | K | L | M | N | O | P | Q | R | S | T | U | V | W | X
Symbols
@@ERROR function, 218
@@TRANCOUNT function, 224
@batch parameter, plan guides, 74
@hints parameter, plan guides, 74
@module parameter, plan guides, 74
@name parameter, plan guides, 73
@params parameter, plan guides, 74
@stmt parameter, plan guides, 73
@type parameter, plan guides, 74
A
ACID (Atomicity, Consistency, Isolation, and Durability) properties, 443
Action class, AMO OLAP, 155
Active Server Pages (ASP), 10
Administer permission, AMO, 156
Administrative classes, AMO, 156
ADO (ActiveX Data Objects)
data access object model, 32
data access with, 10–11
ADOMD, 157–158
AdomdDataReader object, 158
ADO.NET
as connected object model, 35–36
as disconnected object model, 32–34
asynchronous processing, 184
connecting with, 6–7
data providers, 438
exercise connecting with, 14–15
exercise retrieving data with DataReaders, 40
exercise retrieving data with DataSets, 36
recommended uses as data provider, 5
selecting as data provider, 4
SqlParameter objects, 211
AdventureWorks database
installing, xxii
software requirements for Exam 70-442, xx
AFTER triggers
cascading actions and, 199
data validation and, 210
aggregate functions
built-in functions, 61
T-SQL, 435
aggregating data
built-in functions, 61
exercise de-normalizing aggregated data, 335–338
PIVOT operator, 55
UNPIVOT operator, 55
user-defined functions, 61
algorithms, for data mining, 156
alias data types, 335
ALLOW_SNAPSHOT_ISOLATION, 244
ALTER TABLE statement, 347
AMO (Analysis Management Objects), 154–157
administrative classes, 156
assemblies and namespaces, 154
compared with XMLA, 154
data mining classes, 155
fundamental objects, 154
OLAP classes, 155
overview of, 154
security classes, 156
APIs (application programming interface) ADO.NET as, 4
application domains, 430
application performance, SQLOS and, 416–425
application-side distributed transactions, in .NET Framework, 268
APPLY statements
combining subqueries with, 293
exercise using, 67
SELECT statements and, 55–57
ASP (Active Server Pages), 10
ASP.NET
asynchronous processing, 184
caching methods, 97, 99
assemblies
AMO (Analysis Management Objects), 154
CLR Host assembly permissions, 428
.NET Framework, 425
RMO (Replication Management Objects), 150
SMO (Server Management Objects), 145
asymmetric keys, 472
asynchronous processing, 184–189
Command object for accessing methods, 185
enabling with ADO.NET, 184
exercise applying callback feature, 186–189
overview of, 184
Atomicity, Consistency, Isolation, and Durability (ACID) properties, 444
Attributes, OutputCache directive, 100
authentication
CLR Host and, 427
creating endpoints and, 20
user IDs and passwords and, 9
authorization, CLR Host and, 427
Auto Stats trace event, 461
B
balanced tree indexes, 305, 464. See also clustered indexes, non-clustered indexes
baselines
creating performance baselines, 380
exercise comparing clustered indexes, nonclustered indexes, and indexed views against baseline, 330
exercise creating performance baseline for queries, 324–325
Basic authenticationz, 20
BEGIN DISTRIBUTED TRANSACTION statement, 267
BEGIN TRANSACTION statement, 244
Body, of XQuery expressions, 82
bookmark lookup, indexes, 467
bottlenecks, I/O and CPU bottlenecks, 384
B-Tree (Balanced Tree) indexes, 305, 464. See also clustered indexes, non-clustered indexes
buffer cache
defined, 410
determining object data stored in, 424
buffer pool (BPool), 447
built-in functions
aggregate functions, 61
SQL Server 2005, 61
C
C#
command line compiler, 363
options for writing code for SQL Server, 360
C++, used with ODBC or OLE DB, 10
Cache class
expiration methods, 108
System.Web.Caching namespace, 102
caching, 99–114
adding items to cache, 102
custom, 102–105
exercise evaluating effects of, 112–114
exercise using OutputCache directive, 109–112
hardware read cache, 448
hardware write cache, 448
output caching, 99
overview of, 99
page-level output caching, 99
Procedure Cache Manager, 455
query notifications and, 105–108
references, 141
refresh strategy and, 108
removing items from cache, 103
storing DataSets in cache, 103
user control-level output caching, 100–101
call back feature, asynchronous processing, 186–189
CAS (Code Access Security)
CLR (Common Language Runtime) and, 429
benefits of procedures vs. XPs, 433
CLR Host and, 428
CASCADE action, foreign key constraints, 198
CASE functions, as alternatives to cursors, 118
case scenarios
in Exam 70-442, xxiii
structure of, xxiv
case sensitivity, XML and XQuery, 83
catalog views
function catalog views in partitions, 348
object catalog views in partitions, 348
sys.partitions, 346
catch blocks. See try/catch blocks
CD, using CD accompanying this book, xxiv
Cellset object, 158
central processing units. See CPUs (central processing units)
certificates, 471–472
encryption key types, 472
information contained in, 471
trust and, 472
certification, Microsoft Certified Professional, xxvi
Certification Mode, of practice tests, xxv
check constraints, 199–208
advantages of, 199
disadvantages of, 200
execution of, 201
extending with user-defined functions, 200–202
for improving query performance, 206–208
overview of, 199
performance impact of, 202
checkpoints
flushing data pages to disk, 451
storage engine, 449
class identifiers (CLSIDs), OLE DB 10
classes
AMO class library, 154–157
RMO class library, 150
SQL Server Profiler event classes, 372
CLI (Common Language Infrastructure), 426. See also CLR (Common Language Runtime)
clients
client-side precautions for preventing SQL injection attacks, 212
connections for heterogeneous client computers, 19–22
protocol configuration, 4
CLR (Common Language Runtime), 426–444
application domains supported, 430
assembly permissions, 429
CAS (Code Access Security), 430
data sources as factor in when to use, 437
database object types, 430
design goals for, 426
enabling, 361
examples of code, 434
HPAs (Host Protection Attributes), 429
memory usage statistics, 441–443
new hardware trends, 406
operation with side effects, 438
overview of, 425
reliability of, 426
scalability of, 426
security of, 427
services, 429
table-valued functions, 297
transaction management, 438–441
T-SQL compared with, 430–434
type-safe code, 429
typing and, 438
usability of code, 437
when to use code, 437
XPs compared with procedures, 433
CLSIDs (class identifiers), OLE DB 10
clustered indexes
appropriate uses of, 312
compared with non-clustered, 305
covered indexes and, 305
created by primary keys, 196
exercise comparing with non-clustered indexes, indexed views, and non-indexed baseline, 330
exercise optimizing query, 325–327
index performance and, 312–313
organization of, 463
scan option, 467
structures of, 464
COALESCE function, SELECT statements, 59
code
determining which tier to use for efficient operation, 360
exercise creating user-defined function, 367–370
moving code to different tiers and, 360–363
reuse in multi-tier architectures, 360
Code Access Security. See CAS (Code Access Security)
COLLATE operator, in filtered queries and, 291
collation, computed columns and, 318
columns
clustered indexes and, 313
computed columns in index performance, 316–320
data type size when indexing, 335
in covered indexes, 305
index levels of included columns, 310
index levels of non-included columns, 309
index performance and, 307–312
index size matrix comparing included and non-included columns, 309
maximum columns in an index, 307
used for filtering, grouping, or sorting in index key, 308
command line compilers, 363
Command object, asynchronous processing, 185
Common Language Infrastructure (CLI), 426. See also CLR (Common Language Runtime)
Common Language Runtime. See CLR (Common Language Runtime)
common table expressions. See CTEs (common table expressions)
Common Type System (CTS), 426
compatibility level, database settings, 57
compilers
command line, 363
recompilation, 460
computed columns
applied to indexing, 316
collations and, 318
foreign key constraints on, 197
index performance, 316–320
primary and unique constraints on, 196
concurrency, 234–236
cursors, 134
dirty reads, 235
locks for solving concurrency problems, 235
lost updates, 234
non-repeatable reads, 235
overview of, 234
phantom reads, 235
READ_ONLY concurrency option, 120
update conflicts and, 246–250
connected models, ADO.NET, 35-36
connections
exercise consuming HTTP endpoint, 28–30
exercise creating HTTP endpoint, 27–28
for heterogeneous client computers, 19–22
for HTTP Web services, 22–24
for SQL Server named instances, 25–26
constraints
check constraints, 199–208
for implementing data integrity, 195
foreign key constraints, 196–199
primary key and unique key constraints, 195
types of, 195
CONTAINS predicate, in full-text queries, 63
CONTAINS TABLE predicate, in full-text queries, 64
correlated subqueries, 292–295
costs
index usage, 322
query costs, 285, 322
counters, System Monitor
adding, 378
defaults, 378
diagnosis with, 382
performance baselines with, 381
covered indexes
compared with non-covered, 307
exercise using non-clustered index for optimizing query, 327
index performance and, 305–307
performance benefits of, 306
CPUs (central processing units)
bottlenecks, 384
configuring processor affinity, 413
expanding limits for 32-bit CPUs, 407
multiple threads, 407
NUMA and, 408
query costs and, 285
CREATE VIEW statement, 354
CREATE XML SCHEMA COLLECTION, 80
CROSS APPLY operators, in SELECT statements, 56
csc.exe, C# compiler, 363
CTEs (common table expressions)
queries placed in, 294
recursive queries and, 118
CTS (Common Type System), 426
Cube
AMO objects, 154
AMO OLAP class, 155
CursorRecompile trace event, 461
cursors, 115–140
alternatives to, 116
building with dynamic SQL, 131
CASE function as alternative to, 118
case scenario evaluating cursor performance, 140
CLOSE and DEALLOCATE, 121
comparing execution time of cursor with alternative methods, 122
concurrency, 134
CursorRecompile trace event, 461
dynamic management functions, 123
efficient use of, 129
evaluating efficiency of, 133
evaluating use of, 122
exercise comparing with alternatives, 125–127
exercise creating, 124–125
exercise creating scrollable cursor, 136
exercise examining, 138
FORWARD_ONLY or FAST_FORWARD types, 121
function of, 97
MARS compared with, 170
minimizing blocking, 133
minimizing or eliminating, 135
overview of, 115
performance maximization, 120
processing on row-by-row basis with, 130
query performance and, 297
READ_ONLY concurrency option, 120
recursive queries as alternative to, 118–119
reducing amount of data in, 120
references, 141
scrollable, 129
SELECT statements as alternative to, 116–117
server memory and, 133
Transact-SQL code for declaring, 115
types of, 132
when to use, 129
WHILE loops as alternative to, 117
Custom Mode, of practice tests, xxv
CXPacket waits, 423
D
DAO (Data Access Objects), 32
data access
ADO (ActiveX Data Objects), 10–11
ADO.NET, 6–7
data providers, 4–5
exercise connecting using SNAC (SQL Native Client), 16–17
exercise connecting with, 14–15
MDAC (Microsoft Data Access Components), 7–11
network protocols, 2–4
ODBC (Open Database Connectivity), 8–9
OLE DB (Object Linking and Embedding Database), 10
password policies, 12–13
procedures compared with XPs, 433
SNAC (SQL Native Client), 12
stored procedures for, 209
data access object models, 32–42
ADO (ActiveX Data Objects), 32
ADO.NET as connected model, 35–36
ADO.NET as disconnected model, 32–34
exercise retrieving data with DataReader, 40–42
exercise retrieving data with DataSet, 36–39
data archiving, 349
data integrity
case scenario validating, 231
declarative. See declarative data integrity
exercise applying data validation rules to tables, 214
procedural. See procedural data integrity
data mining
AMO classes for, 155
AMO for controlling/managing, 154
exercise creating data mining structure, 166–168
references, 154, 190
SQL Server Business Intelligence Development Studio, 155
data partitioning. See partitioning
data providers
ADO.NET, 6, 438
available for linked servers, 51
selecting, 4–5
Data Source Configuration Wizard, 33
Data Source Name (DSN), 8
data sources
accessing multiple, 51–54
connection objects, 6
creating, 158
DataSets from, 34
exercise creating data mining structure, 166–168
heterogeneous, 51
when to use CLR and, 437
data stewardship, 360
data storage optimization, 332–338
data types, 334–335
de-normalizing, 333–334
exercise de-normalizing an aggregation, 335–338
overview of, 332
row width, 332–333
data types, 334–335
alias, 335
columns using large data types, 308
consistent use of, 335
overview of, 334
page reads and, 286
procedures compared with XPs, 433
restrictive data types for preventing SQL injection attacks, 211
selecting appropriate, 335
smallint, 62
variable-length, 332
xml data type, 80
data validation. See data integrity
data warehousing databases, 358
database engine, 451–454
flushing data pages to disk, 451
flushing log pages to disk, 452
overview of, 451
read-ahead, 453–454
database optimization
data storage. See data storage optimization
indexes. See index performance
queries. See query performance
databases
compatibility level settings, 57
exercise creating database with SMO, 160–166
datagrid controls, 99
DataReaders
compared with DataSets, 35
exercise retrieving data with, 40–42
in ADO.NET connected object model, 35
using, 35
DataSet Designer, 33
DataSets
ADOMD object, 158
benefits of, 33
compared with DataReaders, 35
creating, 33
data sources for, 34
exercise retrieving data, 36–39
making changes to, 34
stored in cache, 103
supporting ADO.NET disconnected model, 33
DataSource. See data sources
DataSourceView
creating, 158
exercise creating data mining structure, 166–168
datetime values, cache expiration and, 108
DDL (Data Definition Language), 145
deadlocks
causes of, 240
exercise protecting against, 276
minimizing, 266
DEALLOCATE keyword, cursors and, 121
declarative data integrity, 195–208
check and foreign keys for improving query performance, 206–208
check constraints, 199
defined, 195
exercise comparing with procedural data
integrity, 213
extending check constraints with userdefined functions, 200–202
foreign key constraints, 196–199
foreign keys used with subqueries, 205
overview of, 195
primary and unique key constraints, 195
user-defined functions used with subqueries, 202–204
DELETE statements
OUTPUT keyword, 261
query hints and, 71
steps in processing, 457
subqueries, 50
within snapshot transaction, 248
de-normalization. See also normalization
data storage optimization and, 333–334
exercise de-normalizing an aggregation, 335–338
DENSE RANK, 58
derived tables
as alternative to cursors, 116
queries placed in, 294
developers, cursor use by, 129
Digest authentication, 20
Dimension
AMO objects, 154
AMO OLAP class, 155
dirty reads
concurrency problems, 235
read committed isolation level for protecting against, 236
read uncommitted isolation level and, 238
disconnected object models, ADO.NET, 32–34
disk space
optimizing data storage and, 332
removing seldom-used indexes and, 332
distributed partition views (DPVs), 353–355
distributed queries, linked servers and, 51–54
Distributed Transaction Coordinator (DTC), 267
distributed transactions, 267–269
Distributor object, RMO, 149, 151
DLLs (dynamic-link libraries), 2
DML
modify method, 84
procedural data integrity and, 195
DML triggers
AFTER triggers and cascading actions, 199
implementing procedural data integrity and, 209
DMVs (Dynamic Management Views)
analyzing database application performance, 380
diagnosing problems with, 383
measuring memory consumption, 441
querying SQLOS wait types, 417
viewing SQLOS memory nodes, 410
DPVs (distributed partition views), 353–355
creating, 353
goal of, 353
DROP INDEX statement, 346
DSN (Data Source Name), 8
DTC (Distributed Transaction Coordinator), 267
Duration attribute, OutputCache directive, 99
dynamic cursors, 132
dynamic-link libraries (DLLs), 2
dynamic management functions, cursors, 123
Dynamic Management Views. See DMVs (Dynamic Management Views)
dynamic SQL
building cursors, 131
preventing SQL injection attacks and, 212
E
eager writes
flushing data pages to disk, 451
storage engine, 450
eBook, electronic version of this book, xxiv
ELEMENTS keyword, XML RAW and, 86
encryption, 471–475
certificates, 471–472
hardware trends, 406
keys types, 472
overview of, 471
SSL configuration, 473–475
endpoints, TCP/IP, 414
Enterprise Manager, 347
error handling, 218–231
@@ERROR function, 218
@@TRANCOUNT function, 224
exercise using a stored procedure for
implementing try/catch blocks, 228–231
localized user-defined messages, 226
overview of, 218
techniques, 218
try/catch blocks, 221–224
user-defined messages, 225
writing error messages to Event Log, 227
XACT_ABORT, 219–221
XACT_STATE ( ), 224
error messages
creating localized user-defined, 226
creating user-defined, 225
creating, modifying, deleting, 225
storing, 225
writing to event logs, 227
error probability, data validation and, 209
estimated query cost, 285
evaluation editions, of software, xxvii
Event Logs, 227
Event Viewer, 227
events
selected events for capture, 376
SQL Server Profiler, 372
Exam 70-442
case scenarios in, xxiii
hardware requirements, xix
labs, xxiv
practice test, xxv
Preparation Guide, xix
software requirements, xx
EXCEPT operators, in SELECT statements, 57
exclusive locks (X-locks), 236, 260, 265
ExecuteAndSend method, 362
execution context, as component of query engine, 458
execution order, query performance, 286–289
execution plan
correlated subqueries, 295
for SARG filtered query, 290
graphical execution plan for query optimization, 289
query performance and, 70
execution time
cursors, 122
query performance, 286
query performance metrics, 294
exist method, XQuery, 83
EXISTS operator, correlated subqueries and, 292
EXPLICIT mode, FOR XML clause, 86
extents, storage engine, 447
EXTERNAL_ACCESS assemblies, 428
F
FAST_FORWARD cursors
cursor types, 121
selecting, 132
when to use, 133
federations of servers, 349–352
DDR (Data-Dependent Routing), 355–356
DPV (Distributed Partition View), 353–355
linked servers, 349–352
SODA (Service-Oriented Database Architecture), 357–358
targeting multiple servers, 349
fetch options, scrollable cursors, 129
FETCH statements, cursors and query performance and, 297
file DSN, 8
file groups
partition schemes for assigning to partitions, 346
partitioning and, 345
filters, SARG, 290
FOR XML clauses
exercise generating RAW XML data, 92
mode, 87
modes, 86
FORCE ORDER, query optimizer, 78
Forced Unit Access (FUA), 447
foreign keys
improving query performance, 206–208
in implementing declarative integrity, 196–199
join operations and, 49
used with subqueries, 205
FORWARD_ONLY cursors
cursor types, 121
selecting, 132
when to use, 133
fragment caching. See user control-level output caching
FREETEXT predicate, in full-text queries, 64
FROM clauses
APPLY operators used in, 55
FORCE ORDER option, 78
full-text query predicates and, 63
in SELECT statements, 455
PIVOT operators used with, 54
FUA (Forced Unit Access), 447
full-text indexes, 468
full-text queries, 63–64
function catalog views, 348
functions, 61–63
built-in functions in SQL Server 2005, 61
creating partition functions, 345
exercise creating partition function, 364
user-defined, 61
G
GRANT command, 415
graphical execution plan, 289
GROUP BY clauses, 465
H
hardware
read cache and write cache, 449
requirements for Exam 70-442, xix
scatter-gather capabilities, 449
hardware trends
CPUs, 407
new trends in hardware manufacture, 406
NUMA, 407
software design and, 408
HAVING clauses
function in SELECT statements, 456
subqueries and, 50
heaps, in non-clustered indexes, 305
heterogeneous data sources, linked servers, 51
HOLDLOCK, 260
horizontal partitioning
DDR and, 355
DPV and, 353
overview of, 344
HPAs (Host Protection Attributes), 429
HTTP Web services, connections for, 22–24
HTTP endpoints
authentication options, 20
creating for client connections, 19
exercise consuming HTTP endpoint, 28–30
exercise creating HTTP endpoint, 27–28
SMO for accessing, 145
I
I/O (input/output)
bottlenecks, 384
multichannel support in SQL Server 2005 Storage Engine, 445
NUMA affecting I/O performance, 411
query costs and, 285
Torn I/O, 445
IAM (Index Allocation Map), 464
IDE (Integrated Drive Electronics), 447
IIS (Internet Information Services), software requirements for Exam 70-442, xxi
included column feature, indexes, 466
Index Allocation Map (IAM), 464
index depth, 307–312
index keys
columns in, 308
defined, 196
updating, 313
index performance, 305–331
analyzing index usage, 322–323
clustered indexes, 312–313
columns and index depth and, 307–312
computed columns, 316–320
covered indexes, 305–307
exercise comparing clustered indexes, nonclustered indexes, and indexed views against baseline, 330
exercise creating performance baseline for queries, 324–325
exercise using clustered index for optimizing query, 325–327
exercise using indexed view for optimizing query, 328–330
indexed views and, 320–322
maximum columns in indexes, 307
non-clustered index for optimizing query, 327–328
overview of, 305
read performance vs. write performance, 313–316
size matrix for included and non-included columns, 309
indexed views
creating, 320
exercise optimizing query, 328–330
index performance and, 320–322
query cost and, 322
verifying use of, 330
indexes, 462–470
associating indexes with partition schemes, 346
clustered indexes created by primary keys, 196
clustered-index structures, 464
foreign keys and, 197
full-text indexes, 468
full-text queries using, 63
hardware trends, 406
heap structures, 464
included columns indexes, 466
index scan, 75
non-clustered index structures, 465
non-clustered indexes created with unique keys, 195
optimizing index structures, 467
optimizing structures of, 467
organization of, 463
overview of, 462
primary XML indexes, 469
secondary XML indexes, 470
SQL Server 2005 Enterprise Edition supporting parallel operations, 422
types of, 462
XML indexes, 469
inner joins
overview of, 50
query optimization and, 291
ranking functions and, 64
input/output. See I/O (input/output)
INSERT statements
OUTPUT keyword, 261
query hints and, 71
steps in processing, 457
subqueries, 50
usability of code and, 437
INSTEAD OF triggers, DML, 210
Integrated authentication method, endpoints and, 20
Integrated Drive Electronics (IDE), 448
INTERCEPT operators, in SELECT statements, 57
Internet Explorer, software requirements for Exam 70-442, xxi
Internet Information Services (IIS), software requirements for Exam 70-442, xxi
Interoperability, multi-tier architectures and, 360
IS_MEMBER function, verifying security settings in stored procedures, 210
ISNULL function, in SELECT statements, 60
isolation levels, transactions
excessive blocking and, 384
exercise acquiring locks using read committed snapshot, 256–258
exercise applying default isolation level, 269–273
exercise applying locking hints, 273–276
exercise using read committed, 253–256
extensive locking problems, 252
list of, 234
locking hints, 260
locks and, 236
minimizing deadlocks, 266
OUTPUT keyword for optimizing locking, 261, 266
read committed, 236–238, 250–252
read uncommitted, 238–239
repeatable read, 240–241
serializable, 242–243
snapshots, 244–250
J
J#, options for writing code for SQL Server, 360
JIT (Just In Time) compiler
.NET Framework, 425
type safety, verifying, 429
JOIN operators
exercise using, 65
in SELECT statements, 49
types of, 50
joins
optimizing query performance, 291
overview of, 50
Jscript, ADO(ActiveX Data Objects) used with, 10
K
Kerberos authentication, endpoints, 20
keyset-driven cursors
cursor types, 132
when to use, 133
KPI (Key Performance Indicator), 155
L
large object data types. See LOBs (large objects)
latches, storage engine, 449
lazy writer
flushing data pages to disk, 451
procedure cache and, 459
storage engine, 449
LEFT joins, 50
lightweight pooling, 407
LIKE operators, full-text searches compared with, 63
LIKE predicate, T-SQL (Transact-SQL), 468
linked servers
creating, 53
data providers available for, 51
distributed queries and, 51–54
loopback linked servers, 354
scalability, 349–352
SQL Server 2005, 25
Linked Servers node, Microsoft SQL Server Management Studio, 53
Linux clients, 19
load balancing, storage engine, 445
LOBs (large objects)
allocation units in heaps, 464
page reads and, 286
locking hints
applying, 265
exercise applying, 273–276
list of commonly used, 260
locks
case scenario for optimizing locking, 280
exercise acquiring locks using read committed snapshot isolation level, 256–258
exercise applying locking hints, 273–276
exercise using stored procedure to protect against deadlocks and phantom reads, 276–280
extensive locking problems, 252
locking hints, 260
minimizing deadlocks, 266
OUTPUT keyword for optimizing locking, 261, 266
schema stability locks (Sch-S), 239
SET _TIMEOUT statement, 236
solving concurrency problems, 235
logical reads, page reads, 286
logon passwords, 12
logon credentials, for connecting to server, 148
Log Sequence Number (LSN), 449
logs
flushing log pages to disk, 452
log parity in SQL Server 2005 Storage Engine, 446
writing error messages to event logs, 227
loopback connections, data access and, 433
loopback linked servers, not allowing, 354
lost updates
concurrency problems, 234
read uncommitted isolation level and, 238
LSN (Log Sequence Number), 448
LTRIM function, 468
M
managed code
implementing procedural data integrity, 208
reliability and, 433
MARS (Multiple Active Result Sets), 170–183
compared with cursors, 170
enabled for use with SNAC ODBC provider, 171
enabled for use with SqlClient .NET provider, 172
enabling for use with SNAC OLEDB provider, 171
exercise comparing query execution, 175–183
overview of, 5, 170
transactions and, 173
MCP (Microsoft Certified Professional), xxvi
MDAC (Microsoft Data Access Components), xx
ADO (ActiveX Data Objects), 10–11
as data provider, 4
ODBC (Open Database Connectivity), 8–9
OLE DB (Object Linking and Embedding Database), 10
recommended use as data provider, 5
software requirements for Exam 70-442, xxi
MDX (Multidimensional Expression), 155
MeasureGroup class, AMO OLAP, 155
memory
application performance issues, 385
expanding limits for 32-bit CPUs, 407
memory use statistics, 441–443
optimizing data storage and, 332
memory nodes, SQLOS, 410
merge replication, 359
metrics
for analyzing query performance, 285
page reads, 285
query cost, 285
query execution time, 286, 294
Microsoft Certified Professional (MCP), xxvii
Microsoft Certified IT Professional (MCITP). See Exam 70-442
Microsoft Data Access Components. See MDAC (Microsoft Data Access Components)
Microsoft Developer Network. See MSDN (Microsoft Developer Network)
Microsoft Full-Test Engine for SQL Server (MSFTESQL), 63
Microsoft Press, technical support options, xxvii
Microsoft SQL Server Management Studio. See SQL Server Management Studio
Microsoft Visual Studio 2005. See Visual Studio 2005
Microsoft Web Application Stress Tool
installing, xxiii
software requirements for Exam 70-442, xx
Microsoft.SqlServer.ConnectionInfo assembly, 146
Microsoft.SqlServer.RegSvrEnum assembly, 146
Microsoft.SqlServer.Replication.dll assembly, 150
Microsoft.SqlServer.Rmo.dll assembly, 150
Microsoft.SqlServer.ServiceBrokerEnum assembly, 146
Microsoft.SqlServer.Smo assembly, 146
Microsoft.SqlServer.SmoEnum assembly, 146
Microsoft.SqlServer.SqlEnum assembly, 146
Microsoft.SqlServer.WmiEnum assembly, 146
MiningStructures, AMO, 154
mirroring, storage engine and, 446
mode, FOR XML clause, 86–88
modify method, XML DML statements, 84
monitoring memory use, 441
msdb database, 225
MSDN (Microsoft Developer Network)
query hints, 73
references for RMO, 153
MSFTESQL (Microsoft Full-Test Engine for SQL Server), 63
Multidimensional Expression (MDX), 155
Multiple Active Result Sets. See MARS (Multiple Active Result Sets)
N
named instances, SQL Server, 25–26
named pipes, 3
namespaces
AMO (Analysis Management Objects), 154
data source connection objects and, 6
.NET Framework, 425
RMO (Replication Management Objects), 150
SMO (Server Management Objects), 147
Native XML Web Services, 357
network libraries, installed as DLL files, 2
network protocols, 2–4
New Query window, SQL Management Studio, 54
NO ACTION, foreign key constraints, 197
nodes method, XQuery methods, 84
non-clustered indexes
columns and index depth and, 307–312
compared with clustered, 305
covered indexes, 305–307
created with unique keys, 196
exercise comparing with clustered indexes, indexed views, and non-indexed baseline, 330
exercise optimizing query, 327–328
non-key columns in, 466
organization of, 463
structures of, 465
non-repeatable reads, concurrency problems, 235
Non-Uniform Memory Architecture. See NUMA (Non-Uniform Memory Architecture)
normalization
de-normalizing an aggregation, 335
de-normalizing databases, 333
DML triggers for controlling denormalized data, 210
of data, 49
Notification Services, 357
NOWAIT, locking hints, 261
NTILE, ranking function, 58
NTLM authentication, endpoints, 20
NULL values
check constraints and, 200
empty strings contrasted with, 60
replacing with specific values, 60
NUMA (Non-Uniform Memory Architecture)
architecture of, 408
configuring for more than 32 processors, 412
configuring soft-NUMA support, 411–413
hardware trends, 407
memory nodes, 410
NUMBER, ranking function, 58
nvarchar data type, 335
O
object catalog views, 348
object IDs, in storage engine, 447
Object Linking and Embedding Database. See OLE DB (Object Linking and Embedding
Database)
object-oriented programming, 133. See also Transact-SQL objects
AMO fundamental objects, 154
Auto Stats trace event, 461
core objects in AMO, 154
data sources connection objects and, 6
in SQL Sever object model, 145
ODBC (Open Database Connectivity)
data access methods, 8–9
MARS enabled for use with SNAC ODBC provider, 171
OLAP (Online Analytical Processing)
AMO classes for OLAP processing, 155
cubes and dimensions and, 154
OLE DB (Object Linking and Embedding Database)
accessing multiple data sources, 51
data access with, 10
MARS enabled for use with SNAC ODBC provider, 171
Online Analytical Processing. See OLAP (Online Analytical Processing)
Open Database Connectivity. See ODBC (Open Database Connectivity)
OPENDATASOURCE function, 352
OPTIMISTIC, concurrency options, 134
optimizing data storage. See data storage optimization
optimizing indexes. See index performance
optimizing queries. See query performance
OPTION clause, query hints and, 71
ORDER BY clauses
query execution order and, 288
result sets of SELECT statements and, 455
orphaned connections, application performance and, 385
OUTER APPLY operator, in SELECT statements, 56
outer joins, 291
output caching
exercise using OutputCache directive, 109–112
overview of, 99
page-level, 99
user control-level, 100–101
OutputCache directive
attributes, 100
exercise using, 109–112
page-level caching and, 99
P
page reads, query performance, 285
page-level output caching, 99
pages, flushing data pages to disk, 451
parameters, XQuery, 84–85
partition functions
creating, 345
defining range of records included in a partition, 346
partitioning, 344–348
exercise creating partition function, 364
function catalog views, 348
functions of, 345
object catalog views, 348
overview of, 344
schemes, 346
vertical and horizontal types, 344
passwords
authentication and, 9
policies for data access, 12–13
peer-to-peer replication, 359–360
effective use of, 360
overview of, 359
performance baselines
creating, 380
exercise creating for queries, 324–325
Performance Monitor (Perf Mon). See System Monitor
performance, 372–391
case scenario diagnosing performance, 391
case scenario evaluating cursor performance, 140
check constraints and, 202
common issues, 384
DMV (dynamic management views) for analyzing, 380
excessive blocking, 384
exercise using SQL Server Profiler, 386–390
I/O and CPU bottlenecks, 384
inefficient or slow-running queries, 384
maximizing cursor performance, 120
limited space for tempdb, 385
memory issues, 385
orphaned connections, 385
overview of, 372
performance baselines, 380
read uncommitted isolation level and, 238
SQL Server Profiler for analyzing, 372–377
System Monitor for analyzing, 377–379
trace events, 461
permissions
SSAS security, 156
T-SQL for verifying, 210
Perspective class, AMO OLAP, 155
phantom reads
concurrency problems, 235
exercise protecting against, 273, 276
repeatable read isolation and, 240
serializable isolation level and, 242
PIVOT operators, in SELECT statements, 54
plan guides
case scenario creating plan guide, 93
exercise creating, 78
query hints, 73
Point in Time, storage engine, 444
policies, password, 12–13
port affinity, TCP/IP, 414
ports
configuring TCP/IP port affinity, 414
SQL Server 2005 configured for ports, 416
practice tests
Exam 70-442, xxiv
how to use, xxv
installing, xxv
optional modes, xxv
uninstalling, xxvi
Preparation Guide, for Exam 70-442, xix
primary keys
declarative data integrity and, 195
DROP INDEX statement and, 347
join operations and, 49
primary XML indexes, 469
ProactiveCaching class, AMO OLAP, 155
procedural data integrity, 208–216
data validation in stored procedures, 209
defined, 195
DML triggers and, 209
exercise comparing with declarative data integrity, 213
overview of, 208
SQL injection attacks and, 211–213
T-SQL for verifying permissions, 210
procedure cache
as a memory pool, 458
query engine and, 459
Procedure Cache Manager, 455
Process, AMO permissions, 156
processor affinity, 413
Prolog, XQuery namespace declaration, 81
providers. See data providers
proxy code, WSDL document for generating, 23
Publisher object, RMO 149, 151
Q
queries, 47–96
check and foreign keys for improving query performance, 206–208
distributed queries, 51–54
enabling ad hoc queries, 352
exercise comparing query execution with/without MARS, 175–183
foreign keys used with subqueries, 205
full-text queries, 63–64
inefficient or slow-running, 384
MARS (Multiple Active Result Sets). See MARS (Multiple Active Result Sets)
querying data with ADOMD.NET, 157–158
ranking functions, 58
reference materials, 95
results with _NUMBER function, 59
SELECT statements. See SELECT statements
Transact-SQL and, 364
user-defined functions used with subqueries, 202–204
queries, XML data, 80–93
exercise generating XML, 92
exercise writing XQuery expression, 91
overview, 80
parameters, 84–85
returning data as XML, 86–89
updating data using XML, 89
XQuery, 81–84
query cost
indexed views and, 322
metrics for analyzing query performance, 285
query engine, 454–462
components, 454, 455
execution context, 458
hardware trends, 406
overview of, 454
procedure cache, 459–462
processing, 455–458
query plan, 458
steps in operation of, 457
query hints
for optimizing performance, 71–73
plan guides, 73
query notifications
benefits of, 357
caching and, 105–108
query optimizer
benefits of, 457
check and foreign key constraints for improving query performance, 206
cost-basis of, 456
DML triggers and, 210
execution order and, 286
FORCE ORDER, 78
in SQL Server 2005 query engine, 455
query plan as output, 455
reliance on distribution statistics, 457
searchable arguments and, 74
query performance, 70–79, 285–304. See also index performance
case scenario creating plan guide, 93
correlated and uncorrelated subqueries, 292–294
costs, 285
covered indexes and, 306
cursors and, 297
execution order, 286–289
execution plan and, 70
execution time, 286
exercise creating performance baseline, 324–325
exercise creating plan guide, 78
exercise optimizing example query, 302–304
exercise rewriting query, 76
exercise testing large result set, 300–302
exercise testing small result set, 298–300
exercise using clustered index for optimizing query, 325–327
exercise using indexed view for optimizing query, 328–330
exercise using non-clustered index for optimizing query, 327–328
graphical execution plan for optimizing, 289
joins for optimizing, 291
overview of, 70, 285
page reads, 285
plan guides for injecting query hints, 73
query hints for optimizing, 71–73
SARGs (search arguments) for optimizing, 290
scalar user-defined functions (UDFs), 294–296
scenario optimizing, 338
searchable arguments, 74–76
table-valued UDFs, 296
query plan
as component of query engine, 458
as output of query optimizer, 455
caching, 462
condition requiring new plan, 459
optimizing index structures, 467
read-ahead contrasted with parallel query plans, 454
R
RAISERROR statement, 222
ranking functions, queries, 58
RAW mode
exercise generating XML, 92
FOR XML clause, 86
RDOs (Remote Data Objects), 32
Read, AMO permissions, 156
read committed isolation level
exercise acquiring locks using, 256–258
exercise using, 253–256
locking hints, 260
transactions, 236–238
read committed snapshot isolation level, 250–252
read locks, concurrency problems, 235
read performance, vs. write performance in indexes, 313–316
read uncommitted isolation level, 238–239
READ_ONLY, cursor concurrency options, 120, 134
read-ahead
parallel query plans contrasted to, 454
SQL Server 2005 database engine, 453–454
ReadDefinition permissions, AMO, 156
READPAST, locking hints, 260
recordset objects, 11
recursive queries, 118–119
refresh strategy, caching and, 108
relational databases, querying. See queries Remote Data Objects (RDOs), 32
repeatable read isolation level, 240–241
replication. See also RMO (Replication Management Objects)
defined, 149
peer-to-peer, 359
results sets, from queries, 57
RIGHT joins, 50
RMO (Replication Management Objects), 149–153
assemblies, 150
class library, 150
namespace, 150
overview of, 149
publisher and distributor, specifying, 151
roles, SSAS security, 156
ROW_NUMBER function, 293
rows
covered indexes compared with noncovered, 307
optimizing row width in data storage, 332–333
performance benefits of covered indexes, 306
SARG filter for reducing rows returned by query, 290
RTRIM function, 468
rules
applying data validation rules to tables, 214
for implementing data integrity, 195
for implementing foreign key constraints, 196
S
SANs (storage area networks)
I/O related problems, 411
scalable shared databases accessed over, 359
SARGs (searchable arguments)
query performance, 74–76
optimizing query performance, 290
scalability, 343–371
CLR (Common Language Runtime), 426
DDR (Data-Dependent Routing), 355–356
DPV (distributed partition view), 353–355
exercise creating partition function, 364
exercise creating user-defined function, 367–370
linked servers and, 349–352
moving code to different tiers and, 360–363
NUMA and, 408
overview of, 343
peer-to-peer replication, 359–360
procedures compared with XPs, 433
scalable shared databases, 358
server federations and, 349–352
SODA (Service-Oriented Database Architecture), 357–358
techniques for, 343
Transact-SQL compared with SQLCLR, 364
scalable shared databases, 358
scalar functions
check constraints and, 200
defined, 62
query performance and, 294–296
ScalarMiningStructureColumn, AMO data mining class, 155
scan option, clustered indexes, 467
scans, SARG, 290
scatter-gather, hardware capabilities for, 449
schedulers, SQLOS, 414–416
schema stability locks (Sch-S), 239
schemes, partition, 346
scripting languages
ODBC not used with, 9
used with, 11
SCROLL_LOCKS, concurrency options, 134
scrollable cursors
exercise creating, 136
fetch options, 129
overview of, 115, 129
searchable arguments. See SARGs (searchable arguments)
secondary XML indexes, 470
Secure Sockets Layer. See SSL (Secure Sockets Layer)
security
CLR (Common Language Runtime), 427
AMO classes, 156
procedures compared with XPs, 433
protocols for securing replication topology, 153
seek, SARGs, 290
SELECT statements, 49–69
APPLY operators, 55–57
as alternative to cursors, 116–117, 133
COALESCE function, 59
correlated subqueries and, 292
cursors representing rows returned from, 115
EXCEPT and INTERSECT operators, 57
execution order of, 286
exercise using APPLY operator, 67
exercise using JOIN operator, 65
full-text queries and, 64
functions and, 61–63
ISNULL functions, 60
JOIN operators, 49
linked servers for accessing multiple data sources, 51–54
ORDER BY clause in, 288
PIVOT/UNPIVOT operators, 54
query engine and, 455
query hints and, 71
ranking functions and, 58
steps in processing, 457
subqueries, 50
syntax, 49
usability of code and, 437
what defined by, 455
Send method, CLR, 362
SendResultsEnd method, CLR, 363
SendResultsRow method, CLR, 363
SendResultsStart method, CLR, 362
serializable isolation level, 242–243
serializable locking hint, protecting against phantom reads, 273
Server Management Objects. See SMO (Server Management Objects)
Server object, SMO, 145
server processor identifier (SPID), 413
servers
cursors as server-side objects, 115
cursor demands on server memory, 133
distributed transactions in T-SQL, 267
targeting multiple servers to accommodate high demand, 349
Service Broker
asynchronous processing and, 184
services provided by, 357
SMO assembly supporting, 146
Service-Oriented Architecture (SOA), 357
Service-Oriented Database Architecture. See SODA (Service-Oriented Database Architecture)
services, ADO.NET, 6
services, SQL Server. See SQL Server services
SET _TIMEOUT statement, locks, 236
SET DEFAULT, foreign key constraints, 197
SET NULL, foreign key constraints, 197
SET STATISTICS IO ON statements
page reads metric, 285
performance metric, 307
query cost and, 294
SET STATISTICS TIME ON statements
metric for query execution time, 286
query cost and, 294
SET TRANSACTION ISOLATION LEVEL statement, 234
shared locks (S-locks)
acquiring/releasing for read operations, 236
solving concurrency problems, 235
shared memory, network protocols, 3
Simple Object Access Protocol (SOAP), 21
smallint data types, 62
SMO (Server Management Objects), 145–149
assemblies, 145
connecting to a server instance, 147
exercise creating database using SMO application, 160–166
namespaces, 147
overview of, 145
tasks performed with, 149
SMP (Symmetric Multi Processing)
hardware trends, 407
NUMA as improvement to, 408
SNAC (SQL Native Client)
as data provider, 5
data access with, 12
exercise connecting using, 16–17
MARS enabled for use with OLEDB provider, 171
MARS enabled for use with SNAC ODBC provider, 171
recommended uses as data provider, 5
snapshot isolation level, 244–250
applying in a database, 244
overview of, 244
update conflicts and, 246–250
SOA (Service-Oriented Architecture), 357
SOAP (Simple Object Access Protocol), 21
SODA (Service-Oriented Database Architecture), 357–358
benefits of, 357
drawbacks of, 357
how it works, 358
overview of, 357
software
evaluation editions, xxvii
impact of hardware tends on software design, 408
sp_addmessage stored procedure, 225
sp_altermessage stored procedure, 225
sp_configure system stored procedure, 352
sp_create_plan_guide stored procedure, 73
sp_dropmessage stored procedure, 225
SPID (server processor identifier), 413
SQL injection attacks
implementing procedural data integrity and, 211–213
preventing, 211
vulnerability to, 132
SQL Native Client. See SNAC (SQL Native Client)
SQL Server 2005
care in use of cursors, 133
concurrency control, 120
connections for named instances, 25–26
database encryption. See encryption, SQL Server 2005 databases
database engine. See database engine
dynamic management functions, 123
evaluating effective use of cursors, 122
index internals. See indexes
linked servers, 25
query engine. See query engine
query notification feature, 105–108
storage engine. See storage engine
XML Web services, 22
SQL Server 2005 Installation Wizard
AMO installed with, 154
replication services installed with, 150
SQL Server Agent jobs, 149
SQL Server Analysis Services. See SSAS (SQL Server Analysis Services)
SQL Server Business Intelligence Development Studio
data mining, 155
exercise creating data mining structure, 166
SQL Server Configuration Manager
client protocol configuration, 4
network protocol configuration, 3
SQL Server Enterprise Edition
installing, xxi
software requirements for Exam 70-442, xxi
SQL Server instance, 147
SQL Server Integration Services (SSIS), 352
SQL Server Management Studio
AMO as alternative to
creating linked servers, 51
displaying query execution plan, 70
identifying NULL values in query results, 60
creating linked servers, 25
SMO for administering tasks normally done with, 145
SQL Server Profiler, 372–377
creating performance baselines, 380
exercise analyzing performance with, 386–390
overview of, 372
resource use in traces, 377
starting new trace, 374
templates, 373
trace property selection, 376
tracing cursor performance problems, 135
ways to execute, 373
SQL Server services
AMO (Analysis Management Objects). See AMO (Analysis Management Objects)
asynchronous processing. See asynchronous processing
RMO (Replication Management Objects). See RMO (Replication Management Objects)
SMO (Server Management Objects). See SMO (Server Management Objects)
SQL-92 syntax, 134
SqlClient .NET Provider, 172
SQLCLR (SQL Common Language Runtime). See also CLR (Common Language Runtime)
benefits of, 357
exercise creating user-defined function, 367–370
Transact-SQL compared with, 364
SQLOS (SQL Operating System), 408–425
application configuration strategy, 419
application performance and, 416–425
architecture, 409–411
configuring soft-NUMA support, 411–413
determining degree of parallelism in currently executing queries, 423
hierarchical relationship between nodes, schedulers, and tasks, 409
new hardware trends, 406
overview of, 408
querying wait types, 417
schedulers, 414–416
SQL Server task scheduling, 413
synchronization objects, 427
UMS compared with, 417
workload distribution, 420
SqlParameter objects, ADO.NET, 211
SSAS (SQL Server Analysis Services)
DataSource and DataSourceView objects, 158
programmatically controlling SSAS objects. See AMO (Analysis Management Objects)
references, 154
SSIS (SQL Server Integration Services), 352
SSL (Secure Sockets Layer)
certificates, 20
configuring SQL Server 2005 for, 473–475
stable media, storage engine, 444
static cursors
cursor types, 132
when to use, 133
Statistics Manager, query engine, 455
storage area networks. See SANs (storage area networks)
storage engine, 443–450
ACID properties, 443
buffer pool (BPool), 447
checkpoints, 449
data page sizes and numbers, 447
eager writes, 450
extents, 447
FUA (Forced Unit Access), 446
hardware read cache, 448
hardware trends, 406
hardware write cache, 448
latches, 449
lazy writer, 449
log parity, 446
LSN (Log Sequence Number), 448
mirroring and remote mirroring, 446
multichannel and load balancing systems, 445
object ID, 447
overview of, 443
Point in Time, 444
stable media, 444
tasks of, 443
Torn I/O, 445
WAL (Write-Ahead Logging), 448
write ordering (write dependency), 444
stored procedures
data validation in, 209
exercise creating, 228–229
for creating, modifying, deleting error messages, 225
object types, 430
processing on row-by-row basis, 130
SP:Recompile trace event, 461
using XACT_ABORT ON with, 220
verifying security settings in, 210
XPs executing out of band, 426
strings
disallowing unneeded characters in preventing SQL injection attacks, 211
empty strings compared with NULL values, 60
XML strings as query hints, 71
Study Mode, of practice tests, xxv
subqueries
correlated with outer query, 292–294
SELECT statements, 50
syntax for, 50
without correlation to outer query, 292
Surface Area Configuration tool, 361
symmetric key encryption, 472
Symmetric Multi Processing. See SMP (Symmetric Multi Processing)
synchronization objects, SQLOS, 427
sys.dm_db_index_physical_stats, 333
sys.dm_db_index_usage_stats, 323
sys.dm_exec_cursors function, 123
sys.partitions, catalog view, 346
sysadmin role, 19
system DSN, 8
System Monitor, 377–379
adding counter, 378
counters for creating performance
baselines, 381
counters for diagnosing problems, 382
creating performance baselines, 380
default counters, 378
overview of, 377
system requirements, Exam 70-442, xix
System.Transactions namespace, 268, 438
System.Web.Caching namespace, 102
T
table locks. See locks
table scan, indexes, 467
TableMiningStructureColumn, AMO data mining, 155
tables
applying data integrity rules to, 195, 214
heaps and clustered, 305
maximum columns in, 307
row width and, 332
table-valued functions. See TVFs (Table-Valued Functions)
Tabular Data Stream (TDS), 414
task scheduling, SQL Server, 413
TCP (Transmission Control Protocol), endpoints, 19
TCP/IP
communication protocol, 3
port affinity, 414
TDS (Tabular Data Stream), 414
technical support, Microsoft Press, xxvii
tempdb
limited space for, 385
snapshot transaction level and, 244
templates, SQL Server Profiler, 373
theoretical execution order, queries, 286
threads
multiple threading CPUs, 407
SQLOS synchronization and, 427
tiers, improving performance by moving code to different tiers, 360–363
timeouts, locks, 236
traces
cursor performance, 135
resource use and, 377
selecting trace properties, 376
SQL Server Profiler, 372
SQL:StmtRecompile, 460
starting new trace, 374
transactions, 233–282
applying locking hints, 265
case scenario for optimizing locking, 280
CLR for managing, 438–441
concurrency problems, 234
distributed, 267–269
excessive blocking, 384
exercise acquiring locks using read committed snapshot isolation level, 256–258
exercise applying default isolation level, 269–273
exercise applying locking hints, 273–276
exercise using read committed isolation level, 253–256
exercise using stored procedure to protect against deadlocks and phantom reads, 276–280
extensive locking problems, 252
isolation levels, 234, 444
locking hints, 260
MARS for avoiding transaction locks, 170
minimizing deadlocks, 266
.NET Framework transactions used with MARS, 173
OUTPUT keyword for optimizing locking, 261, 266
overview of, 233
read committed isolation level, 236–238
read committed snapshot isolation level, 250–252
read uncommitted isolation level, 238–239
repeatable read isolation level, 240–241
serializable isolation level, 242–243
snapshot isolation level, 244–250
Transact-SQL. See T-SQL (Transact-SQL)
triggers, 430. See also DML triggers
try/catch blocks
error handling, 221–224
exercise creating, 229–231
implementing in SQL Server 2005, 221
limitations in SQL Server 2005, 224
UPDATE or DELETE statement within snapshot transaction, 248
T-SQL (Transact-SQL)
aggregate functions, common problems with, 435
APPLY operator, 55
CREATE XML SCHEMA COLLECTION, 80
declaring cursors, 115
derived tables as alternative to cursors, 116
enabling ad hoc queries, 352
establishing query notification, 105
extended syntax for cursor declaration and, 134
implementing procedural data integrity, 208
inline table valued function for query optimization, 297
LIKE predicate, 468
Multi-statement-statement table-valued function, 297
not designed for object-oriented programming, 133
PIVOT operator, 54
query execution plans and, 71
queries, 364
SELECT statements. See SELECT statements
server-side distributed transactions, 267
as set-based language, 364
SCROLL_LOCKS option, 134
SQLCLR compared with, 364, 430–434
verifying permissions, 210
tuning queries. See also query performance
exercise rewriting query, 76
self-tuning capability of SQL Server, 70
TVFs (Table-Valued Functions)
APPLY operators and, 56
defined, 62
example application, 434
object types, 430
typed datasets, 33
type-safe code, CLR, 429
U
UDAs (User-Defined Aggregates), 430
UDFs (user-defined functions)
extending check constraints with, 200–202
scalar UDFs in query performance, 294–296
SQL Server 2005, 61
table-valued UDFs in query performance, 296
used with subqueries, 202–204
UDTs (User-Defined Types), 430
UMS (User Mode Scheduler), 406
SQLOS compared with, 417
SQLOS replaces, 408
Unicode data, 335
UNION
combining results of two or more SELECT statements, 57
query execution order and, 286
using with linked servers, 352
unique key constraints, 195
UNIX clients, 19
UNPIVOT operator, SELECT statements, 54
UNSAFE assemblies, 428
update locks (U-locks), 236
UPDATE statements
OUTPUT keyword, 261, 266
query hints and, 71
steps in processing, 457
subqueries, 50
within snapshot transaction, 248
updates, concurrency problems and, 246
UPDLOCK, locking hints, 261, 265
USE, query hints, 71
user control-level output caching, 100–101
compared with page-level, 100
when to use, 101
User DSN, 8
user IDs, 9
User-Defined Aggregates (UDAs), 430
user-defined data types (UDTs), 335, 430
user-defined functions. See UDFs (userdefined functions)
user-defined messages
error handling, 225
localizing for error handling, 226
User Mode Scheduler (UMS), 406
SQLOS compared with, 417
SQLOS replaces, 408
V
validation. See data integrity
value method, XQuery, 83
varchar data type, 335
VaryByControl attribute, OutputCache directive, 100
VaryByParam attribute, OutputCache directive, 99
vbc.exe, 363
vertical partitioning, 344
VIA (Virtual Interface Adapter), 3
Visual Basic
ADO (ActiveX Data Objects) used with, 10
command line compiler, 363
options for writing code for SQL Server, 360
Visual Basic.NET, 10
Visual C#, 10
Visual Studio 2005
installing Professional Edition, xxii
Web reference to HTTP endpoint, 23
W
W3C (World Wide Web Consortium), 81
WAL (Write-Ahead Logging), 448
WANs (wide area networks), 3
Web services
HTTP Web services, 22–24
XML Web services, 22
Web Services Description Language (WSDL)
creating endpoints and, 21
proxy code generation with, 23
WHERE clauses
compared with check constraints, 200
computed columns and, 317
function in SELECT statements, 456
non-repeatable reads and, 235
phantom reads and, 235
repeatable read isolation and, 240
searchable arguments, 74
SELECT statements, 455
serializable isolation level and, 242
subqueries and, 50
WHILE loops, as alternative to cursors, 117, 133
wide area networks (WANs), 3
Windows OSs
software requirements for Exam 70-442, xx
Visual Studio service packs for Windows Vista, xxi
Windows Event Viewer. See Event Viewer
WITH (HOLDLOCK), locking hints, 260
WITH (NOLOCK), locking hints, 260
WITH (NOWAIT), locking hints, 261
WITH (READ UNCOMMITTED / READ COMMITTED / REPEATABLEREAD / SERIALIZABLE), locking hints, 260
WITH (READPAST), locking hints, 260
WITH (UPDLOCK), locking hints, 261, 265
WITH (XLOCK), locking hints, 260, 265
World Wide Web Consortium (W3C), 81
Write permissions, AMO, 156
Write-Ahead Logging (WAL), 448
write ordering (write dependency), storage engine, 444
write performance, vs. read performance in indexes, 313–316
WSDL (Web Services Description Language)
creating endpoints and, 21
proxy code generation with, 23
X
XACT_ABORT, error handling, 219–221, 224
XACT_STATE ( ), error handling, 224
XLOCK, locking hints, 260, 265
XML (eXtensible Markup Language)
case sensitivity of, 83
exercise generating XML, 92
modify method, 84
overview of, 5
returning data from queries as XML, 86–89
updating data using XML, 89
xml data type
methods, 83
query methods for, 81
SQL support for, 80
updating data in XML queries, 89
XML indexes
overview of, 469
primary XML indexes, 469
secondary XML indexes, 470
XML schema
storing XML schema information, 80
SMO for accessing XML schema collections, 145
XML Web Services
benefits of, 357
SQL Server 2005 and, 22
XMLA (XML for Analysis), 154
XmlReader object, ADOMD.NET, 158
XPs (extended stored procedures)
executing out of band, 426
procedures compared with, 433
XQuery
case sensitivity, 83
components of, 81
exercise writing XQuery expression, 91
parameters, 84–85
querying XML data in SQL Server 2005, 469
querying XML data with, 81–84
© Microsoft. All Rights Reserved.