Top 100 T-SQL Interview Questions and Answers 2023

2023-03-10

Join Telegram
Join Whatsapp Groups

Top 100 T-SQL Interview Questions and Answers: T-SQL, or Transact-SQL, is a dialect of SQL (Structured Query Language) used in Microsoft SQL Server. It is widely used for managing and querying relational databases. For individuals aspiring to become T-SQL developers or administrators, T-SQL Technical Interview Questions are an integral part of the hiring process.

Table of Contents

T-SQL Technical Interview Questions

The Latest T-SQL Interview Questions and T-SQL Interview Questions for Freshers are designed to assess the candidate’s technical knowledge and problem-solving abilities. This article presents the latest and top 100 T-SQL interview questions and answers, including technical questions and questions for freshers, to help candidates prepare for their T-SQL interviews and increase their chances of success.

Top 100 T-SQL Interview Questions and Answers 2023

1. What is T-SQL, and what is its purpose?

T-SQL is a dialect of SQL used in Microsoft SQL Server
Its purpose is to manage and query relational databases
It enables users to perform various database-related operations, including creating tables, views, stored procedures, and more.

2. What is the difference between T-SQL and SQL?

SQL is a standard language used for managing relational databases
T-SQL is a proprietary extension of SQL, used specifically in Microsoft SQL Server
T-SQL provides additional functionality, such as stored procedures, triggers, and more.

3. How do you use the COALESCE function to handle NULL values in T-SQL?

The COALESCE function in T-SQL returns the first non-NULL value in a list. To use the function, simply pass a comma-separated list of values to the function, like this:

SELECT COALESCE(column1, column2, column3, ‘default value’) AS myColumn FROM myTable

In the example above, if column1 is NULL, the function will return column2. If column2 is also NULL, the function will return column3. If all three columns are NULL, the function will return ‘default value’.

4. What are the various data types available in T-SQL?

T-SQL supports various data types, including numeric, character, date/time, and more.
Numeric data types include INT, BIGINT, FLOAT, DECIMAL, and more.
Character data types include CHAR, VARCHAR, and TEXT.
Date/time data types include DATETIME, DATE, TIME, and more.

5. What is normalization, and how is it achieved in T-SQL?

Normalization is the process of organizing data in a database to reduce redundancy and improve data integrity.
It is achieved by breaking down large tables into smaller, more manageable tables, linked by relationships.
T-SQL provides various normalization techniques, including first normal form (1NF), second normal form (2NF), and third normal form (3NF).

6. What is the difference between INNER JOIN and OUTER JOIN in T-SQL?

INNER JOIN	OUTER JOIN
Returns only matched rows	Returns all matched and unmatched rows
Does not include NULL values	Can include NULL values for unmatched rows
Cannot be used with unmatched tables	Can be used with unmatched tables using LEFT or RIGHT keywords

Here’s a more detailed explanation of each difference:

INNER JOIN returns only the rows that have matching values in both tables being joined, while OUTER JOIN returns all rows from both tables, with NULL values for any unmatched rows.
INNER JOIN does not include NULL values, while OUTER JOIN can include NULL values for unmatched rows.
INNER JOIN cannot be used with tables that have no matching rows, while OUTER JOIN can be used with tables that have unmatched rows using LEFT or RIGHT keywords.
There are three types of OUTER JOIN: LEFT JOIN (returns all rows from the left table and any matching rows from the right table), RIGHT JOIN (returns all rows from the right table and any matching rows from the left table), and FULL JOIN (returns all rows from both tables and NULL values for any unmatched rows).

7. What are indexes in T-SQL, and how do they improve performance?

Indexes are structures that improve the performance of database queries by enabling faster data retrieval.
They achieve this by creating a copy of a portion of a table’s data in a separate structure.
When a query is executed, the database engine can use the index to find the data more quickly.

8. What is the purpose of the RANK function in T-SQL, and how do you use it to rank data?

The RANK function in T-SQL is used to assign a rank to each row within a result set based on the values in one or more columns. To use the function, you must specify the column(s) to rank by in the OVER() clause. For example:

SELECT name, score, RANK() OVER (ORDER BY score DESC) AS rank FROM myTable

In the example above, the RANK function assigns a rank to each row based on the score column, in descending order. The row with the highest score will be assigned a rank of 1.

9. What is the purpose of a clustered index?

A clustered index determines the physical order of data in a table.
It defines the order in which data is stored on disk, based on the values in one or more columns.
A table can have only one clustered index.

10. What is the difference between CHAR and VARCHAR data types in T-SQL?

CHAR	VARCHAR
Fixed-length	Variable-length
Trailing spaces are added	Trailing spaces are not added
Takes up more storage space	Takes up less storage space
Faster than VARCHAR for fixed-length data	Slower than CHAR for fixed-length data

Here’s a more detailed explanation of each difference:

