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 !

JQuery: Synchronize two ASP.Net text boxes


jquery1

“The central concept behind jQuery is – find something, do something. More specifically, select DOM element(s) from an HTML document and then do something with them using jQuery methods. This is the big picture concept.” ___________________________Cody Lindley(jQuery Succinctly)

Introduction:

As the title suggests, this post is about interacting with two ASP.Net text boxes. If any change/interaction happens to one of the text boxes then we’ll try to do the same to the other text box, programmatically.

Live Demo & Code

Live demo here
Download code hereDownload

Designing the UI:

Let’s start with a new WebForm. The UI in this case requires only two multiline text boxes separated by some white spaces. To keep things simple we’ll concentrate on ‘how it works’ rather than ‘how it looks’. Now, the above quotation indicates that we need to find something and do something and thus it is a two step process. Here the ‘something’ is the text boxes and we can find them by their ids, class, tag, type etc. Particularly for this example we’ll employ the find by class trick. So, we need to mention same CssClass for both the text boxes and thing to notice is- this class may not exists at all.

<div>
     <asp:TextBox runat="server" ID="TextBox1" CssClass="sync" TextMode="MultiLine" Height="100px" />&amp;nbsp;&amp;nbsp;
     <asp:TextBox runat="server" ID="TextBox2" CssClass="sync" TextMode="MultiLine" Height="100px" />
</div>

Writing the Code:

In the script part, we’ll code for two things-

  • Synchronizing the text typed in to one text box with the other
  • Synchronizing the scrolling in both the text boxes

So, we will write two functions for these two specific actions. The first operation can be done on keyup event and the second with scroll event.

<script>
        $(document).ready(function () {
            $(".sync").keyup(function () {
                $(".sync").val($(this).val());
            });
            $(".sync").scroll(function () {
                $(".sync").scrollTop($(this).scrollTop());
            });
        });
</script>

Result:

As I already have said, we can demonstrate two operations

  • Typing anything in to one of the text box will result in displaying same text in the other text box, simultaneously.
  • Type few lines of code so that the scroll bar is visible. Now when we scroll any of the text boxes using either keyboard or mouse, other one will be scrolled automatically.

Explaination:

We have embedded our code inside $(document).ready() function as this function is executed after the DOM is loaded and before the page contents are rendered in to the browser. We can define a common function for both the textboxes with the use of CssClass name. The first action is defined inside the keyup event so that when we release the key the action will be performed. The action here is nothing but copying the content of the current text box to the other. The val() with no argument returns the value of the text box and when some argument is passed to it, sets the argument as value of that text box. I hope this made the thing very clear and further explanation is not required. Now consider about the second action. The scroll() event is fired when we try to scroll the content of text box either through key board or mouse. Again, scrollTop() with zero argument returns the current scroll position but, sets the scroll positions to a value same as the argument if called with an argument.

Conclusion:

Now we are able to develop a page with two text boxes which are synchronized upon typing text in to them or using the scroll bar. We can use more than two text boxes and it will still work as long as we use a common CssClass for them.

I hope you have enjoyed reading this post. Your feedback is very important to me.

Book: Visual Studio 2012 and .NET 4.5 Expert Development Cookbook


book
Book Title
: Visual Studio 2012 and .Net 4.5 Expert Development Cookbook
Author: Abhishek Sur (www.abhisheksur.com)
About Author: Abhishek Sur is a Microsoft MVP in Client App Dev since 2011. He is an architect in the .NET platform.  He is also a renowned public speaker and owns one of the most active  Microsoft user group named Kolkata Geeks.  His website abhisheksur.com guides both budding and experienced developers to understand the details of languages and latest technology. You may also follow him at Facebook or Twitter .

The very first thing I like about this book is its conciseness. Even though it is a 380 pages book you will find every aspect of the enhancement & new features introduced with Visual Studio 2012 and .Net 4.5 has been  explained extensively with proper examples. Starting with the Visual Studio IDE features, author marches us through the enhancement to ASP.Net, WPF etc. It has a dedicated chapter on memory management too.


I always go for the shortcuts while developing applications and also love to use features provided by my IDE for faster development. This book is a good educator of  IDE features and shortcuts introduced in Visual Studio 2012 which will help you in saving development time.
The key component of the book is, everything comes with a proper example. With this book, one can also learn –

  • In-depth memory management helps to to grasp under the hood happenings.
  • Asynchronous programming has become very important in the last few years and the book explains all about the necessity and usage of Asynchronous Programming in .Net
  • Enhancement to ASP.Net includes support for HTML5, Web Socket, JQuery and been explained beautifully.
  • Enhancement to WPF
  • Building Windows 8 style applications and communication & sharing between apps and devices.

In case you want to check it out before buy, you can download Chapter-1: Introduction to Visual Studio IDE features for free.

You can buy it from packtpub.com or amazon.com

Honestly speaking, I have been immensely benefited from C# internal series by the author on his blog DOT NET TRICKS and this book further helped me to discover the roots.
It is not conclusive by any means and there is a much more to grab. At last, if you are a .Net developer and a technology loving person then you’ll definitely love to add this book to your bookshelf.

Windows 8/8.1: Bring back IE 10/11 to Metro Style mode


IE

After using Windows 8.1 preview for few days I noticed that my Internet Explorer is not opening in metro style mode even though I am clicking on the tile placed on my start screen which was behaving normal, just before few days. If you are facing the same problem then this post will be really helpful to you.Although I mentioned about Windows 8.1 which means I have IE 11 on my PC but, this works also for IE 10 on Windows 8 PCs.

First of all this is not a bug or issue, in fact this is its normal behavior. Now, you may ask why is this happening? OK, answer is quite simple but confusing. Setting a browser as default other than Internet Explorer led to this situation. Now this statement made half of the answer to question “How to bring back IE 10/11 to metro style mode ?” Let’s see detailed steps to do this.
Step-1:
Open Internet Explorer and click on menu ‘Tools’ (if you are unable to view the menu bar, press ‘Alt’ key from your keyboard or you can use shortcut ‘Alt+T’ ) and then click ”Internet Options’. A window pops out with different tabs.

Step-2:ie102

Go to ‘Programs’ tab. Click on ‘Make Internet Explorer the default browser’ as shown in the figure.

Step-3: ie104You’ll be presented with ‘Set Default Programs’ window containing list of programs installed on your PC. Choose ‘Internet Explorer’ from the list and click on ‘Set this program as default’. Finally click ‘OK’.

That’s it !

Thanks for reading.

Update (24 July 2013): Corrected incorrect information that was present in the post. I deeply regret for the same.