Top 35 T-SQL and Microsoft SQL Server Interview Questions and Answers

Hello guys, if you are preparing for Java developer interview where Microsoft SQL Server is listed as mandatory and required skill or you preparing for a junior DBA role for Microsoft SQL Server and looking for common SQL Server interview questions for practice then you have come to the right place. Earlier, I have not only shared SQL Server courses for beginners but also 50+ SQL and Database Questions for programmers and DBA. You an also check them to get yourself familiar with generic SQL concepts like normalization, joins, aggregation, partitioning, indexing, and transaction along with these SQL Server specific questions which focuses mainly on T-SQL and SQL Server functionalities and how general SQL commands behave in Microsoft SQL Server. 

35 T-SQL and SQL Server Interview Questions with Answers for Beginners

So, what are we waiting for? Here is the list of popular and frequently asked SQL Server interview questions. Many of these questions have appeared in programming interviews and I have myself seen many questions like WHERE vs HAVING clause or ISNULL vs COALESCE on telephonic and face-to-face round of interview.  

Even if you have not worked in SQL Server recently, you can use these questions to revise key T-SQL and SQL server concepts before going for interviews. Regarding answers, I have given very brief and to-the-point answer because I like to see those kind of answer when I am looking for interview questions, if you need more information on any questions, you can always ask me in comments. 

1) What is the difference between SQL and T-SQL?

SQL is ANSI standard, while T-SQL is the dialect of and extension to SQL that Microsoft implements for its RDBBS- SQL SERVER. Oracle has a similar dialect, known as PL-SQL. See here for a detailed answer.

2) What is the difference between WHERE and HAVING clause in SQL?

The WHERE clause is evaluated before rows are grouped while HAVING clause is evaluated after rows are grouped. Which also means that WHERE clause is evaluated per row while HAVING clause is evaluated per group.

3) Can you access column alias defined in SELECT clause on WHERE predicate? Why?

No, you cannot because the WHERE clause is logically evaluated before SELECT clause, hence any column alias declared in SELECT clause is not available to WHERE clause.

4) Can you refer to a column alias defined by the SELECT clause in the same SELECT?

No, because all expressions that appear in same logical query processing phase are evaluated at the same point in time. 

5) Can you declare an identifier whose name is same as T-SQL keyword? How?

Yes, you can. Those are called irregular identifier and needs to be declared inside bracket e.g. []

6) Why FLOAT is not the right data type to represent a PRICE?

FLOAT is not the correct data type to represent product price because it's an approximate data type and cannot represent all values precisely.

7) Which data type should you used to represent a monetary price?

NUMERIC, because it provide precision

8) What is the difference between NEWID and NEWSEQUENTIALID in SQL SERVER?

Order is main difference. The NEWID function generates GUID values in random order, whereas the NEWSEQUENTIALID function generates GUIDs that increase in sequential order.

9) What is difference between GETDATE() and SYSDATETIME() functions?

The return type is different. Even though both returns the currant date, GETDATE returns the current date and time values as DATETIME data type, while SYSDATETIME returns DATTIME2 values.

10) What is difference between plus(+) operator and CONCAT function in SQL SERVER or MSQL?

The both + operator and CONCAT function is used to concatenate character String. The + operator by default yields a NULL result on NULL input, whereas the CONCAT function treats NULLs as empty String. See here for more detailed answer.

11) What is the performance benefit of using WHERE clause?

WHERE clause helps to reduce the network traffic by filtering data in database server. It can also use indexes to avoid full scans of tables reducing disk IO. 

12) How do you guarantee the order of the rows in the result of a SQL query?

By using ORDER BY clause, there is no other way.

13) How do you guarantee deterministic results with TOP in SQL SERVER or MSSQL?

TOP cannot handle ties by itself. To guarantee the deterministic result you can either use WITH TIES option or by you can define unique ordering to break ties.

14) What are the benefits of using OFFSET-FETCH over TOP?

Main benefit is that OFFSET-FETCH is standard and TOP isn't, its T-SQL specific feature. Also OFFSET-FETCH supports a skipping capability that TOP doesn't have.

15) What are old and new syntax of cross join in SQL SERVER?

The new syntax has the CROSS JOIN keywords between table names and the old syntax has a comma.

16) What are different types of OUTER JOINs in SQL?

Three types, LEFT, RIGHT and FULL OUTER JOIN

17) What is difference between correlated and non-correlated (self-contained) SQL queries?

Self-contained subqueries are independent of outer query but correlated subqueries have a reference to an element from the table in the outer query. In case of former, subquery only runs once but in case of correlated, subquery runs for each row returned by outer query. See here for more differences.

