MCITP: 2 Down 2 to go

Categories: News, Professional, SSC
Comments: 3 Comments
Published on: May 26, 2011

It’s time for the weekly update.  Much like last week, we had more issues with taking this exam today.  I scheduled exam 70-433 for first thing in the morning at a “private” test center.  The registration was easy enough.  The process is a bit different since this test center is not listed on the Prometric site.  The staff seemed friendly and helpful.

The problem this time around came down to lack of connectivity to the Prometric server in order to download my exam.  This morning I was there for 35 minutes and then we decided to reschedule.  I was going to reschedule for next week but didn’t reschedule when I left.  I wanted to wait and see.  The center contacted me over an hour later and informed me that they had regained connectivity.  I scheduled to go back in during lunch to take the exam.

This exam was pretty straight forward.  I passed with ample time remaining and only had concerns about a couple of questions.  The concerns were in my knowledge base so I figured I could go home and create some scenarios to try.

Next week, I will be working towards 70-450.

Also, I have been thinking of also pursuing the BI MCITP.  That track will take more study and practice on my part.  But I believe it will be well worth the effort.  Thus, these updates may be changing to represent the additional two exams.

CTE, Recursion and Math

Tags: ,
Comments: No Comments
Published on: May 23, 2011

Earlier this month we had a TSQL Tuesday on the topic of CTEs.  I bailed on my submission because I already posted some CTE examples and was bone dry on what I could write about.

Later I saw a request for some help on creating combinations and permutations from TSQL.  I thought about this request and finally came up with the idea to use CTEs to do it.  The idea came about based on seeing multiple suggestions in that thread and then combining some of the pieces together.  Though not a suggestion, per-se, but seeing in the code submitted that a factorial was being used along with the sum of all integers from 1 to the given integer (or summation), I decided I would incorporate that into my solution.

I thus set out to create a solution that would use a CTE to generate those permutations, the summation, and the factorial.  In this article I will just be focusing on how I came to produce the factorial and summation using a little bit of Recursive CTE.  Also, we will see an alternative solution to calculating the summation.  For help in setting up the structure of a recursive CTE, you may want to refer to this article.

Here is the script first, and then I will explain later.

[codesyntax lang=”tsql”]


As you can see, I am using several CTEs in this script.  The first group of CTEs is to create a Dynamic Tally (Numbers) table.  The last two CTEs are performing the same thing but with a bit of filtering applied.  When you look at them, you should note that the first one is Casting the Factorial to a Numeric data type.  The last CTE is casting the Factorial to a FLOAT data type.  This isn’t necessarily required but more of an aesthetic for myself.  So any number greater than 33 will use the FLOAT data type and anything less than 34 will use the numeric data type.

The FLOAT data type will support those smaller numbers – that’s not an issue.  The Numeric data type will not support the larger values (it is limited to 38 characters).  We start running into arithmetic overflow at 34! and must use the FLOAT at that point.  The problem is that I prefer to avoid the overflow style of displaying the numbers except when necessary.  For simplicity sake, you could eliminate the filtering between the CTEs and simply use the FLOAT across the board.

Since I am trying to filter between the two and create a single row result set, I also have a second issue that is created by this code.  That issue is resolved and seen when performing the final select from the CTEs.  If the query requires the use of FLOAT, I will get an error message with an arithmetic overflow again unless I Convert the Numeric and Float results into something that is compatible between the two.  I chose to use VarChar(100) in this case.

Note, also, that I am using a FULL OUTER Join in the final query.  This is due to the fact that I will see results in either the CTEMathN or CTEMathF CTE but not both.

Now down into the nitty gritty.  Let’s look at CTEMathN to see how we are getting the factorial and the summation.

[codesyntax lang=”tsql”]


First we have the required anchor definition.  Second we have the recursion definition joined by the Union All and referencing the same CTE where both of these pieces reside.

The factorial is easy enough.  Since a Factorial is n! or n(n-1) we can multiply n by the previous n in the sequence.  We can get both of those values through our join statement where we join back out to the Tally CTE and showing an increment in the number (here I am join CTETally.n to CTEMathN +1).  This ensures we will move through the record set (or recurse).

The same principle applies for the summation.  We add each number in the sequence from 1 to n to retrieve our result.  So, much the same as the factorial, I add n to the previous value of n and proceed from there through the record set.

Note also that I threw an additional limiting factor on the where clause.  I want to make sure that I do not try to recurse through a record set that includes numbers outside of my range.

Now, since I only really care about the factorial and summation for the number I inserted in the variable at the beginning, I want to make sure that I have a filter added to the final select clause.  Here is that final filter…

[codesyntax lang=”tsql”]


This enforces a single record will be returned by selecting only the last iteration.  Iteration is another column in the CTEs that is used during our recursion process.  The iteration column just increments by a value of one with each pass.

Pretty easy eh?

Now, if all you need is a summation and it is not used to control result sets (like mine will be in the next bit where we discuss permutations), then you could rip out the aggregation stuff and just use this in the final select statement…

[codesyntax lang=”tsql”]