CHAR is a fixed-length data type, while VARCHAR is a variable-length data type. This means that CHAR always reserves the same amount of storage space, while VARCHAR can take up only the space needed for the actual data.
When storing data in CHAR columns, trailing spaces are added to make up the full length of the column. With VARCHAR, trailing spaces are not added.
Because CHAR always reserves the same amount of space, it takes up more storage space than VARCHAR. This can be a disadvantage if the data being stored varies greatly in length.
CHAR is faster than VARCHAR when dealing with fixed-length data, but slower than VARCHAR when dealing with variable-length data, due to the additional processing required to remove trailing spaces.
CHAR can be used for fixed-length data, such as country codes or phone numbers, while VARCHAR is more appropriate for variable-length data, such as names or addresses.

11. How do you create an index in T-SQL?

To create an index in T-SQL, use the CREATE INDEX statement.
Specify the table and column(s) to index, along with any other options, such as whether the index is clustered or non-clustered.

12. What is the purpose of a non-clustered index?

A non-clustered index is used to improve the performance of queries that do not involve sorting or grouping.
It creates a separate structure containing the indexed columns and a pointer to the corresponding table row.
A table can have multiple non-clustered indexes.

13. How do you optimize a query in T-SQL?

To optimize a query in T-SQL, you can use various techniques, including:

Using indexes to speed up data retrieval
Simplifying the query by removing unnecessary joins or subqueries
Reducing the number of columns retrieved
Using the EXPLAIN or SHOWPLAN statements to analyze the query execution plan

14. How do you use the PIVOT and UNPIVOT operators to transform data in T-SQL?

The PIVOT operator in T-SQL is used to transform rows into columns, based on the values in a specified column. The UNPIVOT operator is used to transform columns into rows. For example:

SELECT *
FROM (SELECT product, region, sales FROM myTable) AS t
PIVOT (SUM(sales) FOR region IN (North, South, East, West)) AS p

In the example above, the PIVOT operator transforms the sales data from rows into columns, with each region as a separate column.

15. What is the difference between a stored procedure and a function in T-SQL?

A stored procedure is a block of code that performs one or more database operations.
It can be called from other T-SQL statements or applications.
A function is a block of code that returns a value.
It can be used in T-SQL statements to perform calculations or manipulate data.
Stored procedures can modify data, while functions cannot.
Stored procedures can have input/output parameters, while functions can only have input parameters.
Stored procedures are called using the EXECUTE statement, while functions are called as part of an expression or query.

16. What is the difference between TRUNCATE and DELETE commands in T-SQL?

TRUNCATE	DELETE
Removes all rows from a table	Removes specific rows from a table
Cannot specify a WHERE clause	Can specify a WHERE clause to filter rows
Resets identity column values	Does not reset identity column values
Faster than DELETE for large tables	Slower than TRUNCATE for large tables
Uses less transaction log space	Uses more transaction log space

Here’s a more detailed explanation of each difference:

TRUNCATE removes all rows from a table, while DELETE removes only specific rows that meet a given condition using a WHERE clause.
TRUNCATE cannot be used with a WHERE clause to filter rows, while DELETE can specify a WHERE clause.
TRUNCATE resets the identity column values to their seed values, while DELETE does not reset identity column values.
TRUNCATE is faster than DELETE when dealing with large tables, because it uses less transaction log space and does not log individual row deletions.
DELETE is slower than TRUNCATE for large tables, because it uses more transaction log space to log each individual row deletion.

17. How do you create a stored procedure in T-SQL?

Use the CREATE PROCEDURE statement.
Define input and output parameters.
Write T-SQL statements within the BEGIN and END blocks.
Use the EXECUTE or EXEC statement to execute the stored procedure.

18. How do you create a function in T-SQL?

Use the CREATE FUNCTION statement.
Define input parameters and return data type.
Write T-SQL statements within the BEGIN and END blocks.
Use the RETURN statement to return a value.

19. What is a trigger in T-SQL, and how do you create one?

A trigger is a special type of stored procedure that automatically executes in response to specific data modifications.
Use the CREATE TRIGGER statement.
Specify the trigger type (AFTER or INSTEAD OF) and event (INSERT, UPDATE, or DELETE).
Write T-SQL statements within the BEGIN and END blocks.

20. How do you use the ROW_NUMBER function in T-SQL to generate a unique row number for each row in a result set?

The ROW_NUMBER function in T-SQL is used to generate a unique row number for each row in a result set. To use the function, specify the column(s) to order by in the OVER() clause. For example:

SELECT name, score, ROW_NUMBER() OVER (ORDER BY score DESC) AS rownum FROM myTable

In the example above, the ROW_NUMBER function assigns a unique row number to each row, based on the score column in descending order.

21. How do you use the CASE statement in T-SQL?

The CASE statement is a conditional statement used to return different values based on different conditions.
Use the CASE statement with WHEN and THEN clauses to define conditions and corresponding values.
Use the ELSE clause to define a default value.

22. How do you use the IF statement in T-SQL?

