D

DAO (Data Access Objects), 12

data, sending to and from stored procedures, 410–412

data access, 7

Data Access Objects (DAO), 12

data conservation, 445

data consistency rule, 498–499

Data Definition Language (DDL), 26, 82, 308

data ecology or farming, 449

data factors, 4–7

data files, 143

data integrity, 355–378

and business rules, 357–359

configuring and programming, 366–375

check constraints, 370–372

default values, 367–370

foreign key and primary key constraints, 372–375

overview, 366

constraints, 6–7, 375–377

database and table, 359

domain, 362

entity, 360–361

mechanisms, 34–36

in operational data, 441–442

overview, 6–7, 355–357

planning for, 363–366

referential, 359–360

and SQL Server tables, 34–36

transaction, 363

transition, 362–363

type, 361

user-defined types, 377–378

Data Manipulation Language (DML), 82, 423, 441–442

data migration, 212

data mining, 212

data objects, 99

data pages, 39, 63–64

data partitioning, 140

data sampling, 540

data source, BULK INSERT statements, 432

data streams, 81

data tiers, 385, 400

Data Transformation Services (DTS), 84, 213

data types, 299–308

approximate numerics, 302

binary and varbinary, 304

char or varchar, 303

cursor, 305

datetime and smalldatetime, 302

and domains in SQL Server tables, 28

exact numerics, 301–302

nchar and nvarchar, 304

ntext, text, and image, 304–305

overview, 301

precedence of, 299–300

rules for, 452

sql_variant, 305–306

synonyms, 308–309

table, 306–307

timestamp, 307

and UNION operators, 475–476

uniqueidentifier, 307–308

XML, 308

data_type arguments, T-SQL conversion functions, 322

database, SQL Server Backup General page, 194

database administrators (DBAs), 3, 7, 11–12, 256–257

Database attributes, 252

database backups, 179–180, 188–192

Database Console Commands (DBCC) statements, 534, 556–557

Database Diagrams, 170–172

database engine, 75–94

client/server architecture, 16

configuring SQL Server’s mailing engines, 91–93

maintenance plans, 93–94

Management Studio, 83–86

overview, 4–5, 75

server-side components, 75–77

SQL administrative architecture, 81–82

SQL Server Agent, 87–91

SQL Server automated administration architecture, 86–87

SQL Server connections, 78–81

SQL Server tools, 82–83

Database Engine Tuning Advisor, Microsoft SQL Server 2007, 175

database files, 40–41

database locks, 491

Database Mail, 91–93

Database Management System (DBMS), 49–73

architecture, 12

components, 51–52

data streams, 71–72

I/O architecture, 57–58

login security plans, 128–134

creating and configuring logins, 130–131

database roles, 129–130

overview, 128

securing objects with GRANT, DENY and REVOKE, 133–134

surface area configuration, 128

users, 132–133

memory, 52–57

address space, 56–57

Address Windowing Extensions, 55–56

overview, 52–53

page files and virtual memory manager, 53–54

paging, 54–55

overview, 16, 21, 49

queries, 64–71

caching and reusing execution plans, 70–71

optimization of, 66

overview, 64–66

processing, 66–70

reading and writing data pages, 63–64

relational database engine, 52

security, 118–127

GUIDS for nontrusted users, 127

overview, 118

permissions, 124–126

roles, 120–124

users, 118

SQL Server security plan and, 135–136

thread and task architecture, 58–59

TPC benchmarks, 50–51

transactions, 60–62

database mirroring, 15

Database Mirroring Security Wizard, 246–249

Database Properties dialog box, Management Studio, 237

Database Snapshots, 139

database_name argument, 165

DATABASEPROPERTY function, 572

DATABASEPROPERTYEX function, 572

databases, 21–47, 137–176

ALTER DATABASE statement, 150–158

arguments, 153–158

syntax, 150–153

FOR ATTACH keyword, 147

backup maintenance plan, 208–209

client and server roles, 47

collation name, 147

CREATE DATABASE statements, 144–146

creating with Copy Database Wizard, 160–162

creating with Management Studio, 158–159

examples, 149–150

file system, 138–140

filegroups, 140–142

FILEGROWTH keyword, 149

FILENAME keyword, 148

indexes, 173–174

ON keyword, 146–147

LOG ON keyword, 147

MAXSIZE keyword, 149

moving data, log, and full-text files, 172–173

NAME keyword, 148

overview, 11–12, 21–22, 137

performance, optimization, and tuning, 175–176

PRIMARY keyword, 148

