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 | Z
Symbols
# (number character), for temporary stored procedures, 188
##MS_AgentSigningCertificate##, 256
##MS_SQLAuthenticatorCertificate##, 256
##MS_SQLReplicationSigningCertificate# #, 256
##MS_SQLResourceSigningCertificate##, 256
$PARTITION function, 123
@@error, 7
@@IDENTITY function, 79
@@NESTLEVEL system function, 92
@@ROWCOUNT variable, 297
@body parameter, for sp_send_dbmail stored procedure, 422
@query parameter, for sp_send_dbmail stored procedure, 422
0 (zero), as return value from stored procedure, 191
A
abstraction layer, view as, 154
acceptance testing, 557
access control, stored procedures for, 187
Accumulate function, 217, 221
ACID properties, of SQL Server transactions, 456
Active Directory security, 373
ActiveX Script subsystem, proxy account for, 233
ActiveX Script task, 556
ad hoc batches, disabling, 230
ad hoc reports, 448
by end users, 22
Add Database Reference dialog box, 195
ADD SIGNATURE statement, 409
ADD SIGNATURE TO statement, 281
Add/Rename/Delete rights, to database object, 397
ADF. See application definition file (ADF)
Adjusted Average function, 220
administrative permissions, for database roles, 236
Administrator, executing test query as, 411
ADO, distributed transaction support by, 17
ADO.NET
DataSet object, 49, 134
distributed transaction support by, 17
AdventureWorks sample database, 29
AdventureWorksDW sample database, 29
AFTER triggers, 90, 210
Deleted table for, 211
for summary table, 466
Inserted table for, 211
order for, in integrity check, 91
aggregate functions, in indexed view, 115
aggregate queries, and indexes, 102
aggregating details, 45
aggregation strategies, 142
aggregations in OLAP system, 23
alerts. See also SQL Server Agent alerts
Notification Services for, 20
SQL Server Agent for responding to, 10
aligned indexes, 120
ALLOW_SNAPSHOT_ISOLATION option, 457
ALTER ANY LOGIN server-level permission, 257
ALTER ANY ROLE permission, 257
ALTER ANY USER permission, 257
ALTER AUTHORIZATION statement, 262
ALTER DATABASE command, 457
ALTER DATABASE statement, 281
triggers and, 91
ALTER INDEX statement, 106
triggers and, 91
ALTER OBJECT permission, permissions implying, 268
ALTER permission, 270
ALTER PROCEDURE statement, 192
ALTER SCHEMA command, 261
ALTER SCHEMA permission, 269
ALTER statement
triggers and, 7, 210
for principals, 254
ALTER TABLE statement, 76
PERSISTED keyword in, 80
altering table for transactional replication, 376
Analysis Services (SSAS). See SQL Server Analysis Services (SSAS)
analytical applications, scaling out, 141
ANSI_NULLS option, 114
ANSI-92 standard permissions, 271
application cache, 49
application definition file (ADF), 495–499
event provider configuration in, 507
for creating database for application, 499
for defining notification class, 500
for index creation on subscription class, 523
for Notification Services application configuration, 503
for Notification Services distributor configuration, 513
for Notification Services generator configuration, 512
for subscription schemas, 522
nonhosted event provider configuration in, 508
subscription class name and filegroup in, 521
application queries, scaling, 139
application role, 229
application security, test design for, 310, 312
applications list, for Notification Services instances, 495
Apply Label dialog box, 402
APPLY operator, 7, 276
and indexes, 102
archive for old data, 49, 139
argument encryption, for Notification Services instances, 497
arity of fact types, 35
assembly permissions, 198
assumptions for testing script, setup testing script for, 294
asymmetric key,
encryption, 245
in master database, 281
ASYNC_IO_COMPLETION wait type, 336
asynchronous processing, 14
atomic values, in first normal form, 39
attaching database files, with scripts, 364
attacker, potential, 226
attributes
constraints for, 306
domain of possible values, 59
of entities, 36
ORM and, 33
testing values of, 306
user-defined aggregate, 219
audience, report uses by, 445
audit trail
creating, 378
triggers for, 210, 215
auditing
Analysis Services, 243
benefits of, 240
database design and, 240
DML events, 242
failed logins, 228, 240
logs from, retention period, 241
methods for SQL Server activity, 379
review of data from, 241
for security events, 242
Service Broker for, 15
strategy considerations, 240–241
authentication, 227
certificates for, 245
in DatabaseMail, 421
modes for SQL Server, 227, 231
for SSRS, 234
authenticators, 280
authorization, 227
strategy, 228
AUTHORIZATION keyword, 262
automatic activation, in Service Broker, 15
automation, 289
of performance test execution, 301
auto-numbering, with Identity property for column, 79
AWBuildVersion table, 414
B
backup and restore, for deploying permissions, 374
backup and restore database, 365
to SQL Server Express, 381
balanced trees (B-trees), 105
baseline, 302. See also performance baseline
basic authentication for Web Services, 431
Bcp.exe command prompt utility, 143, 144, 147
BEGIN DISTRIBUTED TRANSACTION statement, 17
BEGIN-END block, 176
benchmark statistics
best practices, 350
plan for tracking, 348–352
-continued testing and performance measuring, 349
-setting goals, 348
best practices, 372
for benchmark statistics, 350
for creating performance objectives, 326
for DatabaseMail, 233
for queries, 139
for replication, 231
for schemas, 260
BETWEEN operator, and indexes, 102
BIDS. See Microsoft Business Intelligence Development Studio (BIDS)
Bigint data type, 60
binary associations, 35
binary facts, 35
binary large objects (BLOBs), XML data type as, 128
binary serialization, 68
binary strings, 61
bit data type, 60
bottlenecks, 343
disk I/O-related, scaling up and, 344
bottom-up hierarchy, for objects, 273 breakpoint
for debugging control flow, 555
for debugging stored procedure, 197
Browser role, 235
brute-force attacks, mixed mode authentication and, 228
B-trees (balanced trees), 105
budget, and performance objectives creation, 327
Bulk Insert task, 140, 565
efficiency vs. INSERT statement, 143
BULK_LOGGED recovery model, 119, 143
business cost reduction, from tracking benchmark statistics, 349
business intelligence applications, data flow for, 50
Business Intelligence transformations, 543
business requirements, and auditing, 241
business tasks
selecting services to support, 5
SQL Server Agent for, 10
bytes, for string data types, 60
C
C# .NET, 134
for stored procedures, 166
C2 auditing, 379
cached data, Service Broker for maintaining, 15
CALLER, executing as, 170, 281
candidate keys, 39
capacity planning, 328
predictive analysis, 329
transactional cost analysis, 328
cardinality, 35
CAS. See Code Access Security (CAS)
CASCADE option
for DENY statement, 272
in REVOKE statement, 272
Cascade rule, for parent and child tables, 78
cascading table changes with triggers, 90
CASE expression, for indexed view base query, 116
CATCH statement, 201
certificates, 245
creating, 408
creating user account from, 410
change management. See also performance changes
and database for testing, 295
historical record of database schema changes, 378
history maintenance, 413
for source code, 395
testing as part, 378
tracking changes to groups of objects, 413–414
changeset, 392, 413
adding comments to, 394
Char data type, 61
Check constraint
for partition key, 120, 121
for XML namespace, 131
CHECK constraints, 84, 306
CLR UDFs for, 194
for executing UDFs, 174
in partitioning column, 157
Check In dialog box, 391
displaying, 393
Check Out/Check In rights, to database object, 397
Checked Out For Edit status
removing, 393
in Solution Explorer (SSMS), 392
check-in history of the item, 393
child table, relationship to parent table, 77
Choose Folder In Team Foundation Server dialog box, 390
Choose Item Version dialog box, 401
chronicles, 506, 523
for subscription history, 527
chunked update, UPDATE statement support for, 62
class creation when creating CLR data type, 68
client applications, for accessing Web service, 432
clipboard, copying item to, 392
CLR (Common Language Runtime)
enabling, 195
integration and consumption of Web methods, 147
performance, 133
and scalability, 139
stored procedures, 166, 187, 195–198
-deploying, 197
-programming, 195
-SSAS support for, 236
-testing and debugging, 197
-v. T-SQL, 133
CLR data types
basic operations, 67
best practices, 70
CLR functions
in indexed view, 115
user-defined, 178
CLR integration
security, 230
turning on, 275
CLR scalar functions, creating, 205
CLR user-defined aggregates, 217–222
CLR user-defined types (UDTs), 66
Check constraint and, 85
Clustered Index Scan operator, 111, 117
clustered indexes, 105–108, 463
creating, 109, 111
nondeterministic functions and, 206
on view vs. on table, 155
unique, for view, 155
cmd (batch) file, for unit testing scripts, 296
Codd, Edgar F., 31
Code Access Security (CAS) model, 8, 230
permission groups, 8
security sets, 231
code coverage
goals for, 317
meeting test requirements, 317
test design for, 317–320
test to validate, 317
code injection, threat of, 277
coding practices, 372
color for version differences, 405
color codes in BIDS, 554
column charts, for data viewer, 556
column data types and sizes, 59–64
best practices, 64
choosing data types, 65
cursor data type, 63
datetime data types, 61
numeric system data types, 59
Sql_variant data type, 63
string data types, 60
system data types, 59
table data type, 63
Uniqueidentifier data type, 63
XML data type, 62
column width, and clustered indexes, 463
column-level security, views to implement, 244
columns
computed, 80
Identity property, 79
included, indexes with, 107
obtaining information about names from OLE DB provider, 149
repeating group, and first NF, 40
selecting for index, 103
variable-length, 75
COLUMNS_UPDATED function, 92
comments for files, 392
commit phase for transaction, 17
COMMIT TRANSACTION statement, 17
COMMIT WORK statement, 17
Common Criteria Compliance, 379
common language runtime. See CLR (Common Language Runtime)
common table expressions (CTEs), 6
Compact Edition of SQL Server 2005, 138
companion CD, Visual Basic .NET code in, 70
Compare dialog box, 393
Component Object Model (COM), 134
composite keys, for indexes, 107
composite indexes, 106–107
computed columns, 80, 463
indexes for, 113
for notification data, 527
updating index on, 113
user-defined function for, 174
computer resources, estimating for application performance objectives, 328
Concatenate aggregate function, user-defined, 217
conceptual schema, creating, 31
Conceptual Schema Design Procedure (CSDP), 34
concurrent transactions, database support for, 329
configuration arguments, encryption, 497
configuration management audits, for baselines, 333
Configuration Manager, for enabling SQL Server Agent, 9
CONNECT permission, 270
revoking, 256
Connect To A Team Foundation Server dialog box, 390
Connect To Server dialog box, 361, 388
connection managers, 533–534
Connection Properties dialog box, 452
connection string, 533
connections between computers, encrypting, 231
Connections logical folder, 388
consolidating data, Service Broker for, 15
constraints, 59
in domains, 67
on performance objectives, 325, 327
program code for implementing, 90
purpose of, 127
trigger to implement, 210
unit testing of, 289, 306
containers in SSIS packages, 537–539
For Loop, 538
Foreach Loop container, 539
sequence, 538
CONTAINS predicate, 108
CONTAINSTABLE function, 108
Content Manager role, 235
continuous event provider, 507
control flow
debugging, 559
in SSIS packages, debugging, 554
control flow engine, 533
CONTROL permission, 268, 270
Copy Database Wizard (SSMS), 357–361
configuring logins for, 375
customizing SSIS package from, 361–364
for deploying permissions, 374
copying database files, 364
corporate audiences, SSRS solution design for, 445
cost of operations, in transactional cost analysis, 328
COUNT_BIG (*) function, 115, 116
counter logs, 337
covered query, 108, 464
covering index, 526, 527
CPU utilization, target, based on application needs, 324
CREATE ASSEMBLY statement, 198
CREATE DATABASE command, 458
CREATE DATABASE script, for unit testing, 295
CREATE DATABASE statement, triggers and, 91
CREATE ENDPOINT statement (T-SQL), 431
CREATE FUNCTION statement, 204, 217
CREATE INDEX statement, 108
triggers and, 91
with INCLUDE option, 465
CREATE PARTITION FUNCTION, 121
CREATE PROC statement, 199
CREATE PROCEDURE statement, 188, 409
CREATE statement, 262
DDL triggers and, 210
with DROP, vs. ALTER, 192
for principals, 254
triggers on, 7
CREATE STATISTICS statement, 103
CREATE TABLE permission, 269
CREATE TABLE statement, 75
DDL trigger to prevent, 213
event notification for, 246
example, 81
PERSISTED keyword in, 80
CREATE TRIGGER statement, 212
CREATE TYPE command, 66
CREATE UNIQUE CLUSTERED INDEX statement, 117
CREATE VIEW statement, 153
WITH CHECK OPTION clause, 244
cross-database ownership chains, and security, 276
CSDP (Conceptual Schema Design Procedure), 34
CTEs. See common table expressions (CTEs)
cubes for SQL Server Analysis Services, permissions for, 236
cumulative grants, 267
current state, in fully normalized schema, 45
cursor data type, 63
custom constraints, 306
validating, 307
custom delivery protocols, for Notification Services
instances, 495
CXPACKET wait type, 336
D
data
test to validate consistency, 307
for unit testing setup, 296
validating
-before transaction, 193
-stored procedures for, 187
data access layer, 90
data caching
obsolescence, 148
for scaling out, 141
data distribution options, 417
DatabaseMail, 419–424
-accounts, 421
-architecture, 419
-configuring, 423
-enabling, 420
-profiles, 421
-security, 421
-sending messages, 422
-testing, 424
Notification Services, 438–441
-architecture, 438
-defining applications, 440
-scale-out options, 439
-vs. DatabaseMail, 441
-vs. Reporting Services, 440
Reporting Services component (SSRS), 434–437
-data-driven subscriptions, 435
-delivery options, 434
-subscriptions, 434
SQL Server Agent alerts, 429
-alert definition, 425
-checking for running, 428
-operator definition and notification, 427
-table for drop-table WMI event data, 428
-user-defined events, 428
Web Services, 430
-creating and defining, 430
-guidelines for using, 432
-security, 431
data flow in SSIS packages, 535
debugging, 555, 559
data flow architecture design, 49–51
for business intelligence applications, 50
for online transactional processing (OLTP), 49
Data Flow editor, 547
data flow engine, 533
data flow transformations, 541, 544
data integrity, 125
concern with XML, 127
data manipulation language (DML) triggers, 47
data mining, 22, 570
Data Mining Extensions (DMX) language, 135
data modeling, 29, 31
key steps and best practices, 32
data paths, 541
data preparation tasks, in SSIS packages, 535
data protection, 244
data retrieval, excessive, 342
data sources, creating from Report Manager, 449
data transformation design, 465–468
Data Transformation Services (DTS), 531
data types, 59–64
best practices, 64
built-in, 59
choosing, 65
cursor data type, 63
datetime data types, 61
deprecation, 7
for stored procedure parameters, 167
numeric system data types, 59
returned by UDFs, 179
Sql_variant data type, 63
string data types, 60
system data types, 59
table data type, 63
Uniqueidentifier data type, 63
user-defined, integrated CLR for, 9
XML data type, 62
data validation, stored procedures for, 187
data warehousing (DW), 3, 23
Integration Services for, 24, 50
for scaling out, 142
services for, 5
sliding-windows scenarios in, 119
SSIS for, 533
database-access strategy, 252
database benchmarking strategy, 323
database consistency checker commands, 335
database design
logical phase of, 31
for performance. See also indexes
systematic approach, 31–36
Database Engine component, 3
database files, detach and attach method, with scripts, 364
database-level principals, 228
database mirroring, 458
database modeling, tool for, 33
database objects
source code of, rules for viewing, 258
two-part names for, 261
unit testing of, 289
user rights to, 397
database projects
adding to source control, 389–393
removing item from, 392
Solution Explorer to view empty, 387
source-controlled, 393–395
SSMS to create, 380
Visual SourceSafe–controlled, opening, 398
database replication, 459
database roles, 233
and schemas, 261
for SQL Server Agent, 232
login in, 255
nesting, 262
permissions for, 236
validating existence, 310
database schema, hiding with view, 154
database snapshots, 50, 458
and deployment planning, 357
database source code, 387
database state, object changes and, 378
Database Tuning Advisor, 138, 527
database user accounts, restricted, 233
database users, creating, 255, 263
DatabaseMail, 10, 141, 419–424
accounts, 421
architecture, 419
best practices, 233
configuring, 423
enabling, 420
vs. Notification Services, 441
profiles, 421
security, 421
sending messages, 422
and SQL Server Agent, 232
testing, 424
DatabaseMail Configuration Wizard dialog box, 423
DatabaseMailUserRole database role, 11
databases
calculating capacity, 329
creating test, 110
stored procedures storage in, 187
marking as trustworthy, 281
maximum number of objects, 154
monitoring, vs. monitoring operating system level, 337
for Notification Services instances, 494
restoring state after testing, 295
Service Broker message storage in, 15
testing impact of modification, 287
data-change strategy, creating, 378
data-control language (DCL) elements, for controlling permissions, 267
data-definition (DDL) events
event notifications for auditing, 246
triggers on, 7
data-driven subscriptions, 435
data-modification language (DML) triggers, 90–92
categories, 90
disallowed T-SQL commands, 91
example, 93
nested, 92
security, 92
DataSet object (ADO.NET), 134
dates, support for, 64
datetime data types, 61
DB Library, distributed transaction support by, 17
DBCC CHECKIDENT command, 79
DBCC SQLPERF command, 145
DBCC WAITSTATS, 336
db_dtsadmin user, 237
db_dtsltduser user, 237
db_dtsoperator user, 237
dbo.GetTop10SalesPeople stored procedure, 291
dbo privileged database user, 256
dbo.ImplyingPermissions function, 268
db_owner role, 256, 257
db_securityadmin role, 257
DDL events. See data-definition (DDL) events
DDL triggers, 210, 242
auditing, 379
creating, 212
for populating tables for reports, 465
deadlocks, 342
debugging
CLR stored procedures, 197
SSIS packages, 553
-control flow, 554, 559
-data flow, 555, 559
-script code, 556
declaring variables in SSIS, 535
decomposition in normalization, 38
default, validating by UDF, 308
Default constraint, 84
example, 87
for executing UDFs, 174
order for, in integrity check, 90
DEFAULT keyword, 178
default mapping, for linked servers, 232
default values
for input and output parameters, 168
for UDF parameters, 180
Delaney, Kalen, Inside Microsoft SQL Server 2005, 75
DELAYFOR command, to slow down query, 486
DELETE permission, 270
DELETE statement (SQL)
common table expressions for, 6
DML triggers and, 210
OLE DB Command transformation and, 542
OUTPUT statement operator, 243
Deleted table
accessing information from, 243
for triggers, 211
deleted-rows table, trigger to create, 214
deleting stored procedures, 193
deliverables of programming project, unit testing of, 289
delivery channel
for Notification Services applications, 21
for Notification Services instances, 496
denial of service, 226
denormalization, 345, 460
for design optimization, 45–47
maintaining data after, 46
triggers for maintaining data, 210, 460
and update anomalies, 38
DENSE_RANK function, 6
density, 103
DENY CONNECT statement, 230
DENY statement, for controlling permissions, 267, 271
departmental audiences, SSRS solution design for, 445
dependencies, external, CLR objects and, 8
deploying CLR stored procedures, 197
deployment of database
planning for. See planning for database deployment
practical considerations, 372–383
-audit trail, 378–379
-change control, 380
-data-change strategy, 378
-object-change strategy, 376
-project-management methodology, 380
-security, 372–375
to SQL Server Express, with backup and restore, 381
deprecation
of ActiveX code, 556
of data types, 7
and exam questions, 66
extended stored procedures, 134, 166
of partitioned views, 119
of SQL Mail, 10
derived tables, vs. common table expressions, 6
deserialization, 70
design process, troubleshooting performance issues early, 325
destination database, configuring in wizard, 359
Destroy rights, to database object, 397
detach and attach method, for database transfer, 359
Detach Database dialog box, 370
detaching database files, with scripts, 364
detailed information, hiding, with views, 473
determinism
of expressions, 113
-for persisted computed columns, 464
of functions, 206
Developer Edition of SQL Server 2005, 138
development environment, schemas to control, 261
Differences dialog box, for versions, 405
Digest authentication, for Web Services, 431
digest delivery, by SSNS, 141
digital signatures, 244
asymmetric key encryption for, 245
for packages, 237
dimension tables, 461
creating, 545
dimensions, permissions for, 236
disclosure, of information, 226
discrete tests, in transactional cost analysis, 328
disk drives, spreading database across multiple, 139
disk I/O-related bottlenecks, scaling up and, 344
display phase of reporting execution process, 482
distributed partitioned view, 119, 161
for scaling out, 140
distributed queries, implementing, 146
distributed system, reliability in, with Service Broker, 15
distributed transactions, 16, 538
statements starting, 17
Distribution Agent, account for, 231
Distributor
for replication, 459
role for accounts, 233
DML events, auditing, 242
DML triggers, 210, 224
creating, 210
-syntax, 212
for auditing DML events, 242
Inserted and Deleted tables for, 211
documentation
for auditing solution, 241
of changes to test environment, 335
performance testing, 302
domain integrity, 84–86
Check constraints, 84–86
Default constraints, 84
implementing, 86
domain-key normal form (DKNF), 67
domains, 59, 66
constraints in, 67
DROP DATABASE statement, triggers and, 91
DROP INDEX statement, triggers and, 91
DROP PROCEDURE statement, 193
DROP statement
with CREATE, vs. ALTER, 192
DDL triggers and, 210
for principals, 254
triggers on, 7
DTExec command-line utility, 553
DTExecUI.exe, 553
DTS (Data Transformation Services), 531
DW. See data warehousing (DW)
dynamic expression, for data source, 449
dynamic management views (DMV), 335
dynamic string concatenation, 277
E
e-commerce applications, 3
Edition of SQL Server 2005, 138
e-mail. See also DatabaseMail
user-defined function for extracting, 207
e-mail addresses
CLR function to validate against regular expression, 88
data type example for, 68–70
event notifications to, 427
embedded reporting, 447
employees, as security risk, 226
encryption, 240, 244–246
certificates for, 245
of configuration arguments, 497
database design and, 240
of source code, 408–412
end users
ad hoc reports, 22
OLAP client tool for, 23
endpoint connect permissions, limiting, 230
endpoints, security for, 256
enforcement of rules, 227
entities, 74–80
ANSI rules for enforcing relationships, 77
attributes of, 36
computed columns, 80
design and creation, 74–76
entity integrity, 76
Identity columns, 79
properties, 35
referential integrity, 77
relationships between, 35
supertypes and subtypes for generalization levels, 52
with structured data, 59
entity key, 36
entity objects in ORM, 33
entity relationship (ER) diagram of Production set of tables, 163
entity relationship (ER) method, 31
and logical model, 35
diagrams, 36
entity sets, 35
Entity-Attribute-Value (EAV) table approach, 128
ER. See entity relationship (ER) method
error handling, 7
in stored procedures, 169
in user-defined functions (UDFs), 180
error message
no index content, 24
number 1919 “Column...is type that is invalid for use as key column”, 81
from SQL Server, notifications of, 425
for transaction rollback, 91
error output from data sources, 541
evaluation element, in For Loop container, 538
event classes for Notification Services, 506
creating with ADF, 510
defining, 506
event providers definitions, 507–509
-hosted providers, 507
-nonhosted providers, 508
File System Watcher event provider, 509
standard event providers, 508
event notifications, 242
for auditing DDL operations, 7
-practice, 246
Notification Services for, 20
event providers
in Notification Services, 20, 439
EventCollector class, 508
EVENTDATA function, 242
EventLoader class, 508
EventLoader method, 508
exam questions
answer choices, 425
data dissemination options, 25
data distribution options, 142
data warehousing and OLAP, vs. reporting solution, 24
deprecated features in, 66
on full-text indexes, 129
on object permissions, 271
on securing Reporting Services and Analysis Services, 234
tip on answering, 5
on unit tests, 289
Excel 2007, linking to, 148
executable, for DatabaseMail, 419
EXECUTE (EXEC) statement, 167
for stored procedures, 189
EXECUTE AS clause, 244
for stored procedures, 170
for validating for permissions, principals and roles, 310
EXECUTE AS command, 265, 280
Execute Package tasks, 567
EXECUTE permission, 270
T-SQL to grant, 410
execution context, 280, 281
checking for module, 283
defining, 282
validating for permissions, principals and roles, 310
execution status value
from stored procedure, 169
exist() XML data type method, 129
explicit column list, vs. SELECT * queries, 140
Export Package dialog box, 362
exporting
real-time methods for, 146
SSIS package, 361
Express Edition of SQL Server 2005, 137
with Advanced Services, 138
expressions
defining column as, 80
deterministic, 113
Extended ER model, 31, 36
extended properties, for change history, 413
extended stored procedures, 134, 166
Extensible Stylesheet Language Transformations (XSLT) content formatter, 21
extents, 74
EXTERNAL ACCESS ASSEMBLY permission, for login, 281
external dependencies, CLR objects and, 8
external resources, integrated CLR for access, 9
external schema, 32
external systems. See also interoperability with external systems
EXTERNAL_ACCESS CAS security set, 231
EXTERNAL_ACCESS permission set, 8, 147, 198, 236
extraction, in ETL process, 467
extraction, transformation, and loading (ETL) solutions, 465
creating dataflow, 547
creating workflow, 546
design, 467
design patterns, 565
going beyond process, 569
in-memory pipeline, 569
with SSIS, 566–569
-in-memory, 468
transformation packages in, 567
extranet solutions, SSRS solution design for, 446
F
fact table, 461
failed logins, auditing, 228, 240, 379
failover accounts, for DatabaseMail, 421
failover cluster, Notification Services generator on, 512
fail-safe operator, 427
failure of unit testing, 291
feature test, 290
federated database servers, with partitioned views, 161
file permissions, 397–399
and source code access, 395
File System Watcher, 20
File System Watcher event provider, 508
configuring, 509
filegroups, spreading database across multiple, 139
File_IO_Stats tool, 314
FileSystemWatcher class, 508
filtered rows equivalent, with stored procedure, 476
filtered views
impersonation for, 476
and scalability, 476
firewall, 230
first normal form, 38
fixed data sources, 449
fixed server roles, adding users, 255
fixed-string data types, 64
Flat File source, creating connection, 549
FLATTENED keyword (DMX), 135
Float(n) data type, 60
floating-point operations, 206
fn_trace_gettable system function, 338
folder permissions, 398
folders, labels for, 400
For Loop container in SSIS packages, 538
FOR XML . . . XMLSCHEMA, 129
Foreach Loop container in SSIS packages, 539
foreign key, 41, 53, 77
and indexes, 463
Foreign Key constraint, 77, 78, 82, 306
creating index for, 109
for enforcing range-integrity rule, 85
example, 87
order for, in integrity check, 90
test to validate, 307
validating, 306
forest, 53
Format attribute, 219
free models, as starting point, 33
FREETEXT function, 108
FREETEXT predicate, 108
FROM clause, for executing UDFs, 173
FTS. See Full-Text Search component (FTS)
Full Recovery model, 526
full-text indexes, 108
on string data types, 62
on XML columns, 129
Full-Text Search (FTS), 4
in Express Edition with Advanced Services, 138
G
General Principles for the Assessment of Certification Bodies for Product Certification (C2), 240
generalization, 52
generators, role for accounts, 233
GETDATE function, 113
globally unique identifier (GUID), 63
goals, for code coverage, 317
grain of audit, 240
GRANT CONNECT statement, 230
GRANT OPTION FOR, in REVOKE statement, 272
GRANT statement
for controlling permissions, 267, 271
security impact, 92
grants, cumulative, 267
graph, 53
grids, for data viewer, 556
GROUP BY operator
for executing UDFs, 174
and indexes, 102
grouping
principals, 229
sets of tasks, sequence container for, 538
users, 262
guest principal, 255
guest user account, 255
permissions for, 256
gzip compression, 430
H
hackers, 226
hardware
failure, and performance change, 342
for scaling up, 137
hardware RAID, 139
hash value, 561
HAVING clause, for executing UDFs, 173
Health Insurance Portability and Accountability Act (HIPAA), 240, 378
heap, 105
hiding
detailed information, with views, 473
sensitive columns, with views, 474
hierarchies, 52
for objects
-bottom-up, 273
-top-down, 272
modeling, 53
resolving, 54
XML data type for, 63
histograms, for data viewer, 556
historical data, snapshots for, 458
history
and fully normalized schema, 45
for Notification Services applications, 502
for Notification Services instances, 497
hosted event providers, 507
HTTP endpoints, 13
creating, 431, 432
security, 431
HTTP listener, 14
HTTP. See Hypertext Transfer Protocol (HTTP)
http.sys kernel mode driver, 14
http.sys listener, 430
HTTPS. See Hypertext Transfer Protocol Secure (HTTPS)
Hypertext Transfer Protocol (HTTP), 13, 146, 430
I
I/O load, 344
I/O operations, impact on performance, 342
IBinarySerialize interface, 68, 219
ICF. See Instance Configuration File (ICF)
IDEF1X. See Integration Definition for Information Modeling (IDEF1X) method
IDeliveryProtocol interface, 495
IDENT_CURRENT function, 79
IDENT_INCR function, 79
IDENT_SEED function, 79
Identity columns, 79
IDENTITY function, in SELECT statement, 79
IDENTITY_SCOPE function, 79
IIS. See Microsoft Internet Information Services (IIS)
image data type
Check constraint and, 84
separate page for storing, 75
impersonation, 170
for filtered views, 476
implicit context switching, 280
testing, 283
Import and Export Wizard, 365–367
Import Package dialog box, 363
importing
data from text files, 140
real-time methods for, 146
using INSERT ... SELECT statement, 143
IN operator, building list dynamically, 275
INCLUDE keyword, 527
included columns, 465
indexes with, 107
incompleteness, normalization to eliminate, 38
inconclusive unit testing results, 291
Index Allocation Map (IAM) pages, 105
Index Seek operator, 111
indexed views, 113–116, 155–156, 162
base query, 116
creating, 117
exercise, 164
prerequisites, 114
SELECT statement for, 114
indexes, 101–103
aligned, 120
column selection for, 103
for computed column, 80, 113
creating, 108
design, 105–109, 463
-clustered and nonclustered indexes, 105–108
-computed columns and, 463
extents for, 74
full-text, 108
-on string data types, 62
guidelines for, 101
with included columns, 107
logical fragmentation of, 105
outdated statistics, and performance change, 342
and performance, 344
for subscription class, 523
unique and composite, 106–107
updating on computed column, 113
information disclosure, 226
INFORMATION SCHEMA principal, 256
Init function, 217, 221
initialization element, in For Loop container, 538
inline functions, table-valued user-defined functions as, 179
inline scalar UDFs, 175
inline table-valued functions, 204
Input parameters, for stored procedures, 189
input parameters
for stored procedures, 168
for user-defined functions, 180
INSERT ... SELECT statement (T-SQL), import using, 143
INSERT permission, 270
INSERT statement (SQL)
Check constraints for, 84
common table expressions for, 6
Default constraints and, 84
DML triggers and, 210
efficiency vs. bulk insert, 143
OLE DB Command transformation and, 542
OUTPUT statement operator, 243
Inserted table
accessing information from, 243
for triggers, 211
Inside Microsoft SQL Server 2005, 75
Instance Configuration File (ICF), 493
custom delivery protocols defined in, 496
database system defined in, 494
defining instance name in, 493
delivery channel defined in, 496
encryption defined, 497
list of applications defined in, 495
parameters in, 498
schema name defined in, 494
version number added with, 497
instance database, NSVersionInfo table, 497
INSTEAD OF triggers, 90, 210
Deleted table for, 211
Inserted table for, 211
order for, in integrity check, 90
purpose of, 91
Int data type, 60
Integrated authentication, for Web Services, 431
Integration Definition for Information Modeling (IDEF1X) method, 31
ER model, 36
Integration Services (SSIS). See SQL Server Integration Services (SSIS)
integration testing, 557
integrity checks, order of, 90
intelligent data cleansing, Integration Services packages for, 25
intermediate levels, in B-trees, 105
Internet, replication over, 16
Internet Information Services (IIS), 13
interoperability with external systems, 146–149
asynchronous methods, 147
synchronous methods, 146
IO_COMPLETION wait type, 336
IsByteOrdered attribute, 81
IsInvariantToDuplicates attribute, 219
IsInvariantToNulls attribute, 220
IsInvariantToOrder attribute, 220
IsNull property, 68
IsNullIfEmpty attribute, 220
isolation levels, in SQL Server, 457
iteration element, in For Loop container, 538
iterative process, data modeling as, 31
J
JOIN operator, 276
and indexes, 102
join performance, table partitioning and, 120
joins, 45
JScript, 556
JScript Compact Profile (ECMA 327), 556
K
Kass, Steve, 242
Kerberos authentication, 230
for Web Services, 431
Key Lookup operator, 111
key performance indicators (KPIs), 350
monitoring, 509
key scenarios, absence of documentation, 327
KISS principle (“Keep It Simple, Simon!”), 67
L
Label dialog box, 400
labels for stored version, 400
languages, 133–136
CLR and T-SQL vs. other, 134
CLR vs. T-SQL, 133
practice choosing, 135–136
large data types, 62
storage, 75
large object data (LOBs), 74
HTTP endpoints and, 14
separate page for storing, 75
LATCH_x wait type, 336
latency, 343
LCK_x wait type, 336
leaf nodes
in B-trees, 105
in nonclustered index, 106
least-privilege concept, 227, 231
LIKE operator, and indexes, 102
linked server, 146
creating, 148
security for, 232
linking to Excel 2007, 148
load testing, 313, 326
loading phase, in ETL process, 467
LOBs. See large object data (LOBs)
local data storage, 372
local partitioned view, 161
local stored procedures, 187
Locals pane display, for debugging, 555
locking contention, 139
snapshots to reduce, 50
Log Reader Agent, account for, 231
log shipping, 458
logic, stored procedures to consolidate, 187
logical folders, for project, 388
logical fragmentation of index, 105
logical phase of database design, 31
and entity relationship (ER) method, 35
login token, 280
logins
auditing, 242, 379
creating, 263
-for test user, 408
directly in database role, 255
synchronizing after moving database, 373
LOGMGR wait type, 336
logs
for DatabaseMail, 419
-user-defined error messages, 428
from auditing, retention period, 241
lookup tables
for Check constraint, 85
creating, 86
Lookup transformation, vs. Merge Join transformation, 543
M
maintenance cost reduction, from tracking benchmark statistics, 349
maintenance plans, SSIS packages for creating, 569
maintenance tasks
SQL Server Agent for, 10
in SSIS packages, 537
malicious code, in triggers, 92
Management Studio. See SQL Server Management Studio (SSMS)
manual testing, for SSIS package, 558
MAPI. See Microsoft Messaging Application Programming Interface (MAPI)
master database, asymmetric key in, 281
master package, for ETL packages, 567
MAX length specification, for data types, 6
max specifier, 62
MaxByteSize attribute, 220
maximum size of row, 74
MDAC. See Microsoft Data Access Components (MDAC)
Measure SQLServer
Buffer ManagerBuffer Cache Hit Ratio counter, 315
median performance, measuring, 334
member tables, for partitioned view, 157
memory, for scaling up, 137
MemoryAvailable Bytes counter, 315
Merge Agent, account for, 231
Merge function, 217
Merge Join transformation, 550
vs. Lookup transformation, 543
Merge method, 221
merge replication, 16, 19, 49
merging data, Integration Services package for, 25
message queuing, scaling out with, 141
metadata
for DatabaseMail, 419
management, 447
in Notification Services instances, 494
security, validating, 311
visibility, 257
Microsoft .NET Framework, CLR integration, 8–9
Microsoft Business Intelligence Development Studio (BIDS)
color codes, 554
creating SSIS package, 367, 533, 545
for debugging SSIS package, 554
for report deployment, 486
for running SSIS package, 553
Report Wizard, 451, 453
Microsoft Business Intelligence Development Studio (BIDS) Report Designer, 22
creating shared data source from, 450
Microsoft Business Intelligence Web site, 20
Microsoft Data Access Components (MDAC), 13, 430
Microsoft Distributed Transaction Coordinator (MSDTC), 538
Microsoft Extended Stored Procedure application programming interface (API), 134
Microsoft Full-Text Engine for SQL Server (MSFTESQL) service, 108
Microsoft Internet Information Services (IIS), 13
Microsoft Message Queue (MSMQ) service, 15, 141
Microsoft Messaging Application Programming Interface (MAPI), 232
Microsoft Office 12.0 Access Database Engine OLE DB provider, 148
Microsoft Office Business Scorecard Manager 2005, 447
Microsoft Office SharePoint Server, 447
Microsoft Office Visio, 30
Microsoft Solutions Framework (MSF) process model, 31
Microsoft Source Code Control Interface (MSSCCI) provider, installing, 389
Microsoft SQL Server, extending functionality, 185
Microsoft SQL Server Desktop Engine 2000 (MSDE), 137
Microsoft Visual Studio 2005, 30
Microsoft Visual Studio 2005 Team Edition for Database Professionals, 381
Microsoft Visual Studio 2005 Team Foundation Server products, 380
Microsoft Windows authentication, 227, 231, 373
linked server support for, 232
Microsoft Windows Server 2003, 64-bit edition, 137
middle-tier business logic, HTTP endpoints and, 14
migration tool, SSIS as, 533
mirroring, and deployment planning, 357
Miscellaneous logical folder, 388
mixed extents, 74
mixed mode authentication, 227
Mixed Mode security, 408
modeling hierarchies, 53
module
execution context for, 281
-changing, 282
signing, 281
Money data type, 60
moving objects between schemas, 261
msdb database, 419
saving package to, 237
MSDTC service, 17
MSFTESQL. See Microsoft Full-Text Engine for SQL Server (MSFTESQL) service
MSMQ service. See Microsoft Messaging Queue (MSMQ) service
multicast delivery, by SSNS, 141
multidimensional database, 463
Multidimensional Expressions (MDX), 135, 236
multistatement table-value functions, 204
multistatement user-defined functions (UDFs)
scalar, 175
table-valued, 176
-data types returned by, 180
My Reports role, 235
N
name resolution
for objects, 260
testing, 264
Named Pipes, endpoint for, 256
names
of instances of Notification Services, 493
of items checked out for editing
-changing, 393
for local stored procedures, 187
passing parameter values by, 190
qualifying inside stored procedures, 194
for stored procedure parameters, 167
of subscription class, 521
namespace in XML, Check constraint for, 131
naming conventions, for Notification Services instances, 494
Native attribute, 219
natural key, 77
Nchar data type, 61
near real-time reports, 455, 456
nesting
database roles, 262
tables, Transact-SQL and, 135
triggers, 92
.Net SqlClient library, for SQLCmd, 368
network share, for pull subscriptions, 232
NETWORKIO wait type, 337
New Database dialog box, 545
New Database Mail Account dialog box, 423
New Database Reference dialog box, 196
New Project dialog box, 381, 387
NEWID function, 63
NEWSEQUENTIALID function, 63
NMO. See Notification Services Management Objects (NMO)
NOCOUNT statement, 200
nodes
in B-trees, 105
in graph, paths to, 53
NOEXPAND table hint, 156
nonclustered indexes, 105–108, 109, 463
creating, 111
included columns, 465
nondeterministic behavior, 206
nonhosted event providers, 508
non-key columns, functional dependencies, and 3 NF, 42
non-Unicode strings, 60
normal forms, 38
first normal form, 38
second normal form, 40
third normal form, 41
normalization, 38, 345, 460
ORM incorporation of, 33
normalized database design, 38–42
for scalability, 138
Notification Services, 4, 20, 141, 438–441, 491
application testing, 516–519
-registering new instance and compiling application, 516
-removing application after, 518
applications configuration, 498, 502
-application database, 499
-notification classes, 500
-version and history, 502
architecture, 438
configuring instances of, 493–498
-argument encryption, 497
-custom delivery protocols, 495
-database system, 494
-delivery channels, 496
-instance database and schema, 494
-instance name, 493
-instance version and history, 497
-list of applications, 495
-parameters in ICF, 498
configuring for hosting applications, 502
defining applications, 440
implementing decision process, 20
name of, 493
optimizing, 526
-event data, 526
-notifications, 527
-subscriptions, 527
registering, 516
scale-out options, 439
security for, 233
subscriptions, 521–524
unregistering, 518
vs. DatabaseMail, 441
vs. Reporting Services, 440
Notification Services compiler, 518
Notification Services distributor, 512
Notification Services event classes, 506
creating with ADF, 510
defining, 506
-chronicles, 506
-properties, 506
event providers definitions, 507–509
-hosted providers, 507
-nonhosted providers, 508
File System Watcher event provider, 509
standard event providers, 508
Notification Services generator, 512
Notification Services Instance dialog box, 517
Notification Services Management Objects (NMO), 491
for configuring applications, 500
for defining notification class, 500
delivery channel defined in, 496
for index creation on subscription class, 523
for Notification Services distributor configuration, 513
for Notification Services generator configuration, 512
subscription class name and filegroup in, 521
notifications, in Notification Services, 491
NSAnalysis role, 234
NSDistributor role, 233
NSEventProvider role, 233
NSGenerator role, 233
NSReader role, 234
NSRunService role, 234
NSSubscriberAdmin role, 233
NSVacuum role, 234
NSVersionInfo table, 497
ntext data type, 6, 62
Check constraint and, 84
separate page for storing, 75
NTFS permissions, 234, 237
NTILE function, 6
NTML authentication, for Web Services, 431
Null property, 68
NULL values, 66
Default constraints and, 84
and Primary Key constraints, 77
nullability, order for, in integrity check, 90
number character (#), for temporary stored procedures, 188
numeric system data types, 59
nvarchar data type, 6, 61, 62, 64
length specifier for, 62
storage, 75
O
object design for data retrieval, 151
Object Explorer, for viewing Notification Services
instance and application databases, 518
object hierarchy, specifying permissions at higher levels, 271
object ownership, and schemas, 262
object permissions, 267, 270–272
object role modeling (ORM) method, 31, 33
object-change strategy, creating, 376
OBJECTPROPERTYEX function, 262
objects
bottom-up hierarchy, 273
moving between schemas, 261
name resolution for, 260
orphaned, 263
relationships between, 34
top-down hierarchy, 272
tracking changes to groups of, 413–414
accessing data source, 134
distributed transaction support by, 17
providers, 146
OLE DB Command transformation, 542
OLE DB Connection Manager dialog box, 546
OLEDB wait type, 337
OLTP. See online transactional processing (OLTP)
online analytical processing (OLAP), 22, 47
and performance, 344
reporting, 463
online transactional processing (OLTP), 3
data flow for, 49
data integrity for, 127
impact of triggers, 465
and performance, 344
OnPostExecute event, for breakpoint, 555
OnPreExecute event, for breakpoint, 555
OPENDATASOURCE rowset function, 146
OPENQUERY function, 134, 146
OPENROWSET function, 134, 146
Operating System (CmdExec) subsystem, proxy account for, 233
operating systems, resource usage, 335, 337
operation contract, stored procedure definition of, 167
operators
alerts sent to, 426
defining for SQL Server Agent, 427
optimizing
real-time reports, 456
queries. See also indexes
-basics of, 101
reports, 482–487
-practice, 485
-report snapshots, 483
-subscription models for distribution, 484
-with caching, 482
option, 271
optional parameters, for stored procedures, 168
optionality, 35
Options dialog box (SSMS), 389
ORDER BY clause, 105
for executing UDFs, 174
and indexes, 102
ORM. See object role modeling (ORM) method
orphaned logins, managing, 381
orphaned objects, 263
orphaned rows in child table, 78
OSql utility, 368
OUTPUT parameters
for returning scalar values from stored procedure, 167
for stored procedures, 168, 189
OUTPUT statement operator, 7, 243
outputs from data sources, 541
owner
checking for module, 283
executing as, 281
executing stored procedure as, 170
of objects, and schemas, 262
of schema, 260
ownership chains, 276, 473
creating broken, 278
creating unbroken, 277
P
packages in Integration Services, 25
basics, 533–535
-connection managers, 533–534
-variables, 535
control flow, 535–540
-containers, 537–539
-precedence constraints, 539
-tasks, 535
creating, 533–551
-practice, 544–551
-with BIDS, 533
data flow, 540
-data paths, 541
-data sources, 540
-destinations, 544
-transformations, 541
debugging and testing, 553
-control flow, 554
digital signatures for, 237
running, 553
saving to msdb database, 237
testing, 557, 560
unit testing, 558
verifying execution, 561–564
page for data storage, 74
PAGEIOLATCH_x wait type, 337
PAGELATCH_x wait type, 337
pagers, event notifications to, 427
parameterized views, user-defined functions for, 244
parameters
for stored procedures, 189
in ICF, for Notification Services instances, 498
parent table, relationship to child table, 77
Parse method, 68
partition function, 119, 121
partition scheme, 119
preparing, 121
partitioned tables, vs. partitioned views, 161
partitioned views, 119, 157–162
estimated query plan for execution
-with filter, 161
-without filter, 160
federated database servers with, 161
vs. partitioned tables, 161
partitioning column, 119, 157
partitioning tables, 119–121
manual, 140
switching partitions, 120, 122
Pascal, Fabian, Practical Issues in Database Management, 67
passing parameter values, 190
password, for sa login, 228, 255
Path XML index, 129
peer-to-peer topology, for replication, 16
Pending Checkins dialog box, 393, 394
performance
covered queries and, 464
data collection for predictive analysis, 329
ETL process and, 568
of indexed views, 114
of Integration Services package, 25
issues impacting, 344
multiple filegroups and, 139
in normalized system, 45
of Notification Services
-indexes and, 523
rows on pages and, 75
scalability and, 137
SSIS as SQL placeholder, 566
standard view impact on, 155
and stored procedures, 140, 165
T-SQL commands for measuring, 314
of UPDATE trigger, 92
performance baseline, 323
generating, 333
measuring, 333, 339
performance budgets, 325
performance changes
determining effects of system, 343
identifying, 339
measurement strategy, 333
-baseline generation, 333
-implementing techniques, 335
-monitoring test environment, 334
measuring, 334
responding to, 343
response plan for, 341
-considering potential causes, 341
-goal setting, 341
performance counters
performance goals to evaluate, 314
SQL Server Agent monitoring of, 425
SQL Server-specific, 337
performance goals, 302
setting, 305, 313
performance metrics, 302
performance modeling, 325
Performance Monitor, 337
performance objectives, 324
best practices for creating, 326
capacity planning for, 328
constraints on, 325
establishing, 324, 327
-practice, 330
inputs required for, 326
performance testing, 301
baseline, 302
design process, 301
documenting, 302
PERMISSION_SET option, for CREATE ASSEMBLY statement, 198
permissions. See also file permissions
for creating tables, 273
for database objects, 267–273
for database roles, 236
to deploy database, 373
for guest user, 256
hierarchies, 252, 268
owner and, 276
for principals, 229
for public role, 256
schemas and, 261
validating existence, 310
verifying, 272
persisted computed columns, 464
PERSISTED keyword, 80, 114
perspectives, 236
physical data types, 59
physical database design
column data types and sizes, 59–64
-best practices, 64
-choosing data types, 65
-cursor data type, 63
-datetime data types, 61
-numeric system data types, 59
-Sql_variant data type, 63
-string data types, 60
-system data types, 59
-table data type, 63
-Uniqueidentifier data type, 63
-XML data type, 62
domain integrity and business rules,
-Check constraints, 84–86
-Default constraints, 84
entities, 74–80
-computed columns, 80
-design and creation, 74–76
-entity integrity, 76
-Identity columns, 79
-referential integrity, 77
programmable objects for maintaining integrity, 90–92
user-defined data types, 66–70
best practices, 70
CLR user-defined types (UDTs), 66
T-SQL aliases, 66
physical I/O devices, employment by SQL Server, 335
physical phase, of database design, 32
PhysicalDiskAvg. Disk Queue Length counter, 315
PhysicalDiskAvg. Disk sec/Read counter, 315
PIVOT statement operator, 7
and indexes, 102
planning for database deployment, 355
with backup and restore, 365
with Copy Database Wizard (SSMS), 357–361
with Import and Export Wizard, 365–367
with manual script creation, 365
selecting technique, 357–371
with scripts for detaching and attaching database files, 364
with SQLCmd utility, 368–369
with SSIS, 367
with T-SQL scripts, 364–365
pointers to index pages, in B-tree nodes, 105
portable data types, 59
portal integration, with SSRS reports, 447
position, passing parameter values by, 190
Practical Issues in Database Management (Pascal), 67
precision
of functions, 206
of return code, 169
pre-defined reports model, 448
predicate, 39
predictability, from tracking benchmark statistics, 349
predictive analysis, for capacity planning, 329
prepare phase, for transaction, 17
primary identifier for a login token, 280
primary key, 39, 41, 67, 76
applicability property of, 77
for clustered index, 155
dependence on, 40
sequential numbers for, 79
Primary Key constraint, 82
clustered index for, 107
and NULL values, 77
indexes for implementing, 102, 106
order for, in integrity check, 91
for table, 76
Primary XML index, 128
principals, 228, 229, 252, 373
managing, 254–258
security for, 256
special, 255
validating existence, 310
viewing details about, 374
privacy, and database for testing, 295
private key, 245
private profiles, for DatabaseMail, 421
privilege, elevation of, 227
ALL, 271
process objectives, setting targets, 334
process phase of reporting execution process, 482
Processor% Processor Time counter, 315
production database, copy for testing, 295
productivity, benchmark statistics and, 348
profiles, in DatabaseMail, 421
programmable objects, for security, 275
project
adding to source control, 389
scope definition, 33
project-management methodology, 380
project objects, XML code to define, 388
promotable transactions, 17
Properties pages of SQL Server instance, 228
Property XML index, 129
proposition, generalized form, 39
ProtectionLevel property of a package, 237
protocols, endpoints for, 256
prototypes, 326
proxy accounts, in SQL Server Agent, 233
public fixed role, 255
public profiles, for DatabaseMail, 421
Publication Access List (PAL), 231
public-key encryption, 245
Publisher role, 235
for replication, 459
pull subscriptions, 434
network share for, 232
push subscriptions, 434
Q
quality, benchmark statistics and, 348
quantum, 513
quaternary facts, 35
queries, 455
best practices for, 139
covered, 464
DELAYFOR command to slow down, 486
optimizing, basics of, 101
for reports, 472
results in e-mail, 422
testing performance, 301–305
-writing test, 303
types benefiting from indexes, 101
Queries logical folder, 388
Query Builder, 472
Query Designer, 451
query files, 389
query notifications
decision to consume, 133
query optimizer
index reuse by, 156
for partitioned view, and WHERE predicate, 160
query plan, for stored procedure, 165
Queue Reader Agent, account for, 231
queued requests, increase in number, 343
QUOTED_IDENTIFIER option, 114
R
RAISERROR, 428
RAISERROR WITH LOG, 428
RANK function, 6
ranking functions, 6
read committed snapshot isolation level, 457
Read method, of IBinarySerialize interface, 68
Read rights, to database object, 397
read-only user, creating in Visual SourceSafe 2005, 397
Real data type, 60
real-time reports, 455
optimizing, 456
real-time transaction processing, HTTP endpoints and, 14
RECEIVE permission, 270
RECONFIGURE statement, triggers and, 91
recursive trigger, 92
redundancy
adding to database, 460
normalization to eliminate, 38
triggers for maintaining, 210
reference objects, 68
REFERENCES permission, 270
referential integrity, 77
registering new Notification Services instance, 516
regression testing, 290, 557
regular entities, 36
regular expressions
CLR function to validate e-mail address against, 88
CLR function to validate input string against, 85
for extracting e-mail, 207
T-SQL and, 85
for validating e-mails, 205
regular outputs from data sources, 541
regulatory requirements, and auditing, 240
relational data model, 31
relational database management system (RDBMS), 32
denormalized data maintained by, 47
name of, 260
relational databases, 127
OLE DB source to return data, 135
relational model, 125
relations, 66
relationships
between entities, 35
between objects, 34
between tables, 41
releases, labels for tracking, 400
reliability
of Database Mail, 10
in distributed system, with Service Broker, 15
Remove Space transformation, 550
removing
item from database projects, 392
Notification Services application after testing, 518
render phase of reporting execution process, 482
repeating group of columns, and first NF, 40
replication
best practices for, 231
database, 459
and deployment planning, 357
and object changes, 376
for scaling out, 142
security for, 231
transactional, 50
Replication Agent security model, 231
Replication component, 4, 16
Replication Monitor, stopping synchronization from, 376
Report Builder role, 235
Report Designer, 450
for editing generated report, 453
Report Manager
creating data source from, 449
for configuring report caching, 482
report models, 22, 449
Report Server
Project Wizard template, 451
URL for, 446
report snapshots, 483
for scaling out, 142
reporting execution process, 482
Reporting Services. See SQL Server Reporting Services (SSRS)
reports, optimizing, 482–487
with caching, 482
ReportServer database, 234
ReportServerTempdb database, 234
repudiation, 226
request phase of reporting execution process, 482
requests, number queued, 343
requirements for data modeling, identifying, 32
Residual Information Protection (RIP), 379
resource managers, 16
resources for DML triggers, 243
response phase of reporting execution process, 482
response plan for performance changes, 341
response time, evaluating, 301
RESTORE DATABASE statement, triggers and, 91
RESTORE LOG statement, triggers and, 91
restoring database, 365
retention period, for audit logs, 241
return code, from stored procedure, 169, 191
RETURN statement, 191
REVERT command, 265
REVERT statement, 280
REVOKE CONNECT FROM GUEST statement, 255
REVOKE statement, for controlling permissions, 267, 271
role-based authorization, for SSRS, 235
ROLLBACK statement, 242
ROLLBACK TRANSACTION statement, 17
test script to validate, 308
ROLLBACK WORK statement, 17
root node in B-trees, 105
row-by-row operations
CLR vs. T-SQL, 133
T-SQL for, 9
row-filtered report, creating, 478
row ID, in nonclustered index, 106
row-level security, views to implement, 244
ROW_NUMBER function, 6
row-overflow units, and performance, 75
row security, views for, 474
row transformations, 542
row versioning, 457
ROWCOUNT_BIG function, 297
rows of table
pages for, 74
uniquely identifying, 76
rowset transformations, 542
run-time engines, for SSIS package, 533
S
sa login, password for, 228
sa SQL Server login, 255
SAFE CAS security set, 231
SAFE permission set, 8, 133, 198, 236
sample databases, 29
Sarbanes-Oxley Act, 378
Save File As dialog box, 394
saving package, 367
to msdb database, 237
scalability, 125
of application queries, 139
and CLR, 139
database design for, 138
of Database Mail, 10
designing for, 137–145
and filtered views, 476
issues impacting, 344
of Integration Services packages, 25
of Notification Services, 21
scalar user-defined functions, 175, 204–208
data types returned by, 179
exercise, 181
validating, 292
scalar values, returned by stored procedure, 167
scaling out, 140–143, 344
analytical applications, 141
with message queuing, 141
for Notification Services, 439
vs. scaling up, 161
scaling up, 344
hardware changes for, 137
scatter plot, for data viewer, 556
scenarios, absence of documentation, 327
scheduled event provider, 507
scheduled refreshed reports, 455, 456
scheduled Windows Shared File subscription, configuring, 484
schema test, 290
SCHEMABINDING option, 114
schemas, 29, 260–263
best practices guidelines, 260
checking for module, 283
creating, 264
and database roles, 261
defining, 260
design for reporting services, 460
-OLAP reporting, 463
-selective denormalization, 460
-star or snowflake schema, 461
-summary tables, 461
efficiency of, 345
maintaining historical record of changes, 378
for Notification Services instances, 494
and object ownership, 262
replicating changes, 16
for stored procedure security, 194
for subscriptions, 522
scope
of change, and object-change strategy, 376
of project, defining, 33
of securables, 228
of variables, in SSIS, 535
Script Component, 541
scripting tasks
in SSIS packages, 536
-code inspection, 557
-debugging, 556
selecting language, 556
scripts, DTExec for creating, 553
second normal form, 40
secondary identifiers for login token, 280
secret-key encryption, 245
securables, 228, 252
user permission to see definition, 258
Secure Sockets Layer (SSL), 230, 431
for DatabaseMail, 421
secure systems, structure of, 227
security, 225
for audit logs, 241
auditing events, 242
for CLR integration, 230
of Database Mail, 11
data protection, 244
deployment of permissions, 373–375
and deployment planning, 355, 372–375
for endpoints and principals, 256
for HTTP endpoints, 230
for Integration Services (SSIS), 25, 237
for linked servers, 232
model for Reporting Services, 22
for Notification Services, 233
and performance change, 342
programmable objects for, 275
for replication, 231
for rows, with views, 474
for SQL Server, 226
-practice, 238
for SQL Server Analysis Services (SSAS), 235
for SQL Server Reporting Services (SSRS), 234
for stored procedures, schemas for, 194
stored procedures and, 275
system views related to, 310
taxonomy of threats, 226
test design for application security, 310, 312
triggers’ impact, 92
for Web Services, 431
security execution context, and user-defined functions (UDFs), 181
Security identifiers (SIDs), 373
security test, 290
security tokens, for user identification, 280
securityadmin server-level role, 257
SELECT * queries, vs. explicit column list, 140
Select A Destination Server page, in Copy Database Wizard, 358
Select A Source Server page, in Copy Database Wizard, 358
SELECT permission, 270
select permissions, T-SQL to grant, 410
SELECT statement (DMX), 135
SELECT statement (SQL)
common table expressions for, 6
IDENTITY function in, 79
for indexed view, restrictions, 115
inside stored procedure, 167
OLE DB Command transformation and, 542
Select The Transfer Method page, in Copy Database Wizard, 359
selective denormalization, 460
selectivity, 103
SELF, executing as, 170, 281
semi-structured data, 59
sending messages, in DatabaseMail, 422
sensitive columns, hiding, 474
sequence containers in SSIS packages, 538
sequential numbers, for primary key, 79
server-level principals, 228
Service Broker, 243, 422, 425
for event notifications, 242
Service Broker component, 4, 14
service principal name (SPN), 230
Service-Oriented Architecture (SOA), 14
set, 31
Set Breakpoints dialog box, 559
SET clause, for executing UDFs, 174
Set Default rule, for parent and child tables, 78
SET IDENTITY_INSERT command, 79
Set Null rule
for parent and child tables, 78
SET options
for connection to create index, 114
SET REMOTE_PROC_TRANSACTIONS ON statement, 17
sets, table to represent, 76
setup testing script
for unit testing, 294
validating, 295
SETUSER, for impersonating user, 265
shared data sources, 449
creating, 449, 450
shared locks, 457
Shared Memory protocol, endpoint for, 256
shredding XML data, 128
Simple Mail Transport Protocol (SMTP), 10, 527
and DatabaseMail, 232
setting up services, 422
Simple Object Access Protocol (SOAP), 13, 146, 430
Simple recovery model for database, 119
site capacity, verifying, 329
sliding-windows scenarios, table partitioning for, 119
Slowly Changing Dimension transformation, 543
Smalldatetime data type, 61
Smallint data type, 60
Smallmoney data type, 60
SMTP. See Simple Mail Transport Protocol (SMTP)
Snapshot Agent, account for, 231
snapshot isolation, 50, 139, 457
demands on tempdb database, 458
enabling, 457
snapshot replication, 16
snapshot strategy, 456–460
database mirroring, log shipping, and database replication, 458
database snapshots, 458
transaction isolation levels, 456
snapshots
generating new, 377
object changes and, 378
of data, 21
snowflake schema, 461
SOA. See Service-Oriented Architecture (SOA)
SOAP. See Simple Object Access Protocol (SOAP)
solution definition files, 388
Solution Explorer (SSMS), 381
Checked out for edit status in, 392
connection in, 389
for view of empty database project, 387
source-controlled items in, 392
Sort Alternate Names transformation, 550
Sort transformation, 550
sorting encrypted data, 246
source code
benefits of version control, 385
database, 387
of database object, rules for viewing, 258
encryption, 408–412
file permissions for access, 395
managing changes to, 395
source control, 33
adding project to, 389–393
and SSMS, 387–389
setting file permissions, 398
source-controlled database projects, 393–395
sp_addlinkedsrvlogin system procedure, 148, 232
sp_addlogin stored procedure, 254
sp_addmessage stored procedure, 91, 428
sp_addrolemember system procedure, 255
sp_addscriptexec stored procedure, 377
sp_addsrvrole member system procedure, 255
sp_addtype system stored procedure, 66
sp_attach_db stored system stored procedure, 365
sp_change_users_login stored procedure, 374, 382
sp_columns_ex system stored procedure, 149
sp_configure system procedure, 8, 230, 242
clr enabled option, 195
sp_detach_db stored system stored procedure, 364
sp_grantlogin stored procedure, 254
sp_replicationdboption stored procedure, 376
sp_send_dbmail stored procedure, 420, 422, 424
profile for, 421
sp_setapprole stored procedure, 229
sp_table_ex system stored procedure, 149
sp_tableoption system stored procedure, 75
sp_unsetapprole stored procedure, 229
sp_updatestats system stored procedure, 103
special principals, 255
specialization, 52
split and join transformations, 543
spoofing identity, 226
SQL commands, creating data source from, 541
SQL injection, 227
SQL Mail, deprecation of, 10
SQL Management Object (SMO) method, 359
SQL placeholder, SSIS as, 565, 566
SQL Server
components, 3
editions, 137
extending functionality, 185
multiple instances, Service Broker and, 15
Properties pages of instance, 228
security for, 226
SQL Server 2005 Books Online, on CREATE CERTIFICATE, 408
SQL Server 2005 Express Edition
deployment of database to, with backup and restore, 381
downloading, 381
and Reporting Services, 435
SQL Server 2005 Query Notifications, 148
SQL Server Agent, 4, 9, 469
and DatabaseMail, 232
database roles for, 232
for executing Integration Services package, 25
for running SSIS package, 554
service account configuration, 233
SQL Server Agent alerts, 425–428
alert definition, 425
checking for running, 428
operator definition and notification, 427
table for drop-table WMI event data, 428
user-defined events, 428
SQL Server Analysis Services (SSAS), 4, 22–24, 51, 143
auditing, 243
Command subsystem, proxy account for, 233
Event Provider, 20, 509
Query subsystem, proxy account for, 233
security for, 235
tasks in SSIS packages, 537
Unified Dimensional Model (UDM) cubes, 135
SQL Server Area Configuration (SAC) tool, 195
SQL Server Business Intelligence Development Studio (BIDS), 362, 400, 448
SQL Server databases, writing programmatic objects for, 8
SQL Server error messages, alerts on, 10
SQL Server Event Provider, 20, 508
SQL Server events, alert for, 425
SQL Server Integration Services (SSIS), 4, 24, 147, 531
conventional ETL with, 566–569
for data warehousing, 50
ETL design patterns, 565
ETL in-memory pipeline with, 569
going beyond ETL process, 569
package creation, 533–551
planning deployment with, 367
proxy account for, 233
security for, 237
SQL Server logins, 254
SQL Server Management Studio (SSMS), 387
adding new script, 394
console, 297
Copy Database Wizard, 357–361
-configuring logins for, 375
-customizing SSIS package from, 361–364
-for deploying permissions, 374
item check out for editing, 392
Maintenance Plan Wizard, 570
new query window, 394
opening item in, 392
Query Editor, 143
Solution Explorer, 381
and source control, 387–389
SQL Server Notification Services (SSNS). See Notification Services
SQL Server Profiler, 138, 243, 314, 338, 527
for tracking database activity, 379
SQL Server project template, 195
SQL Server Reporting Services (SSRS), 4, 21–22, 142, 147, 443
Configuration Manager, 446
data-driven subscriptions, 435
data sources, 449
-configuring, 449
data transformation design, 465–468
delivery options, 434
evaluating uses, 445–449
-by audience, 445
in Express Edition with Advanced Services, 138
index design, 463
vs. Notification Services, 440
optimizing reports, 482–487
-with caching, 482
-practice, 485
-report snapshots, 483
-subscription models for distribution, 484
pre-defined roles, 235
programmatic interfaces, 472, 481
-querying tables directly, 472
-stored procedures, 476–478
-views, 473
real-time requirements, 455
Report Builder, 450
report construction, 448
-ad hoc reporting, 448
-pre-defined reports model, 448
report delivery, 446
row-filtered report, creating, 478
schema design, 460
-OLAP reporting, 463
-selective denormalization, 460
-star or snowflake schema, 461
-summary tables, 461
security for, 234
snapshots of data, 50
subscriptions, 434
SQL Server Scripts template, 387
SQL Server Service Broker (SSB), 141
SQL Server services
selecting, 3–5
-to support business needs, 5
SQL Server SQL Server Command Line Utility (Sqlcmd.exe), 296
SQL Server tasks, in SSIS packages, 536
SQL Server tuning tools, 527
SQL Server wait stats, 335
SQL Server Web Services, 13
sql_variant data type, 63, 64
SQLAgentOperatorRole, 232
SQLAgentReaderRole, 232
SQLAgentUserRole, 232
Sqlcmd.exe (SQL Server SQL Server Command Line Utility), 296, 368–369
SqlDependency class, 148
SqlNotificationRequest class, 148
SQLQuery1.sql file, 389
.sqlsuo files, 388
SQLXML 3.0, 13
SQRT function, 113
SSAS. See SQL Server Analysis Services (SSAS)
SSIS. See SQL Server Integration Services (SSIS)
SSIS package from Copy Database Wizard
customizing, 361–364
saving, 367
SSL (Secure Sockets Layer), 230, 431
for DatabaseMail, 421
SSMS. See SQL Server Management Studio (SSMS)
.ssmssln files, 388
.ssmssqlproj files, 388
SSRS. See SQL Server Reporting Services component (SSRS)
stale data, in OLTP applications, 49
standalone applications, SSRS as, 446
Standard Edition of SQL Server 2005, 138
standard event providers in Notification Services, 508
standard subscriptions, 434
standard views, 153–154, 162
exercise, 163
standardizing data types, T-SQL aliases for, 66
star schema, 461
in OLAP system, 23
state of database, object changes and, 378
statement operators, new in SQL Server 2005, 7
statement permissions, 267, 268–269
statistics
gathering, disabling process, 103
maintaining, and indexes, 102
status value, from stored procedure, 169
step into (F9) command, for debugging stored procedure, 197
step over (F10) command, for debugging stored procedure, 197
stored procedures, 187–202
CLR, 187, 195–198
-deploying, 197
CLR in place of extended, 9
code to test, 202
creating, 188
creating and source-control, 395
for data access, 476–478
-advantages and disadvantages, 477
for data protection, 244
and data retrieval, 151
data returned by, 167
design, 165–172
error handling routines in, 169
executing, 189
execution status value, 169
extended, 134
HTTP endpoints for exposing, 14
input, output and optional parameters, 168
names for parameters, 167
number of parameters, 167
parameter data type, 167
parameters for, 189
and performance, 101, 140
practice, 171
for programming constraints, 90
purpose of, 187
query plan for, 165
return code, 191
for row-filtered report, 478
-testing, 479
schemas for security, 194
security execution context for, 170
signing with certificate, 409
table-valued function alternatives to, 176
Transact-SQL, 187
-changing, 192
-creating, 199–202
-deleting, 193
-design process, 193
trigger call of, 92
types, 166
-CLR stored procedures, 166
-extended stored procedures, 166
-T-SQL stored procedures, 166
unit testing of, 289
for update activities, 275
string data types, 60
dynamic concatenation, 277
full-text indexes on, 62
striping a version, 400
strong entities, 36
Strong Name command prompt utility (sn.exe), 281
structure creation when creating CLR data type, 68
structured data, 59
subqueries, in Check constraint, 84
subscribers
data in Notification Services instances, 494
defining, 440
for replication, 459
subscription class
creating, 521
indexes for, 523
subscription management interfaces (SMI), 439, 440, 523
subscription management objects (SMOs), 439
subscriptions, 521
data-driven, 435
in Notification Services, 491
optimizing, 527
push vs. pull, 434
reinitializing, 377
for report distribution, 484
rules, 522, 527
schemas for, 522
standard, 434
subtypes, 52
success, of unit testing, 291
summary in tables, maintaining, 46
summary reports, creating, 468–470
summary tables, 461
triggers for maintaining, 465
supertypes, 52
supportability, of Database Mail, 11
Surface Area Configuration tool, 134, 230
for enabling SQL Server Agent, 9
surrogate key, 77
symmetric key encryption, 244
synchronizing logins, after moving database, 373
sys principals, 256
sys.database_permissions catalog view, 256, 310
sys.database_principals view, 310
sys.database_role_members view, 310
sys.dm_io_virtual_file_stats dynamic management view, 338
sys.dm_os_performance_counters system table, 337
sys.dm_os_wait_stats dynamic management view, 336
sys.fn_builtin_permissions system function, 270
sys.objects catalog views, 283
sys.schemas catalog views, 283
principal.id attribute, 262
sys.server_permissions catalog view, 258
for checking endpoint access, 257
sys.server_permissions view, 310
sys.server_principals view, 310
sys.server_role_members view, 310
sys.server_triggers catalog view, 92
sys.sql_logins catalog view, 254, 310
sys.sql_modules catalog view, 92, 283
sys.triggers catalog view, 92
sysadmin permissions, 134
sysadmin server-level role, 257
System Administrator role, 235
system data types, 59
system failure, 343
system resources
starvation, 343
test design for use, 313–315
wait stats for, 336
system stored procedures, 187
system testing, 557
System User role, 235
system verified property of functions, 206
system views, related to security, 310
SystemProcessor Queue Length counter, 315
T
table data type, 63
table-defined user-defined functions, validating, 293
table scan, 101
TABLE type variable, from UDF, 176
tables
cascading changes with triggers, 90
clustered index on, vs. clustered index on view, 155
creating, 75
-permissions for, 273
after decomposing to multiple, for second NF, 41
for drop-table WMI event data, 428
extents for, 74
nested, T-SQL and, 135
obtaining information about names from OLE DB provider, 149
partitioning, 119–121
-manual, 140
-switching partitions, 120, 122
relationships between, 41
for structured entities, 59
table-valued user-defined functions, 176
data types returned by, 179
Tabular Data Stream (TDS), 13, 430
tabular result sets, from stored procedures, 167
TAKE OWNERSHIP permission, 270
tampering with data, 226
tasks in SSIS packages, 535
Analysis Services, 537
data flow, 535
data preparation, 535
maintenance, 537
scripting, 536
setting properties, 537
SQL Server, 536
workflow, 536
TCP/IP, endpoint for, 256
TDS (Tabular Data Stream), 13, 430
Team Edition for Database Professionals, 372
Team Explorer, 400
Team Foundation Servers dialog box, 400
team projects, 390
teams, managing multiple with schema and statement permissions, 273
teardown testing script
for unit testing, 295
validating, 295
technical review strategy, 557
tempdb system database, 50
clean up, 247
isolation levels and, 458
Notification Services use of, 526
and online index operations, 109
as potential bottleneck, 139
temporary stored procedures, 188
Terminate function, 217, 222
ternary facts, 35
test automation, of unit testing, 558
test case, for unit testing, 558
test condition, 297
test database, creating, 110, 295, 299
test design
for application security, 310, 312
for code coverage, 317–320
for data consistency, 306–309
for query optimization, 301
test environment
documenting changes to, 335
monitoring, 334
testing. See also unit testing
CLR stored procedures, 197
and deployment planning, 355
impact of database modification, 287
packages in Integration Services, 553, 557
script, 291
SSIS packages, 560
text data type, 6, 62
Check constraint and, 84
separate page for storing, 75
text files, importing data from, 140
text mining, 570
think time, adding to performance testing scripts, 301
third normal form, 41
threads, for Notification Services, 512
timeouts, 343
timestamps
Check constraint and, 84
generating duplicate, 64
Tinyint data type, 60
top-down hierarchy
for objects, 272
TOP statement operator, 7
ToString method, 68
transaction log, impact of index DDL operations, 109
transaction manager, 16
transaction rollback, for rule violation, 91
transactional cost analysis, 328
transactional environment, testing data consistency in, 308
transactional replication, 16, 50, 147
altering table for, 376
for scaling out, 142
transactional reports, real-time reports use of, 455
transactions, 46
distributed, 16, 538
-statements starting, 17
and identity value, 79
isolation levels, 456
for maintaining denormalized data, 47
performance issues, 342
promotable, 17
in SQL Server, ACID properties, 456
triggers as part of, 90
two-phase commit, 17
for unit test code, 294
Transact-SQL
commands for measuring performance, 314
for detaching and attaching database files, 364
disallowed commands in trigger, 91
enhancements, 6
for opening trace information, 338
for testing script, 291
vs. CLR, 9, 133
Transact-SQL aliases, 66
best practices, 70
Transact-SQL query
encapsulating in view, 153
Transact-SQL stored procedures, 166
Transfer SQL Server Objects task (SSIS), 367
Transfer SQL Server Objects Task Editor, 367
transformation, in ETL process, 467
transformation packages, in ETL process, 567
transformations, Integration Services package support for, 25
tree, 53
trend line, 329
triggers, 210. See also DDL triggers
asynchronous processing, 15
auditing, 379
for maintaining summary tables, 465
on data-definition (DDL) events, 7
reasons for, 210
unit testing of, 289, 293
TRUNCATE TABLE command, 120
trustworthy, marking database as, 281
TRY . . . CATCH construct, 7, 318
for stored procedure error handling, 169
two-phase commit, 17
typed XML schema, 62
U
UDM. See Unified Dimensional Model (UDM)
unary facts, 35
Unicode strings, 60
for multilanguage applications, 64
storing in XML format, 62
Unified Dimensional Model (UDM), 24
cubes for scaling out, 142
cubes for SQL Server Analysis Services, 135
Unified Modeling Language (UML), 31
Use Case diagrams, 32
uniform extents, 74
unique clustered index, for view, 155
Unique constraint
for entity integrity, 76
indexes for implementing, 102, 106
order for, in integrity check, 91
unique indexes, 106–107
Uniqueidentifier data type, 63
uniqueness constraints, 306
unit testing, 289, 557
data setup for, 296
evaluating result, 297
executing, 296
goals, 290
planning for, 287, 290
script creation, 291, 297
-code example, 291
setup testing script for, 294
SSIS packages, 558
teardown testing script, 295
triggers, 293
types, 290
validating scripts for, 295
UNPIVOT statement operator, 7
UNSAFE assemblies, 198
UNSAFE ASSEMBLY permission, for login, 281
UNSAFE CAS security set, 231
UNSAFE permission set, 8, 236
unsetting application role, 229
unstructured data, 59
large data types for, 62
update anomalies, 42
denormalization and, 38, 46
update conflicts, multiple copies of data and, 49
update performance, indexed views and, 155
UPDATE permission, 271
UPDATE statement (SQL)
Check constraints for, 84
common table expressions for, 6
DML triggers and, 210
OLE DB Command transformation and, 542
OUTPUT statement operator, 243
support for chunked update, 62
UPDATE STATISTICS statement, 103
UPDATE TRIGGER, unit test for, 293
updateable views, triggers for, 210
URL, for Report Server, 446
user accounts, creating from certificate, 410
user-defined aggregate attributes, 219
user-defined aggregate functions, 133
user-defined aggregates
CLR, 217–222
creating, 220
integrated CLR for, 9
programming, 217–222
UserDefined attribute, 219
user-defined data types, 66–70
best practices, 70
CLR user-defined types (UDTs), 66
integrated CLR for, 9
T-SQL aliases, 66
user-defined database roles, 229
user-defined events, in SQL Server Agent, 428
user-defined functions (UDFs), 173–182
and data retrieval, 151
CLR
-for CHECK constraints, 194
Concatenate aggregate function, 217
contexts for execution, 173
data types returned by, 179
error handling routines in, 180
for data protection, 244
for extracting e-mail, 207
input parameters, 180
practice, 181
properties, 206
for retrieving namespace, 131
scalar, 175, 204–208
-creating CLR, 205
-creating T-SQL, 204
-validating, 292
security execution context and, 181
table-defined, validating, 293 types, 175
-CLR, 178
-table-valued, 176
unit testing of, 289
validating data consistency, 308
user-defined metadata, adding to database objects, 413
user identification, security tokens for, 280
user_name, executing as, 281
user permissions and rights, in Visual SourceSafe 2005, 397
user profile
cost of average, 329
in transactional cost analysis, 328
user token, 280
UserID global variable, 477
users
ad hoc reports, 22
adding to fixed server roles, 255
grouping, 262
OLAP client tool for, 23
as security risk, 226
SETUSER for impersonating, 265
validating creation of, 311
V
validating
data
-before transaction, 193
-stored procedures for, 187
XML schema collection, 131
Value objects in ORM, 34
Value XML index, 129
value() method, of XML data type, 131
VALUES clause, for executing UDFs, 174
varbinary data type, 6, 64
length specifier for, 62
storage, 75
varbinary(max) data type, for encrypted data, 246
varchar data type, 6, 61, 64
length specifier for, 62 storage, 75
variables
evaluating status, 555
for return value from stored procedure, 191
in SSIS, 535
version control folder, displaying, 390
versions
detecting differences, 404–406
information, 400–403
for Notification Services applications, 502
for Notification Services instances, 497
VIEW ANY DATABASE permission, 257
VIEW DEFINITION permission, 257, 271
views. See also indexed views
benefits of encapsulating query in, 154
and data retrieval, 151
for data protection, 244
designing, 153–164
hiding detailed information with, 473
hiding sensitive columns with, 474
impersonation for filtered, 476
limitations, 165
practice, 162–164
for reports, 473
for row security, 474
for security, 275
table-valued function alternatives to, 176
types, 153–162
-indexed views, 155–156, 162
-partitioned views, 157–162
-standard views, 153–154, 162
unit testing of, 289
Virtual Interface Architecture (VIA), endpoint for, 256
virtual tables, 153
virus, 226
Visio, 30
Visio for Enterprise Architects, 33
Visual Basic .NET, 134
in companion CD, 70
for stored procedure, 166
Visual Basic Script, 556
Visual C# 2005 Express Edition, downloading, 30
Visual SourceSafe 2005, 387, 397
Administrator utility, 397
comparison utility for versions, 404
creating or accessing instance, 398
creating read-only user, 397
Explorer, for version information, 400
folder permissions, 398
login dialog box, 398
user permissions and rights, 397
Visual Studio
creating database project, 207
deploying project from, 198
Visual Studio 2005 Team Foundation Server, 387
applying label in, 400
comparison utility for versions, 404
configuration information, 389
MSSCCI Provider.msi file, 389
Visual Studio 2005 Team Foundation System
version control in, 389
Visual Studio for Applications (VSA), 556
Visual Studio Team System, 33, 74
W
wait stats, for system resources, 336
Wait_Stats tool, 314
watch, adding for debugging, 555
weak entities, 36
Web applications, embedded reporting in, 447
Web browsers, SSRS interaction through, 446
Web services, 13, 430
creating and defining, 430
decision to consume, 133
guidelines for using, 432
security, 431
Web Services Definition Language (WSDL), 13, 146, 430
WHERE clause, for query optimization, 139
WHERE predicate
calling UDF in, 176
for executing UDFs, 173
and indexes, 102
and query optimizer for partitioned view, 160
Windows Application log, alert for messages logged to, 425
Windows applications, embedded reporting in, 447
Windows Event Log, 242
alerts on, 10
Windows groups, 257, 262
Windows Guest account, 230
Windows Management Instrumentation (WMI) events
alerts on, 10
SQL Server Agent response to, 426
Windows Management Instrumentation Query Language (WQL), 426
Windows Shared File subscription, configuring
scheduled, 484
Windows SharePoint Services, 447
Windows-level principals, 228
WITH ENCRYPTION option, 244
WITH GRANT OPTION, 271
WMI events. See Windows Management Instrumentation (WMI) events
workflow of package, 533
workflow tasks, in SSIS packages, 536
Workgroup Edition of SQL Server 2005, 138
workload
analyzing, 138
expected, and performance objectives, 327
workload characterization, 328
WQL (Windows Management Instrumentation Query Language), 426
Write method, of IBinarySerialize interface, 68
WRITELOG wait type, 337
WSDL (Web Services Definition Language), 13, 146, 430
X
XML
for defining project objects, 388
HTTP endpoints for, 14
for Notification Services instance configuration, 493
XML columns, full-text indexes on, 129
XML configuration files, and encryption, 497
XML data
in databases, 127–132
shredding, 128
XML data type, 7, 62
indexing columns, 108
risk of overuse, 64
storage, 75
usage, 127–128
value() method of, 131
XML indexes, 108, 128
XML injection attacks, 242
XML schema collection, exercise
for preparing, 129–130
for validating, 131
XML Web services, 146
xp_logevent stored procedure, 428
XQuery language, 7, 63, 128, 134
XSLT content formatter. See Extensible Stylesheet Language Transformations (XSLT) content formatter
Z
zero (0), as return value from stored procedure, 191