Art of the DBA Rotating Header Image

August 20th, 2012:

T-SQL Tuesday #33 Wrap-up (#tsql2sday)

Last week I had the opportunity to host this month’s SQL blog party, T-SQL Tuesday.  I hope folks enjoyed the topic (it seemed like it), because the idea of experimenting with something is very much a core part of the learning process.  SQL Server is so deep and has a number of nooks and crannies.  To become proficient at it (let alone an “expert”), we have to keep pushing our boundaries and asking “What happens when….?”

I want to again thank everyone who participated.  Here’s a quick round up of the posts:

Rob Farley(b|t) peels back the cover on recursive Common Table Expressions (CTEs).  If you haven’t worked with them yet, CTEs are an incredibly useful tool in the SQL arsenal.  Using them recursively is just one example of how powerful they can be to a developer.

Jeffrey Verheul (b|t) chimes in with a big picture discussion on using cursors versus set based operations.  While we database folks prefer set based operations, sometimes it’s to our advantage to use a cursor.

Jim McLeod (b|t) jumps in with a great little way to use SQL Profiler to gain some insight into the workings of SQL Server by tracing Management Studio.  Not a lot of people are aware that almost everything that happens in Management Studio is a query that can be scripted out, and profiler lets us see that.

Oliver Asmus (b|t) shares some of his knowledge gained from putting together preventative maintenance tasks (a vital yet often overlooked part of being a DBA).

Aaron Bertrand (b|t) provides a cool trick to swap tables around using schemas.  A lot of SQL DBAs overlook the power of schemas in databases, but Aaron’s trick really gives as an efficient way to handle offline updates and other resource intensive tasks.

Nigel Sammy (b|t) pokes at the Transaction log with DBCC SQLPERF.  It’s important for DBAs to really understand their transaction log usage, and Nigel’s trick can give a DBA some real insight.

Tracy McKibben (b|t) gives us a trick using one of my favorite system views, sys.partitions.  There are times I lament not being able to see how long a query or process will take (I’d LOVE to have something like Oracle’s V$SESSION_LONGOPS view), but Tracy shows us how to use sys.partitions for insight into index rebuilds.

Chris Shaw (b|t) gives us some information on cluster failovers. While we want our operations to appear seamless to our users, as DBAs it’s important that we’re always aware of where our instances are living within our environment.

Rob Volk (b|t) really gets down the rabbit hole, using one of the Sysinternals tools to crack open SQL Server.  I’ve seen some folks do some really cool and powerful stuff with Sysinternals, so Rob’s post is a great way to introduce people to this awesome tool suite.  (Plus, how many guys even get to use the word “hekaton”?!?)

Jennifer Salvo (b|t) adds a little business intelligence to our topic, giving us a cool SSAS trick to manage currency conversions.  Businesses will ALWAYS care about their money, so having a good way to manage multiple currencies is a must.

Finally, first timer Jose Rivera (b|t) brings us full circle with his own post on using recursive CTEs to solve a tricky problem for drug costs.

(Oh, and look at this post if you want to see my contribution.  :D  )

I really appreciate everyone’s participation.  This is such a great part of the SQL Family and I hope that if you’re reading this you’ll consider participating and, perhaps, hosting a T-SQL Tuesday in the future.