T-SQL Tuesday #023 – Joins

Invitation and roundup from Stuart Ainsworth.

Topic d’jour?  JOINS (I’m in a fundamentals mood lately).  Note that I also like creative and esoteric posts, so if you can find a way to apply SQL as a metaphorical language for community activity, I’ll read it and enjoy it.  If you just want to tell me in a simple fashion the difference between a HASH and MERGE join, I’m cool with that, too.

T-SQL Tuesday #022 – Data Presentation

Invitation and round-up from Robert Pearl.

On to this month’s Topic

What I have described above may be some behind-the-scenes details, but nevertheless, I packaged it in such a way that would provide entertainment (maybe ridicule?) to the reader at large.  This was my presentation to the reader.

Therefore, the topic of this month’s T-SQL Tuesday is, “data-presentation” Or put better, formatting data for presentation to the end-user.

We may be the developers, and techno-geeks behind the code, whether simple, advanced, spaghetti, or otherwise.  But, the data the user sees is most critical.  The query output, the report, or data presentation, must be absolutely formatted in such a way that is easily understandable and readable by the end-user.  The end-user can be the boss, supervisor, department head, the analyst, employees, or customers.  And they must be the ones we cater our queries to!

It helps a lot, if we can simplify our code too.    For example, when doing comparative analysis of the dataset results returned by a query, it makes it completely understandable if the output includes a percentage column.  While for the end-user, it may be hard to digest milliseconds, megabytes, totals, and other assorted aggregated data, everyone can easily comprehend when something is X% percentage out of the whole.

For example, with the advent of Common Table Expressions (CTE), this makes it a whole lot easier to return all the data rows, along with the percentage in one single T-SQL pass.

Therefore, I am inviting you all to write about “data presentation” to the user.   This can be in the form of T-SQL code, an SSRS report, etc.  What can you do to streamline data presentation?  I used a CTE, you can use one, but you don’t have to.  No hard format, just be creative, and mention the importance of data presentation.

T-SQL Tuesday #021 – A Day Late and Totally Full Of It.

Invitation from Adam Machanic.

“This ugly hack is only temporary,” you think. Six months later, a coworker curses your name, sacrificing a chicken to any deity that will help expedite your getting struck down by lightning, a school bus, or both.

Crap code. We’ve all seen it. We’ve all created it. We’re all guilty. Yes, even you. Sometimes our crap is purposeful—the ugly, “temporary” hack. Sometimes we produce crap because we simply don’t know any better. But there is no excuse good enough. As professionals, we must strive to rid ourselves of bad habits. And the only way to learn the difference is to see lots, and lots, and lots of examples.

That’s where you come in. This month’s T-SQL Tuesday challenge: reveal your crap to the world. Why is (or was) it crap? Why did you do it? And how did you learn from your mistake?

T-SQL Tuesday #020 – T-SQL Best Practices

Invitation and summary from Amit Banerjee.

What is the topic for July?

The topic for July is “T-SQL Best Practices”. If you work with SQL Server, then undoubtedly you would have had to write T-SQL queries atleast once or would have had to debug the seemingly useful piece of T-SQL code written by your developers to find out where the performance bottleneck or problem was. Your post for this month’s revolving blog party could be along one of the areas:

a. A set of T-SQL best practices that you follow in your shop that or you believe that should be followed always. It could be as specific as for writing only linked server queries or writing queries for SSIS packages etc.
b. An issue that you resolved because certain T-SQL best practices were not followed.
c. A workaround that you used (like query hints) to resolve an issue where T-SQL best practices couldn’t be implemented due to involvement of a third party solution.

Why did I choose this topic?

Over the years of troubleshooting SQL performance related issues, I have found on multiple occasions that the T-SQL query in question was performing badly because certain best practices for writing that piece of code were not followed and the one responsible for the development had not foreseen that such an oversight could become a bottleneck when the data or the number of users increased. So, I thought it would be a good idea to get the SQL Community’s thoughts around best practices in this area. Sometimes, the most obvious things are the easiest to overlook!

