This test contains 40-50% word problems that require you to look at syntax and determine what the result set will be or if the code will return an error. Also I got at least 5 of the questions where you had to rate if the solution they proposed would work or not. You really don’t have time to tear each statement apart(some are on separate exhibits and quite long)so I think the best strategy is to look thru the code and try to spot obvious syntax errors that will eliminate that option as a correct answer. This seemed to work to eliminate about half the possibilities so it cuts down on the overall time you’d spend analyzing all options. You will also face a number of questions where there are checkboxes and you are told to pick two answers. These all had one fairly obvious solution and the other three were not familiar. There is more than one way to write these statements and you will need to know them.
Know all replication models and their usage. For example: What model would be used if you have a slow link over which replication occurs? What if the you have a high volume order processing situation?
Where is a stored procedure stored? Msdb? Master db? Database of creation?
If you have an index(1 clustered and 2 non-clustered) on three columns of a table which index is used for the initial sort? Which indexes come into play for second sort?
You have a replication scenario where you are rolling up all the transactions from four publishers to one subscriber. Are you going to partition the tables horizontal or vertical? Or use composite primary key?
You have an error you want written to the NT log. How do you accomplish that? Raiserror? With Log?
Know what is in sysobjects.
You want to force the use on an index. What syntax is used? Know all options-it asked for two ways.
Know IN, BETWEEN, CASE and LIKE keywords and what they look like syntax-wise.
You are wanting to expand your transaction log by 25MB. What would the instructions look like to accomplish this?
Users are getting repeated error messages. The transaction log is full and you want to dump it. How? DUMP TRANSACTION? What else should you use? NO_LOG, NO_TRUNCATE or TRUNCATE_ONLY?
Msjobs is located where? Syspublications? Msjob_commands?
What is dropped when a table is dropped? Triggers? Defaults? Stored procedures?
Understand self-joins and aliases. You will see several examples where the aliases in the from statement are bogus.
What does sp_logdevice command do? Why would you use it with ALTER DATABASE.
Know WAITFOR DELAY and WAITFOR TIME.
You want to insure referential integrity. What it is used? Trigger? Rule? Constraint? Index? Stored Procedure?
You have an identity column and want to insert values into it and another column. What would the syntax look like to do this?
You want to use an unused identity value. How would you determine which values are available? How would you determine the next value? What would the syntax look like to set up an identity column. SEED? INCREMENT?
Understand Mail options and stored procedures within SQL Server.
You are setting up some stored procedures and for security purposes to hide the actual text used to create the stored procedure. How can you accomplish this? sp_helptext? WITH ENCRYPTION?
You want to find all the books in the titles table that begin with the word "computer". How would you structure your query to accomplish this?
Under what circumstances would you use SELECT * and SELECT ALL?
What does a HIGH FILLFACTOR vs a LOW FILLFACTOR mean?
How does a composite primary key work? Is its uniqueness due to both columns?
Date last updated: 12/04/04