50+ Microsoft SQL Server Phone Interview Questions and Answers

Are you preparing for an SQL Server Interview? or, a Java developer position where SQL Server skills are important? If yes, then here are a couple of frequently asked Microsoft SQL Server Interview questions you should practice before going for your interview. These questions are also good to brush-up your Database and SQL fundamentals which is always asked as part of interviews.These list contains not only godo SQL Server interview questions but also some basic SQL questiosn which you will see everywhere. The list is useful for both beginners and intermediate develoeprs and DBAs with 0 to 2 years of exeprience.

1. What is normalization? (answer)
Normalization is processed to reduce duplication from tables. They help to reduce storage space by removing duplicate but after a certain normal form, it also makes difficult to query data. A table should at least be in 3rd normal form for all practical purpose.

2. How to join three tables in one single SQL query? (solution)

3. Write a SQL query to find all table names in a database? (solution)

4. How do you find the duplicate rows in a table on a database? (solution)

5. What is the difference between WHERE and HAVING clause in SQL? (answer)

6. Difference between Primary and Candidate key in table? (answer)

7. What is the fastest way to empty a table? (answer)
You can use truncate to empty a table, it's faster than deleting all records because of no logging. You can also DROP and recreate the table, it is as good as truncating the table.

8. Difference between Self and Equi Join in SQL? (answer)
hint - in self join a table is jointed to itself and on the equi join the condition of joining is based upon equality. 

9. What are different types of joins possible in SQL Server?  (answer)
hint - INNER, LEFT OUTER, RIGHT OUTER and CROSS JOIN, etc. If you want to learn more about truncate and delete, I suggest you take a look at The Complete SQL Bootcamp course on Udemy. One of the best course to learn SQL fundamentals.

50+ Microsoft SQL Server Phone Interview Questions and Answers


10. What is an identity column in SQL Server? (answer)
An identity column is one where Id is incremented sequentially for each record. If your table doesn't have a column which is a natural primary key e.g. SSN, EmployeeId, etc, then you can use an Identity column as the primary key. For

11. How do you return an identity value from a table? (answer)

12. How do you return an identity value from a table with a trigger? (answer)

13. How many bytes can you fit in a row, do you know why? (answer)

14. What is a clustered index? (answer)
Hint - a clustered index defines the structured data is actually stored in the disk

15. How many clustered indexes per table is allowed? (answer)
Hint - Only one clustered index per table, its the primary key of the table.

16. How many nonclustered indexes you can create per table?  (answer)
hint - more than one, but more index does make SELECT query fast but INSERT, UPDATE will become slow)

17. What is the difference between clustered and non-clustered index?  (answer)
hint - clustered index represents the physical ordering of data while the non-clustered index represents logical ordering. There can only be one clustered index per table but you can have many non-clustered indexes in a table.

18. What is the difference between varchar and nvarchar data type in SQL Server? (answer)
hint - varchar is used for character data but nvarchar is used to store Unicode values.  You can further see Microsoft SQL for Beginners to learn more about different data types in SQL Server like decimal, numeric, DateTime, etc.

Top 50 Microsoft SQL Server Interview Questions and Answers



19. What is an execution plan?  (answer)
Hint - The steps/decision chosen by Query analyzer to execute your query)

20. Which one is fastest and slowest among index scan, index seek and table scan? (answer)
hint -  index seek is fastest and table scan is slowest

21. How do you return a value from a proc?  (answer)

22. How do you return a varchar value from a proc?  (answer)

23. If I have a column that will only have values between 1 and 250 what data type should I use?  (answer)

24. How do you enforce that only values between 1 and 10 are allowed in a column?  (answer)

25. How to check for a valid date in SQL Server?  (answer)

26. Which date format is the only safe one to use when passing dates as strings?  (answer)

27. How do you suppress rows affected messages when executing an insert statement?  (answer)

28. Can you name the 4 isolation levels in Microsoft SQL Server?   (answer)

29. How would you select all last names that start with S?   (answer)
hint - By using LIKE keyword like the last name like 'S%'

30. How would you select all rows where the date is yesterday's date? (answer)

31. What is horizontal partitioning in SQL Server database?  (answer)

32. How do you find the Nth highest salary in a table? (solution)

33. What is the difference between char and varchar data type in SQL Server? (answer)

34. What does schemabinding do in Microsoft SQL Server?  (answer)

35. How do you test for nulls in SQL query?  (answer)
Hint - by using IS NULL and IS NOT NULL clause. Never test using = or != operators.

