Which of Microsoft's SQL Tools provide a Graphical User Interface?

Some people like GUIs more than scripting languages. They can still manage to work with SQL Servers.

Databases reside in the database servers. These servers may reside on your laptops, computers running with various operating systems such as Windows, Linux, and macOS. These servers may run in the cloud such as Azure. The tools, however, may not reside on the same platforms but allows you to connect to the various above-mentioned servers.

In brief, the tools are the following:

1. Azure Data Studio uses the operating systems, Windows, macOS, and Linux

This tool can run on-demand queries and save data as text, JSON, or Exel. It has a lightweight editor

2. SQL Server Management Studio (SSMS)

It is a full-featured GUI as well as it can run TSQL statements. It has tons of features.

It is a one-stop database management tool.

It can connect to the cloud, Azure SQL Database, Azure Synapse Analytics, etc.

Developers and DBMs of all skill levels can use this tool.

3. SQL Server Data Tools(SSDT) 

Using this you can build relational databases, Azure SQL databases, Analytical services, integration services, reporting services, etc

4. Visual Studio Code

Using the "mssql extension" for Visual Studio Code you can connect to SQL Servers

You can do scripting with a lightweight editor after connecting to SQL Server in Visual Studio.

You can find extensive references to complete articles on the following sister blogs:

http://hodentek.blogspot.com

http://hodentekMSSS.blogspot.com

More on GUI tools here:

https://docs.microsoft.com/en-us/sql/tools/overview-sql-tools?view=sql-server-ver15

How do you install SQL Server Managment Studio Version 17.8.1?

SQL Server Management Studio does not install when you install SQL Server 2017 (also in some previous versions). It is a separate install.

Kindly follow my previous post  to download the executable program for the installation.

Make sure you are not running SQL Server Management Studio (SSMS), if you are, close it.

Double clicking the execcuatable begins the installation as per the following screen shots. It may take a while (20 to 30 mts).

Close. Close SSMS and double click the executable to begin.

 Click Install.

It is done after a wait (20 to 30 minutes)

SSMS v17.8.1 and other components.

What is SQL Operations Studio?

Well there is a heavy-weight tool, SQL Server Management Studio that is installed as a separate application from SQL Server installation (used to be a part of the SQL Server installation).

SQL Operations Studio is a lighter tool. It is free and you can carry out database development operations using it. It is available for SQL Server on Windows, Linux and Docker, Azure SQL Database and Azure SQL Data warehouse on Windows, Mac or Linux.

The preview version became available in November of 2017.

If you would like to use it you need to download the version you want from here:

I downloaded the Windows version. You need to extract the files into a folder. 

The executable is sqlops.exe and it is indeed a small file.

What is a SQL Server Query Store and how do you enable it?

This is a new feature in SQL Server 2016 that provides with an insight into query plan choice and performance. As the query plan changes it helps you find the performance differences caused by the change.

Query store is automatic in that Query Store captures a history of queries, plans and runtime statistics for later review. It separates information chronologically by time windows so that you can view database usage patterns and monitor query plan changes that has taken place.

You need to enable the database to use this feature which an be done using SQL Server Management Studio as well as T-SQL Alter Database Set Option.

How do you turn it on using SQL Server Management Studio?

Since it is database related right click the database node after launching the SQL Server Management Studio and connecting to the server instance. The next image shows the result of right clicking the AdventureWorks 2014 database in SQL Server 2014.

QS_00.png

Click Properties to open the next window as shown.

QS_01

The Query Store Property has the following items:
General:
    Operation mode(actual)       default Off(disabled)
    Operation mode (Requested)   default Off but can be Read only or Read/write
Monitoring
    Data Flush Interval (Minutes)
    Statistics Collection Interval
Query Store Retention
    Max Size(MB)
    Query Store Capture Mode
    Size-based cleanup Mode
    Stale query Threshold(Days)

The previous window also shows the Current Disk Usage for database as well as Query Store.
As it is not enabled, the Query Store used is 0.0. You also have the option to purge the data.

You enable the Query Store feature by changing the Operation Mode(requested) from OFF to ON as shown.

QS_02

After it is enabled, a Query Store is added to the database as shown:

QS_03a.png

Hands-on Learning Event in Honolulu 1 : Introduction to Structured Query Language

