Getting Started with Query History and Table Designer in Azure Data Studio

By:   |   Updated: 2022-05-18   |   Comments   |   Related: > Azure Data Studio


Problem

Developers and DBAs run multiple queries while connecting to a SQL Server, so how can you view a history of the queries executed? Similarly, you might be familiar with table designers in SSMS. How can we design tables graphically using Azure Data Studio to work with SQL Server or an Azure database?

Solution

Azure Data Studio can be installed on Windows, Linux, and macOS for both on-premises and cloud databases. Azure Data Studio or ADS offers the following valuable features:

  • Cross-platform support
  • Modern editor experience including IntelliSense, Source Control, SQL Code Snippets
  • Built-in graphical result sets and customizable server, database dashboards
  • Extensions support enhancing tool features
  • Integrated Bash, PowerShell, sqlcmd, BCP, and ssh terminal
  • Jupyter Notebooks, HDFS integration, and external data wizard

Let's explore how Azure Data Studio can help retain query history and design tables graphically for SQL Server and Azure databases.

Azure Data Studio Query History Extension

Azure Data Studio marketplace provides several extensions for enhancing its features and function. These extensions are free to download and install.

I am working with the latest Azure Data Studio version 1.35.1, released on March 17, 2022.

azure data studio version

You can navigate to Microsoft and download the latest version of Azure Data Studio before proceeding with this article.

Launch ADS and click on the Extension icon as highlighted in the following screenshot. It shows you the marketplace with a list of available extensions. These can be Microsoft recommended or third-party custom extensions. If you have installed any extensions, it shows under Installed.

azure data studio extensions

Query History Extension

Azure Data Studio provides a Query History extension to add a Query History panel that captures the past executed queries. ADS recommends installing this tool.

azure data studio Query History Extension

Now, search for the Query History extension in the extension marketplace and click on it and click Install.

azure data studio Query History Extension

Note: Query History extension is in the preview phase and it might change significantly before its general availability release.

It quickly adds the query history extension to Azure Data Studio. Once installed, the user can either disable or uninstall it.

azure data studio Query History Extension

To see the query history, go to the View menu and click on Query History as shown below.

azure data studio Query History Extension

Let's connect to the SQL Server instance and test the query history extension by executing a few sample queries. The query store tab shows the query, connection information, and the timestamp at which the query was executed.

azure data studio Query History Extension

You can select any query from the query history and right-click on it to get a few options.

azure data studio Query History Extension
  • Open Query: click on Open Query and it opens a new query window with the query and connection.
  • Run Query: it opens and runs the query in a new query window on the existing database connection.
  • Delete: if you want to remove a query from the query history, click on the delete option.
  • Clear All History: if you want to reset the query history, click on Clear All History, and it clears all queries from history.
  • Pause Query History Capture: suppose we want to pause capturing query history temporarily. We can click on the option – Pause Query History Capture. If you paused the capture process, you get another option – Start Query History Capture to recapture query histories.

You can also use shortcuts to clear all history, pause or resume query history capturing, maximize the query history window and close it.

azure data studio Query History Extension

Table Designer in Azure Data Studio

Developers need to create new tables in SQL databases. If you are not familiar with T-SQL, table designer in SQL Server Management Studio is useful for deploying tables with constraints, indexes, and identities. However, with the Azure SQL Database, how do we create tables.

It is helpful for developers to learn to write CREATE TABLE statements with customized objects, i.e., constraints and indexes, but Azure Data Studio provides a table designer for designing tables and viewing the T-SQL script.

Connect to a SQL instance in Azure Data Studio, expand Databases, and right-click on the Tables folder.

Azure Data Studio Table Designer

Click on New Table, and it opens a new window with the following sections.

Azure Data Studio Table Designer
  • Table name: specify the table name.
  • Columns: specify the required columns, their data types, length, precision, allow null or not, and the default value.
  • Foreign key: if you want to create a foreign key, click on it and define the key.
  • Check constraints: you can put check constraints to specify valid values for the columns.
  • Indexes: you can define indexes and their properties in the indexes section.
  • General: in the general section, you can define the table name and choose schema and description.
  • Scripts: this shows the equivalent T-SQ script once you change the table properties, columns, and indexes.

Let's define the table "DemoTable" and add a new ID column with an integer data type. Once you add the column, you can modify the column properties as below.

  • Whether it is a primary key column
  • Does it allow NULL values
  • Is there a default value for this column
  • Is the column an identity - if yes, specify the identity seed and increment
Azure Data Studio Table Designer

Let's add the column as a primary key and enable the identity property. As shown below, the table script shows the changes for the primary key and identity columns.

Azure Data Studio Table Designer

Similarly, you can add required columns and change their properties. For this demo, I added the columns [FirstName] and [LastName].

Azure Data Studio Table Designer

For deploying the new table in the SQL database, you can use the following ways.

  • Copy the script and run it in a new query window
  • Click on Publish Changes
Azure Data Studio Table Designer

Let's click on publish changes, and it shows the Preview Database Updates window.

Azure Data Studio Table Designer

You can generate the scripts from the option – Generate Script or click on Update Database to deploy it directly. It creates the new table in the designated database and shows a pop-up that the changes have been successfully published.

Azure Data Studio Table Designer

Refresh the tables folder in Azure Data Studio to see the new table.

Azure Data Studio tables

If we want to make changes to the table, we can also do that in Azure Data Studio. For example, suppose I want to add a non-clustered index on the [FirstName] column. For this purpose, right-click on the table and choose Design.

Azure Data Studio tables

It opens the table-designer window that we used earlier to create the table.

Azure Data Studio Table Designer

Let's go to the indexes section and add a non-clustered index on the [FirstName] column. You can enter an index name in the index properties and choose its properties as below.

  • Enabled or disabled
  • Is it a clustered index
  • Is it a unique index

Choose the column from the drop-down and specify if it is ascending or descending. It modifies the create table script as well.

Azure Data Studio Table Designer

Click on publish changes, verify the change and choose Update Database.

Azure Data Studio Table Designer

Refresh the table and expand indexes to see that the new non-clustered index is created.

Azure Data Studio indexes
Next Steps



Get Started Now - Click here to get your free 14 day trial of SolarWinds Database Insights




get scripts

next tip button



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

View all my tips


Article Last Updated: 2022-05-18

Comments For This Article





download














get free sql tips
agree to terms