100 points
3

Want to solve programming problems and get paid for it? If that sounds interesting to you then contact us.

Top 17 T-SQL Interview Questions and Answers To Know in 2021

Q1. Explain what is T-SQL?

Answer: T-SQL stands for Transact-Structured Query Language, which is an extension of SQL functionality supported by Microsoft SQL Server and Sybase ASE.

Source : educba.com

2. What are the Types of Tables?

Answer: 

  • Partitioned Tables
  • Temporary Tables
  • System Tables
  • Wide Tables

Source : tsql.info

3. What is a Primary key?

Answer:  A Primary key is a column or combination of columns that contain values that uniquely identify each row in the table.

Source : tsql.info

4. What is a query ?

Answer:  A query is the result set of a SELECT statement. The select command is used to retrieve rows and columns from one or more tables.

Source : tsql.info

Q5. What is a Table?

Answer:  A Table is an object in the database that contain all the data in a database.

Source : tsql.info

Q6. 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.

Source : educba.com

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

Answer: The NULLability of result expression is different for ISNULL and COALESCE. The ISNULL return value is always considered NOT NULLable (assuming the return value is a non-nullable one) whereas COALESCE is not. So the expressions ISNULL(NULL, 1) and COALESCE(NULL, 1) although equivalent has different NULLability values. This makes a difference if you are using these expressions in computed columns and creating key constraints or making return value of a scalar UDF deterministic so that it can be indexed.

Source : educba.com

Q8. What Tools Do You Use For Performance Tuning?

Answer : SQL Server 2000 includes several tools you may find useful when performance tuning your SQL Server applications. The include:

  • Query Analyzer
  • Profiler
  • Index Wizard
  • Performance Monitor.

Source :wisdomjobs.com

Q9. How Can You Execute An Sql Query From Command Prompt?

Answer : By using OSQL & SQLCMD we can execute an sql query from command prompt.

Source :wisdomjobs.com

Q10. Difference Between Delete & Truncate Statement? Which Statement Can Be Rollbacked?

Answer :

  • With DELETE we can provide conditional WHERE clause to remove/delete specific rows, which is not possible with TRUNCATE.
  • TRUNCATE is faster than DELETE as Delete keeps log of each row it deletes in transaction logs, but truncate keeps log of only de-allocated pages in transaction logs.
  • Both statements can be rolled backed if provided in a transaction (BEGIN TRANS). If not then none of them can be rollbacked.
  • DELETE is DML just like INSERT, UPDATE, but TRANCATE is DDL, just like CREATE, ALTER, DROP.

Source :wisdomjobs.com

Q11. Mention what are dynamic queries in T-SQL?

Answer : Dynamic queries in T-SQL are those queries designed on the fly/ at run time using variables or using CTE or other sources.  We use EXECUTE function or SP_EXECUTESQL Stored Procedure to execute such queries.

Source :career.guru99.com

Q12. Mention what are ROLLUP and CUBE in T-SQL?

Answer :Rollup and cube are the grouping sets used along with GROUP BY clause to generate summarized aggregations. These are mainly used for Data Audits and Report Generation.

Source :career.guru99.com

Q13. Mention what are the maximum number of rows that can be constructed by inserting rows directly in VALUE list?

Answer : The maximum number of rows that can be constructed by inserting rows directly in VALUE list is 1000.

Source :career.guru99.com

Q14. Mention what is TOP in TSQL?

Answer : TOP limits the rows returned in a query result set to a specified number of rows or percentage of rows in SQL Server. When TOP is used in combination with the ORDERBY clause, the result set is limited to the first N number of ordered rows. Otherwise, it retrieves the first N number of rows in an undefined order.

Source :career.guru99.com

Q15.Could you explain the difference between Primary Key and Unique Index?

Answer : The differences between the two are:

  • Column(s) that make the Primary Key of a table cannot be NULL since by definition, the Primary Key cannot be NULL since it helps uniquely identify the record in the table. The column(s) that make up the unique index can be nullable. A note worth mentioning over here is that different RDBMS treat this differently –> while SQL Server and DB2 do not allow more than one NULL value in a unique index column, Oracle allows multiple NULL values. That is one of the things to look out for when designing/developing/porting applications across RDBMS.
  • There can be only one Primary Key defined on the table where as you can have many unique indexes defined on the table (if needed).
  • Also, in the case of SQL Server, if you go with the default options then a Primary Key is created as a clustered index while the unique index (constraint) is created as a non-clustered index. This is just the default behavior though and can be changed at creation time, if needed.

Source :stackoverflow.com

Q16. Name 5 commands that can be used to manipulate text in T-SQL code

Answer : 

  • CHARINDEX( findTextData, textData, [startingPosition] ) - Returns the starting position of the specified expression in a character string. The starting position is optional.
  • LEFT( character_expression , integer_expression ) - Returns the left part of a character string with the specified number of characters.
  • LEN( textData ) - Returns integer value of the length of the string, excluding trailing blanks.
  • LOWER ( character_expression ) - Returns a character expression after converting uppercase character data to lowercase.
  • LTRIM( textData) - Removes leading blanks. PATINDEX( findTextData, textData ) - Returns integer value of the starting position of text found in the string.
  • REPLACE( textData, findTextData, replaceWithTextData ) - Replaces occurrences of text found in the string with a new value.
  • REPLICATE( character_expression , integer_expression ) - Repeats a character expression for a specified number of times.
  • REVERSE( character_expression ) - Returns the reverse of a character expression.
  • RTRIM( textData) - Removes trailing blanks. SPACE( numberOfSpaces ) - Repeats space value specified number of times.
  • STUFF( textData, start , length , insertTextData ) - Deletes a specified length of characters and inserts another set of characters at a specified starting point.
  • SUBSTRING( textData, startPosition, length ) - Returns portion of the string.
  • UPPER( character_expression ) - Returns a character expression with lowercase character data converted to uppercase.

Source: mssqltips.com

Q17. Mention the difference between DELETE statement and TRUNCATE statement?

Answer: With the use of DELETE and TRUNCATE command, all data will be lost in a table. The difference between DELETE statement and TRUNCATE statement is that,
•DELETE is used for conditional removal of data records from Tables. These operations are logged.
•TRUNCATE is used for the unconditional removal of data records from Tables. Truncate operations are not logged.

Source : educba.com

100 points
3