The IF statement is a conditional statement used to execute code based on a condition.
Use the IF statement with the condition and the code to execute if the condition is true.
Optionally, use the ELSE clause to execute different code if the condition is false.

23. How do you use the WHILE statement in T-SQL?

The WHILE statement is a loop statement used to execute code repeatedly while a condition is true.
Use the WHILE statement with the condition and the code to execute within the loop.
Ensure that the condition eventually becomes false to avoid an infinite loop.

24. What is the difference between a temporary table and a table variable in T-SQL?

A temporary table is a physical table created in the tempdb database.
A table variable is a variable that represents a table in memory.
Temporary tables persist until they are explicitly dropped or until the session ends.
Table variables are destroyed when the batch or procedure ends.

25. What is the difference between TRUNCATE and DELETE commands in T-SQL?

TRUNCATE	DELETE
Removes all rows from a table	Removes specific rows from a table
Cannot specify a WHERE clause	Can specify a WHERE clause to filter rows
Resets identity column values	Does not reset identity column values
Faster than DELETE for large tables	Slower than TRUNCATE for large tables
Uses less transaction log space	Uses more transaction log space

Here’s a more detailed explanation of each difference:

TRUNCATE removes all rows from a table, while DELETE removes only specific rows that meet a given condition using a WHERE clause.
TRUNCATE cannot be used with a WHERE clause to filter rows, while DELETE can specify a WHERE clause.
TRUNCATE resets the identity column values to their seed values, while DELETE does not reset identity column values.
TRUNCATE is faster than DELETE when dealing with large tables, because it uses less transaction log space and does not log individual row deletions.
DELETE is slower than TRUNCATE for large tables, because it uses more transaction log space to log each individual row deletion.

26. How do you create a temporary table in T-SQL?

Use the CREATE TABLE statement with the # symbol to create a temporary table.
Define the table schema and optional constraints.
Use the DROP TABLE statement to drop the temporary table when it is no longer needed.

27. How do you create a table variable in T-SQL?

Use the DECLARE statement with the @ symbol to declare a table variable.
Define the table schema and optional constraints using the TABLE data type.
Populate the table variable using INSERT INTO statements.

28. How do you use the GROUP BY clause in T-SQL?

The GROUP BY clause is used to group rows based on one or more columns and perform aggregate calculations on the groups.
Use the GROUP BY clause with one or more columns to define the groups.
Use aggregate functions like SUM, AVG, COUNT, MAX, and MIN to perform calculations on the groups.

29. How do you use the GROUP BY clause in T-SQL to group rows by a specified column?

The GROUP BY clause in T-SQL is used to group rows by a specified column. When used with aggregate functions like SUM, AVG, and COUNT, the clause returns summary information for each group. For example:

SELECT product, SUM(sales) AS totalSales FROM myTable GROUP BY product

In the example above, the GROUP BY clause groups the rows by product, and the SUM function returns the total sales for each product.

30. What is the purpose of the HAVING clause in T-SQL?

The HAVING clause is used to filter or restrict the results of a GROUP BY query based on a condition.
It specifies a search condition for a group or an aggregate function used in the SELECT statement.
HAVING clause is used with GROUP BY clause to apply filter criteria to the grouped data.
It allows you to filter grouped data based on aggregate calculations, such as SUM, COUNT, AVG, MIN, MAX, etc.
It is used after the GROUP BY clause and before the ORDER BY clause.

31. How do you use the TOP keyword in T-SQL?

The TOP keyword is used to limit the number of rows returned in a query result set.
It is used with the SELECT statement to specify the number of rows to return.
It can be used with an ORDER BY clause to specify the order in which the rows are returned.
The TOP clause can be used with a percentage value to retrieve a percentage of rows from the result set.
The TOP clause can be used with a subquery to retrieve the top rows from a subquery result set.

32. How do you use the CASE statement in T-SQL to conditionally return a value?

The CASE statement in T-SQL is used to conditionally return a value based on a set of conditions. It is similar to an IF-THEN-ELSE statement in other programming languages. For example:

SELECT name, score,
CASE
WHEN score >= 90 THEN ‘A’
WHEN score >= 80 THEN ‘B’
WHEN score >= 70 THEN ‘C’
ELSE ‘F’
END

33. How do you use the UNION operator in T-SQL?

The UNION operator is used to combine the result sets of two or more SELECT statements into a single result set.
The columns in each SELECT statement must have the same data type and be in the same order.
The UNION operator removes duplicate rows from the result set.
The UNION operator returns all rows from both SELECT statements.
The UNION operator can be combined with the ALL keyword to retain duplicate rows in the result set.

34. What is the purpose of the EXCEPT operator in T-SQL?

The EXCEPT operator is used to retrieve rows from the first SELECT statement that do not exist in the second SELECT statement.
The columns in each SELECT statement must have the same data type and be in the same order.
The EXCEPT operator removes duplicate rows from the result set.
The EXCEPT operator returns only distinct rows from the first SELECT statement.

