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: set trustworthy
  • 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

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?)

March 30, 2011

Something Light-hearted.

Filed under: Miscelleaneous SQL,Tongue In Cheek — Michael J. Swart @ 12:00 pm

So it’s April Fool’s day later this week and instead of trying to pull something over on folks, I thought I’d just do something light-hearted. So nothing super technical this week. Just plugged in my electric pen and …

How Most DBA Romances Start

Princess Bride spoof


Trololo guy

On Twitter Last Night

@MJSwart: I need one more SQL comic to draw. Ideas? (You give the idea, I give the drawing and the credit)
@BrentO: I like to think of the Resource Governor as Colonel Sanders. 😉
@MJSwart: Done and done! Stay tuned.

… Here you go Brent
Colonel Sanders spoof

Bonus cartoon

For a nifty bonus cartoon just press F13 (This works on most browsers).

March 2, 2011

Mandelbrot Revisited.

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

So I caught some sort of flu bug recently and that means no new blog post this week. Instead, I’m going to dig through the archives to bring something that you may have missed the first time around.

Today I’m sharing something I first posted two years ago: Something Pretty.

Something Pretty

A T-SQL script I came up with that displays the Mandelbrot set. (Tip: Hit Ctrl-T before executing)

;WITH Numbers ([row]) AS
   FROM sys.columns
SELECT A.row AS x,
   B.row AS y,
   0 AS iter,
   A.row AS iterx,
   B.row AS itery,
   '.' AS symbol
FROM Numbers A, Numbers B
WHERE B.[row] <= 24
-- scale
SET x = x * 3.0 / 100.0 - 2,
   y = y * 2.0 / 24.0 - 1,
   iterx = x * 3.0 / 100.0 - 2,
   itery = y * 2.0 / 24.0 - 1
SET iterx = iterx*iterx - itery*itery + x,
    itery = 2*iterx*itery + y,
    iter = iter+1
