Code360 powered by Coding Ninjas X Naukri.com. Code360 powered by Coding Ninjas X Naukri.com
Table of contents
1.
Introduction
2.
Most Asked T SQL Interview Questions
2.1.
1. What are the differences between T SQL and SQL?
2.2.
2. What is a DML statement?
2.3.
3. What is a DDL statement?
2.4.
4. List at least five commands used to manipulate text in the T-SQL code. 
2.5.
5. What is a subquery in T-SQL?
2.6.
6. Write a Query To Copy Only Structure?
2.7.
7. What is a join?
2.8.
8. What are the types of functions in T-SQL?
2.9.
        → System Functions9. What are the types of joins in T-SQL?
2.10.
10. What is the difference between UNION  ALL and UNION?
2.11.
11. What is the difference between Varchar and Char Data Type in T SQL?
2.12.
12. What is a view?
2.13.
13. What are the types of views in T SQL?
2.14.
14. What is a trigger?
2.15.
15. What are the Types of DML Triggers?
2.16.
16. What are the types of cursors?
2.17.
17. Why should you avoid using a cursor?
2.18.
18. How T SQL statements can be written and submitted to the Database engine?
2.19.
19. What is “GO” in T-SQL?
2.20.
20. Mention how a local variable is defined using T SQL?
2.21.
21. Is it possible to import data directly from T-SQL commands without using the SQL Server Integration Services? What are the commands?
2.22.
22. What are CUBE and ROLLUP in T-SQL?
2.23.
23. What are the different T String functions available in T SQL?
2.24.
24. What are the limitations of the IDENTITY column?
2.25.
25. What are the three ways that Dynamic SQL can be issued?
2.26.
26. What are the commands to remove all of the data from a table?
2.27.
27. Difference Between Sql Server 2005 and Sql Server 2000 Features Or New Features Of 2000 Vs 2005?
2.28.
28. What are the tools you Use For Performance Tuning?
2.29.
29. How can you execute an SQL query from the command prompt?
2.30.
30. What Are The Various Ssis Logging Mechanisms?
3.
Conclusion
Last Updated: May 27, 2024

T-SQL Interview Questions

Author Manish Kumar
1 upvote

Introduction

Are you planning to interview for the Data Engineer position at top MNCs? This blog will help you tackle those questions. Big software corporations have begun to ask some T SQL interview questions as a part of their hiring process. Therefore it becomes critical to have in-depth knowledge of T SQL interview questions. 

The most vital and likely T SQL interview questions will be covered in this blog. Happy reading, Ninja!

T SQL interview questions

Most Asked T SQL Interview Questions

1. What are the differences between T SQL and SQL?

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

2. What is a DML statement?

Ans: Data manipulation language  (DML) allows you to change, retrieve and add information to the database. 

DML statements include the following:

  • SELECT
  • UPDATE
  • INSERT
  • DELETE
  • BULK INSERT
  • MERGE
  • TRUNCATE TABLE

3. What is a DDL statement?

Ans: Data Definition Language (DDL) allows you to define objects in a database. DDL statements include the following commands:

  • ALTER
  • CREATE
  • RENAME
  • DROP
  • ENABLE TRIGGER
  • DISABLE TRIGGER
  • UPDATE STATISTICS
  • COLLATIONS

4. List at least five commands used to manipulate text in the T-SQL code. 

Ans: 

  • CHARINDEX( findText, text, [startPosition] ) – It returns the start position of an expression in a character string.
  • LEFT(character, integer) – It returns the left part of a character with the provided number of characters.
  • REVERSE( character ) – It returns the reverse of a character.
  • REPLACE( text, findText, replaceWithText ) – It replaces a new value for occurrences of findText found in the string.
  • LEN( text ) – It returns the length of the string, excluding the trailing blanks.
  • LTRIM( text ) – Any leading blanks will be excluded.
  • LOWER ( character ) – It returns the lowercase character of a given character after converting an uppercase character to lowercase.

5. What is a subquery in T-SQL?

Ans: It is a query nested inside a SELECT, UPDATE, INSERT or DELETE statement or further nested inside another subquery.

6. Write a Query To Copy Only Structure?

Ans: select * into secondTable from firstTable where 3 = 2. This query will copy only the structure, not the data.

7. What is a join?

Ans: Join is a SQL keyword used to get tuples from two or more tables based on the relationship between the columns of the tables.

8. What are the types of functions in T-SQL?

Ans: →Table-Valued Functions

        → Scalar Function 

        → System Functions
9. What are the types of joins in T-SQL?

Ans: → Outer join

         → Inner join

         → Right outer join

         → Full outer join

         → Left outer join

         → Cross join

10. What is the difference between UNION  ALL and UNION?

Ans: UNION ALL will return all values without eliminating duplicate rows.

         UNION will eliminate duplicate rows and return only distinct values.

11. What is the difference between Varchar and Char Data Type in T SQL?

Ans: The Char type works with the entire declared length even if fewer characters are stored in the variable. 

The Varchar type only uses the length loaded with characters and does not use the entire declared length.

12. What is a view?

