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
·
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
o TRUNCATE
·
DQL (Data Query Language) – Allows users to select/fetch
data from database.
o
SELECT
·
DCL (Data Control Language) – Allows users to control
access to data within the database.
The various DCL commands are;
·
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
·
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
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.
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.
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
No comments:
Post a Comment