Updated July 6, 2023
Introduction to TSQL Interview Questions and Answers
If you are looking for a job related to TSQL, you need to prepare for the 2023 TSQL Interview Questions. Though every interview is different and the job scope is also different, we can help you with the top TSQL Interview Questions and answers, which will help you take the leap and get success in your TSQL Interview.
Below is the list of Important 2023 TSQL Interview Questions that are asked primarily on an interview:
Part #1 – TSQL Interview Questions and Answers (Basic)
Below are the basic interview questions and answers:
Q1. Explain what T-SQL is.
Answer:
T-SQL stands for Transact-Structured Query Language, an extension of SQL functionality supported by Microsoft SQL Server and Sybase ASE.
Q2. Explain what are the differences between SQL and T-SQL.
Answer:
T-SQL differs from SQL in that SQL is a query language designed to operate on sets, while T-SQL is a proprietary procedural language specific to MS SQL Server. Also, T-SQL has a different implementation of DELETE and UPDATE than SQL.
Q3. Please name at least five commands which can manipulate text in the T-SQL code. For example, replace a text string, obtain a portion of the text, etc.
Answer:
- LEFT(character_expression, integer_expression ) – It returns the left part of a character expression with the specified number of characters.
- CHARINDEX( findTextData, text data, [startingPosition] ) – It returns starting position of an expression in a character string, and starting position is optional.
- REPLACE( textData, findTextData, replaceWithTextData ) – It replaces a new value for occurrences of text found in the string.
- REVERSE( character_expression ) – It returns the reverse of a character expression.
- LEN( textData ) – It returns the length of the string, excluding trailing blanks.
- LOWER ( character_expression ) – After converting an uppercase character to lowercase, it will return a character expression.
- LTRIM( textData) – Leading blanks will be removed.
- PATINDEX( findTextData, textData ) – It returns the starting position integer value of text found in the string.
- REPLICATE(character_expression, integer_expression ) – It repeats a character expression a specified number of times.
- RTRIM( textData) – Removes trailing blanks. SPACE( number of spaces ) – It repeats the space value specified a number of times.
- STUFF( textData, start, length, insert text data ) – It deletes a specified length of characters and inserts another set of characters at a specified starting point.
- SUBSTRING( textData, startPosition, length ) – It returns a portion of the string.
- UPPER( character_expression ) – It returns a character expression with lowercase characters to uppercase.
Q4. Is importing data directly from T-SQL commands possible without using SQL Server Integration Services? If so, what are the commands?
Answer:
Yes – Six commands are available to import data directly into the T-SQL language. These commands include:
- BCP
- Bulk Insert
- OpenRowSet
- OPENDATASOURCE
- OPENQUERY
- Linked Servers
Q5. Mention what is ‘GO’ in T-SQL.
Answer:
‘GO’ is not a Transact-SQL statement but a batch separator. It is a command identified by the sqlcmd and osql utilities and SQL Server Management Studio Code editor. SQL Server utilities read “GO” as a signal that they should send the current batch of TSQL statements to an instance of SQL Server.
Q6. Mention the difference between the DELETE statement and the TRUNCATE statement.
Answer:
The DELETE and TRUNCATE commands will result in the loss of all data in a table. The difference between the DELETE statement and the TRUNCATE statement is that,
- The system logs these operations when we conditionally remove data records from tables using DELETE.
- TRUNCATE is used for the unconditional removal of data records from Tables. Truncate operations are not logged.
Part #2- TSQL Interview Questions and Answers (Advanced)
Below are the advanced interview questions and answers:
Q7. When to use COALESCE() & ISNULL() Functions.
Answer:
The NULLability of the result expression is different for ISNULL and COALESCE. The ISNULL return value is always considered NOT NULLable (assuming the return value is non-nullable), whereas COALESCE is not. So the expressions ISNULL(NULL, 1) and COALESCE(NULL, 1), although equivalent, have different NULLability values. This makes a difference if you are using these expressions in computed columns and creating key constraints or making the return value of a scalar UDF deterministic so that it can be indexed.
Q8. Mention what the sub-query is.
Answer:
You use a sub-query to return data that is then used in the main query as a condition to restrict the data further to be retrieved. A sub-query can be used with the statements like Update, select, delete, and insert with the operators like =, >, <, >=,<=, etc.
Q9. What are the types of XML indexes in SQL Server?
Answer:
Microsoft SQL Server supports different types of XML indexes. An XML index is different from a relational index. There are two types of XML Indexes: Primary XML Indexes and Secondary XML indexes. A clustered index on an internal table called the node table, known as the primary XML index, cannot be directly used by users from their T-SQL statements. To enhance search performance, we create secondary XML indexes. These make secondary links (RID) at the leaf level for existing clustered index-based KEY pages. It would be best if you created a primary XML index before creating the Secondary XML Indexes.
Q10. What is SQL Server?
Answer:
SQL Server is a vast, accessible, powerful Relational Database Management (Data Warehouse Management) application from Microsoft. It offers Database Development, Database Management, and Business Intelligence capabilities. This incredible technology is very easy to master and manage.
This offers the following advantages:
- Easy To Use
- Support for Small, Medium, and Large Database Storage
- Cheaper, compared to other RDBMS
- Data warehouse Support
- Enhanced Security Features
- Enterprise Feature Support
- In-Memory Processing
- Business Intelligence Support
- Highly Scalable and Powerful
- Low Cost of Ownership
Q11. Mention new error handling commands introduced with the SQL Server 2005 and beyond. What commands did they replace? How is the command used?
Answer:
The new commands introduced with SQL Server 2005 are TRY and CATCH. Though they do not directly replace any specific command, in many aspects, TRY, and CATCH can be used instead of RAISERROR. The TRY block covers business logic, whereas the CATCH logic is for capturing the error.
Q12. Mention what TOP is in TSQL.
Answer:
TOP limits the rows returned in a query result set to a specified number or percentage of rows in SQL Server. Combining TOP with the ORDER BY clause limits the result set to the first N number of ordered rows. Otherwise, it retrieves the first N number of rows in an undefined order.
Recommended Articles
This has been a guide to the List of TSQL Interview Questions and Answers so the candidate can easily crack down on these Questions. This is the most useful and valuable post on TSQL Interview Questions and answers. You may also look at the following articles to learn more –
