What are SQL Server and T-SQL

Preamble

The most widely used database management system (DBMS) worldwide is SQL Server. Small applications to large, heavily loaded projects can all be used with this DBMS.

Microsoft was the innovator of SQL Server. In 1987, the initial release was made. And the version that will be used in the current manual is version 16, which was released in 2016.

Since version 16, SQL Server has been a Windows-only database management system that is also accessible on Linux.

Such characteristics of SQL Server include:

• Performance: SQL Server operates quickly.
• Data encryption is offered by SQL Server for dependability and security.
• Simplicity: This DBMS is comparatively simple to use and administer.

SQL Server is one of the most popular database management systems (DBMS)

Every DBMS, including MS SQL Server, is built around a database. A database is a categorized collection of data. Though it’s not required, the database frequently corresponds physically to a file on a hard drive. Database management system (DBMS) or DBMS is used to store and manage databases. One such DBMS is Microsoft SQL Server.

Databases are arranged using a relational model by MS SQL Server. Edgar Codd created this database design back in 1970. Currently, it serves as a standard for database organization.

The relational model is based on the idea that data is stored in tables, which are made up of rows and columns. Each row holds a different object, and each column holds information about that object.

Using the primary key, it is possible to identify each row in a table. Any number of columns could be used as the primary key. We can refer to a specific table row by using the primary key. Therefore, a primary key cannot be the same for two rows.

The keys can link one table to another, forming links between the two tables. The table itself can also be considered a “relation.”

SQL (Structured Query Language) is used for interaction with the database

Through a unique API, the client (for instance, an external program) sends a SQL query. The client receives the execution result after the DBMS correctly interprets and executes the query.

SQL was originally created by IBM for the System/R database system. SEQUEL (Structured English Query Language) was thus the name of the language itself. Though historically the term SQL is frequently pronounced as a “sequel,” neither the database nor the language itself were subsequently officially published as a result.

The first database management system, called Oracle, was created in 1979 by Relational Software Inc. and used the SQL language. Due to the success of this product, the company was renamed Oracle.

Later, other SQL-based database architectures started to emerge. Because of this, the American National Standards Institute (ANSI) published its first standard in 1989. Since then, the standard has been periodically updated and supplemented. The last update took place in 2011. Even though there is a standard SQL language implementation, DBMS makers often use their own, slightly different versions.

There are two types of SQL language: PL-SQL and T-SQL

PL-SQL is used in databases such as Oracle and MySQL. T-SQL (Transact-SQL) is used in SQL Server. Because of this, it will be treated as T-SQL in the current tutorial.

The T-SQL command may fall under one of the categories below, depending on the task it performs:

1. A database, tables, indexes, stored procedures, and other objects are all created using the DDL (Data Definition Language) type of command.

These commands can be specifically referred to as follows:

• CREATE: creates database objects (the database itself, tables, indexes, etc.).
• ALTER: changes the database objects
• DROP: deletes database objects
• TRUNCATE: deletes all data from tables

2. DML (Data Manipulation Language). This category includes all of the commands used to manage data, such as those used to select, update, add, and delete data.

This type includes the following commands:

• SELECT: retrieves data from the database.
• UPDATE: updates data
• INSERT: adds new data
• DELETE: deletes data

3. DCL (Data Control Language). The commands that manage data access rights fall under this category. The following commands in particular are:

• GRANT: grants rights to access the data
• REVOKE: revokes data access rights

MS SQL Server 2017 Installation

MS SQL Server is available in various versions. It is first and foremost MS SQL Server Enterprise, a full release designed for use in actual projects. It is used on various hosting and database servers. However, it is only offered in the paid version, which is quite expensive and is only available after the trial period.

For straightforward applications, Express may also be adequate and is cost-free. It has the advantage of being able to be installed as a real server and used for real tasks; however, it has less functionality than the full version.

There is also MS SQL Server Developer Edition. It is a full-featured release that is only intended for development needs and contains all functionality and the full version of MS SQL Server Enterprise. In addition, this version cannot be deployed as a real server on actual projects. However, this version is the best choice for studying all of MS SQL Server’s mechanisms, so we will employ it.

So, let us install MS SQL Server 2017 Developer Edition. We will go to my.visualstudio.com for this purpose. Accessing it might require a Microsoft account. You must sign in using a Microsoft account in this instance.

We download the entire iso file with English set as the default language. Since the downloaded file’s extension is.iso, we must first unpack it before running the installer program. The installation wizard window will appear:

Here, we choose the first option, “Add features to an existing installation or create a new SQL Server stand-alone installation.” The installation options must then be set using a series of steps.

Let’s select the “Product Key” item. At this point, you must either specify one of the free issues or enter the key. Here, we choose the “Developer” release, and then click the Next button to move on to the next step.

The license agreement must then be approved. The “Feature Selection” step is then immediately followed. You have the option to choose the components that will be installed at this point. Here, we indicate each component while accounting for the amount of memory that is available:

The number of installation steps increases depending on the components chosen, where any settings must be made. In my case, every component has been chosen. Therefore, in the future, we’ll think about the scenario in which every component has been chosen.

Next, we must specify the name and ID of the active SQL Server entity on the step “Instance Configuration.”

Set the Default instance option for the name and MSSQLSERVER for the ID. The name of the instance by which we can access the server from third-party applications will be this.

then choose “Database Engine Configuration” for the first of the next two steps. We’ll make the current user the server administrator by clicking this button, Add Current User.

We will also make the current user an administrator for the Analysis Services feature on the following step of “Analysis Services Configuration”:

Leave the default settings in place for the following two steps. We will then add the current user in a similar manner on the step “Distributed Replay Controller.”

Leave all subsequent steps’ default settings alone, then select Install on the final installation screen:

At some point, MS SQL Server will be installed.

So, we have installed SQL Server 2017 and assigned the identifier “MSSQLSERVER” to it. It should be noted that you must ensure it is operational before connecting to it. To do this, you can open the service window. If it isn’t already running, we can start it up in the service panel and work with it from there.

Installing SQL Server Management Studio

We will install a unique administration tool called SQL Server Management Studio (SSMS) for simple database management and various MS SQL Server options and settings. This tool can be used to build databases and the tables within them, to write and run database queries, and much more.

Navigate to docs.microsoft.com to install SSMS. Links to versions for various locales can be found near the bottom of the page. Download the English version (or, if you prefer, select a localized version in a different language).

Even though MS SQL Server 2016 and even earlier versions of the server are covered, this version of SSMS, which has the version number 17, is still applicable.

Let’s launch the SSMS installation program after downloading it:

To install, click on the “Install” button.

After installation, we will find SQL Server Management Studio in the Start menu among installed programs in Microsoft SQL Server Tools 2017.

Let’s launch SQL Server Management Studio now. First, we will be asked to connect to the right server.

In the “Server name” field, select “Browse for more…” from the drop-down list. When a window appears, you must select the desired server:

In my case, the local computer has two servers: the Express release and the Developer release. However, I can tell from the name that the first item stands in for Express, so I need to choose the second one. I don’t have to choose that release if there is only one Express release installed on the local machine.

After you select a server, its name will be displayed in the “Server name” field. Then, in order to connect to it, you must click the Connect button:

When the connection works, the program will show all of the server’s databases and other files, such as:

Introduction to Transact SQL (T-SQL) using Microsoft SQL Server

About Enteros

​​​​Enteros offers a patented database performance management SaaS platform. It finds the root causes of complex database scalability and performance problems that affect business across a growing number of cloud, RDBMS, NoSQL, and machine learning database platforms.