Archive for the ‘Tips and Tricks’ Category

How familiar you are with SQL, the question always answered by how fast you work with SQL. The speed is directly proportional to the shortcuts we use. Let us discuss some useful keyboard short cuts that help you in daily activities with SSMS.

  1. Ctrl + N – New Query Window
  2. Ctrl + K  + C/U – Comment the selected line(C)/Uncomment(U)
  3. Ctrl + Shift+L/U – Make a selected word upper(U)/Lowe(L) Case
  4. Ctrl + W  – Select current ( Ctrl + Shift+ ->(arrow) )
  5. Ctrl + U – Select Database
  6. Ctrl+F5 – Parse Query
  7. Ctrl + E – Execute (F5)
  8. Ctrl + (Arrow) – Cursor move to arrow direction till next special character
  9. Ctrl + (Space)/J (Alt+Right Arrow) – List member (intellisense)
  10. Ctrl + M – Enable Execution Plan
  11. Ctrl + L – Display actual execution plan
  12. Ctrl+Shift+V – Cyclic clip board ( Paste number of copied values)
  13. F8 – Object Explorer
  14. Ctrl+Alt+T – Template Explorer
  15. Ctrl+Tab – Move between SSMS query window tabs
  16. Ctrl + D/T – Changes result type [ Grdi(D), Text(T)]
  17. Ctrl + Q – Query Designer
  18. Ctrl + R – Hide/unhide Result pane
  19. Ctrl + H – Find and replace ( Ctrl+Shift+H – > You can experiment in your system)
  20. Ctrl + Shift+T – Toggle Words both sides of a special character
  21. Ctrl + F4 – Close currently opened query window
  22. Ctrl +\ + E – Error List
  23. F10 – Step over ( Debugging shortcut)
  24. F11 – Step Into ( Debugging shortcut)
  25. F9 – Toggle Bookmark.

And also SQL supports all windows shortcuts like Ctrl+C,Ctrl+N etc.

Apart from these plenty of shortcuts, Microsoft has provided an option to customize your own shortcuts.

shortcuts_01

In “Tools” menu “Options” tab “Keyboard” -> “Query Shortcuts“, here you could set your own keyboard shortcuts. For example if you wanted select 100 rows from a table, you can set any key combinations as “Select top 100 * from ” and then select any table name in query window and press the assigned key combination to get the result. Same way you can set frequently using key words as shortcuts.

Please do start playing with SSMS and find more shortcuts and leave them in comment box.

When we think of delete duplicate, we are familiar with deleting data by generating row number for each row( either using derived table or CTE). Now here I would like to show you a method to identify each row from duplicated records without using row number or temp table logic.

The method would break down a concept of duplicate rows in SQL and this key word works after SQL2005. As I said there is no duplicate rows in SQL, even the rows are duplicated each rows are stored identically and they can be identified using an internal storage id called Row Id. Let us see how to utilize this id to resolve our issue.

physloc

Above I have created a table and inserted duplicated rows, in the select query I have used “%%Physiloc%%” keyword and displays row id of each rows (this is an hexadecimal value of the location where rows are being stored), we can use this row ID to delete duplicate rows from the table using very simple logic.

physloc2

Here I have deleted the duplicate rows only by executing the first query in the above screenshot.

Take away : There is no duplicate rows in a table, each rows can be identified by Row ID of each row. Please do leave your comments, we will discuss more about physiloc in up coming post.

Whenever we required to test a scenario in lower version of an SQL. Microsoft has provided an option to change the compatibility level of database to achieve it.

Com_level1

Compatibility value and it’s respective versions

Com_level2

Once you have changed the compatibility level of a DB, features available to database is restricted to the compatibility level set to the DB.

As we all know LAG and TRY_CONVERT are introduced in SQL 2012 version, let use see what error SQL shows if we execute these statements.

Com_level3

Ref : https://msdn.microsoft.com/en-in/library/bb510680.aspx

We have discussed about Template explorer in previous post.Are you still expecting little more customization in it ??

Yes we do have it.

If you have selected a particular template then go to “Query” menu and select “Specify values for ….” or Ctrl + Shift + M.

Template_Param_01

Once you have selected the option you will get a window based on the template available in the query window.

Template_Param_02

All you have to do is to fill the values as per your need ( column name, table name, database name etc.)

Template_Param_03

Once you have done with the entering of values in newly available window just click on “OK” button to effect the changes in query.

Template_Param_04

Hope you have enjoyed to have a tip to Lazy 🙂

How many of You Searched “Google” to add a column or Drop a constraint ?.

We weren’t searching for the functionality we were searching for syntax, isn’t it ??

If the Syntax is already available in SQL, do we ever think about Google ??

Yes it is there, see the below picture.

Template _explorer