SQL Server is a relational database management system, or RDBMS, developed by Microsoft.
SQL Server is built on SQL, a standard programming language for interacting with the relational databases. The Microsoft implementation of SQL that adds a set of proprietary programming constructs.
It is a software product with the primary function of storing and retrieving data as requested by other software application, which may run either on the same computer or on another computer across a network (including the Internet).
Microsoft markets at least a dozen different editions of Microsoft SQL Server, aimed at different audiences and for workloads ranging from small single-machine applications to
SQL Server works on Windows environment for more than 25 years. In 2016, Microsoft made it available on Linux. In 2017 became generally available in October 2016 that ran on both Windows and Linux.
What Can SQL Server do?
- SQL can execute queries and get result from a database
- SQL can retrieve data
- SQL can update records
- SQL can insert records
- SQL can delete records from a database
- SQL can create new databases
- SQL can create new tables in a database
- SQL can create stored procedures
- SQL can create views
- SQL can set permissions on tables and views
- SQL can get result like pivot using Group by command
SQL Server Architecture Explained:
MS SQL Server is a client-server architecture. MS SQL Server process starts with the client application sending a request. The SQL Server accepts, processes and replies to the request with processed data
Database Engine:
The engine of the instance can be broken down into two parts. The Relational Engine and the Storage Engine.
Relational Engine:
The Relational Engine handles the execution of queries as they are received from client applications. This engine has the following components: Query Parser, Optimizer/Planner, and Executor.
Storage Engine:
The Storage Engine handles the actual accessing of the data as requested by the query executor. Within this engine there is a transaction manager and buffer manager which interact with the data and log files to select/insert/update/delete data as required.
Query Parser:
Query Parser (Command Parser): This will check syntax of the query and it will convert the query to machine language.
Query Optimizer:
It will prepare the execution plan as output by taking query, statistics and Algebraize tree as input.
Execution Plan:
It is like a roadmap, which contains the order of all the steps to be performed as part of the query execution.
Query Executor:
This is where the query will be executed step by step with the help of execution plan and also the storage engine will be contacted.
SQL OS:
This lies between the host machine (Windows OS) and SQL Server. All the activities performed on database engine are taken care of by SQL OS. SQL OS provides various operating system services, such as memory management deals with buffer pool, log buffer and deadlock detection using the blocking and locking structure.