As I was finishing up my final cramming yesterday, I thought it might be interesting to document what I learned while studying for the 70-229 SQL Server 2000 Database Design and Implementation exam. First I'll just outline the studying process I used and then discuss what I learned and how it relates to my 6 years of development experience of dealing with databases.
The studying process I used for the exam:
- I bought Que's Training Guide for 70-229 from Amazon.com
- Read it cover-to-cover doing the exercises and exam questions after each chapter (this took a good 6 weeks)
- Read the Fast Facts (sort or a 20 page summary of the book)
- Looked up some items in SQL Server's Books Online (see below) that I thought I needed to know better.
- Did all the exam questions after each chapter again
- Day before exam and morning of exam: Reviewed each chapter - flipping through every page of the book and reading what I had highlighted
- Morning of exam: Did all the exam questions after each chapter again
- Morning of exam: Did sample exam questions (44 questions)
- Morning of exam: Wanted to read the Fast Facts again, but ran out of time
That is pretty much the process that has worked for me so far (4 exams - passed first try).
Even though I have been using SQL Server 2000 for 3+ years (started with a beta version), I learned a lot of new stuff while studying for this exam. Some of the stuff I might have learned on the job sooner or later, but some of it I probably would not have learned (like replication). Even though I have been using databases with applications that I build for at least 6 years, I would not have passed the exam if I just took the exam and didn't read Que's Training Guide for 70-229 from cover to cover.
Stuff I learned - I tried to dig up some links from MSDN for most of this stuff for you to dig into deeper if you wish.
I got a little clarity about some of the system databases :
Master - This database has all the system information for the SQL Server instance and really should be backed up once you create new databases.
Msdb - This database is used by SQL Server Agent stuff, like jobs, alerts, operators
Tempdb - This database is used to store all temporarily created stored procedures and tables.
Model - (I hadn't even noticed this database before) This database is the template database used when you create a new database.
Got a refresher on database design. This is things like Normalization.
Even though I had created a lot of databases, I had never really dug into Files and Filegroups or Transaction Logs. Both of those topics are covered early in the training guide which gave me a different perspective on the planning of a database implementation. Things like why you might want the log files stored on a separate disk, filegrowth, RAID considerations, storing text, ntext or image columns in separate files (and maybe separate disk drives). SQL Server 2000 has Cascading Actions, something that I knew what it was and pretty much how it worked, but I didn't know that SQL 2000 had it (as opposed to writing triggers to enforce it). SQL Server has a lot of db options that can be set using sp_dboption
When it comes to querying and modifying data - the basics, I didn't learn much that was new to me, but got a refresher on some of the things you don't use a lot (or I don't anyways) like Bitwise Operators, CEILING, FLOOR, and several System Functions that I have never seen.
With the advanced data retrieval and modification, I learned some new functions like COUNT_BIG and a few other Aggregate Functions that I hadn't seen before like the CHECKSUM functions. Last year I actually had to use sp_addlinkedserver, but I didn't know you could use Enterprise Manager to configure linked servers too. A long the same lines of getting data from other sources, I learned about OPENROWSET, OPENQUERY, OPENDATASOURCE, and OPENXML. Using OPENXML requires sp_xml_preparedocument and sp_xml_removedocument. I also learned the different ways to use the FOR XML clause. I had played around with it a little, but had done nothing with the EXPLICIT mode. Pretty cool stuff, syntax is a little odd though. Getting a schema by using the XMLDATA option was completely new to me and seems like it was on the exam or practice exam one.
Programming SQL Sever 2000 (chapter 6 in training guide) was pretty much a review of what I use the most, but still picked up a few new items. I had forgotten there were so many options on DELCARE CURSOR such as LOCAL, GLOBAL, FORWARD_ONLY, SCROLL, STATIC, KEYSET, DYNAMIC, FAST_FORWARD, READ_ONLY, SCROLL_LOCKS, OPTIMISTIC, etc. (probably a good thing I don't know all these choices). I also learned to use sp_addmessage to create my own error to raise. sp_setapprole and Application security roles was a little new to me as well. I was only familiar with standard roles, not application roles. I use the GRANT statement all the time for scripting out objects, but I didn't know all of the options on it. If you are going to take the exam you will really want to brush up on its options.
Working with views - also learned a few things. I never new there was a bunch of wizards in Enterprise Manager available by going to: Tools Menu -> Wizards. There are 23 different wizards available from that one menu item. I am not a big fan of wizards, but it is nice to know how to get to them if you really want to. I learned little thing about views that I had noticed but never made the connection as to what was causing the problem -> you can't use the ORDER BY clause in a view unless you use the TOP statement. One thing I have never utilized but had to learn was how to perform updates through views. There are several rules for a view to actually be able to update the underlying tables, but none the less, it was interesting. When you put the possibility of adding an INSTEAD OF trigger on a view things really get interesting.
Triggers are something I don't use a lot so I definitely needed a refresher on them. The INSTEAD OF trigger is new to SQL 2000 and really worth checking out.
Stored procedures are something I consider myself an advanced user in, but I still learned a few things I didn't know before (or had completely forgotten). You can used named parameters calling stored procedures (example: EXEC SPROCNAME @param1 = 1, @param2 = 2). Just remember: if you get a named parameter in a list, every parameter after that has to be named. User Defined Functions were something I really wanted to play around with, but until I started studying for this exam I just couldn't find the time to do so. Now that I have learned out to write them, I wish I would have done so earlier.
Boosting performance with indexes - something I only had a little experience in. For the last couple of years I have been working with databases that aren't that large (less than 10,000 records in biggest table), so I really needed to brush up and learn more about indexes and what to index. I learned about SQL Server's Table Index Architecture, designing an index (Clustered and Nonclustered), and a lot about index maintenance (Fill Factor Rebuilding Indexes and what your options are to do so and why to pick one over another, (too much to sum up here). I also played around with the Index Tuning Wizard, pretty cool and it really does seem to provide good advice on what to index. I learned you also create indexes on views now in SQL Server 2000. Another topic I had wanted to learn for awhile but didn't get to it until studying for this exam is Full Text Indexing. I set up my laptop so I could play with the full text indexing (you have to make sure the MS Search service is running first. There are basically four key words you need to know to utilize a Full Text Index (full text catalog), FREETEXT, FREETEXTTABLE, CONTAINS and CONTAINSTABLE.
Replication was all new to me. I knew the theory, but didn't know the different types, why to choose one over the other and never had taken the time to actually set it up at home (I did after reading the chapter on replication. I made a change on my laptop and by the time I walked over to my desktop, the item I changed had already been pushed over... really cool!).
Some other stuff that I learned and will be useful to me: DBCC, dtsrun utility, SQL Profiler and System Monitor
Overall comment of Training Guide and Exam
The training guide helped keep my studying structured and lead me to learn what I needed to learn to pass the exam. When in comes down to it, there probably isn't anything in there that isn't in Books Online (except the exam questions - needed to help you be ready for "being tested"). I am sure I now a little bit better of a Database Programmer, now that I have gone through this experience.