SERIES - 3 SQL Server Indexes Interview Questions and Answers

In this article, I am going to discuss the most frequently asked SQL Server Indexes Interview Questions and Answers. Please read our previous article where we discussed the most frequently asked SQL Server Temporary Tables Interview Questions and Answers. As part of this article, we are going to discuss the following SQL Server Indexes Interview Questions with answers.

1. How is data stored in a database in SQL Server?
2. How will be the database engine retrieves the information from a table in SQL Server?
3. What is the use of an Index in SQL Server?
4. What is an Index in SQL Server?
5. Why do we need Indexes in SQL Server?
6. What are the types of Indexes available in SQL Server?
7. What is a Clustered Index in SQL Server?
8. What is a Non-Clustered Index in SQL Server?
9. What is the difference between clustered and non-clustered index?
10. What is a Unique Index?
11. What are the differences between UNIQUE Constraints and the UNIQUE Index?
12. When should we be creating a unique constraint over a unique index?
13. When SQL Server uses Indexes?
14. When should we create indexes on a table?
15. What is a Covering Query in SQL Server?
16. What is a table scan? Or what is the impact of table scans on performance?
17. What is the purpose of the query optimizer in SQL Server?
18. What is the first thing you will check for if the query below is performing very slowly?
19. What is the significance of an Index on the column used in the GROUP BY clause?
20. What is the role of an Index in maintaining a unique column in a table?
21. What are the disadvantages of an Index in SQL Server?
22. How many Clustered and Non-Clustered Indexes can you have per table?
23. Which Index is faster, Clustered or Non-Clustered Index?
24. When is it usually better to create a unique non-clustered index on the primary key column?
25. What is a Composite Index in SQL Server? or What is the advantage of using a Composite Index in SQL Server? or What is Covering Query?
26. What are the different index configurations a table can have?

Q.1 How is data stored in a database?

1. SQL Server stores data in it under data pages where a data page is a memory location for storing the information.
2. A data page will be having a size of 8KB and every 8 data pages we stored under a logical container known as “extend”.

Q.2 How will be the database engine retrieves the information from a table?

Whenever the database engine wants to retrieve the information from the table it will adopt two different mechanisms for searching the data

1. Full page scan
2. Index Scan

In the first case, SQL Server will search for the required information in each and every data page to collect the information. So, if the tables having more number of rows it will take lots of time for searching all the data so it is a time-consuming process.

In the second case SQL Server without searching into each and every data page for retrieving the information it will make use of an index for retrieving the information, where an index is a pointer to the information what we retrieve which can reduce the disk I/O operation saving the time, but if we want to use index scan for searching the data first the index has to be created.

Note: Whenever an index is created on a column or columns of a table internally an index table gets created maintaining the information of a column on which the index is created as well as the address (pointer to the row corresponding to a column).

Q.3 What is the use of an Index in SQL Server?

Series - 2 SQL Server Temporary Tables Interview Questions and Answers

SQL Server Temporary Tables Interview Questions and Answers

Q.1 What are Temporary tables?

Q.2 What are the 2 types of Temporary Tables in SQL Server?

Q.3 What is a Local Temporary Table?

Q.4 What are Global Temporary Tables in SQL Server?

Q.5 What are the differences between Local and Global Temporary Tables?

Q.6 Can you please explain when to use a temp table in SQL Server?

Q.7 Can you create foreign key constraints on temporary tables?

Q.8 Do you have to manually delete temporary tables?

Q.9 In which database, the temporary tables get created?

Q.10 How can I check for the existence of a temporary table?

Q.11 SQL appends random function as at the end of the Local table’s name. So, we can create a Local table with the same name. But the Global table’s name has to be unique. Why is that?

Q.12 How to copy the structure of the temporary table from one table to another? i.e., copying the only structure and no data?

Q.13 What is the difference between a Temporary Table and a Table variable?

Series - 1 Series of SQL Server Interview Questions and Answers

Series - 1: Basics Of SQL Server

Q.1 What is RDBMS?

Q.2 Explain DML, DDL, DCL, and TCL statements with examples?

Q.3 What is the difference between Drop, Delete and Truncate statements in SQL Server?

Q.4 What is Cascading referential integrity constraint?

Q.5 What is the difference between where clause and having a clause in SQL Server?

Q.6 What are the differences between primary key and unique key in SQL Server?

Q.7 How can we copy the data from one table to another using the SQL query .?

Q.8 How to create a new table from an existing table or in how many ways we can create a new table from an existing table?

SQL Interview Questions and Answers

1) What is SQL?

2) When SQL appeared?

3) What are the usages of SQL?

4) Does SQL support programming?

5) What are the subsets of SQL?

6) What is a Data Definition Language?

7) What is a Data Manipulation Language?

8) What is Data Control Language?

9) What are tables and fields in the database?

10) What is a primary key?

11) What is a foreign key?

12) What is a unique key?

