Exam Reviews

Subtle SQL 7.0

While SQL 7.0 offers plenty that's easier to do than in SQL 6.5, you'd never know that from the Designing and Implementing exam.

• By Brian Moore
• 05/01/1999

SQL Server 7.0 makes it significantly easier for us to perform a great many operations than did its predecessor, SQL Server 6.5. If you’ve spent much time building and maintaining SQL Server 6.5 solutions, you’ll find the additional functionality and increased efficiency offered by the new release an exciting prospect. At least, that was my experience.

From the moment I loaded one of the earliest SQL 7.0 betas on a test machine, I began to discover the new possibilities it offered. Several new components easily handled what had taken me hours of writing resourceful TSQL procedures in SQL 6.5. I’ve spent a little time working with the new product’s data warehousing features and I’m still plenty impressed.

Considering myself a savvy TSQL programmer, I decided to upgrade my MCSD certification with the SQL Server 7.0 Developing and Implementing beta exam. I assumed that the new exam would be fairly easy for me based on several facts:

• I had extensive experience as a SQL 6.5 developer.
• I was somewhat familiar with the new functionality of SQL 7.0.
• SQL 7.0 was “easier” to use than SQL 6.5.
• I passed practice exams for SQL 6.5 equivalent exams easily on the first attempt.

I couldn’t have been more mistaken in my assumption.

I’ve taken four Microsoft tests in the past year, and the SQL 7.0 one was in a league of its own in terms of difficulty. You’ll be expected to have a holistic, in-depth knowledge of the product’s features as well as a solid grasp on syntax. You must be able to discern the subtle differences between many potentially “right” answers.

In this tale of woe, I’ll share my insights on how I could have better prepared for this difficult exam. (At the time of this writing, I don’t know whether I passed or failed.) First, I’ll discuss how I’ve successfully prepared for tests in the past and compare that with how I prepared for this one specifically. I’ll mention the few resources that were available to me that proved helpful. Then I’ll discuss why I was so misguided in my assumptions. And I’ll give you some technical coverage related to the exam that I hope will be helpful.

Preparation by Hands-on

My normal method of preparation for an exam has been a fairly rote process that hasn’t steered me wrong yet. First, get extensive hands-on experience using the product. Second, I’ve relied on practice tests from various third-party vendors to target my areas of weakness. When I get a question wrong, I go directly to the referenced MSDN or TechNet article to read up. Then I apply my new knowledge with the product to reinforce these concepts.

Of course, since this exam was still in beta form, I had no such resources to rely on. However, I referred to Microsoft’s Preparation Guide for the test. With this document as a map, I found the Transact SQL help file and SQL 7.0 Books Online very useful. The 7.0 Books Online are far superior to their 6.5 counterpart. You’ll find a lot of information there; but make sure you read thoroughly and actually run the SQL examples. Finally, when you think you understand the concept, try modifying the sample SQL to achieve a desired result that really tests your knowledge.

Tip: Another “must read” document for preparing for this test is, “Microsoft SQL Server 7.0 Performance Tuning Guide,” a Microsoft white paper by Henry Lau. The author provides an excellent discussion of indexing and the SQL Profiler.

Faulty Assumptions

So why did I have such a tough time? I was working under some faulty assumptions:

• Myth #1: “I have extensive experience as a SQL 6.5 developer, and this knowledge should naturally carry me through the SQL 7.0 exam.”

These two products have tangible differences and it’s obvious one of Microsoft’s objectives is to test you on SQL 7.0’s unique functionality. I wrongly thought that since my daily activities consist primarily of cranking out command line SQL, I’d be ready.

While my SQL 6.5 experience certainly helped in the areas of data modeling and SQL DML (SELECT, INSERT, UPDATE, DELETE, sub-queries, joins, and functions), I made a big mistake by resting on the laurels of my SQL 6.5 accomplishments. In fact, for some areas of the test your SQL 6.5 knowledge can actually work against you. I caught several questions in which I knew what answer would have been correct for SQL Server 6.5. However, it wasn’t the optimal answer because of differences and improvements in the new version. Watch out for this trap.

• Myth #2: “I’m somewhat familiar with the new functionality of SQL 7.0. This should be enough to get me through the new exam.”

The fact is, you really have to know much more than glossary definitions of SQL 7.0 keywords. Expect to be tested on how to apply low-level knowledge of the product.

For example, a Microsoft white paper on the SQL 7.0 Storage Engine explains the role and functionality of files and filegroups. You’ll need to understand this for the test, but it’s even more crucial that you be able to manage files and filegroups through the CREATE TABLE and ALTER TABLE statements.

For the past six months I’ve been working extensively on data warehousing. Thus, while I’m well versed in indexing strategies, bcp, and data modeling, I rarely have to deal with locking issues. Based on Microsoft’s recent touting of the data warehousing enhancements, I thought the test would feature many more questions related to bcp improvements, Data Transformation Services (DTS), and the Decision Support Server (DSS). Wrong! The live test may ultimately be different, but the exam I took was heavily geared to OLTP databases.

Tip: You really need to know every aspect of the product and not just the area you work with. Before you tackle this exam, evaluate your daily experience with SQL Server and brush up on the parts that you don’t use regularly.

• Myth #3: “SQL 7.0 is easier to use than SQL Server 6.5. Therefore, this test must be easier than the SQL 6.5 exam.”

