Page 56 - MSDN Magazine, December 15, 2017

Figure 4 The Servers Dashboard for an Azure SQL Database Server— SQL Operations Studio Doesn’t Show the Backup Task for Azure SQL Database Because Backups Are Automatic
for the available schemas and objects. I was also pleased to discover I could use the familiar F5 keystroke to execute que- ries—something you can’t do in VS Code because so many tools and extensions have to share the keyboard shortcuts.
While you can edit data directly, you can’t yet edit the database schema visually. The Script as Create menu item is the closest you can get in these early days of SQL Opera- tions Studio to being able to modify schema in the database.
Customizing the Dashboard
Most of the features of the mssql extension for VS Code are in SQL Operations Studio. If you’ve used that extension already, or read my article (msdn. com/magazine/mt809115) or watched my Pluralsight course (bit.ly/PS_MSSQL), you may already be familiar with the many features of the SQL editor, as well as the available snippets for writing and executing queries and commands against a SQL Server database. The query results window, with its ability to export results to CSV, JSON or
Both of these groups will now show in the explorer window. I can drag the existing localhost onto the DockerServers Group bar and it will move it into that group, as shown in Figure 3.
Next, I’ll create a connection to one of my Azure SQL servers. There are a few paths, but because I want it in the AzureServers group, I can click on the green bar and choose New Connection from its context menu. Back in the connection window, you’ll see that the last option provided is Server group. That will be auto-populated with AzureServers and then I can fill out the rest of the information to connect to my Azure SQL Database server. After I’ve made the connection, I can see that server, its databases and the dashboard, which tells me it’s an Azure SQL Database (see Figure 4). Notice that the Backup and Restore tasks aren’t avail- able. Azure SQL Database backs up data automatically, so there’s no need to have an explicit task for triggering such operations. Restore works differently in Azure SQL Database, so I’m hoping to see a Restore task for Azure SQL Database in a future update.
Interacting with Data
Let’s work with some data! I added the tried-and-true Adventure- WorksLT database into my Azure SQL Database server because it’s available as a sample when you create a new server. Another bonus is that it’s prepopulated with lots of data. I’ll do something I often do in SSMS—manually edit some data from a table. Expanding Adventure- WorksLTSample again, I’ll right-click on the SalesLT.Customer table in the explorer, which displays a context menu filled
Excel files, is another feature that came from the extension. In SQL Operations Studio, a new addition to the query results window allows you to tap into some of the amazing extensibility in this IDE.
Let me demonstrate and then do a big reveal of something I’ve never done in SSMS as I complete this little demo. In preparation for this functionality, it’s time to work with the file system. Create a folder on your computer where you’ll save some of the SQL you’ll be writing.
Back in SQL Operations Studio, click the File Explorer icon in the Activity Bar and then the Open Folder button to open the folder you created. The File Explorer will display the folder and any files within (currently there are none).
You can create a new file inside the folder by clicking the “new file” icon to the right of the folder name in the File Explorer window. Be sure to give it a .sql extension. As shown in Figure 7, I’ve called mine TableSizes.sql.
I’m interested in some metadata about my AdventureWorksLT database: how many rows are in each of the tables and how much space are they taking up on my drive? Or, in this case, how much storage in my Azure account? Rather than spending hours trying to figure out how to write that query, thankfully there’s a snippet that gives me just what I need, plus a bit more metadata along the same lines.
In the editor window, start by typing SQL and you’ll see a list of the snippets. SQLGetSpaceUsed, shown in Figure 7, is the one I’m looking for. You can tab to auto-complete the snippet name and then hit enter to display the snippet’s SQL in the edi-
with functions, as shown in Figure 5.
I’ll choose Edit Data to open up the grid shown in Figure 6, which has a Max Rows dropdown defaulted to 200 (a safe bet when pulling data over the Internet. The other options are 1,000 and 10,000.) I edited the Title in the first row, which was saved automatically when I moved to another cell. To see if the table really updated to Azure, I then opened a new query window (CMD+N) and was happy to have IntelliSense help me type SELECT * from SalesLT.Customer, prompting me
Figure 5 The Context Menu for a Database Table
tor window. The snippet has a placeholder to type in a table name, but I don’t want to filter on a particular table; I want all of the tables. Scroll down to line 20 of the SQL and remove the following line:
WHERE TABL.name LIKE '%TableName%'
The query returns the following columns:
INDX.name AS index_name,
SUM(PART.rows) AS rows_count, SUM(ALOC.total_pages) AS total_pages, SUM(ALOC.used_pages) AS used_pages, SUM(ALOC.data_pages) AS data_pages, (SUM(ALOC.total_pages)*8/1024) AS total_space_MB, (SUM(ALOC.used_pages)*8/1024) AS used_space_MB, (SUM(ALOC.data_pages)*8/1024) AS data_space_MB
52 msdn magazine
Data