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

Hit Counter