Exam 70-229

Designing and Implementing Databases with Microsoft SQL Server 2000 Enterprise Edition

These notes began with the "Skills Being Measured" on Microsoft's page for this exam as of February 8, 2003.

All hierarchically lettered and numbered items are directly from this MS list. Almost all links lead to http://msdn.microsoft.com online pages, but there are some that lead elsewhere.

The list is by no means complete or error free. Please if you find any additional links or ones that I've listed that are broken or irrelevant.

I took this exam during its beta on February 25, 2001, and scored 909. I also participated in a 'cut score' meeting at MS where they solicited feedback on the score to expect from someone who has the skills listed below.

I think this exam is relatively difficult compared to most MS exams, though I found it a little more straightforward than the SQL 7 version, 70-029. Many testers I've talked to have had problems with running short of time at the end, so I recommend paying attention to the clock as you go.

Skills Being Measured

This certification exam measures your ability to design and implement database solutions by using Microsoft SQL Server 2000 Enterprise Edition. Before taking the exam, you should be proficient in the job skills listed below.

1. Developing a Logical Data Model
1. Define entities. Considerations include entity composition and normalization.
   1. Specify entity attributes.
      Tables
      Tables [Yes, it's different. "Tables in a Database Diagram" also includes sample graphics similar to table diagrams on the exam.]
      Designing Tables
      Specifying a Column Data Type
       
   2. Specify degree of normalization.
      Support WebCast: Database Normalization Basics [support.microsoft.com - be sure to check the "Supplemental reading" links]
      SWYNK.COM: Database Normalization [swynk.com]
      SQL by Design: Why You Need Database Normalization [sqlmag.com]
      Normalization
       
2. Design entity keys. Considerations include FOREIGN KEY constraints, PRIMARY KEY constraints, and UNIQUE constraints.
   1. Specify attributes that uniquely identify records.
      Creating and Modifying Identifier Columns
      IDENTITY (Property)
      SCOPE_IDENTITY
      SET IDENTITY_INSERT
      Using IDENTITY Values with Replication
      uniqueidentifier
      Using uniqueidentifier Data
      PRIMARY KEY Constraints
      Creating and Modifying PRIMARY KEY Constraints
      UNIQUE Constraints
      Creating and Modifying UNIQUE Constraints
      Using Unique Indexes
      Triggers
       
   2. Specify attributes that reference other entities.
      FOREIGN KEY Constraints
      Creating and Modifying FOREIGN KEY Constraints
      Triggers
       
3. Design attribute domain integrity. Considerations include CHECK constraints, data types, and nullability.
   1. Specify scale and precision of allowable values for each attribute.
      Precision, Scale, and Length
       
   2. Allow or prohibit NULL for each attribute.
      Allowing Null Values
      Null Values
       

   3. Specify allowable values for each attribute.
      Constraints
      CHECK Constraints
      Triggers
       

2. Implementing the Physical Database
1. Create and alter databases. Considerations include file groups, file placement, growth strategy, and space requirements.
   Physical Database Files and Filegroups
   Files and Filegroups
   CREATE DATABASE
   Using Files and Filegroups to Manage Database Growth
   Estimating the Size of a Table
   Estimating the Size of a Table Without a Clustered Index
   Estimating the Size of a Table with a Clustered Index
    
   1. Specify space management parameters. Parameters include autoshrink, growth increment, initial size, and maxsize.
      Shrinking Databases
      CREATE DATABASE
       
   2. Specify file group and file placement. Considerations include logical and physical file placement.
      Data Placement Using Filegroups
       
   3. Specify transaction log placement. Considerations include bulk load operations and performance.
      Optimizing Transaction Log Performance
      Optimizing Bulk Copy Performance
       
2. Create and alter database objects. Objects include constraints, indexes, stored procedures, tables, triggers, user-defined functions, and views.
   Constraints
   CREATE INDEX
   Creating a Stored Procedure
   CREATE PROCEDURE
   ALTER PROCEDURE
   Creating and Modifying a Table
   CREATE TABLE
   ALTER TABLE
   Creating a Trigger
   CREATE TRIGGER
   ALTER TRIGGER
   User-Defined Functions
   SQL User-Defined Functions
   User-Defined Function Recommendations
   User-Defined Function Samples
   Deterministic and Nondeterministic Functions
   Inline User-Defined Functions
   CREATE FUNCTION
   ALTER FUNCTION
   Views
   SQL Views
   Creating a View
   CREATE VIEW
   Modifying and Renaming a View
   ALTER VIEW
    
   1. Specify table characteristics. Characteristics include cascading actions, CHECK constraints, clustered, defaults, FILLFACTOR, foreign keys, nonclustered, primary key, and UNIQUE constraints.
      Cascading Referential Integrity Constraints
      CHECK Constraints
      Clustered Indexes
      Using Clustered Indexes
      DEFAULT Definitions
      Creating and Modifying DEFAULT Definitions
      Fill Factor
      fill factor Option
      CREATE INDEX
      FOREIGN KEY Constraints
      Creating and Modifying FOREIGN KEY Constraints
      Nonclustered Indexes
      Using Nonclustered Indexes
      PRIMARY KEY Constraints
      Creating and Modifying PRIMARY KEY Constraints
      UNIQUE Constraints
      Creating and Modifying UNIQUE Constraints
      Using Unique Indexes
       
   2. Specify schema binding and encryption for stored procedures, triggers, user-defined functions, and views.
      Programming Stored Procedures [See "Encrypting Procedure Definitions"]
      Programming Triggers [See "Encrypting Trigger Definitions"]
      CREATE FUNCTION
      CREATE VIEW
       
   3. Specify recompile settings for stored procedures.
      Recompiling a Stored Procedure
       
   4. Specify index characteristics. Characteristics include clustered, FILLFACTOR, nonclustered, and uniqueness.
      CREATE INDEX
      Clustered Indexes
      Using Clustered Indexes
      Fill Factor
      fill factor Option
      Nonclustered Indexes
      Using Nonclustered Indexes
      Using Unique Indexes
       
3. Alter database objects to support replication and partitioned views.
   How Replication Works
   Implementing Replication
   Managing Identity Values
   Using IDENTITY Values with Replication
   Using NOT FOR REPLICATION
   Sample Chapter from Microsoft® SQL Server 2000™ Performance Tuning Technical Reference by Edward Whalen, Marcilina Garcia, Steve Adrien DeLuca, and Dean Thompson
   Designing Federated Database Servers
   Creating a Partitioned View
   Using Partitioned Views
    
   1. Support merge, snapshot, and transactional replication models.
      Introducing the Types of Replication
      Replication Data Considerations
      Data Needs and Characteristics
      Planning for Merge Replication
      Planning for Snapshot Replication
      Planning for Transactional Replication
       
   2. Design a partitioning strategy.
      Designing Partitions
       
   3. Design and create constraints and views.
      Microsoft SQL Server - Partitioning the Data in a Table - SQLTeam.com [sqlteam.com]
      CHECK Constraints
      CREATE VIEW
       
   4. Resolve replication conflicts.
      Merge Replication Conflict Detection and Resolution
      Microsoft Resolver Descriptions
      Choosing a Resolver
       
4. Troubleshoot failed object creation.
   Creating an Indexed View [See "Requirements for the View"]
   User-Defined Functions [See "Schema-Bound Functions"]
    
3. Retrieving and Modifying Data
1. Import and export data. Methods include the bulk copy program, the Bulk Insert task, and Data Transformation Services (DTS).
   Preparing Data for Importing and Exporting
   Using bcp and BULK INSERT
   Logged and Minimally Logged Bulk Copy Operations
   Parallel Data Loads
   Constraint Checking
   BULK INSERT
   DTS Overview [many links]
   Bulk Insert Task
    
2. Manipulate heterogeneous data. Methods include linked servers, OPENQUERY, OPENROWSET, and OPENXML.
   Distributed Queries
   Distributed Query Architecture
   Configuring Linked Servers
   sp_addlinkedserver
   Identifying a Data Source Using a Linked Server Name
   OPENQUERY
   OPENROWSET
   OPENDATASOURCE
   Using OPENXML
   Writing XML Using OPENXML
   OPENXML
    

3. Retrieve, filter, group, summarize, and modify data by using Transact-SQL.
   Accessing and Changing Relational Data Overview [many links]
   Parts of a SELECT Statement
   SELECT [many links]
   Subquery Fundamentals
   Join Fundamentals
   Summarizing Data
   Grouping Rows with GROUP BY
   GROUP BY and Null Values
   GROUP BY and ALL
   Summarizing Data
   Summarizing Data Using CUBE
   Summarizing Data Using ROLLUP
   SELECT Examples
   Adding Data
   INSERT
   Inserting Rows Using SELECT INTO
   Deleting Rows with DELETE
   Deleting All Rows Using TRUNCATE TABLE
   Changing Data with UPDATE
    

4. Manage result sets by using cursors and Transact-SQL. Considerations include locking models and appropriate usage.
   Cursors
   Cursor Locking
   Cursor Transaction Isolation Levels
   Cursor Concurrency
   Cursor Implementations
   Changing Rows with Positioned Operations
   cursor threshold Option
   Application Design [See "Do not use cursors more than necessary."]
   Multirow Considerations [See Note after 2nd paragraph]
   Query Tuning Recommendations
   Performance Tuning SQL Server Database Cursors [sql-server-performance.com]
   T-SQL Blackbelt [sqlmag.com - See "Sequential to Set-Based", "Matching Transactions", "Grouping Time Intervals", "Identifying Trends"]
    

5. Extract data in XML format. Considerations include output format and XML schema structure.
   Retrieving and Writing XML Data
   Guidelines for Using the FOR XML Clause
   Basic Syntax of the FOR XML Clause
   Using AUTO Mode
   Using RAW Mode
   Using EXPLICIT Mode
    
4. Programming Business Logic
1. Manage data manipulation by using stored procedures, transactions, triggers, user-defined functions, and views.
   1. Implement error handling in stored procedures, transactions, triggers, and user-defined functions.
      Error Handling
      Using @@ERROR
      Using RAISERROR
       
   2. Pass and return parameters to and from stored procedures and user-defined functions.
      Executing a Stored Procedure
      Parameters
      Returning Data from a Stored Procedure
      Returning Data Using a Return Code
      Returning Data Using OUTPUT Parameters
      SQL User-Defined Functions
      User-Defined Functions That Return a table Data Type
      Invoking User-Defined Functions That Return a Scalar Value
       
   3. Validate data.
      Validating User Input [Added May 12, 2003]
       
2. Enforce procedural business logic by using stored procedures, transactions, triggers, user-defined functions, and views.
   Programming Stored Procedures
   Enforcing Business Rules with Triggers
    
   1. Specify trigger actions.
      Cascading Referential Integrity Constraints
      Trigger Execution
       
   2. Design and manage transactions.
      Controlling Transactions
      Coding Efficient Transactions
      Isolation Levels
      Locking Hints
       
   3. Manage control of flow.
      Control-of-Flow
      Using @@ERROR
      Using RAISERROR
       
   4. Filter data by using stored procedures, triggers, user-defined functions, and views.
      Scenarios for Using Views
      Using Views as Security Mechanisms
      Using Stored Procedures as Security Mechanisms
       
3. Troubleshoot and optimize programming objects. Objects include stored procedures, transactions, triggers, user-defined functions, and views.
   SQL Stored Procedures
   Rollbacks in Stored Procedures and Triggers
   Coding Efficient Transactions
   Effects of Transactions and Batches on Application Performance
   Minimizing Deadlocks
   Designing Triggers
   SQL User-Defined Functions
   User-Defined Function Recommendations
   SQL Views
   View Indexes
   Deferred Name Resolution and Compilation
    
5. Tuning and Optimizing Data Access
1. Analyze the query execution plan. Considerations include query processor operations and steps.
   Graphically Displaying the Execution Plan Using SQL Query Analyzer
    
2. Capture, analyze, and replay SQL Profiler traces. Considerations include lock detection, performance tuning, and trace flags.
   Creating and Managing Traces and Templates
   How to create a trace (SQL Profiler)
   Viewing and Analyzing Traces
   How to replay a trace table (SQL Profiler)
   Locks Event Category
   Troubleshooting Deadlocks
   Performance Event Category
    
3. Create and implement indexing strategies. Considerations include clustered index, covering index, indexed views, nonclustered index, placement, and statistics.
   Designing an Indexed View
   Placing Tables on Filegroups
   Placing Indexes on Filegroups
    
4. Improve index use by using the Index Tuning Wizard.
   Index Tuning Wizard
    
5. Monitor and troubleshoot database activity by using SQL Profiler.
   Monitoring with SQL Profiler
   SQL Profiler Scenarios
    
6. Designing a Database Security Plan
1. Control data access by using stored procedures, triggers, user-defined functions, and views.
   1. Apply ownership chains.
      Using Ownership Chains
       
   2. Use programming logic and objects. Considerations include implementing row-level security and restricting direct access to tables.
      Using Views as Security Mechanisms
      Using Stored Procedures as Security Mechanisms
       
2. Define object-level security including column-level permissions by using GRANT, REVOKE, and DENY.
   Managing Permissions
   Permissions
   Granting Permissions
   Denying Permissions
   Revoking Permissions
    
3. Create and manage application roles.
   Establishing Application Security and Application Roles [See Transact-SQL links at the end of the page too]
    

Home    Back to Certification Stuff

about this page

Last Update: May 12, 2003