35. What is the purpose of the INTERSECT operator in T-SQL?

The INTERSECT operator is used to retrieve only the rows that exist in both SELECT statements.
The columns in each SELECT statement must have the same data type and be in the same order.
The INTERSECT operator removes duplicate rows from the result set.
The INTERSECT operator returns only distinct rows from the result set.

36. What is the difference between a stored procedure and a user-defined function in T-SQL?

Stored Procedure	User-Defined Function
Can perform any type of operation	Can only return a scalar value or a table
Does not necessarily return a value	Must return a value or table
Can have input and output parameters	Can only have input parameters
Cannot be used in SELECT statements	Can be used in SELECT statements
Can modify data in tables	Cannot modify data in tables directly

37. How do you use the EXISTS keyword in T-SQL?

The EXISTS keyword is used in conjunction with a subquery and returns true if the subquery returns any rows. The syntax for using EXISTS is as follows:

SELECT column1, column2, …
FROM table1
WHERE EXISTS (SELECT column1 FROM table2 WHERE condition);

38. How do you use the EXISTS keyword in T-SQL?

The EXISTS keyword is used to check whether a subquery returns any rows.
It is used in a WHERE clause to test for the existence of rows in a subquery.
If the subquery returns at least one row, the EXISTS condition is true.
The EXISTS condition is often used in conjunction with a correlated subquery, where the subquery references a table from the outer query.

39. What is the difference between the INNER JOIN and OUTER JOIN in T-SQL?

INNER JOIN returns only the matching rows from both tables, based on the specified join condition.
OUTER JOIN returns all the rows from one table and the matching rows from the other table based on the specified join condition.
There are three types of OUTER JOIN: LEFT JOIN, RIGHT JOIN, and FULL JOIN.
LEFT JOIN returns all the rows from the left table and the matching rows from the right table.
RIGHT JOIN returns all the rows from the right table and the matching rows from the left table.
FULL JOIN returns all the rows from both tables and includes NULL values for any unmatched rows.

40. How do you use the LEFT JOIN in T-SQL?

The LEFT JOIN operator returns all the rows from the left table and the matching rows from the right table based on the specified join condition.

To use a LEFT JOIN, specify the left table in the FROM clause, followed by the LEFT JOIN keyword, the right table, and the join condition in the ON clause.
The LEFT JOIN keyword can be abbreviated as LEFT OUTER JOIN.
If there is no matching row in the right table, the result will contain NULL values for the columns of the right table.

41. How do you use the RIGHT JOIN in T-SQL?

The RIGHT JOIN operator returns all the rows from the right table and the matching rows from the left table based on the specified join condition.
To use a RIGHT JOIN, specify the right table in the FROM clause, followed by the RIGHT JOIN keyword, the left table, and the join condition in the ON clause.
The RIGHT JOIN keyword can be abbreviated as RIGHT OUTER JOIN.
If there is no matching row in the left table, the result will contain NULL values for the columns of the left table.

42. What is the difference between a clustered and a non-clustered index in T-SQL?

Clustered Index	Non-Clustered Index
Determines physical order of data	Does not determine physical order of data
Only one per table	Multiple per table
Can improve performance for range queries	Can improve performance for point queries and some range queries
Requires more disk space	Requires less disk space
Can be used to enforce primary key or unique constraints	Cannot be used to enforce constraints

Here’s a more detailed explanation of each difference:

A clustered index determines the physical order of data in a table, while a non-clustered index does not. The clustered index defines the order in which data is physically stored on disk.
Only one clustered index can exist per table, while multiple non-clustered indexes can exist per table.
Clustered indexes can improve performance for range queries (such as BETWEEN), while non-clustered indexes can improve performance for point queries (such as WHERE column = value) and some range queries.
Clustered indexes require more disk space than non-clustered indexes, because the data is physically ordered on disk.
Clustered indexes can be used to enforce primary key or unique constraints, while non-clustered indexes cannot. Primary key and unique constraints are automatically implemented as clustered indexes.

43. What is a subquery in T-SQL, and how do you use one?

A subquery is a query that is nested inside another query.
It is used to retrieve data from one or more tables and use that data as a filter or a value in another query.
A subquery can be used in the SELECT, FROM, WHERE, and HAVING clauses of a SQL statement.
A subquery can return a single value, a single row, or multiple rows.
A subquery can be a correlated subquery, where the subquery references a table from the outer query.
A subquery can be used with comparison operators such as IN, ANY, ALL, EXISTS, and NOT EXISTS to filter data based on the results of the subquery.

44. How do you use the LEFT JOIN in T-SQL?

LEFT JOIN returns all the rows from the left table and matching rows from the right table. The syntax for using LEFT JOIN is as follows:

SELECT column1, column2, …
FROM table1
LEFT JOIN table2 ON table1.column = table2.column;

45. How do you use the IN operator in T-SQL?

The IN operator allows you to specify multiple values in a WHERE clause.

Syntax:

