SQL Operations Studio - Query Editor and Source Control


By:   |   Updated: 2018-03-20   |   Comments   |   Related: More > SQL Operations Studio

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.

windows

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.

sql operations

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.

recent history

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.

new query

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

sql query

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.

sql query

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.

sql create database

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

database name

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.

go to definition

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

human resources

Here is the definition of the object:

select database

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.

peek definition
create table

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.

human resources

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

windows

Running Queries and Viewing Results

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

explain

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.
select top

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

full name
  • 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:

results

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

select top

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

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

Bar Chart Example

chart type

Pie Chart

pie chart

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

bar chart

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

bar chart

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.

sql query

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.

download git

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

terminal

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

git

Download the software and start the installation process.

general punlic license

Provide the installation folder.

select destination location

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

select components

Select the program shortcut for the start menu folder.

select start menu folder

Choose the default editor used by Git.

choosing the default editor
path environment

Choose HTTPS transport for the backend.

transport backend

Select the line ending conversions.

configuring conversions

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

enable file system caching

Start the installation process by clicking on Install.

installing

Once setup is completed, we get the below notification.

completing the git setup wizard

Now launch SQL Operations Studio and click on Source Control.

sql query

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

select folder

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

source control

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

sql operations studio
Next Steps


Last Updated: 2018-03-20


get scripts

next tip button



About the author
MSSQLTips author Rajendra Gupta Rajendra is a Consultant DBA with 9+ years of extensive experience in database administration including large critical OLAP, OLTP, Reporting and SharePoint databases.

View all my tips
Related Resources




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name
*Email
Email me updates

Signup for our newsletter

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.






download

























get free sql tips

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



Learn more about SQL Server tools