That would be in place of this bit…

[codesyntax lang=”tsql”]


Have fun with it!!

MCITP 1 Down 3 To Go

Categories: News, Professional, SSC
Comments: No Comments
Published on: May 20, 2011

Yesterday I posted a quick blurb about my certification plans this year.  Well, ok not full blown plans , but you get the gist.  I announced that I would be taking an exam this morning.  Well, this is to report on that experience.

I scheduled the exam for near first thing in the morning.  I didn’t do first thing but what’s 15 minutes?  I got there 20 minutes early (Prometric recommends arriving up to 30 minutes early).  The location was not difficult to find but it was more than an hour drive (not too enamored by that, but hey it is what it is 😉 ).  At test start time, I was already signed in at the center and ready to go.  They started prepping the computer and everything looked good to go.  I clicked on “start exam” and that is when things turned south.

Needless to say, thirty minutes later and I had yet to proceed past the first question.  Was I in over my head?  Had I studied enough at this point?  I was ready to just ask the administrator to forget it and reschedule me.  I might have done so as well, but she was far too busy with other exam takers and on the phone with this person and that.  She really needed a few more bodies in the testing center today.  SO, I waited patiently until she could breath.

She was about to start helping another candidate get into his computer, when he politely suggested she help me first.  She came back to me to assist.  One more time we were going to try to get this exam working on the computer after prior failures.  You see, the exam just blew up on the computer and it took 30+ minutes to get it running properly.  This time, after clicking “start exam” we thought it was going to fail again.  Then five minutes later it finally popped up.  We had already given up hope on this computer.

As for the actual exam and material, I didn’t like the 5 second delay between clicking next and finally getting to see that question.  But there was more than adequate time to complete the exam and then to review the questions.  There was also plenty of time to take both surveys.  I am pleased to announce that I scored somewhere between 700 and 1000.  Most of the questions that I missed came in the security topic, so I will be working on that topic a bit more.

Last quip for this exam, I thought it was too basic.  Take that how you will with my prior comments.  It really seemed more entry level and that is probably an accurate level for this particular exam.  If you have been working with SQL Server 2008 for a couple of years, you should be able to pass this exam – my opinion.  Some of my sentiments on this match what Jack Corbett said when he passed the same exam.  You can read his comments here.

Now on to 70-433 and then the MCITP equivalents (pre-reqs first then the MCITP stuff).


Categories: News, Professional, SSC
Comments: 4 Comments
Published on: May 19, 2011

Last year (and this year too) I had set the goal to renew my certification.  I failed miserably at it last year with the exception that I at least bought the vouchers and did a little bit of studying.  I never really set some time aside to achieve that goal.  Things are a bit different this year.

I am finally taking that plunge.  I have scheduled my first exam (70-432) for Friday morning.  If things go as planned, I will be taking 70-433 next week.  I am currently studying for the 70-433 exam because I feel comfortable with the 70-432 material.

One thing I don’t like about the process is availability of testing sites.  Years ago when I did my first round of certifications on SQL 2000, there were tons of testing centers.  Now, there are only four such centers in all of Utah – none of them within an hours drive of me.

DBA VC Presentation May 11

Categories: News, Professional, SSC
Comments: No Comments
Published on: May 11, 2011

Come one, come all!!  Oh wait, it’s too late now.  I just finished giving my presentation to the DBA VC (virtual chapter).  There was good attendance and there were good questions asked.  My presentation was the very same presentation that I gave for SQLSat83 (sheepishly: I even used the same slidedeck).

The presentation was recorded and I will post the link for that when it becomes available.  There was one goofup on my part.  I went on for about 5 minutes without the shared application having actually switched – D’OH.  My sincerest apologies for that.

During the presentation I made references to a few resources that I felt should be noted somewhere since I didn’t provide the exact resource during the presentation.

  1. Paul Randal myth-a-day concerning heap rebuilds.
  2. Kimberly Tripp’s information on VLFs.
  3. 3rd party tool for documenting a database quickly – called SQLSpec.  (it’s fairly inexpensive)

I really enjoyed giving this presentation and sharing my yet-to-be finished basement with everybody.  I hope to be doing more of these over time.

May 2011 S3OLV Meeting Reminder

Categories: News, Professional, SSC
Comments: No Comments
Published on: May 10, 2011

This is a quick reminder of the upcoming May meeting for the Las Vegas SQL Server Users Group.  We will be meeting on May 12, 2011 at the usual time and at the usual place.

We would really like to get a gaggle of database professionals out to support the group and the speaker this month.  Erika Bakse will be treating us to a slick presentation on MDX.  You can see the original info on this upcoming meeting here.  Time, date, meeting info, abstract, etc is all available in that original post.

My Public Events RoundUp

Categories: News, Professional, SSC
Comments: No Comments
Published on: May 10, 2011

As a part of my careers goals, I am trying to remain involved in the community through various engagements and events.  Recently, I was able to present to the South Africa Johannesburg group as a part of SQL Saturday 83.  I posted an announcement on that opportunity here.

