Michael J. Swart

April 13, 2011

Whew… Microsoft Certification Exam 70-433

Filed under: SQLServerPedia Syndication,Technical Articles,Tongue In Cheek — Tags: , , , — Michael J. Swart @ 12:00 pm

Yesterday was April 12, 2011, and as far as I know mainstream support for SQL Server 2005 ended. See Aaron Bertrand’s A Quick Note About SQL Server 2005 Mainstream Support.

And if you care, this also means that the Microsoft Certifications that are tied to that product also expire. At least that’s what I gather from this Microsoft FAQ which says:

Q. How long will my certification be valid?
A. Today, most of our Microsoft Certified Technology Specialist (MCTS), Microsoft Certified IT Professional (MCITP), and Microsoft Certified Professional Developer (MCPD) exams retire when Microsoft discontinues mainstream support for the related technology…

But that’s just the exam, I’m not too sure about the certification. In any case it really doesn’t matter at all. I mean, the skills a person has with SQL Server does not diminish or become obsolete with the expiration of SQL Server 2005. SQL Server 2008 and other versions are still alive and kicking.

Bad News: So my MCTS (Microsoft Certified Technology Specialist) certification for SQL Server 2005 expired yesterday (don’t worry I’ll get over it).
Good News: My MCTS certification for SQL Server 2008 takes effect last Friday when I passed the 70-433 exam (yaay!!) It’s my first step towards a new certification for myself, the MCITP (IT Professional).

It was a decent exam, I thought it was fair. There were a couple of things I would have changed:

  • I found at least four typos in the questions and at least once I had to answer the question they meant to ask not the question they actually asked.
  • Two thirds of the way through the exam, there was a power outage. No one could have foreseen it, but I was glad to discover that my questions weren’t lost and the time without electricity was not on-the-clock.

Section 5, My Nemesis

I did well in every section except one, Working With Additional SQL Server Components. In that section I got two questions correct out of six. I guess I should have given more focus to it while studying. To be honest, I didn’t actually work the plan I wrote out for myself for that section. I had heard scary things about some of the other sections and focused on them. But even so, two out of six is pretty rotten for a multiple choice test. Random guesses might have even fared better. A lucky monkey has a good chance (46%) of doing at least as well or better than I did simply by throwing darts randomly as a strategy for choosing answers.

A picture of a monkey with holding a dart.

My 70-433 Study Plan

Of course I can’t give details about the content of the exam for obvious reasons, but I do want to share the study plan I followed when studying for this exam. I wrote the plan based on Skills Measured as described by Microsoft. Everything I write here can be found online:

Tables and Views

  • What does with CHECK OPTION mean when creating views
  • What does with ENCRYPTION mean when creating views
  • what’s the syntax for fill factor?
  • write (without checking syntax) a nci with fill factor
  • rebuild an index (without checking syntax) with a fillfactor
  • create statistics without checking syntax
  • add a pk (without checking syntax)
  • add a fk with cascading deletes
  • disable/enable pk
  • disable/enable check
  • disable/enable fk
  • disable/enable unique
  • Create table with column that has filestream
  • create a spatial column (without checking)
  • What’s a structured vs semi-structured column?
  • name collation varieties (case sensitive etc…)
  • Create partitioned table (without checking syntax)
  • Split merged table into two, merge back together
  • Create empty table and switch into partition

Programming objects

  • Without checking syntax, pass in a tvp
  • without checking syntax, write sprocs with all varieties of “Execute as”
  • Without checking syntax, create sproc that specifies RECOMPILE
  • Without checking syntax, create sproc that specifies WITH ENCRYPTION
  • What permissions are there on sprocs? 2 flavors of security here: GRANT, DENY, REVOKE versus EXECUTE AS
  • Build Function without checking syntax
  • What permissions are there on udfs? flavors of security here: GRANT, DENY, REVOKE versus EXECUTE AS
  • What is schemabinding on udf?
  • what are the three (four?) kinds of functions
  • Explore flavours of triggers.
  • Create trigger that uses INSERTED/DELETED
  • Explore security on triggers, (EXECUTE AS)
  • Create DDL trigger.
  • Create DDL trigger that returns data.
  • Create CLR assembly.
  • Explore clr: SAFE/UNSAFE/EXTERNAL_ACCESS
  • Explore CLR: set trustworthy
  • Implement TRY CATCH RAISERROR
  • error handling, look at Rusanu’s service broker scripts, he’s an awesome error handler.

Query Fundamentals

  • Explore different DELETE syntax (i.e. DELETE A FROM A…)
  • Without checking syntax, use the OUTPUT clause
  • Without checking syntax, use the MERGE statement
  • Write a query that uses grouping sets.
  • What’s the difference between LEN and DATALENGTH
  • without checking syntax, use patindex and charindex

Additional Query techniques

  • write a subqueries (correlated)
  • write a subqueries (simple)
  • write a subqueries (scalar)
  • write a subqueries (list)
  • write a subqueries (table valued)
  • Without checking syntax, Use rank
  • Without checking syntax, Use dense rank
  • Without checking syntax, Use row_number
  • Without checking syntax, Use ntile
  • Without checking syntax, Use row_number, and partition by
  • look through table hints to understand any unused ones
  • look through query hints to understand any unused ones
  • manage collations? how can they be managed.
  • what’s the diff between column/db/server collations
  • (international) define custom errors

