D

DAO (Data Access Objects), 32

data access

ADO (ActiveX Data Objects), 10–11

ADO.NET, 6–7

data providers, 4–5

exercise connecting using SNAC (SQL Native Client), 16–17

exercise connecting with, 14–15

MDAC (Microsoft Data Access Components), 7–11

network protocols, 2–4

ODBC (Open Database Connectivity), 8–9

OLE DB (Object Linking and Embedding Database), 10

password policies, 12–13

procedures compared with XPs, 433

SNAC (SQL Native Client), 12

stored procedures for, 209

data access object models, 32–42

ADO (ActiveX Data Objects), 32

ADO.NET as connected model, 35–36

ADO.NET as disconnected model, 32–34

exercise retrieving data with DataReader, 40–42

exercise retrieving data with DataSet, 36–39

data archiving, 349

data integrity

case scenario validating, 231

declarative. See declarative data integrity

exercise applying data validation rules to tables, 214

procedural. See procedural data integrity

data mining

AMO classes for, 155

AMO for controlling/managing, 154

exercise creating data mining structure, 166–168

references, 154, 190

SQL Server Business Intelligence Development Studio, 155

data partitioning. See partitioning

data providers

ADO.NET, 6, 438

available for linked servers, 51

selecting, 4–5

Data Source Configuration Wizard, 33

Data Source Name (DSN), 8

data sources

accessing multiple, 51–54

connection objects, 6

creating, 158

DataSets from, 34

exercise creating data mining structure, 166–168

heterogeneous, 51

when to use CLR and, 437

data stewardship, 360

data storage optimization, 332–338

data types, 334–335

de-normalizing, 333–334

exercise de-normalizing an aggregation, 335–338

overview of, 332

row width, 332–333

data types, 334–335

alias, 335

columns using large data types, 308

consistent use of, 335

overview of, 334

page reads and, 286

procedures compared with XPs, 433

restrictive data types for preventing SQL injection attacks, 211

selecting appropriate, 335

smallint, 62

variable-length, 332

xml data type, 80

data validation. See data integrity

data warehousing databases, 358

database engine, 451–454

flushing data pages to disk, 451

flushing log pages to disk, 452

overview of, 451

read-ahead, 453–454

database optimization

data storage. See data storage optimization

indexes. See index performance

queries. See query performance

databases

compatibility level settings, 57

exercise creating database with SMO, 160–166

datagrid controls, 99

DataReaders

compared with DataSets, 35

exercise retrieving data with, 40–42

in ADO.NET connected object model, 35

using, 35

DataSet Designer, 33

DataSets

ADOMD object, 158

benefits of, 33

compared with DataReaders, 35

creating, 33

data sources for, 34

exercise retrieving data, 36–39

making changes to, 34

stored in cache, 103

supporting ADO.NET disconnected model, 33

DataSource. See data sources

DataSourceView

creating, 158

exercise creating data mining structure, 166–168

datetime values, cache expiration and, 108

DDL (Data Definition Language), 145

deadlocks

causes of, 240

exercise protecting against, 276

minimizing, 266

DEALLOCATE keyword, cursors and, 121

declarative data integrity, 195–208

check and foreign keys for improving query performance, 206–208

check constraints, 199

defined, 195

exercise comparing with procedural data integrity, 213

extending check constraints with 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