13) What is the difference between primary key and unique key?

14) What is a Database?

15) What is DBMS?

16) What are the different types of database management systems?

17) What is RDBMS?

18) What is Normalization in a Database?

19) What is the primary use of Normalization?

20) What are the disadvantages of not performing Database Normalization?

21) What is an inconsistent dependency?

22) What is Denormalization in a Database?

23) What are the types of operators available in SQL?

24) What is a view in SQL?

25) What is an Index in SQL?

26) Which are the different types of indexes in SQL?

27) What is the unique Index?

28) What is the Clustered Index in SQL?

29) What is the Non-Clustered Index in SQL?
30) What is the difference between SQL, MySQL and SQL Server?

31) What is the difference between SQL and PL/SQL?

32) Is it possible to sort a column using a column alias?

33) What is the difference between clustered and non-clustered index in SQL?

34) What is the SQL query to display the current date?

35) Which are the most commonly used SQL joins?

36) What are the different types of joins in SQL?

37) What is Inner Join in SQL?

38) What is Right Join in SQL?

39) What is Left Join in SQL?

40) What is Full Join in SQL?

41) What is a "TRIGGER" in SQL?

42) What is self-join and what is the requirement of self-join?

43) What are the set operators in SQL?

44) What is the difference between BETWEEN and IN condition operators?

45) What is a constraint? Tell me about its various levels.

46) Write an SQL query to find names of employee start with 'A'?

47) Write an SQL query to get the third maximum salary of an employee from a table named employee_table.

48) What is the difference between DELETE and TRUNCATE statement in SQL?


49) What is ACID property in a database?

50) What is the difference between NULL value, zero and blank space?

51) What is the usage of SQL functions?

52) What do you understand by case manipulation functions?

53) Which are the different case manipulation functions in SQL?

54) Explain character-manipulation functions?

55) Which are the different character-manipulation functions in SQL?

56) What is the usage of NVL() function?

57) Which function is used to return remainder in a division operator in SQL?

58) What are the syntax and use of the COALESCE function?

59) What is the usage of the DISTINCT keyword?

TOP 100 Interview Questions and Answers for .NET Developer

Q.1 What is SOLID Principle 

Ans. In the Object-Oriented Programming (OOPS), SOLID is an acronym, introduced by Micheal Feather, for five Design principles used to make software design more understandable, flexible, and maintainable. These principles are a subset of many other principles promoted by Robert C. Martin.

There are Five SOLID Principles
1.Single Responsibility Principle (SRP).
2.Open-Closed Principle (OCP).
3.Liskov Substitution Principle (LSP).
4.Interface Segregation Principle (ISP).
5.Dependency Inversion Principle (DIP).

Q.2 What is boxing and unboxing 

Ans. Boxing and Unboxing in C# Programming

Boxing is a mechanism converting value type to object type 

int j = 123;

// The following line boxes i.

object o = j;

Unboxing is an explicit conversion from the type object to a value type or from an interface
type to a value type

int j = 123;      // a value type

object o = j;     // boxing

int k = (int)o;   // unboxing

Q.3 What is the difference between asp.net c# and mvc.?

ASP.NET framework is a part of .net platform for building, deploying and running
web applications. Now, we can develop a web application by using Asp.Net Web Form
and Asp.Net MVC. In this article, I am going to expose the main difference between
ASP.NET WebForms and ASP.NET MVC.

Difference between ASP.NET MVC and WebForms

Asp.Net Web Forms	Asp.Net MVC
Asp.Net Web Form follow a traditional event-driven development model.	Asp.Net MVC is a lightweight and follows MVC (Model, View, Controller) pattern based development, model.
Asp.Net Web Form has server controls.	Asp.Net MVC has HTML helpers.
Asp.Net Web Form supports view state for state management at the client-side.	Asp.Net MVC does not support view state.
Asp.Net Web Form has file-based URLs means file name exists in the URLs must have its physical existence.	Asp.Net MVC has route-based URLs means URLs are divided into controllers and actions and moreover it is based on controller not on physical file.
Asp.Net Web Form follows Web Forms Syntax	Asp.Net MVC follow customizable syntax (Razor as default)
In Asp.Net Web Form, Web Forms (ASPX) i.e. views are tightly coupled to Code behind(ASPX.CS) i.e. logic.	In Asp.Net MVC, Views and logic are kept separately.
Asp.Net Web Form has Master Pages for a consistent look and feels.	Asp.Net MVC has Layouts for a consistent look and feels.
Asp.Net Web Form has User Controls for code re-usability.	Asp.Net MVC has Partial Views for code re-usability.
Asp.Net Web Form has built-in data controls and best for rapid development with powerful data access.	Asp.Net MVC is lightweight, provide full control over markup and support many features that allow fast & agile development. Hence it is best for developing an interactive web application with the latest web standards.
Asp.Net Web Form is not Open Source.	Asp.Net Web MVC is an Open Source.