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!
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
24+ registered 
