What is a SQL database?

A SQL database is a relational database management system (RDBMS) that uses SQL (Structured Query Language) commands to manage, store, retrieve, process and organize data in a structured format.

Popular SQL database systems include Microsoft SQL Server, MySQL, PostgreSQL and Oracle.

SQL databases offer a standardized, efficient way to use SQL to manage, query and interact with structured data. Their flexibility makes them valuable in a variety of applications including web development, enterprise applications, data analysis and reporting. Organizations use SQL databases to support a wide range of uses from simple data storage to complex business intelligence systems.

Azure SQL Database from Microsoft Azure is an example of a SQL database offered as a scalable, managed, cloud-based relational database system.

There are several brands of SQL database, each with different options for licensing cost. Commonly used SQL databases include the following:

SQL Server

Microsoft offers several editions of SQL Server – both free and commercial – including Express, Developer, Evaluation, Enterprise, Web and Standard.

• SQL Server Express is a free and feature-limited edition of SQL Server used for small-to-medium-scale apps, mainly for development and learning. You can use it for free; no licensing costs are associated with it.
• Developer Edition is also free, but it is intended for use only in a development and testing environment. It provides all the features of the Enterprise Edition, but it is not licensed for production use.
• Community Edition refers to the free editions like Express and Developer. These editions are often used by developers and small-scale businesses.
• Enterprise Edition provides a comprehensive set of advanced features for large-scale and mission-critical applications. Its licensing cost is higher than that of other editions.
• Evaluation Edition provides the full features of the Enterprise Edition but is time-limited to 180 days. It is suitable for testing and evaluating SQL Server.

MySQL

MySQL is a cost-free, open-source RDBMS that is well known for its use in web applications. MySQL is also available in commercial editions like MySQL Enterprise Edition, offering additional features and support in exchange for licensing fees.

PostgreSQL

PostgreSQL is another cost-free RDBMS. Its permissive, open-source license allows for subsequent use, modification and distribution of the software.

Oracle

• Express Edition (XE) is the free version of Oracle Database.
• Oracle Database is also available in commercial editions that include additional features for medium businesses and enterprises. The commercial editions require licensing fees.

Note that the database software itself may be free, but the vendor may charge for support, maintenance or specific features. Check the website of each vendor for up-to-date information on proper usage, cost and licensing requirements.

Besides the fundamentals of using SQL to manage structured data, each brand of SQL database provides services that support database development, deployment and management.

SQL databases are built on a relational model, where data is stored in tables with rows and columns. With SQL, you can perform common operations like querying data, inserting new records and updating or deleting existing records. The ACID (Atomicity, Consistency, Isolation and Durability) properties of SQL databases ensure the reliability and integrity of database transactions. Elements like primary keys, foreign keys, unique constraints and check constraints ensure the accuracy of the stored data.

As an example of database services provided by a SQL database, consider those built into Microsoft SQL Server, as follows.

RDBMS, data storage and data retrieval

With SQL Server, users create and manage databases, define tables consisting of rows and columns, establish relationships among tables and transact using SQL. The relational model is an organized way to store and retrieve information.

Integration

SQL Server Integration Services (SSIS) is a data integration tool that helps facilitate the extraction, transformation and loading (ETL) of data among databases and systems.

Analysis

SQL Server Analysis Services (SSAS) provides online analytical processing (OLAP) and data mining. It allows users to create multidimensional data models for complex analysis and reporting.

Reporting

SQL Server Reporting Services (SSRS) is a server-based reporting platform for creating, deploying and managing reports. It supports a variety of report types including tabular, matrix and graphical.

Machine Learning Services

SQL Server Machine Learning Services integrates with popular machine learning frameworks and languages like Python and R. It enables data professionals to run machine learning models directly within the SQL Server environment.

Database administrators (DBAs) and developers use tools for tasks like database design, development, monitoring, administration and performance tuning. Frequently used tools include the following:

SQL Server Management Studio (SSMS)

SSMS is the primary tool that Microsoft provides for managing and administering SQL Server databases. It lets users connect to and manage SQL Server instances, write and execute Transact-SQL (T-SQL) queries, design and administer databases, create unit tests and automate tasks.

SQL Server Data Tools (SSDT)

SSDT is an integrated development environment (IDE) for building, designing, testing and deploying SQL Server databases. Database developers use it to create and maintain database projects, design tables, write stored procedures and manage database schema.

SQL Server Configuration Manager

This tool is used to configure and manage SQL Server services and network settings.

SQL Server Profiler

Profiler helps users capture and analyze events that occur in a SQL Server instance. It is often used for performance tuning, troubleshooting and auditing.

Visual Studio with SQL Server Data Tools

The SQL Server Data Tools extension to Visual Studio enables more extensive development of applications. The additional features provided by the extension integrate database development with application development.

Azure Data Studio

This cross-platform database tool can be used with on-premises SQL Server and Azure SQL database. It includes features for writing and executing queries, managing databases and visualizing data.

SQLCMD

DBAs and developers use this tool to execute T-SQL commands and scripts directly from the command line. It is useful for automating tasks and scripting.

Database Engine Tuning Advisor

This tool helps in optimizing the performance of queries and databases. It analyzes query workloads and suggests performance improvements such as indexes.

Extended Events

A lightweight performance monitoring system for SQL Server, Extended Events is used for tracing and monitoring events inside the database engine.

Backup and recovery

As essential processes for safeguarding your database environment, backup and recovery allow you to resume normal operation with minimal downtime in case of an outage.

As the name Structured Query Language suggests, SQL is neither a database nor a server but a programming language used for managing and interacting with relational databases.

In the context of databases, “server” refers to the database server, which stores data and manages the database. The combination of SQL and a database server allows you to manipulate the data stored in a SQL database.