Hello everyone! Welcome to this first entry in the SQL and T-SQL course.
If you work in development, sooner or later you will come across a database. And to interact with them, the undisputed standard is SQL.
While SQL is a standard defined by organizations like ANSI and ISO, T-SQL extends this standard by adding procedural programming features.
SQL vs T-SQL: What’s the Difference?
It’s common to confuse both terms, but it’s important to understand the distinction:
- SQL (Standard SQL): It’s a declarative language designed to query and manipulate data. It tells us what we want to get, but not necessarily how the engine should do it internally.
- T-SQL (Transact-SQL): It’s a specific implementation by Microsoft. In addition to all the functions of standard SQL, it includes flow control structures, error handling, variables, and stored procedures.
Basically, T-SQL takes all the good parts of standard SQL and adds functionalities to allow us to program complex logic directly in the database.
Main Features of T-SQL
What makes T-SQL powerful is that it turns SQL into a complete programming language. This allows us to perform operations that go far beyond a simple SELECT or INSERT.
T-SQL incorporates logical structures we find in languages like C# or Java. We can use conditionals and loops:
IF...ELSE: For decision making.WHILE: For loops and repetitions.BEGIN...END: To define code blocks.
-- Simple example of logic in T-SQL
DECLARE @Cantidad int = 10;
IF @Cantidad > 5
BEGIN
PRINT 'There are more than 5 items';
END
ELSE
BEGIN
PRINT 'There are few items';
END
We can declare variables, assign them values, and use them throughout our script or stored procedure. This is essential for saving intermediate results or setting dynamic parameters.
DECLARE @NombreCliente NVARCHAR(50);
SET @NombreCliente = 'Luis Llamas';
SELECT * FROM Clientes WHERE Nombre = @NombreCliente;
T-SQL allows us to encapsulate code in reusable objects within the database.
- Stored Procedures: Blocks of code that can execute complex logic, transactions, and data modifications.
- User Defined Functions (UDF): Functions that return a value or a table, ideal for repetitive calculations.
One of the most critical parts in databases is ensuring data integrity. T-SQL offers robust tools for this:
TRY...CATCH: To capture and manage execution errors in a controlled way.TRANSACTION: To ensure that a set of operations are all performed or none (atomicity).
BEGIN TRY
BEGIN TRANSACTION;
-- We try to update a record
UPDATE Inventario SET Stock = Stock - 1 WHERE ProductoID = 10;
-- If everything goes well, we commit
COMMIT TRANSACTION;
END TRY
BEGIN CATCH
-- If something fails, we undo the changes
ROLLBACK TRANSACTION;
PRINT 'An error occurred: ' + ERROR_MESSAGE();
END CATCH
The ANSI Standard and Portability
Microsoft strives to keep T-SQL compatible with the ANSI SQL-92 standard and later versions. However, T-SQL includes many proprietary functions (like TOP, ISNULL, or specific date functions like GETDATE()).
Put simply:
- Every valid SQL command should work in T-SQL
- But not every T-SQL command will work in other databases (like MySQL or PostgreSQL)
When we write in T-SQL, we often have to choose between:
- Writing purely standard code (more portable, but sometimes more limited).
- Leveraging the specific power of T-SQL (better performance and features, but less portable).
In this course, we will focus on squeezing the most out of T-SQL, as we are working on SQL Server.
Why Learn T-SQL?
If you are going to work with SQL Server, T-SQL is essential. It’s the main language we use to interact with Microsoft SQL Server and Azure SQL Database.
But what if you don’t plan to use Microsoft forever? Is this course still worth it? The answer is yes.
SQL Foundation: 90% of the syntax you will use is Standard SQL. What you learn here will be immediately useful in MySQL, SQLite, or MariaDB.
Transferable Concepts: T-SQL is a procedural “dialect”. Other engines have their own (PostgreSQL has PL/pgSQL and Oracle has PL/SQL). But, even if a keyword changes, the concepts are identical.
If you master T-SQL, moving tomorrow to PostgreSQL or another system will be very easy, because you will already understand the logic behind advanced databases.
