Tuesday, March 28, 2017

Why VSCode has replaced Management Studio as my default SQL Database and Query editor

Firstly let me start by stating that when I originally set out in an IT career I was heading down a developer path, and certainly had a number of developer type roles over the years, or found ways of continuing development projects while working in infrastructure roles..... probably why I have an interest in DevOps. So taking that into account it's no surprise that for my entire career I have always been comfortable working in code and not relying on GUIs. Even for all the years as a SQL DBA armed with SQL Management Studio (SSMS), yet I was always most comfortable working in TSQL rather than the wizards. Probably comes from the days of Enterprise Manager and Query Analyser (ahhhhh nostalgia). Now the MS Product Team has done a great job at improving the wizards in SSMS and making tasks as easy as they can be in the tools. I will also state that this post is by no means saying SSMS is dead because there are just some things where it is better positioned.

What I will cover in this blog post is why my go to TSQL editor and tool for general database work is now VSCode with the MSSQL extension.

Please don't take this as a statement that I have now uninstalled SSMS or Visual Studio with SQL Data Tools (SSDT) from my laptop, I wish, but I have always found those tool a bit bloated with memory consumption when all I want to do is connect to a database, run some queries, or make some basic changes. What I will show is why/how I now perform those tasks with VS Code, but for anything more in depth like designing SSIS packages or performance troubleshooting I still rely on the existing tools (for now).

Another factor that is driving this adoption of a text based editor is that a large amount of my work is now with Azure and other cloud solutions, and for the majority of the work you need to do it is largely console or script based.

Now that you know why I have arrived at this place, lets get into how I setup and use VS Code for this purpose. I look forward to healthy discussions with people around this because I am not a believer of the "one size fits all" approach to a tool set either so it is always great to hear what others use.

Setup and configure your environment
Here are the steps I use to setup my VSCode environment:

  1. Download and install VSCode https://code.visualstudio.com/download
  2. Open VS Code
  3. Press Ctrl+Shift+X (on windows)
    1. Alternatively use the View > Extensions menu item
  4. Locate and install the following extensions
    1. vscode-icons
    2. mssql
    3. powershell
    4. c#
  5. Configure the extensions
    1. From File > Preferences > File Icon Theme select "VSCode Icons"
      This will ensure that any files you open and access have nicely displayed icons to make your experience easier.
  6. Configure the environment settings
    1. From File > Preferences > Settings
      1. VS Code works in two setting modes, User and Workspace. User should be personal preferences and Workspace should be used for project specific settings that will ship with the repo.
        User settings are stored in the file C:\Users\\AppData\Roaming\Code\User\settings.json but you shouldn't have to edit that manually as the VS Code window provides the best method for working with these files.
    2. I don't change too many settings at this time from the default, but some to consider depending on your needs are:
      mssql.splitPaneSelection = "current|next|end"

      IntelliSense will help you complete the values if you need to see what is available.
  7. Now you should be ready to start working inside VS Code. However, I recommend reading the release notes when new updates are made as the developer community is extremely active improving VS Code and there is always new and useful features being added.

While VSCode has a built-in integrated terminal, I like the cmder tool for my terminal use. If you aren't familiar with cmder check it out, very versatile, run multiple terminals and languages. Best of all a Quake mode. 

