SQL SERVER Cloud Computing and me – TJay Belt delivers the best look at cloud computing that I’ve read yet. Some interesting affects of Table Partitioning – Kent Tegels discusses some interest side effects of partitioning tables and indexes. The N pillars of a well built database? – Louis Davidson is in the process of re-working his book and is thinking out loud about what makes for good database design.

I stole this from TJay Belt. It seemed like a nice change of pace from the SQL shenanigans to 1. Who was your FIRST prom date? Didn’t go to prom (they don’t have them in Scotland), but I went to my high school dance with a girl named Ashley. 2. Do you still talk to your FIRST love? Sometimes, but it’s only via the internet. 3. What was your FIRST alcoholic drink?

SQL SERVER Amazon Launches Public Data Sets To Ease Research – Michael Arrington (via TechCrunch) outlines the new data sets that Amazon is bringing to the public. This is monumental for a couple of reasons. This data is going to be freely available to anyone and everyone. This alone will add considerable value to many existing applications. In addition, this gives people interested in BI a sufficiently large corpus of data to start learning about building dimensions outside of entry level tutorials.

Things I found interesting this week (only two days late). GENERAL The Leonardo da Vinci Guide To Being A Renaissance Man – Dumb Little Man is always good for helpful tips, but this is a great guide to tapping into your natural curiosity and drive and taking it to the next level. I have to admit that’s a good part of how I’ve gone from a Bachelor’s degree in English to where I am now in only 8 years.

Before diving into how to go about using a common table expression, let’s take a look at what a common table expression is and why you would want to use one. The What and Why of Common Table Expressions Essentially, a common table expression (CTE) is a temporary result set. In this regard, a CTE is similar to a view, temporary table, or derived table. There are some important ways that a CTE is different from a view, temporary table, or a dervied table: CTEs are not persisted, views are.

Sometimes you need a list of months between two dates. Admittedly, this is another case where a calendar table would come in VERY handy. Unfortunately, I have not been able to build one yet. In case you’re in a similar situation, here’s how you could go about doing this:``` CREATE FUNCTION dbo.GetMonthList ( @StartDate DATETIME, @EndDate DATETIME ) RETURNS @months TABLE ( [month] DATETIME ) WITH EXECUTE AS OWNER AS BEGIN /******************************************************************************

SQL SERVER Anatomy of a Deadlock – Jonathan Kehayias outlines how deadlocks can occur, even in scenarios where they seemingly shouldn’t occur. I answered a question about this several weeks ago, but I couldn’t determine what the underlying cause of the deadlock was, I did not realize at the time that a SELECT would issue a Shared with Intent Exclusive lock on the table. (More on lock modes) Showplan error – Tom LaRock delves into an execution plan oddity: Are you a member of sysadmin?

How many of you have had to load a massive quantity of data from other tables in the same database? How many of you have manage to fill up TEMPDB in the process? I encountered both of these scenarios yesterday. 31 million rows is not a tiny amount of data, by any stretch of the imagination. When I attempted to run my query (which worked a few weeks ago with a partial set of data), everything worked for about 17 minutes and then errored out with a message that TEMPDB was unable to allocate any additional space.

GENERAL 7 Steps to Zap Your Creativity – Dumb Little Man shares tips for giving your creativity a quick pick me up. Some of it might be common sense, but it’s always good to get a refresher on what you can be doing to be more creative. Regardless of profession, creativity is a vital part of doing a great job. Microsoft BizSpark Program – BizSpark is a program intended to help startups get off the ground using a Microsoft platform for their business needs.

Just a quick post with two T-SQL functions to compute the first and last day of the month for any DATETIME. These have been incredibly useful on a daily basis and I figured that I would pass them along. Keep in mind that these are inline scalar-value functions and will be evaluated for every row in a result set. This isn’t so bad when you only have a few rows, but when you have a million rows, it’s expensive.