DAO (Data Access Objects), 32
data access
ADO (ActiveX Data Objects), 10–11
ADO.NET, 6–7
data providers, 4–5
exercise connecting using SNAC (SQL Native Client), 16–17
exercise connecting with, 14–15
MDAC (Microsoft Data Access Components), 7–11
network protocols, 2–4
ODBC (Open Database Connectivity), 8–9
OLE DB (Object Linking and Embedding Database), 10
password policies, 12–13
procedures compared with XPs, 433
SNAC (SQL Native Client), 12
stored procedures for, 209
data access object models, 32–42
ADO (ActiveX Data Objects), 32
ADO.NET as connected model, 35–36
ADO.NET as disconnected model, 32–34
exercise retrieving data with DataReader, 40–42
exercise retrieving data with DataSet, 36–39
data archiving, 349
data integrity
case scenario validating, 231
declarative. See declarative data integrity
exercise applying data validation rules to tables, 214
procedural. See procedural data integrity
data mining
AMO classes for, 155
AMO for controlling/managing, 154
exercise creating data mining structure, 166–168
references, 154, 190
SQL Server Business Intelligence Development Studio, 155
data partitioning. See partitioning
data providers
ADO.NET, 6, 438
available for linked servers, 51
selecting, 4–5
Data Source Configuration Wizard, 33
Data Source Name (DSN), 8
data sources
accessing multiple, 51–54
connection objects, 6
creating, 158
DataSets from, 34
exercise creating data mining structure, 166–168
heterogeneous, 51
when to use CLR and, 437
data stewardship, 360
data storage optimization, 332–338
data types, 334–335
de-normalizing, 333–334
exercise de-normalizing an aggregation, 335–338
overview of, 332
row width, 332–333
data types, 334–335
alias, 335
columns using large data types, 308
consistent use of, 335
overview of, 334
page reads and, 286
procedures compared with XPs, 433
restrictive data types for preventing SQL injection attacks, 211
selecting appropriate, 335
smallint, 62
variable-length, 332
xml data type, 80
data validation. See data integrity
data warehousing databases, 358
database engine, 451–454
flushing data pages to disk, 451
flushing log pages to disk, 452
overview of, 451
read-ahead, 453–454
database optimization
data storage. See data storage optimization
indexes. See index performance
queries. See query performance
databases
compatibility level settings, 57
exercise creating database with SMO, 160–166
datagrid controls, 99
DataReaders
compared with DataSets, 35
exercise retrieving data with, 40–42
in ADO.NET connected object model, 35
using, 35
DataSet Designer, 33
DataSets
ADOMD object, 158
benefits of, 33
compared with DataReaders, 35
creating, 33
data sources for, 34
exercise retrieving data, 36–39
making changes to, 34
stored in cache, 103
supporting ADO.NET disconnected model, 33
DataSource. See data sources
DataSourceView
creating, 158
exercise creating data mining structure, 166–168
datetime values, cache expiration and, 108
DDL (Data Definition Language), 145
deadlocks
causes of, 240
exercise protecting against, 276
minimizing, 266
DEALLOCATE keyword, cursors and, 121
declarative data integrity, 195–208
check and foreign keys for improving query performance, 206–208
check constraints, 199
defined, 195
exercise comparing with procedural data integrity, 213
extending check constraints with user-defined functions, 200–202
foreign key constraints, 196–199
foreign keys used with subqueries, 205
overview of, 195
primary and unique key constraints, 195
user-defined functions used with subqueries, 202–204
DELETE statements
OUTPUT keyword, 261
query hints and, 71
steps in processing, 457
subqueries, 50
within snapshot transaction, 248
de-normalization. See also normalization
data storage optimization and, 333–334
exercise de-normalizing an aggregation, 335–338
DENSE RANK, 58
derived tables
as alternative to cursors, 116
queries placed in, 294
developers, cursor use by, 129
Digest authentication, 20
Dimension
AMO objects, 154
AMO OLAP class, 155
dirty reads
concurrency problems, 235
read committed isolation level for protecting against, 236
read uncommitted isolation level and, 238
disconnected object models, ADO.NET, 32–34
disk space
optimizing data storage and, 332
removing seldom-used indexes and, 332
distributed partition views (DPVs), 353–355
distributed queries, linked servers and, 51–54
Distributed Transaction Coordinator (DTC), 267
distributed transactions, 267–269
Distributor object, RMO, 149, 151
DLLs (dynamic-link libraries), 2
DML
modify method, 84
procedural data integrity and, 195
DML triggers
AFTER triggers and cascading actions, 199
implementing procedural data integrity and, 209
DMVs (Dynamic Management Views)
analyzing database application performance, 380
diagnosing problems with, 383
measuring memory consumption, 441
querying SQLOS wait types, 417
viewing SQLOS memory nodes, 410
DPVs (distributed partition views), 353–355
creating, 353
goal of, 353
DROP INDEX statement, 346
DSN (Data Source Name), 8
DTC (Distributed Transaction Coordinator), 267
Duration attribute, OutputCache directive, 99
dynamic cursors, 132
dynamic-link libraries (DLLs), 2
dynamic management functions, cursors, 123
Dynamic Management Views. See DMVs (Dynamic Management Views)
dynamic SQL
building cursors, 131
preventing SQL injection attacks and, 212