SELECT column_name FROM table_name WHERE column_name IN (value1, value2, …);

Example: SELECT product_name FROM products WHERE category_id IN (1, 3, 5);

46. How do you use the BETWEEN operator in T-SQL?

The BETWEEN operator allows you to retrieve values within a specific range in a WHERE clause.

Syntax:

SELECT column_name FROM table_name WHERE column_name BETWEEN value1 AND value2;

Example: SELECT employee_name FROM employees WHERE salary BETWEEN 30000 AND 50000;

47. How do you use the LIKE operator in T-SQL?

The LIKE operator is used to match patterns in a WHERE clause.

Syntax:

SELECT column_name FROM table_name WHERE column_name LIKE pattern;

Example: SELECT customer_name FROM customers WHERE customer_name LIKE ‘A%’;

48. What is the purpose of the ORDER BY clause in T-SQL?

The ORDER BY clause is used to sort the result set in ascending or descending order based on one or more columns.

Syntax:

SELECT column_name FROM table_name ORDER BY column_name [ASC | DESC];

Example: SELECT product_name, unit_price FROM products ORDER BY unit_price DESC;

49. How do you use the ROW_NUMBER function in T-SQL?

The ROW_NUMBER function assigns a unique sequential number to each row within a result set.

Syntax:

SELECT ROW_NUMBER() OVER (ORDER BY column_name) AS row_num, column_name FROM table_name;

Example: SELECT ROW_NUMBER() OVER (ORDER BY hire_date) AS row_num, employee_name FROM employees;

50. What is the purpose of the RANK function in T-SQL?

The RANK function assigns a rank to each row within a result set based on the values in one or more columns.

Syntax:

SELECT RANK() OVER (ORDER BY column_name) AS rank_num, column_name FROM table_name;

Example: SELECT RANK() OVER (ORDER BY sales_amount DESC) AS rank_num, employee_name FROM employees;

51. How do you use the OVER keyword in T-SQL?

The OVER keyword is used in conjunction with aggregate functions and ranking functions to perform calculations over a specific set of rows within a result set.

Syntax:

SELECT aggregate_function(column_name) OVER (PARTITION BY column_name) FROM table_name;

Example: SELECT SUM(sales_amount) OVER (PARTITION BY employee_id) AS total_sales FROM sales;

52. What is the purpose of the COALESCE function in T-SQL?

The COALESCE function returns the first non-null expression in a list of expressions.

Syntax:

SELECT COALESCE(expression1, expression2, …) FROM table_name;

Example: SELECT COALESCE(shipper_name, ‘Unknown’) AS shipper_name FROM orders;

53. What is the purpose of the NULLIF function in T-SQL?

The NULLIF function returns null if two expressions are equal; otherwise, it returns the first expression.

Syntax:

SELECT NULLIF(expression1, expression2) FROM table_name;

Example: SELECT NULLIF(discount, 0) AS discount_percentage FROM orders;

54. How do you use the TRY…CATCH block in T-SQL?

The TRY…CATCH block is used to handle errors in T-SQL code.

Syntax:

BEGIN TRY — T-SQL code here END TRY BEGIN CATCH — Error handling code here END CATCH

Example: BEGIN TRY SELECT 1/0; END TRY

55. What is the purpose of the RAISERROR function in T-SQL?

The RAISERROR function is used to raise an error and return a message to the application.
It can be used to provide custom error messages, severity levels, and state codes.

56. What is the difference between COUNT(*) and COUNT(column_name) in T-SQL?

Feature	COUNT(*)	COUNT(column_name)
Definition	Returns the number of rows in a table	Returns the number of non-null values in a column
Usage	Can be used to get the total number of rows in a table.	Can be used to count non-null values in a specific column.
Performance	May require a table scan to count all rows	Can use an index if specified column has one
NULL values	Counts all rows, including those with NULL values	Counts only non-null values in specified column
Syntax	`SELECT COUNT(*) FROM table_name`	`SELECT COUNT(column_name) FROM table_name`
Example	`SELECT COUNT(*) FROM Customers`	`SELECT COUNT(City) FROM Customers`
Notes	COUNT(*) returns the total number of rows in the table, regardless of whether the column contains NULL values or not.	COUNT(column_name) only counts non-null values in the specified column.

57. How do you use the RIGHT JOIN in T-SQL?

RIGHT JOIN returns all the rows from the right table and matching rows from the left table. The syntax for using RIGHT JOIN is as follows:

SELECT column1, column2, …
FROM table1
RIGHT JOIN table2 ON table1.column = table2.column;

58. What is the purpose of the @@ERROR function in T-SQL?

The @@ERROR function returns the error number of the last executed statement in the current session.
It can be used to check for errors in T-SQL code and to handle them accordingly.

59. How do you use the SET NOCOUNT statement in T-SQL?

The SET NOCOUNT statement is used to stop the message that shows the number of rows affected by a T-SQL statement.
It is used to improve the performance of T-SQL code by reducing network traffic.

