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.
Then you execute the downloaded file.
We choose the Basic installation type.
We accept the License Terms.
We specify the installation location and we wait for the installation to complete.
After the installation, we click Install SSMS (SQL Server Management Studio).
It redirects us to the Microsoft Download page, and we click on the download link.
We execute the file.
Specify the installation location and press install.
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.
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]
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.
You can download the full source code of this example here: SQL Server 2019: Overview and Installation