Through its numerous wizards and GUI interface, SQL 7.0 makes power users less dependent on a DBA for certain tasks. Even the best developers rely on these tools at times. But you’d still better know how to perform chores through the lower-level equivalent. And remember: Syntax counts.

Tip: While you probably won’t be asked how to perform a task via the GUI, you should be able to interpret a graphical representation of a DTS package and a query optimization plan.

• Myth #4: “I passed the practice SQL 6.5 exams easily on the first attempt. I should be able to handle the SQL Server 7.0 exam.”

The format of the SQL beta exam is vastly different from the format on the exams I took a year ago. The questions are lengthier and more detailed than those found in previous exams; a single question frequently tested my expertise in more than one area. Most of the questions in my version of the test offered check boxes, which means there can be multiple correct answers (vs. radio buttons, which require only a single answer). One slip on these types of questions and you’ve lost it all—even if all your other choices were correct. No deductive guessing here.

I’m sure you’re familiar with the type of question in which you’re presented with a business problem, a desired primary objective, and desired secondary objectives. You’re given a proposed solution and asked whether the solution meets the primary objective only, the secondary objective only, both objectives, or neither. This test features a somewhat different format. It gives you the business requirements along with a more detailed solution. You’re asked to check off every statement that’s true after the implementation of the solution. Some of the differences between these answers are incredibly subtle. Take your time and read carefully.

Data Modeling

This topic probably reflects the least change in versions from a practical standpoint. After all, the principles of normalization and sound data design remain the same. However, I detected a great deal of emphasis on this topic in the beta. Take your time with these questions; they’re complex. Most provide a description of a specific business model and an ERD exhibit showing the table structure. You’re asked to evaluate the model to confirm that it meets the principles of good design as well as specific business requirements.

Another form of question presents a business requirement and then gives you CREATE TABLE or ALTER TABLE SQL statements. You’re asked to verify what statement meets the requirement. Again, the differences between answers can be subtle. You’ll have to be familiar with the syntax of all extensions to these statements. Make sure you can add Rules, Defaults, Primary Key Constraints, Unique Constraints, Foreign Key Constraints, and Check Constraints at both the column and table level.

Tip: Make sure you can choose which statement to use based on a specific business requirement.

Data Services

If you’re a strong TSQL programmer, your experience will benefit you here. You may find numerous questions on fairly straightforward SQL DML (SELECT, INSERT, UPDATE, and DELETE). If you write SQL by trial and error (and, honestly, who doesn’t “debug” SQL by testing what’s in the buffer?), better brush up and have a solid understanding of syntax.

TSQL programmers will also feel comfortable with several questions that present a stored procedure and ask you to evaluate what the return code or value of a specific variable will be based on a given set of parameters. These questions also test your general knowledge of TSQL control-of-flow techniques.

Be aware of the changes to triggers in SQL 7.0. Note that 7.0 now allows for a single table to have more than one delete, insert, and update trigger and that triggers can be invoked recursively after setting the database option “recursive triggers.” You’ll also want to know the SQL statements not allowed in a trigger.

If you’re a Visual Basic developer who rarely writes SQL, I suggest you spend several hours writing SQL in an ISQL/w buffer against the pubs database. Many of the sample questions resemble the schema of the sample pubs and Northwind databases. Hate to repeat myself, but syntax counts for a lot on this test. You don’t have the benefit of looking into a help file or the ability to debug by running what you’ve written in the SQL buffer.

The beta placed a heavy emphasis on transactions and locking. This makes sense. SQL 7.0 features row-level locking, which wasn’t available in the prior version. Understand SQL Server’s dynamic locking strategies but also be able to customize locking in your applications. Familiarize yourself with the locking hints.

Creating a Physical Database

As I’ve mentioned, be familiar with how to manage files and filegroups and how to create a table on a specific filegroup. Also be able to implement a maximum size and a growth increment for a file.

One area on the exam that threw me involved the full text search functionality added to SQL 7.0. This lets you search columns for matching text and compare text for approximate matches. So I spent my time learning how to wield the CONTAINS and FREETEXT predicates of the WHERE clause. However, what I should have spent more time on was how to implement full text searching in a database using the applicable stored procedures.

In another area, be prepared with the syntax for the command line bcp and know how to read a bcp format file.

Tip: Know the entire syntax of the CREATE INDEX statement with all of its options. Know what each option does as well as when to apply it.

Maintaining a Database

Research the topics of query execution plans and SQL Profiler. Be able to set traces to monitor server connections, TSQL commands, login attempts, and deadlocks with SQL Profiler. Understand the DBCC SHOWCONTIG command. Familiarize yourself with query execution plans output, generated both graphically and textually (SHOWPLAN_TEXT and SHOW_PLAN_ALL). Interpreting the graphical output from a query analysis sounds like an easy task, but there’s more to it than meets the eye.

Difficult, Not Impossible

The SQL 7.0 beta exam was a difficult exam, not impossible. But even if you have hands-on experience, you’ll still have to commit to learning how to implement all the new features of SQL 7.0 and be clear on syntax. I suggest starting with the Preparation Guide and SQL Server Books Online TSQL examples. Use the ISQL/w window to run queries from a test database. The kind of knowledge required really comes only from experience. I wouldn’t even consider taking the test unless you’re building actual solutions with SQL Server 7.0 on a regular basis. And even then, be prepared to study to fill in those gaps in your knowledge.