Invitation and roundup from Sebastian Meine.
As many of you know already, I decided to declare December 2012 as the month of the JOIN by writing the A Join A Day blog series. So, it should be quite obvious what this month T-SQL Tuesday is about. You guessed correctly — I would like you to join me in talking about joins. Now you might think “If Sebastian is writing 31 posts about JOINs already there is nothing left to write about.” But I can assure you that there is still plenty out there. In my series I am going to cover just the basics. For example, what is a cross join or an anti-semi-join? What is the difference between a hash and a merge join? There are many things I won’t be able to cover, for example how to write efficient join queries.
Your mission – should you accept – is to write about topics like the good and the bad patterns of joining you have seen out there or really anything else that comes to your mind when thinking about joins:
- Have you had to deal with a slow monster join that you were able to conquer? Let us know how you did it.
- Have you noticed a join pattern in use that is really not good for readability but you come across it time and time again? Tell us how to do it better.
- Have you discovered a really cool way of using the APPLY command instead of a JOIN to force the execution engine to utilize the existing CPU resources more effectively? We would like to hear about it.
And if you have a topic that you always wanted to write about but that is only remotely related to joins, feel free to use it anyway and make sure to tell us why you think it is related to joins.
Hope to see you (or at least your post) next week at the party.
Invitation and roundup from Mike Fal.
For a while, I was in an amateur pool league. No, not the one involving water and swimming, but where you try to sink balls into pockets. It was a lot of fun and is a challenge both for your motor skills as well as your strategy. I still shoot from time to time, as well as hang out with my old pool buddies.
One thing guys would get into is trick shots. Two and three rail bank shots, masse shots, or jumping the cue ball to hit the target. Most of these shots weren’t tournament legal, but they were fun to try and nice to impress the ladies. More than that, they were a tool to teach you the physics of your pool game. You could see how throw and English could affect your shot, or how balls would behave after impact.
Just like so many other things I do in my life, the trick shot lessons translate over to SQL Server. How many times have we built something neat or puzzled out a particular bit of logic that, while it may not have been particularly useful, taught us about how SQL Server behaves. This month’s T-SQL Tuesday is all about this and the assignment is two-fold:
- Show us a cool trick or process you developed, maybe a DMV you used or some reporting logic you created. It doesn’t have to be useful, just something that you thought was pretty neat.
- Tell us what you learned from this trick. Is it something about an oddity in SSRS? Maybe with the query processor? Whatever you did, tell us how it gave you insight in to how SQL Server works.
Invitation and followup from Allen White
It doesn’t seem that long ago that having cool little tidbits of information about SQL Server made a huge difference in how effective you could be. Well, that’s still true, but let me give you an example.
SELECT name FROM sysobjects WHERE sysstat & 4 > 0
In the early days of SQL Server, this was the way to pull a list of the names of all the stored procedures in your database. The 4 bit in the sysstat column represented stored procedures. (1 represented user tables and 2 represented view, as I recall, so changing the WHERE clause to read WHERE sysstat & 7 > 0 returned all tables, views and stored procedures.)
As SQL Server has evolved, Microsoft has made it easier to query the metadata to determine what objects existed, adding columns that helped (like ‘Type’ in this case), catalog views, Dynamic Management Objects, etc.
So, the challenge for this month’s T-SQL Tuesday is: What T-SQL tricks do you use today to make your job easier? (Notice I didn’t say PowerShell – I have a bunch of those now, but this is T-SQL Tuesday, not PowerShell Tuesday.)
Invitation and roundup from Brad Shulz.
You are hereby invited to this month’s T-SQL Tuesday #024, which will take place on November 8, 2011.
So, all you T-SQL Bloggers out there, please join the blog party and write up something revolving around this month’s topic: Prox ‘n’ Funx (which is just a coo-ul way of referring to Procedures and Functions).
This topic covers a lot of ground, so there’s a myriad of possibilities in what you can write about. You could discuss a really cool stored procedure or function that you wrote. You could write about a Dynamic Management Function that you can’t live without… or perhaps write about some of the new functions that are coming in SQL2012. How about limitations or “gotchas” or performance issues in working with procedures and functions? And on and on and on…
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.
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?
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!
Invitation and wrapup from Bob Pusateri.
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.”
Site list, but invitation below from Matt Velic.
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!
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.”