Top 10 exciting new features in SQL Server Management Studio 2016


[This article was originally posted in CodeProject]

Introduction

This article is an effort to list out few exciting improvements those has been made to SQL Server Management Studio 2016. I believe, most of these new features can help improve the speed of our work and more convenient to use the SSMS.

1. Color Theme

With SSMS 2016, now you can choose your preferred color theme, a feature similar to that in Visual Studio. For now, you can choose one from 2 available color themes – Blue or Light, Blue being the default theme.

Tools - Options - Environment - General - Color theme

2. Hide Save Query Window Prompt

In earlier versions of SSMS, you can’t ignore the save query windows prompt that comes when you want to close either an unsaved query window or the SSMS itself. While this restriction is actually meaningful to have in SSMS but with SSMS 2016, you can now customize to hide those prompts as per your preference.

Tools - Options - Query Execution - SQL Server - General - “Prompt to save unsaved T-SQL query windows on close”

3. Update from SSMS Menu

SSMS 2016 automatically checks for any updates available when it is online and a toast notification comes when there is an update available something as following screenshot-

However, you can also check manually for updates availability.

Tools - “Check for Updates…”

You can disable automatic checking for updates by unchecking the checkbox in the popup window that appears when you click “Check for Updates…” as described above.

4. Search Options

Confused with so many new features and steps to open the window to configure them? Don’t worry, SSMS now has the Search Options which is one of much requested feature. You can search with the feature name or a keyword.

Tools - Options

5. Highlight Current Line

How many of the times you feel lost while modifying an existing huge query or stored procedure in SSMS? The problem occurs when your SQL statements are very long and you haven’t configured text editor to wrap words automatically and then when you use the horizontal scrollbar to scroll the content, it becomes difficult to identify which the line you wanted to modify or check. Now, with SSMS 2016, an outline will follow your cursor where ever you placed it. That makes easy to identify the line you are currently working with. The best part is, it can be configured.

Tools - Options - Text Editor - General - “Highlight current line”

6. Scroll & Zoom Execution Plan Window

There are also few improvements done on the Execution Plan window. Now you can zoom the content of this window using shortcut CTRL + Scroll. You can also scroll the content of the window by dragging the content with mouse.

7. Retain Carriage Return and Line Feeds on Copy or Save

It may seem to you as a weird default behavior. In SSMS 2016, if you copy any result from the Result Grid and paste it in a notepad or any text editor, you will notice that the carriage return and line feeds are not in place. However, this can also be configured.

Tools - Options - Query Results - SQL Server - Results to Grid - "Retain CR/LF on copy or save"

Example:

Executed following query by unchecking the checckbox.

SELECT 'This is a demo text
for test purpose only' AS demotext

The output while copying to notepad was as follows-

demotext
-------------------
This is a demo text  for test purpose only

Now, executed the same query after checking the mentioned checkbox and got result as –

demotext
------------------
This is a demo text
for test purpose only

 

8. Advanced Scroll Bar

If you have used Visual Studio, then probably you are already aware of such feature. With SSMS 2016, you can use “bar mode” or “map mode” for the vertical scroll bar in the query window.

  • Bar Mode : This is the default and normal vertical scroll bar same as you can see in the previous versions.
  • Map Mode: This is new and source overview can be viewed on the scroll bar which can again be configured as –
    • Off : Source Overview will not be visible
    • Narrow: Overview with minimum width
    • Medium: Wider than previous
    • Wide: Widest scrollbar with overview

You can also configure to show Tooltip on hover or scroll using checkbox “Show Preview Tooltip”

Tools - Options - Text Editor - All languages - Scroll Bars - Behavior

You can also open the configuration window by right clicking on the vertical scroll bar and then clicking "Scroll Bar Options..."

To know rest of the features , please visit my original article on CodeProject

Thanks for reading. Awaiting your feedback 🙂

