Unlock T-SQL Mastery - Top Interview Questions by Category

Intro to T-SQL

Welcome to the beginning of your T-SQL journey! If you're looking to work with data in Microsoft SQL Server, understanding T-SQL is essential. Think of T-SQL as the language you use to talk to your database.

T-SQL stands for Transact-SQL. It's Microsoft's version of SQL (Structured Query Language). SQL is a standard language used for managing and manipulating data in relational databases. T-SQL builds upon the standard SQL with some additional features specific to Microsoft SQL Server.

Why do we need a language like T-SQL? Because data is often stored in databases, and we need a way to store, retrieve, update, and delete that data efficiently. This is where T-SQL comes in. You use T-SQL commands to perform these actions.

Databases are managed by software called Relational Database Management Systems (RDBMS). Microsoft SQL Server is one such RDBMS. T-SQL is the primary way you interact with SQL Server.

At its core, working with T-SQL involves writing queries. A query is basically a request for information or an instruction to perform an action on the database. You write a query in T-SQL, send it to the SQL Server, and it executes the command.

Getting started with T-SQL means understanding the basic commands and concepts. These fundamentals are the building blocks for more complex operations you'll perform as you progress. Mastering these basics is the first step towards becoming proficient in working with data in SQL Server.

Basic T-SQL Syntax

Understanding the fundamental building blocks of T-SQL is crucial for anyone starting out or preparing for interviews. Here are some common questions about basic syntax.

What is T-SQL?

T-SQL, or Transact-SQL, is Microsoft's proprietary extension to the SQL language. It is primarily used within Microsoft SQL Server and Azure SQL Database. T-SQL adds procedural programming elements like variables, loops, and conditional statements, as well as enhanced transaction control and error handling.

How do you select data from a table?

You use the SELECT statement to retrieve data. The basic syntax involves specifying the columns you want to see and the table they are in using the FROM clause.

SELECT ColumnName1, ColumnName2
FROM YourTableName;
  

To select all columns, use the asterisk (*):

SELECT *
FROM YourTableName;
  

Explain the WHERE clause.

The WHERE clause is used to filter records returned by the SELECT statement. It specifies a condition that rows must meet to be included in the result set.

SELECT ColumnName1
FROM YourTableName
WHERE Condition;
  

Conditions can use comparison operators (=, <>, >, <, >=, <=) and logical operators (AND, OR, NOT).

How do you add comments?

Comments are used to add explanatory notes within your T-SQL code. They are ignored by the SQL Server parser.

Use two hyphens (--) for a single-line comment:

SELECT * -- This is a single-line comment
FROM Sales;
  

Use /* ... */ for multi-line comments:

/*
  This is a comment
  that spans multiple lines.
*/
UPDATE Customers SET Status = 'Active';
  

Is T-SQL case-sensitive?

By default, T-SQL keywords and identifiers (like table or column names) are case-insensitive. For example, SELECT, select, and SeLeCt are treated the same. However, string comparisons within queries can be case-sensitive or insensitive depending on the specific database or server collation settings.

Data Types & Usage

Understanding SQL data types is fundamental for anyone working with databases, especially in T-SQL. They define the kind of data a column can hold, impacting everything from storage efficiency and data integrity to query performance. In interviews, expect questions testing your knowledge of common data types and how to choose the right one for a given scenario.

Choosing the correct data type ensures data accuracy and helps prevent errors. Using an efficient data type can also reduce the size of your database and speed up queries.

Common T-SQL Data Types

T-SQL offers a variety of data types to store different kinds of information. Here are some frequently encountered categories:

• Numeric Types: For storing numbers. These include exact numerics like INT, DECIMAL, BIGINT, and approximate numerics like FLOAT. Choose exact types when precision is critical, like for financial data.
• Character Strings: For storing text. CHAR stores fixed-length strings, while VARCHAR stores variable-length strings. NCHAR and NVARCHAR are used for Unicode characters, essential for multilingual data. VARCHAR(MAX) is often preferred for large text blocks.
• Date and Time Types: For storing temporal data. Examples include DATE, TIME, and DATETIME. Understanding the differences, like how TIMESTAMP might handle time zones differently than DATETIME, is important.
• Binary Types: For storing binary data like images or files. VARBINARY is a common choice.
• Other Types: Includes specialized types like BIT (for boolean-like values), UNIQUEIDENTIFIER (for GUIDs), XML, and spatial types.