restoring, 198–202

securing with GRANT, DENY, or REVOKE, 134

SIZE keyword, 148

SQL Server database definitions, 22–25

SQL Server security plan and, 136

and table integrity, 359

tables, 162–172

creating table with Management Studio, 170

Database Diagrams, 170–172

overview, 162–163

target, and BULK INSERT statements, 432

UNLIMITED keyword, 149

users, 119

Databases SQL Server object, 547

data-bound controls, 404, 448

DATALENGTH function, 312–313, 572

Date, Chris, 98, 357

date and time functions, T-SQL, 319

DATEADD function, 320, 572

DATEDIFF function, 320, 572

DATENAME function, 320, 572

DATEPART function, 320, 572

datetime constants, 292

datetime data type, 302, 572

DAY function, 320, 572

db_accessadmin fixed database role, 123

db_backupoperator fixed database role, 123

db_datareader fixed database role, 123

db_datawriter, 123, 442

db_ddladmin fixed database role, 123

db_denydatareader fixed database role, 123

db_denydatawriter fixed database role, 123

DB_ID function, 572

DB_NAME function, 573

db_owner fixed database role, 123, 442

db_securityadmin fixed database role, 123, 442

db_update_option, 156

db_user_access_option, 156

DBAs (database administrators), 3, 7, 11–12, 256–257

DBCC (Database Console Commands) statements, 534, 556–557

DBCC CHECKALLOC function, 573

DBCC CHECKCATALOG function, 573

DBCC CHECKCONSTRAINTS function, 573

DBCC CHECKDB function, 573

DBCC CHECKFILEGROUP function, 573

DBCC CHECKIDENT function, 573

DBCC CHECKTABLE function, 573

DBCC CLEANTABLE function, 573

DBCC CONCURRENCYVIOLATION function, 573

DBCC DBREINDEX function, 573

DBCC DBREPAIR (switch around) function, 573

DBCC dllname (FREE) function, 573

DBCC DROPCLEANBUFFERS function, 573

DBCC FREEE SESSIONCACHE function, 573

DBCC FREEPROCCACHE function, 573

DBCC FREESYSTEMCACHE function, 573

DBCC function, 573

DBCC HELP function, 573

DBCC INDEXDEFRAG function, 573

DBCC INPUTBUFFER function, 574

DBCC OPENTRAN function, 574

DBCC OUTPUTBUFFER function, 574

DBCC PINTABLE (DEPRACTED) function, 574

DBCC PROCCACHE function, 574

DBCC SHOW_STATISTICS function, 574

DBCC SHOWCONTIG (SWITCH) function, 574

DBCC SHRINKDATABASE function, 574

DBCC SHRINKFILE function, 574

DBCC SQLPERF function, 574

DBCC TRACEOFF function, 574

DBCC TRACEON function, 574

DBCC TRACESTATUS function, 574

DBCC UNPINTABLE function, 574

DBCC UPDATEUSAGE function, 574

DBCC USEROPTIONS function, 574

dbcreator fixed server role, 122

DBMS. See Database Management System (DBMS)

DDL (Data Definition Language), 26, 82, 308

DDNS (Dynamic Domain Name Service), 285

deadlocks, 62, 495–497, 535

DEALLOCATE function, 506, 574

debtors tables, 26

debugging

cross-language, 337

stored procedures, 408–409

decimal constants, 293

decimal data type, 301, 308, 574

decision support systems (DSSs), 19, 50–51

declarative referential integrity (DRI), 360

DECLARE @ local_variable, 575

DECLARE @variable statement, 436

DECLARE CURSOR statements, 468, 504, 506, 575

DECLARE statements, T-SQL, 309

DecryptBy… commands, 575

DEFAULT argument, 166–167

default constraint objects, 328

default filegroups, 42, 141–142

default monitored network addresses, 80

default values, 367–370, 411, 425–428

deferred name resolution, 391

DEGREES function, 318, 575

delayed name resolution, 500

DELETE statements, 439–442, 525–526, 575

deletes

cascaded, 6

SQL Server logins and users, 133

delimited identifiers, 329

DENSE_RANK function, 575

DENY function, 133–134, 575

dependencies, 264, 284

deployment

assemblies as units of, 348

.NET Framework, 9

stored procedure, 405–406

deployment level data integrity, 358

derived tables, 460–461

DESC argument, 467

description, SQL Server Backup General page, 196

Design Table, Management Studio, 368–370, 374

design-time reality, CLR, 336

destination, SQL Server Backup General page, 196

