How SQL Server fits into a data application

SQL Server is a database engine. It stores and manages data, executes Transact-SQL (T-SQL) statements, enforces constraints, and manages transactions. Applications and database tools can communicate with SQL Server. In a typical web application, the browser communicates with a server-side application, which then communicates with SQL Server. This lesson uses DDI_SQL_Training dataset v1.0.0 on SQL Server 2019 or later.

A simple request flow

Imagine a reporting page needs the number of customers. Its server-side code sends a query to SQL Server. SQL Server reads dbo.Customers and returns one result row. The reporting page then decides how to display that result. This is one common web application flow; database tools, scripts, and other applications can also connect to SQL Server. Read the example now; you can run it after setting up the practice database in lesson 5.

SELECT COUNT(*) AS CustomerCount
FROM dbo.Customers;

SQL Server returns a result set containing the count. Running this SELECT does not create a new table or change the stored rows. The database name is DDI_SQL_Training. dbo is the schema that contains the Customers table in this database, so dbo.Customers identifies the table by schema and table name. A SQL editor such as SQL Server Management Studio is one client; it is not the database engine itself.

Reads and writes

The SELECT COUNT(*) query shown here returns a result without changing the stored rows. INSERT, UPDATE, and DELETE can change stored rows when the connected user has the required permissions. SQL Server also manages transactions so related changes can succeed or be rolled back together.

Check your understanding