SQL Server Tutorial: Unlock the Power of Data Management

SQL Server is a relational database management system (RDBMS) developed by Microsoft. It leverages the SQL programming language to manage and access databases. Overall, SQL Server is built to allow users to store large amounts of data, access it easily and securely, and ensure accuracy across the database and related fields.

This tutorial will walk you through the basics of SQL Server, explaining what it is, why you should use it, and some of the basic processes on SQL Server, including basic features and functionality. This article can serve as a great supplemental resource to our Introduction to SQL Server course.

What is SQL Server?

The first version of SQL Server, v1.0 was released in 1989. Since then, there have been many iterations, with the most recent, SQL Server 2022, being released in November 2022.

Throughout the iterations, many key features have been added to the offering, including:

• A database engine, used to store, process, secure data, and manage relational and XML data.
• Analysis services for creating analytical processing and data mining applications.
• Reporting services for creating different types of reports.
• Integration services for moving, copying, and transforming data and ensuring data quality.
• Master data services for managing access, security, data versioning, business rules, and requests for data.
• Machine learning services for scalable machine learning applications using data sources.
• Azure connected services for leveraging Microsoft’s cloud computing offerings with pay-as-you-go billing.

SQL Server competes primarily with MySQL and Oracle in the RDBMS space. SQL Server integrates well with other Microsoft products, runs well on both Windows and Linux, has a free version, has good integrations with Azure for cloud database options, and is relatively easy to learn and use.

SQL Server works well for most people and is cheaper than Oracle, which requires a lot more licenses to access all of its features. MySQL is an open-source option that is free but lacks some of the more advanced features SQL Server and Oracle offer.

Why Use SQL Server?

We covered many of the diverse and important features offered by SQL Server in the section above. We’d like to highlight a few features that make SQL Server a great option to leverage for an RDBMS solution. A lot of these features can be learned about in significantly more detail through our SQL Server for Database Administrators course.

SQL Server is scalable and has great performance on databases of any size, whether you’re independently learning to use the system on a small database or an enterprise working with Petabytes of data.

Scaling the system is very easy, as you can leverage Microsoft’s Azure Elastic Database tools to increase the size and performance of the database as your needs scale. Azure offers a pay-as-you-go model where you pay for the servers you need as you need them. Azure also offers vertical scaling in the form of improving server compute power and horizontal scaling in the form of purchasing more databases and storage size as needed.

SQL Server also scales very well on your own machine and can handle large databases, assuming you have the hardware available.

You’ll find a range of security features available in SQL Server that help an enterprise ensure important data is only visible to the correct people. This is achieved through column-level protection, which ensures people with the right clearance can see certain columns of data in the database, row-level protection to ensure certain rows of data are only visible to people with the proper roles, and file encryption to ensure people without access cannot read the files.

Finally, SQL Server offers a range of business intelligence capabilities ranging from data modeling, reporting, and access to local and cloud data with minimal effort. As part of Microsoft’s product ecosystem, SQL Server offers a BI solution through its Power BI.

SQL Server Editions

There are a few different versions of SQL server available:

1. SQL Server Express: An entry-level, free database version, ideal for individuals building smaller applications and learning SQL server.
2. SQL Server Standard: Gives access to most of the basic data management and business intelligence database resources and is ideal for smaller businesses with less IT resources.
3. SQL Server Enterprise: The full-feature premium option that allows for more advanced features like datacenter capabilities, faster performance, and end-to-end business intelligence.

Deciding on the best version depends on your needs from SQL Server.

• If you are just starting out and want to learn how to use SQL server, or you are an independent developer, express is likely the best option for you.
• If you are on a small team building a database system with minimal resources, the standard version is likely ideal.
• If you are part of a larger organization with specific requirements or more complex database needs, the enterprise version is likely the best of the three.

Each option provides options to upgrade to a more advanced version (e.g. transitioning from Express to standard).

How to Set Up SQL Server

