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:

SELECT schema_name(SO.schema_id)+'.'+SO.NAME,SO.TYPE_DESC,SM.DEFINITION,SO.CREATE_DATE,SO.MODIFY_DATE
FROM SYS.SQL_MODULES SM
INNER JOIN SYS.OBJECTS SO ONSO.OBJECT_ID=SM.OBJECT_ID
WHERE SM.DEFINITION LIKE '%blah%'

----------------------------------------------

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:

DECLARE @mylist VARCHAR(MAX)
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.

SELECT ABS(CHECKSUM(NEWID()))%9+1 FROM MASTER.sys.columns

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.

SELECT TOP 15 * FROM MyTable ORDER BY NEWID()

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?