Understanding SQL, Its Role in Databases, and How It All Works Under the Hood - Updated

 

SQL, which stands for Structured Query Language, is the standardized language used to communicate with Relational Database Management Systems (RDBMS). It allows users to perform a wide variety of operations on data stored in relational databases—everything from querying and updating data to defining database structures and controlling access.

While SQL is standardized by ISO and ANSI, most RDBMS platforms implement their own version of SQL with proprietary extensions. Popular systems include Microsoft SQL Server, Oracle, MySQL, PostgreSQL, Sybase, IBM DB2, Access, and Informix. Despite variations, all of them support the core SQL language.

SQL Server Internals: It’s More Than Just a Database

Many people don’t realize that a modern RDBMS like Microsoft SQL Server is much more than a data store—it operates almost like its own mini operating system. It has its own memory manager, process scheduler, I/O subsystem, security model, and storage engine. This underlying architecture is designed to manage data efficiently and ensure transactional consistency, performance, and scalability.

When you issue a SQL command, you're not interacting directly with disk-based tables. Instead, SQL Server parses and compiles your command, generates an execution plan, and handles memory management, concurrency, and transaction logging to make sure everything runs safely and quickly.

Types of SQL Commands: DDL vs DML

SQL commands are typically categorized into different types based on what they do. The two most important are:

1. DML (Data Manipulation Language)

These are commands used to manipulate data within existing database structures. They're the most commonly used in day-to-day queries and transactions. Examples include:

  • SELECT – Retrieves data from one or more tables.

  • INSERT – Adds new rows of data to a table.

  • UPDATE – Modifies existing data.

  • DELETE – Removes data from a table.

DML statements interact with the data itself, and are subject to transactions—meaning you can commit or roll back changes.

2. DDL (Data Definition Language)

These commands are used to define or alter the structure of database objects like tables, indexes, and schemas. Common DDL commands include:

  • CREATE – Creates a new object (e.g., table, view, index).

  • ALTER – Changes the structure of an existing object.

  • DROP – Deletes an object from the database.

  • TRUNCATE – Removes all rows from a table but retains its structure.

DDL statements affect the schema of the database and are typically auto-committed—meaning changes take effect immediately and can't be rolled back like DML.

SQL Is Everywhere, but Not Always the Same

While SQL is a standardized language, every database vendor has its own "dialect." For example:

  • SQL Server has T-SQL (Transact-SQL)

  • Oracle uses PL/SQL (Procedural Language/SQL)

  • PostgreSQL supports PL/pgSQL

  • MySQL includes its own procedural extensions

Each dialect extends standard SQL with proprietary functions, error handling, looping, and more—so while basic commands are portable, advanced features often require rewriting between platforms.

Final Thoughts

Whether you're writing a simple SELECT query or managing an enterprise-level database system, SQL is the core language that makes it all possible. Understanding the difference between DDL and DML, how RDBMSs like SQL Server work under the hood, and how SQL varies across platforms is essential knowledge for any serious database professional.

In short: SQL is not just about running queries—it's about understanding how data is structured, managed, and secured from the ground up.

Comments

Post a Comment

Popular posts from this blog

Free Monthly Budget Spreadsheet (UK-Friendly)

Want to take control of your finances and live more frugally? I've created a simple, easy-to-use monthly budget spreadsheet — perfect for UK households. Track your income, bills, spending, savings, and more in one place. 📊 What’s Included: Income and bills tracker Subscriptions and everyday expenses Savings and debts planner Savings goals section — plan and visualise your future funds £1 to £365 Savings Challenge — stay motivated as you save! 💡 What is the £1 to £365 Savings Challenge? This challenge helps you gradually save £665 in a year. You simply save an amount between £1 and £365 — one of each — and tick them off as you go. You can save the biggest amounts first, smallest first, or mix it up depending on your budget that week. It’s a flexible and fun way to build savings! 📌 Sample Data & Formulas Each sheet includes sample data to show how to use it. Please delete or replace only the numbers — not the formulas. Any field with calculation...
Read more
Image
👨‍💻 Helping Kai Build His First Front-End App It’s been a few weeks since my last post with Kai. We’ve been heads-down coding together, and instead of just talking about database security, we started building a working front end for his application. This has been a big step for Kai — going from SQL tables and stored procedures to actually seeing his app come alive in the browser. We used C# with ASP.NET Web Forms (ASPX) to create login and home pages, and then expanded into pages for courses, students, lectures, grades, and even a small library system.     🔑 Step 1: Building the Login Page Before you can get into the app, you need a secure login. We reused our earlier hashed + salted password system but wrapped it with an ASPX login form. <asp:TextBox ID="txtUsername" runat="server" Placeholder="Username"></asp:TextBox> <asp:TextBox ID="txtPassword" runat="server" TextMode="Password" Placeholder=...
Read more

Financial Literacy and ADHD – Money, Mistakes, and Learning the Hard Way

Let’s talk money. Not the flashy kind with yachts and investment portfolios. I mean the real stuff—bills, debt, impulse buys, budgeting apps, and that sinking feeling when your card gets declined at Tesco. I grew up without much. Council estate life doesn’t come with lessons in financial planning. You learn fast—or you don’t eat. But ADHD made learning about money even harder. It’s not just about being “bad with money.” ADHD affects how we make decisions, process consequences, and handle stress. Here’s what ADHD and money looks like for me: I once gave away my phone to someone who needed it… and forgot to cancel the bill. It cost me thousands . I’ve bought gadgets on a whim, convinced they’d change my life, only for them to gather dust. I’ve missed payments, not because I didn’t have the money—but because I forgot the due date. I’ve signed up to trials and subscriptions I forgot to cancel. I’ve had months where I lived like a millionaire, followed by months where I ...
Read more