D
DAO (Data Access Objects), 401
data caching, 443–444
Data Control Language (DCL)
DENY statement, 123, 347
GRANT statement, 123, 346–347
overview, 123–124
REVOKE statement, 123, 347
Data Definition Language (DDL)
ALTER statement, 117, 322
CREATE statement, 117, 322
DROP statement, 117, 322
objects, altering, 121–122
objects, dropping, 122–123
overview, 117–118
scripting practices, 120–123
stored procedure, creation of, 118–119
table, creation of, 117–118
trigger, creation of, 119
user-defined function, creation of, 119–120
view, creation of, 118
data, information transformed into, 33–34
data integrity
connections, 16
described, 16
locks, 16–18
transactions, 16
Data Manipulation Language (DML)
Camel Case naming standard, 112
comments, 112–114
Hungarian Notation naming standard, 112
in-line comments, 113–114
layers, queries having, 108
long names naming standard, 112
lower-case, delimited naming standard, 112
naming conventions, 110–112
overview, 107
Pascal Case naming standard, 112
query syntax, 109–110
row-based operations, 109
script generation, 115
script management, 116
SELECT statement, 108
set-based operations, 109
templates, 114–115
version control, 116–117
data objects
CALLER option, 344
check constraint, 333–334
constraints, 331–336
CREATE FUNCTION statement, 344
CREATE PROCEDURE statement, 340–343
CREATE TABLE statement, 325–330
CREATE VIEW statement, 336–339
defaults, 329–330
DENY statement, 347
EXECUTE AS option, 344
foreign key constraint, 334–336
GRANT statement, 346–347
IDENTITY property, 326–329
indexed views, 339–340
naming, 322–325
nullability, 325–326
overriding constraints, 336
OWNER option, 344
parameters, 341–342
primary key constraint, 331–333
REVOKE statement, 347
SCHEMABINDING option, 338
securing, 344–347
SELF option, 344
unique constraint, 333
UniqueIdentifier type, 330–331
USER_NAME option, 344
WITH CHECK option, 338
WITH ENCRYPTION option, 338–339, 342
WITH RECOMPILE statement, 343
DATA (reserved word), 465
data retrieval
SELECT statement, 128–156
storage and retrieval, 127–128
data transactions
ACID test, 254
adding records, 256–263
auto-commit transactions, 254
checkpoint, 273–274
consistency of, 254
criteria for, 254
defaults, inserting, 259–261
DELETE statement, 269–270
durability of, 254
explicit transactions, 254, 273–276
filtering updates, 264–266
implicit transactions, 254
INSERT statement, 257–258
isolation of, 254
logged operations, 256
modifying records, 263–269
multiple tables, deleting records based on, 271–272
multiple tables, updating rows based on, 266–267
NULL values, inserting, 259–261
overview, 253–254
removing records, 269–273
rows from another table, inserting, 261–262
SELECT INTO statement, 262
SELECT statement, 258
stored procedures, managing inserts using, 262–263
stored procedures, updating records using, 267–269
transaction logs, 255–256
TRUNCATE TABLE statement, 272–273
types of, 254
unity of, 254
UPDATE statement, 264–269
views, updating using, 267
WHERE clause, 270
Data Transformation Services (DTS), 256
data types, 8–13
database engine procedures, 485–488
database maintenance procedures, 489
database management system (DBMS)
described, 4
hierarchical database management systems (HDBMS), 4
network database management systems (NDBMS), 4
object-oriented database management systems (ODBMS), 4
object-relational database management systems (ORDBMS), 4–5
relational database management systems (RDBMS), 4
database objects
altering, 322
creating, 322
dropping, 322
overview, 321–322
DATABASE (reserved word), 461
Database Tuning Advisor, 73
database..object, 449
DATABASEPROPERTY() function, 194, 476
DATABASEPROPERTYEX() function, 194, 476
database.schema.object, 449
DATALENGTH() function, 200, 203–204, 480
datasets, 418–423
DATA_TYPE view, 510, 512, 514, 515
date and time, altering, 456
date functions
DATEADD() function, 176–178, 474
DATEDIFF() function, 178–179, 474
DATENAME() function, 180, 474
DATEPART() function, 180, 474
DAY() function, 181, 474
GETDATE() function, 180–181, 474
GETUTCDATE() function, 180–181, 474
MONTH() function, 181, 474
overview, 176
YEAR() function, 181, 474
DATE (reserved word), 463, 465
DATEADD() function, 176–178, 474
DATEDIFF() function, 178–179, 474
@@DATEFIRST, 467
DATENAME() function, 180, 474
DATEPART() function, 180, 474
DATETIME_PRECISION view, 511, 512, 514, 515
DAY() function, 181, 474
DAY (reserved word), 463, 465
DBCC (reserved word), 461
DB_ID() function, 195, 476
DBMS. See Database Management System (DBMS)
DB_NAME() function, 195, 476
@@DBTS, 467
DCL. See Data Control Language (DCL)
DDL. See Data Definition Language (DDL)
DEALLOCATE (reserved word), 461, 463
DEC (reserved word), 463, 465
DECIMAL (reserved word), 463, 465
DECLARE @ local_variable command, 455–456
DECLARE (reserved word), 461, 463
default query view, 419
DEFAULT (reserved word), 461, 463
DEFAULT_CHARACTER_SET_CATALOG view, 516
DEFAULT_CHARACTER_SET_NAME view, 516
DEFAULT_CHARACTER_SET_SCHEMA view, 516
defaults
inserting, 259–261
naming, 324
overview, 329–330
DEFERRABLE (reserved word), 464, 466
DEFERRED (reserved word), 464, 466
DEGREES() function, 193, 475
DELETE command, 269–270, 455
Delete procedure, 374
DELETE (reserved word), 461, 464
DELETE_RULE view, 514
delivery time constraints, 400
DENSE_RANK() function, 197–198, 477
DENY command, 123, 347
DENY (reserved word), 461
DEPTH (reserved word), 466
DEREF (reserved word), 466
derived columns, 135–137
derived table, creating, 287
DESC (reserved word), 461, 464
DESCRIBE (reserved word), 464, 466
DESCRIPTOR (reserved word), 464, 466
design patterns, 397
desktop database applications, 400–401
DESTROY (reserved word), 466
DESTRUCTOR (reserved word), 466
deterministic functions, 161–162
DETERMINISTIC (reserved word), 466
DIAGNOSTICS (reserved word), 464, 466
diagram pane in Query Designer tool, 53
DICTIONARY (reserved word), 466
DIFFERENCE() function, 304, 478
DISCONNECT (reserved word), 464, 466
DISK (reserved word), 461
DISTINCT (reserved word), 461, 464
distributed queries procedures
sp_addlinkedserver, 489
sp_addlinkedsrvlogin, 489
sp_catalogs, 489
sp_column_privileges_ex, 489
sp_columns_ex, 489
sp_droplinkedsrvlogin, 489
sp_foreignkeys, 489
sp_indexes, 490
sp_linkedservers, 490
sp_primarykeys, 490
sp_serveroption, 490
sp_table_privileges_ex, 490
sp_tables_ex, 490
DISTRIBUTED (reserved word), 461
Distributed Transaction Coordinator (DTC), 409
DML. See Data Manipulation Language (DML)
docking window placement in SQL Server Management Studio, 77–80
DOMAIN (reserved word), 464, 466
DOMAIN_CATALOG view, 509, 510, 511, 512, 514
DOMAIN_CONSTRAINTS views
CONSTRAINT_CATALOG view, 512
CONSTRAINT_NAME view, 512
CONSTRAINT_SCHEMA view, 512
DOMAIN_CATALOG view, 512
DOMAIN_NAME view, 512
DOMAIN_SCHEMA view, 512
INITIALLY_DEFERRED view, 512
IS_DEFERRABLE view, 512
DOMAIN_DEFAULT view, 512
DOMAIN_NAME view, 509, 511, 512, 514
DOMAINS views
CHARACTER_MAXIMUM_LENGTH view, 512
CHARACTER_OCTET_LENGTH view, 512
CHARACTER_SET_CATALOG view, 512
CHARACTER_SET_NAME view, 512
CHARACTER_SET_SCHEMA view, 512
COLLATION_CATALOG view, 512
COLLATION_NAME view, 512
COLLATION_SCHEMA view, 512
DATA_TYPE view, 512
DATETIME_PRECISION view, 512
DOMAIN_CATALOG view, 512
DOMAIN_DEFAULT view, 512
DOMAIN_NAME view, 512
DOMAIN_SCHEMA view, 512
NUMERIC_PRECISION view, 512
NUMERIC_PRECISION_RADIX view, 512
NUMERIC_SCALE view, 512
DOMAIN_SCHEMA view, 509, 511, 512, 514
double quotes, 313
DOUBLE (reserved word), 461, 464
down arrow icon in SQL Server Management Studio, 75
DROP (reserved word), 461, 464
DROP statement, 117, 322
dropping database objects, 322
DTC (Distributed Transaction Coordinator), 409
DTD_IDENTIFIER view, 516
DTS (Data Transformation Services), 256
DUMMY (reserved word), 461
DUMP (reserved word), 461
DYNAMIC (reserved word), 466
EAN: 2147483647
Pages: 131