WHERE iterx*iterx+itery*itery <= 2*2
GO 257
UPDATE #GRID SET symbol = CHAR(64+(iter%26)) WHERE NOT iter = 257
WITH concatenated (y, c) AS
   SELECT G2.y,
       (SELECT SUBSTRING(G.symbol, 1, 1) AS [data()] FROM #GRID G WHERE G.y = G2.y FOR XML PATH('')) c
SELECT REPLACE(c, ' ', '') FROM concatenated ORDER BY y

You’ll get something like the following:


I’ll be back next week with something new, you have my word.



January 11, 2011

My 2011 Resolution

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

T-SQL Tuesday LogoSo it’s T-SQL Tuesday time again! Thanks to Jenn McCown for hosting this month. Today we’re talking about resolutions. Quite apt for January I think.

Resolution time!

Michael J. Swart (looking introspective) resolves to be less introspective.

But seriously, What are my resolutions this year? My personal resolutions are personal (but fairly dull) and my work goals are exciting (but also private). So what’s left? Here it is, short and sweet:

I plan to blog more.

Hmm… that could have fit inside a tweet 7 times. I don’t think that’s quite gonna cut it for a blog post. Okay, Here’s exactly what I plan. I have a goal to get four times as many readers as 2010. It’s ambitious, but here’s some things that could help.

  • Write a post once a week with a post going live at Wednesday at noon. I haven’t been the most prolific and hopefully 2011 will be better.
  • Keep to technical content as much as possible (SQL Server Denali should be exciting).
  • Include illustrations when I can.

I like drawing. And I think I’ve improved enough that I can settle on a style. (one panel 300×500, reminiscent of Ripley’s Believe It or Not).  At best, I get a smash hit every month or two. At worst it’s a nice graphic that pulls people into the content.

November 7, 2010

Why Michael J Swart won’t be at the PASS Summit this year.

Filed under: SQLServerPedia Syndication,Tongue In Cheek — Michael J. Swart @ 11:45 am

Hiker looking for people at a mountain pass and mountain summit.

I get confused sometimes

But seriously, I went to my first PASS Summit a year ago and I had a blast. As much as I’d like to be in Seattle this week, it’s just not in the cards for me. But I look forward to following all the news via blogs and twitter and I’ll even catch some of the keynotes courtesy of PASS.

October 5, 2010

DBA humour

Filed under: Data Cartoons,SQLServerPedia Syndication,Tongue In Cheek — Michael J. Swart @ 9:44 pm

Data (Bass)

Data (Bass)

Data (Bass)

(Pun so intended)

May 7, 2010

SQL Self Portrait

Filed under: SQLServerPedia Syndication,Tongue In Cheek — Michael J. Swart @ 9:24 am

Something fun for Friday. I pulled out almost all the content for this post from the comment section of More images from the Spatial Results Tab.

The one thing that I learned (and might be news to you) is the reason that Microsoft’s spatial results tab has such bland faded colors. It turns out that these colors have a certain amount of transparency applied to them so on a white background, they seem faded. But this also means that overlapping colors are combined!

Overlapping colors

Overlapping colors in the Spatial Results Tab

And transparent colors means a larger palette to play with without using the more faded colors! So I first take this source photo:


I reduce the color depth to the colors in my palette:

Reduced colors

and with C# and the usual magic I get:

SQL Self Portrait (Click to embiggen)

SQL Self Portrait (Click to embiggen)

With only six “polygons”. For the curious, the query itself is here but I’m warning you, uncompressed it’s 260 kb and it takes some time to run.

Happy Friday

February 17, 2010

More images from the Spatial Results tab

Filed under: SQLServerPedia Syndication,Tongue In Cheek — Michael J. Swart @ 1:00 am

Inspired by Itzik Ben Gan’s Geekiest Sig Challenge and Michael Cole’s virtual Christmas Card. I came up with a query that forces SQL Server’s spatial results tab to display something pretty. In recognition of Valentine’s Day, I chose a detail of Botticelli’s Birth of Venus.

The Query

The query isn’t too large, but it’s 120 KB, you can download it here:


The Results

Not quite as pretty as the original

Not quite as pretty as the original

The Process

  • First reduce the color depth to a given color pallet (SQL Server’s icky pastels). I personally use a custom technique for this.
  • Use a program like Adobe Illustrator to turn into an svg file.
  • From there use your C# and VI savvy to convert into a SQL query.

Cheers guys

January 18, 2010

Tic Tac Toe vs. T-SQL

Filed under: SQLServerPedia Syndication,Tongue In Cheek — Michael J. Swart @ 11:50 pm

It's learning

It's learning

Stephen Horne (aka BlueDog67) threw down the challenge last week to Create An Unbeatable Tic-Tac-Toe Game. He did that in this post.

So that’s what I did. Here’s my crack at it (which I also left as a comment):

The curious can download https://michaeljswart.com/wp-content/uploads/2010/01/tictactoe.zip

The zipped file contains two files:

  • One is a bcp output file that contains data. (It is used by the script)
  • The other is a sql script that creates the following tables and procedures:
    • TABLE tttBoard (spaceId, value)
    • TABLE tttMoves(id, turn, bestNextMove)
    • PROCEDURE s_CleanBoard()
    • PROCEDURE s_PrintBoard()
    • PROCEDURE s_MakeMove(@spaceId, @letter)
    • PROCEDURE s_ComputerMove

I decided to use a look up table (tttMoves) to find the best move. I thought it fitting to develop a solution that uses a set of data rather than an algorithm because of it is T-SQL after all. Here are three things I learned.

  • The size of tttMoves is extremely small. Roughly 4500. Think about it. If you take nine ordered spaces on a tic-tac-toe board and put every combination of X, O, or space, there’s still a manageable number of boards to deal with. I removed completed boards and boards showing games that were invalid. 4500 were left. Of these game boards, only about 400 were more complicated than a simple win or a block for their best move.
  • The whole process was a lot quicker than it took the eighties super-computer to learn tic tac toe in WarGames. (Who remembers that? To this day, joshua is a terrible password)
  • A T-SQL tip: This should have been obvious but remember that order of columns in a table matters when using BULK INSERT with a data file using native formatting.

January 17, 2010

An incentive, and a hint

Filed under: SQLServerPedia Syndication,Tongue In Cheek — Michael J. Swart @ 8:21 pm

So last week, I challenged the world to solve a puzzle. This was part of Adam Machanic’s T-SQL Tuesday. And while the entire world does not read my blog, Google Analytics tell me that there are roughly a hundred of you that do.

But even among those of you who read my blog, there were no bites. Not one. So I decided to not give the solution this week. Instead I offer a prize and a hint. First the hint for everyone (it’s a two parter):

  • First, look in the source for a clue
  • secondly, the puzzle is not about cryptography, but can be solved with one or two lines of T-SQL

And now for the prize: Coffee on me. This prize is for one person. I’ve got a $10 Tim Horton’s gift card ready to be sent to the first person to tell me the answer to the puzzle. (A Starbucks gift card if you’re international). So get to it!
Update(1/18/2010)… We have a winner. Check out the solution here.

« Newer PostsOlder Posts »

Powered by WordPress