Tongue In Cheek | Michael J. Swart

April 13, 2011

Whew… Microsoft Certification Exam 70-433

Filed under: SQLServerPedia Syndication,Technical Articles,Tongue In Cheek — Tags: "sql server", 70-433, exam study guide, mcts — 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.

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

-- Comments (17)

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

SQLololo

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

Bonus cartoon

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

-- Comments (7)

March 2, 2011

Mandelbrot Revisited.

Filed under: SQLServerPedia Syndication,Technical Articles,Tongue In Cheek — Tags: "Sql Server" mandelbrot — 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)

SET NOCOUNT ON;
 
--populate
;WITH Numbers ([row]) AS
(
   SELECT TOP 100 CAST(ROW_NUMBER() OVER (ORDER BY NEWID()) AS FLOAT) [row]
   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
INTO #GRID
FROM Numbers A, Numbers B
WHERE B.[row] <= 24
GO
 
-- scale
UPDATE #GRID
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
GO
 
--iterate
UPDATE #GRID
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
GO
 
--print
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
   FROM (SELECT DISTINCT y FROM #GRID) AS G2
)
SELECT REPLACE(c, ' ', '') FROM concatenated ORDER BY y
GO
 
DROP TABLE #GRID

You’ll get something like the following:

@@@@@@@AAAAAABBBBBBBBBBBBBBBBBBBBBBBBBBBCCCCCCCCCCCDDDDDEFFGILWLIGFEDDDCCCCCCCBBBBBBBBAAAAAAAAAAAAAA
@@@@@@AAAABBBBBBBBBBBBBBBBBBBBBBBBBBCCCCCCCCCCCCDDDDEEEFJMESD..NXMNHEDDDDDDCCCCCBBBBBBBBBAAAAAAAAAAA
@@@@AAAABBBBBBBBBBBBBBBBBBBBBBBBBCCCCCCCCCCCDDDEEEEEFFFGHO.......GQHGFEEEEDDDDDCCCBBBBBBBBBAAAAAAAAA
@@@AAABBBBBBBBBBBBBBBBBBBBBBBBCCCCCCCCDDDDDEFNMQ.JHHIAPLEOPV....VPQMLJPQGFFFFHNEDCCBBBBBBBBBBAAAAAAA
@@AABBBBBBBBBBBBBBBBBBBBBBCCCCCCDDDDDDDDEEEFFHJR..HU...................XJLICTUAIEDDCCBBBBBBBBBBAAAAA
@@ABBBBBBBBBBBBBBBBBBBCCCDDDDDDDDDDDDEEEEEFHMJMQR............................OHFEDDCCCBBBBBBBBBBAAAA
@ABBBBBBBBBBBBBCCCCCDDEPHFEEEEEEEEEFFFFFGGILR.................................JGGFDDCCCBBBBBBBBBBAAA
ABBBBBBCCCCCCCCCDDDDEEFHQJJLIHIGMIHHGGGHHJR.....................................SKEDCCCCBBBBBBBBBBAA
BBBCCCCCCCCCCDDDDDDDEFFGHLW..WF...T.RPKJKNL....................................MJFEDCCCCBBBBBBBBBBBA
BCCCCCCCCCCDDDDDDEFGGGHKG..............UQG.....................................KGEDDCCCCCBBBBBBBBBBA
CCDDCCCDEEEEEEFFFGHJ@PRPC.....................................................KFEDDDCCCCCBBBBBBBBBBA
...........................................................................OJHFFEDDDCCCCCBBBBBBBBBBB
CCDDCCCDEEEEEEFFFGHJ@PRPC.....................................................KFEDDDCCCCCBBBBBBBBBBA
BCCCCCCCCCCDDDDDDEFGGGHKG..............UQG.....................................KGEDDCCCCCBBBBBBBBBBA
BBBCCCCCCCCCCDDDDDDDEFFGHLW..WF...T.RPKJKNL....................................MJFEDCCCCBBBBBBBBBBBA
ABBBBBBCCCCCCCCCDDDDEEFHQJJLIHIGMIHHGGGHHJR.....................................SKEDCCCCBBBBBBBBBBAA
@ABBBBBBBBBBBBBCCCCCDDEPHFEEEEEEEEEFFFFFGGILR.................................JGGFDDCCCBBBBBBBBBBAAA
@@ABBBBBBBBBBBBBBBBBBBCCCDDDDDDDDDDDDEEEEEFHMJMQR............................OHFEDDCCCBBBBBBBBBBAAAA
@@AABBBBBBBBBBBBBBBBBBBBBBCCCCCCDDDDDDDDEEEFFHJR..HU...................XJLICTUAIEDDCCBBBBBBBBBBAAAAA
@@@AAABBBBBBBBBBBBBBBBBBBBBBBBCCCCCCCCDDDDDEFNMQ.JHHIAPLEOPV....VPQMLJPQGFFFFHNEDCCBBBBBBBBBBAAAAAAA
@@@@AAAABBBBBBBBBBBBBBBBBBBBBBBBBCCCCCCCCCCCDDDEEEEEFFFGHO.......GQHGFEEEEDDDDDCCCBBBBBBBBBAAAAAAAAA
@@@@@@AAAABBBBBBBBBBBBBBBBBBBBBBBBBBCCCCCCCCCCCCDDDDEEEFJMESD..NXMNHEDDDDDDCCCCCBBBBBBBBBAAAAAAAAAAA
@@@@@@@AAAAAABBBBBBBBBBBBBBBBBBBBBBBBBBBCCCCCCCCCCCDDDDDEFFGILWLIGFEDDDCCCCCCCBBBBBBBBAAAAAAAAAAAAAA
@@@@@@@@AAAAAAAABBBBBBBBBBBBBBBBBBBBBBBBBBBCCCCCCCCCCDDDDEEFHNIHJREDCCCCCCCBBBBBBBBAAAAAAAAAAAAAAAAA

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

... AND MY AXE!

-- Comments (4)

January 11, 2011

My 2011 Resolution

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

So 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!

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.

-- Comments (9)

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.

-- Comments (2)

October 5, 2010

DBA humour

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

Data (Bass)

(Pun so intended)

-- Comments (3)

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 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:

and with C# and the usual magic I get:

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

-- Comments (0)

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:

birth_of_venus.sql

The Results

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

-- Comments (34)

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

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.

-- Comments (1)

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.

-- Comments (1)

« Newer Posts — Older Posts »