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

·         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

·         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
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


95 comments:

  1. Nice article thanks for given this information. I hope it useful to many People
    SQL Azure Online Training
    Azure SQL Training
    SQL Azure Training

    ReplyDelete
  2. As we know there are many companies which are converting into Big data app development. with the right direction we can definitely predict the future.

    ReplyDelete
  3. https://programmingtutorialsscript.blogspot.com/

    ReplyDelete
  4. Really nice blog, very infromative. You can refer more topics related to SQL like SQL Commands and Advanced SQL from here for future articles
    thanks !

    ReplyDelete
  5. https://programmingtutorialsscript.blogspot.com/

    ReplyDelete
  6. https://programmingtutorialsscript.blogspot.com/

    ReplyDelete
  7. Really nice blog, very infromative. You can refer more topics related to SQL like SQL Commands and PLSQL, PLSQL cursors from here for future articles
    thanks !

    ReplyDelete
  8. Its a great post focusing on SQL and other aspects to find out how far can one go and utilise all these.

    SQL Server Load Soap API

    ReplyDelete
  9. "Our main objective is to teach everyone value
    Investing so that one can become financial independent "
    Visit

    ReplyDelete
  10. "Our main objective is to teach everyone value
    Investing so that one can become financial independent "
    Visit

    ReplyDelete
  11. Thank you for sharing this informative post. Looking forward to reading more.
    Best PHP Development Services

    ReplyDelete
  12. Thank you for sharing programming tutorials (https://www.programingtutorial.com/)

    ReplyDelete
  13. https://www.programingtutorial.com/

    ReplyDelete

  14. DevOps is a new trend in software development over the past few years. Although the term is relatively new, it is actually a combination of many practices that have been in use for years. DevOps allows software to be released quickly and efficiently, while still maintaining high levels of security. Azure devops backup

    ReplyDelete
  15. Thank you for such an informative blog and there are so many websites where you can learn SQL commands. MySQL developers can use the visual Query builder to drag and drop database tables, build SQL queries, build SQL Server database ad hoc reports, parameterized reports, online data dashboards, build D3 based charts, and schedule reports-all with point and click simplicity and database security. You can go for SQL tutorial on any website. Datasparc also provide online tool to run commands.

    ReplyDelete
  16. Thanks For the Great Content. I Will also share with my Friends & once again thanks a lot.
    .Net Online Training Hyderabad
    Visit us: dot net training

    ReplyDelete
  17. Train yourself in specific software modules to brush up your skills & shine in your career growth with the best software training institute in Chennai, Infycle Technologies. Infycle offers the Best Data Science Training in Chennai to serve the candidate's job profile requirements, including the top job placements in the MNC's. Rather than just teaching the theories, our fundamental aim is to make you a master by giving you live hands-on training. Therefore, individuals will be asked to work on the live tasks & real-time use cases that bring out the definite coder in you! To grab all these, call 7502633633 for a free demo.

    ReplyDelete
  18. Thanks for this post..SQL Server course has been created by Colt Steele who is an experienced and successful bootcamp instructor. He has condensed his SQL bootcamp curriculum into this online course very well. The course imparts a solid foundation in databases using MySQL, the most popular open source SQL database out there.
    HTML and CSS Online Course
    Programming Languages Course

    ReplyDelete
  19. Very useful information.Thankyou so much for this wonderful blog…Great work keep going. Looking for the best database services in Hyderabad hire Cyanous software solutions now.
    Best Database services in Hyderabad
    Best software & web development company in Hyderabad

    ReplyDelete
  20. It's really Good Blog!! Thank you for sharing with us..
    DevOps Training
    DevOps Online Training

    ReplyDelete
  21. Nice information. Thanks for sharing such an amazing article. For online training and Self Paced courses visit our site click4learning.com/

    ReplyDelete
  22. I am quite impressed with the content of this article.
    Visit us: Dot Net Online Course
    Visit us: .Net Online Training

    ReplyDelete
  23. Excellent Blog, I like your blog and It is very informative. Thank you.
    Visit us: RPA Ui Path Online Training
    Visit us: Ui Path Course Online

    ReplyDelete
  24. A decent blog consistently concocts new and energizing data and keeping in mind that perusing I have feel that this blog is truly have each one of those quality that qualify a blog to be a one. sql training

    ReplyDelete
  25. Good Post. I like your blog. Thanks for Sharing.
    Visit us: Core Java Online Course
    Visit us: Best Online Java Course

    ReplyDelete
  26. Extremely overall quite fascinating post. I was searching for this sort of data and delighted in perusing this one. Continue posting. A debt of gratitude is in order for sharing.Mobilemall Bangladesh

    ReplyDelete
  27. Infycle Technologies, the best software training institute cum placement center in Chennai offers the best Digital Marketing course in Chennai for freshers, students, and tech professionals at the best offers. In addition to the Digital Marketing Training, other in-demand courses such as AWS, DevOps, Data Science, Python, Selenium, Big Data, Java, Power BI, Oracle will also be trained with 100% practical classes. After the completion of training, the trainees will be sent for placement interviews in the top MNC's. Call 7504633633 to get more info and a free demo.Best Digital Marketing Course in Chennai | Infycle Technologies

    ReplyDelete
  28. Grab the extraordinary Oracle Course with PLSQL from Infycle Technologies, the best software training institute in Chennai. Infycle offers the Best Oracle PLSQL Training in Chennai, with various IT demanding courses such as Big Data, Python, DevOps, Selenium, Full-Stack development, etc., in complete hands-on practical training with professional tutors in the field. In addition to that, the mock interviews will be done for the candidates so that they can face the interviews with total confidence. To have all these within your hands, call 7502633633 for having a free demo.

    ReplyDelete
  29. The overview blog on SQL is really so interesting and how it opens an opportunity window for people who are specifically targetting this field. if you want to understand such topic further you can check out data science course in bangalore

    ReplyDelete
  30. Hi, This is so informative blog, I am doing course on SQL Online and this blog is really informative for me. Thank you for this blog!

    ReplyDelete
  31. Your blog has wonderful information regarding Cloud Services, I also have some valuable information regarding the Best Cloud Application Development Services in USA

    ReplyDelete
  32. Infycle Technologies, the top software training institute and placement center in Chennai offers the Best Digital Marketing course in Chennai for freshers, students, and tech professionals at the best offers. In addition to Digital Marketing, other in-demand courses such as DevOps, Data Science, Python, Selenium, Big Data, Java, Power BI, Oracle will also be trained with 100% practical classes. After the completion of training, the trainees will be sent for placement interviews in the top MNC's. Call 7504633633 to get more info and a free demo.

    ReplyDelete
  33. Infycle Technologies, the No.1 software training institute in Chennai offers the No.1 Big Data Hadoop Training in Chennai | Infycle Technologies for students, freshers, and tech professionals. Infycle also offers other professional courses such as DevOps, Artificial Intelligence, Cyber Security, Python, Oracle, Java, Power BI, Selenium Testing, Digital Marketing, Data Science, etc., which will be trained with 200% practical classes. After the completion of training, the trainees will be sent for placement interviews in the top MNC's. Call 7502633633 to get more info and a free demo.

    ReplyDelete
  34. Nice work, truly valuable to me.
    I hope you keep it up.
    We are offering best offshore development services then,
    Visit here:
    Iyrix Technologies
    Remote Software Developers
    Software Development Services
    SEO and Online Marketing Services

    ReplyDelete
  35. I liked this article very much. The content is very good. Keep posting.

    Best SQL Server Online Training Certification in Hyderabad

    ReplyDelete
  36. Great post. Thanks for sharing. Keep Sharing.
    Do you want to enroll for an Azure course in Delhi? . Then Ducat is of the finest option chosen by the student where they offer quality education under the guidance of experienced and trained teaching staff.

    ReplyDelete
  37. Hey! Love the way you write, I would like to thank you for sharing this interesting information. I appreciate reading it. This is very useful & informative for me. Thanks for sharing with us.
    Also check out
    Warehouse Rack
    Warehouse Storage Rack

    ReplyDelete
  38. Very informative. Now I got an idea about this topic. thanks for the content.
    CCNA Training In Chennai
    CCNA Training Online
    CCNA Course In Coimbatore

    ReplyDelete
  39. I really like and appreciate your post.Really thank you! Fantastic.
    sharepoint training
    sharepoint online training

    ReplyDelete
  40. Thanks for sharing. more impressive blog. thanks for shared with us.

    Java developer job available in Chennai

    ReplyDelete
  41. Thanks for this post...Elegant Training of SQL Server Course Dubai will help you gain a full understanding of this universal programming language. You'll start by learning key concepts and move on to more advanced topics as you progress through the lessons. By the end of the series, you'll have a solid working knowledge of SQL.
    Graphic Designing Course in Dubai
    best web Development courses Dubai
    Programming Languages Course Dubai

    ReplyDelete
  42. We provide support for all major databases, including Oracle MySQL, Percona MySQL, PostgreSQL, MongoDB, and associated platforms. Our experts have adequate knowledge of all databases. You are at the perfect destination to manage your database systems. Genex's foremost objective is clients satisfaction and works hard towards your needs. Data is a very important record and a confidential thing to safeguard.

    https://genexdbs.com/

    ReplyDelete
  43. This is the perfect webpage for everyone who hopes to understand this topic. You know a whole lot its almost tough to argue with you (not that I personally would want to…HaHa). You definitely put a brand new spin on a topic that's been written about for ages. Excellent stuff, just wonderful! Webinfotechsolution

    ReplyDelete
  44. https://mysqlknowledgebank.blogspot.com/2020/06/working-with-python-file-handling.html?showComment=1643910037422#c2650056577462506782

    ReplyDelete
  45. Great Post. Very informative. Keep Sharing!!

    Apply Now for Azure Training in Noida

    For more details about the course fee, duration, classes, certification, and placement call our expert at 70-70-90-50-90

    ReplyDelete
  46. I would say, there are three fundamental kinds of SQL Server Certifications.These are for processes, programming arrangements, and different arrangements. I'm intentionally barring related fields, for example, business knowledge or testing and approval to keep this answer clean.

    ReplyDelete
  47. Great Post. Very informative. Keep Sharing!!

    Apply Now for Azure Training Classes in Noida

    For more details about the course fee, duration, classes, certification, and placement call our expert at 70-70-90-50-90

    ReplyDelete
  48. For Education,jobs, news, health, Business, Astrology
    http://wisdommaterials.com/
    https://www.globalcompaniesinfo.com/
    http://wisdomallcodes.com

    ReplyDelete
  49. This post is so usefull and informaive keep updating with more information.....
    Selenium Training in Bangalore
    Selenium Course in Bangalore

    ReplyDelete
  50. Its a great post focusing on SQL and other aspects to find out how far can one go and utilise all these.
    sql tainining

    ReplyDelete
  51. Hi, I have just started to SQL Certification Online. and this blog is really informative for me. Keep us posted for this kind of valuable blog. Thank you for this blog!

    ReplyDelete