Let’s get to the main section of our SQL server tutorial, where we’ll briefly explore how you can get set up with SQL server, and how to use SQL server with a look at some basic operations and advanced features.

1. System requirements

The full list of hardware and software requirements can be found in the Microsoft documentation. Generally, these requirements are not restrictive, with 6GB available hard drive space required, 512MB-1GB memory available, and a decent processor, minimum 1.4GHz with a recommended speed of 2.0 GHz or faster. SQL server does require Windows 10 or Windows Server 2016 or greater and now also available on Linux.

2. Installing SQL Server

Once you’ve decided on the edition you are interested in and have confirmed your system meets the required software and hardware requirements, we’re ready to install SQL Server. There are many different options for downloading SQL Server. At the link, you can select the version you want to download and press the blue “Download now” button. You can then click through the installer interface until it is installed.

You will also want to download SQL Server Management Studio (SSMS) as an interface to use SQL Server. You can go to SSMS download page and scroll down to the “Free Download for SQL Server Management Studio” button to download the tool. Azure Data Studio and Visual Studio are also options, but we’d recommend SSMS as the other options lack some features.

3. Initial SQL Server configuration

Once you’ve installed SQL Server and SSMS, you can open SSMS to get started. In the Connect to Server menu screen, you’ll want to set the Server Type to Database Engine and the Server Name to SQL Express or SQL Developer (whichever one you installed). If you’d like to download a sample database from Microsoft, the AdventureWorks sample database is a free and easy-to-set-up option to get started quickly.

Basic SQL Server Operations

Let’s take a look at some of the essential steps if you’re new to SQL server:

Creating databases with SQL Server

You can create a database either through the SSMS interface or by using SQL code.

Using the SSMS interface, you can create a database using the following steps:

1. In the object explorer on the left side of the screen, right-click on the Databases folder and select New Database
2. Enter a name for the database
3. You can either click OK to accept the default options or you can select more nuanced options in the optional settings menu.

In order to create a database, which we’ll call Tutorial, you can use the below syntax, leveraging the CREATE DATABASE and LOG ON commands.

USE master;
GO

CREATE DATABASE Tutorial ON
(NAME = Tutorial_dat,
    FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL19.MSSQLSERVER\MSSQL\DATA\tutorialdat.mdf',
    SIZE = 10,
    MAXSIZE = 50,
    FILEGROWTH = 5)
LOG ON
(NAME = Tutorial_log,
    FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL19.MSSQLSERVER\MSSQL\DATA\tutoriallog.ldf',
    SIZE = 5 MB,
    MAXSIZE = 25 MB,
    FILEGROWTH = 5 MB);
GO

Creating tables in SQL Server

To create a table in the database, a user will need CREATE TABLE permissions. Again, the table creation process can be done either with the SSMS interface or with Transact-SQL.

To create a table in SSMS table designer, you’ll need to follow these steps:

1. In the object explorer, connect to the database you’d like to create a table in.
2. Expand the database that you would like to add a table to in the object explorer by hitting the plus sign next to the name of the database.
3. Right-click the Tables node and select New Table.
4. You can then specify the names of the columns, the data types, and if the values are allowed to be empty (null).
5. If you’d like to specify a column as a primary key, you can right-click on the column and click Set Primary Key.
6. To create relationships between tables, right-click on the table and click Relationships.

To create a table using Transact-SQL, we can use the CREATE TABLE command and specify data types and if they are nullable after the column names as seen below:

CREATE TABLE dbo.TutorialTable (
	ID INT NOT NULL PRIMARY KEY,
	Name TEXT NOT NULL,
	Email TEXT NOT NULL,
	Age INT NULL
);

Basic SQL queries in SQL Server

We’ve already seen the basic syntax of SQL queries in the table creation process. SQL relies on a simple verb-based syntax with a small list of verbs to do certain operations, like CREATE TABLE in the previous section. We’ll cover the basics of retrieving data (known as querying) from these tables next, but if you’d like to get better practice and mastery with SQL, you should check out our SQL Server Fundamentals track.

