What are SQL Server and T-SQL

SQL Server the most popular database management systems (DBMS) in the world. This DBMS is suitable for a variety of projects: from small applications to large highly loaded projects.

SQL Server was created by Microsoft. The first version was released in 1987. And the current version is version 16, which was released in 2016 and will be used in the current manual.

SQL Server has long been exclusively a database management system for Windows, but since version 16, this system is available on Linux.

SQL Server is characterized by such features as:

• Performance. SQL Server works very fast.
• Reliability and security. SQL Server provides data encryption.
• Simplicity. It is relatively easy to work and administer with this DBMS.

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

The central aspect in MS SQL Server, as in any DBMS, is the database. A database is a storage of data organized in a certain way. Often physically the database represents a file on a hard disk though such conformity is not obligatory. For storage and administration of databases, database management system (DBMS) or DBMS is used. And just MS SQL Server is one of such DBMS.

MS SQL Server uses a relational model for database organization. This database model was developed back in 1970 by Edgar Codd. And to date, it is actually a standard for the organization of databases.

The relational model assumes data storage in the form of tables, each of which consists of rows and columns. Each row stores a separate object, and the columns contain the attributes of that object.

To identify each row within a table, the primary key is used. One or more columns can be used as a primary key. Using the primary key, we can refer to a certain row in the table. Accordingly, two rows cannot have the same primary key.

Through the keys, one table can be linked to another, i.e. links can be organized between two tables. And the table itself can be represented as a “relation”.

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

The client (for example, an external program) sends a query in SQL language through a special API. The DBMS properly interprets and executes the query and then sends the execution result to the client.

Originally SQL language has been developed in company IBM for system of databases which was called System/R. Thus language itself was called SEQUEL (Structured English Query Language). Although as a result neither the database nor the language itself were not subsequently officially published, traditionally the term SQL is often pronounced as a “sequel”.

In 1979, Relational Software Inc. developed the first database management system, called Oracle, which used the SQL language. Due to the success of this product, the company was renamed Oracle.

Subsequently, other database systems that used SQL began to appear. As a result, in 1989, the American National Standards Institute (ANSI) codified the language and published its first standard. Since then, the standard has been periodically updated and supplemented. The last update took place in 2011. But despite the presence of the standard, it is not uncommon for DBMS manufacturers to use their own SQL language implementations, which differ slightly from each other.

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

PL-SQL is used in such DBMS as Oracle and MySQL. T-SQL (Transact-SQL) is used in SQL Server. That’s why in the current tutorial it will be considered T-SQL.

Depending on the task the T-SQL command executes, it can belong to one of the following types:

1. DDL (Data Definition Language). This type includes various commands that create a database, tables, indexes, stored procedures, etc. In general, the data is defined.

In particular, we can refer to this type of commands 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 type includes commands to select data, update, add, delete them – in general, all those commands with which we can manage 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). This type refers to commands that control access rights to data. In particular, these are the following commands:

• 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. First of all, it is MS SQL Server Enterprise – full release, aimed at using in real projects. It is used on various hosting and database servers. However, it is available only in the paid version (not counting the trial period) and costs quite decent money.

For simple applications may also suffice and release Express: it is free. Besides it has advantage – it can be put as a real server and used in real tasks, however it has the reduced functionality in comparison with the full version.

And also there is MS SQL Server Developer Edition. It is a full-featured release, which contains all functionality, as well as the full version of MS SQL Server Enterprise, only targeted only for development needs. At the same time, this version can not be used for deployment as a real server on real projects. However, to study all the mechanics of MS SQL Server, this version is the best option, so this version we will use.

So, let us install MS SQL Server 2017 Developer Edition. For this purpose we will go to my.visualstudio.com. You may need a Microsoft account to access it. In this case you need to login with a Microsoft account.

We leave English as the default language and download the entire iso file. Since the downloaded file has the extension .iso, then after the download we unpack it and run the installer program. We will see the installation wizard window:

Here we select the first item “New SQL Server stand-alone installation or add features to an existing installation“. Next, using a sequence of steps, we will need to set the installation options.

Let’s click to the item “Product Key”. At this stage, you need to enter the key, or specify one of the free issues. Here we specify the “Developer” release and proceed to the new step by clicking the Next button.

Next, the license agreement will have to be accepted. And then we snap to the “Feature Selection” step. At this stage you are offered to select the components to be installed. Here we mark all the components, taking into account the amount of free memory:

Depending on the selected components, the number of installation steps increases, where any settings need to be made. In my case all components are selected. So in the future we will consider the case when all the components are selected.

Next, on the step “Instance Configuration” we will need to specify the name and ID of the running SQL Server entity.

For the name, specify the Default instance option, and for the ID, set MSSQLSERVER. This will be the name of the instance that we can use to reach the server from external applications.

Then click the next two steps with the default options to “Database Engine Configuration“. Using the Add Current User button here, we will add the current user as the server administrator.

On the next step of “Analysis Services Configuration” we will also add the current user as an administrator for the Analysis Services feature:

The next two steps are to leave the default settings. And then on the step “Distributed Replay Controller” we will similarly add the current user.

On all subsequent steps leave the default settings and on the last screen for installation click the Install button:

After some time 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 before connecting to it, you should make sure that it is running. To do this, you can open the service window. If it is not running, we can run it there in the service panel and then we can work with it.

Installing SQL Server Management Studio

For convenient database management and various options and settings in MS SQL Server we will install a special administration tool called SQL Server Management Studio (SSMS). You can use this tool to create databases and their tables, write and execute database queries and much more.

To install SSMS, go to docs.microsoft.com. Closer to the bottom of the page we will find links to versions for different locales. Download the English version (or you can choose the localized version in any other language if you want).

Despite the fact that this version of SSMS has the number 17, it also applies to MS SQL Server 2016 and even earlier versions of the server.

After downloading, let’s run the SSMS installation program:

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.

Okay, let’s run the SQL Server Management Studio. 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. And a window will open where you will need to select the desired server:

In my case, the local computer has two servers, the Express release and the Developer release. But by name I can understand that the first item represents Express and therefore I need to select the second item. If there is only one Express release installed on the local computer, then I don’t have to select that release.

After you select a server, its name will be displayed in the “Server name” field. And then you will need to click on the Connect button to connect to it:

And after successful connection the program will open the server content – all its databases and other components:

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