Connecting to a database and executing SQL queries
There are many tricks and ways to work within VS Code but here is a simply walk through on the basics to get you started.
  1. Open VS Code if you haven't already
  2. You do not need to open a folder or save files just to run queries but it could be beneficial. Think of a folder like a Project/Solution, but in a simplier (faster) format. This works great with Git and cross platform collaboration.
    For the case of this walk through just create a new file (click New File on the welcome page)
  3. Without saving the file, lets make sure we are in the right language mode.

    Click the current language in the tray menu (e.g. Plain Text)

    This will open the command palette with prompts to select the language. Either browse or type to find your language and select it.

    Now the correct SQL language is shown in the tray menu


    Now the color coding and formatting, along with IntelliSense, will be suitable for SQL Server development.

    TIP: When you save a file then the language mode is automatically detected based on the file extention.
  4. Press Ctrl+Shift+P to open the Command Palette
  5. Type "mssql" and select the mssql: Connect option or press Ctrl+Shift+C

    TIP: Make sure your focus is in a file with the SQL language set and not any other areas of VSCode when you press Ctrl+Shift+C as otherwise it will open a console as per those keyboard shortcuts default.
  6. Select an existing connection profile or select the Create Connection Profile to create a new one. So lets create one.
  7. Follow the wizard filling out your server/instance, database (optional), authentication etc.

    Once you start to connect the status is shown in the tray menu

    Any errors connecting will be shown with an overlay

    Once connected VS Code will update intellisense dictionary and perform other operations set by the extension.
  8. Now write your query in the file
  9. When ready you can execute the query in a few methods

    Use the Command Palette and the MSSQL: Execute Query command.

    Right click in the editor and select

    or my favorite just simply press Ctrl+Shift+E
  10. The query results tab will open. By default this opens in a new split window column, or the next one if you have multiples. The idea here is so you can see the query and result all in one window.

    You can put the query results at the bottom of the screen which might be a more familiar view to those use to SSMS. To do this select the Toggle Editor Group Layout from the View menu, or press Alt+Shiftt+1.

    Now the results are below the query you executed.

    Alternatively you can also set the query results to display in the current split window column (e.g. new tab)

    So as you can see you can customise where the results are displayed just like in SSMS.

    Something to keep in mind is that a new result tab will open for every file you execute a query from, but if you re-run a query or a new query from the same file then it will use the existing results tab for that file.
  11. Now just like the query editor in SSMS, it will either execute the entire file contents or what you have selected. So like in this example it will just execute the selected query and not the entire file contents.

    This is why I like the keyboard shortcut Ctrl+Shift+E to execute queries because it becomes really quick to work from a file and execute different selected queries as desired.
Obviously some people will really miss Object Explorer to understand the schema's of databases they are not familiar with, but keep in mind VSCode is designed for developers and so typically you would have a folder that contains all the scripts for creating the database and therefore your schema to refer to, or you would be familiar with the schema. However, as we all know there are plenty of views you can easily query to get that data (because after all that is all Object Explorer does). 

Happy SQL Scripting.

Registering your SQL Server connections
VS Code has a json based configuration system. SQL Connections can be saved in the User Settings file, think along the lines of "Registered Servers" in SSMS. I have already briefly touched on how to create a new profile when you connect. However here is how to register them ahead of time and manage existing connection profiles.

Keep in mind though, these connections are not unique to a project/solution/folder, they are unique to your user settings. So you make sure you give them meaningful names to easily identify which databases/projects they belong to.
  1. Press Ctrl+Shift+P to open the Command Palette
  2. Type "mssql" and select the MSSQL: Manage Connection Profiles option

  3. The Command Palette will then prompt you with some more options.

    Create: This will walk you through creating a new profile via the Command Palette prompts
    Edit: This will open the User Settings JSON file and allow you to manually edit the connection profiles. NOTE: Passwords can be saved in an encrypted form but are not stored in this file for security.
    Remove: This will walk you through removing an existing profile via the Command Palette prompts

    This is an example of the JSON configuration provided with the Edit option.

  4. Once you have configured the profile you can then simply select it from the list provided under the MSSQL: Connect command.

VS Code official site https://code.visualstudio.com/
VS Code opensource repo  https://github.com/Microsoft/vscode

