Inside SQL Server 2000

The software to play with

General Information & Resources for SQL Server 2000

the Transcender Certification Trainer for exam 70-229

Resources by Exam Objective

Developing a Logical Data Model

• Define Entities
• Specify degrees of Normalization
  • DeveloperZone's Intro to Normalization is easy to read and goes through 3NF. The example is a little contrived, but it works.
  • The University of Texas' Overview of Normalization is a little more difficult, but it goes thorugh 5th normal form, as well as a couple variations. The example here is even more contrived than that above. Still, this is the more thorough paper.
• Gayathri Gokul offers an overview of Constraints.

Implementing the Physical Database

Retrieving and Modifying Data

• DTS
  • Marcin Policht offers this series on DTS. I will link to each article, because Database Journal does not provide links from one part to the next and the links under an author's name are sometimes incomplete.
    • Part 1 introduces DTS
    • Part 2 discusses DTS Wizards.
    • Part 3 introduces DTS Designer Connections.
    • Part 4 introduces the other half of the DTS equation -- DTS Designer Tasks.
      • Bulk Insert -- quick, but inflexible. No transformations possible.
      • Copy SQL Server Objects -- duplicates any objects -- tables, views, constraints, etc. Inefficient for merely copying data.
      • Execute Process -- launch any Win32 program
      • Execute SQL -- executes statements & stored procedures.
      • File Transfer Protocol -- will FTP files -- useful in automated import/exports between heterogenous or remote systems.
      • Send Mail -- requires a MAPI profile under the appropriate login.
      • Transfer Database -- Contained in Copy Database Wizard.
      • Transfer Error Messages -- to copy the sysmessages table.
      • Transfer Jobs
      • Transfer Logins
      • Transfer Master Stored Procedures
    • Part 5 covers the remaining tasks and global variables
    • Part 6 introduces Packages Workflow. Somehow, I'm not expecting to see DTS questions this detailed on the test, so I think I'll stop here for now. It is a good set of articles, though, to which I plan to return.
  • MVP Narayana Vyas Kondreddi discusses DTS Best Practices. Execellent article!
• Bulk copy utility (bcp)
  • Using bcp and BULK INSERT.
  • Mitchell Harper offers a good Introduction to the Bulk Copy Utility.
  • bcp Utility T-SQL Reference -- if you want to see ALL the flags.
  • Alexander Chigrik offers 14 tips to Optimize Bulk Copy performance.
• Bulk Insert T-SQL Reference

• Don Schlichting has written the following series on linked servers. I will link to each article, because Database Journal does not provide links from one part to the next and the links under an author's name are sometimes incomplete.
  • Part 1 introduces linked servers.
  • Part 2 begins to deal with security.
  • Part 3 deals more with security.
  • Part 4 deals with linking to an Oracle DB.
• The SQL Server Linked Server Performance Tips are great.
• OPENQUERY is simple enough that one reference should suffice.

• I will assume you know basic SQL statements -- SELECT, INSERT, UPDATE, etc. -- and will push on to the less common summary stuff.
• Summarizing data using CUBE introduces this level of summary.
• Summarizing data using ROLLUP explains the difference between this and CUBE.
• Grouping Rows with GROUP BY -- you probably already know this if you've done any SQL for reporting.
• Choosing Rows with the HAVING clause explains the differnece between this and WHERE.

• Retrieving and Writing XML Data is a great place to start. Good overview with just enough detail to get started.
• OPENXML T-SQL Reference is as dry as a complex BOL page is expected to be, but very thorough.
• Using OPENXML provides 11 pages of examples and explanation. It's probably overkill as exam prep, but a good reference when you put OPENXML to work.
• Writing XML using OPENXML ties it all together very well. It explains the meaning of an edge table (on page 2), a term that is used in the other documents, but not defined.

Programming Business Logic

• Specify Trigger Actions
  • Let Kalen Delaney get you started with this excellent explanation of INSTEAD OF Triggers and when to use them. If printing her article to read offline, be sure to print code listings 1 and 2.
  • Designing INSTEAD OF Triggers gives a good example of the use of such a trigger on a view and reinforces Delaney's explanation that that is the real purpose for the new triggers.
• Views
  • Doug Carpenter reviews the basics of views, their purposes, advantages, and limitations, before discussing indexed views. He discusses the limitations (no outer or self joins???), the performance boosts (3 - 50 times in his tests, 10 - 100 according to Microsoft), when to use them, and when not.
• Doug Carpenter explains User-Defined Functions as well.

Tuning and Optimizing Data Access

Designing a Database Security Plan