18) Which set operators are supported by T-SQL?

T-SQL and MSSQL supports UNION, INTERSECTION and EXCEPT set operators. It also support UNION ALL multiset operator.

19) What the two requirements for the queries involved in a set operator e.g. UNION?

The number of columns in the two queries must be same and corresponding columns needs to have compatible types.

20) What is difference between APPLY and JOIN operators in Microsoft SQL Server?

With a JOIN operator, both input represent static relation. With APPLY, the left side is a static relation, but the right side can be a table expression with correlation to elements from the left table.

21) What makes a SQL query grouped query?

When you use an aggregate functions like SUM(), AVG(), MAX() or MIN, a GROUP BY clause or both.

22) What the clause that you can use to define multiple grouping sets in the same query in SQL SERVER?

Three clauses, GROUPING SETS, CUBE and ROLLUP

23) What is difference between PIVOT and UNPIVOT in SQL SERVER?

PIVOT rotates data from a state of rows to a state of columns. UNPIVOT rotates the data from columns to rows. 

24) What type of language constructs are PIVOT and UNPIVOT implemented as?

both are implemented as table operators

25) What are the clauses that are supported by different types of window functions?

Window function in SQL SERVER supports partitioning, ordering, and framing clause.

26) Which is a more specific predicate, CONTAINS or FREETEXT in SQL SERVER?

CONTAINS predicate is used for more specific searches.

27) How can you get an XSD schema together with an XML document from your SELECT statement?

You can do that by using XMLSCHEMA directive in the FOR XML clause.

28) how to retrieve scaler values from an XML instance in SQL SERVER?

You can get scaler values from an XML instance by using values() method of XML data type in SQL SERVER.

29) Can a table column name contain spaces, apostrophes, and other non-regular characters?

Yes, table and column names can be delimited identifiers containing non-standard characters. You need to use them inside bracket e.g. [Course Details]

30) What type of table compression are available in SQL SERVER?

two types, page or row compression. Page compression includes row compression.

31) How does SQL SERVER enforce uniqueness in both primary key and unique constraints?

SQL SERVER uses unique indexes to enforce uniqueness for both primary key and unique constraints.

32) Can two table of same database have primary key with same name?

No, all table constraints must have unique name in a database.

33) How many SELECT statements a view consists in SQL SERVER?

Technical, a view can contain only one SELECT statement, but you can use UNION clause to join multiple SELECT statement as one. 

34) What are different types of views available in T-SQL?

T-SQL supports regular view, which are just stored SELECT statements and index views, or materialized views, which actually materialize the data. It also support partitioned views.

35) What is inline table-valued functions in SQL SERVER?

That's all about T-SQL and Microsoft SQL Server Interview Questions and Answers. You can use them as you want, for example, you can use them to prepare for Java developer interview where SQL Server skills are required or you can use it to revise key SQL Server concepts or just learn T-SQL and SQL Server basics.  I have myself learned a lot by looking at the interview questions, they often ignite the spark you need to research which trigger learning.

Other related SQL queries, Interview questions, and articles:

• How to find the second highest salary in a table? (solution)
• 12 Database Index Questions from interviews (index questions)
• 5 Courses to learn Database and SQL Better (courses)
• Top 5 Websites to learn SQL online for FREE? (resource)
• Write a SQL query to copy or backup a table in MySQL (solution)
• 5 Courses to learn Oracle and Microsoft SQL Server database (courses)
• How to migrate SQL queries from Oracle to SQL Server 2008? (answer)
• What is the difference between UNION and UNION ALL in SQL? (answer)
• Top 5 Books to learn Advanced SQL and Database Design (books)
• Difference between clustered and non-clustered indexes in SQL? (answer)
• 5 Free Courses to learn T-SQL and SQL Server for Beginners (Courses)
• Difference between the Unique and Primary keys in the table? (answer)
• 5 Best PostgreSQL Courses for Beginners (online courses)
• Difference between View and Materialized View in Database? (answer)
• Top 5 Courses to learn MySQL Database for Beginners (Courses)
• 10 Free SQL and Database Courses for Beginners (free courses)

Thanks for reading this article so far. If you like these TSQL and SQL Server Interview questions answers then please share them with your friends and colleagues. If you have any questions or feedback then please drop a note.

P.S. - If you are interested in learning Microsoft SQL Server in depth and looking for best resources to start your journey then you can also checkout these Microsoft SQL Server online courses to learn T-SQL and SQL Server in depth.