The basic syntax for receiving data relies on the SELECT, FROM, and WHERE keywords. SELECT specifies the columns we’d like to retrieve from the table, FROM specifies the name of the table we’d like to query, and WHERE specifies the conditions of the data we’d like to include. For example, if we wanted to see all of the names and emails of people over 30 in our TutorialTable table created above, we’d use the following query.

SELECT Name, Email
From TutorialTable
WHERE Age > 30;

There are many more SQL concepts, like functions and summarizing groups of data that are beyond the scope of this tutorial. If you’d like to learn more about these concepts, check out our Intermediate SQL Server course.

Advanced SQL Server Features

Stored Procedures

A stored procedure is a saved SQL code that you can reuse. This can be created using the CREATE PROCEDURE command as seen below:

CREATE PROCEDURE procedure_name AS 
SQL_Statement
GO;

This procedure can then be executed using the following SQL code:

EXEC procedure_name;

Triggers

A trigger is a type of stored procedure that runs automatically when something happens in a database server. These can be set up to run for events like when a table is created, changed, or deleted or when a user logs onto the database. These can be set up using the CREATE TRIGGER command, as seen below.

CREATE TRIGGER trigger_name
ON table_name
[BEFORE or AFTER] [INSERT AND/OR UPDATE AND/OR DELETE]
AS 
tigger_code;
GO

Views

A view is a type of table that is defined by a query. A view can be queried like a normal table. The view acts as a reference to the table, so if values in the table are adjusted, then the view will also be adjusted. Views act as a filtered version of the underlying table, making more complex queries easier to both write and read. For example, if we wanted to create a view of the table we queried in the Basic SQL Queries section, we could use the following syntax:

CREATE VIEW email_view AS 
SELECT Name, Email
From TutorialTable
WHERE Age > 30;

Best Practices for Using SQL Server

Navigating through SQL Server involves adhering to pivotal best practices that safeguard and optimize your data management. Whether you're a seasoned administrator or a beginner, these guidelines are crucial for ensuring data security, optimal performance, and smooth operations.

Prioritize data safety through regular backups

You’ll need to safeguard your data against unforeseen data management errors or physical damage to the server. To do so, you should:

• Create backups of your data to prevent irreversible data loss due to accidental deletions or other mishaps.
• Store copies of your data in secure, off-site locations to protect against onsite issues such as natural disasters or hardware failures.
• Implement a regular backup schedule to minimize data loss and ensure data recovery is possible from the most recent point.

Explore Microsoft's comprehensive documentation on data backup processes for detailed guidance.

Optimize database performance through tuning

To enhance the efficiency and responsiveness of your database, you should monitor and optimize its performance with the following:

• Utilize SQL Server’s monitoring functionalities to gain insights into user behavior and database interactions.
• Analyze the collected data to identify any bottlenecks or areas that may benefit from resource reallocation.
• Implement adjustments based on the analysis to ensure resources are optimally utilized, and performance is maximized.

Uphold data integrity with robust security measures

Data accessibility should be restricted to authorized personnel only, maintaining confidentiality and compliance. Here are some steps you can follow:

• Implement column-level and row-level security to control the visibility of specific data and variables based on user clearance.
• Employ file encryption methods to safeguard data and protect it from unauthorized access.
• Mitigate potential hardware and infrastructure threats by employing physical and network security measures.

Conclusions

In this article, we took a look at the basics of SQL Server, including what it is, why you should use it, and how to install it. We then covered some of the basics of creating, managing, and querying databases. FInally, we explored some of the more advanced features and best practices to get the most out of SQL Server.

For a more in-depth introduction to SQL Server, check out DataCamp’s Introduction to SQL Server course or check out our Intermediate SQL Server course to learn more advanced topics with SQL.