SQL Server: Core Concepts and Features

Posted on Mar 13, 2025 in Computing and Communications

Week 1: Introduction to SQL Server

SQL Server is a relational database management system (RDBMS) developed by Microsoft. It is built on top of SQL for interacting with relational databases, utilizing T-SQL, Microsoft’s implementation of SQL, which adds a set of programming constructs.

SQL Server worked exclusively on the Windows environment for more than 20 years. In 2016, Microsoft made it available on Linux.

Each installation of SQL Server is considered an instance. You can install multiple instances of SQL Server on the same computer. SQL Server requires you to separate each of the installations, either by using a named instance or by setting it as a default instance.

• By providing a named instance, any time a client wants to connect to that SQL Server, they must know the computer name and the instance name to connect to it.

Make sure to proceed with the installation as a default instance.

Week 2: Databases and Data Handling

Relational Database History and Platforms

First Releases:

• Oracle: 1979
• DB2: 1985
• MySQL: 2000
• SQL Server: 1987

Primary Platforms:

• Oracle: Unix/Linux; z/OS
• DB2: OS/390 & z/OS; Unix/Linux
• MySQL: Unix/Linux; Windows; macOS
• SQL Server: Windows; Linux

SQL Server System Databases

• master: Records all the system-level information for an instance of SQL Server, which includes instance-wide metadata such as logon accounts, endpoints, linked servers, and system configuration settings.
• model: Used as the template for all databases created on an instance of SQL Server.
• msdb: Used by SQL Server Agent for scheduling alerts and jobs.
• tempdb: A workspace for holding temporary objects or intermediate result sets.

Database Data Files

SQL Server databases are stored in files.

• The default extension for the single data file used with a small database is .mdf. MDF stands for Master Data File. Every database has one and only one MDF.
• A log file or LDF (Log Database File) is a database file of Microsoft SQL Server, which stores all the transaction logs/events, which executes on the database.

Common Date Formats

Common Time Formats

A SELECT Statement Using the CAST Function

SELECT InvoiceDate, InvoiceTotal,
CAST(InvoiceDate AS varchar) AS varcharDate,
CAST(InvoiceTotal AS int) AS integerTotal,
CAST(InvoiceTotal AS varchar) AS varcharTotal
FROM Invoices;

Convert and Format Dates

SELECT CONVERT(varchar, InvoiceDate) AS varcharDate,
CONVERT(varchar, InvoiceDate, 1) AS varcharDate_1,
CONVERT(varchar, InvoiceDate, 107) AS varcharDate_107,
CONVERT(varchar, InvoiceTotal) AS varcharTotal,
CONVERT(varchar, InvoiceTotal, 1) AS varcharTotal_1
FROM Invoices;

Convert and Format Dates with TRY_CONVERT

SELECT TRY_CONVERT(varchar, InvoiceDate) AS varcharDate,
TRY_CONVERT(varchar, InvoiceDate, 1) AS varcharDate_1,
TRY_CONVERT(varchar, InvoiceDate, 107) AS varcharDate_107,
TRY_CONVERT(varchar, InvoiceTotal) AS varcharTotal,
TRY_CONVERT(varchar, InvoiceTotal, 1) AS varcharTotal_1,
TRY_CONVERT(date, 'Feb 29 2019') AS invalidDate
FROM Invoices;