Legal Stuff: The contents of this blog is provided “as-is”. The information, opinions and views expressed are those of the author and do not necessarily state or reflect those of any other company with affiliation to the products discussed. This includes any URLs or Tools. The author does not accept any responsibility from the use of the information or tools mentioned within this blog, and recommends adequate evaluation against your own requirements to measure suitability.


  1. Un par de cientos de quid te comprarán un gran reloj por derecho propio sin tener que recurrir a la compra de una falsificación.comprar relojesCiertos relojes Seiko o pequeñas marcas como Smiths o Precista de Timers tienen un gran sucesor y con razón.más barato rolex relojesTienen movimientos mecánicos buenos y duraderos y ganarán durante muchos años.

  2. A few hundred quid will buy you a great watch in it's own right without having to resort to buying a fake. mini for watch Certain Seiko watches or small brands such as Smiths or Precista from Timefactors have huge following and rightly so. replica BREITLING watchesThey have great, durable mechanical movements and will serve for many years.

  3. Since 1848, Omega watches built a reputation of being the ultimate in luxurious elegance and sporty sophistication. In 1931, replica watches salethe brand entered the Geneva Observatory trials, replica omega watches salecompeting in several categories designed to test the rigor and mechanical reliability of the Omega timepiece. The brand won all six categories and thereby adopted the slogan, "Omega, exact time for life." In 1965, NASA selected Omega to be the official watch used in training and space exploration. A true giant in name and reliability, Omega produces elegant watches that are impervious to changes in fashion. At , we offer a variety of Omega watches for sale — at the lowest prices online. When you are ready to buy an Omega watch, browse our selection that includes the: Seamaster, Speedmaster, 1957 Trilogy, DeVille, Planet Ocean, and Constellation.

  4. Buy a discounted IWC watch from this Time and get free shipping. Large selection in stock. replica watches UKIWC watches, is an iconic brand. The IWC Portuguese, replica IWC watches sale a collection that includes the IWC Portuguese automatic and IWC Yacht Club chronograph is legendary. IWC Schaffhausen is also known for the IWC Aquatimer, Top Gun, Big Pilot watch, Mark XVII, Pilot's Watch, Spitfire chronograph and their Big Pilot Watch, the St-Exupery.

  5. Cartier has recommended many classic and successful watch collections, such as fake cartier watches, Cartier Santos and Cartier Ronde and so on. Every Cartier watch has its special charm, and Cartier watches are many people's dreams. Our replica santos dumont watchesshop provides different Cartier replica watches at very low prices. Just enjoy shopping here.

  6. Cartier watches boast a large variety of design and functionality, replica cartier ukyet maintain the utmost quality and sophistication in every series. Crafted from the finest steel, replica calibre de cartier carbon diver watchesrose gold, yellow gold, and white gold, and utlizing the highest grade of diamonds worthy of the Cartier name, Cartier has dominated the industry of elegant watches. Mens and ladies watches alike are assured attention to detail internally and externally, and the namesake itself is a milestone in the world for which the standard of reputation alone is reminiscent of the product.

  7. Giuseppe Zanotti was born replica sneakers in San Mauro Pascoli, replica jimmy choo an Adriatic town with a long tradition of shoemaking. He has worked in luxury footwear for women for over twenty years, beginning as a design apprentice before making a name for himself as a footwear designer for Italian and international companies. In 1994, Giuseppe Zanotti decided to try his hand in his own business. There followed a steady rise to distinction which fully justifies the international acclaim he now enjoys.

  8. replica rolex watches are crafted from the finest raw materials and mini for watchassembled with scrupulous attention to detail. Every component is designed, developed and produced in-house to the most exacting standards.

  9. Best cheap Soccer Jerseys Shirts Wholesale Shop for Custom Team Soccer Jerseys Online,cheap xherdan shaqiri JerseysFCB Jerseys,cheap Euro 2020 Kit Top.

  10. Alexander McQueen est une maison de couture emblématique, alexander mcqueen chaussures francereprise par Sarah Burton, pas cher femme alexander mcqueenqui continue à travailler avec la marque pour développer une gamme de prêt-à-porter et de prêt-à-porter haut de gamme.

  11. I am upbeat to discover this post exceptionally helpful for me, as it contains part of data. I generally want to peruse the quality substance and this thing I found in you post. The latest Tweets from Celebrity birthdays with 22 million users visit our website each month.

  12. replica Cartier is the renowned French jeweller and watchmaker with a history of pioneering design and a drive for excellence. The first references to replica Ballon Blanc de Cartier watches wristwatches date back to 1888.