SQL Overview

This tutorial helps you in learning Structured Query Language and also to work on SQL commands which provides instant result.

SQL

SQL is a language for database. SQL stands for Structured Query Language; it is used for storing, retrieving and manipulating data in relational databases.

The various types of SQL are; 

• T-SQL for MS SQL Server.
• JET SQL (native format) for MS Access.  
• PL/SQL for Oracle and
• ANSI SQL for all databases. American National Standards Institute (ANSI) published a universal standard for the SQL which database vendors could adhere.

SQL Language/Commands

SQL Language/commands are used to perform various functions. These functions include building database objects, manipulating objects, loading data to database tables, updating existing data in tables, deleting data, performing database queries, controlling database access, and overall database administration.

The various types of SQL Language/Commands are; 

·         DDL (Data Definition Language) – Allows users to create, modify and deletion of database objects (Database, Tables and columns, Index, View, Synonym, Procedure, Function and Trigger)

The various DDL commands are;

o    CREATE TABLE

o    ALTER TABLE

o    DROP TABLE

o    ALTER COLUMN

o    DROP COLUMN

o    CREATE INDEX

o    ALTER INDEX

o    DROP INDEX

o    CREATE VIEW

o    DROP VIEW

o    CREATE SYNONYM

o    DROP SYNONYM

o    CREATE PROCEDURE

o    DROP PROCEDURE

o    CREATE FUNCTION

o    DROP FUNCTION

o    CREATE TRIGGER

o    DROP TRIGGER

o   TRUNCATE TABLE -  is a DDL due to the following functionalities compared to DELETE.

deallocates the data pages in a table and only this deallocation is stored in transaction log

acquires only table and page locks for the whole table. since no row locks are used less memory is required  (lock is a pure memory object)

resets identity column if there is one

 removes ALL pages. NO empty pages are left behind in a table

 doesn't fire delete triggers

https://msdn.microsoft.com/en-us/library/ff848799.aspx

·         DML (Data Manipulation Language) – Allows users to manipulate data within objects of a relational database.

The various DML commands are;

o   INSERT

o   UPDATE

o   DELETE

https://msdn.microsoft.com/en-us/library/ff848766.aspx

·         DQL (Data Query Language) – Allows users to select/fetch data from database.

o    SELECT

https://msdn.microsoft.com/en-us/library/ms189499.aspx

·         DCL (Data Control Language) – Allows users to control access to data within the database.

The various DCL commands are;

o   GRANT - https://msdn.microsoft.com/en-us/library/ms187965.aspx

o   REVOKE - https://msdn.microsoft.com/en-us/library/ms187728.aspx

o   DENY  - https://msdn.microsoft.com/en-us/library/ms188338.aspx

·         Data administration commands - Database administration is the overall administration of a database. Allows users to perform audits and perform analyses on operations/system performance within the database

o    ALTER SERVER AUDIT

https://msdn.microsoft.com/en-us/library/cc280563.aspx

·         Transactional Control Language (TCL)– Allows users to manage the database transactions.

The various TCL commands are:

o   COMMIT - Saves all transactions to database of current session.

o   ROLLBACK - Undoes all the transactions from database of current session.

o  SAVEPOINT - Creates points within groups of transactions in which to ROLLBACK.

o   SET TRANSACTION - Places a name on a transaction.

SQL Server Architecture

SQL Server Architecture is built with the following major components.

1.     The Network Interface Layer

2.     Relational Engine

3.     Storage Engine and

4.     SQL OS

  

 

 

1.     The Network Interface Layer

SQL server has a layer that enables its communication with the outer environment. This layer is the SQL Server Network Interface (SNI) layer. It comprises of three important components:

a) The Network Protocols

b) TDS (Tabular Data Stream)

c) The Protocol layer

a)   The Network Protocols:  There are four protocols supported by SQL Server.

·      Shared Memory: This is the default protocol used to connect to a client residing   locally (i.e. on the same machine).

·     TCP/IP: This protocol helps us to connect to the SQL server over the network via  an IP address and a port number. The default TCP port used by SQL Server is  1433.

·     Named Pipes: Originally developed for LAN, this protocol is similar to TCP/IP, but  is efficient for slower networks like WAN. It uses 445 as port number.

·  VIA: Virtual Interface Adapter is a protocol used for high performance  communication between two connected systems, with the help of specialized  hardware on both ends.

