Job interview questions and answers
We are excited to have you here, where you will find a comprehensive collection of T-SQL interview questions and expertly crafted answers. Whether you are a beginner or an experienced professional, this page will provide valuable insights and help you ace your T-SQL interviews. Good luck!
1. What is T-SQL, and how does it differ from SQL?
T-SQL (Transact-SQL) is a proprietary extension of SQL used by Microsoft SQL Server. It adds features such as stored procedures, functions, and control flow statements that regular SQL does not have.
2. What is the difference between CHAR and VARCHAR data types?
The CHAR data type is fixed-length and always reserves the same amount of space, regardless of the stored data. VARCHAR is variable-length and only uses the necessary amount of space for the actual data.
3. Explain the purpose of the GROUP BY clause in SQL.
The GROUP BY clause is used to group rows that share the same values in specified columns. It is typically used with aggregate functions like SUM or COUNT to perform calculations on grouped data.
4. What is the difference between INNER JOIN and OUTER JOIN?
INNER JOIN returns only the matching rows between two tables, while OUTER JOIN returns both matching and non-matching rows. There are three types of OUTER JOIN: LEFT OUTER JOIN, RIGHT OUTER JOIN, and FULL OUTER JOIN.
5. What are triggers in T-SQL, and how are they used?
Triggers are special stored procedures that are automatically executed in response to specific database events, such as insertions, updates, or deletions in a table. They are used to enforce business rules, maintain data integrity, or perform auditing tasks.
6. What is the purpose of the HAVING clause in SQL?
The HAVING clause is used in conjunction with the GROUP BY clause to filter the result set based on conditions involving aggregate functions. It allows you to specify conditions on calculated values in the SELECT statement.
7. Explain the difference between UNION and UNION ALL operators.
UNION combines the result sets of two SELECT statements, removing duplicate rows. UNION ALL, on the other hand, simply concatenates the result sets, allowing duplicate rows to be present.
8. What is normalization in databases, and why is it important?
Normalization is the process of organizing data in a database to reduce redundancy and improve data integrity. It helps in eliminating data anomalies and ensures efficient data storage and retrieval.
9. What are scalar functions in T-SQL?
Scalar functions are user-defined functions that return a single value based on the input arguments. They can be used in SELECT statements, WHERE clauses, or as part of computed columns.
10. What is the purpose of the CASE statement in SQL?
The CASE statement in SQL is used to perform conditional processing based on specified conditions. It allows you to perform different actions or calculations based on different criteria.
11. What is the purpose of the ROLLBACK statement in T-SQL?
The ROLLBACK statement is used to undo any changes made in a transaction and rolls back the database to its previous state. It is typically used in error handling to ensure data consistency.
12. Explain the difference between a primary key and a foreign key.
A primary key uniquely identifies a record in a table, while a foreign key establishes a relationship between two tables. Foreign keys refer to the primary key in another table, enforcing referential integrity.
13. What is the purpose of the LIKE operator in SQL?
The LIKE operator is used in WHERE clauses to search for a specified pattern in a column. It allows the use of wildcard characters, such as ‘%’ for any number of characters and ‘_’ for a single character.
14. What is a stored procedure?
A stored procedure is a named set of SQL statements that can be executed multiple times without recompiling the code. It helps improve performance, reusability, and security in database operations.
15. What is the primary purpose of the SELECT statement in SQL?
The SELECT statement is used to retrieve data from one or more tables in a database. It allows you to specify which columns to retrieve and apply conditions for filtering the result set.
16. What is the purpose of the INSERT statement in SQL?
The INSERT statement is used to add new rows to a table. It specifies the table name, column names, and values to be inserted.
17. What is a subquery in T-SQL?
A subquery is a query nested inside another query. It can be used within a SELECT, INSERT, UPDATE, or DELETE statement to return a set of values that are used in the outer query.
18. What is a view in SQL?
A view is a virtual table derived from one or more tables or other views. It represents a subset of data and can be treated as a regular table in queries.
19. What are the ACID properties in database transactions?
ACID stands for Atomicity, Consistency, Isolation, and Durability. These properties ensure that database transactions are executed reliably and consistently, even in the presence of failures.
20. What is the purpose of the UPDATE statement in SQL?
The UPDATE statement is used to modify existing data in a table. It specifies the table name, column names, and new values to be assigned to the columns.
1. What is the difference between a temporary table and a table variable?
Temporary tables are created in the tempdb database and can be accessed by multiple users. Table variables are created in memory and are only accessible within the scope of the current session.
2. What are the different types of indexes in SQL Server?
SQL Server supports clustered, non-clustered, and filtered indexes. Clustered indexes determine the physical order of data in a table, while non-clustered indexes provide a separate storage structure. Filtered indexes are used to index a subset of the rows in a table based on specific filter criteria.
3. How can you find duplicate records in a table?
You can find duplicate records using the GROUP BY clause along with the HAVING clause, like this:
“`
SELECT column1, column2, COUNT(*)
FROM table
GROUP BY column1, column2
HAVING COUNT(*) > 1
“`
4. What is a CTE (Common Table Expression)?
A CTE is a temporary result set that you can reference within a SELECT, INSERT, UPDATE, or DELETE statement. It helps in improving readability and simplifying complex queries.
5. How can you transpose rows to columns in SQL Server?
You can use the PIVOT operator to transpose rows into columns. The PIVOT operator rotates rows into columns based on the values in a specified column.
6. Explain the differences between UNION and UNION ALL.
UNION removes duplicate rows from the result set, while UNION ALL includes all rows. UNION performs a sort operation to remove duplicates, which can impact performance.
7. How can you concatenate multiple rows into a single string in SQL Server?
You can use the FOR XML PATH command to concatenate multiple rows into a single string. For example:
“`
SELECT STUFF((SELECT ‘, ‘ + column1
FROM table
FOR XML PATH(”)), 1, 2, ”) AS concatenated_string
“`
8. What is the purpose of the ROW_NUMBER() function?
The ROW_NUMBER() function is used to assign a unique sequential number to each row within a result set. It is often used for pagination, ranking, and grouping.
9. How can you find the second highest salary in a table?
You can use the TOP keyword with the ORDER BY clause and a subquery to find the second highest salary. For example:
“`
SELECT TOP 1 salary
FROM (SELECT DISTINCT TOP 2 salary
FROM table
ORDER BY salary DESC) AS subquery
ORDER BY salary ASC
“`
10. Explain the concept of data normalization.
Data normalization is the process of organizing data in a database to minimize redundancy and improve data integrity. It involves dividing large tables into smaller ones and defining relationships between them.
11. What is an execution plan in SQL Server?
An execution plan is a step-by-step guide that the SQL Server query optimizer uses to execute a query. It shows the sequence of operations, including the tables accessed, join methods, and indexes used.
12. How can you enable and disable triggers in SQL Server?
You can use the ALTER TABLE statement with the ENABLE TRIGGER and DISABLE TRIGGER clauses to enable or disable triggers on a table.
13. Explain the difference between CAST and CONVERT.
CAST is used for explicit type conversion, while CONVERT provides additional formatting options. CONVERT also supports converting dates and times using different formats.
14. What are the different isolation levels in SQL Server?
SQL Server supports four isolation levels:
– Read Uncommitted (dirty reads are allowed)
– Read Committed (prevents dirty reads)
– Repeatable Read (prevents dirty reads and non-repeatable reads)
– Serializable (achieves the highest level of isolation by locking entire tables)
15. What is the purpose of the MERGE statement in SQL?
The MERGE statement combines INSERT, UPDATE, and DELETE operations into a single statement. It performs an upsert operation, either updating existing records or inserting new records, based on a specified condition.
16. What are the new features introduced in SQL Server 2019?
Some of the new features in SQL Server 2019 include: Big Data Clusters, Intelligent Query Processing, Accelerated Database Recovery, Scalar UDF Inlining, and UTF-8 support.
17. How can you implement pagination in SQL Server?
You can use the OFFSET and FETCH clauses introduced in SQL Server 2012 to implement pagination. OFFSET specifies the number of rows to skip, and FETCH specifies the number of rows to retrieve.
18. What are correlated subqueries?
Correlated subqueries are subqueries that reference columns from the outer query. They are used to filter or modify the results based on values from the outer query.
19. How can you compare two tables and identify differences in SQL Server?
You can use the EXCEPT operator to compare the result sets of two SELECT statements, which returns the rows that exist in the first query but not in the second.
20. What is the purpose of the NOLOCK hint in SQL Server?
The NOLOCK hint allows a transaction to read uncommitted data, even if it is currently being modified by another transaction. It can help improve performance but can also lead to inconsistent results.
suresh