Tuesday, December 13, 2011

T-SQL Tuesday #025 : Tricks of the Trade

This week's SQL Tuesday theme is "Tips and Tricks", which led me to start making an inventory of the tricks and tools I use almost daily as a SQL developer.

As an old(er) dog, many of the tricks I use are things I've learned over the past couple of decades, and as I document them, I'm finding that in many cases, there's new ways of doing them (DMO's, for example).  It's no excuse for not learning the new, better methods, but in my defense I'll just say that I have to support clients back to SQL 2000 (and occasionally even 7!) so I find that the old ways still work really well, even if newer versions introduce newer features.

First, some tweaks to the environment:

My first trick is the only one with a price tag.  I've been working with Redgate's SQL Developer Bundle all summer, and I can't imagine ever not using it for SQL development.  It's pricey, but I'd estimate that I work 50-100% faster with it.  If there's money in the budget, ask your boss to get you the best tools available.  I'll be happy to talk to your boss if you want a testimonial of how much productivity you can buy with these tools.

Next, color your connections!  SSMS will do this if you tell it to, but I prefer the coloring provided as part of the free SSMS Tools Pack.  Nothing reminds you that you're on the production server like a giant red bar at the edge of the query editor.  SSMS Tools Pack also automatically saves all your tabs periodically with a searchable history, gives you SQL snippets (similar to SQL Prompt's), helps analyze execution plans, and about ten other handy things, all free.

Finally, if you're staring at the query editor all day, why not try a soothing color scheme?  I really prefer a black background with light text.  Tomas Restrepo wrote a handy little tool for porting color themes between Visual Studio and SSMS.  I've found it handy to backup the following registry key after I set the colors I like:
HKEY_CURRENT_USER\Software\Microsoft\Microsoft SQL Server\90\Tools\Shell\FontAndColors
which makes it handy to switch between themes.  I use a dark background for development, and a white background for presentations.  There's lots of free themes out there at Studio Styl.es for you to try.  (Note: if you import a registry key with a new color scheme, you can implement the changes without starting SSMS by going to Tools/Options, touching Fonts and Colors, and hitting OK.)

Everyone's a DBA Sometimes:

Even though I'm usually in development mode, when things get slow, any database person will do.