b)    Tabular Data Stream (TDS): A protocol used for interacting with database servers.     The moment a TCP/IP connection is established, a corresponding link to the TDS       endpoint is made for the client-server communication.

c)     The Protocol Layer: This layer unwraps the TDS packet that was created at the        client’s side. Also, the results sent to the client by the server, are packaged by this      layer.

 

 

2.     Relational Engine

Relational Engine also called as the query processor, It includes the components of SQL Server that determine what your query exactly needs to do and how best way to do it. It manages the execution of queries as it requests data from the storage engine and processes the results returned to Protocol layer.

The various tasks of Relational Engine are:

·         Query Processing

·         Memory Management

·         Thread and Task Management

·         Buffer Management

·         Distributed Query Processing

3.     Storage Engine

Storage Engine is responsible for storage and retrieval of the data on to the physical storage system (Hard Disk, SAN etc.). Let’s understand more in details as given below.

Any database in SQL server, there are two types of files (Data file and Log file) that are created at the disk level.

Data file - Data file physically stores the data in data pages.

Log file - Log file used for write logs which are used for storing transactions performed on the database.

See the following more details on data file and log file.

Data File: Data File stores data in the form of Data Page (8KB) and these data pages are logically organized in extents.

Extents: Extents are logical units in the database. They are a combination of 8 data pages i.e. 64 KB forms an extent. Extents can be of two types, Mixed and Uniform. Mixed extents hold different types of pages like index, system, data etc (multiple objects). On the other hand, Uniform extents are dedicated to only one type (object).

Pages: As we should know what type of data pages can be stored in SQL Server, below mentioned are some of them:

·   Data Page: It holds the data entered by the user but not the data which is of type text, ntext, nvarchar(max), varchar(max), varbinary(max), image and xml data.

·       Index: It stores the index entries.

·     Text/Image: It stores LOB ( Large Object Data) like text, ntext, varchar(max), nvarchar(max),  varbinary(max), image and xml data.

·      GAM & SGAM (Global Allocation Map & Shared Global Allocation Map): They are used for saving information related to the allocation of extents.

·      PFS (Page Free Space): Information related to page allocation and unused space available on pages.

·      IAM (Index Allocation Map): Information pertaining to extents that are used by a table or index per allocation unit.

·      BCM (Bulk Changed Map): Keeps information about the extents changed in a Bulk Operation.

·      DCM (Differential Change Map): This is the information of extents that have modified since the last BACKUP DATABASE statement as per allocation unit.

Log File: It also known as write ahead log. It stores modification to the database (DML and DDL). The sufficient information is logged to be able to:

·         Roll back transactions if requested.

·         Recover the database in case of failure.

·         Write Ahead Logging is used to create log entries.

o    Transaction logs are written in chronological order in a circular way.

o    Truncation policy for logs is based on the recovery model.

4.     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. It is a highly configurable operating system with powerful API (Application Programming Interface), enabling automatic locality and advanced parallelism. 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. Other services include exception handling, hosting for external components like Common Language Run-time, CLR etc. For more details refer the following link.

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/bac0637c-45e6-4569-a38b-331e3736a8ab/sql-server-architecture-diagram-and-explanation?forum=sqldatabaseengine

SQL Processing Architecture

SQL Processing Architecture represents how Microsoft SQL Server queries are processed on the client, how the various clients interact with SQL Server, and what SQL Server does to process clients' requests. The high-level diagram is given below. Let’s understand more details on this topic, refer the following link.
 

https://msdn.microsoft.com/en-us/library/aa226174(v=sql.70).aspx

Query Execution Evaluation Order

The Optimizer will try to find the most efficient way/plan to execute the query based on available information such as table Index and scanning mechanism.

The Query execution default order is:
1.     FROM  clause
2.     WHERE  clause
3.     GROUP BY clause
4.     HAVING  clause
5.     SELECT  clause
6.     ORDER BY  clause

Example:

SELECT d.DepartmentID, d.DepartmentName,

COUNT(e.EmployeeID) AS NumberOfEmployees
FROM Employee e
INNER JOIN Department d ON d.DepartmentID = e.DepartmentID
WHERE Gender ='M'
GROUP BY d.DepartmentID, d.DepartmentName
HAVING COUNT(EmployeeID)>=2
ORDER BY DepartmentName