Here are some T-SQL interview questions and answers:
Q: What is T-SQL?
A: T-SQL (Transact-SQL) is a programming language used to manage and manipulate data in Microsoft SQL Server.
Q: What is a primary key?
A: A primary key is a column or set of columns in a table that uniquely identifies each row in the table. It enforces data integrity and helps ensure that there are no duplicate records in the table.
Q: What is a foreign key?
A: A foreign key is a column or set of columns in a table that refers to the primary key of another table. It is used to enforce referential integrity between the two tables.
Q: What is normalization?
A: Normalization is the process of organizing data in a database in a way that reduces redundancy and dependency. It involves breaking up large tables into smaller, more specialized tables and creating relationships between them.
Q: What is a stored procedure?
A: A stored procedure is a precompiled block of T-SQL code that is stored in a database and can be executed by calling it from an application or other T-SQL code. It can be used to simplify complex queries, improve performance, and enforce security.
Q: What is a view?
A: A view is a virtual table that is based on the result of a SELECT statement. It does not store data but provides a way to access and manipulate data from one or more tables in a simplified manner.
Q: What is a trigger?
A: A trigger is a special type of stored procedure that is executed automatically in response to a specific event, such as a data change or a database operation. It can be used to enforce business rules, log events, or update related data.
Q: What is a cursor?
A: A cursor is a database object that allows you to process data row by row in T-SQL. It is often used when you need to perform complex data processing or when you need to update or delete data in a controlled manner.
Q: What is a transaction?
A: A transaction is a sequence of one or more T-SQL statements that are executed as a single unit of work. It allows you to group related operations into a single transaction and ensure that they are either all completed or all rolled back in case of an error.
Q: What is a deadlock?
A: A deadlock is a situation where two or more transactions are blocked and waiting for each other to release resources, causing them to be stuck indefinitely. It can occur when transactions are accessing the same resources in a different order or when there is a circular dependency between them.
Q: How do you optimize a query in SQL Server?
A: There are several ways to optimize a query in SQL Server, including using indexes, minimizing the number of joins, avoiding subqueries, using appropriate data types, and using SET NOCOUNT ON to reduce network traffic. Additionally, you can use the Query Optimizer to generate execution plans and identify performance issues.
Q: What is the difference between a clustered and non-clustered index?
A: A clustered index determines the physical order of data in a table and is stored with the table data itself. A table can only have one clustered index. A non-clustered index is a separate data structure that contains a copy of selected columns from the table and a reference to the actual table data. A table can have multiple non-clustered indexes.
Q: What is SQL injection?
A: SQL injection is a type of security attack where an attacker injects malicious code into a database query in order to gain unauthorized access to sensitive data or perform unauthorized actions on the database. It can be prevented by using parameterized queries or stored procedures, validating user input, and sanitizing input data.
Q: What is a subquery? A:
A subquery is a query that is nested inside another query and is used to retrieve data that will be used as a condition or value in the main query.
Q: What is the difference between DELETE and TRUNCATE in SQL Server?
A: DELETE is a DML (Data Manipulation Language) statement that removes one or more rows from a table, while TRUNCATE is a DDL (Data Definition Language) statement that removes all rows from a table and resets the identity value. TRUNCATE is faster than DELETE but cannot be rolled back.
Q: What is the purpose of the GROUP BY clause in SQL Server?
A: The GROUP BY clause is used to group rows that have the same values in one or more columns and perform aggregate functions on each group, such as COUNT, SUM, AVG, MAX, or MIN.
Q: What is the purpose of the HAVING clause in SQL Server?
A: The HAVING clause is used to filter groups that meet a specific condition based on the result of an aggregate function. It is similar to the WHERE clause but is used with GROUP BY and aggregate functions.
Q: What is a CTE (Common Table Expression) in SQL Server?
A: A CTE is a named temporary result set that can be used within a SELECT, INSERT, UPDATE, or DELETE statement. It is similar to a derived table or a subquery but can be referenced multiple times in the same query.
Q: What is the purpose of the RANK function in SQL Server?
A: The RANK function is used to assign a rank or a row number to each row in a result set based on a specific order and criteria. It is often used to retrieve the top N rows or to perform pagination.
Q: What is the purpose of the OVER clause in SQL Server?
A: The OVER clause is used to define a window or a subset of rows within a result set that can be used for aggregate functions, ranking functions, or analytic functions.
Q: What is the purpose of the TRY...CATCH block in SQL Server?
A: The TRY...CATCH block is used to handle errors that occur during the execution of a T-SQL statement or a stored procedure. It allows you to catch and handle errors gracefully, log them, and take appropriate actions, such as rolling back a transaction or notifying a user.
Q: What is the purpose of the EXISTS operator in SQL Server?
A: The EXISTS operator is used to check whether a subquery returns any rows or not. It returns a Boolean value of True or False, which can be used as a condition in a WHERE or a JOIN clause.
Q: What is the purpose of the COALESCE function in SQL Server? A: The COALESCE function is used to return the first non-null expression in a list of expressions. It can be used to replace null values with default values or to handle missing data.
Q: What is the purpose of the PIVOT and UNPIVOT operators in SQL Server?
A: The PIVOT operator is used to transform rows into columns based on a specified column, while the UNPIVOT operator is used to transform columns into rows. They are useful for generating reports or displaying data in a different format.
Q: What is the difference between a stored procedure and a function in SQL Server?
A: A stored procedure is a reusable block of T-SQL code that performs a specific task, such as inserting or updating data, while a function is a reusable block of T-SQL code that returns a scalar value or a table-valued result set. Functions can be used in queries, while stored procedures cannot.
Q: What is the purpose of the APPLY operator in SQL Server?
A: The APPLY operator is used to apply a table-valued function to each row of a table or a result set, and return the combined result. It is similar to a join but can also be used to perform calculations or filter data.
Q: What is the purpose of the OFFSET FETCH clause in SQL Server?
A: The OFFSET FETCH clause is used to implement pagination or limit the number of rows returned by a query. It allows you to specify how many rows to skip and how many rows to return, based on a specified order.
Q: What is the purpose of the OUTER APPLY operator in SQL Server?
A: The OUTER APPLY operator is used to apply a table-valued function to each row of a table or a result set, and return all the rows from the table even if there is no match in the function. It is similar to a left outer join but can also be used to perform calculations or filter data.
Q: What is the purpose of the CROSS APPLY operator in SQL Server?
A: The CROSS APPLY operator is used to apply a table-valued function to each row of a table or a result set, and return only the rows that have a match in the function. It is similar to an inner join but can also be used to perform calculations or filter data.
Q: What is the purpose of the MERGE statement in SQL Server?
A: The MERGE statement is used to perform an insert, update, or delete operation on a target table based on the data from a source table or a view. It allows you to synchronize two tables or handle data changes efficiently.
Q: What is the purpose of the TRY_CONVERT function in SQL Server?
A: The TRY_CONVERT function is used to convert a value of one data type to another data type, and return null if the conversion fails. It allows you to handle data conversion errors gracefully and avoid runtime errors.