Query Azure SQL Data Warehouse in SSMS and SSDT

Azure SQL Data Warehouse in SSMS and SSTD

A while ago I wrote a blog post about Azure SQL Data Warehouse and Power BI which I explained how to install a new instance of Azure SQL Data Warehouse and how to visualise your Azure SQL DW data in Power BI. In this post I explain how to query an Azure SQL DW in SSMS and Visual Studio.

Requirements

Querying Azure SQL Data Warehouse from Visual Studio

Prior the latest release of SQL Server Management Studio (SSMS) 2016, the only available tool for querying an Azure SQL Data Warehouse was SQL Server Data Tools (SSDT) for Visual Studio 2013 or 2015. Here is how you can use SSDT 2015 to query an Azure SQL Data Warehouse:

  • Open SQL Server Data Tools 2015
  • Click “SQL Object Explorer” from View menu

SQL Server Object Explorer Visual Studio

  • Click “Add SQL Server”

Add Server to SQL Server Object Explorer Visual Studio

Connect to Azure SQL Data Warehouse in SQL Server Object Explorer Visual Studio

  • Enter “Server Name”
  • If you don’t recall server name then open a web browser and log into Azure portal
  • Click “SQL databases”
  • Click any desired Azure SQL Data Warehouse you created before. Make sure the database is “Online”

Azure SQL Data Warehouse in Azure Portal

  • Hover over the server name then copy it by clicking on “Copy” icon
  • Switch to Visual Studio and paste the server name
  • Change “Authentication” type to “Sql Server Authentication”
  • Enter “User Name” and “Password”
  • Select the Azure SQL Data Warehouse then click “Connect”

Connect to Azure SQL Data Warehouse in SQL Server Object Explorer Visual Studio

  • You successfully connected to the “Azure SQL Data Warehouse”
  • Expand the Server from “SQL Server Object Explorer” pane
  • Expand “Databases” then expand the database
  • Expand “Tables”
  • You can now see all of your data warehouse tables

SQL Server Object Explorer Visual Studio

  • To query the data warehouse right click on the data warehouse and click “New Query”

New Query in SSDT 2015

  • Write a T-SQL query and click on “Execute” button (image) or press “Ctrl + Shift + E”

Query Azure SQL Data Warehouse in SSDT 2015

  • You can also right click on an object from Object Explorer and script the object.

View Code in SSDT 2015

Note: Drag and drop from SQL Object Explorer to query editor is NOT supported in Visual Studio.

I bet you hate the “Ctrl + Shift + E” key combination as much as I do. To change the above key combination:

      • Click “Tools” menu
      • Click “Options”Visual Studio 2015 Options
      • Expand “Environment” then click “Keyboard”
      • On the “Show commands containing” type “execute”
      • Select “SQL.TSqlEditorExecuteQuery” from the list
      • Click in the “Press shortcut keys” box then press “F5”
      • Click “Assign”
      • You can see the current command that are currently using “F5” as shortcut
      • If you’re happy to set “F5” as SQL query execution shortcut click OKVisual Studio 2015 Options

Querying Azure SQL Data Warehouse from SQL Server Management Studio (SSMS) 2016

In previous version of SSMS (2008, 2012, 2014, 2016 CTP3) you could connect to an instance of Azure SQL Data Warehouse, you could also execute T-SQL queries after you connected to the database, but, it wasn’t fully functional. For instance, whenever you open a new query editor you get the following message in SSMS 2008, 2012 and 2014:

“Unable to apply connection settings. The detailed error message is: ‘QueryGovernorCostLimit’ is not a recognized option.”

Unable to apply connection settings. The detailed error message is: 'QueryGovernorCostLimit' is not a recognized option

and you get the following error in SSMS 2016 CTP3:

“’HAS_PERMS_BY_NAME’ is not a recognized built-in function name. (Microsoft SQL Server, Error: 104162)”

'HAS_PERMS_BY_NAME' is not a recognized built-in function name. (Microsoft SQL Server, Error: 104162)”

Another example of issues using prior versions of SSMS with Azure SQL Data Warehouse is that they were unable to show the database tables in “Object Explorer” pane which means you couldn’t right click and script the tables.

Azure SQL Data Warehouse in SSMS

The good news is that with the new release of SQL Server Management Studio 2016 (currently 13.0.15600.2) we can easily connect to an Azure SQL Data Warehouse, modify the objects, execute queries and see the tables in Object Explorer. Basically, we can do a lot in Azure SQL DW with SSMS as we used to do with any normal SQL databases.

  • Open SSMS 2016
  • Click “Connect” from Object Explorer
  • Select “Database Engine” for “Server type”
  • Enter Server name
  • Select “SQL Server Authentication” for “Authentication”
  • Enter Login and Password then click OK

Connect to Azure SQL Data Warehouse in SSMS

  • From Object Explorer, expand databases, expand an existing data warehouse, then expand “Tables”

Azure SQL Data Warehouse in SSMS 2016

  • As you can see there is a new icon for an Azure SQL Data Warehouse (Azure SQL Data Warehouse Icon). There are also new icons for tables:
    • Tables with “Round robin” distribution method use (Round robin Distributed Table in SSMS) icon
    • Tables with “Hash Distributed” distribution method use (Hash Distributed Table in SSMS) icon

Note: Check this out to learn more about Azure Data Warehouse distribution methods.

  • We can open a new query editor pressing “Ctrl+N” or by right clicking on the data warehouse and “New Query”

Query Azure SQL Data Warehouse in SSMS 2016

  • When we open a new query editor we get the following warning:

Query Azure SQL Data Warehouse in SSMS 2016

  • Click an option of your choice then write your SQL script and execute it normally

Query Azure SQL Data Warehouse in SSMS 2016

And, of course, you can drag and drop objects from the Object Explorer to the query editor.

You can also right click on an object from Object Explorer and script the object.

Script Azure SQL Data Warehouse Objects in SSMS 2016

Leave a Reply