January 2009

Weekly Link Roundup 2009-01-28

Stuff I’ve been reading:

Lots and lots of stuff on the digital tv transition:  This is fascinating to me; it’s a perfect example of the stupidity of federal government.  They sold the bandwidth, so everyone needs to upgrade.  They set a date.  We have a change in exeuctive administration (but not in legislative), and suddenly the date’s no good anymore.  I wonder how many lawsuits will be filed by the companies that will technically own the airwaves past the transition date if it gets delayed?

I’ve gone back to the newsgroups:  I used to post quite frequently in microsoft.public.sqlserver.programming (and a few others), but got tired of the same old questions, and many of the same old responses.  However, since SQL Server 2008 has been released, there’s some fresh meat in there.

Notes from SQLSaturday #10 in Tampa:  I’ll admit it; I’m starting to get nervous about our own upcoming SQLSaturday.  Andy Warren’s writeup of the recent SQLSaturday just goes to remind me of how much we’ve got left to do.  Speaking of Andy, he’s also posted his PASS Updates: 1, 2, 3

Happy coding.

Building ranges using a dynamically generated numbers table

We use a queueing metaphor for dataloads for the majority of our imports, and it’s been a challenge to figure out how to generate dynamic ranges to add to the queue. In other words, we want to tell our system to go and pcik data from a source that has an id value >x and <=y, and then do it again in a few minutes with a new x and y value. I should say that it’s been a challenge because I’m still not used to SQL 2005 features; I came across a bit of code using a CTE and recursion to generate a numbers table (thanks to Stefan Keir Gordon). Here’s a slightly modified version of his code:

/*thanks to Stefan Keir Gordon for the inspiration
 http://www.keirgordon.com*/

SET STATISTICS IO ON

;WITH Numbers(n) AS
   (SELECT 1 AS n
	UNION ALL
	SELECT (n + 1) AS n
	FROM Numbers
	WHERE n <= 32767)
SELECT n
FROM Numbers
WHERE n<=32767
OPTION(MAXRECURSION 32767)
In case you’re wondering, 32767 is the maximum number of recursions supported by SQL Server in a CTE; Stefan’s code neatly generates a single column table with values from 1 to 32767. Looking at that, I realized I could easily get a simple range by adding a second column to the final SELECT, like so:
 ;WITH Ranges(MinValue) AS
  (SELECT 1 AS MinValue
   UNION ALL
   SELECT (Minvalue + 1) AS MinValue
   FROM Ranges WHERE MinValue <= 32767)
SELECT MinValue, MinValue + 1 as MaxValue
FROM Ranges
WHERE MinValue <= 32767
OPTION(MAXRECURSION 32767)

I end up with a two-column table with a MaxValue > MinValue, and the ranges increase by 1. But I had some issues to address:

  • I needed to be able to pass in Start and End values to this code.
  • I needed chunks bigger than 1 value in size; I also had a minimum size I wanted to use.
  • In some cases 32767 ranges would be appropriate; in some cases, I needed less.

Here’s the final code; setting the Start and End valeus for the range of ranges is easy to understand, but the other two values may be a bit confusing. The first is @MaxRanges, which simply says “I want no more than this number of ranges to get from the Start to the End”. The maximum value for this number is 32767. @MinChunk refers to the minumum chunk size, or the space between MinValue and MaxValue for a given row.

It may be easier to just play with the values and see; you should never have more rows than @MaxValue, and the difference between MinValue and MaxValue for a given row should always be greater than or equal to @MinChunk:

/*A dynamically generated table of ranges.
2009-01-25 Stuart R Ainsworth
	http://codegumbo.com/

Thanks to Stefan Keir Gordon for the inspiration
 http://www.keirgordon.com */

SET STATISTICS IO ON

DECLARE @MaxRanges int, @MinChunk int, @Start int, @End int
SELECT @Start=10, @End=200000

/*@MaxRanges must be <= 32767;
  if the range between Start and End is large,
  and @MaxRanges > 32767, the End point won't be reached.*/
SELECT @MaxRanges=1000, @MinChunk=205

IF (SELECT CEILING((@End-@Start)/(@MaxRanges*1.00)))>@MinChunk
	SET @MinChunk = CEILING((@End-@Start)/(@MaxRanges*1.00))

;WITH Ranges(MinValue) AS
  (SELECT @Start AS MinValue
   UNION ALL
   SELECT (Minvalue + @MinChunk) AS MinValue
   FROM Ranges WHERE MinValue < @End)
SELECT MinValue, MaxValue = CASE WHEN MinValue + @MinChunk < @End
			     THEN MinValue + @MinChunk
			     ELSE @End End
FROM Ranges
WHERE MinValue < @End
OPTION(MAXRECURSION 32767)

Good luck; happy coding!

Let’s all go to the movies….

I just got back from watching Gran Torino with the wife; if you haven’t seen this film yet, it’s frikkin’ awesome!  Warning, this movie definitely warrants the R rating it carries.  It’s both funny and sad, and speaks a lot about vengeance, growth, and personal retribution.  It’s kind of a mature Karate Kid.  The only sad thing is that I can’t repeat any of the fiercely comedic lines BECAUSE THEY ARE SO AMAZINGLY OFFENSIVE!    Seriously, drop one of these lines in the wrong crowd, and you could lose some teeth.

