| Developing a Logical Data Model |
| Group data into
entities by applying normalization rules. |
| Identify primary
keys. |
| Choose the foreign
key that will enforce a relationship between
entities and that will ensure referential
integrity. |
| Identify the business
rules that relate to data integrity. |
| Incorporate business
rules and constraints into the data model. |
| In a given situation,
decide whether denormalization is appropriate. |
| Deriving
the Physical Design |
| Assess the potential
impact of the logical design on performance,
maintainability, extensibility, scalability,
availability, and security. |
| Creating
Data Services |
| Access data by using
the dynamic SQL model. |
| Access data by using
the stored procedure model. |
Manipulate data by
using Transact-SQL cursors.
- Choose the appropriate type of cursor.
- Define the appropriate level of sensitivity
to change.
- Choose the appropriate navigation.
- Choose the scope of the cursor, specifically
global or local.
|
Create and manage
explicit, implicit, and distributed transactions
to ensure data consistency and recoverability.
- Define the transaction isolation level.
- Design transactions of appropriate length.
- Avoid or handle deadlocks.
- Use optimistic locking appropriately.
- Implement error handling by using
@@trancount.
|
| Write INSERT, DELETE,
UPDATE, and SELECT statements that retrieve and
modify data. |
| Write Transact-SQL
statements that use joins or subqueries to combine
data from multiple tables. |
| Create scripts by
using Transact-SQL. Programming elements include
control-of-flow techniques, local and global
variables, functions, and error handling
techniques. |
Design, create, use,
and alter views.
- Modify data through a view.
- Query data through a view.
|
Create and execute
stored procedures to enforce business rules, to
modify data in multiple tables, to perform
calculations, and to use input and output
parameters.
- Implement error handling by using return
codes and the RAISERROR statement.
- Choose appropriate recompile options.
|
Create triggers that
implement rules, that enforce data integrity, and
that perform cascading updates and cascading
deletes.
- Implement transactional error handling.
|
| Create result sets
that provide summary data. Query types include TOP
n PERCENT and GROUP BY, specifically
HAVING, CUBE, and ROLLUP. |
| Configure
session-level settings. |
Access data from
static or dynamic sources by using remote stored
procedures, linked servers, and OPENROWSET.
- Evaluate where processing occurs when using
OPENQUERY.
|
| Creating
a Physical Database |
| Create and manage
files, filegroups, and transaction logs that
define a database. |
Create tables that
enforce data integrity and referential integrity.
- Choose the appropriate data types.
- Create user-defined data types.
- Define columns as NULL or NOT NULL.
- Define columns to generate values by using
the IDENTITY property, the
uniqueidentifier data type, and the NEWID
function.
- Implement constraints.
|
Create and maintain
indexes.
- Choose an indexing strategy that will
optimize performance.
- Given a situation, choose the appropriate
type of index to create.
- Choose the column or columns to index.
- Choose the appropriate index
characteristics, specifically FILLFACTOR,
DROP_EXISTING, and PAD_INDEX.
|
| Populate the database
with data from an external data source. Methods
include the bulk copy program and Data
Transformation Services (DTS). |
| Implement full-text
search. |
| Maintaining a Database |
| Evaluate and optimize
the performance of an execution plan by using DBCC
SHOWCONTIG, SHOWPLAN_TEXT, SHOWPLAN_ALL, and
UPDATE STATISTICS. |
| Evaluate and optimize
the performance of query execution plans. |
| Diagnose and resolve
locking problems. |
| Identify SQL Server
events and performance problems by using SQL
Server Profiler. |