36. What are some differences between isnull and coalesce in SQL Server?  (answer)
hint - The isnull() only allows 2 values, but coalesce() allows multiples. Also, COALESCE is ANSI standard while ISNULL is T-SQL specific. See here for a couple of more differences between is null and coalesce. You can further see 70-461, 761: Querying Microsoft SQL Server with Transact-SQL course on Udemy which is for preparing SQL server certification but also good for understanding SQL server concept like this in-depth.

SQL Server Phone Interview Questions and Answers



37. What is the difference between the cast, convert, and parse function in SQL Server? (answer)

38. What is the temp table?  (answer)
hint - A temp table is something which starts with # and removed when the user terminates its session. It only remains available unless user session is live, it's not visible to anyone outside the session.)

39. What is the difference between a local and global temporary table?  (answer)
hint - A local temp table is only visible to the current user but a global temp table is visible to everybody)

40. What is the difference between GETDATE, SYSDATETIME, and GETUTCDATE function in SQL Server? (answer)

41. If you create a local temp table and then call a proc is the temp table available inside the proc?  (answer)
Hint - Yes, it will be available.  here is the proof

--create proc
CREATE PROC myProc
AS

SET NOCOUNT ON

SELECT * FROM #temp
GO

--create our temp table
CREATE TABLE #temp (id int)

INSERT #temp VALUES(1)
INSERT #temp VALUES(2)
INSERT #temp VALUES(3)

--exec proc
EXEC myProc

DROP PROC myProc


42. What is referential integrity in a relational database?  (answer)
Hint - It's a constraint to ensure data integrity among the related table, primary key and foreign key relationship are based upon this concept. for more details see Introduction to SQL course on Pluralsight.

43. How to compare dates in SQL Server? which operator will you use? (answer)
hint - you can use between the operator and the less than or greater than the operator.

44. How to remove duplicate rows from a table in SQL? (answer)

45. What data type should you use to store monetary values?  (answer)
hint - NUMERIC with proper precision.

46. What is cascade delete in SQL Server?  (answer)
hint - This ensures that if a row is deleted from the primary table then all rows dependent upon that also gets deleted from related tables. For example, if a User is deleted then all his Setting should also get deleted from UserSettings table if both are related to each other e.g. via user_id

47. What is the difference between close and deallocate cursor in SQL? (answer)

48.  Difference between Primary and Foreign key in table? (answer)

49. What is the difference between row_number, rank, and dense_rank in SQL Server? (answer)

50. What is a SQL injection? 
hint - A vulnerability which arises due to dynamically generating SQL query by concatenating String and using unsanitized user data.)

51. What are the differences between PRIMARY KEY and UNIQUE constraints?  (answer)
Hint - Primary key cannot NULL, but Unique constraints allow one NULL, primary key creates clustered index but Unique constraints don't create clustered index)

52. How do you ensure that SQL Server will use an index?  (answer)
hint - you can use table hints in SQL query

53. What is the difference between union and union all?  (answer)
Hint - both are used to combine the result of two SQL query with identical structure but union all keeps duplicate rows and union doesn't see here for more details)


That's all about some of the frequently asked SQL Server Interview questions for programmers. I have tried to provide enough hints and answers but if you want to learn in-depth you can also join a comprehensive SQL Server course like The SQL server maser class. These questions are often asked on telephonic round as well as on first few technical rounds of interview. It's useful for both beginners and experienced programmer and DBAs with a couple of years of experience.

Further Learning
The Complete SQL Bootcamp
Introduction to SQL
Microsoft SQL for Beginners
SQL Puzzles and Answers, the 2nd Edition


Other related SQL queries, Interview questions, and articles:
  • How to find second highest salary in a table? (solution)
  • 5 Courses to learn Database and SQL Better (courses)
  • 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)
  • Top 5 Websites to learn SQL online for FREE? (resource)
  • What is the difference between UNION and UNION ALL in SQL? (answer)
  • Top 5 Courses to learn MySQL Database for Beginners (Courses)
  • What is the difference between View and Materialized View in Database? (answer)
  • Difference between clustered and non-clustered index in SQL? (answer)
  • 5 Free Courses to learn T-SQL and SQL Server for Beginners (Courses)
  • Difference between Unique and Primary key in table? (answer)

Thanks for reading this article so far. If you like these SQL Server Interview questions then please share 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 Database and SQL and looking for some free resource to start your journey then you can also take a look at this list of Free SQL Courses for Beginners to kick-start your learning.

No comments:

Post a Comment