T-SQL Fundamentals & Syntax
7 questions found
What is T-SQL, and how does it relate to standard SQL?
Beginner
T-SQL, short for Transact-SQL, is Microsoft's extension of standard SQL used specifically in SQL Server, adding extra features beyond the standard SQL language, such as procedural programming constructs like variables, loops, and error handling, that let you write more powerful logic directly within the database.
DECLARE @Count INT;
SET @Count = 10;
IF @Count > 5
PRINT 'Count is greater than five';
Real-world example
A developer familiar with standard SQL learns T-SQL's additional procedural features, like variables and IF statements, to build more sophisticated logic directly within SQL Server stored procedures.
Common follow-ups: What specific features does T-SQL add beyond standard ANSI SQL?;Are T-SQL scripts portable to other database systems like MySQL or PostgreSQL?
Stored Procedures & Functions;Error Handling with TRY CATCH
How do you declare and use a variable in T-SQL?
Beginner
You declare a variable using the DECLARE keyword followed by its name, prefixed with an at sign, and its data type, then assign it a value using either SET or SELECT, after which you can reference that variable anywhere within the same batch or procedure to store and reuse values throughout your script.
DECLARE @CustomerName VARCHAR(100);
SET @CustomerName = 'John Smith';
SELECT * FROM Customers WHERE Name = @CustomerName;
Real-world example
A script declares a variable to hold a customer's name, reusing that same variable across several subsequent queries instead of repeating the literal value throughout the script.
Common follow-ups: What is the difference between using SET and SELECT to assign a value to a variable?;What is the scope of a variable declared inside a stored procedure?
Dynamic SQL;Stored Procedures & Functions
How do IF and WHILE control flow statements work in T-SQL, and what kinds of tasks are they typically used for?
Intermediate
IF lets you conditionally execute a block of code only when a specified condition is true, while WHILE repeatedly executes a block of code as long as its condition remains true, and together these constructs let you build procedural logic, such as looping through a set of values or branching behavior based on data conditions, directly within your SQL scripts.
DECLARE @Counter INT = 1;
WHILE @Counter <= 5
BEGIN
PRINT 'Iteration ' + CAST(@Counter AS VARCHAR);
SET @Counter = @Counter + 1;
END;
Real-world example
A maintenance script uses a WHILE loop to process a batch of records in smaller chunks, checking a condition after each iteration to determine whether to continue processing or stop.
Common follow-ups: When should a WHILE loop be avoided in favor of a set based approach?;Can you use BREAK and CONTINUE inside a WHILE loop in T-SQL?
Cursors;Query Optimization & Plans
What is the difference between a batch and a transaction in T-SQL, and how does the GO keyword relate to batches?
Intermediate
A batch is a group of one or more T-SQL statements sent to SQL Server together for parsing and execution as a single unit, separated using the GO keyword, which is actually a client tool command rather than real T-SQL, while a transaction is a separate concept controlling atomicity of data changes, and a single batch can contain zero, one, or several transactions within it.
DECLARE @Message VARCHAR(50) = 'Hello';
PRINT @Message;
GO
-- This starts a new batch, and @Message is no longer in scope here
PRINT @Message; -- this would cause an error
Real-world example
A developer troubleshooting a confusing error learns that a variable declared before a GO statement is not available afterward, since GO starts an entirely new batch with its own separate variable scope.
Common follow-ups: Why does a variable's scope end at a GO statement?;Can you use GO inside a stored procedure definition?
Transactions & ACID;Stored Procedures & Functions
How do CASE expressions work in T-SQL, and how can they be used to implement conditional logic directly within a SELECT statement?
Advanced
A CASE expression evaluates one or more conditions in order and returns a corresponding value for the first matching condition, or a default value if none match, letting you implement conditional logic like categorizing values or transforming data directly within a query, without needing separate application code or complex branching statements.
SELECT OrderId, Amount,
CASE
WHEN Amount > 1000 THEN 'High Value'
WHEN Amount > 100 THEN 'Medium Value'
ELSE 'Low Value'
END AS OrderCategory
FROM Orders;
Real-world example
A sales report categorizes each order as high, medium, or low value directly within the query using a CASE expression, avoiding the need to perform this categorization logic separately in the application code.
Common follow-ups: What is the difference between a simple CASE expression and a searched CASE expression?;Can a CASE expression be used inside a WHERE clause?
Aggregate Functions & GROUP BY;Data Types & Schema Design
How would you write a T-SQL script that combines variables, control flow, and error handling together to perform a robust, multi step administrative task?
Advanced
You would declare variables to track state throughout the script, use control flow statements like WHILE loops or IF conditions to implement the required logic, wrap risky operations in a TRY CATCH block to gracefully handle any errors, and use PRINT statements or a logging table to provide visibility into the script's progress and outcome.
DECLARE @TableName VARCHAR(100), @RowCount INT;
BEGIN TRY
SELECT @RowCount = COUNT(*) FROM Orders WHERE Status = 'Pending';
IF @RowCount > 0
BEGIN
PRINT CAST(@RowCount AS VARCHAR) + ' pending orders found';
END
END TRY
BEGIN CATCH
PRINT 'Error: ' + ERROR_MESSAGE();
END CATCH;
Real-world example
A database administrator writes a comprehensive maintenance script combining variables, conditional logic, and proper error handling to safely check and report on pending orders across the system every night.
Common follow-ups: What logging strategy works well for a long running administrative script like this?;How do you test a complex T-SQL script safely before running it in production?
Error Handling with TRY CATCH;SQL Server Agent & Job Scheduling
What are the differences between the various string functions available in T-SQL, such as SUBSTRING, CONCAT, and TRIM?
Intermediate
SUBSTRING extracts a portion of a string starting at a specific position, CONCAT combines multiple strings together while automatically handling NULL values gracefully, and TRIM removes unwanted leading and trailing spaces or specified characters from a string, together giving you a versatile toolkit for cleaning and manipulating text data directly within your queries.
SELECT CONCAT(FirstName, ' ', LastName) AS FullName,
TRIM(Email) AS CleanEmail,
SUBSTRING(PhoneNumber, 1, 3) AS AreaCode
FROM Customers;
Real-world example
A customer data cleanup script combines several string functions together, building a clean full name, trimming whitespace from email addresses, and extracting area codes from phone numbers all within a single query.
Common follow-ups: How does CONCAT handle NULL values differently from using the plus operator to combine strings?;What characters does TRIM remove by default if none are specified?
Data Types & Schema Design;Query Optimization & Plans