60. What is the purpose of the SET ROWCOUNT statement in T-SQL?

The SET ROWCOUNT statement is used to limit the number of rows returned by a T-SQL query.
It is used to improve the performance of T-SQL code by reducing the amount of data returned.

61. What is the purpose of the SET IDENTITY_INSERT statement in T-SQL?

The SET IDENTITY_INSERT statement is used to allow explicit values to be inserted into an identity column.
It is used when you want to insert a specific value into an identity column instead of using the auto-increment feature.

62. How do you use the BEGIN TRANSACTION statement in T-SQL?

The BEGIN TRANSACTION statement is used to start a new transaction in T-SQL.
It is used to group a set of T-SQL statements into a single transaction.

63. How do you use the COMMIT TRANSACTION statement in T-SQL?

The COMMIT TRANSACTION statement is used to commit a transaction in T-SQL.
It is used to make permanent changes to the database after a transaction has been successfully completed.

64. What is the purpose of the ROLLBACK TRANSACTION statement in T-SQL?

The ROLLBACK TRANSACTION statement is used to roll back a transaction in T-SQL.
It is used to undo any changes made to the database during a transaction that has not been committed.

65. What is the purpose of the @@TRANCOUNT function in T-SQL?

The @@TRANCOUNT function returns the number of active transactions in the current session.
It is used to determine if a transaction is already in progress before starting a new one.

66. What is the purpose of the WAITFOR statement in T-SQL?

The WAITFOR statement is used to suspend the execution of a T-SQL batch or transaction for a specified period of time.
It is used to simulate a delay or pause in the execution of T-SQL code.

67. How do you use the CAST and CONVERT functions in T-SQL?

The CAST and CONVERT functions are used to convert data from one data type to another.
Use the CAST function to convert values to a specified data type.
Use the CONVERT function to convert values to a specified data type with optional format options.

68. What is the difference between the ISNULL and COALESCE functions in T-SQL?

Feature	ISNULL	COALESCE
Definition	Returns the first non-NULL expression	Returns the first non-NULL expression
Usage	Replaces NULL values with a specified value	Returns the first non-NULL expression in a list of expressions
Data types	Only works with two arguments	Can work with multiple arguments
Syntax	`ISNULL(expression, replacement_value)`	`COALESCE(expression1, expression2, ...)`
Example	`SELECT ISNULL(ProductName, 'N/A')`	`SELECT COALESCE(ProductName, 'N/A')`
Default value	Can only specify a single default value	Can specify multiple default values
Performance	Faster for two arguments than COALESCE	Slower for two arguments than ISNULL
NULL values	Replaces NULL with the specified value	Returns the first non-NULL expression in the list, or NULL if all expressions are NULL
Compatibility	SQL Server specific	Supported by multiple relational databases

69. How do you use the DATEADD and DATEDIFF functions in T-SQL?

The DATEADD function is used to add a specified amount of time to a date or datetime value.
The DATEDIFF function is used to calculate the difference between two dates or datetime values.
Specify the interval (year, month, day, hour, minute, etc.) and the amount of time to add or subtract.

70. What is the purpose of the GETDATE function in T-SQL?

The GETDATE function is used to return the current system date and time in SQL Server.
Use the function with parentheses to return the current date and time.

71. How do you use the FORMAT function in T-SQL?

The FORMAT function is used to format a value as a specific data type.
Specify the value to format and the format string.
Use the format string placeholders to define the desired output format.

72. What is the purpose of the STUFF function in T-SQL?

The STUFF function is used to replace a portion of a string with another string.
Specify the original string, the starting position, the length of the substring to replace, and the replacement string.

73. How do you use the PIVOT and UNPIVOT operators in T-SQL?

The PIVOT operator is used to transform rows into columns based on a pivot column.
Specify the pivot column, the columns to pivot, and the aggregate function to perform on the pivoted data.
The UNPIVOT operator is used to transform columns into rows based on an unpivot column.

74. What is the purpose of the CROSS APPLY and OUTER APPLY operators in T-SQL?

The APPLY operators are used to join a table-valued function with the outer query.
The CROSS APPLY operator returns only the matching rows between the function and the outer query.
The OUTER APPLY operator returns all rows from the outer query and matching rows from the function.

75. How do you use the MERGE statement in T-SQL?

The MERGE statement is used to perform insert, update, and delete operations on a target table based on a source table.
Specify the source and target tables and the join condition.
Define the actions to perform on the target table based on the matching and non-matching rows in the source and target tables.

76. What is the purpose of the CTE (Common Table Expression) in T-SQL?

A CTE is a named temporary result set that can be referenced within a SELECT, INSERT, UPDATE, or DELETE statement.
Use the WITH statement to define the CTE.
Use the CTE name within the outer query to reference the temporary result set.

77. How do you use the LAG and LEAD functions in T-SQL?

The LAG function is used to return the value from a previous row in the result set.
The LEAD function is used to return the value from a subsequent row in the result set.
Specify the column to evaluate and the offset to apply.