Tip-3: Exciting SSMS features derived from Visual Studio


In this series of posts, we’ll see few great yet less known and under utilized tips/features those are there since earlier versions of Visual Studio and later made their way to SSMS (SQL Server Management Studio). I am sure, you’ll love these tips.
Tip 3: Transact SQL Code Snippet – Insert Code Snippet

 

Have you used code snippet feature in Visual Studio? Aren’t they very useful?

What are code snippets?
Code Snippets are reusable code templates those help to write code faster.

What are Transact SQL code snippets?
“A Transact-SQL code snippet is a template containing the basic structure of a Transact-SQL statement or block. You can use snippets as a starting point when adding statements in the Database Engine Query Editor. You can insert the pre-defined snippets supplied with SQL Server, or create your own.”MSDN

We’ll discuss about how we can create a Transact SQL code snippet of our own on a later blog post. For now, we’ll see how we can use them.

You can do it either way-801800

  • Right click on the query window and click Insert Snippet…
  • Using keyboard shortcuts CTRL+K, CTRL+X802

Then choose the appropriate category and then the appropriate template to insert it in to your query window.

 

Hopefully, you liked this tips. Please stay tuned to learn few more exciting tips in this series. Will love to hear from you. Thanks for reading 🙂

 

Tip-2: Exciting SSMS features derived from Visual Studio


In this series of posts, we’ll see few great yet less known and under utilized tips/features those are there since earlier versions of Visual Studio and later made their way to SSMS (SQL Server Management Studio). I am sure, you’ll love these tips.


Tip 1: Clipboard Ring – Copy Paste rediscovered
Tip 2: Box Selection – Select and Edit text easily

90Have you ever imagined to select a particular portion of the text or rectangular region of the text from multiple rows like the figure -1 ?
Box selection?
Why I need that ? Oh ! Believe me, many times while executing queries for various tests you’ll need it.

Is this really possible?
Sad, with traditional horizontal selection system you can’t do that.

If you are known to Visual Studio 2010 (release: 12 April 2010) or newer environment, probably you already know this. Since Eclipse 3.5 (release: 24 June 2009), this facility is there. If you have SQL Server 2014 (not sure about SQL Server 2012), you have the power to do columnar selection also known as box selection.

box-selectionYou can do it either way-

  • Holding down the ALT key and dragging mouse to select text as we do for normal selection.
  • Using keyboard shortcuts ALT + SHIFT + ARROW KEYS

You can do following actions through Box Selection

  • Copy : Copy rectangular region/box of text
  • Text Insertion: Select the box and start typing to replace the selection with typed in new text on every selected line.
  • Paste:
    • Single Copy – Box Paste: On box selection pasting some text will be applied to every line
    • Box Copy – Box Paste: Paste contents of one box selection to another box selection
    • Box Copy – Single Paste: Little weird though. Check what is happening !
  • Zero-Length boxes: Vertical selection of zero characters wide to create a multi-line insertion point for new or copied text. To do this, just hold down the ALT key and keep pressing down/up key till the length you want.  This will create a multi-line insertion point.

 

Hopefully, you liked this tips. Please stay tuned to learn few more exciting tips in this series. Will love to hear from you. Thanks for reading 🙂

 

Tip-1: Exciting SSMS features derived from Visual Studio


In this series of posts, we’ll see few great yet less known and under utilized tips/features those are there since earlier versions of Visual Studio and later made their way to SSMS (SQL Server Management Studio). I am sure, you’ll love these tips.


Tip 1: Clipboard Ring – Copy Paste rediscovered

How many times it has happened that you have copied some text and before pasting it on the desired position /location, copied another chunk of text and then you realized that you lost the previous copy?
Have you ever wished for a feature which can help you accessing text previously copied to clipboard and which should be as simple as the popular copy paste shortcuts?

If you are using Visual Studio 2010 or newer, probably you already know this. Since SSMS 2012, this feature know as “Clipboard Ring” has been introduced. Either of the following two simple keyboard shortcuts will do the job.

