{"id":76365,"date":"2017-12-18T10:47:55","date_gmt":"2017-12-18T10:47:55","guid":{"rendered":"https:\/\/www.red-gate.com\/simple-talk\/?p=76365"},"modified":"2021-05-11T15:57:18","modified_gmt":"2021-05-11T15:57:18","slug":"walk-around-sql-operations-studio","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/databases\/sql-server\/tools-sql-server\/walk-around-sql-operations-studio\/","title":{"rendered":"A Walk Around SQL Operations Studio"},"content":{"rendered":"
\n

NOTE: Microsoft has changed gears on this tool. Instead, they have released Azure Data Studio for Windows, macOS, and Linux. You can download it here<\/a>.<\/p>\n

<\/em><\/div>\n

 <\/p>\n

As a data person you\u2019ve likely looked, with some envy, at the cool new multiplatform tools Microsoft has released for your developer coworkers. Visual Studio Code<\/a>, or VS Code, is an open source application which will run on Windows, macOS, and Linux, allowing software development for a variety of languages. You\u2019ve probably thought, \u201cHey, where\u2019s OUR multiplatform tool for working with data?\u201d Well, have I got good news for you!<\/p>\n

Microsoft has just released SQLOPS \u2013 SQL Operations Studio \u2013 in preview mode. Built on top of VS Code, it is designed to manage your SQL Server from multiple platforms. SQLOPS runs on Windows, macOS, and Linux. Best of all, it is absolutely free to use.<\/p>\n

Downloading SQLOPS<\/h2>\n

Before you rush off and download it, there is a word that needs to be stressed. PREVIEW<\/strong>. The tool is essentially in beta. This means there will be features that might not be quite complete, and you will likely encounter bugs. You are using this at your own risk, and you should expect frequent updates to the product. As a result, installation on your production systems is probably not a good idea.<\/p>\n

As you will see, there are some features that make it worth looking at now. Using the tool is simple, and low risk, as technically there is no true install. You simply download it, extract the files, and start using it. To download, just go to https:\/\/docs.microsoft.com\/en-us\/sql\/sql-operations-studio\/download<\/a> or you can use the shortcut http:\/\/bit.ly\/sqlops<\/a>. When you do, you\u2019ll see this on the page:<\/p>\n

<\/p>\n

From here, select the version appropriate to your operating system. Once it\u2019s downloaded, installation is very simple.<\/p>\n

Installation on a Mac<\/h2>\n

To install on a Mac, be sure to navigate to the site and download the file. Once the file is downloaded, simply select Open in Finder<\/em> and unzip the file.<\/p>\n

<\/p>\n

With the downloads folder open, simply drag and drop (or copy and paste) into the Applications<\/em> folder.<\/p>\n

<\/p>\n

Then, just double click on the SQL Operations Studio icon to open it. (Note this Mac has the Show file extensions<\/em> option enabled, yours may not show the .app<\/strong> extension at the end. It\u2019s still the same program.)<\/p>\n

<\/p>\n

And there you go, you are ready to connect to your SQL Server!<\/p>\n

<\/p>\n

Installation on Windows<\/h2>\n

Installation on Windows is even simpler. First, you will create a folder on your hard drive that will hold the application. Unlike most windows applications, SQLOPS does not have a traditional installer. On this Windows installation, C:\\SqlOps<\/strong> was created.<\/p>\n

Once you have it downloaded, just go to your downloads folder. Double click on the sqlops-windows-0.23.6.zip file (note the file name will change as new versions are released, use the file name that you downloaded). It will open another folder with the contents of the ZIP file.<\/p>\n

<\/p>\n

Drag and drop (or copy and paste) the sqlops-windows<\/strong> folder to your C:\\SqlOps<\/strong> (or wherever you created your target folder). Once there, navigate to the sqlops-windows<\/strong> folder.<\/p>\n

<\/p>\n

Double-click sqlops.exe<\/strong> to run it. If the connection window doesn\u2019t appear when you open SQLOPS, simply click on the New Connection<\/em> icon.<\/p>\n

<\/p>\n

Fill out the connection information and click Connect<\/em>. Unlike other tools, this version doesn\u2019t provide a dropdown list of databases, so you must type in a valid database name.<\/p>\n

<\/p>\n

Installation on Linux<\/h2>\n

Installation on Linux turns out to be almost as simple. The download page has a bash script you can run to extract the files once they have been saved to your system. Be aware that depending on the version of Linux you run, there may be additional dependencies you have to install. Due to the wide variety of Linux distributions there are too many combinations to attempt to document here; just follow the instructions on the download site for your version of Linux.<\/p>\n

The SQLOPS Interface<\/h2>\n

By this point, you\u2019ve installed SQLOPS on your operating system and have filled out the connection information. Once you are connected to your server, you should see something like this (note the numbers were added to enable discussion of the layout):<\/p>\n

<\/p>\n

The area denoted by the red number 1 is the Servers<\/em> pane, also known as the Object Explorer<\/em> in some online documentation. Expanding the server branch (in this example localhost<\/em>), then Databases<\/em>, will present a list of the databases. The Object Explorer<\/em> serves the same function here as it does in SSMS (SQL Server Management Studio). It provides a way to navigate the various objects on your SQL Servers, drilling down and providing menus to launch other operations like editing data or executing queries.<\/p>\n

In SSMS, when you connect to a server the area to the right is empty. With SQLOPS though, you can see a nice dashboard. The area in green, number 2, is the dashboard header. It gives an overview of the server including the SQL Server version, edition of SQL, computer name, and OS (Operating System) version.<\/p>\n

The next area, number 3, are shortcuts to common tasks. At the server level, the three most common tasks are restoring a database, configuring your server, and creating a new query. As later versions of SQLOPS are developed, you may see other tasks in this area as new functionality is brought to the application. Below the tasks pane is area 4, which shows the backup status of your various databases.<\/p>\n

To the right is area 5, the search pane. This provides a quick way to find various objects on your server. Just begin typing and it will narrow the list of databases to match what you are keying in.<\/p>\n

Finally, the very bottom is area 6. This shows a graph comparing the sizes of your various databases. This screen capture is cut off due to size limitations, but it just allows you to get a quick at a glance comparison of size between your various databases.<\/p>\n

On the very left is a big gray bar with some icons. Let\u2019s take a quick look at it.<\/p>\n

<\/p>\n

The top brings up the panel you\u2019d already seen, the Servers<\/em> (aka the Object Explorer). The next button down is the Task History<\/em>. If you had performed any backups, restores, or other similar tasks you could reference them in this area. Below this is the Explorer<\/em> pane. The Explorer<\/em> pane has a list of all open files in the SQLOPS editor. It offers some useful options, so let\u2019s take a closer look at it.<\/p>\n

<\/p>\n

The Explorer<\/em> pane contains list of all open files. To the right of the file name is the full path to the file. Right clicking on the file provides a rich context menu. Most of the options, such as Save<\/em> and Close<\/em>, are pretty obvious in their functionality but a few do warrant further explanation.<\/p>\n

Open to the Side<\/em> opens the file in split screen, so you can compare two files side by side in SQLOPS. Reveal in Explorer<\/em> opens up an Explorer window in your computers operating system. Open in Terminal<\/em> opens up a terminal window in the lower half of the SQLOPS application, by default it is PowerShell, but it can also be changed to a Command Prompt (CMD), or a BASH terminal. This may vary by operating system, for example on macOS you wouldn\u2019t have the option for a CMD window. You can also open a terminal window by using the View menu in the toolbar, and selecting Integrated Terminal<\/em>.<\/p>\n

The Search<\/em> pane brings up a pane that can be used to search, or search and replace, text in the current editor window. Below this is the Source Control<\/em> pane, this allows you to manage your various files within your source code control system.<\/p>\n

Way down at the bottom is a big gear icon. This allows you to alter the various settings of SQLOPS, such as the keyboard shortcuts, color theme, and more.<\/p>\n

Running Queries<\/h2>\n

At this point you should have a good overview of the SQLOPS interface. Let\u2019s start doing some actual work and do one of the most common things, running a query. From the Servers<\/em> pane, expand your databases, then expand one database, then the tables for that database. For this example, I\u2019ll be using the WideWorldImportersDW database.<\/p>\n

NOTE: WideWorldImporters is a new sample database introduced with SQL Server 2016. If you don\u2019t have it, you can download the backup file<\/a> or source code<\/a> from GitHub. There are multiple databases as part of the sample, for this article we\u2019ll be using the data warehouse version (WideWorldImportersDW).<\/p>\n

Right click on a table, and chose the Select Top 1000<\/em> option.<\/p>\n

<\/p>\n

If you\u2019ve worked with SSMS, the following screen should look familiar. Like SSMS, the query is at the top, and the results at the bottom. Unlike SSMS, which has the Results<\/em> and Messages<\/em> on separate tabs, SQLOPS places the messages just below the results. From here you can make changes to the query and re-run it, save your query, and perform other actions you\u2019d normally do with a SQL file. There are two areas though that should be called out. The first is the Explain<\/em> button, highlighted in red in the above image.<\/p>\n

<\/p>\n

Clicking Explain<\/em> brings up the query plan. While this is of course possible in SSMS, SQLOPS makes it much more convenient to run. Hovering over any item in the query plan brings up more details about that item.<\/p>\n

<\/p>\n

The other area you should notice is to the right of the results, highlighted in green in the image showing the query results. From top to bottom, the first three are Save as CSV<\/em>, Save as JSON<\/em>, and Save as Excel<\/em>. This is a great addition, allowing you to easily save your data for other purposes.<\/p>\n

The final option is View as Chart<\/em>. For the data in the previous SELECT 1000<\/em> query the option won\u2019t provide anything meaningful, so let\u2019s look at an example that will. In the Servers<\/em> pane, right click on your database and pick New Query<\/em>. Then, paste in the query below, assuming you are using the WideWorldImportersDW sample database.<\/p>\n

SELECT d.[Calendar Year]\r\n     , SUM(s.[Total Including Tax])\r\n  FROM [WideWorldImportersDW].[Fact].[Sale] s\r\n  JOIN [WideWorldImportersDW].[Dimension].[Date] d\r\n    ON s.[Invoice Date Key] = d.[Date]\r\n GROUP BY d.[Calendar Year]\r\n ORDER BY d.[Calendar Year]\r\n<\/pre>\n
Run the query, and view the results at the bottom. Now press the chart button<\/strong>, as indicated by the giant red arrow.<\/p>\n
<\/p>\n
Ta-da! A chart which neatly summarizes the results of your query appears. If you expand the Chart Type<\/em> on the right, you\u2019ll see many different charts are available. Play with it to see what the results are for the various types. One thing to be aware of, the chart viewer works best when there are a limited number of categories, as in this example, the years. If you have a lot of categories, for example hundreds of customers, the chart will be unreadable and not very useful.<\/p>\n
<\/p>\n
Help Writing Your Queries<\/h2>\n
All of us have SQL syntax we use on a regular basis. But what about times when you are writing SQL queries that you don\u2019t normally run? Often, you wind up doing internet searches to remind yourself of the correct syntax, or hunt for previous scripts where you used a similar query. Well SQLOPS has a great tool to help you out. Return to the Server<\/em> pane, right click on your database, then pick New Query<\/em>. In the empty editor, type sql<\/strong>, and see what pops up!<\/p>\n
<\/p>\n
SQLOPS has an extensive library of snippets built in. A snippet is a code template that has the basics for a command. Let\u2019s narrow it down some, keep typing adding Se<\/strong> after the code you have already typed.<\/p>\n
<\/p>\n
As you can see, the snippet popup narrows itself down to those options with SE in the name. Since sqlSelect is already highlighted, just hit the TAB<\/strong> key on your keyboard.<\/p>\n
<\/p>\n
You\u2019ll see TableOrViewName<\/strong> is highlighted. Just type City<\/strong>, and you\u2019ll see it updates both the TableOrViewName<\/strong> in the comment, as well as in the query itself. Hit the TAB<\/strong> key, which will highlight SchemaName<\/strong>. Simply type in Dimension<\/strong>, and it will also update both the comment and the query. From here you can edit your query normally, adding to the WHERE<\/strong> clause (or removing it), adding a GROUP BY<\/strong>, and whatever else you\u2019d like to do. Then, you can run your query. This provides a very efficient way of writing your queries, especially for query types you don\u2019t work with on a regular basis.<\/p>\n
Peeking into Your Database<\/h2>\n
When writing queries, a common need is to know more information about the objects you\u2019re working with. Let\u2019s return to the previous example, where we generated a SELECT *<\/strong> query. Now you want to limit the query to a few columns. Sure, you could jump over the Server pane, and navigate down to the database, then the table, then the columns. It would be much more efficient though if you could jump right to the definition of the object. That\u2019s where the peek functionality comes into play.<\/p>\n
In the query editor, put your cursor on the table name, and right click. On the menu, pick Peek Definition<\/em>.<\/p>\n
<\/p>\n
A small window opens under the item you peeked. The definition for the table appears within. You can copy columns out of the window, as well as review the definition. Peek works for more than just tables, it can also be used with stored procedures, functions, and other similar objects. To close the peek window, just click on the x<\/em> in the upper right of the peek window.<\/p>\n
<\/p>\n
The other option is Go to Definition<\/em>. This will open a new tab with the definition of the object (in this example the City table). From there you can alter, run, or anything else you\u2019d normally do in a SQL window.<\/p>\n
Summary<\/h2>\n
In this article, we explored SQLOPS \u2013 SQL Server Operations Studio. SQLOPS is the new open source, multiplatform tool for working with SQL Server. We began by seeing how easy it is to install, then showed the interface. Next, we looked at some common tasks such as editing queries, then reviewed the cool snippets feature. Finally, we looked at how to \u2018peek\u2019 into your database. Although still in preview, SQLOPS already has many features that make it a worthy addition to your toolkit.<\/p>\n
If you\u2019d like to see the actual code behind SQLOPS, you can get it from Microsoft\u2019s github site, https:\/\/github.com\/Microsoft\/sqlopsstudio<\/a><\/p>\n","protected":false},"excerpt":{"rendered":"
SQL Server Management Studio has been the default tool for working with SQL Server for over a decade. It\u2019s not, however, compatible with any operating system outside of Microsoft Windows. SQL Operations Studio (SQLOPS), the open source program originally called \u2018Project Carbon\u2019, is now available for users of Linux and macOS as well as Windows. Robert Cain demonstrates the features of SQLOPS.…<\/p>\n","protected":false},"author":316962,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[143532],"tags":[],"coauthors":[52865],"class_list":["post-76365","post","type-post","status-publish","format-standard","hentry","category-tools-sql-server"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/76365","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/users\/316962"}],"replies":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/comments?post=76365"}],"version-history":[{"count":5,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/76365\/revisions"}],"predecessor-version":[{"id":83763,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/76365\/revisions\/83763"}],"wp:attachment":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media?parent=76365"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/categories?post=76365"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/tags?post=76365"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/coauthors?post=76365"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}