sql

SQL Server 2019: Overview and Installation

1. Introduction

In this tutorial, we will see some new and key features of SQL Server 2019 and we will write a small example to help you get started.

2. What’s new in SQL Server 2019

2.1 Intelligent Query Processing Enhancements

This is a set of enhancements that improves Query Optimizer. Query Optimizer is a component inside SQL Server that generates the execution plans for queries. This includes dynamic memory grants for rowstore tables, table variable deferred compilation, batch mode on rowstore, and more. This means better performance overall after doing the upgrade.

2.2 AlwaysEncrypted with secure enclaves

This is the next version of AlwaysEncrypted. AlwaysEncrypted is an encryption technology introduced in SQL Server 2016 that allows transparent column encryption without giving administrators access to the decryption keys. Using the new secure enclaves technology, SQL Server can now securely encrypt a portion of memory to perform computations on these encrypted columns without ever exposing the unencrypted values to the rest of the processes.

3. Important features

3.1 Memory-optimized Tempdb metadata

The SQL team has made optimizations to the tempdb code so some of the metadata that can be a bottleneck on tempdb heavy systems can rely completely on memory and be optimized for RAM access. In this way large-volume, large-scale environments that use a lot of tempdb, can be optimally run and accessed.

3.2 Accelerated Database Recovery (ADR)

This is a completely new way for SQL Server to perform database recovery in the event of a transaction rollback, an instance restart, or an availability group failover. The SQL team has redeveloped how recovery works and has dramatically decreased how long this process takes.

4. SQL Server 2019 installation on Windows

To install SQL Server 2019, you need to download it from this link.

SQL Server 2019 Developer Download link.
Fig. 1: SQL Server 2019 Developer Download link

Then you execute the downloaded file.

sql server 2019 - Downloaded File
Fig. 2: Downloaded File.

We choose the Basic installation type.

sql server 2019 - Installation Type.
Fig. 3: Installation Type.

We accept the License Terms.

sql server 2019 -  License Terms
Fig. 4: License Terms.

We specify the installation location and we wait for the installation to complete.

sql server 2019 - Installation Location.
Fig. 5: Installation Location.

After the installation, we click Install SSMS (SQL Server Management Studio).

SQL Server Management Studio 2019 Installation
Fig. 6: SQL Server Management Studio Installation.

It redirects us to the Microsoft Download page, and we click on the download link.

sql server 2019 - SSMS Download Link.
Fig. 7: SSMS Download Link.

We execute the file.

Fig. 8: Downloaded File.
Fig. 8: Downloaded File.

Specify the installation location and press install.

Fig. 9: Installation Location.
Fig. 9: Installation Location.

We just installed SQL Server 2019 and it is ready to be used.

5. Create a Small Database

In this example, we will start with a small database, create a table, and traverse the data.

5.1 Create the Database

We right-click Database and we choose New Database. We type a name and then we click OK.

Fig. 10: Database Creation.
Fig. 10: Database Creation.
Fig. 11: Database Settings.
Fig. 11: Database Settings.

5.2 Create a Table

In order to create a Table, we have to specify the name of the columns and the data type of each one.

tutorial.sql

CREATE TABLE [dbo].[People](
	[ID] [int] IDENTITY NOT NULL,
	[First Name] [varchar](50) NOT NULL,
	[Last Name] [varchar](50) NOT NULL,
	)

5.3 Insert Data

Now that the table is created, we can insert some data by using INSERT INTO .... VALUES .

tutorial.sql

INSERT INTO [dbo].[People]
           ([First Name]
           ,[Last Name])
     VALUES
           ('John',
           'Kayn')

5.4 Select the rows

We can see the saved data by using SELECT .... FROM .

tutorial.sql

SELECT [ID]
      ,[First Name]
      ,[Last Name]
  FROM [dbo].[People]
Fig. 12: SELECT ... FROM output.
Fig. 12: SELECT … FROM output.

5.5 Drop Table

We can delete the table we created by using DROP .

tutorial.sql

DROP TABLE [dbo].[People]

6. Summary

In this tutorial, we learned how to get started with SQL Databases, install SQL Server, connect to it and create some entities. There are a lot of things still to be learned. SQL Server is one of the most used programs to create databases by using a friendly and fast GUI.

7. Download Source Code

This was an example of how we can install and use SQL Server 2019.

Download
You can download the full source code of this example here: SQL Server 2019: Overview and Installation

Odysseas Mourtzoukos

Mourtzoukos Odysseas is studying to become a software engineer, at Harokopio University of Athens. Along with his studies, he is getting involved with different projects on gaming development and web applications. He is looking forward to sharing his knowledge and experience with the world.
Subscribe
Notify of
guest

This site uses Akismet to reduce spam. Learn how your comment data is processed.

0 Comments
Oldest
Newest Most Voted
Inline Feedbacks
View all comments
Back to top button