T-SQL Tuesday #019 – Disasters and Recovery

Invitation and wrapup from Allen Kinsel.

Disasters

Its the first week of June and for those of us living along the Gulf and Atlantic coasts of the US, that brings the beginning of hurricane season.  It also means its time for this months installment of T-SQL Tuesday.

This Months Topic

Hurricane Ike dead ahead

There goes your weekend/month

Disaster Recovery.  This topic is very near and dear to me based on the fact that I live on a barrier island that was the site to the deadliest natural disaster in US history and more recently destroyed by the third costliest hurricane in history.  Needless to say preparing for disasters is nearly instinctive to me which might explain why I’m a DBA but I digress.  Anything you’d like to blog about related to preparing for or recovering from a disaster would be fair game, have a great tip you use to keep backups and recovers running smoothly, a horrific story of recovery gone wrong? or anything else related to keeping your systems online during calamity.  We want to hear it!

T-SQL Tuesday #018 – CTEs

Invitation and wrapup from Bob Pusateri.

The Topic

This month’s topic is CTEs, or Common Table Expressions. Had you asked me 10 years ago what CTE meant, I would have replied “coefficient of thermal expansion” but that was back in my semiconductor & electronic materials phase. I like the database version much better 🙂

Have you ever solved or created a problem by using CTEs? Got a tip, trick, or something nifty to share? I’d love to see your posts about any of the above. Also don’t forget that T-SQL Tuesday is not limited to only T-SQL:

“Any post that is related to both SQL Server and the theme is fair game. So feel free to post about SSIS, SSRS, Java integration, or whatever other technologies you’re working with in conjunction with SQL Server. Even if your post includes no T-SQL we still want to see it.”

T-SQL Tuesday #017 – APPLY Knowledge

Site list, but invitation below from Matt Velic.

APPLY Knowledge

Recently on Twitter, I heard the claim that “If you don’t understand the APPLY operator, your skills are somewhere around the 50th percentile at best.” While I believe that Adam was giving a warning to self-proclaimed experts (possibly one he might have been interviewing at the time…), I also believe that we could take it as a challenge as a T-SQL blogging community to learn more about APPLY and the ways in which we can use it in our work.

Please share how you use this wonderful feature. Maybe you know how APPLY works inside and out? Perhaps you’ve got a fantastic user defined function (UDF) to share? Or maybe your experience revolves around using Dynamic Management Functions (DMFs) in your never-ending quest for SQL Server performance? Let the community know as it is time to study!

T-SQL Tuesday #016 – Aggregate Functions

Invitation and SUM() from Jes Borland.

The Topic: Aggregation

No, not aggravation (although I’ve used the two interchangably before). I want to hear how you solved business problems with aggregate functions. I want to see your cool T-SQL tricks. How are aggregates used in SSRS, SSAS, or SSIS? What have you learned about aggregate functions?

Let’s SUM(thoughts), COUNT(ways we’ve done things), and set MAX(awesome) on our posts!

Remember: “any post that is related to both SQL Server and the theme is fair game. So feel free to post about SSIS, SSRS, Java integration, or whatever other technologies you’re working with in conjunction with SQL Server. Even if your post includes no T-SQL we still want to see it.”

T-SQL Tuesday #015 – Automation in SQL Server

Invitation and Summary from Pat Wright.

Having taken part in several T-SQL Tuesday’s I decided I would finally put my name in to host one.  I figured it would be a good way to lose my sanity learn some great ideas from this wonderful SQL community.  I figured that since many of you out there set a goal this year to blog more and to learn Powershell then this Topic should help in both of those goals.    So the topic I have chosen for this month is Automation!   It can be Automation with T-SQL or with Powershell or a mix of both.  Give us your best tips/tricks and ideas for making our lives easier through Automation.  Now here are all the details you’ll need for a successful T-SQL Tuesday post!