CTRL + SHIFT + V
CTRL + SHIFT + INS

Using these shortcuts you can cycle through the clipboard ring which stores maximum of 20 copy actions.

Hopefully, you liked this tips. Please stay tuned to learn few more exciting tips in this series. Will love to hear from you. Thanks for reading 🙂

 

Windows: Copy only files recursively from a folder containing sub folders


windows
What this post is all about?
This post is basically to serve a way to copy all or some files at once from a folder containing one or multiple sub-folders.

Which OS are supported?
Windows 7, Windows 8, Windows 8.1

What is the situation, by the way? Can we have an example ?
Let’s create two folders named Folder1 and Folder2 on desktop. Then copy some files in to Folder1. Create few sub-folders inside this folder and again copy few files to each of the sub-folders. Now what we may want to do are:

  • Copy all the files from Folder1 and its sub-folders to Folder2
  • Copy specific types of files from Folder1 and its sub-folders to Folder2

Is there any solution to this requirement?
Yes ! 🙂

Is it a simple one?
Yes ! Very much. Indeed, You know this already 😛 .It’s just a simple trick to what you already know.

Then you are waiting for what? Show me !
Okay. I have few pdf files in each level of the folder hierarchy in Folder1 as shown the fig. below .
a1     a2   a3

Now I want all these pdf files to be copied to a4Folder2 at once. Here is the trick then: open Folder1 and search for *.*

Select all files (by pressing CTRL+A) and exclude all the sub-folders, copy (CTRL+C) and paste (CTRL+V) on Folder2 . This solves our first purpose.

For the second purpose, you only need to change the search pattern to *.<file extension> instead of *.* .For Example- *.pdf . This will list only the pdf files automatically excluding the sub-folders.

Hopefully you liked reading this little post. I really welcome all kind of feedbacks and suggestions.

Thanks for reading !

MySQL: Condition based sorting


AscDescIntrioduction:

Sometimes we need to sort a result set based on some conditions. Let’s take few real time scenarios and than try to resolve these with the help of CASE WHEN. Scenario-1: Sometimes we need an element to be placed at bottom of the list. For example, a Dropdownlist containing educational qualifications may contain  an item “Other”. While showing all the educational qualifications from database we may need this particular element (i.e, “Other”) to be moved to the bottom of the list. If we try to sort this list using usual ORDER BY keyword then we may not get the desired sequence. To get the desired result we need to use CASE WHEN keyword along with the ORDER BY. Let’s illustrate this with an example- Simple ORDER BY :


SELECT * FROM
(
 SELECT 'I.Sc.' AS Qualification
 UNION
 SELECT 'B.Sc.' AS Qualification
 UNION
 SELECT 'Other' AS Qualification
 UNION
 SELECT 'P.G.' AS Qualification
 UNION
 SELECT 'MCA' AS Qualification
 UNION
 SELECT 'DCA' AS Qualification
 UNION
 SELECT 'P.H.D' AS Qualification
) AS TBL
ORDER BY Qualification

OUTPUT: 5         ORDER BY with CASE WHEN :


SELECT * FROM
(
 SELECT 'I.Sc.' AS Qualification
 UNION
 SELECT 'B.Sc.' AS Qualification
 UNION
 SELECT 'Other' AS Qualification
 UNION
 SELECT 'P.G.' AS Qualification
 UNION
 SELECT 'MCA' AS Qualification
 UNION
 SELECT 'DCA' AS Qualification
 UNION
 SELECT 'P.H.D' AS Qualification
) AS TBL
ORDER BY CASE WHEN Qualification='Other' 
THEN 'ZZZZZ' ELSE Qualification END

