Top 24 T-SQL Interview Questions & Answers

1) Mention what is T-SQL?

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

2) Mention what is the difference between SQL and T-SQL?

The difference between SQL and TSQL is that SQL is a query language to operate on sets, while TSQL is a proprietary procedural language used by MS SQL Server.  Also, T-SQL has a different implementation of DELETE and UPDATE than SQL.

3) Mention how tsql statements can be written and submitted to the Database engine?

Tsql statements can be written and submitted to the Database engine in following ways,

  • By using the SQLcmd Utility
  • By using the SQL Server Management Studio
  • By connecting from an application that you create

4) Mention what is “GO” in T-SQL?

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

5) Mention what is the difference between TRUNCATE and DELETE statement?

The difference between TRUNCATE and DELETE statement is that,

  • TRUNCATE is used for unconditional removal of data records from Tables. Truncate Operations are not logged.
  • DELETE is used for conditional removal of data records from Tables. These operations are logged.

Tsql-icon

6) Mention how does a local variable is defined using T-SQL?

A local variable is defined using TSQL by using statement “DECLARE” and the name of the local variable should begin with “@” sign as the first character of its name. For example, integer CNT we will define local variable as,

DECLARE @CNT INT

7) Mention what does the T-SQL command IDENT_CURRENT does?


The TSQL command IDENT_CURRENT returns the last identity value produced for a specified table or view.  The last identity value created can be for any session and any scope.

8) Mention what does the T-SQL command IDENT_INCR does?

TSQL command IDENT_INCR returns the increment value mentioned during the formation of an identity column in a table or view that has an identity column.

9) Mention if it is possible to import data directly from T-SQL commands without using SQL Server Integration Services? If yes, what are the commands?

Yes, it is possible to import data directly from T-SQL commands without using SQL Server Integration Services.  These commands include,

  • BCP
  • OpenRowSet
  • Bulk Insert
  • OPENQUERY
  • OPENDATASOURCE
  • Linked Servers

10) Mention what is sub-query?

A sub-query is used to return data that will be used in the main query as a condition to further restrict the data to be retrieved.

A sub-query can be used with the statements like Update, select, delete and insert with the operators like =, >, <, >=,<=, etc.

11) Mention what are dynamic queries in T-SQL?

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.

12) Mention what are ROLLUP and CUBE in T-SQL?

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.


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

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

14) Mention what is TOP in TSQL?

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.

15) Mention what are the Join Types in TSQL?

Join Types in TSQL are,

  • 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

16) Mention what are the T String functions available in TSQL?

T String functions available in TSQL are,

  • Left
  • Right
  • Ltrim
  • Rtrim
  • Substring
  • Replace
  • Stuff

17) Mention what is the syntax used for partition in TSQL?

In TSQL, the syntax used for partition is,

[ database_name. ] $PARTITION.partition_function_name(expression)

18) Mention what is the syntax for using SQL_Variant_Property?

The syntax for using SQL_Variant_Property is,

SQL_Variant_Property (expression, property)

19) Mention what is OFFSET-FETCH filter in tsql?

In tsql OFFSET-FETCH filter is designed similar to TOP but with an extra element.  It helps to define how many rows you want to skip before specifying how many rows you want to filter.

20) Mention what is uncommittable transactions?

When an error occurs in a transaction within a TRY block, and if the error is not serious it enters into a status open and Uncommittable. In uncommittable state, the transactions cannot perform any action that would generate a write to the transaction log.

21) Mention what is Sp_pkeys?

Sp_pkeys is part of  Catalog Stored Procedures and returns primary key information for a single table in the current database.

Sytax for Sp_pkeys is sp_pkeys [ @table_name = ‘Tbale name.’ ]

22) Mention how to do backup entire database?

To backup entire database, use following syntax;

BACKUP DATABASE { database_name }

TO backup_device [ ,…n ]

[ MIRROR TO clause ]

[ WITH { DIFFERENTIAL | [ ,…n ] } ];

23) Mention what are the limitations of IDENTITY column?

The limitations of the IDENTITY column is that column values cannot be updated once generated. Also, it may require to specify this column as a PRIMARY KEY, as such, there is a possibility of duplication of values within a table.  Identity property is applicable for integer based column only.

24) Mention what is the use of SET statement in TSQL?

In TSQL, SET statement allows you to change the current session handling of specific information like: system language, dateformat, lock timeout, rowcount and so on.


FacebookTwitter
0 replies

Leave a Comment

Leave a Reply

Your email address will not be published. Required fields are marked *