Choosing the Right Type

Selecting the appropriate data type involves considering the nature of the data, the range of values, storage requirements, and performance implications. For instance, while you could store numbers in a character string column, it would prevent mathematical operations and could lead to sorting issues.

Interview Focus

Interview questions about data types often revolve around:

• Explaining the purpose of common data types.
• Differentiating between similar types (e.g., CHAR vs VARCHAR, DATETIME vs TIMESTAMP).
• Identifying the best data type for a specific kind of data (e.g., phone number, currency, website URL).
• Discussing implicit vs. explicit data type conversion using CAST and CONVERT.
• Understanding the limitations and storage characteristics of different types.

Operators & Logic

Understanding how to use operators and logic is key in T-SQL. These elements help you filter data, perform calculations, and control the flow of your queries and scripts. They are fundamental building blocks for more complex operations.

Common T-SQL Operators

T-SQL provides various operators for different purposes:

• Arithmetic Operators: Used for mathematical calculations. +, -, *, /, % -- Addition, Subtraction, Multiplication, Division, Modulo
• Comparison Operators: Used to compare values and return TRUE, FALSE, or UNKNOWN. =, != or <>, >, <, >=, <=
• Logical Operators: Used to combine conditions in a WHERE clause. AND, OR, NOT
• String Operators: For concatenating strings. + -- Also used for string concatenation

Logic in WHERE Clauses

Operators are most frequently used in the WHERE clause to filter rows based on specified conditions. Logical operators combine these conditions.

SELECT ProductName, UnitPrice
FROM Products
WHERE UnitPrice > 20
AND ProductName LIKE 'C%';
  

This query uses a comparison operator (>) and a logical operator (AND) to find products meeting two criteria.

Conditional Logic (CASE)

The CASE expression allows you to add if-then-else logic to your SQL statements, often used in the SELECT list or WHERE clause.

SELECT ProductName,
       CASE
           WHEN UnitsInStock < 10 THEN 'Low Stock'
           WHEN UnitsInStock < 50 THEN 'Medium Stock'
           ELSE 'High Stock'
       END AS StockStatus
FROM Products;
  

Mastering operators and conditional logic is crucial for writing effective and precise T-SQL queries.

Functions Explained

T-SQL functions are a fundamental part of database programming and a common topic in interviews. They allow you to encapsulate logic and reuse it across your queries and scripts. Understanding the different types of functions and how they are used is crucial.

What are T-SQL Functions?

In T-SQL, a function is a database object that takes zero or more input parameters, performs an action, and returns a value. Functions can be used in the SELECT clause, WHERE clause, and other parts of a SQL query.

Types of Functions

There are two main categories of functions in T-SQL:

• System Functions: Built-in functions provided by SQL Server. These cover a wide range of operations, including mathematical calculations, string manipulation, date and time operations, and system information.
• User-Defined Functions (UDFs): Functions created by users to perform specific tasks. UDFs can be scalar-valued (returning a single value) or table-valued (returning a result set).

Common Interview Questions

Be prepared to discuss the differences between scalar and table-valued functions, when to use a function versus a stored procedure, and potential performance considerations. Here are a few examples:

• Explain the difference between a scalar function and a table-valued function.
• When would you use a T-SQL function instead of a stored procedure?
• What are the potential performance issues with using scalar UDFs in queries?
• How do you create a simple scalar-valued function?

Scalar Function Example

Here is a basic example of a scalar-valued function that calculates the total amount based on quantity and price.

CREATE FUNCTION CalculateTotal (
    @Quantity INT,
    @Price DECIMAL(10, 2)
)
RETURNS DECIMAL(10, 2)
AS
BEGIN
    RETURN (@Quantity * @Price);
END;
GO

Table-Valued Function Example

An example of a table-valued function that returns a list of products above a certain price.

CREATE FUNCTION GetProductsAbovePrice (
    @MinPrice DECIMAL(10, 2)
)
RETURNS TABLE
AS
RETURN
(
    SELECT *
    FROM Products
    WHERE Price > @MinPrice
);
GO

Familiarizing yourself with creating and using these functions, as well as understanding their limitations and best practices, will be beneficial for your T-SQL interview preparation.