OUTPUT: 6           EXPLAINATION: Here we have set the value of item “Other” as “ZZZZZ” while the sorting is being done. Generally “ZZZZZ” will be the last item in any real world list and hence will move to the last. Scenario 2: In some other cases we may need the desired item to be moved to top of the result set. An example for this situation can be, a Dropdownlist containing items for Book Category. The list may have an item with text “General” , which is meant for those books whose category is not clearly known. Let’s assume that most of the books are supposed to be of this category. Then we have to place this item in the first index of the Dropdownlist so that it will be easier for user to pick the item. Now, we need a little modification in the ORDER BY clause as follows.


ORDER BY CASE WHEN Qualification='General' THEN NULL ELSE Qualification END

Please share your feedback ! Thanks   You may also like :

Creating a simple stored procedure in MySQL

CodeProject

CSS: Under-utilized content property


css

#Batman {
background-color: black;
clear: both;
visibility: hidden;
content: “I’m Batman”; }  /* @CSSHumor */

Introduction:

In this post we’ll discuss about

  • (The Theory) use and importance of CSS property – content
  • (The Magic) creating printable links using CSS content and pseudo-elements

If you are not interested in ‘The Theory’ which is available in almost all CSS books and web tutorials like w3schools.com, you can start with ‘The Magic’, the feature which motivated me to write this blog post.

CSS ‘content’ Property:

The primary use of CSS is to describe the presentation semantics of an HTML documents but, we can also use CSS for content generation. With content property we can inject a piece of information to an existing document. We have to use content property along with pseudo-elements like :before, :after etc.

Example:

I feel, the best use of content property is to inject the URL of a hyperlink next to it’s text content.

a:after
{
   content: "(" attr(href) ")";
}

This will inject the URL mentioned within the href tag next to each link and will be decorated with a pair of parenthesis. For detailed list of content property values you may refer w3Schools.com.

Create Printable Links:

Before we start let’s have a look on the below paragraph of an HTML page-

“If you want to start learning web technologies then there is no better place than W3School to start with. For Microsoft technologies you can get comprehensive help from MSDN. My suggestion is to follow blogs like Technology Talks, Daily Dot Net Tips, Dot Net Tricks, Kunal Chowdhury, SQL Authority etc. ”

The HTML code for this is

printable_link_1

Note that this HTML snippet has certain links to some other places. Now let someone wants to print the page containing the above paragraph. He/she will get the print copy as the above paragraph but

  • What about the links?
  • How can the user get the links for Technology Talks, Daily Dot Net Tips, Dot Net Tricks, Kunal Chowdhary, SQL Authority etc. from the print copies?
  • Is there any way that we can show links on the print copy but not in the web page?

Solution

The easiest solution is to use content property of CSS along with pseudo-element :after. And as we want the URL to be injected next to it’s corresponding link only in the print page and not in the page that is rendered to the browser, we need to mention media type as ‘print’. So code view of the final document may look like

<style media="print">
    a:after {
      content: "(" attr(href) ")";
    }
</style>
<div>If you want to start learning web technologies then there is no better place than
 <a href="http://www.w3schools.com/">W3School</a> to start with.For Microsoft technologies
 you can get comprehensive help from <a href="http://msdn.microsoft.com/en-US/">MSDN</a>.
 My suggestion is to follow blogs like <a href="https://suvendugiri.wordpress.com/">
 Technology Talks</a>, <a href="http://dailydotnettips.com/">Daily Dot Net Tips</a>,
 <a href="http://www.abhisheksur.com/">Dot Net Tricks</a>, <a href="http://www.kunal-chowdhury.com/">
 Kunal Chowdhury</a>, <a href="http://blog.sqlauthority.com/">SQL Authority</a> etc.</div>

Live Demo

You can view the live demo here. To spot the difference just view the print preview.

Conclusion

Now, we are able to do the magic with which we can create better web pages with links for both kind of users i.e, online users and offline users (with printed material).

Hopefully, you have enjoyed reading this post.
I’ll love to read a feedback from you.
Thanks !