.NET and SQL Server Interview Question - How does index makes search faster?

Answer:

For better understanding, let us consider a simple search example which shows differences between a table, declared with index and without index.
Let's first see an example for a table which is created without declaring an index and look how exactly the SQL search engine will perform action. Below diagram will give u better idea…

In the above example, SQL search engine will search from initial till it finds the respective record and once the record is found it will basically display the record. Further, When we create an index on any column of a table then the large data get divided like following B-Tree diagram, so that search becomes easier and faster.


B-tree structure of a SQL Server index

For Example-

Suppose we have to search value 25 in an indexed column, the query engine will first look in the “Root Node” to determine which node to refer in the “Branch Nodes”. In the above example first “Branch Node” has Value 1 to 20 and the second “Branch Node” has Value 21 to 40, so the query engine will go to the second “Branch Node” and will skip the first “Branch Node” as we have to search Value 25. Same like “Branch Nodes” the query engine will operate the “Leaf Node” to retrieve respected result.

Regards,

Please click here to see more .NET and SQL Server interview questions

.NET and SQL Server Interview Question - What is Cross Join and in which scenario do we use Cross Join?

Answer:

CROSS JOIN: Cross join is used to return all records where each row from first table is combined with each row in second table.
Cross join is also called as Cartesian Product join.

The cross join does not apply any predicate to filter records from the joined table. Programmers can further filter the results of a cross join by using a WHERE clause.
For Example:- We have following two tables.
Look at the "Product" table:



Note that the "P_Id" column is the primary key in the "Product" table.
Next, we have the "SubProduct" table:



Note that the "Sub_Id" column is the primary key in the "SubProduct" table.
There are lots of scenarios where we use the cross join(permutation and combination), below are the example of hotel where customer's gets the detail of combined product and its total cost, So that it is easy to select their respective choice.
Query:- select
Product.ProductName,SubProduct.SubProductName,(Product.Cost+SubProduct.Amount)as
TotalCost from Product cross join SubProduct
The output look like below:



Regards,
Please click here to see more .NET and SQL Server interview questions

SQL Server Interview Question - What is Normalization and its different forms?

Answer:
Normalization is the process of organizing the table's data in proper manner.

In other words Normalization is the process of breaking up data into a logical non-repetitive format that can be easily reassembled as a whole.
Normalization have 3 different forms namely 1Normal Form,2Normal Form,3Normal Form.
1Normal Form:

A table is said to be in 1NF if it satisfies the following rules.

• The table must not contain any redundant groups of data

• data must be broken up into the smallest units possible. In addition to breaking data up into the smallest meaningful values.

2Normal Form:
A table is said to be in 2NF if it satisfies the following rules.

• The table must be in 1NF

• All the non-key column must depend on primary key.

3Normal Form:

A table is said to be in 3NF if it satisfies the following rules.

• The table must be in 2NF

• A non-key field should not depend on another Non-key field.

• The data should not be derived further.
For ex:
Below table is in denormalize format:

Applying Normalization on this table.

1NF:After applying 1NF the table look like



The customer Name is divided into two units like Customer FirstName and Customer LastName.

Region field is also divided into two units like Country and State.
2NF:After applying 2NF the table look like

CountryTable

ProductTable

For avoiding duplication Create a new master table of Country and Product.
3NF:After applying 3NF the table look like

A non key field Total is removed from the table.
Regards,
Please click here to see more SQL Server interview questions

SQL Server Interview Question - What are the differences between INNER JOIN, LEFT JOIN and RIGHT JOIN in SQL Server?

Answer:

Let us Assume we have following two tables:-
Create a new table as"Customers":



Note that the "Cust_Id" column is the primary key in the "Customers" table.
This means that no two rows can have the same Cust_Id.

The Cust_Id distinguishes two persons even if they have the same name.

Next, we have the "Orders" table:


Note that the "Order_Id" column is the primary key in the "Orders" table and that the "Cust_Id" column refers to the persons in the "Customers" table without using their names.
Notice that the relationship between the two tables above is the "Cust_Id" column.
LEFT JOIN: Return all rows from the left table, even if there are no matches in the right table.
For Example:-
The Following is the example for LEFT JOIN:
Considering the above two tables:
Query:- Select * from Customers left join Orders on Customers.Cust_Id = Orders.Cust_Id
The output will look like following:


RIGHT JOIN: Return all rows from the right table, even if there are no matches in the left table.
For Example:-
The Following is the example for RIGHT JOIN:
Considering the above two tables:
Query:- Select * from Customers right join Orders on Customers.Cust_Id = Orders.Cust_Id
The output will look like following:


INNER JOIN: The INNER JOIN keyword return rows when there is at least one match in both tables.
For Example:-

The Following is the example for RIGHT JOIN:

Considering the above two tables:
Query:- Select * from Customers inner join Orders on Customers.Cust_Id = Orders.Cust_Id
The output will look like following:

Regards,
Please click here to see more SQL Server interview questions

.NET interview question - What is Shadowing?

Answer:

Shadowing is a concept of altering the behaviour of the base class member. It basically replaces complete element of the parent class like method becomes a variable.
For example: In the below example in class1 'i' is declared as variable whereas in class2 'i' is declared as a method.

class Program
  {
      static void Main(string[] args)
      {
          class1 obj = new class1();
          class2 obj1 = new class2();
          obj1.i();                 //here i is treated as method
          Console.WriteLine(obj.i); //here i is treated as variable
          Console.ReadLine();
      }
  }
  class class1
  {
      public int i=2;
  }
  class class2 : class1
  {
      public void i()
      {
          Console.WriteLine("Hello World");
      }
  }

Regards,

Please click here to see more .NET interview questions

.NET interview questions: - What is public, private, protected, internal and internal protected?

Answer:
In the following manner we can describe the C# access modifiers: -

Public - The members (Functions & Variables) declared as public can be accessed from anywhere.

Private - Private members cannot be accessed from outside the class. This is the default access specifier for a member, i.e if you do not specify an access specifier for a member (variable or function), it will be considered as private. Therefore, string PhoneNumber; is equivalent to private string PhoneNumber;

Protected - Protected members can be accessed only from the child classes.

Internal - it can be accessed only within the same assembly.

Internal Protected - internal protected can be accessed within the same assembly
as well as in derived class.

Regards,

Please click here to see .NET and C# interview questions and answers

.Net and C# Interview Questions:-What are the various questions asked in C# and .NET interview

Answer:




The following are the 4 basics section for the interview purpose and below is the diagram to understand in better manner.

• .NET basics


• New Technologies


• Architecture


• Process

.NET basics include the questions on the following sections.

• Basics of .NET OOPS


• SQL Server


• ADO.NET


• ASP.NET


• Web Services

New Technologies includes the questions on the following sections.

• WCF


• WPF/Silverlight


• LINQ/Entity framework


• Azure


• WWF

Architecture includes the questions on the following sections.

• Design Patterns


• MVC MVP MVVM


• UML


• Documentation


• Requirement to design

Process includes the questions on the following sections.

• Agile Estimation


• FPA


• CMMI


• Six Sigma

.NET basics
Following questions are related to fundamentals of .NET.

• What is CLR, CTS, CTS, IL Code?


• What is Value type, Reference type and Stack, Heap Memory?


• What is Garbage Collector and working of Garbage Collector?


• What are GAC, Delegate, Event, and JIT?

Following questions are related to object oriented programming system (OOPS).

• Why we need OOPS concept?


• What are the different Principals of OOPS?


• How to implement Abstraction, Encapsulation, Inheritance and
  Polymorphism?


• What are Abstract Class and Interface and differences? What are the
  Classes and Object?

Following questionsis related toSQL Server.

• What are SQL Queries, Sub Queries?


• What are Joins and its different types?


• What are Normalization, Denormalization and Performance Tuning?


• What are Indexes, Clustered Indexes?

Following questions is related to ADO.NET.

• What are Dataset, Data Reader and Data Adapter?


• What are Connection Object and Connection String?

Following questions is related to ASP.NET.

• What are the Session Variable, Application and Cache Object?


• What isServer.Transfer and Response.Redirect?


• Difference between Server.Transfer and Response.Redirect?


• What is Authentication and Authorization?

Following questions is related to Web Services.

• What is WSDL?


• What are ASMX, SOAP?

New Technologies
Following questions is related to WCF.

• What are Operation Contract, Service Contract and Data Contract?


• What is Data Member?


• What are Address Binding and Contract Binding?


• What do you mean End points?

Following questions is related toWPF/Silverlight.

• What are XAML, XAP?


• What is Silverlight Architecture?


• What is MVVM pattern and RIA Services.

Following questions is related toLINQ/Entity framework.

• What are the three main components on LINQ?


• What is the use LINQ and Entity Framework?


• What are Data Context Object and Entity Object?


• What are LINQ DBML and LINQ EDMX?

Following questions is related toAzure.

• What is Cloud Computing?


• What are blobs, tables and Queues?

Following questions is related toWWF.

• What areSequential Workflow and State Machine Workflow?

Architecture
Following questions is related to Design Pattern.

• What is Factory Design Pattern?


• What areSingleton Design Pattern and Iterator?

Following questions is related to MVC, MVP and MVVM.

• Define MVC, MVP and MVVM?


• What is MVC template and when to use?

Following questions is related toUML.

• What are Use cases?


• What is Activity diagram and Class diagram?

Following questions is related toDocumentation.

• What areRequirement document and Design document?


• What is test plan?

Following questions is related toRequirement Design.

• How will you go from Requirement to Design?

Process
Following questions is related toAgile.

• What are Scrum Backlog Template and Simple Backlog Template?

Following questions is related toEstimation FPA.

• What are EI, EO, EQ and EIF?

Following questions is related toCMMI.

• What is meant by Key Process Area (KPA)?


• What are the different five level of CMMI?

Following questions is related toSix Sigma.

• What is Six Sigma?


• What is the importance’s of Six Sigma?


• What are Six Sigma Yellow Belt and Black Belt?

Regards,
Please click here to see .NET and C# interview questions and answers