Joins & Relationships

Combining data from different tables is a core task in T-SQL. Joins are the primary way to achieve this, linking tables based on shared data. Mastering joins is key to writing powerful queries for data retrieval and analysis.

Types of SQL Joins

T-SQL provides several join types to handle different data combination needs:

• INNER JOIN: Returns only the rows where the join condition is met in both tables. This is the most common type.
• LEFT (OUTER) JOIN: Returns all rows from the left table and the matching rows from the right table. If no match exists in the right table, NULL values are returned for the right table's columns.
• RIGHT (OUTER) JOIN: Returns all rows from the right table and the matching rows from the left table. If no match exists in the left table, NULL values are returned for the left table's columns.
• FULL (OUTER) JOIN: Returns all rows from both tables, including rows where there is no match in the other table. NULL values appear where there is no match.
• CROSS JOIN: Returns the Cartesian product of the rows from the joined tables. This means every row from the first table is combined with every row from the second table. It does not require a join condition.

Key Concepts for Interviews

When discussing joins in an interview, be prepared to explain:

• The purpose and use case of each join type.
• How NULL values are handled, especially in outer joins.
• Using table aliases to simplify queries involving multiple tables or self joins.
• Joining three or more tables in a single query.
• Performance considerations and how different joins might impact query speed.

Subqueries, CTEs, Views

Understanding how to use nested queries, reusable expressions, and stored query definitions is key to writing efficient and organized T-SQL code. Interviews frequently assess your grasp of these concepts.

Subqueries

A subquery, or inner query, is a query embedded within another SQL statement. They can return a single value, a single row, multiple rows, or a table.

Common Interview Question: What is the main difference between a subquery and a JOIN?

JOINs combine data from two or more tables based on related columns, returning results horizontally. Subqueries are often used to filter data (e.g., using WHERE with IN, EXISTS) or provide values for columns, returning results that are then used by the outer query. While functionality can overlap, their structure and how they process data differ.

Here's a simple example of a subquery:

SELECT ProductName
FROM Products
WHERE CategoryID IN (SELECT CategoryID FROM Categories WHERE CategoryName = 'Beverages');

Common Interview Question: Explain a correlated subquery.

A correlated subquery is linked to the outer query; it references one or more columns from the outer query. It executes once for *each* row processed by the outer query, which can impact performance compared to non-correlated subqueries or JOINs.

CTEs (Common Table Expressions)

A CTE is a temporary, named result set that exists only for the duration of a single statement (SELECT, INSERT, UPDATE, DELETE, or MERGE).

Common Interview Question: What are the benefits of using CTEs?

CTEs improve query readability and maintainability, especially for complex queries. They can be used for recursive queries (processing hierarchical data) and can reference the same result set multiple times within the same statement.

Common Interview Question: How does a CTE differ from a temporary table?

A CTE is a logical construct defined within a query and is not physically stored. A temporary table is a physical table stored in the tempdb database and persists for the duration of the session or procedure. CTEs are best for simplifying complex logic within one statement, while temporary tables are suited for storing intermediate results used across multiple statements or procedures.

Views

A view is a stored query that acts like a virtual table. It doesn't store data itself (unless it's an indexed view) but provides a way to present data from one or more base tables.

Common Interview Question: What are the primary uses of views?

Views are used to simplify complex queries, provide a security layer by restricting access to specific data, and abstract the underlying table structure from users or applications.

Common Interview Question: Can you update data through a view? Explain.

Whether you can update data through a view depends on the view's definition. Simple views based on a single table without aggregations, GROUP BY, or DISTINCT are often updatable. Views involving joins on non-key columns, aggregations, or certain other complex operations are typically not updatable.

People Also Ask

Indexes & Performance

Understanding how to optimize database performance is crucial for any T-SQL professional. Indexes are a fundamental tool for improving query speed. This section covers key interview questions related to indexes and performance tuning in T-SQL.

Why Use Indexes?

Indexes are database objects that provide a fast way to locate data in table rows. Think of them like the index at the back of a book – they help the database engine find specific data without scanning the entire table, significantly speeding up data retrieval operations (SELECT queries).

Types of Indexes

The two primary types you'll encounter are:

