Index
NUMBERS
32-bit processors
AWE, limitations of, 453
extended system support, 454
upgrading to 64-bit, 449–450
virtual address space, 451
64-bit processors
addressable memory, 452
benefits of, 450
comparing SQL Server 2000 and SQL Server 2005, 454–455
A
accessing
dimensions in Analysis Services 2005, 414
metadata, 64
properties in Analysis Services 2005, 410
Resource database, 31
system metadata, 32
catalog security, 33–34
catalog views, 32–33
system tables, 64
active-active clusters, 87
Activity Monitor (SQL Server Management Studio), 110
monitoring locking and blocking, 278–281
addressable memory of 64-bit proces- sors, 452
administering HTTP SOAP endpoints, 328–329
Agent log files, viewing, 109–110
Aggregate transformation, 361
alignment, 271
ALTER INDEX statement, T-SQL enhancement, 190
ALTER permission, 204
AMO (Analysis Management Objects), 57, 405–406
required assembly references, 406
Analysis Services 2000. See also Analysis Services 2005
failover clustering support, 396
migrating to Analysis Services 2005, 415–416
Analysis Services 2005, 9, 392
ANSI SQL-92, SQL Server 2005 compliance, 213
databases, backing up, 412
dimensions, enhancements to, 401
new features
Intellicube, 399
multiple fact tables, 399
Optimize Schema option, 410
partitioning, 409–410
projects, 113
properties, accessing, 410
security, 412
granular permissions, 413–414
role-based, 414
selective aggregation, 394
tasks, 360
UBO, 409
XMLA support, 403–405
APIs
management tools, 146
SMO, 147–150
WMI, 150–151
SNAC, 52
WMI, 57–58
WMIL, managed objects, 58
appdomains, 338
application design, performance objectives
.NET runtime integration, 291
asynchronous messaging, 290
HTTP SOAP support, 292
mutliple active result sets, 291
query notifications, 291
T-SQL enhancements, 291
APPLY relational operator, 184
assemblies
catalog views, 345–346
dependencies, viewing, 336
dropping from database, 336
in .NET Framework, 333
properties, modifying, 336
references, AMO requirements, 406
SQLCLR, 62
assigning permissions to signed module users, 228–229
ASSL (Analysis Services Scripting Language), 403
associating xml data type with XSD schema, 305
Association algorithm, 402
asymmetric keys, 219
asynchronous messaging, 67
benefits of, 422
database integration, 423
asynchronous mode (database mirroring), 248
asynchronous triggers, 68, 435–440
ATTACH REBUILD LOG clause, 243
auditing, 219
AUs (allocation units), 37
endpoint-based, 196–197
limitations of in SQL Server 2000, 198
password policy support, 199
CREATE LOGIN DDL statement, options, 199–200
creating SQL Authenticated logins, 200
credentials, 200
disabling SQL Authenticated logins, 201
native password complexity, 199
SQL Browser, 197–198
authoring
reports, 113–114
SSIS packages, 111–112
authoring tools. See management and authoring tools
authorization, 201
granular permissions control, 202
ALTER permission, 204
CONTROL permission, 204
GRANT statement, 203
hierarchical permission model, 204–207
IMPERSONATE permission, 204
object-scoped permissions, 203
server-scoped permissions, 203
TAKE OWNERSHIP permission, 204
metadata, securing, 217–218
module execution context, 207
EXECUTE AS clause, 208–212
limitations of ownership chaining, 207–208
terminology, 202
user-schema separation, 212
creating schemas, 214
default schemas, 215–217
AUTO UPDATE STATISTICS ASYNC option, 70–71, 287
automatic failover, 244
automating UBO, 411
availability, enhancements to, 243–244
database mirroring, 244–249
database snapshots, 249–254
dedicated administrator connection, 258–259
early restore access, 259–260
failover clustering, 254–255
index operations, 256–257
instant file initialization, 260
mult-instance support, 258
replication, 255–256
AWE (Address Windowing Extensions), 254, 449, 451
dynamic AWE, 22
limitations of in 32-bit environments, 453
B
Back Up Database task, 362
backing up
Analysis Services databases, 412
databases with SQL Server Management Studio, 106
backup operations. See also restores
mirrored backup media, 238–239
performing on Service Broker applica- tions, 434
verifying, 239–240
base objects, 191
base-64 encoding and decoding functions (C#), 343–345
batch processing
query plans, 26–27
Service Broker solutions, 440
BIDS (Business Intelligence Development Studio), 110, 355, 363, 395
Breakpoint window, 365
Call Stack window, 365
Command window, 365
containers
For Loop container, 356
Foreach Loop container, 355
Sequence container, 356–357
Task Host container, 356
Debug window, 365
Immediate window, 365
Locals window, 365
Output window, 365
Properties window, 364
Solution Explorer, 364
SSIS Designer window, 364
Toolbox window, 364
Watch window, 365
BizTalk Server versus Service Broker, 441
BLOBs (binary large objects), 266
blocked process report, 118–120
blocked process threshold, 86, 271–276
blocking, monitoring, 278–281
blocking analysis, 271
Books Online, 93
BPool, 21
breaking changes to SQL Server 2005, 48–49
Breakpoint window (BIDS), 365
breakpoints, 365
building blocks of XML web services, 324–325
bulk loading XML data, 310–312
BULK rowset provider, 186–187
business intelligence, 89–91, 390
Business Intelligence Development Studio. See BIDS
business intelligence framework, 90
C
C#
base-64 encoding and decoding, 343–345
stored procedures, saving XML data to file, 341–343
calculating query plan cost, 27–28
Call Stackwindow (BIDS), 365
calling xml data type methods, 313
capturing blocked process reports, 118–120
catalog security, 33–34
catalog views, 32–33, 64, 188, 345–346
for Service Broker, 441–442
HTTP SOAP, 329
Character Map transformation, 361
Check Database Integrity task, 363
checksum I/O transactions, validating, 240
cleansing data, 387
clients, SSIS, 352
CLR (common language runtime), 9, 62, 334
appdomains, 338
sys.dm clr, 338
as replacement for extended stored procedures, 9
hosting, design goals, 337
integration with SQL. See also SQLCLR integration
benefits of, 339–340
catalog views, 345–346
challenges, 340
examples, 341–345
monitoring activity, 346–347
CLS (Common Language Specification), 332
clusters, active-active, 87
code access security in .NET Framework, 233–234
color-coding of SSIS execution status, 366
columns
non-key, including in indices, 68
partition columns, 38
persisted computed columns, 68–70, 265
COM objects, WMI providers, 58
Command window (BIDS), 365
command-line switches (SQLCMD), 133–136
command-prompt utilities, 366
DTExec, 367
DTExecUI, 366
DTUtil, 368
commands (SQLCMD), 138
common caching framework, 22
comparing
Service Broker and BizTalk Server, 441
Service Broker and Indigo, 441
Service Broker and MSMQ, 441
SQL Server 2000 and SQL Server 2005, 454
computed columns, 26, 68, 70, 265
Conditional Split transformation, 360
configuring
blocked process threshold, 272–274, 276
database mail, 143–145
database mirroring, 245–247
NUMA affinity, 24
tables with SQL Server Management Studio, 107–108
consolidation, 452
constraints
creating for tables, 107–108
precedence constraints, 351
constructing XML with XQuery, example, 315–316
constructors (XQuery), 312
For Loop container, 356
Foreach Loop container, 355
Sequence container, 356–357
Task Host container, 356
context switches, 16
CONTROL permission, 204
control-flow tasks, 358
Analysis Services tasks, 360
data-oriented tasks, 360
process-oriented workflow tasks, 358
Scripting tasks, 360
SQL Server tasks, 358
controlling system metadata access, 30–32
catalog security, 33–34
catalog views, 32–33
conversation groups, 427
conversations, 426
cooperative thread scheduling, 337
copy-on-write operation, database snap- shots, 251
Copy/Map transformation, 361
cost of query plans, calculating, 27–28
covering indices, 262
creating, 263
versus non-key included columns, 264
CREATE ASSEMBLY DDL statement, 335
CREATE ENDPOINT statement (T-SQL), 72
CREATE LOGIN DDL statement, options, 199–200
creating
assemblies, 335
asymmetric keys, 220
contracts, 432
database master keys, 221
database snapshots, 250–251
endpoints
for database mirroring, 247
HTTP, 326–328
Service Broker, 431
indices
covering indices, 263
nonclustered, 263
on computed columns, 265
on xml type columns, 266–267
primary XML indices, 317–318
secondary XML indices, 317–318
message types, 431–432
new databases with SQL Server Management Studio, 106
new logins, 110
plan guides, 282–286
queues, 432–433
reports, 113–114
schema, 214
services, 433
SQL authenticated logins, 200
symmetric keys, 220
synonyms, 191
typed XML, 306–310
cryptography
data encryption
asymmetric keys, 219
symmetric keys, 219–221
key management, 221–224
CTEs (common table expressions), 73–74, 159–163
CTS (Common Type System), 332
cubes, 391
creating from scratch, 416
data sparsity, 394
Intellicube, 399
multiple fact tables, support for on Analysis Services 2005, 399
perspectives, 399
processing, 412
D
DAC (dedicated administrator connec- tion), 142
data-oriented tasks, 360
data collection, Service Broker solutions, 440
Data Conversion transformation, 361
data encryption
asymmetric keys, 219
key management, 221
symmetric keys, 219–224
data explosion, 393
data flow engine (SSIS), 354
destination adapters, 362
source adapters, 360
transformations, 360
data marts, 390
Data Mining Model Training transforma- tion, 361
Data Mining Query transformation, 361
Data Mining Wizard, 403
data source views, 397
data sparsity, 394
data types, xml, 59–60
Database Mail, 57
configuring, 143–145
monitoring, 145–146
Database Maintenance tasks, 362–363
database master keys
creating, 221
encryption, 225
database mirroring, 7
benefits of, 245–246
configuring, 246–247
monitoring, 249
operation modes, 247–248
pausing, 247
server instances, 244
split-brain scenario, 244
copy-on-write operation, 251
creating, 250–251
of mirrored databases, 250
recovering deleted tables, 253–254
sparse files, 251–252
Database Tuning Advisor, 83
DATE CORRELATION OPTIMIZATION option, 70, 287
DBAs (database administrators), perform- ing developer responsibilities, 10
deadlock information, viewing, 278
Debug window (BIDS), 365
declaring variables of xml data type, 304
decrypting data, 220–221
Dedicated Administrative Console feature, 6
dedicated administrator connections, 258–259
default schema, 215–217
defragmenting indices, 87
DENSE RANK function, 77, 179–180
dependencies of assemblies, viewing, 336
deprecated features, 47–48
Derived transformation, 360
design goals of CLR hosting in SQL Server 2005, 337
designing SSIS packages, 368, 372–377, 380
logging, 371
SSIS expressions, 370
SSIS Object model, 368–370
destination adapters, 362
Developer Edition, 41
dialog conversations, 426
starting, 433–434
terminating, 434
dialog security, 446
Dimension Processing transformation, 361
dimension-level security, 414
dimensions, 391
enhancements to Analysis Services 2005, 401
fact dimensions, 401
many-to-many, 402
member properties, 401
members, 391
multiple hierarchy dimensions, 402
reference dimensions, 401
virtual, 401
disabling
Service Broker, 430
SQL authenticated logins, 201
disconnected editing, 98
discontinued features, 47–48
Discover method (XMLA), 403
distributing server-side processing, 440
DMVs (dynamic management views), 189
monitoring Service Broker, 442–443
monitoring SQLCLR activity, 346–347
DRI (declarative referential integrity) actions, 187–188
dropping assemblies from database, 336
DSO (Decision Support Objects), 57, 405
DSVs (data source views), 388
DTA (Database Tuning Advisor), 84–85, 125–131, 271
DTExec, 367
DTExecUI, 366
DTMF (Distributed Management Task Force), 58
DTS (Data Transformation Services), 5
DTS 2000 packages, migrating to SSIS, 384–385
DTS runtime engine, 353
DTUtil, 368
Dynamic AWE, 22
dynamic management objects, 86, 292–294
dynamic management views, 188–189
E
early restore access, 259–260
editions, 40
email, database mail, 57
configuring, 143–145
monitoring, 145–146
Emergency mode, invoking, 242
ENABLE BROKER option, 288
enabling
database engine features, 229
row versioning, 29
Service Broker, 430
encryption. See data encryption
ending dialog conversations, 434
for database mirroring, creating, 247
HTTP, creating, 326–328
HTTP SOAP, administering, 328–329
enhancements
to Agent security, 232
to auditing, 219
to availability, 243–249
server instances, 244
split-brain scenario, 244
database snapshots, 249–254
dedicated administrator connection, 258–259
early restore access, 259–260
failover clustering, 254–255
index operations, 256–257
instant file initialization, 260
multi-instance support, 258
replication, 255–256
to FOR XML clause, 320–323
to reliability, 237
ATTACH REBUILD LOG clause, 243
checksum I/O transactions, validat- ing, 240
Emergency mode, 242
mirrored backup media, 238–239
online restoration, 240
page restorations, 241
piecemeal restoration, 241
verifying backup and restore media, 239–240
to SQL Server 2005 Setup, 43–45
to T-SQL, 153
ALTER INDEX statement, 190
BULK rowset provider, 186–187
CTEs, 159–163
DDL triggers, 167–171
DRI actions, 187–188
dynamic management views, 188–189
error handling, 165–167
event notifications, 171–176
EXCEPT operator, 192–193
INTERSECT operator, 192–193
large object data types, 163–165
metadata views, 188–189
OUTPUT clause, 185–186
ranking functions, 177–180
NTILE, 180
RANK, 179–180
ROW NUMBER, 178
relational operators, 180–184
server configuration options, 191
SET SHOWPLAN XML statement, 193
SET STATISTICS XML operator, 193
snapshot isolation, 176
statement-level recompilation, 191
synonyms, 191–192
TABLESAMPLE clause, 157–158
TOP operator, 155–157
Enterprise Edition, 40
environment variables (SQLCMD), 136–137
EPIC (Explicitly Parallel Instruction Computing), 450
error handling, 75–76, 165–167
ETL (extraction, transformation and loading), 387
Evaluation Edition, 41
event notifications, 56, 171–176
EVENTDATA() function, 168
events, SSIS, 353–354
examples
of exist() method, 314
of query() method, 313
of SQLCLR integration
base-64 encoding and decoding, 343–345
catalog views, 345–346
saving XML data to file, 341–343
of value() method, 314
of xml data type, 302–303
EXCEPT operator, T-SQL enhancement, 192–193
EXECUTE AS clause, 208–212
Execute method (XMLA), 403
Execute Process task, 359
Execute SQL task, 358
executing queries in parallel, 28–29
execution contexts, 27
defining for modules, 207
EXECUTE AS clause, 208–212
limitations of ownership chaining, 207–208
execution plans, 27
Executive DTS 2000 Package task, 358
Executive Package task, 358
exist() method, 313–314
Express Manager, 114
expressions, 370
FLWOR, 315–316
XQuery, 312
extended stored procedures, 9, 334
extended system support, 454
extending virtual memory on 32-bit processors, 451
external activation, 429
extracting data from XML documents, 312
F
fact dimensions, 401
fact tables, 391
failover clustering, 243, 254–255, 396
features
of SQL Server 2005 Profiler, 116–118
of SQL Server Management Studio, 98–99
File Extractor transformation, 361
File Inserter transformation, 361
fixing fragmented indices, 87
FLWOR expressions (XQuery), 312, 315–316
For Loop container, 356
FOR XML clause, enhancements to, 320–323
Foreach Loop container, 355
fragmented indices, fixing, 87
Full-Text Search, enhancements to, 93
fully-qualified objects, naming conven- tions, 213
functions
EVENTDATA(), 168
OPENXML(), 320
ranking, 77
Fuzzy Grouping transformation, 361
Fuzzy Lookup transformation, 361
G – H
garbage collection algorithm, 332
GRANT statement, 203
grantees, 202
grantors, 202
granular permissions
controlling, 202
ALTER permission, 204
CONTROL permission, 204
GRANT statement, 203
hierarchical permission model, 204–207
IMPERSONATE permission, 204
object-scoped permissions, 203
server-scoped permissions, 203
TAKE OWNERSHIP permission, 204
in Analysis Services 2005, 413–414
graphing deadlocks, 121–123, 278
hardware requirements for installation
SQL Server 2005 Enterprise Edition 32-Bit, 41
SQL Server 2005 Enterprise Edition 64-Bit, 42
hierarchical permission model, 204–207
high availability, 237, 243–244
high-protection mode, 247
hints, 286
HOLAP (Hybrid OLAP), 392
host protection attributes, 338
hot add memory, 22
HTTP SOAP, 9
endpoints, administering, 328–329
support for, 298
hyperthreading, 71
I
I/O list, 17
I/O transactions, validating, 240
Immediate window (BIDS), 365
IMPERSONATE permission, 204
implementing
AMO, 406–408
UBO, 410
improving physical database design, covering indices, 262–263
IN ROW DATA AUs, 37
including non-key columns in nonclus- tered indices, 262–264
index operations, 256–257
indices
creating for tables, 107–108
fragmentation, repairing, 87
non-key columns, including, 68
nonclustered, including non-key columns, 262–264
on computed columns, creating, 265
on xml type columns, 60
primary XML indices, 316–318
secondary XML indices, 316–318
inheritance model, 33
initializing transactional subscriptions from backups, 256
initiating dialog conversations, 433–434
initiators, 426
INSERT statements, 303–304
installing
multiple instances of Analysis Services, 258
SQL Server 2005, SAC, 229–232
SQL Server 2005 Enterprise Edition 32-Bit, hardware requirements, 41
SQL Server 2005 Enterprise Edition 64-Bit, hardware requirements, 42
instance classes, 148
instances
endpoints, 196–197
multi-instance support, 258
instant file initialization, 88, 260
integrated messaging and database systems, benefits of, 423
integration of SQL and CLR. See SQLCLR integration
Intellicube, 399
internal activation, 429
internal tables, sys.sysobjvalues, 25
Internet Help feature, 52
INTERSECT operator, T-SQL enhance- ment, 192–193
invoking Emergency mode, 242
IPF (Intel Itanium Processor Family), 450
isolation levels, READ UNCOMMITTED, 65
ITW (Index Tuning Wizard), 269
J – K – L
key management, 221–224
keyboard shortcuts, Query Editor, 104
keywords
PIVOT, 77–78
TABLESAMPLE, 76
UNPIVOT, 77–78
KPIs (key performance indicators), 399
large object data types, T-SQL enhance- ments, 163–165
large page allocator, 21
limitations
of ownership chaining, 207–208
of SQL Server 2000 authentication, 198
LOB DATA AUs, 37
Locals window (BIDS), 365
locking, monitoring, 278, 280–281
log shipping, 243
Logged Lineage transformation, 361
logging, 371
logins, creating, 110
Lookup transformation, 362
M
managed objects, 58
management and authoring tools, 97
BIDS, 110
Analysis Services projects designer, 113
Express Manager, 114
Reporting Services, 113–114
SSIS package designer, 111–112
SQL Server Management Studio, 97–99
built-in web browser, 103
creating new databases, 106
modifying server instance proper- ties, 106
Object Explorer window, 100–102
performing database backups, 106
performing server registration, 105
Query Editor window, 103–105
Registered Servers window, 100
tables, 107–108
managing system metadata access, 30–32
catalog security, 33–34
catalog views, 32–33
many-to-many dimensions, 402
markup languages, XML. See XML
MARS (multiple active results sets), 29
max specifier, 61
measures, 391
member properties, 401
members, 391
memory management, 20
architectural components, 21
BPool, 21
common caching framework, 22
Dynamic AWE, 22
hot add memory, 22
NUMA, 23
plan cache, 26
reserved address space, 21
resource monitor, 23
Merge Join transformation, 361
merge replication, 89
Merge transformation, 361
message forwarding, 429
messaging
asynchronous
benefits of, 422
database integration, 423
BizTalk Server, comparing with Service Broker, 441
Indigo, comparing with Service Broker, 441
MSMQ, 441
sending and receiving messages, 434
Service Broker, 234
catalog views, 441–442
contracts, 425
conversation groups, 427
creating message types, 431–432
dialog conversations, 426
endpoints, 428
message forwarding, 429
message types, 424–425
monitoring, 442–443
Perfmon counters, 445
poison messages, 428
Profiler trace events, 444
queues, 425
remote service bindings, 428
routes, 427
security, 446–447
sequenced messages, 424
service programs, 426
services, 425
troubleshooting, 445–446
unsequenced messages, 424
metadata
accessing, 64
restricting access to, 217–218
views, 188–189
methods
modify(), 319–320
xml data type methods (XQuery), 313
Microsoft .NET Framework. See .NET Framework
Microsoft SQL Server 2000 tools, equiva- lent SQL Server 2005 tools, 96
Microsoft’s Trustworthy Computing initia- tive, 412
migrating
DTS 2000 packages to SSIS, 384–385
from Analysis Services 2000 to Analysis Services 2005, 415–416
mirror database, 87
mirrored backup media, 238–239
mirroring, 244
benefits of, 245–246
configuring, 246–247
monitoring, 249
operation modes, 247–248
pausing, 247
server instances, 244
split-brain scenarios, 244
versus log shipping, 87
Mobile Edition, 40
modifying
assembliy properties, 336
server instance properties with SQL Server Management Studio, 106
module execution context, 207
EXECUTE AS clause, 208–212
limitations of ownership chaining, 207–208
modules, signed, 226–229
MOLAP (Multidimensional OLAP), 392
monitoring
blocking, 276
database mail, 145–146
database mirroring, 249
locking and blocking, 272, 276–281
Service Broker, 442–443
SQLCLR activity, 346–347
monolog, 426
MPIO (multi-path I/O), 36
MSMQ (Microsoft Message Queuing), 422, 441
multi-instance support, 258
multi-page allocator, 21
Multicast transformation, 360
multiple fact tables, support for on Analysis Services 2005, 399
multiple hierarchy dimensions, 402
multiple instance support, 395
N
n-tier model, 11
Naïve Bayes algorithm, 402
naming conventions for fully-qualified objects, 213
native password complexity, 199
.NET Framework
assemblies, 333
base class library, 332
benefits of, 332
CLR, 334
CLS, 332
code access security, 233–234, 333
CTS, 332
garbage collection algorithm, 332
host protection attributes, 338
interoperability with Win32 and COM code, 333
object-oriented programming features, 333
benefits of, 339–340
catalog views, 345–346
challenges, 340
comparing with T-SQL, 153–155
examples of, 341–345
monitoring activity, 346–347
type verification, 333
.NET programming model, 62
neural network algorithm, 402
new features
Dedicated Administrative Console, 6
endpoint-based authentication, 8
failover clustering support, 396
for Analysis Services 2005, 399
multiple instance support, 395
Resource database, 6
setup features, 43–45
SQL Server Management Studio, 5
SSIS, 5
system catalog, 6
UDM (unified dimensional model), 396
new technologies, Service Broker, 67
nodes, SQL Server Management Studio, 101–102
nodes() method, 313
non-key included columns, 68, 264
non-preemptive scheduling, 17
non-recursive CTEs, 159
nonclustered indices
creating, 263
including non-key columns, 262–264
Notification Services, 92
NTILE ranking function, 180
NTITLE function, 77
NUMA (non-uniform memory access), 7, 23–24, 71
O
Object Explorer window (SQL Server Management Studio), 100–102
object-scoped permissions, 203
obtaining query execution plans as XML text, 124
offline restorations, 241
OLAP (online analytical processing)
Analysis Services, 392
business intelligence, 390
cubes, 391
data sparsity, 394
dimensions, 391
measures, 391
relational reporting, 396
storage modes, 392
OLE DB Command transformation, 361
OLTP (online transaction processing), 390
online index operations, 29, 256–257
online restoration, 240
OPENROWSET function, 297
OPENXML function, 320
operation modes, database mirroring, 247–248
operations and configuration tools
Database Mail
configuring, 143–145
monitoring, 145–146
SQL Server Configuration Manager, 131–133
SQLCMD
command-line switches, 133–136
commands, 138
dedicated administrator connection, 142
environment variables, 136–137
startup scripts, 142
variables, 141–142
OPTIMIZE FOR hint, 286
Optimize Schema option (Analysis Services 2005), 410
optimizing queries on xml type columns, 268–269
OUTPUT clause, 185–186
Output window (BIDS), 365
ownership chaining, 82, 207–208. See also module execution context
P
packages, 351
logging, 371
SSIS expressions, 370
SSIS Object model, 368–370
DTS 2000, migrating to SSIS, 384–385
PAE (Physical Addressing Extension), 451
page restoration, 241
parallel index operations, 256–257
parallel processing, Service Broker solu- tions, 440
parallelism, 26–29
PARAMETERIZATION hint, 287
partition columns, 38
Partition Process transformation, 362
alignment, 271
indices, 70
rows, ranking, 77
passphrases
creating, 80
encrypting/decrypting data, 220–221
password policy support
CREATE LOGIN DDL statement, options, 199–200
creating SQL Authenicated logins, 200
credentials, 200
disabling SQL Authenicated logins, 201
native password complexity, 199
path expressions (XQuery), 312
PATH indices, 268
pausing database mirroring, 247
peer-to-peer transactional replication, 89, 255
Performance Counters Correlation, 83
performance enhancement tools, 115
DTA, 125–131
Performance Monitor, counters, 125
SQL Server 2005 Profiler
blocked process report, 118–120
deadlock graphs, 121–123
features, 116–118
XML showplans, 123–125
Performance Monitor, counters, 125, 445
performance tuning
AUTO UPDATE STATISTICS ASYNC option, 70–71
DATE CORRELATION OPTIMIZATION option, 70
hyperthreading, 71
indexed views, 71
NUMA, 71
statement-level recompilation, 288–290
performing
database backups with SQL Server Management Studio, 106
server administration with SQL Server Management Studio, 105
permission buckets (SQLCLR), imported assemblies, 63–64
permissions
assigning to signed module users, 228–229
granular control, 202
.NET Framework, 233–234
ALTER permission, 204
Analysis Services 2005, 413–414
CONTROL permission, 204
GRANT statement, 203
hierarchical permission model, 204–207
IMPERSONATE permission, 204
object-scoped permissions, 203
server-scoped permissions, 203
TAKE OWNERSHIP permission, 204
VIEW DEFINITION, 217
persisted computed columns, 68–70, 265
perspectives, 399
physical database design
covering indices, creating, 262–263
indices on computed columns, creat- ing, 265
piecemeal restoration, 241
pipelines, 387
PIVOT keyword, 77–78
PIVOT relational operator, 180, 183
plan guides, 282–286
poison messages, 428
precedence constraints, 351
preemptive threading model, 337
primary XML indices, 266–267, 316–318
principal, 202
prinicipal of least privileges, 202
proactive caching, 397–398
proactive performance monitoring, 276
processes, 16
processing cubes, 412
processors
32-bit, limitations of AWE, 453
64-bit
addressable memory, 452
benefits of, 450
upgrading to, 449–450
progress reporting (SSIS), 365
projects (SQL Server Management Studio), 98
prolog (XQuery), 312
properties
accessing in Analysis Services 2005, 410
of assemblies, modifying, 336
of server instances, modifying with SQL Server Management Studio, 106
Properties window (BIDS), 364
PROPERTY indices, 268
property promotion, 304–305
providers, WMI, 58
proxy accounts, 233
public keys, certificates, 221
Q
queries
executing in parallel, 28–29
execution plans, obtaining as XML text, 124
optimizing. See query optimization
performance tuning, plan guides, 282–286
performing on XML data, 312
Xpath, syntax, 301
XQuery
expressions, 312
FLWOR expressions, 315–316
XML DML, 319–320
query body (XQuery), 312
Query Editor window (SQL Server Management Studio), 103–105
keyboard shortcuts, 104
query hints, 286
query notifications, 291
query optimization
column-level change tracking, 25
on xml type columns, 268–269
statistics management, 24
AUTO UPDATE STATISTICS ASYNC database option, 25
computed columns, 26
enhanced DBCC SHOW STATISTICS results, 26
parallelism, 26
sample size formula, 26
string statistics, 26
query plans, 26
cost, calculating, 27–28
execution contexts, 27
execution plans, 27
recompilation, 27
query() method, 313
querying languages, WQL, 58
R
ranking functions, T-SQL enhancements, 177
DENSE RANK, 179–180
NTILE, 180
ROW NUMBER, 178
READ COMMITTED isolation level, 8, 29, 65
READ UNCOMMITTED isolation level, 65
real-time log shipping. See database mirroring
Rebuild Index task, 363
receiving messages, 434
recompilation, 27
RECOMPILE hint, 286
recovering deleted tables with database snapshots, 253–254
recursion, infinite loops, 75
redundancy, mirrored backup media, 238–239
reference dimensions, 401
Registered Servers window, 100
relational engine, 59
relational operators, T-SQL enhancements
APPLY, 184
relational reporting (OLAP), 396
reliability, 237
ATTACH REBUILD LOG clause, 243
backup and restore operations verifica- tion, 239–240
Emergency mode, invoking, 242
I/O transaction validation, 240
mirrored backup media, 238–239
online restorations, 240
page restorations, 241
piecemeal restorations, 241
Reorganize Index task, 363
repairing fragmented indices, 87
REPEATABLE READ isolation level, 65
enhancements to, 255–256
merge replication, 89
of Oracle data, 89
of schema changes, 88
peer-to-peer transactional, 89
Replication Monitor, 256
Report Builder, 91
Reporting Services, 91, 113–114
reserved address space, 21
reserved page allocator, 21
Resource database, 6, 30–31, 64
resource monitor, 23
resource waiter list, 17
restores. See also backup operations
early restore access, 259–260
online, 240
page, 241
performing on Service Broker applica- tions, 434
piecemeal, 241
verifying, 239–240
restricting access to metadata, 217–218
RMO (Replication Management Objects), 57, 256
ROLAP (Relational OLAP), 392
role-based security, Analysis Services 2005, 414
routes, 427
routing tasks to scheduler, 20
Row Count transformation, 362
ROW OVERFLOW DATA AUs, 37
Row Sampling transformation, 362
row versioning, 29, 65–67, 176, 276–277
rows, ranking, 77
runnable list, 17
S
SAC (Surface Area Configuration), 229–232
saving XML data to file, C# stored proce- dure, 341–343
scalable application architectures, 421
SCC (System Configuration Checker), 43, 52
scheduling
cooperative thread scheduling, 337
nonpreemptive, 17
scheduling nodes, 18
schema
collections, 309
creating, 214
default, 215–217
replication, 255
user-schema separation, 212
versus user, 81
Script Component transformation, 362
scripter classes, 148
scripting languages, ASSL, 403
Scripting tasks, 360
scrubbing data, 387
secondary XML indices, 267, 316–318
securables, 202
secure by default, 80
secure by deployment, 80
security
Agent security, enhancements to, 232
auditing, 219
authentication, 80
endpoint-based, 196–197
limitations of in SQL Server 2000, 198
native password complexity, 199
password policy support, 199–201
passwords, 80
SQL Browser, 197–198
authorization, 201
granular permissions control, 202–207
metadata, restricting access to, 217–218
module execution context, 207–212
terminology, 202
user-schema separation, 212–217
code access security in .NET Framework, 233–234
cryptography
data encryption, 219–221
key management, 221–224
dimension-level, 414
encryption, 82
of Analysis Services, 412–414
ownership chaining, 82
proxy accounts, 233
role-based, 414
SAC, 229–232
signed modules, 226–227
assigning permissions to users, 228–229
user schema separation, 35
security architecture, 8
selective aggregation, 394
semicolon, statement operator, 163
sending messages, 434
sequence clustering, 402
Sequence container, 356–357
sequenced messages, 424
SERIALIZABLE isolation level, 65
server configuration options, T-SQL enhancments, 191
server-scoped permissions, 203
server-side processing, Service Broker solutions, 440
Service Broker, 9–12, 67, 234, 423
applications, 435–439
batch processing, 440
data collection, 440
parallel processing, 440
server-side processing, distributing, 440
applications, backing up and restoring, 434
availability, 11
catalog views, 441–442
conversation groups, 427
dialog conversations, 426
starting, 433–434
terminating, 434
disabling, 430
enabling, 430
message types, 424–425, 431–432
messages, 424
forwarding, 429
sending and receiving, 434
monitoring, 442–443
Perfmon counters, 445
poison messages, 428
Profiler trace events, 444
remote service bindings, 428
routes, 427
security, 446–447
sequenced messages, 424
service programs, 426
terminology, 424
troubleshooting, 445–446
unsequenced messages, 424
service programs, 426
SET SHOWPLAN XML statement, T-SQL enhancement, 193
SET STATISTICS XML statement, T-SQL enhancement, 193
setup enhancements, 43–45
Setup Progress dialog box, 45
showplans, 123
signed modules, 226–229
single-page allocator, 21
Slowly Changing Dimension transforma- tion, 362
SMO (SQL Server Management Objects), 57, 147–150
SMP (symmetric multiprocessing), 23, 71
SNAC (SQL Native Client), 52
snapshot isolation, 7–8, 276. See also snapshots
row versioning, 65–67
T-SQL enhancements, 176
snapshots, 249
copy-on-write operation, 251
creating, 250–251
recovering deleted tables, 253–254
sparse files, 251–252
snowflake schema, 392
software requirements for installation, 42
Solution Explorer, 364
Sort transformation, 362
source adapters, 360
source data adapters, 387
sparse files, database snapshots, 251–252
split-brain scenarios, 244
SQL Authentication, 196
SQL Browser, 197–198
SQL DMO (Distributed Management Objects), 57
SQL Profiler tool, 395
SQL Server Management Studio, 97–99
built-in web browser, 103
creating new databases, 106
database backups, performing, 106
modifying server instance properties, 106
Object Explorer window, 100–102
Query Editor window, 103–105
Registered Servers window, 100
server administration, 105
tables, 107–108
SQL Server 2005 Express Edition, 72
SQL Server 2005 Profiler
blocked process report, 118–120
deadlock graphs, 121–123
features, 116–118
XML showplans, 123–125
SQL Server 7.0 Service Pack 4, upgrading to SQL Server 2005, 46–47
SQL Server Books Online, 93
SQL Server Configuration Manager, 54, 131–133
SQL server log files, viewing, 109–110
SQL Server Management Studio, 5, 53, 366
projects, 98
usability enhancements, 99
SQL Server Mobile Edition, 72
SQL Server tasks, Execute SQL task, 358
SQLCLR integration
assemblies, 62
benefits of, 339–340
catalog views, 345–346
challenges, 340
comparing with T-SQL, 153–155
examples of
base-64 encoding and decoding, 343–345
saving XML data to file, 341–343
monitoring activity, 346–347
permission buckets, imported assem- blies, 63–64
SQLCMD, 55
command-line switches, 133–136
commands, 138
dedicated administrator connection, 142
environment variables, 136–137
startup scripts, 142
variables, 141–142
memory management, 20
architectural components, 21
BPool, 21
common caching framework, 22
Dynamic AWE, 22
hot add memory, 22
reserved address space, 21
resource monitor, 23
non-preemptive scheduling, 18
threads, max worker threads server configuration option, 19–20
UMS, 16
SSIS (SQL Server Integration Services), 5, 8, 90, 349, 390
BIDS, 363
Breakpoint window, 365
Call Stack window, 365
Command window, 365
Debug window, 365
Immediate window, 365
Locals window, 365
Output window, 365
Properties window, 364
Solution Explorer, 364
SSIS Designer window, 364
Toolbox window, 364
Watch window, 365
clients, 352
command-prompt utilities
DTExec, 367
DTExecUI, 366
DTUtil, 368
Configuration Wizard, 383
control flow, separation from data flow, 351
core components, 350
data flow engine, 354
Designer window, 364
destination adapters, 362
DTS runtime engine, 353
events, 353–354
expressions, 370
Import/Export Wizard, 351, 383
Migration Wizard, 383–387
object model, 368–370
packages, designing, 111–112, 368–377, 380
progress reporting, 365
source adapters, 360
SQL Server Management Studio, 366
tasks, 350
control-flow tasks, 358–360
data-flow tasks, 354
process-oriented workflow tasks, 358
transformations, 360
Windows service, 352
SSMS (SQL Server Management Studio), 395
Standard Edition, 40
star schema, 391
starting dialog conversations, 433–434
startup scripts (SQLCMD), 142
statement-level recompilation, 27, 191, 288–290
statements
ALTER INDEX, new T-SQL enhance- ment, 190
semicolon operator, 163
statistics management, 24
AUTO UPDATE STATISTICS ASYNC database option, 25
column-level change tracking, 25
computed columns, 26
enhanced DBCC SHOW STATISTICS results, 26
parallelism, 26
sample size formula, 26
string statistics, 26
storage engine, 59
AUs, 37
datafile autogrow, 36
instant file initialization, 36
MPIO, 36
partitioning, 38
stored procedures (C#), saving XML data to file, 341–343
string statistics, 26
supported platforms on SQL Server 2005, 454
supported technologies
CLR, 9–10
snapshot isolation, 7–8
XML, 8
Surface Area Configuration tool, 8
symmetric keys, 219–224
synchronous mode (database mirroring), 248
synonyms, new T-SQL enhancement, 191–192
sys.sysobjvalues table, 25
system catalog, 6
system metadata
access, controlling, 32–34
Resource database, 30–31
system tables, accessing, 64
T
comparing with .NET integration, 153–155
CREATE ENDPOINT statement, 72
CTEs, 73–75
enhancements to, 153
ALTER INDEX statement, 190
APPLY relational operator, 184
BULK rowset provider, 186–187
CTEs, 159–163
DDL triggers, 167–171
DRI actions, 187–188
dynamic management views, 188–189
error handling, 165–167
event notifications, 171–176
EXCEPT operator, 192–193
INTERSECT operator, 192–193
large object data types, 163–165
metadata views, 188–189
OUTPUT clause, 185–186
PIVOT relational operator, 180, 183
ranking functions, 177–180
server configuration options, 191
SET SHOWPLAN XML statement, 193
SET STATISTICS XML statement, 193
snapshot isolation, 176
statement-level recompilation, 191
synonyms, 191–192
TABLESAMPLE clause, 157–158
TOP operator, 155–157
UNPIVOT relational operator, 180, 183
triggers, 55
tables
configuring with SQL Server Management Studio, 107–108
TABLESAMPLE clause, 157–158
TABLESAMPLE keyword, 76
TAKE OWNERSHIP permission, 204
targets, 426
Task Host container, 356
tasks, 18
control-flow tasks
Analysis Services tasks, 360
data-oriented tasks, 360
process oriented workflow tasks, 358
Scripting tasks, 360
SQL Server tasks, 358
destination adapters, 362
source adapters, 360
transformations, 360
routing to scheduler, 20
SSIS, 350
tasks (SSIS), 351
TCP-C benchmarks, 451
TDS (Tabular Data Stream), 9, 196
terminating dialog conversations, 434
threads
context switches, 16
cooperative thread scheduling, 337
max worker threads server configura- tion option, 19–20
scheduling, 16–18
worker threads, 18
time series algorithm, 402
timer list, 17
Toolbox window (BIDS), 364
tools
API Management, 146
SMO, 147–150
WMI, 150–151
ITW, 269
management and authoring, 97
BI Development Studio, 110–114
Express Manager, 114
SQL Server Management Studio, 97–108
Microsoft SQL Server 2000, equivalent SQL Server 2005 tools, 96
operations and configuration
Database Mail, 143–146
SQL Server Configuration Manager, 131–133
SQLCMD, 133–134, 136–138, 141–142
performance enhancement, 115
DTA, 125–131
Performance Monitor, counters, 125
SQL Server 2005 Profiler, 116–125
features, 116–118
TOP operator, 155–157
TP-Lite, 11
tracing XQuery activity, 316
transactional publications, initializing subscriptions, 256
transactional replication, peer-to-peer, 89, 255
transactions, row versioning, 29
transformations, 360
translations, 399
transport protocols, endpoints, 197
transport security, 446
triggers, 167
asynchronous, 68
Service Broker solutions, 435–440
DDL, 56
T-SQL, 55
troubleshooting
Database Mail, 145–146
Service Broker, 445–446
Trustworthy Computing Initiative, 195
tuning performance
AUTO UPDATE STATISTICs ASYNC option, 287
DATE CORRELATION OPTIMIZATION ASYNC option, 287
DTA, 84–85
ENABLE BROKER option, 288
typed XML, 305
columns, 59
creating, 306–310
U
UBO (usage-based optimization), 409
automating, 411
implementing, 410
UBO Wizard, 411
UDM (unified dimensional model), 396–398
UMS (User Mode Scheduler), 16–17
Union All transformation, 362
UNPIVOT keyword, 77–78
UNPIVOT relational operator, 180, 183
UnPivot transformation, 362
unreachable remote services, 426
unsequenced messages, 424
Upgrade Advisor, 49
upgrading
processors to 64-bit, 449–450
to SQL Server 2005, 46–47
usage-based optimization, 394
user-schema separation, 35, 81, 212
creating schemas, 214
default schemas, 215–217
utilities, Wiloqutl.exe, 45
utility classes, 148
V
valid XML documents, 299
validating
checksum I/O transactions, 240
XML data with typed XML, 305
VALUE indices, 268
value() method, 313–314
variables
SQLCMD, 141–142
XML data type, declaring, 304
VAS (virtual address space), 20
BPool, 21
of 32-bit processors, 451
reserved address space, 21
verifying backup and restore media, 239–240
viewing
assembly dependencies, 336
deadlock information, 278
views, DSVs, 388
virtual allocator, 21
virtual dimensions, 401
W
W3C (World Wide Web Consortium), 297
Watch window (BIDS), 365
WBEM (Web-Based Enterprise Management), 58
web services support, 72, 298, 323–325
well-formed XML documents, 298–299
“Whidbey”, 333
Wiloqutl.exe, 45
Windows Authentication, 196
Window Communication Foundation, 441
witness server, 87
WMI (Windows Management Instrumentation), 57–58, 150–151
worker list, 17
worker threads, 18
Workgroup Edition, 40
WOW64 mode, 454
WQL (WMI Query Language), 58
WSDL (Web Services Description Language), 325
X – Y – Z
XML (eXtensible Markup Language), 297
bulk loading data, 310–312
FOR XML clause, enhancements to, 320–323
indices, 60
namespaces, 300
OPENROWSET function, 297
primary indices, 316–318
saving data to file, C# stored proce- dure, 341–343
schema collections, 309
secondary indices, 316–318
special characters, 299
special tags, 299
textual nature of, 298
typed XML. See typed XML
valid documents, 299
web services, 323–325
well-formed documents, 298
xml data type, 59–60, 165, 266, 301
associating with XSD schema, 305
example, 302–303
full-text indices, 319
indexing, 266
INSERT statements, 303–304
methods
exist() method, example, 314
query() method, example, 313
value() method, example, 314
modifying, 319–320
primary XML indices, 266–267
property promotion, 304–305
queries, optimizing, 268–269
secondary XML indices, 267
showplans, 123–125
variables, declaring, 304
XMLA (XML for Analysis), 395
Discover method, 403
Execute method, 403
support for in Analysis Services, 403–405
XPath, 301
XQuery, 8
constructing XML, example, 315–316
expressions, 312
FLWOR expressions, 315–316
functions, 316
operators, 316
tracing, 316
xml data type methods, 313
XML DML, 319–320