Skills measured by exam 70-029 |
Course 833 |
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. |
|