Category Archives: TIPS & TRICKS

Back From Vacation Syndrome and SSMS Shortcuts

Was just back to United States from a long vacation and started to work. The first two days were filled with replying to all pending emails and settling up issues reported while I was away. Slowly was out of jet lag and my routine work life started. My client did gave me a new requirement to build a report. Came to my cubicle, opened SQL Server Management Studio (SSMS) and started building the query for it. With me being away from work for few weeks, I thought I might have forgotten the tables, databases and relationships which exists between different database objects.

To my surprise, I didn’t forget any and I painted the whole picture of the report from different tables in my mind when my client gave the requirement. While building the query I noticed something strange, I have been stumbling with keyboard shortcuts I use with SSMS and just took a breath and yelled at myself (How could I?). Shortcuts are cool feature with SSMS and I love using them. I know I should get into my notes and brush up my shortcuts.

This time I wanted to know why did I forget them and started googling about it. The one word answer is Brain Plasticity. The ability of our brain to be flexible and adapt with new changes is called Brain Plasticity and our brain survives by forgetting too. The article I read quoted human brain doesn’t need to forget intentionally and any changes in the outside environment has an effect on our brain and it will make us forget to store new events. Think of it in this way, If I were to think about SQL and databases at my vacation it might have gone terrible right?, luckily I wasn’t and my brain was on vacation mode and now am back to work it will slowly store my work and daily life related data and events and might forget vacation mode and it keeps working!!!Amazing is our brain!!!

I just called it as Back from Vacation Syndrome:)

Here are the shortcuts I brushed up from my notes and would like every SQL Developer to be aware of it and forget only when they are on vacation:P

Ctrl + U  - To Change database connection

Ctrl + F6 – Toggle between query windows

Ctrl + F5 – To parse the code

Ctrl + R – To Toggle Result Pane

Ctrl K + Ctrl C – To Comment the code

Ctrl K + Ctrl U – To Un Comment the code

F8 – To view Object Explorer

Shift + Alt + Enter – To view in Full Screen

Ctrl + Shift + U – Converts the selected code to Upper case

Ctrl  + Shift + D – Converts the selected code to Lower case

Ctrl + Shift + Home – Selects the code from current cursor location to the beginning of query

Ctrl + Shift + End – Select the code from current cursor location till the end

Share with me if you find some more shortcuts are essential.


Configuring Protection Levels of an SSIS Package

We work with SSIS packages holding sensitive data like server credentials and customer information,it is always a  good practice to use Protection Level property to secure the credentials and prevent it from unauthorized access.The Protection can be set up either to an entire package or only to the part holding the sensitive data. Hence its very important for us to know where to go to enable the Protection Level while working with packages.

Thumb rule to be followed is the Package should have same protection level at Project and Package level.

It can be configured in any of the following ways

1. Project Level

Right Click at the Project level and open its Properties window, You can configure the protection level you want from the drop down available at Security part.

Protection Level in Package at Project Level

2. Package Level:

Click anywhere in the designer and open its Properties Window by pressing F4 or View -> Properties

Protection Level at Package Level

3. During Deployment:

If you haven’t configured the Protection Level, you can still configure the package before deploying it to server by this method if you host your solution in a repository say using TortoiseSVN.

Click anywhere in the designer and Go to File ->Save Copy of Package as

Hope it helps!!



Delete last two characters of a Column in SQL Server 2014:

One of my colleague asked me this question, Is there a way to generalize a function to remove last two (n) characters from a column if the column doesn’t has a fixed length.  Yes it can be as below

Syntax: LEFT (ColumnName,LEN(ColumnName) – n)

The function calculates the length of the string and removes last n characters using LEFT() function.

Here is the kicker I wanted to share with you, Is it possible to flip the above function using RIGHT() to remove first n characters from a Column(string), yes it is possible:-)

Syntax: RIGHT (ColumnName,LEN(ColumnName) – n)

I made up a sample data using AdventureWorks - Transaction table in my SQL Server 2014 version. Imagine you have the TransactionID and ProductID concatenated as TranProdID column. Removing the last two characters will give you the TransactionID and removing the first three characters will give you the ProductID.

The query can be written as

SELECT  TranProdID, LEFT(TranProdID,LEN(TranProdID)-2) as TransactionID,                       RIGHT(TranProdID,LEN(TranProdID)-3) as ProductID                     FROM [SQLCAST2014].[Production].[Transaction]

Here is the snippet from my machine,

Remove Last Two Characters from a Column