Update statistics
The update statistics section is relatively straightforward. You simply need to know the syntax for the update statistics command and also how update statistics affects the performance of your server.

Extended stored procedures
The extended stored procedures questions will test your knowledge of syntax for the common set of commands such as trace, mail, and xp_cmdshell. You'll also need to know which login has the authority to add and remove extended stored procedures.

SQL
If there were a single section to cover very heavily, it would be this one.  Fully 1/3 of the exam will probably focus on these topics.  Lose these questions, and there is no hope of passing.  On the other hand, if you can't answer these questions, you shouldn't be taking this exam.  For this section make sure that you know and understand syntax of a select, insert, update, and delete statement.  Know which order clauses go in and which clauses are required when using other clauses.  (An order by is required if using a compute by.)  Understand the alternate update and delete syntax: delete table from table1, table2, ... and update table set <column> = <value> from ...  Know what the table limit is for queries an that this applies to tables contained inside views.  Be able to apply both syntaxes for a case statement.  Aggregates and group by clauses will definitely come up, so make sure you understand the rules when mixing aggregate and nonaggregate columns.  TSQL functions and pattern matching will be minor areas, maybe one question, but make sure you understand these as one question can make the difference.  The update and insert statements each have a default or default values option, make sure you cover how this is implemented.   Finally most of the questions will use ANSI-89 syntax, but one or two contained SQL-92 syntax.  Don't let this trip you up.

Tables
The exam wouldn't be complete without a few questions about tables.  Know the syntax for creating and altering a table.  What can be accomplished with an alter and what can not.  Make sure you understand what an identity is and the limitations of the various datatypes.  Only one user can truncate a table, who is it?  In addition to permanent tables, make sure you understand temp tables.  Where do they reside?   What are the different methods for creating them?  What is their scope?   How long do they persist?

Indexes
The questions on indexes can get pretty tricky.  Understand the difference between a clustered and nonclustered index.  Make sure you know the syntax of the create index statement.  The most difficult will be identifying in what cases you would use a clustered index and in which cases you would use a nonclustered index.  What is a unique index and what does it correspond to in terms of your data model?

Referential Integrity
Thought you'd get by without addressing this one?  Understand the different methods for enforcing declarative RI and the syntax.  Remember that when you add a primary key constraint to a table, a unique index is created by SQL Server.  Make sure you also know how to implement programmatic RI: rules, defaults, and triggers.  What situations require the use of a trigger?

Stored procedures and triggers
Study the syntax for creating triggers and stored procedures.  What is the nesting limit?  What permissions does a stored procedure have on objects and how does this affect your security measures.  How many triggers can be created for a table and who is the only person that can create a trigger.

Views
This one is pretty straightforward.  Make sure you know how to create a view.   How does a view fit into your security implementation?  What statements can not be contained in a view?  What are the restrictions for inserting, updating, and deleting from a view?  Yes, you can BCP from a view.  Finally, make sure you understand how the with check option clause affects a view.

Cursors
The last topic is cursors.  Go over the syntax for creating a cursor and the steps to implement one.  When would you use a cursor?  What are the two modes for a cursor and which one is the default mode?  Make sure you know the syntax for updating and deleting based upon cursor position.  Caution:  Read these questions carefully!  They will try to trip you up on the exact wording if you aren't reading carefully.

70-027 SQL Server Implementation 1 2 3