SQL #48 – Block selection with keyboard shortcut in SQL Management Studio (SSMS)

In a previous blog, SQL #47 – Too many in-line comments totally destroy code readability, I made a point that all SQL developers need to have a consistent writing style to achieve high level of code readability.

Being productive in SQL Management Studio will be your first step to achieve this goal.

In this blog, I’ll show you one trick I use every day in SQL Management Studio. This is one of the many keyboard shortcuts I use to be productive and to achieve the goal of high level of code readability.

I use a lot of tabs/indents in every query I wrote. Hitting the Tab key twenty times for twenty columns can be very tiring (and boring).

Goal

Let’s say you want to type a simple SELECT statement with a style as the following. There are two tab spaces after each comma. 

 

You can certainly insert the comma and two Tab spaces for each column, on each line.

I’ll show you a more fun way to do that, and a more productive way.

Block Selection with Keyboard Shortcut Alt + Shift

Yes, block selection is more fun.

Start to insert the comma, and two Tab spaces for the first three lines.

Put your cursor to the beginning line for YearID, before the comma. Then hold down Alt + Shift, and move the right and down arrow key until you have a block selection as shown below. 

Block Copy with Keyboard Shortcut Ctrl + C

Let go of the Alt and the Shift key, the block selection will remain. Then press Ctrl + C to copy the block selection.

Now you are ready to insert the block selection anywhere you want.

Block Insert with Keyboard Shortcut Ctrl + V

The keyboard shortcut pair Ctrl + C and Ctrl + V is the only thing I can remember from my early days of programming in C.

Now point your cursor to before column QuarterNumber and press Ctrl + V. You will witness the magic.

Practice the above three Keyboard Shortcuts a couple of times

You are going to a pro!

Have fun!

SQL #47 – Too many in-line comments totally destroy code readability

I have been wanting to blog about this for awhile. SQL code readability is not a topic SQL developers like to talk about. In my 10+ years of working career, I only had this topic with two co-workers. Both of them told me that I write very “readable” SQL code. One of them said he would not use the coding style to judge a SQL developer. Another co-worker told me that  he actually installed a SQL code beautifying software to make other developers’ SQL code readable, before he even attempted to modify the code.   

Commenting is always welcome in SQL code. Single line comments, multi-line comments, in-line comments are always good to see when you work on other developers’ SQL code, until you realize that not only the comments itself have no readability, but also the messy comments totally destroy the readability of the code.

I find myself recently not only spending time to beautify the SQL code, but also to beautify the comments, especially the in-line comments.

Multi-line comments in the header portion of the procedure

It’s my preference to have multi-line comments in the header portion of the procedure (beginning of the procedure) .

I also place multi-line comments in my SSIS packages by adding them as Annotation. The annotation is certainly not a sophisticated text editor. So make sure you type up your multi-line comments in a SQL/Text/Word editor, and format it with appropriate indents, then paste it to the Annotation in the SSIS package.

Inside the procedure body, I do not use in-line comments

Inside the procedure body, I absolutely do not use in-line comments. Instead I logically divide my code into sections and paragraphs, similar to sections and paragraphs in a book, where paragraph performs only one task, and section is the collection of small tasks.

For Sections, I’d use comments like this:

For paragraphs, I only use single line comments.

One more note before I close this post. When you design your SSIS package, and run into problems in your SQL query in an OLE DB Source in a data flow, check if you have In-line comments embedded in the query. Removing them might help to solve your problem.

Let’s work together to make our SQL code readable and beautiful with a consistent style!

SQL Server Profiler

Planning to write blogs about this wonderful performance monitoring tool.

When DISTINCT does not work

If you have experience with writing SQL codes, but SQL is not your primary job, you might get a feeling that you are almost there as an expert, but sometimes, you just cannot seem to reach a very straightforward goal.

Recently a business process analyst came to me for some SQL tips.

Here is the challenge he is facing.

When I query T_HISTORY I get multiple records for a Loan Number. How do I return only one or the last record? I tried adding Distinct to the select but that didn’t work.

This is where DISTINCT will not work. We will have to use sub-query, or temp table.

I showed him one way to do this.

The key is in the second part to group the data by the loan number, then get the max ID. Temp table #EXPORT2 should only have the unique service loan number after this.

War over SQL procedures only VS. SSIS packages only

A previous co-worker once summarized my design philosophy. “You are very good at modularizing”, he said. It turned out that the comment is not entirely in the sense of approval.

Because of my design philosophy of modularization, most of the ETL processes I designed ended up having a mix of SQL procedures and tasks in several SSIS packages. Most importantly, each procedure/task/data flow/package is designed to perform just one atomic task.

My previous co-workers told me that now they are having discussions about whether to convert the ETL processes to SSIS package only (favored by developers who is experienced in SQL Integration Services), or SQL procedure only (favored by developers who believe that Integration Services creates too much trouble in terms of deployment).

I frankly told them that do not do either. Stay modularized and specialized in each procedure/task/data flow/package . That’s the key. SQL procedure only or SSIS package only is only indirectly relevant.

If we heed my previous boss’ advice of “do not over engineer”, I think stay put is the best route.