Activity monitor will help you find blocking processes, but often when there's blocking, there's panic.  I keep this code in my snippets (or create a stored procedure and add it to SSMS's keyboard shortcuts):
SELECT * FROM master..sysprocesses WHERE blocked<>0 and blocked<>spid
SELECT * FROM master..sysprocesses WHERE blocked=0 and spid in
(select blocked from master..sysprocesses where blocked<>0 and spid<>blocked)
It immediately tells me if anyone's being blocked, and the head of the blocking chain.  It also works in every version of SQL.   DBCC INPUTBUFFER(spid) is usually not far behind.

Yes, you can do this from Activity Monitor and most tools have a way to find it relatively quickly, but having a snippet defined lets me run it with two keystrokes, immediately and repeatedly.

The other thing I setup in any environment is a snippet or stored procedure to gather information from a variety of application-specific logs table.  Being able to see the last ten errors from a few apps, the ETL processes, maybe even some realtime stats like number of sessions, is invaluable when things are going wrong.

Development Tricks


I've come to rely on Redgate's SQL Seach (it's free!) for finding anything in the database.  If I don't have access to it, I usually have this in a snippet for searching the text of stored procedures, triggers, etc:


Before I started using numbers tables (sometimes called "tally tables"), I thought my life was complete.  Now I use them for all kinds of things, particularly populating data.  Anytime I need a set of integers, my go-to set is:
SELECT number FROM master..spt_values WHERE type='p'
It contains integers 0-2047, and if you need bigger, you can join it to itself to get 4 million, 8 billion, etc.

You can check out the above links for ideas about how to use numbers tables, but for example if I need a table of days for the next five years or so, you can whip one up with:
SELECT DATEADD(d,Number,'1/1/12') as MyDate, Number%7+1 as DayOfWeek
FROM (SELECT Number FROM master..spt_values WHERE type='p') numbers 


Hardly a day goes by when I don't have to convert to or from a comma-delimited list, usually for passing sets around when table-valued parameters aren't an option.  FOR XML PATH is a quick way to get a list of values into a single, comma-delimited string:
SET @mylist=(SELECT TOP 10 ','+name FROM sysobjects WHERE type='p' FOR XML PATH(''))
SET @mylist=STUFF(@mylist,1,1,'')
SELECT @mylist
 You can use a numbers table to quickly split the delimited string back into its components.  (Also see Jeff Moden's definitive piece on tally-table splitters):
SET @mylist=','+@mylist+','
;WITH n AS (SELECT number FROM master..spt_values WHERE type='p' AND number < LEN(@mylist))
SELECT SUBSTRING(@mylist,number+1,CHARINDEX(',',@mylist,number+1)-number-1) FROM n WHERE SUBSTRING(@mylist,number,1)=','

Finally, hardly a day goes by when I don't need to get something random.  Although RAND() will get you a random number, that doesn't help if you want to generate a set of random numbers.  You can always use a loop or a cursor, but there's a better way.
will return a list of random numbers between 1 and 9.  This can be particularly handy for generating test data, particularly when combined with TOP() and CROSS APPLY (more on this in another post).

Of course, NEWID() also comes in handy for getting a random selection of existing rows.
will return a random set of 15 records every time it's run.

Social Tricks

If you're reading this blog, you probably know the most important trick: be connected!  There's a huge network of wicked-smaht, generous SQL experts out there, and more free blogs and videos out there than you'll ever have time for.  Here's some of my favorites:
  • Brent Ozar PLF's Weekly News letter is the best thing about Monday, and usually points me to a dozen cool things, SQL and not.
  • There's probably a SQL PASS chapter near you.  And if there's not, they have Virtual Chapters.  These are great way to learn about SQL topics, meet great SQL people, network, and much much more.  Check yours out!
  • SQL Saturdays are a great, free alternative to the incomparable PASS Summit.  Some pretty phenomenal speakers donate their time and wisdom to these, and again they're a great chance to meet fellow SQL geeks.  (Hint: many presenters also generously upload their presentation materials to the SQL Saturday sites.)
  •  SQL Bits, SQL Server Cental (particularly their Stairway series), and SQL Server Team Blogs are all great spots to go read and watch tons more great SQL content.
  • And of course, if you're not on Twitter, get on!  Almost all of the SQL MVP's, presenters, and even the SQL development team follow #sqlserver and #sqlhelp.  Brent Ozar even wrote a free book specifically for SQL Twitter virgins, so you have no excuse.  A typical Twitter experience: I tweeted thanks for some SQL feature I liked, and a few minutes later, the Microsoft engineer who wrote it tweeted back, "You're welcome!" How cool is that?

Tuesday, December 6, 2011

Can Your Network Do This?

A couple of months ago, my wife and I took a road trip from Cleveland to Pittsburgh.  Stuck in bumper to bumper traffic, the woman behind me suddenly lept from her car, ran up behind my car, and took a picture of my license plate.  She hollered something like "I love SQL!" and disappeared back into traffic.

It took three days for a friend to send this back to me in a tweet:

Aside from the cosmic coolness of having attractive, nerd-appreciating women accosting me in traffic, it occurred to me that this was an indication of a pretty good professional network.  Can your network do this?  How many degrees of Kevin Bacon are you from a random stranger on the freeway?

One of my personal and professional goals this year was to do more networking.  I don't suppose I'm alone in the tech world in putting "networking" right up there with "root canal" in the list of things I'd rather not be doing or having done to me. Images come to mind of schmoozing and making uncomfortable small talk, and telling people we should do lunch, or take lunch, or whatever verb it is people do to lunch while networking.

As it turns out, networking can largely be whatever you want it to be, so long as you're connecting to other people.  Yes, I've "done" a couple of really uncomfortable lunches with people trying to sell me something, or see if I could sell something for them.  But I've also been writing this blog, tweeting, drinking beer with other SQL geeks, and attending some interesting (for me) conferences.  Yes, it's still an effort-- I'm an introvert, and as much fun as I have hanging out with SQL people, I'd often much rather just be at home with my wife and cats.  But really, it hasn't been a chore-- just a conscious effort to get more involved in the SQL community.

And you can do it, even if you have major social anxiety.

Here's some things that I've found good luck with:

  • Find a PASS chapter.  There are groups in most major cities, and if there isn't one near you, there are virtual chapters. It's a chance to hang out with other SQL people, learn some things, and just get a feel for what's going on in the SQL world, both at large and in your town.
  • Get involved with SQL Saturday, SQL Rally, or SQL Summit events.  Some of these are run on shoestring budgets and eager for any help you can give.  Even if your job is just handing out flyers or stuffing bags, being a volunteer gets you backstage access to presenters, MVPs, and others in the community. If you can't volunteer, at least try to attend.  
  • Blog something.  I should spend more time on this, and web presence in general, but having a blog is an opportunity to share your knowledge and get your name out there a little bit.  
  • Read other people's blogs.  SQL BitsSQL Server Cental (particularly their Stairway series), and SQL Server Team Blogs are all great places to find some great SQL blogs to start.
  • Tweet something.  The SQL community is hugely involved in Twitter, and that includes the guys and gals who write SQL Server.  I once tweeted "Thanks, Microsoft!" about some SQL feature that I really liked, and a few minutes later, the developer who wrote that feature tweeted back, "Glad you like it!".  How cool is that?  Follow #sqlserver and #sqlhelp to start, and read Brent Ozar's great introduction to Twitter for SQL folks.
  • Of course, get yourself LinkedIn.  It's not like Facebook, honest-- it's just a good way to keep in contact with people you've worked with.
Notice that some of these don't even require you to leave the living room.  Of course, you'll make more meaningful connections if you actually get out and talk to people.  You might even-- gasp-- make a friend or two.  But at a minimum, the goal is to stay connected to the community.  

One big epiphany I had this year was gleaned from the classic What Color Is Your Parachute: a big goal of networking is to increase the number of people you can call (or email) to get introduced to someone who can help you.  That's it.  These people don't have to be able to vouch for your work personally (although that helps), they just have to be able to introduce you.  

So, for example, I volunteered to help organize SQL Saturday with my local PASS chapter, which introduced me to dozen or so SQL people here in Cleveland.   Now, aside from my ability to order great sandwiches, they don't know much about my SQL skills.  But collectively they probably know someone in every SQL shop in town, or they know someone who does.  So now I not only have some cool SQL friends, but I have a little network of people, and when I find myself asking, "I wonder who I can talk to at XYZ Corp," chances are one of these people can hook me up with a name and an introduction.

Further out at the edges of my network are the presenters I've met and watched at SQL Saturday, bloggers I've read, and the aforementioned SQL developers who tweet or lurk on the same hash tags as I do.  I don't expect Tim Ford or Jeremiah Peschka or Brent Ozar know who I am... but I know them.  (And I know people who know them, bwuahaha.)  More importantly, I know they're the experts in their areas, and that they have a wealth of knowledge and materials that they generously share in talks and blogs.  They're also active twitterers (tweeters?).  If I had some question about DMO's, window functions in Denali, or how SANs work, I know where to go.  

As you're making your New Year's resolutions next year, think about what you can do to increase the size of your SQL network.  Also, think about becoming a nicer driver-- the person behind you might be closer than you think!