The above course will be offered again for the fourth time (Oct 4 - Nov 3, 2016) as a non-credit course by the Pacific Center for Advanced Technology Training.

Course summary:

Databases, organized repositories of information, have become indispensable. Knowledge of databases is a must for professionals and in fact even more relevant since the emergence of Big Data in today's world.

In this introductory course you will learn about relational databases and the basics of Structured Query Language (SQL) including sorting; grouping result sets; using DDL, DML, DCL, and TCL. All SQL statements will initially be written for one table. Most practical, modern and relational databases will include a large number of tables and SQL queries have to access information from several tables. This course will then introduce you to querying more than one table. With this skill you will be able to query two or more tables in a database. This is a hands-on course which will take you from installing SQL Server 2012; learning the ins and outs of SQL Server Management Studio and of course a full dose of SQL. You will be both coding as well using Graphical Userr Interface during this training.


During each meeting the students will be assessed for their knowledge, competency and proficiency in SQL.


New this session:

Microsoft SQL Azure Database: SQL Server in Microsoft Cloud
Windows PowerShell for SQL Server
SQL Server 2016

My sincere thanks and best wishes to my students in the previous meets of this course.

How do you run a simple query in HeidiSQL?

It is really no different from running a query in SQL Server Management Studio. You need to have an instance of SQL Server running on your computer. Launch HeidiSQL and get connected to the instance.

You will be seeing this (different in your case) displayed in the HeidiSQL user interface. Either you can get connected to a session you created or a new one using the New button at the bottom of the UI.
 
RunAQuery01.png

You can connect to a single database by selecting it from the list as shown. Here Northwind was selected.

RunAQuery02.png
The Nov21 sessions gets changed and the changed sessions window opens as shown:


RunAQuery03.png

Click on Query to open a window for inserting query related statements as shown:
RunAQuery04.png

In the Query Window insert your SQL statement. For example a query such as the one shown:

SELECT TOP 50 [CategoryName]
      ,[ProductName]
      ,[ProductSales]
  FROM [Northwind].[dbo].[Product Sales for 1997]

 

Click Query and the response will appear below the query window as shown.

 
RunAQuery07.png

The tabbed window in the center has all the necessary tabs like the SAP SQLAnywhere Studio Central.

Can you add a Identity Column to an existing populated table?

Primary Key is a chief requirement for establishing relationship between tables. In case you have table which has no column providing the Primary Key which auto-increments then you should add this column.

Yes you can, you can use T-SQL code or SQL Server Management Studio. This post shows you how, step-by-step. I will be using SQL Server 2012 but the procedure is similar with probably some very minor variations in other versions.

The first thing is to make sure that when you save changes the table is not recreated. You can do this by removing the check mark from here (Prevent saving changes that require table re-creation).
The Options menu is accessed from Tools | Options... .

Creating a table with three columns:

I will be creating a new table in a database Manoa in an instance of SQL Server. Using the drop-down shown you can create a new table.

When you click New Table... above the next pane opens in SSMS with a default table name Table_1 which can be changed to the one you give after configuring it as shown.  Enter Fname under ColumnName, then Data Type comes up as nchar(10). If it does not you can select from the list. Place check mark for Allow Nulls (which means this column may have a value or may not, it is nothing unless something is entered).

When you close this window, the next window is displayed.

When you click Yes, the above table will be saved and you get the opportunity of overwriting the default name with one of your own as shown.

'

Herein table name was changed to Client. The table Client gets created which you can see in the Table node in Object Explorer. You may need to refresh the Table node (by right clicking and choosing Refresh). The next picture shows the columns of the Client table and the columns that you added.

Populating the table with 3 rows

Right click dbo.Client under the Tables node. From the drop-down select 'Edit Top 200 Rows '.  The next window will be displayed with just the first two with all Nulls. Enter the names by clicking on the Nulls under Lname; then under Fname and then under age. When you finish the 1st row, the second row is displayed again with Nulls. Repeat the above using names shown (you may user your own but of proper data type) for rows 2 and 3. We assume only 3 rows to exist in this table.  It can contain any number of rows. Click the Close(x) button.

The table gets saved with the values you entered. When you run the Select query as shown, you will see the three rows with values you entered. The column with no name shows just the row numbers.

Adding the Identity column

Right click dbo.Client table in Object Explorer in the Tables node. The following window with two panes open as shown. You may need to click the 4th row below the one with Lname 'Amanda' in the above to add the Id column.

