SQL Operations Studio - Query Editor and Source Control

https://www.mssqltips.com/sqlservertip/5375/sql-operations-studio--query-editor-and-source-control/

Problem

In my previous tip, I gave an overview of SQL Operations Studio which was introduced by Microsoft to work on cross-platforms and it contains many exciting features. In this tip, we will explore additional features of SQL Operations Studio. 

Solution

Pre-requisites: Please read the previous tip to get information about installation, an overview of the menu items, connection dialog box etc.

SQL Operations Studio Overview

In this tip, I will be using the February Public Preview release of SQL Operations Studio.

You can find the detailed installation steps in the previous tip.

Once we launch SQL Operations Studio February Public Preview, we get the below welcome message.

Now let's see what it has to offer.

Connection dialog box - SQL Operations Studio

In the February release of SQL Operations Studio, if we connect to an instance, we do not need to enter the database name manually, instead it provides the available databases as a drop-down list.  The previous releases did not provide the database drop-down list.

We can also find the recent connections in the recent history tab. This enables us to quickly connect to the previous connection.

Once we connect to the instance, we can either right-click on the connection followed by new query or click on a new query from the Tasks.

A New Query window opens so we can write queries similar to SQL Server Management Studio.

We can see these options in the query editor window.

• Run: To execute a query
• Cancel: To cancel running query, if required.
• Disconnect: We can change the existing connection.
• Change Connection: Change database context from the drop-down list
• Explain: To show estimated execution plan.

Query Editor Overview in SQL Operations Studio

Snippets

SQL Operations Studio has an extensive library of snippets built in. A snippet is a code template that has the basics for a command. We can speed up writing queries with these T-SQL Code snippets.

As soon as you start typing in the query editor it starts giving a suggestion as shown below.

It shows details about the selected operation to right. For example, if we select sqlCreateDatabase, it shows what this does and the T-SQL code to create a new database.

Just hit the Tab key on the keyboard and it puts the code into a query editor.

As we can see, 'DatabaseName' is highlighted, so we just need to give the 'DatabaseName' and run it. This will create the database for us with the default settings quickly.

Go to Definition and Peek Definition

Developers and database administrators need to view object definitions such as column names, primary key, foreign keys, etc. while working with objects. One way is to expand the table from the drop-down in the object explorer or open a separate query window and get information about the object.

SQL Operations Studio makes it even easier to view the definition of an object. Select the object for which we want to view the definition and right click to get options.

Click on Go to definition or press F12 and a new query window will open with the definition of the object.

Here is the definition of the object:

If we do not want a separate window to open and need to view definition inside the same query editor, click on Peek Definition or Alt+F12.

We can then review and copy columns from the window. 

To close the peek window, just click on the X in the upper right side of the peek window.

It also saves a copy of the definition in the temporary directory.

We can open the directory and see the object definition in a .sql file.

Running Queries and Viewing Results

Now let's execute a query and view the output.

Running a query in SQL Operation Studio looks similar to SQL Server Management Studio. It gives results below the query in the results pane.

There are few highlighted options to look at:

• Explain: Once we click on Explain, it opens up the query execution plan. The format for the execution plans looks good with nice graphics.

If we move mouse over any of the operators, it gives details about the operator.

• Export Results: Along with the query results, we can see it provides options to export data in different formats like CSV, JSON and Excel format.

Save a JSON, export data in JSON format as shown below:

The final option is View as Chart. It represents the data in different chart formats.

We can choose from the below chart types and check out the following examples:

• Count
• Image
• Pie
• Bar
• Horizontal Bar
• Line
• Scatter
• Time series

Bar Chart Example

Pie Chart

If we move mouse over the chart, we can get the details as well.

We have the option to select the data direction (Vertical/horizontal) along with legend position.

We can also copy and save the chart image if required.

Create Insight opens up JSON window as shown below. We will talk more about it future tips.

Source Control in SQL Operations Studio

SQL Operations Studio comes with a Git source control manager. We need to install Git (version 2.0.0 or later) to use these features.

If Git is not installed on the system, we get the below message to download Git.

Also, in the Output Screen we can see that it looks for git in the location shown below.

Click on 'Download Git', it opens a web page to download the software.

Download the software and start the installation process.

Provide the installation folder.

Select the components, we will use the default setup options here.

Select the program shortcut for the start menu folder.

Choose the default editor used by Git.

Choose HTTPS transport for the backend.

Select the line ending conversions.

In the next screen, we can configure extra options (i.e. enable file system caching, enable Git credentials manager, etc.).

Start the installation process by clicking on Install.

Once setup is completed, we get the below notification.

Now launch SQL Operations Studio and click on Source Control.

Now select the folder to use for the Git repository and click on Initialize Repository.

Git integration is now complete and we can see Source Control as GIT.

We get multiple options by clicking on the ...  icon.

Next Steps

• Install SQL Operations Studio and explore the benefits.
• Read more about the February release of SQL Operations Studio/
• Read the release notes for SQL Operations Studio.