• Clustered Index: This index determines the physical order of data rows in a table. A table can have only one clustered index. It's often built on the primary key. Searching on the clustered index key is very fast.
• Non-Clustered Index: This index does not affect the physical order of table rows. Instead, it contains the index key values and row locators (pointers to the actual data rows in the clustered index or heap). A table can have multiple non-clustered indexes. They are ideal for frequently searched columns that aren't part of the clustered index.

How Indexes Work

Most indexes in SQL Server use a B-tree structure. This hierarchical structure allows the database engine to quickly navigate from the root node down through intermediate nodes to the leaf nodes, where the index key values and row locators (or actual data, in the case of clustered indexes) are stored.

Performance Considerations

While indexes speed up data retrieval (SELECT), they can impact the performance of data modification operations (INSERT, UPDATE, DELETE). Whenever data is modified, the corresponding indexes on the table must also be updated. Therefore, having too many indexes, or indexes on columns that change frequently, can slow down these operations. It's a balance between read and write performance.

Index Maintenance

Indexes can become fragmented over time as data is inserted, updated, and deleted. Fragmentation can reduce index efficiency. Regular maintenance is important:

• Reorganize: Physically reorders the leaf pages of the index to match the logical order of the leaf nodes. It's an online operation (the index is available during the process) and less resource-intensive.
• Rebuild: Drops and recreates the index. This process removes fragmentation, compacts the index pages, and updates statistics. It can be performed online or offline, depending on the SQL Server edition and options. It is typically more resource-intensive than reorganizing.

Finding Performance Issues

Tools and techniques like SQL Server Profiler, Extended Events, Dynamic Management Views (DMVs), and execution plans are used to identify performance bottlenecks and queries that could benefit from indexing or query tuning. Looking at execution plans helps understand how the database engine is accessing data and whether indexes are being used effectively.

Procedures & Triggers

Stored Procedures and Triggers are fundamental components of T-SQL and relational database management. They allow for complex operations and automated actions within the database layer. Interviewers often ask about them to gauge your understanding of database logic and control flow.

Stored Procedures

A stored procedure is a prepared SQL code that you can save, so the code can be reused over and over again.

Common interview questions include:

• What are the benefits of using stored procedures?
• How do you pass parameters to a stored procedure?
• What is the difference between IN, OUT, and INOUT parameters?
• Explain the difference between a stored procedure and a function.
• How can stored procedures improve performance?

Triggers

A trigger is a special type of stored procedure that runs automatically when an event occurs in the database server. Events include INSERT, UPDATE, and DELETE operations on a table.

Key trigger concepts often tested:

• What is a trigger and when does it execute?
• What are the different types of triggers in T-SQL (e.g., AFTER, INSTEAD OF)?
• What are the INSERTED and DELETED magic tables used for?
• Provide use cases for triggers (e.g., auditing, enforcing complex business rules).
• What are some potential drawbacks of using triggers?

Mastering these concepts is crucial for demonstrating your proficiency in building robust and efficient database applications using T-SQL.

Transactions & Locks

Understanding transactions and locks is key for managing concurrent data access and ensuring data integrity in T-SQL. These concepts are fundamental in multi-user database environments.

A transaction is a single unit of work. Either all operations within a transaction complete successfully, or none of them do. This adheres to the ACID properties:

• Atomicity: All or nothing.
• Consistency: Ensures data is valid after the transaction.
• Isolation: Transactions operate independently until committed.
• Durability: Changes are permanent once committed.

Transactions are controlled using statements like BEGIN TRANSACTION (or BEGIN TRAN), COMMIT TRANSACTION (to save changes), and ROLLBACK TRANSACTION (to undo changes).

Locks are mechanisms SQL Server uses to manage multiple users accessing the same data simultaneously. They prevent conflicts like read/write or write/write issues. When a transaction needs to read or modify data, it acquires a lock on that data.

Common lock types include Shared (S) locks (for reading data, allowing multiple shared locks) and Exclusive (X) locks (for modifying data, preventing any other locks). Other types like Update (U) and Intent locks also exist.

Mismanagement of transactions and locks can lead to concurrency issues such as blocking (one transaction waiting for another to release a lock) and deadlocks (two or more transactions mutually blocking each other, requiring one to be terminated). Understanding these concepts is vital for writing efficient and reliable T-SQL code.