If you want to work in the TSQL field, you should prepare for the 2021 TSQL Interview Questions. Though each interview is unique, as is the scope of each job, we can provide you with the finest TSQL Interview Questions and Answers that will enable you to take the jump and succeed in your TSQL Interview.
Q1. What exactly is T-SQL?
Ans. T-SQL stands for Transact Structured Query Language. It is a SQL feature extension that is supported by Microsoft SQL Server and Sybase ASE.
Q2. What is the difference between SQL and T-SQL?
Ans. The difference between SQL and TSQL is that SQL is a query language for working with sets, whereas TSQL is a Microsoft SQL Server-specific procedural language. Also, DELETE and UPDATE are implemented differently in T-SQL than they are in SQL.
Q3. How to write Tsql statements and send them to the database engine?
Ans. The following methods can be used to write and submit Tsql statements to the database engine
- Using the SQL command-line utility.
- With the help of SQL Server Management Studio.
- By establishing a connection with a custom application.
Q4. In T-SQL, what does “GO” mean?
Ans. “GO” is a batch separator, not a Transact-SQL command. The sqlcmd and osql utilities, as well as the SQL Server Management Studio Code editor, identify it as a command. The SQL Server utilities interpret “GO” as a command to send the current batch of TSQL statements to a SQL Server instance.
Q5. What is the relationship between the TRUNCATE and DELETE statements?
Ans. The difference between a TRUNCATE and a DELETE statement is that
- TRUNCATE statement is used to delete data from a database.
- TRUNCATE is used to remove data records from Tables in an unconditional way. It is not possible to log Truncate Operations.
- The DELETE command is used to remove data records from Tables on a conditional basis. These actions are kept track of in a database.
Q6. How T-SQL is used to define a local variable?
Ans. A local variable is created with TSQL’s “DECLARE” statement, and the name of the variable should start with the “@” sign as the first character. For instance, for the integer CNT, we will define a local variable as follows:
DECLARE @CNT INT
Q7. What is the purpose of the T-SQL command IDENT_CURRENT?
Ans. IDENT CURRENT is a TSQL command that returns the most recent identity value for a table or view. Any session and scope can use the last identity value produced.
Q8. What is the purpose of the T-SQL command IDENT INCR?
Ans. In a table or view with an identity column, the TSQL command IDENT INCR returns the increment value mentioned during the construction of the identity column.
Q9. Mention whether data may be imported directly via T-SQL commands without the need of SQL Server Integration Services. If that’s the case, what are the commands?
Ans. Yes, without using SQL Server Integration Services, data can be imported directly using T-SQL commands. These are some of the commands:
- BCP
- OpenRowSet
- Bulk Insert
- OPENQUERY
- OPENDATASOURCE
- Linked Servers
Q10. What exactly is a sub-query?
Ans. A sub-query is used to return data that will be utilised as a condition in the main query to further limit the data that may be retrieved.
With operators like =, >, <, >=,<=, etc a sub-query can be utilised with statements like Update, select, delete, and insert.
Q11. Explain what T-SQL dynamic queries are.
Ans. In T-SQL, dynamic queries are ones that are created on the fly/at run time using variables, CTE, or other sources. To run such queries, we use the EXECUTE function or the SP EXECUTESQL Stored Procedure.
Q12. Explain the terms ROLLUP and CUBE in T-SQL.
Ans. The grouping sets rollup and cube are used in conjunction with the GROUP BY clause to construct summarised aggregations. Data audits and report generation are the most common uses for them.
Q13. Mention the maximum number of rows that can be created by directly entering rows into the VALUE list.
Ans. By directly entering rows into the VALUE list, the maximum number of rows that can be created is 1000.
Q14. What does TOP mean in TSQL?
Ans. In SQL Server, TOP restricts the amount of rows returned in a query result set to a specific number or percentage. The result set is limited to the first N number of ordered rows when TOP is used in conjunction with the ORDERBY clause. Otherwise, it fetches the first N rows in an arbitrary sequence.
Q15. What are the different types of joins in TSQL?
Ans. The following are the several types of joins in TSQL:
- Inner join
- Outer join
- Left outer join
- Right outer join
- Left outer join with Exclusions
- Right outer join with Exclusions
- Full outer join
- Full outer joins with Exclusions
- Cross join
Q16. Mention the T String functions that are accessible in TSQL.
Ans. The following T String methods are available in TSQL:
- Left
- Right
- Ltrim
- Rtrim
- Substring
- Replace
- Stuff
Q17. Mention the partitioning syntax in TSQL.
Ans. The syntax for partitioning in TSQL is,
[ database_name. ] $PARTITION.partition_function_name(expression)
Q18. What is the SQL_Variant Property syntax?
Ans. The SQL Variant Property syntax is as follows:
SQL_Variant_Property (expression, property)
Q19. In tsql, what is the OFFSET-FETCH filter?
Ans. The OFFSET-FETCH filter in tsql is similar to TOP but has an additional element. Before defining how many rows you want to filter, it’s a good idea to describe how many rows you want to skip.
Q20. What exactly are uncommittable transactions?
Ans. When an error occurs in a transaction within a TRY block, it becomes open and uncommittable if the error is not serious. Transactions in an uncommittable state are unable to perform any activities that might result in a write to the transaction log.
Q21. What is the meaning of Sp_pkeys?
Ans. Sp_pkeys returns primary key information for a single table in the current database and is part of Catalog Stored Procedures.
Sp pkeys has the following syntax.
sp_pkeys [ @table_name = ‘Table name.’ ]
Q22. Mention a method for backing up a complete database.
Ans. Use the following syntax to backup the complete database:
BACKUP DATABASE { database_name }
TO backup_device [ ,…n ]
[ MIRROR TO clause ]
[ WITH { DIFFERENTIAL | [ ,…n ] } ];
Q23. What are the constraints of the IDENTITY column?
Ans. The IDENTITY column has the drawback of being unable to alter column values once they have been generated. Also, specifying this column as a PRIMARY KEY may be required, resulting in the risk of value duplication within a table. Only integer-based columns are eligible for the Identity feature.
Q24. What is the purpose of the SET statement in TSQL?
Ans. The SET statement in TSQL allows you to change how certain information is handled in the current session, such as system language, dateformat, lock timeout, rowcount, and so on.
Q25. What exactly is ‘GO’ and T-SQL?
Ans. ‘GO’ is a batch separator, not a Transact-SQL command. The sqlcmd and osql utilities, as well as the SQL Server Management Studio Code editor, identify it as a command. The SQL Server utilities interpret “GO” as a command to send the current batch of TSQL statements to a SQL Server instance.