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.
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
- 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
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.
Server 7.0 Databases (70-029)
Rating: I wouldn't even consider
taking the exam 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
Designing and Implementing Databases
with Microsoft SQL Server 7.0.
Number of questions
in reviewed version of exam:
77 or 78 on the beta; fewer on actual
4 hours for beta; live exam will be
Live as of April 1999.
Who should take
it? Developers designing and
implementing database solutions with
SQL Server version 7.0. The exam is
an MCSD elective, MCSE elective, and
What classes prepare
- Course 833—Implementing a Database
on Microsoft SQL Server 7.0.
- Course 832—System Administration
for Microsoft SQL Server 7.0.
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
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.
So why did I have such a tough time?
I was working under some faulty assumptions:
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.
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.
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
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.
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.
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
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.
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
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.