78. What is the difference between a scalar function and a table-valued function in T-SQL?

Feature	Scalar Function	Table-Valued Function
Definition	A function that returns a single value	A function that returns a table of values
Return Type	Returns a single value of any data type	Returns a table of data with defined columns
Arguments	Accepts input arguments	Can accept input parameters
Usage	Can be used in SELECT, WHERE, and SET clauses	Can be used in FROM and JOIN clauses
Return Value	Returns a single value	Returns a table of values
Output	Returns one row of output	Returns multiple rows of output
Examples	`SELECT dbo.GetProductName(1)`	`SELECT * FROM dbo.GetProductsByCategory(1)`
Syntax	`CREATE FUNCTION <function_name>() RETURNS <data_type> AS BEGIN... END`	`CREATE FUNCTION <function_name>(<input_param> <data_type>) RETURNS TABLE AS RETURN (<query>)`
Performance	Faster and more efficient than TVFs	Slower and less efficient than scalar functions
Use cases	Calculations that return a single value	Retrieving a set of records that meet a specific condition
Data transformation	Performs data transformation on a single value	Performs data transformation on a set of records
Example function type	ABS, LEFT, REPLACE, UPPER, LOWER	STRING_SPLIT, CROSS APPLY, OUTER APPLY, PIVOT, UNPIVOT

79. How do you use the STRING_SPLIT function in T-SQL?

The STRING_SPLIT function splits a string into a table of substrings, based on a specified separator.
To use the function, provide a string to be split and a separator value as parameters.
The function returns a table with two columns: value and ordinality.
The value column contains the individual substrings, and the ordinality column contains the order in which the substrings appear in the original string.

80. What is the purpose of the TRY_CONVERT function in T-SQL?

The TRY_CONVERT function tries to convert a value to a specified data type, and returns NULL if the conversion fails.
It is useful for handling data that may contain invalid values or data that could result in an error during conversion.
TRY_CONVERT returns a value of the specified data type, or NULL if the conversion fails.

81. What is the purpose of the STRING_AGG function in T-SQL?

The STRING_AGG function concatenates the values of a specified column, using a specified delimiter.
It is useful for aggregating data from multiple rows into a single string value.
To use the function, provide the column to aggregate and the delimiter to use as parameters.

82. How do you use the OPENROWSET function in T-SQL?

The OPENROWSET function provides a way to access data from an external data source, such as another SQL Server instance, an Excel spreadsheet, or a CSV file.
To use the function, specify the data source, provider, and connection string as parameters.
The function returns a result set that can be used in a SELECT statement or inserted into a table.

83. How do you use the BULK INSERT statement in T-SQL?

The BULK INSERT statement is used to import data from a data file into a SQL Server table.
To use the statement, specify the table to insert into and the file to import, along with any optional parameters.
The file must be located on the SQL Server machine or a network share that the server has access to.

84. What is the purpose of the FILESTREAM attribute in T-SQL?

The FILESTREAM attribute is used to store and manage large binary data (such as images, audio, or video) in SQL Server.
It allows the data to be stored on the file system, rather than in the database itself, improving performance and reducing database size.
FILESTREAM requires a filegroup to be created to store the data, and the column storing the data must be declared as varbinary(max) FILESTREAM.

85. How do you use the CONTAINS and FREETEXT functions in T-SQL?

The CONTAINS and FREETEXT functions are used to perform full-text searches in SQL Server.
CONTAINS is used to search for a specific word or phrase, while FREETEXT is used to search for words that are related to a specified term.
Both functions require the full-text search engine to be installed and configured in SQL Server.

86. What is the purpose of the HASHBYTES function in T-SQL?

The HASHBYTES function is used to generate a hash value for a given expression or column.
It is commonly used for data encryption, password storage, and data validation.
HASHBYTES supports several algorithms, including MD2, MD4, MD5, SHA, and SHA1.

87. How do you use the XML data type in T-SQL?

The XML data type is used to store XML data in SQL Server.
It allows XML data to be stored in a structured format, making it easier to query and analyze.
To use the XML data type, declare a column as xml when creating a table, or cast a string asan XML data type using the CAST or CONVERT function.

88. What is the purpose of the FOR XML clause in T-SQL?

The FOR XML clause is used to return query results as XML.
It can be used with a SELECT statement to format the results as XML elements and attributes.
The clause supports several modes, including RAW, AUTO, PATH, and EXPLICIT, each with its own syntax and output format.

89. How do you use the OPENXML function in T-SQL?

The OPENXML function is used to parse an XML document and insert the data into a table.

Syntax:

EXEC sp_xml_preparedocument @id OUTPUT, xml_document; SELECT * FROM OPENXML(@id, xpath_expression, namespace_declaration) WITH (column_name data_type);