I wanted to briefly cover my experience with that presentation.  I was quite pleased with the presentation and know that I could have done better with it.  That is where practice comes into play with each presentation.  One area of concern was trying to invoke group participation.  The group in JHB participated quite well – considerably better than the last time I presented this particular presentation.  I don’t think the book giveaways had much to do with it since I didn’t even mention that.  Also, they were participating well before we did the first book question.  I might have been a bit punchy during the preso – lack of sleep and sinus meds might have contributed.

I would like to thank everybody in JHB for a great job.  It was nice having a panorama camera available so I could see the group too.  That was very helpful.  I have not heard back yet if the recording is going to be made publicly available.  If so, I will pass it along so more of you can poke fun at me. 😉

This week, I will be giving that very same presentation to the DBA Virtual Chapter.  That presentation will be Wednesday May 11, 2011 at 12PM Mountain Time.  Since this is a virtual chapter, that means the presentation will be done via livemeeting.  If you are interested, you can register and attend the meeting via these links.



I hope to see a few people on for this presentation.  It is a low-level kind of presentation and is aimed at creating useful documentation with sql scripts (thus helping reduce cost).

T-SQL Tuesday #18 – CTEs

Comments: 3 Comments
Published on: May 10, 2011

To CTE or not to CTE, that is the Question

So my post really has nothing to do with the title.  This is not a post to help you determine whether a CTE is appropriate or not.  Or is it?

This month, we have the 18th installment in the TSQLTuesday series.  We are being hosted by Bob Pusateri  (Blog|@SQLBob) this month.  The essence of the topic this month is around CTEs (common table expressions).  There are a great many uses for a CTE in SQL server and this was a nice addition to the product.

One thing I like about CTEs is how much cleaner the code looks to me.  Another benefit for me is the recursion that is available through the use of a CTE.  An observation about CTEs is that a common use would be to use them to replace inline derived tables (which lends to cleaner looking code for some).

Think Think Think

I gave this topic a good long thought.  As I thought about the topic, I came to the conclusion that I had nothing new or unique on the subject.  I did however have some scripts that I posted once upon a time that would work very well for this topic.  Though it is a bit of a cop out, it is an appropriate solution for this month.

In the case of the CTE that I have chosen, there are multiple CTEs being used.  I use the CTE to recurse through data, and then to recurse that same data again – in reverse.  This particular script was created to traverse through system catalogs and create a hierarchy of table relationships.  I use this hierarchy to better understand the structure of the database and the interrelationship of the data between objects.  It is a cheap way of mapping out the objects in an effort to better understand it.

I had thought about using this script once upon a time for a different TSQLTuesday, but thought better of it that time.  Since the original post is more than a year old, it is a good time to bring it up and use it again.  Without further adieu, you can read about that script and CTE here.  I hope you enjoy.

May 2011 S3OLV Meeting

Categories: News, Professional, SSC
Tags: ,
Comments: No Comments
Published on: May 3, 2011

After a whirlwind of last minute changes last month, we expect stability this month with the monthly meeting.

Meeting Time is 6:30PM PST

Erika Bakse (blog | twitter) has been gracious to us by allowing us to pre-empt her last month and then to come back and speak to us this month.

Erika will be presenting on Clever Queries: Crafting MDX Queries to get the Most out of SSRS.

This meeting will be available via LiveMeeting and in Person at The Learning Center.  Erika will be presenting live at The Learning Center.

Reporting Services is a powerful tool that can make designing reports a snap…most of the time. But every once in a while you have to format a report very specifically, and the data just isn’t available in the form you need it to do that.  Enter Clever Queries!  Learn about how to use named sets, dummy members, and other MDX tricks to craft your data query in a way that allows you to conquer the trickiest report layouts.

LiveMeeting Details:
You can use this link to attend via LiveMeeting.

Meeting ID: PFD388

The meeting will be recorded and made available for later viewing as well.

Meme Monday – “I got 99 problems but a disk ain’t one”

Categories: Meme Monday, News, Professional, SSC
Tags: ,
Comments: No Comments
Published on: May 2, 2011

Today is Meme Monday and is a little thing that Thomas LaRock (Blog | Twitter)  has started.  Today he has started things off with a list of things that could cause a Database to go “wrong” that are not always disk related.

He has tagged a few people to add to the list in an effort to get up to 99 things that are unique that could cause failures in the database.  I haven’t been tagged, but wanted to throw out a few of my thoughts to add to the list.

  1. invoices – missing, unpaid, or wrong altogether
  2. vendors (misinformed, ship wrong items)
  3. undefined requirements
  4. bad documentation
  5. rampant use of sa
  6. no backups at all (full, log, or otherwise)
  7. very wide clustered indexes (i.e. three guids make up the PK and Clustered Index)
  8. P2V of a VLDB (not always bad but can be disastrous)
  9. Catch-all queries

These are some of the things I have seen cause some database problems.  Sure some of these things are more encompassing than just the database (like a vendor shipping the wrong part), but they can have a huge impact on the database.

page 1 of 1

May 2011
« Apr   Jun »


Welcome , today is Sunday, March 26, 2017