SQLServerCentral Article

10 SQL Snippets For Faster, More Effective Code


A Quick Summary

Head over to https://github.com/gvohra/sqlpromptsnippets to get a library of the best SQL Prompt snippets. This is an open source project for the benefit of the SQL developer community, so please also contribute back to this repository.


Last time I covered how to make SQL Prompt snippets collaborative within your team, by using a VCS (version control system).

A natural extension of this is to collaborate with the wider SQL developer community.

Since SQL Prompt has existed, users all around the world have thought of thousands of snippets to write SQL faster. Until now, those brilliant snippets have been stuck on hard drives, shared by email or passed along on internet forums. Now, with SQL Prompt connected to version control, you can get connected to the world's largest library, and have access to the latest and greatest on-demand.

Go to https://github.com/gvohra/sqlpromptsnippets to get connected. There you will find all the standard Red Gate snippets, plus a few dozen more.

I strongly recommend using Git to connect to this repository, as per my previous post. If you want to, you can also download the snippets en masse as a .zip:

Now, when you improve or add a snippet, you can share it back with the wider SQL developer community. Please feel free to fork this repository and submit your changes as a pull request to the main repository.

Now for the fun part: showcasing new snippets from the libary!


Here are two very basic snippets, not in the original Redgate pack, which I use perhaps 100-200 times a day:

gb -> GROUP BY

ob -> ORDER BY

Summary statistics

I regularly find myself looking at this in the results pane, and wondering if the number I'm reading is 300,000,000,000, or 30,000,000,000, or 3,000,000,000, or 300,000,000:

This snippet takes care of this:

* num -> FORMAT($CURSOR$,'#,##0')

Pushing this functionality into two more common queries:

* scf -> SELECT FORMAT(COUNT(*),'#,##0') FROM 



Here are two quick helper utilities to get around some of the more tricky-to-type code fragments:

* lk -> LIKE '%$CURSOR$%'

* today -> CAST(GETDATE() as DATE)


There's nothing worse than trawling through a stored procedure with commentary smattered haphazardly throughout it. Firstly, comments are not a good replacement for bad code (read Martin Fowler's excellent book for more on this). Secondly, if comments are absolutely required, the author should at least take the opportunity to make the code easier to read.

So, here are a handful of commentary blocks to help you split up longer sets of SQL / stored procedures, in an orderly fashion:

* lin -> to split up code

* linn -> as above, with space for commentary

* header -> as above, with even  more space


That's all for now. Hopefully you've found this tutorial, and these SQL Prompt snippets helpful.

Do you have some handy snippets on your hard drive? If so please add a comment to this article, and / or contribute to the repository to make the world a more productive place.

Happy coding,

Gaurav (https://www.linkedin.com/in/gvohra)


3.4 (10)

You rated this post out of 5. Change rating




3.4 (10)

You rated this post out of 5. Change rating