Anyway, if you’ve got a strong stomach and love to see old men with attitude, go watch this film.  4 out of 5 stars.

Weekly Link Roundup 2009-01-21

Stuff I’ve been reading:

Causing a stir in Tok Alaska: I first started reading this blog after reading about the -78 degree weather in Alaska on the Drudge Report. The writer is quite talented, and it’s a lot of fun to read.

Wordle:  Technically, this ain’t reading.  I found out about Wordle from Brent’s blog, and thought it was a cool exercise in computer-generated art.  Below is the wordle for my site, generated today.

Joe Celko’s Temporal Data Techniques in SQL:  I’ve said some pretty harsh things about Joe Celko in the past; his online persona is, frankly, obnoxious, and I’ve disagreed with him on many of his approaches to programming in SQL Server.  However, he’s very intelligent, and it’s hard to argue with him when it comes to pure design.

Tony Davis’s Bar-Poo for DBA’s & Developers: Tony writes some esoteric stuff, and this particular post is no exception.  But hey, I won a prize!

Happy coding!

catching up…

I’m starting to fall back into a bad habit of not posting regularly; I’ve already missed a couple of weekly updates, and I haven’t shown off much coding lately.  I wish I could blame it on a lack of time, but the truth is that I’m lazy.  Even though I’ve been keeping busy, I still have plenty of free time.  I’ve just been frittering it away on television.  However, I’ve decided I’m going to take some advice from a fellow blogger: if you can’t keep a committment to two posts a week, commit to one.  Henceforth, I’ll be posting at least one post a week on Wednesday nights.

Now that I’ve got that out of the way, here’s the breakdown of what’s been up with me since my last post:

  • Judged a speech contest at the local community college,
  • Started our latest sprint in the scrum method at work,
  • Finished the sponsor letter for SQLSaturday,
  • made several appeals for volunteers and speakers for SQLSaturday,
  • finally took down my Christmas lights, and
  • cleaned my home office.

I’m pooped just making the list.  🙂

AtlantaMDF meeting 2009-01-12

Wanted to drop a quick post wrapping up tonight’s usergroup meeting for AtlantaMDF.  Tonight was the first chance I’ve had to pitch SQLSaturday #13 to a larger audience, and I got a lot of positive feedback.  I had several people volunteer to help, and even had two or three people volunteer to present (not including those who were interested in presenting in my two super-short sessions).  I’m hoping this is going to be killer; I’ve got a lot of legwork to do to get sponsors lined up, but we’re on our way!

There were a lot of new faces tonight; the cynical side of me wants to point to the economy.  A lot of people are either looking for work, or want to make sure that they still have work in the next year.  User groups provide both free education, and free contacts, and you can’t fault someone for doing what they can to improve themselves and make sure that their opportunites are lined up.  However, I think a lot of people are finally starting to realize that SQL Server is a great platform for database development, and the best way they can learn about the technology is to participate in the user group.

Tonight, Eric Melcher of Intellinet gave a great presentation on End-To-End BI.  If you weren’t there, you may want to download it from the AtlantaMDF site when he publishes it; I think the title was a little misleading, but the content was great.  Eric really focused on the client tools available for analysis, including everything from Excel to Performance Point, touching on Excel Web Services, Proclarity, and Reporting Services along the way.  If you’re like me, and you feel overwhelmed by what BI is, and what options are available to display data, this was a great presentation to get your feet wet.

Happy New Year!

So, sitting up and watching Double Impact and cruising the web, I read Andy Warren’s Thoughts for 2009, and I thought I should probably contribute my own ideas as well.  I definitely want to spend more time blogging and contributing to the user groups at large.  Obviously, I’m going to be tied up with SQL Saturday for the first part of the year (speaking of which, I need to get my sponsor letter organized), but I still want to do more. 

Professionally, I need to find more leadership opportunities in my company.  This year’s a bit of a challenge because we’re understaffed with a lot of projects coming down the pipe, and there’s not a lot of openings in management positions, BUT I need to find a way to make my name known.  I want to be the SQL guy for not just my division, but I also want to expand beyond that to the company level as well.

I want to complete my MCITP certs for database administration and development this year.  One thing I learned at PASS Summit was that I need to continue to find ways to expand my knowledge.  I feel very confident in my ability to code T-SQL and design elegant database solutions, but I have 0 experience in Microsoft’s BI solution, and my DBA skills are a bit rusty.

I like Andy’s suggestion to play chess; I’m going to open that up and say I want to play more games with my kids.  They’re getting older, and soon they’ll be too old to hang out with me.  I want to find ways to encourage them to think, and I want to spend time just having fun with them.

Finally, I’m going to get healthy.  As soon as this shoulder recuperates from surgery, I’m back on the workout routine.  I am going to find 30 minutes a day to work out, and I’m going to stick to it.

How about you?