Databases. SQL. Microsoft SQL Server Management Studio 18. An example of creating a simple query

Microsoft SQL Server Management Studio 18. An example of creating a simple query


Contents


Search other resources:

1. Microsoft SQL Server 2019 package. General information about Microsoft SQL Server Management Studio 18.

The SQL Server 2019 database management system is developed by Microsoft. This system provides ample opportunities for data storage and management of data of different types and volumes both on the local server and on the network.

As you know, the management of any database is performed using the Structured Query Language (SQL). This declarative programming language allows you to perform various operations on databases, which can be, for example, creating database objects, modifying objects, creating database access control systems, etc.

In Microsoft SQL Server, the base query language is Transact-SQL (T-SQL), which extends the capabilities of the standard SQL language. The T-SQL language implements the ANSI/ISO standard.

In order to use the features of SQL Server 2019, you must first install it from the official website www.microsoft.com. The installation process is not the subject of this topic. After installing SQL Server 2019, a number of programs and software tools are available to the programmer:

  • SQL Server 2019 Configuration Manager;
  • SQL Server 2019 Installation Center;
  • Microsoft SQL Server Management Studio 18;
  • Analysis Services Deployment Wizard 18;
  • SQL Server Profiler 18;
  • other.

In our case, to create database queries, you need to use the Microsoft SQL Server Management Studio (SSMS) software tool.

With Microsoft SQL Server Management Studio (SSMS), you can manage SQL Server objects (instances) and databases. The term “object” here means a table, a relationship between tables, a view, a report, etc. Object management includes the ability to configure, administer, monitor the database, and so on.

Using T-SQL queries in SSMS, you can perform many different operations on the database, namely:

  • creation of databases, tables, views, relationships between tables, etc.;
  • modification of the database structure, table structure, data in tables;
  • removal of database objects (tables, views, etc.);
  • other operations.

 

2. Creating a query in Microsoft SQL Server Management Studio 18

Queries can be created against a database hosted both on a local server and in a “cloud” environment. For learning about Transact-SQL topics, using your home computer as a local server is a good fit.

 

2.1. Connecting a local server of type Database Engine

You must first connect a local server. To do this, in the File menu, you need to call the command “Connect Object Explorer …”, as shown in Figure 1.

Microsoft SQL Server Management Studio. Command "Connect Object Explorer...". Connecting a local server

Figure 1. Command “Connect Object Explorer…”. Connecting a local server

This will open the “Connect to Server” window shown in Figure 2.

 

Microsoft SQL Server Management Studio. The "Connect to Server" window

Figure 2. The “Connect to Server” window

In the window that opens, the basic elements for a correct connection are set, which are listed below.

  1. Server type: – the server type. The following options (services) are offered here:
    • Database Engine – mechanism (tool) of databases. Allows you to work with databases;
    • Analysis Services is an analytical data subsystem used in decision support and business intelligence;
    • Reporting Services is a collection of local tools and various services designed to create and manage mobile and paginated reports;
    • Integration Services – a set of tools for building solutions for integration and data transformation at the enterprise level;
    • Azure SSIS Integration Runtime – Uses the compute infrastructure used by Azure Data Factory to integrate data across different network environments. In our case, select the Database Engine.
  1. Server name: – the name of the server is specified here. In our case, this is the computer name that is installed in the Windows system. This name can be viewed using the Properties command of the This PC utility. If you select a list (lower menu), then this name should appear first in the list.
  2. Authentication: – the choice of authentication method for accessing the database. The system offers a number of methods (Windows Authentication, SQL Server Authentication, and others). In our case, you need to select Windows Authentication.

After the connection method is selected, you need to confirm it (the Connect button). This will connect to the server. The Microsoft SQL Server Management Studio window will look something like Figure 3.

 

Microsoft SQL Server Management Studio. Object Explorer window showing the current connection to a Microsoft SQL Server database

Figure 3. Object Explorer window showing the current connection to a Microsoft SQL Server database

 

2.2. Creating a SQL Query

 An SQL query can be created for various databases listed in the Object Explorer utility. In the simplest case, you can create an SQL query by calling a sequence of commands (Figure 4)

File -> New -> Query with Current Connection

 

Microsoft SQL Server Management Studio. Create a request. The "Query with Current Connection" command

Figure 4. Create a request. The “Query with Current Connection” command

After executing the command, a query file (SQLQuery1.sql) is created, as shown in Figure 5. In this file, you can type the query text in the Transact-SQL language. This file can be stored using known open/save commands, its name can be changed, copied, etc.

Microsoft SQL Server Management Studio. The text of the file with the query in the T-SQL language

Figure 5. The text of the file with the query in the T-SQL language

Microsoft SQL Server Management Studio allows you to add multiple SQL query files with the same command. Thus, it is possible to form a set of queries that will be applied depending on the situation.

 


Related topics