Monday, February 1, 2010

Interview Questions on Sql Server -Part 3


1. What are the Sql Joins?
Sql Joins are the way to get data from many tables based on some logical conditions. The different  types of Joins in Sql Server are

1) Inner join or Equi join
2) Self Join (it can be considered as the part of Inner join)
3) Outer Join
4) Cross join

 Read more about Sql Joins

2.What are the Inner joins?

Inner joins:- This type of join is also known as the Equi join. This join returns all the rows from both tables where there is a match. This type of join can be used in the situation where we need to select only those rows which have values common in the columns which are specified in the ON clause.

Read more about Inner Joins 

3. What are the Self Joins?

Self Joins:-Sometime we need to join a table to itself. This type of join is called Self join. In this Join, we need to open two copies of a same table in the memory. Since the table name is the same for both instances, we use the table aliases to make identical copies of the same table to be open in different memory locations.

Read more about Self Joins 

4. What are the Outer Joins?

Outer Joins:-This type of join is needed when we need to select all the rows from the table on the left (or right or both) regardless of whether the other table has common values or not and it usually enter null values for the data which is missing.

The Outer join can be of three types
  1. Left Outer Join
  2. Right Outer Join
  3. Full Outer Join  

Read more about Outer Joins 

5.What are the Cross Joins?

Cross Joins:-This join combines all the rows from the left table with every row from the right table. This type of join is needed when we need to select all the possible combinations of rows and columns from both the tables. This type of join is generally not preferred as it takes lot of time and gives a huge result that is not often useful.

Read more about Cross Joins

6. What are the Indexes in Sql server?

Indexes-Indexing  is way to sort and search records in the table. It will improve the speed of locating and retrieval of records from the table.It can be compared with the index which we use in the book to search a particular record.

7. What are the different types of Indexes in the Sql server?
In Sql Server there are two types of Index
1) Clustered Index
2) Non Clustered Index


8. What is the Clustered Index?
Clustered Index:- Clustered index physically stored the data of the table in the order of the keys values and the data is resorted every time whenever a new value is inserted or a value is updated in the column on which it is defined.


9. What is the  Non Clustered Index?
Non Clustered Index:- In case of Non clustered index it create a sperate list of key values (or created a table of pointers) which points towards the location of the datain the data pages.


10. What are the difference between Clustered and Non Clustered Indexes?
Clustered Index:- Clustered index physically stored the data of the table in the order of the keys values and the data is resorted every time whenever a new value is inserted or a value is updated in the column on which it is defined.

In a table only 1clustered index is possible.

In a clustered Index, the leaf node contains the actual data.


Non Clustered Index:- In case of Non clustered index it create a sperate list of key values (or created a table of pointers) which points towards the location of the datain the data pages.

In a table 249 non clustered index is possible.

In a non clustered Index, the leaf node contains the pointer to the data rows of the table.


11. What are the different ways of creating a table in Sql Server?
1)Creation of a table with the help of a create statement
2)Creation of table with the help of another table.  




Related Articles