Other SQL Components

  • Set up db mail
  • implement full text search
  • full text: Use CONTAINS
  • full text: Use CONTAINSTABLE
  • full text: Use FREETEXT
  • full text: Use FREETEXTTABLE
  • full text: Use STOPLIST
  • Use powershell and smo to create .net cmdlet demonstrates smo
  • Take service broker tutorial, compare sample syntax to rusanu’s syntax
  • Implement change tracking
  • Create db audit spec
  • Use CHANGETABLE

XML Data

  • use each FOR XML type
  • without checking syntax, use OPENXML, and sp_XML_docs
  • without checking syntax, shred xml.
  • review xpath syntax
  • what’s the difference between xquery and xpath?
  • Read through books online topic on xml

Gathering Perf Info

  • Use SHOWPLAN to (showplanxml?) to look through query
  • look at how paul (@sql_kiwi) displays query plan
  • Run through DTA, Prepare a workload <– remember options in DTA here!!!
  • Review catalog views. (Is this INFORMATION_SCHEMA?)

17 Comments

  1. Thanks for this. Added to my bookmarks for when I hopefully go after my own certification one day. Congrats on yours!

    Comment by Aaron Cooper — April 13, 2011 @ 12:18 pm

  2. Thanks Aaron! There are a number of tracks for these, I’m doing the db developer track. If you’re doing the DBA track then there’s an excellent study guide (same sort of deal) done by Buck Woody. I’ll link to that one when I’m back at my desk.

    Comment by Michael J. Swart — April 13, 2011 @ 12:36 pm

  3. Buck Woody’s notes for the 70-432 exam are at http://blogs.msdn.com/b/buckwoody/archive/tags/certifications/

    Comment by Michael J. Swart — April 13, 2011 @ 1:14 pm

  4. Congratulations on the passing of an exam 70-433.

    Comment by Krzysztof — April 13, 2011 @ 2:30 pm

  5. Congrats Michael and thanks for the test prep info.

    Comment by Dave Schutz — April 18, 2011 @ 2:56 pm

  6. Dziękuję Krzysztof,
    Thanks Dave, Hope it’s useful

    Comment by Michael J. Swart — April 18, 2011 @ 3:24 pm

  7. [...] Certification Exam 70-451. I know, I know. This blog post seems like a rerun. Wasn’t it only two weeks ago that I passed this exam? What [...]

    Pingback by Woo Hoo… Microsoft Certification Exam 70-451 | Michael J. Swart — April 27, 2011 @ 12:08 pm

  8. Dude, you rock for sharing this information. I always enjoy primers like this and wanted to thank you for allowing those with the predisposition to utilize!

    Congrats on passing as well.

    Comment by Rick — July 25, 2011 @ 6:44 pm

  9. Dude, no YOU rock. Thanks for the comment.
    If you go ahead with the 70-433 and don’t find it too bad, then don’t be afraid of the 70-451. I felt they weren’t too different (in terms of difficulty)

    Comment by Michael J. Swart — July 26, 2011 @ 8:13 am

  10. [...] http://michaeljswart.com/2011/04/whew-microsoft-certification-exam-70-433/ [...]

    Pingback by My MS70-433 study guide « SQLThoughts — September 7, 2011 @ 11:49 am

  11. [...] study plan I found online from Michael J [...]

    Pingback by 30 Days to 70-433 | What I Learned Today — November 2, 2011 @ 7:20 pm

  12. Hi,

    very nice article, thank you!

    Im looking for information to answer a question:
    “What’s a structured vs semi-structured column?”

    Maybe You can help me out to find some article on the Internet?

    Thank you very much!!

    P.S. Congrats with your achievements!

    Comment by Simonas — August 17, 2012 @ 7:00 am

  13. This is terminology that Microsoft used in their page describing “skills measured”. Google’s topic on that is here: http://en.wikipedia.org/wiki/Semi-structured_data. They describe Semi-structured by contrasting how it’s different than structured data.

    As implemented by Microsoft SQL Server, semi-structured fields have XML types. Everything else is structured.

    Comment by Michael J. Swart — August 17, 2012 @ 8:21 am

  14. So, i’m willing to get a cert on Sql Server, but i’m not so sure if i should go for a SQL Server 2008 or a SQL Server 2012… mainly because the 2008 certs will expire on July 2013.. i have a lot of experience working with sql server (since the 2000 version) but i’m pretty sure actually i dont use all the cool stuff that it has…

    What do you recommend? Should i go for the 2008? or the 2012?

    Comment by Toño — November 21, 2012 @ 4:02 pm

  15. Hi Toño,
    I have to definitely recommend the later ones.
    Technically, you’re weighing a choice between “SQL Server 2008″ and “SQL Server”. You’ll notice that there’s no date associated with the last one. My SQL Server 2008 certification expires when the product expires. The SQL Server certifications that were introduced this year are not connected with any particular version, but need to be renewed after 3 years (I believe).

    So to answer the question as you asked it, go for the 2012 certs.

    Comment by Michael J. Swart — November 21, 2012 @ 4:49 pm

  16. Thanks!

    Comment by Toño — November 22, 2012 @ 3:52 pm

  17. Hi Guys,

    I am planning to sit in 70-433 exam, if anyone like to share Transcender or SelfTest prep material then pls email me.

    Thanks

    Comment by steve Dyson — January 15, 2014 @ 5:02 pm

RSS feed for comments on this post.

Sorry, the comment form is closed at this time.

Powered by WordPress