destination folders, 284

destination servers. See mirroring

determinism, function, 310–311

DIFFERENCE function, 313, 575

Differential Changed Map page, 39

differential database backups, 188, 191

digital certificates, 117

Digital Linear Tape (DLT) systems, 182–183

digital nervous system (Dns), 8

digital signatures, 350

direct recursion, 392

dirty buffer pages, 63–64

dirty reads, 488–489

DISABLE TRIGGER function, 575

disaster recovery, 177–209

backing up SQL Server, 193–202

Management Studio, 194–198

overview, 193–194

restoring database to point in time, 201–202

T-SQL backup scripts, 201

backup and restore architecture, 187–188

backup bandwidth, 202–203

backup devices, 192–193

backup maintenance plan, 206–209

backup/restore concepts, 203–206

baselines for, 178–182

database backup types, 188–192

overview, 177

recovery architecture, 184–187

transaction recovery, 182–184

Disconnect menu, Management Studio, 84

disk configuration and cluster storage, 270–271

Disk I/O performance, 555

disk quota thresholds, 552

disk subsystems, 175

disk usage, 552

diskadmin fixed server role, 122

disks

backup, 192–193

physical or logical, 142

DISTINCT keyword, 454, 468

distributed database architecture, 211–253

fail-over, 250–252

log shipping, 235–243

basics of, 236–237

configuring manually, 237

configuring with Management Studio, 237–242

overview, 235

SQL Server Agent jobs, 243

mirroring, 244–249

creating mirror, 246–249

fail-over, 245

mirror session, 245–246

overview, 244–245

resolving errors, 249–250

witness server, 245

overview, 211

standby servers, 233–235

distributed network applications, 7–9

distributed partitioned views, 16

distributed queries, 503

Distributed Transaction Coordinator (DTC). See Microsoft Distributed Transaction Coordinator (MSDTC)

distributed transactions, 502–503

Distributor component, SQL Server replication services, 215

distributor setup options, Management Studio, 226–227, 231–232

Divide (/) operator, 559

Divide (/) operators, 295

DLL (dynamic linked libraries), 37

DLT (Digital Linear Tape) systems, 182–183

DML (Data Manipulation Language), 82, 423, 441–442

Dns (digital nervous system), 8

DNS (Domain Name Service), 114, 284–285

dollar ($) signs, 329

domain integrity, 362

Domain Name Service (DNS), 114, 284–285

domains, 11, 447–448

dot notation, .NET Framework, 351

DRI (declarative referential integrity), 360

DROP AGGREGATE function, 575

DROP APPLICATION function, 575

DROP ASSEMBLY function, 575

DROP ASYMMETRIC KEY function, 575

DROP CERTIFICATE function, 575

DROP CONTRACT function, 575

DROP CREDENTIAL function, 575

DROP DATABASE function, 575

DROP DEFAULT function, 575

DROP ENDPOINT function, 575

DROP EVENT NOTIFICATION function, 575

DROP FULLTEXT CATALOG function, 575

DROP FULLTEXT INDEX function, 575

DROP FUNCTION function, 576

DROP INDEX function, 576

DROP LOGIN function, 576

DROP MASTER KEY function, 576

DROP MESSAGE TYPE function, 576

DROP PARTITIION SCHEME function, 576

DROP PARTITION FUNCTION function, 576

DROP PROCEDURE function, 408, 576

DROP QUEUE function, 576

DROP REMOTE SERVICE BINDING function, 576

DROP ROLE function, 576

DROP ROUTE function, 576

DROP RULE function, 576

DROP SCHEMA function, 576

DROP SERVICE function, 576

DROP SIGNATURE function, 576

DROP STATISTICS function, 576

DROP SYMMETRIC KEY function, 576

DROP TABLE function, 576

DROP TRIGGER function, 576

DROP TYPE function, 576

DROP USER function, 576

DROP VIEW function, 576

DROP XML SCHEMA function, 576

dropping stored procedures, 414

DROPY SYNONYM function, 576

DSSs (decision support systems), 19, 50–51

DTA.EXE command-line, 83

DTC (Distributed Transaction Coordinator). See Microsoft Distributed Transaction Coordinator (MSDTC)

DTS (Data Transformation Services), 84, 213

DUMP function, 577

duplicate records, 430

durability rule, transaction, 498–499

dynamic assemblies, 345–346

Dynamic Domain Name Service (DDNS), 285

dynamic filters, 218–219

dynamic linked libraries (DLL), 37

dynamic locking, 490–491, 495