Hello guys, if you are preparing for an SQL Server Interview or a Java developer position where SQL Server skills are required and looking for SQL server interview questions then you have come to the right place. In the past, I have shared 10 SQL query Interview questions and 50 database and SQL questions, and today, I am going to share some SQL Server conceptual and theory questions which are very popular in interviews. The list not only includes Microsoft SQL server related questions, like isnull vs coalesce but also many general SQL and database design concepts like referential integrity, joins, normalization, window functions, etc.
Wherever possible I have also provided answers and hints to guide you in the right direction. Most likely you already know the answers to these questions but I strongly recommend you to practice these frequently asked Microsoft SQL Server Interview questions before going for your interview.
These questions are good to brush up on your Database and SQL fundamentals which are always asked as part of interviews. This list contains not only good SQL Server interview questions but also some basic SQL questions which you will see everywhere. The list is useful for both beginners and intermediate developers and DBAs with 0 to 2 years of experience.
If you are a complete beginner to SQL Server or haven't touched the SQL Server for a long time then it's better to first brush up on essential SQL and MSSQL skills by joining a quick course like Microsoft SQL for Beginners before attempting this question. This will save a lot of your time and help in better preparation for your interviews.
50 Microsoft SQL Server Interview Questions with Answers
Without wasting any more of your time, here is a list of frequently asked Microsoft SQL Server Interview questions and answers from the telephonic round of interviews. You can use these questions to quickly reverse all essential SQL Server and database concepts.
1. What is the difference between union and union all? (answer)
While both UNION and UNION ALL are used to combine the result of two SQL queries with identical structures but UNION ALL keeps duplicate rows and UNION doesn't. You can further see the answer for a more detailed explanation and an example of how to use UNION and UNION ALL for reporting purposes.
2. 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
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 myProc3. Difference between Primary and Foreign keys in the table? (answer)
The primary key in one table is actually a foreign key on another table. Just like the Primary key is used to uniquely identify a record in a table, a foreign key is used to identify a corresponding record in another table.
For example, if you have a Product table where ProductId is the primary key and then you have an Order table where ProductId can be a foreign key to represent the product for which the order is. A foreign key is used to join and its link between two related tables.
4. What is the difference between row_number, rank, and dense_rank in SQL Server? (answer)
All three are window functions in SQL Server and can be used to provide order in a partition. What separates them is how they behave in case of a tie in the ranking, I mean duplicate records.
While row_number will always give unique ranking even to duplicates, both rank and desne_rank give the same ranking, but in dense_ranking the ranks are always in sequence like 1, 2, 3 but in the case of rank(), there will be gaps if two records have the same rank.
For example, if there are two records in second place then there is no third place. Very similar to the Olympic games where if two players win gold then there is no silver medal. You can further see 70-461, 761: Querying Microsoft SQL Server with Transact-SQL course on Udemy to learn about window function and other advanced SQL Server concepts.
This course is great for both people preparing for MSSQL certification as well as those who want to learn SQL Server in depth.
5. What is the difference between WHERE and HAVING clause in SQL? (answer)
While both WHERE and HAVING clause is used for filter data in SQL queries, there is the key difference between them. WHERE is used to filter data before groups are formed, especially if you are using the GROUP BY clause while HAVING filters data after groups are formed.
6. Difference between the Primary and Candidate key in the table? (answer)
All the keys which are eligible to become a primary key are called candidate keys.
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 equijoin, the condition of joining is based upon equality.
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 equijoin, the condition of joining is based upon equality.
9. What are different types of joins possible in SQL Server? (answer)
There are many types of joins possible in SQL Server. The two main types of joins are INNER JOIN and OUTER JOIN. While INNER join gives only matching records from both tables, OUTER JOIN allows you to include all records from one table either left or right, hence you can further have LEFT OUTER JOIN and RIGHT OUTER JOIN.
There are many types of joins possible in SQL Server. The two main types of joins are INNER JOIN and OUTER JOIN. While INNER join gives only matching records from both tables, OUTER JOIN allows you to include all records from one table either left or right, hence you can further have LEFT OUTER JOIN and RIGHT OUTER JOIN.
Along with that, there is another type of join called CROSS JOIN, which returns the cartesian product of both tables. This means if you have 8 rows in one table and 10 rows in another table then CROSS JOIN will return 80 rows. That's why you should be very careful while using CROSS JOIN on large tables with millions of records, as this can potentially blow up your database. etc.
If you want to learn more about JOINs and other essential database and SQL concepts then I highly recommend you take a look at The Complete SQL Bootcamp course on Udemy. One of the best courses to learn SQL fundamentals.
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 that is a natural primary key like SSN, EmployeeId, etc, then you can use an Identity column as the primary key. For
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 that is a natural primary key like 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, it's the primary key of the table.
16. How many non-clustered 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 indexes? (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 Introduction to SQL course on Pluralsight to learn more about different data types in SQL Server like decimal, numeric, DateTime, etc.
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)
Yes, It's possible to return a value from a stored procedure in SQL Server. In fact, there are two ways to do that. You can use either output parameters or RETURN keyword to return a value from a stored procedure in the Microsoft SQL Server database.
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 types in SQL Server? (answer)
34. What does schemabinding do in Microsoft SQL Server? (answer)
35. How do you test for nulls in SQL queries? (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.
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 types in SQL Server? (answer)
34. What does schemabinding do in Microsoft SQL Server? (answer)
35. How do you test for nulls in SQL queries? (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.
37. What is the difference between the cast, convert, and parse function in SQL Server? (answer)
While all of these function used to convert one data type to another in MSSQL, there is a subtle difference between them like CAST just take two parameters but both Convert and PARSE take additional parameters for more sophisticated conversion. Also, CAST is an ANSI SQL standard, so it's best practice to use CAST for database portability. Convert and PARSE are SQL Server functions.
38. What is the temp table? (answer)
hint - A temp table is something that starts with # and is removed when the user terminates its session. It only remains available unless the 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 the GETDATE, SYSDATETIME, and GETUTCDATE functions in SQL Server? (answer)
41. How to join three tables in one single SQL query? (solution)
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 relationships are based upon this concept.
37. What is the difference between the cast, convert, and parse function in SQL Server? (answer)
While all of these function used to convert one data type to another in MSSQL, there is a subtle difference between them like CAST just take two parameters but both Convert and PARSE take additional parameters for more sophisticated conversion. Also, CAST is an ANSI SQL standard, so it's best practice to use CAST for database portability. Convert and PARSE are SQL Server functions.
38. What is the temp table? (answer)
hint - A temp table is something that starts with # and is removed when the user terminates its session. It only remains available unless the 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 the GETDATE, SYSDATETIME, and GETUTCDATE functions in SQL Server? (answer)
41. How to join three tables in one single SQL query? (solution)
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 relationships are based upon this concept.
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)
This is another popular SQL interview question, for this, you need to first identify the duplicates, then copy them into the temp table and finally join the temp table with the original table and delete them. Btw, if you need more challenging SQL query questions, then you can also check out SQL Puzzles and Answers, the 2nd Edition book by Joe Kelko, a gold standard on SQL queries and puzzles.
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 get deleted from related tables. For example, if a User is deleted then all his Setting should also get deleted from the 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. Write a SQL query to find all table names in a database? (solution)
You can check the solution where I have shared the full query to find all table names in a database. Though the solution varies between different database for example, MySQL and Microsoft SQL server both provide different ways to get all table names.
49. How do you find the duplicate rows in a table on a database? (solution)
50. What is a SQL injection?
hint - A vulnerability that arises due to dynamically generating SQL query by concatenating String and using unsanitized user data.)
50. What is normalization? (answer)
Normalization is processed to reduce duplication from tables. They help to reduce storage space by removing duplicates but after a certain normal form, it also makes it difficult to query data. A table should at least be in 3rd normal form for all practical purposes.
Normalization is processed to reduce duplication from tables. They help to reduce storage space by removing duplicates but after a certain normal form, it also makes it difficult to query data. A table should at least be in 3rd normal form for all practical purposes.
If you want to learn more about database design, you can further see Database Design Fundamentals for Software Engineers course on Educative, an interactive learning platform where you can run SQL queries and code on a browser. It has an excellent collection of interactive courses for coding interviews available for just $14 per month.
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
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 rounds as well as on the first few technical rounds of the interview. It's useful for both beginners and experienced programmers and DBAs with a couple of years of experience.
Other related SQL queries, Interview questions, and articles:
- How to find the 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)
- 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)
- Top 5 Books to learn Advanced SQL and Database Design (books)
- Difference between View and Materialized View in Database? (answer)
- 10 Free SQL and Database Courses for Beginners (free courses)
Thanks for reading this article so far. If you like these common 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 Database and SQL and looking for some free resources 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
Feel free to comment, ask questions if you have any doubt.