Tag: t-sql

Invitation this month from Matthew McGiffen.

A few years back I started running regular SQL workshops in my workplace. Teaching beginners the basics of querying databases with SQL, as well as more advanced topics for the more advanced.

During one session we were discussing the issue of knowledge acquired being quickly lost when people didn’t get the chance to regularly practice what they’d learnt. One of the attendees suggested that I should be assigning them homework.

I could see from the faces of everyone else present that the word “homework” struck an unpleasant chord. Perhaps reminding them of school days struggling to get boring bookwork done when they’d rather be at relaxation or play.

Okay, so homework maybe wasn’t going to go down well, but I figured everyone likes a good puzzle. So every Friday I started creating and sharing a puzzle to be solved using SQL. This went on for the best part of a year, then other things got in the way and gradually I stopped.

This is my invitation to you this T-SQL Tuesday. Write a blog post combining puzzles and T-SQL. There’s quite a few ways you could approach this, so hopefully no-one needs be left out for lack of ideas:

• Present a puzzle to be solved in SQL and challenge your readers to solve it.
• Or give us a puzzle or quiz about SQL or databases.
• Show the SQL solution to a classic puzzle or game.
• Provide a method for solving a classic sort of querying puzzle people face.
• Show how newer features in SQL can be used to solve old puzzles in new ways.
• Tell us about a time you solved a problem or overcame a technical challenge that was a real puzzle.
• Or just make your own interpretation of “puzzle” and go for it!

There’s some great stuff out there already. Itzik Ben-Gan’s done a bunch of them. There’s Kenneth Fisher’s crosswords. The SQL Server Central questions of the day. Pinal Dave’s SQL Puzzles. And there’s a few on my blog too if you take a look back:

SQL Puzzle 1: Magic Squares

SQL Puzzle 2: Eight Queens

SQL Puzzle 3: Knights and Queens

SQL Puzzle 4: The Beale Papers

SQL Puzzle 5: Prime Magic

Let’s puzzle together, trying to solve the challenges each other sets, and make it a real puzzle party!

Have fun all

Invitation from Steve Jones

Triggers, for fun and frustration

I’ve been working with SQL Server and T-SQL a long time, and across many jobs, I think I’ve ended up using triggers in 0.01% of my tables or less. They can be a useful and helpful construct, but they can also be problematic and difficult, especially in the age of changing business models and rules.

Since I’ve found triggers to be both helpful and hurtful, I decided to ask you to write about an experience you’ve had with triggers. Either good or bad, but let me know this month what stands out in your mind.

Invitation and Roundup from Bert Wagner. The next invitation should be released on August 7.

Code You Would Hate To Live Without

Before modern online programming communities, finding good code samples or sharing your own code was challenging. Forums and email lists (if searchable) were good, but beyond that you had to rely on books, coworkers, and maybe a local meetup of like-minded individuals to help you work through your programming problems.

Check out this month’s T-SQL Tuesday invitation in visual form!

Today, accessing and using code from the internet is second nature – I almost always first look online to see if a good solution already exists.  At the very least, searching blogs, GitHub, and StackOverflow for existing code is a great way to generate ideas.

For this month’s T-SQL Tuesday, I want you to write about code you’ve written that you would hate to live without.

Maybe you built a maintenance script to free up disk space, wrote a query to gather system stats for monitoring, or coded some PowerShell to clean up string data.  Your work doesn’t need to be completely original either – maybe you’ve improved the code in some open source project to better solve the problem for your particular situation.

There’s probably someone out there in the world who is experiencing the same problem that you have already solved; let’s make their life a little easier by sharing.

And don’t worry if your code isn’t perfect – just explain how your solution works and if you are aware of any caveats.  If it’s not an exact solution for someone else’s problem, at the very least it may help them generate some ideas.

Invitation and roundup from Sebastian Meine.

The Invite

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 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?