Example: EXEC sp_xml_preparedocument @id OUTPUT, ‘<root><person id=”1″><name>John</name><age>30</age></person><person id=”2″><name>Jane</name><age>25</age></person></root>’; SELECT * FROM OPENXML(@id, ‘/root/person’, 2) WITH (id int ‘@id’, name varchar(50) ‘name’, age int ‘age’);

90. What is the purpose of the sp_executesql stored procedure in T-SQL?

The sp_executesql stored procedure is used to execute a dynamically constructed SQL statement or a stored procedure.

Syntax:

EXEC sp_executesql @sql_statement, @parameter_definition, @parameter1, @parameter2, …;

Example: DECLARE @sql_statement nvarchar(max), @table_name nvarchar(max); SET @table_name = ‘customers’; SET @sql_statement = N’SELECT * FROM ‘ + QUOTENAME(@table_name) + ‘ WHERE customer_id = @customer_id’; EXEC sp_executesql @sql_statement, N’@customer_id int’, 1;

91. How do you use the xp_cmdshell extended stored procedure in T-SQL?

The xp_cmdshell extended stored procedure is used to execute a command shell command.

Syntax:

EXEC xp_cmdshell ‘command’;

Example: EXEC xp_cmdshell ‘dir C:’;

92. What is the purpose of the xp_regread extended stored procedure in T-SQL?

The xp_regread extended stored procedure is used to read a value from the Windows registry.

Syntax:

EXEC master.dbo.xp_regread ‘HKEY_LOCAL_MACHINE’, ‘registry_path’, ‘value_name’;

Example: EXEC master.dbo.xp_regread ‘HKEY_LOCAL_MACHINE’, ‘SOFTWARE\Microsoft\Windows NT\CurrentVersion’, ‘ProductName’;

93. How do you use the fn_virtualfilestats function in T-SQL?

The fn_virtualfilestats function returns file statistics for a specified database.

Syntax:

SELECT * FROM fn_virtualfilestats(database_id);

Example: SELECT * FROM fn_virtualfilestats(DB_ID());

94. What is the purpose of the fn_dblog function in T-SQL?

The fn_dblog function is used to read the SQL Server transaction log.

Syntax:

SELECT * FROM fn_dblog(NULL, NULL);

Example: SELECT [Current LSN], [Transaction ID], [Transaction Name], [Operation], [Context] FROM fn_dblog(NULL, NULL) WHERE [Transaction Name] LIKE ‘transaction_name%’;

95. How do you use the fn_get_sql function in T-SQL?

The fn_get_sql function is used to return the SQL text for a specified query plan.

Syntax:

SELECT * FROM fn_get_sql(query_plan);

Example: SELECT * FROM sys.dm_exec_query_stats CROSS APPLY sys.dm_exec_sql_text(sql_handle) CROSS APPLY sys.dm_exec_query_plan(plan_handle) WHERE object_name = ‘table_name’;

96. What is the purpose of the fn_trace_gettable function in T-SQL?

The fn_trace_gettable function is used to read data from a trace file created by SQL Server Profiler.

Syntax:

SELECT * FROM fn_trace_gettable(trace_file, number_of_files);

Example: SELECT * FROM fn_trace_gettable(‘C:\trace.trc’, DEFAULT);

97. How do you use the fn_servershareddiagnostics function in T-SQL?

The fn_servershareddiagnostics function is used to get information about the shared memory objects that SQL Server is using.

Syntax:

SELECT * FROM fn_servershareddiagnostics();

Example: SELECT * FROM fn_servershareddiagnostics() WHERE type_name = ‘MEMORYCLERK_SQLQUERYEXECUTION’;

98. What is the purpose of the fn_helpcollations function in T-SQL?

The fn_helpcollations function is used to return a list of all collations supported by the SQL Server instance.

Syntax:

SELECT * FROM fn_helpcollations();

Example: SELECT * FROM fn_helpcollations() WHERE name LIKE ‘%_CS_AS’;

99. When to use COALESCE() & ISNULL() Functions?

The NULLability of the result expression varies between ISNULL and COALESCE functions. While the ISNULL return value is always considered as NOT NULLable (provided the return value is non-nullable), COALESCE doesn’t operate in the same way. As a result, the expressions ISNULL(NULL, 1) and COALESCE(NULL, 1), though equal, have different NULLability values. This distinction is essential when using these expressions in computed columns, creating key constraints, or making the return value of a scalar UDF deterministic for indexing purposes.

100. How do T-SQL window functions work?

To obtain a single value for each row from the underlying query, it is necessary to apply a window function to a set of rows defined by a window descriptor. The window descriptor serves to specify the group of rows to which the function should be applied. To provide the window specification, use the OVER clause.

The Top 100 T-SQL Interview Questions and Answers provide comprehensive coverage of technical and beginner-level questions to help job seekers succeed in T-SQL interviews. To access more informative content, follow us at freshersnow.com and broaden your knowledge.

T-SQL Technical Interview Questions

Top 100 T-SQL Interview Questions and Answers 2023

Jobs by Qualification