For ColumnName enter 'Id'. For DataType enter or choose 'tinyint' and place check mark for Allow Nulls as shown. The bottom pane is automatically populated as shown.

In the bottom pane scroll down to the line shown, 'Identity Specification' .  Change from 'No' to 'Yes' indicating you want Id to be an identity column. The Identity Increment and Identity Seed gets filled with default values and you will notice that the check mark for Allow Nulls is removed as soon as you make these changes.

After making Id the identity column your table design is displayed as shown.

Again run a Select statement as shown and you will see the Id column as being added to your table.

Adding the Primary Key
 Now that you have added the Identity column, you will now designate that column to contain the Primary Key.

Open the table in design view.  Highlight the row containing Id and then right click that row to open a drop-down menu as shown.

Choose, Set Primary Key. The Id column will now show a 'Key' left to the column as shown.

After the above step, you will have set the Identity column to have the Primary Key. Expand the dbo.Client table as shown to see the 4 columns and the Key you added.

This completes the procedure to add an identity column to a pre-existing, populated table.

How do you detach a database from a SQL Server Instance?

There are a couple of ways you can do this. Let us say you are not into coding. Then the easiest way is to use the SQL Server Management Studio as described in this post.

Detaching a database
Let us say we want to detach database Feb6 from the Server Hodentek8\Regency Park
Observe that a query has fun on the database

detach01

Right click and click Detach.. in the Tasks pop-up menu.
Detach Database window appears as shown

detach02

Click OK. After some processing the detach window appears again indicating an error as shown.

detach03

In order to detach, the database should not be in use. Once the database is a target of a query it is in use.

Now close the query window. Click on Detach... menu item as before.
The Detach Database window appears as before.
Now click OK.
You do not get any message the detach window will close, but you can see that the database  Feb6 has disappeared from the Object Explorer.
You will find the detached files Feb6.mdf and Feb6_log.ldf in the following location on your computer:
C:\Program Files\Microsoft SQL Server\MSSQL11.REGENCYPARK\MSSQL\DATA

If you want to re-attach it with Windows PowerShell read here:
http://hodentekmsss.blogspot.com/2015/04/attaching-detached-database-in-sql.html

How do you design a Query using Query Editor in SQL Server Management Studio?

From scratch:
Open a Query pane in SSMS (from a database, for example) and right click to display the drop-down menu as shown.

DesignQueryinEditor

Choose the Design Query in Editor... option and the Query Designer window gets displayed with a Add Tables window. You can add as many or as few tables as you want and design your query. Holding control key down, select multiple tables and click ADD to add all the selected tables. If you suspect that the data has changed recently, you can click Refresh to use latest data.

DesignQueryinEditor2

Display a query you created in the designer

You can create a query in the query pane. Highlight the query, right click the highlighted portion and select Design Query in Editor... as shown.

QueryEditorUp
The query you designed gets displayed in the Query Editor as shown.

SimpleSelect
 Selecting the query and displaying it in designer will not work for more complicated queries.

I do not see the available databases toolbar item in SSMS, Why?

Missing Toolbar item? NO

The SQL Server Management Studio(SSMS) is highly context sensitive.

For example, unless a Query Window is open (by creating a new query or by clicking first in the query window) the menu item Query will not be displayed.

SQLQueryeditor00

When you open a query window (by using New Query  from database or SQL Server
instance node) then you will see the Query menu item.

SQLQueryeditor01

In a similar manner depending on how whether View | Toolbars | Sql Editor
is chosen or not you will not see the 'Available databases' item as shown in the
next image.

SQLQueryeditor01

How do I bring up the query designer in SQL Server 2012 Express?

Query Designer is a Graphic User Interface.

It is quite easy.
There are two ways you can do it.

Step 1. Open a Query pane by right clicking either the Server Node or right clicking one
of the user databases as shown.

 SERVER NODE

USER DATABASE NODE

Step 2. Once the query pane is open, click inside the query pane. The main menu
changes
to display the Query menu item as shown.

 

Step 3.Click Query menu item to display a drop-down list and click on Design Query in Editor or Ctrl+Shift+Q

 

This should bring up the designer as shown.

Step 3b. Another way is to right click inside the query pane and choose Design Query in Editor as before.