T-SQL

T-SQL (Transact-SQL) is an extension of SQL (Structured Query Language) developed by Microsoft for managing and querying data in Microsoft SQL Server. It builds on SQL by adding additional features and functionalities, such as procedural programming, local variables, error handling, and transaction control.

Here are some of the key aspects and features of T-SQL:

1. Procedural Extensions

T-SQL includes programming constructs such as IF...ELSE, WHILE loops, and CASE statements, which allow more control and logic in your queries.

You can declare and use variables within a T-SQL script, making it easier to store intermediate results and control the flow of the script.

1. Stored Procedures and Functions

T-SQL allows you to create stored procedures and functions, which are precompiled code blocks that perform specific tasks. These can be used to encapsulate complex logic and can improve performance by reducing the need to recompile SQL statements.

Functions in T-SQL can be scalar (returning a single value) or table-valued (returning a result set).

1. Error Handling

T-SQL provides error handling through the TRY...CATCH construct, allowing you to catch errors within a script and take appropriate action, such as rolling back a transaction or logging an error.

1. Transactions

T-SQL supports transactions, which ensure that a group of SQL operations either all succeed or all fail, maintaining data integrity. You can use commands like BEGIN TRANSACTION, COMMIT, and ROLLBACK to control transactions.

1. Dynamic SQL

T-SQL supports dynamic SQL, allowing you to construct SQL statements as strings and execute them using the EXEC command or sp_executesql. This is useful for cases where the SQL structure is not known until runtime but can introduce security risks if not used carefully.

1. Common Table Expressions (CTEs)

T-SQL allows the use of Common Table Expressions (CTEs), which provide a temporary result set that you can reference within a query. CTEs are helpful for breaking down complex queries or recursive queries.

1. Built-in Functions

T-SQL offers a wide range of built-in functions, such as string functions (LEN, SUBSTRING), date functions (GETDATE, DATEADD), and system functions (@@IDENTITY, SCOPE_IDENTITY), which can simplify data manipulation.

1. Temporary Tables

T-SQL lets you create temporary tables that exist only within the scope of a session or a stored procedure, useful for intermediate data storage in complex queries.

Example: T-SQL Code for a Stored Procedure

Here's an example of a T-SQL stored procedure that retrieves customer orders based on their ID:

CREATE PROCEDURE GetCustomerOrders

@CustomerId INT

AS

BEGIN

SET NOCOUNT ON;

-- Error handling with TRY...CATCH

BEGIN TRY

    SELECT OrderId, OrderDate, TotalAmount

    FROM Orders

    WHERE CustomerId = @CustomerId;

END TRY

BEGIN CATCH

    PRINT 'An error occurred while fetching orders';

END CATCH

END;

Comparison with PL/SQL

If you're familiar with PL/SQL (Oracle's extension to SQL), here are some differences:

Platform: T-SQL is for Microsoft SQL Server, while PL/SQL is for Oracle databases.

Syntax and Functions: While similar, there are differences in syntax, functions, and built-in packages between T-SQL and PL/SQL.

Procedural Constructs: Both offer procedural extensions, but the syntax can vary, and each has its unique functions and packages.