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 🙂

Get your hands on SQL Server 2016 RC3 on Azure


[ Note: This post is for beginners with Microsoft Azure and just about creating the environment. Will cover few exciting features in the next blog posts ]

Expiry date of the post: 1st June 2016
A post, with an expiry date ! Sounds illogical, isn’t it?
Well, it’s true and I will explain this soon.

If you are from database background and have ever used SQL Server or currently working on areas related to data or SQL Server, you must be aware of the trending buzz among the people around you. A more faster, secure and powerful SQL Server, SQL Server 2016 will be available soon. Microsoft continuously surprising people with their moves towards making many development tools like Visual Studio, .Net etc cross-platform, open-source and more feature rich. SQL Server 2016 have few surprises too and with many new features & improvements.

The biggest news this year for database professionals was the announcement to make SQL Server available to run on Linux OS (the expected time for availability is mid of 2017, though). Apart from this, peoples at Microsoft have worked hard to bring many exciting features to SQL Server 2016 in the areas like Security, BI, Data Warehousing, Analytics etc. There are many improvements and new features added to the SQL Server Management Studio too. Microsoft has announced to release SQL Server 2016 on 1st of June 2016, around 13 days more. Once it’s available you can download and checkout the features. As announced earlier, the developer edition will also be free along with Express edition as usual. Now, if you are somebody who can’t wait for another 13 days period (like me 😉 ) then let’s see what are the options available with us. As you might already know, SQL Server 2016 RC3 (Release Candidate) is available for download, you can just download and install it in your machine. But for just 13 days,  the better option is to create a free Azure account with promotional $200 credit and checkout the features without harming to your existing system. Hopefully, I have clarified on the “Expiry Date” note.

The process is very clear and simple.

  1. Open azure.microsoft.com
  2. Following screen should appear
    1
    You can see $200 credit offer which will be valid for 30 days. Click Start now.
  3. You will be redirected to the Azure login page. Provide your existing Microsoft account details or create a new one using Sign up now.
  4. Once you are logged in, either click on Portal or just visit portal.azure.com
  5. You should be presented with something like following screenshot. Right side is your dashboard which you can customize as per your need and the left menu lists out the broad categories of Azure service offered. Click on the Virtual machines.
    2
  6. Click Add.
    3
  7. Find SQL Server in the list and click on it.
    4
  8. As you march forward, you’ll notice that further settings are getting opened as a new panel on the right side. Find SQL Server 2016 RC3 Evaluation on Windows Server 2012 R2 towards the bottom of the list and click on it.
    5.png
  9. Just click on the Create button for now.
    6.png
  10. You need to provide some more details about your virtual machine. Go ahead with filling Basic settings first. Note that I have used Azure Pass as subscription here but you will have the free trial option if you have signed up for free trial, otherwise your existing subscriptions.
    8
  11. At next step, you need to select the size which lists out the hardware details for each of the option. Choose a size from the recommended category only as these are the options which meets the hardware requirement specification that is published by the publisher (Microsoft in our case).
    NOTE: Prices mentioned in these options will be charged from your subscription account. If you are using Azure Pass or Free Trial, you can use those subscriptions to purchase this. For Free Trial, make sure not to agree for paying from your credit card. The subscription days should be truncated as per your balance in the subscription.
    9
  12. Next, you can customize various other settings such as Storage, Network etc. or just click OK to navigate to next step.
    10.png
  13. Then few more settings for your SQL Server instance like whether you want to use it inside VM, Virtual Network or available publicly over internet etc. You can customize according to your need or just click OK.
    11.png
  14. Then it will run a final validation and clicking on OK should start the deployment. You will be redirected the dashboard and you can see the progress there.
    12
  15. Once deployment is completed, you will be notified with a message “Deployment Succeeded”. Just copy the public IP from your virtual machine details and do the RDC (Remote Desktop Connection)
  16. Your VM is now ready with SQL Server 2016 RC3 installed in it. Type “SSMS” in the start screen and pin it to Task Bar or Start Screen and start using it.

That’s all for now. In my next blog posts, I will share some interesting features that are introduced with SQL Server 2016. Stay Tuned !

Thanks for reading 🙂
 

 

 

 

 

 

 

 

SQL Server: Convert Text to Upper or Lower Case


ConvertUpperCase

This small post is all about converting the text typed in New Query window in SSMS (SQL Server Management Studio)  to upper and lower case. There is nothing much to write except a keyboard shortcut but, I feel this feature deserves a separate post. This is particularly useful when you are formatting an existing SQL query, Stored Procedure, View, Trigger etc. which is not well formatted earlier.

Here are the shortcuts:
Select the text and

Press CTRL + Shift + U to convert to Upper Case
and
Press CTRL + Shift + L to convert to Lower Case

Hope this helps.
Thanks for reading.