Excited? That’s how it feels to learn a new skill on your first day. It can also feel a bit overwhelming. You have lots of questions. You don’t know where to start. Learning SQL Server for the first time feels the same. And that’s what this article is about. It will answer the very first important questions.
Ready?
SQL Server Definition (What is MSSQL?)
Definition? Meaning? Sounds boring.
This may not be the most exciting part of learning but trust me, you need it to understand when starting with SQL Server. In this section, you are going to get answers to the following questions:
- What is SQL Server? What is SQL Server used for?
- What is a relational database management system (RDBMS)?
- What is an SQL Server database?
- What is a database table?
Microsoft SQL Server (pronounced as “sequel server”) is one of the most popular databases used by many companies for decades. It also goes with other names like MSSQL, MS SQL Server, or simply, SQL Server. These databases are used to store relevant information to run a business.
It is Microsoft’s flagship database. It runs on Windows, Linux, and Mac (through Docker). You can also use it in the cloud through Azure, AWS, and Google Cloud.
But there are more than 300 databases today. That’s quite a number of products to store information. The most popular ones are relational database management systems (RDBMS). And this is where SQL Server belongs.
RDBMS uses SQL that stands for Structured Query Language to retrieve, insert, update, and delete data (primarily). SQL Server uses T-SQL, an extension of SQL to interact with its database. We will talk about this later.
But how do you benefit from using it?
- Enjoy a promising career as an administrator, a developer, or a data analyst.
- Help businesses get a reliable, scalable, and secure way to store and manage information for their apps.
- MS SQL has a wide variety of user-friendly tools. One of them is SQL Server Management Studio (SSMS), a free tool by Microsoft.
What is the Use of MSSQL Server?
MSSQL Server stores and manages data efficiently and securely. And how is that done?
Like any other relational database, the SQL Server database is organized into tables that can be related by data common to each. Meanwhile, a table is organized into columns and rows. SQL is also the language used to create databases and tables within.
Let’s have a very common example. This way you will also have an idea of how to use the MSSQL Server.
Phonebook
Who doesn’t know what a phonebook is? In our smartphones, we have names. These names can have one or more phone numbers. You can also link one or more email addresses and other information to names in the phonebook.
And it’s not the end of the story.
Some smarty pants invented a way to sync your phonebooks to some servers online. So, when you need to reset your phone or buy a new phone, your contacts won’t be gone for good. It’s amazing.
That is the power of databases. You don’t need to retype all of them on your phone.
If you’re going to make an MSSQL Server database like this, you need 3 tables. They are contact names, phone numbers, and email addresses.
But Why 3 Tables?
The answer lies in how tables are designed in relational databases, including MSSQL Server. Before we had smartphones, we stored contacts in our phonebooks.
| Contact | Phone Number |
| Bill (home) | 546-9999 |
| Bill (work) | 659-4545 |
| Jane | 655-4576 |
| Toni (home) | 455-7979 |
| Toni (work) | 655-8877 |
Bill is just one person, so is Toni. But because they have 2 numbers, you must repeat names, and you can’t store email addresses. But today, we store the contact information differently.
| Contact | Phone Number | |
| Bill | 546-9999 | [email protected] |
| 659-4545 | [email protected] | |
| Jane | 655-4576 | [email protected] |
| Toni | 455-7979 | [email protected] |
| 655-8877 |
You only have to type the name once. Then, add numbers, email addresses, work addresses, notes, and more to it.
Thus, in our database, contact names include Bill, Jane, and Toni. That’s our first table. Then, phone numbers and email addresses are in their separate tables. But they are related in a way it is arranged like the table above.
What’s the point?
The same information will not be stored repeatedly to save storage space. That’s how relational databases like SQL Server simplifies working with data. Very nice!
That’s why SQL Server has been trusted in industries like finance, health, services, manufacturing, and others. It also has a 4.5 out of 5-star reviews in Gartner Peer Insights.
Microsoft SQL Server Editions
There are few editions of SQL Server that you should be aware of. It will also stop you from using an edition with features you don’t need and save you money and resources.
Each edition has its system requirements, limitations, and price. But all editions support 64-bit processors only. If you’re a learner, you might be looking for a FREE edition.
Though there are several editions of SQL Server, you can install different versions and editions on 1 machine. You can do this up to 50 instances. Totally insane, if you ask me, but it’s allowed. And yet, you also need to have insane resources to make that happen.
Anyway, here are the different editions of SQL Server.
SQL Server Enterprise Edition
One word to describe this edition: PREMIUM.
This is the most full-featured edition for high-end data centers. These data centers need blazing-fast performance and end-to-end business intelligence. So, this edition enables high-service demands for mission-critical workloads. You’ll also get the maximum database size and compute capacity with this edition.
If you are more concerned about performance and features vs. price, this is it. But if you’re just learning, this is overkill.
SQL Server Standard Edition
SQL Server Standard Edition delivers basic data management and business intelligence. This is applicable for small to mid-sized organizations and also good for organizations with minimal IT resources.
With the standard edition, you’ll also get the maximum database size. But compute capacity is reduced compared to the enterprise edition.
SQL Server Web Edition
This is a low total-cost-of-ownership option for web hosters who want to use SQL Server for small to large-scale web properties. With the Web Edition, you’ll also get the maximum database size. But compute capacity is reduced compared to the Standard Edition. Features and capabilities are also reduced.
SQL Server Developer Edition
This is also full-featured like the Enterprise Edition. But deployments are limited to development and test environments.
This edition is free, making it ideal for newbies and professionals alike. You can download it and install it on your desktop or laptop. Then, start learning or create projects with it.
SQL Server Express Editions
Another free edition for entry-level and data-driven apps is the Express Edition. The database size is up to 10GB only. Utilized RAM is also up to 1GB only no matter how much memory you add. Compute capacity is limited to 4 cores.
Yet, a seamless upgrade is possible if you need features from other editions.
Another version of the Express Edition called LocalDB is lightweight. It also has zero-configuration installation and has the least prerequisites.
What is T-SQL?
T-SQL is the SQL Server language implementation of SQL. It provides extensions to the standard SQL for programming purposes. It is also known as Transact-SQL or Transact structured query language.
It is used for programming SQL commands in scripts, stored procedures, functions, and triggers. You will learn more of this as you progress in your study of T-SQL.
One of the notable programming differences between T-SQL and standard SQL is error handling. In programming, it is the best practice to handle errors you anticipate in your code. The code won’t break at runtime and leave all sorts of problems in data.
Here’s an example of error handling in T-SQL:
BEGIN TRANSACTION
BEGIN TRY
INSERT INTO Table1
(StringColumn, IntegerColumn)
VALUES
'sample text',100;
-- commit the changes upon successful insert
COMMIT TRANSACTION;
END TRY
BEGIN CATCH
-- rollback changes on error
ROLLBACK TRANSACTION;
END CATCH
The TRY-CATCH block is a T-SQL extension on handling errors. In the above code, a transaction is started. Then, an attempt is made to insert values to Table1. If successful, the transaction is committed. The inserted data is also made permanent in Table1. But when an error occurs, execution proceeds in the CATCH block. And the transaction is rolled back.
You can create T-SQL queries or commands using language construct in 4 different groups. We’ll discuss this next.
What are DDL, DML, DCL, and TCL?
Four groups define Transact-SQL commands. Let’s define each of them.
DDL, or Data Definition Language
DDL includes commands to create, alter, and maintain databases and database objects. Here are some of them:
- CREATE DATABASE – used for creating new databases and defining database options.
- ALTER DATABASE – used to change database properties and settings.
- DROP DATABASE – used to delete databases.
- CREATE TABLE – used for creating new tables with new columns
- ALTER TABLE – used to change table structure and columns.
- DROP TABLE – used for deleting tables.
- CREATE INDEX – used for creating indexes. Each table can have one or more indexes to search records faster.
DML, or Data Manipulation Language
DML includes commands to retrieve records from tables and also commands to insert new rows, update existing rows, and delete rows. Here are some of them:
- SELECT – used when you need to retrieve rows from tables.
- INSERT – used when you need to add new rows in tables.
- UPDATE – used when you need to change values in existing rows in a table.
- DELETE – used when you need to delete rows in tables.
DCL, or Data Control Language
This includes commands to secure the database and grant and revoke permissions to access database objects. Here are some of them:
- GRANT – used to allow user privileges to users or roles in the database.
- DENY – bans users or user roles in performing commands against database objects.
- REVOKE – remove existing user or role privileges in the database.
TCL, or Transactional Control Language
You have seen most of the TCL commands from the sample code above. Here they are again.
- BEGIN TRANSACTION – starts a transaction. Any changes in database objects will not be saved until a COMMIT TRANSACTION is made.
- COMMIT TRANSACTION – permanently save all changes in database objects done after BEGIN TRANSACTION.
- ROLLBACK TRANSACTION – cancels all changes done after BEGIN TRANSACTION as if nothing happened.
SQL Server Latest Version
At the time of writing, the latest version is Microsoft SQL Server 2019 or version 15. The release date was on November 4, 2019. Check out the official documentation for a detailed discussion of system requirements. Then, you can download the latest version from here.
When choosing between different setups, you can pick cloud or on-premise. Or download free editions of SQL Server’s latest version.
The installer also involves downloading SQL Server Management Studio (SSMS). The latest version of SSMS is 18.9.2 at the time of writing. This will work on Windows operating system.
If you wish to learn SQL Server in Linux or Mac, you will need Azure Data Studio. This is also available from the download page.
See the SQL Server download page below.
SQL Server 2019 Overview
All examples in our beginner’s tutorial will use this. You can choose between SQL Server Express 2019 or SQL Server 2019 Developer Edition. Enjoy FREE licensing for these editions. In our upcoming article, you will learn how to install these step-by-step.
Screenshots will be captured from a Windows machine using SQL Server Management Studio.
So, what are the new features and updates in SQL Server 2019?
Performance
- Support for persistent memory. It is a type of non-volatile media that fits in a standard DIMM (memory) slot. Unlike standard RAM, information remains even after a power failure or system shutdown.
- Improvements in Intelligent Query Processing for faster execution of queries compared to the previous version.
- Support for in-memory TempDB. The TempDB is one of the bottlenecks that make queries slow. Making it work in RAM makes queries faster compared to previous versions.
Security and Compliance
- Data Discovery and Classification is a new feature that allows table columns to be labeled as sensitive data (like email address, phone number, etc.). This also allows administrators to protect this information with improved data auditing.
- Data encryption improvements in Always Encrypted with secure enclaves. This allows a broader set of features to protect sensitive data.
Developer and DBA Tools
- Azure Data Studio is introduced to have a tool for SQL Server that runs in Linux and Mac. And aside from SQL Server, you can also manage PostgreSQL and MySQL deployed in Azure.
- Improvements in PolyBase. This allows querying relational and non-relational data from within SQL Server. In MS SQL 2019, you can now access Oracle, PostgreSQL, MongoDB, Teradata, and ODBC-compliant data sources.
- Improvements in the graph database, UTF8 Unicode support, and spatial data.
- SQL Server Language Extension that allows an external language like Java to be executed from SQL Server.
Choice Across OS and Containers
- SQL Server components like PolyBase, MS DTC, Machine Learning, and Replication are now available in SQL Server for Linux.
- Support for Red Hat Enterprise Linux-based containers besides Ubuntu-based containers.
Other SQL Server Versions List
SQL Server 2019 is supported until January 8, 2030. Meanwhile, here is the list of previous versions that are still supported.
| SQL Server Version | Supported Until (MM/dd/yyyy) |
| SQL Server 2017 | 10/12/2027 |
| SQL Server 2016 | 07/14/2026 |
| SQL Server 2014 | 07/09/2024 |
| SQL Server 2012 | 07/12/2022 |
For further and updated information, you can visit the Microsoft Lifecycle page here.
Microsoft also uses code names when they develop SQL Server. Here’s a list of known code names:
| SQL Server Version/Version Number | Code Name |
| 2019 (version 15) | Seattle |
| 2017 (version 14) | Helsinki |
| 2016 (version 13) | SQL 16 |
| 2014 (version 12) | SQL 14 |
| 2012 (version 11) | Denali |
| 2008 R2 (version 10.5) | Kilimanjaro |
| 2008 (version 10) | Katmai |
| 2005 (version 9) | Yukon |
| 2000 (version 8) | Shiloh |
How to Check the SQL Server Version and Edition
You can check these data in 2 easy ways to make sure the SQL Server installed on your PC is the one we are going to use in our examples. In both ways, you also need SSMS installed.
First, for people who love to type, open a New Query window in SSMS and type:
SELECT @@version;Then, click the Execute button in the toolbar or press Ctrl-E. Here is the output on my PC:
Microsoft SQL Server 2019 (RTM-CU12) (KB5004524) - 15.0.4153.1 (X64)
Jul 19 2021 15:37:34
Copyright (C) 2019 Microsoft Corporation
Developer Edition (64-bit) on Windows 10 Home Single Language 10.0 <X64> (Build 19043: )
There you go. Mine has the latest cumulative update so the version is 15.0.4153.1. This is a Developer Edition.
Another way to know the version and edition is for people who don’t like to type. The steps are:
- In the Object Explorer of SSMS, right-click the server.
- Select Properties.
- In the Server Properties window, look for the Product and Version properties.
For the Server Properties, see the step 3 screenshot below.
Takeaways
Microsoft SQL Server is one of the best relational databases. It opens a career opportunity for developing and administering data. And it also is a good starting point in learning SQL in general.
It has a few editions that fit any business needs. But if you’re a learner, the best is the Developer Edition. You can download it now. The next article will discuss the installation of the downloaded installer.
Tags: sql server, sql server 2019 Last modified: October 08, 2021