Ans: Views in T SQL are kind of virtual tables based on the SELECT statement of one or more tables.

13. What are the types of views in T SQL?

Ans: Different types of views:

  • Partitioned Views
  • Indexed Views
  • System Views

14. What is a trigger?

Ans: A trigger is a special type of database object of stored procedure that is fired when a specific type of data modification is done against a specific column or table.

15. What are the Types of DML Triggers?

Ans: INSTEAD OF trigger

          AFTER trigger

16. What are the types of cursors?

Ans:  There are mainly four types of cursors:

Dynamic cursor, static cursor, forward-only cursor and keyset-driven cursor.

17. Why should you avoid using a cursor?

Ans:  Cursor reduces the scalability of the application and also their performance.

They also use a lot of SQL server resources. Instead of cursor, we might use sub-queries, temp tables, correlated, the CASE statement or other alternatives.

18. How T SQL statements can be written and submitted to the Database engine?

Ans:  We can write and submit T SQL statement to the Database engine in the following ways,

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

19. What is “GO” in T-SQL?

Ans: It is not a T SQL statement but a batch separator. This command is identified by the osql utility and SQL Server Management Studio Code editor and sqlcmd.  SQL Server identifies “GO'' as a signal that they should send the current batch of T SQL statements to an instance of SQL Server.

20. Mention how a local variable is defined using T SQL?

Ans: A local variable is defined in T SQL by using the statement “DECLARE”. Name of the local variable should begin with the “@” sign before the first character of its name.

For example, integer COUNTVAR we will define local variable as,

DECLARE @COUNTVAR INT

21. Is it possible to import data directly from T-SQL commands without using the SQL Server Integration Services? What are the commands?

Ans: Yes, it is possible to import data from T-SQL commands without using the SQL Server Integration Services.  These commands are,

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

22. What are CUBE and ROLLUP in T-SQL?

Ans: Cube and rollup are the grouping sets used along with the GROUP BY clause to generate summarised aggregations. These are generally used for Data Audits and Report Generation.

23. What are the different T String functions available in T SQL?

Ans: T String functions that are available in T SQL are,

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

24. What are the limitations of the IDENTITY column?

Ans: IDENTITY column values cannot be updated once generated. As there is a possibility of duplication of values within a table, It may require to specify this column as a PRIMARY KEY. The identity property is applicable for integer based columns only.

25. What are the three ways that Dynamic SQL can be issued?

Ans: The three ways are:

  • Using EXEC.
  • Writing a query with parameters.
  • Using sp_executesql.

26. What are the commands to remove all of the data from a table?

Ans: TRUNCATE command removes all rows from a table. This operation cannot be rolled back and no triggers will be fired. TRUNCATE is faster than DELETE and doesn't use as much undo space as DELETE.

We can use the DELETE command to remove rows from a table. A WHERE clause can be used to remove only desired rows. If there is no WHERE condition specified, all rows will be removed. After performing a DELETE operation, we need to either COMMIT or ROLLBACK the transaction to make the change permanent or to undo it.

27. Difference Between Sql Server 2005 and Sql Server 2000 Features Or New Features Of 2000 Vs 2005?

Ans: 

  • Exception handling (TRY-CATCH block)
  • Ranking functions (ROW_NUMBER, RANK, DENSE_RANK, NTILE)
  • PIVOT, UNPOVIT
  • CTE (Common Table Expressions)
  • SYNONYMS
  • CUBE, ROLLUP & GROUPING SET

28. What are the tools you Use For Performance Tuning?

Ans: SQL Server 2000 includes several tools we may find useful while performance tuning our SQL Server applications. This include:

  • Profiler
  • Query Analyzer
  • Performance Monitor
  • Index Wizard

29. How can you execute an SQL query from the command prompt?

Ans: By using SQLCMD and OSQL, we can execute an sql query from the command prompt.

30. What Are The Various Ssis Logging Mechanisms?

Ans: 

  • SQL Server Profiler
  • Text file
  • Windows Event Log
  • XML File
  • SQL Server
Get the tech career you deserve, faster!
Connect with our expert counsellors to understand how to hack your way to success
User rating 4.7/5
1:1 doubt support
95% placement record
Akash Pal
Senior Software Engineer
326% Hike After Job Bootcamp
Himanshu Gusain
Programmer Analyst
32 LPA After Job Bootcamp
After Job
Bootcamp

Conclusion

In this blog, you learned about the top T SQL Interview questions. All the important topics related to T SQL were covered in detail.

Also, do refer to other Interview Questions related articles:

After reading these T SQL Interview questions, are you not feeling confident to crack any interview? We hope you had a great time reading our T SQL Interview questions blog. 

Please refer to our guided paths on Coding Ninjas Code360 to learn more about DSA, Competitive Programming, JavaScript, System Design, etc. And also, enrol in our courses and refer to the mock test and problems available. Have a look at the interview experiences and interview bundle for placement preparations.

Please upvote our blogs if you find them helpful and informative!

Happy learning!

Previous article
Brain Teaser Interview Questions
Next article
Soft Skills Interview Questions
Live masterclass