Content area
The past year has seen the introduction of SQL Server 7, along with its accompanying certification exams. These exams comprise the core of Microsoft's latest certification: the MCDA (Microsoft Certified Database Administrator). Encountering new exams for a new certification that covers a new application can be part of the challenge of studying for the SQL Server 7 core exams. But with everybody starting off with a clean slate in their studies, it is difficult to decide where to begin. Some of the tips and techniques to use to help pass the exams are discussed.
This past year has seen the introduction of SQL Server 7, along with its accompanying certification exams. These exams comprise the core of Microsoft's latest certification: the MCDBA (Microsoft Certified Database Administrator). Encountering new exams for a new certification that covers a new application can be part of the challenge of studying for the SQL Server 7 core exams. With everybody starting off with a clean slate in their studies, where do you begin?
You can't rely on older study materials or exam software. SQL Server 7 is such a departure from its 6.5 predecessor that few procedures hold true for both. Brain dumps are just beginning to trickle in, and they are rife with errors, speculation, and pranksters. This isn't Networking Essentials where the answers have been settled for the past few years. In SQL Server 7, even experts are still disputing how to optimize the replication of a database in dynamic situations.
Moreover, those just-in-time study guides for new exams are sometimes the most treacherous. When scouring through the few SQL Server 7 study books that were available at the time I was preparing for the exams (more and better books are coming out monthly), I was exasperated to discover numerous typos and errors caused by a publisher's rush to be first to market. SQL syntax examples didn't work. Code that was supposed to be on the accompanying CD just wasn't there. Indexes listed the wrong pages for their subjects. This is unfortunate. A few errors make a student question the accuracy of the entire book.
So how do you begin to study for the SQL Server exams? In this article, I'm going to explore some of the tips and techniques I used to help pass the exams.
Where do you start?
If you listen to what some say in the SQL Server 7 study groups, you'll come away believing you need eons of professional experience with SQL Server even to fantasize about passing the exams. You get bombarded with inspiring comments like "I've been a Database Administrator for an international high-tech firm for the past 10 years and I still failed these excruciating exams!"
With all due respect to the mavens of data-processing, I think they're overdramatizing. In fact, if you've become ingrained in the ways of SQL Server 6.5, it could be more of a challenge just to unlearn its 6.5 methods than it is for someone approaching SQL Server 7 from scratch.
Our mavens are right, however, to suggest that hands-on experience is an essential part of studying for these exams. For decent results, you need ready access to the SQL Server 7 application. Somehow, some way, get it, even if it's the 120-day trial version, and have the application by your side while you study.
Any time a book provides an example, be ready to try it out for yourself on SQL Server. Crank up that application, roll up your sleeves, crunch some numbers, drill-down through the data, design your own Entity Relationship Diagrams --- even organize your video collection into the video catalog of your dreams to get some practice building a database. If you can get access to any real, live databases, you can safety afford to screw up-say the Pentagon's or your tax accountant'sthat's a plus, but not necessary. Most of your homework can be done by using a non-networked computer with the sample databases supplied with SQL Server.
If you haven't already, it's time to familiarize yourself with the friendly folks at that gourmet food distributor, Northwind (one of the sample databases included in SQL Server 7). Put on your DBA propeller beanie. Challenge yourself with questions about how Northwind can find out the amount of Camembert Pierrot that was sold to Alfreds Futterkiste in the last two years. Write a query or create a view that shows you the phone numbers of all your suppliers in London. How many of your customers are in the 90110 ZIP code?
Or go to the Pubs database. Examine the layout of the Entity Relationship Diagram. Observe how the primary keys and foreign keys are set on particular tables. How can you view the addresses of all your authors who live in Oakland? Which of those were best sellers? What are the titles of books that were written by two authors? Which books have the word onion in the title?
Many study guides also use the Northwind and Pubs databases for convenient examples of the concepts they explain. In fact, you should select study books that tap into this ready-made study source. Speaking of books, one crucial resource for the student is the Books On-Line (BOL) included with SQL Server 7.
By general consensus of my fellow exam-aholics, BOL is the most helpful and authoritative study guide around. Even skeptics can't find enough good things to say about its ease of use when you're in a jam. I especially admire its flexible search engine that can search on all possible variations of a term. As a handy reference for the student or the database administrator, BOL is the best Microsoft has produced. Use it any time you want to explore a new term or you want to verify concepts from your other study materials.
But BOL isn't just abstracts. It provides plenty of examples of SQL syntax usage, making it simple to cut and paste its SQL code examples right into SQL Server Query Analyzer. This way you see a live demonstration of the syntax at work. Once you've pasted the query into SQL Server Query Analyzer, you can then tinker with the code to see if the results were altered the way you expected them to be. As you do this, take a look at the SQL Server Query Analyzer's Graphic Execution Plans to see the different paths it takes in response to each modification.
Taking the Administering SOL Server 7.0 exam
Exam 70-028, Administering SQL Server 7.0, consists of 52 questions with a required passing score of 680. You'll be given 90 minutes to complete the exam. This exam focuses on the administration side of SQL Server. It involves concepts similar to what you might have already experienced in Windows NT. These are the administrative tasks common to all networks such as how to install the application and migrate from a previous version. For example, you might be quizzed on what it takes to get SQL Server 7 to communicate with its SQL Server 6.5 ancestors.
Backup strategies, replication, and security concerns get more emphasis here than on most Windows NT exams, so you should be familiar with how these features integrate with NT, plus the SQL Server-specific differences between the two. For example, users' permissions in Windows NT are the most open until you restrict them but users' permissions in SQL Server 7 remain the most restrictive until you grant them.
Know both the server and database roles-and their associated sets of permissions. These roles have varying degrees of autonomy when it comes to backing up, replication, and security, and it's helpful for you to know what each can and can't do. Be aware that permissions also change completely when a user accesses a database through an application role.
Understand that many of these administrative tasks can also be set up to be performed as a job by the SQL Server Agent, which notifies you when its tasks are accomplished or alerts you if it fails. This doesn't leave the administrators time to twiddle their thumbs-now they have to strategize what jobs and when they want to schedule the jobs to run, and on which database. Be able to configure the SQL Server Agent to ensure that it alerts you about problems in a timely manner.
As more and larger databases are being used to store voluminous amounts of text, Microsoft has begun to place emphasis on the full-text search capabilities of its application. For the Administering SQL Server exam, make sure you know the steps for setting up and maintaining such a full-text database, and that a full-text index must be scheduled for regular repopulation in order to register any changes made. It's only when you get to Designing and Implementing that you should know the best syntax (FREETEXT, CONTAINS, and LIKE) for querying a full-text database.
By the way, if you're prioritizing your study schedule, save your Transact-SQL review until you're ready to take the Designing and Implementing Databases test. For,the Administration test, concentrate on roles, permissions, backups, replication, and DTS. It might be impressive to be able to do all your Administrative work with Transact-SQL, but you only need to be aware of a handful of administrative SQL commands, plus the purpose of a few stored procedures (such as sp_addrole or sp_who) for exam 70-028.
Taking the Designing and Implementing Databases exam
Exam 70-029, Designing and Implementing Databases with Microsoft SQL Server 7.0, consists of 49 questions with a required passing score of 693. You'll be given 150 minutes to complete the exam. By the time you're ready to take the 70029 exam, you should be able to look at an Entity Relationship Diagram and detect whether the databases are normalized (at least to the third level).
You should be able to gauge by the diagram whether certain queries are possible. You've probably realized by now that in the database world, normal doesn't always mean good. There are cases where a highly normalized database isn't the best kind of database. Database designers (and exam takers) have to judge how, when, and why their databases should or shouldn't be normal.
At one time, designers never imagined that their databases would be changing every second, or that hundreds of people would be querying it for a quick answer over a network. Nor did they think a database would be able to hold bunkers of information that you could use to analyze trends over decades. The Designing and Implementing exam grills you on the requirements of dynamic On-Line Transaction Processing (usually a fast, highly normalized database) as opposed to the more static and bulky Decision Support System. Be familiar with the speed versus fuel-efficiency trade-offs you get in each system, and how to balance both on your networks.
Indexes are another factor in the efficiency of a database. Microsoft wants to be sure you know the advantages of using clustered or non-clustered indexes, or both. Be prepared to look at the output of a dbcc showcontig command and discern if an index is internally/externally fragmented.
Test-taking tips
For all of the murmurs about the latest Microsoft exams using innovative product simulation questions (those of you who took Internet Information Server know the simulated Management Console I'm talking about) the 70-028 and 70-029 exams proved surprisingly non-gimmicky in format, sticking with standard, multiplechoice questions. But what choices! What questions! Examinees, prepare to scroll, and scroll, especially in the case of the Designing and Implementing Databases test. These detailed scenario questions can be daunting if you allow their lengthy multiple-choice answers to swamp you.
There's no substitute for knowing your stuff, but there are a few tricks to testtaking which can help you squeeze by a few tight spots. Take a deep breath. Focus on the tree and not the forest. Remember that Microsoft spices its exams with red herrings. Some details mean absolutely nothing for deducing the right answer. Usually, however, there is one detail included that suggests that only one answer can be possible.
For example, let's say there's a scenario that involves accessing data on a remote Microsoft Access database. You might get a lot of red herrings about salesmen in different territories who need to print reports. You can be fairly sure that in any question concerning a SQL Server connecting to an Access database, the answer must contain some mention of Microsoft.Jet.OLEDB 4.0. This is the one distinctive part of the solution applicable to no other database but Access.
Or, let's say you're looking at a scenario followed by five multiple-choice answers of SQL code. Don't panic just because you see a screen of endless code. Just ask yourself "What type of clause best solves the question proposed in the scenario? Is an UPDATE clause even necessary in a scenario that requires only that you retrieve data?" If not, you can eliminate the choice that includes an UPDATE clause. No need to get bogged down further in its details.
Another brilliant deduction: If the scenario mentions querying a list for all employees in a Personnel table, wouldn't a LEFT JOIN (which lists all data on the left side of the join) be the most likely choice? If only one of the multiple-choice answers includes a LEFT JOIN, then you don't need to look further. By eliminating syntax choices that are obviously unworkable, or zeroing in on the only syntax that is workable in a given scenario, lengthy choices can be whittled down to the correct answer-or at least a pool of educated guesses.
Transact-SOL syntax rules
As I studied the details of Transact-SQL, I composed a list of near-absolutes regarding the Transact-SQL syntax; just as in English grammar, we have some rules. The following list of SQL rules isn't exhaustive, and it's possible to come up with many more, but these are the rules I found the most helpful:
The GROUP BY clause is used to specify the columns by which rows must be grouped when aggregate functions are used in the SELECT list.
When the COMPUTE BY clause is used, the ORDER BY clause must come before it.
In UNION ALL, the WHERE, GROUP BY, and HAVING clauses are applied to the individual SELECT statements. But the ORDER BY clause is always applied at the end of all of the statements.
In a GROUP BY clause, you must specify the name of a table or view column, not the name of a result set column assigned with an AS clause.
The HAVING clause is typically used in conjunction with the GROUP BY clause although the HAVING clause can be specfied separately.
If an aggregate function is used in a select list or in a HAVING clause, then all of the other columns that are used in the select list and the HAVING clause, and that appear outside the aggregate function, must also be listed in a GROUP BY clause.
The WHERE clause cannot contain aggregate functions (like AVG or SLIM).
Aggregate functions are allowed as expressions only in the select list of a SELECT statement (either a subquery or an outer query), a COMPUTE or COMPUTE BY clause, or a HAVING clause.
Using this list as a guide, if you see a WHERE clause that contains the AVG function, you can eliminate it from the possible answers no matter what the scenario. These guidelines can save you time, help you feel less rushed, and allow you more time to puzzle over questions that really stump you.
General rules
You can also come up with a few rules of thumb for other aspects of the exams that will help you drill down quickly to the correct answer. These rules are more general. They may not always hold true, so examine the context of the question. But they can give you quick guidance in weeding out the wrong answers. Again this list isn't exhaustive, and you can probably come up with many more:
In almost any scenario about planning the physical implementation of your database, a transaction log is best kept on a single separate disk (when that's given as an option).
A Decision Support Server is most effident when it isn't on the same server as an OLTP server.
Transactional replication is necessary only in, a scenario that seems to require real-time information in a network of databases.
Differential backups increase the total amount of time spent making backups, but decrease the amount of time required to restore backups.
Differential backups cannot be made across filegroups.
Transaction logs may take longer to restore, but they are the only way to restore a database up to a specific point in time.,
Data Transformation Services
For both the Administering and the Designing exams, you won't need to know the peripheral extras like English Query or OLAP services. Even though you might be curious to explore these and their accompanying tutorials, save something for the Data Warehousing exam!
There is one extra that gets strong coverage in both exams: Microsoft's Data Transformation Services (DTS). Microsoft is justifiably proud of this handy, allpurpose application that comes bundled with SQL Server 7. DTS is a useful bridge for translating many different OLE-1313 data sources, be it Oracle, Access, or SQL Server. SQL Server doesn't have to be either the source or destination of the data. Walk through the steps required to move databases from Access, to commadelimited text format, to SQL Server 7. Be aware of how you can alter data types via DTS, and how there are some exceptions involving triggers, stored procedures, constraints, and user-defined data types.
Scripts also enable you to micromanage the transformation of data down to small details. For the exams, you won't need to know how to write scripts, but you should know they're an option available for customizing data. On either exam, if you see that DTS is offered as one of the multiple choices, remember how proud Microsoft is of this, and how very aware Microsoft would like you to be of its usefulness.
Conclusion
I have one final observation regarding the exams themselves: the two core SQL Server 7 exams should be divided up into five exams-similar to the curriculum track offered by Oracle. just as Oracle begins by testing you on PL/SQL, Microsoft should have a test to demonstrate that you're well-grounded in the syntax of its own Transact-SQL.
Oracle offers a test in Database Administration, but it also requires a test focussed solely on the crucial details of backup and recovery. Whereas Oracle divides its curriculum into separate courses and exams on subjects such as performance tuning, network administration, and PL/SQL, Microsoft's Designing and Implementing exam blankets the same territory-and assumes that you're already fluent in Transact-SQL.
Even if Microsoft doesn't compartmentalize their exams this way, you, as a student, probably should. Viewing SQL Server 7 as several manageable components might be one key approach you can use for success in the exams.
Visit our new pay-per-view resource library at www.zdjournals. com/get/ejournals for access to over 20,000 articles from 26 different ZD Journals publications.
Copyright ZD Journals Mar 2000