Passing this exam gives you MCTS – SQL Server 2005 certification. It’s a prerequisite for the MCITP: DBAdmin cert, but it’s not an elective for MCPD (unlike the 70-229 SQL 2000 exam for MCSD).

The Transcender test is available on DEVEXAM, and it’s helpful. It includes simulations that work like the ones on the actual test. I also used the ExamCram book by Thomas Moore, which had good test-specific material. I’ll pass it on to Mark D. for the Dev library.

The exam had 47 questions, scheduled for 170 minutes, with a score of 700 to pass. The exam was in two parts -- 70 minutes for 35 objective questions, and 100 minutes for 12 simulation questions. Once you finish the objective question section, you can’t go back from the simulation section.

For folks up north, I went to the Business Communication Solutions test center in Pflugerville. It’s tucked away in the same strip mall on Pecan Street / 1825 as the cone-shaped ice cream stand from the opening credits of the Friday Night Lights TV show.

There’s a lot of backup stuff on the exam, both using SQL Management Studio and T-SQL backup syntax. Know your way around the General and Options pages of the backup dialog in Mgmt Studio, particularly the “Truncate” and “Back up the tail of the log” transaction log options. Know about the various backup types, including snapshots. One weird thing: one of the simulations apparently wanted a Transaction Log backup, but the Recovery Model showed as SIMPLE, meaning that in reality you can’t do a Transaction Log backup (the option’s not available in the product, but it was in the simulation).

Know about DML triggers (fire on INSERT, UPDATE, DELETE) vs. DDL triggers (fire on CREATE, ALTER, DROP), INSTEAD OF triggers, and the setup options that control nested triggers (Server Properties / Advanced / Allow Triggers to Fire Others, and Database Properties / Options / Recursive Triggers Enabled).

TRIM is not a T-SQL function, but LTRIM and RTRIM are; so use “select LTRIM(RTRIM(column))” to trim whitespace.

I was expecting a lot about the XML datatype – XML RAW, XML AUTO, creating XML indexes, using the XML methods like query, modify, nodes, etc., etc. The only question I got, though, was a simple one where you just had to know there was an XML datatype (i.e. you need to store exact copies of XML documents in your db; do you use the nvarchar, varbinary, xml datatype?). That may not be typical.

Also, I expected more on table partitioning – CREATE PARTITION FUNCTION, CREATE PARTITION SCHEME, $PARTITION in queries, etc. All I got was a simple one about horizontal vs. vertical partitioning to handle a huge table.

There was one simulation on creating a new database that wanted the log to be on a fault tolerant drive – remember that RAID 1 is fault tolerant, RAID 0 is not.

T-SQL supports TRY / CATCH syntax, like this:

BEGIN TRY
SELECT * FROM sys.messages WHERE message_id = 21;
END TRY
BEGIN CATCH
SELECT ERROR_NUMBER() AS ErrorNumber;
END CATCH;
GO

Say you have a CLR function that accesses the file system, and users start whining that it doesn’t work. You need to go to the Programmability / Assemblies node for the database, and set the assembly for EXTERNAL_ACCESS mode (SAFE is the default, which means the CLR function can’t touch anything outside of SQL Server). UNSAFE mode isn’t necessary.

There was a simulation about uncommitted transactions and Management / Activity Monitor, so know your way around the View Processes, View Locks by Process, and View Locks by Object screens. Under View Processes, you can right-click a process, pick Details, and see the last SQL batch, which had the information I needed (no COMMIT to go with BEGIN TRANSACTION).

Good luck!