Comments in SQL Stored Procedures

Comments can be extremely valuable for anyone who has to review, maintain, or troubleshoot your code. This includes future you. I highly recommend against worrying about the performance impact of a reasonable amount of comments, and instead focus on prioritizing the usefulness of context that the comments provide. As someone on Twitter said, there is a limit. If your comments amount to the abridged version of War and Peace, you might consider – at the risk of decoupling the code from its documentation – putting that documentation elsewhere, and reference the link in the procedure body comments.

https://sqlperformance.com/2016/11/sql-performance/comments-hamper-performance

Format your T-SQL code

Good T-SQL code formatting really does matter. There are things that will help you and others read the code more quickly. It also helps to avoid mistakes that can take hours to find and fix.

Microsoft is requiring the semi-colon in more and more statements. You’re going to hit issues if you don’t use it as a statement-terminator in all your statements. Yes, older code can still work, but as you add newer functions, you’re going to hit the issue. Eventually they’re going to require the terminator for all statements, just start writing it that way now and avoid the issue.

SQL Prompt includes tools to help with this.

This article makes some very good points. Please read this. It will be well worth your time.

https://www.simple-talk.com/sql/t-sql-programming/devil-wrote-code/

SQL Prompt Snippet Manager

Everyone in our department has SQL Prompt installed. You are able to write scripts faster using it. When you start to type it looks at the SQL Schema system views and supplies a list for you to choose from. It also uses the Snippet Manager to select shortcuts for commonly used statements. But did you know that you can add your own shortcuts? Just Select the Snippet Manager from the SQL Prompt menu item and add away. It takes a little effort to remember to use the snippet instead of just typing everything out. Typing only “scf” will produce “SELECT COUNT(*) FROM” to Count number of records returned by query. Typing three letters is much quicker. Take a few minutes to check it out. It will be worth your time.

Here is an article about this topic:

http://www.sqlservercentral.com/blogs/steve_jones/2016/09/27/quick-tipsusing-ii-in-sqlprompt/

Excel save-as CSV options

Here is little trick to change the CSV save-as option to use something other then a comma. Sometimes you need to make an output file with a delimiter other than a comma, tab or space. Excel does not come with an option to save a file as pipe delimited. You have to make a system change to accomplish this. This option is in the strangest place and you would never think to look there.

This link will show you the easy way to make the change. Be aware that when you make this change, you make it for the computer not just Excel.

http://superuser.com/questions/783060/excel-save-as-csv-options-possible-to-change-comma-to-pipe-or-tab-instead

OVER Clause

I think that the OVER clause is underutilized. I plan to start using it more for development solutions.

It determines the partitioning and ordering of a rowset before the associated window function is applied. That is, the OVER clause defines a window or user-specified set of rows within a query result set. A window function then computes a value for each row in the window. You can use the OVER clause with functions to compute aggregated values such as moving averages, cumulative aggregates, running totals, or a top N per group results.

This link provides a great overview of the OVER clause.

http://www.sqlservercentral.com/articles/Over+Clause/132079/

BTW: You can now subscribe to this blog.

SSMS Query Shortcuts

I use some of these all the time. I know I would like to setup some custom shortcuts to make my life easier.

https://msdn.microsoft.com/en-us/library/ms174205.aspx?f=255&MSPPError=-2147217396

Here is an article including a script you can run on your SSMS to setup custom shortcuts.

http://slavasql.blogspot.com/2016/02/ssms-query-shortcuts.html

If you happen to be in Charlotte on Saturday Sept. 17th, Sign up to attend SQL Saturday. It is a great learning experience.

http://www.sqlsaturday.com/560/eventhome.aspx#