Difference between Working With Html.BeginForm() and Ajax.BeginForm()

Ajax: 
1. Won't redirect the form even you do a RedirectAction().
2. Will perform save , update and any modification operations asynchronously. 
3. Validate the Form using FormMethods - OnSubmit. So you are abort the Post 
4. This creates a form that submits its values using an asynchronous ajax request. This allows a portion of the page to be update without requiring that the entire page be refreshed. 


Html : 
1. Will redirect the form. 
2. Will perform operations both Synchronously and Asynchronously (With some extra code and care). 3. Html.BeginForm will always use RouteTable to detrmine the action attribute value.
4. This will create a form on the page that submits its values to the server as a synchronous HTTP request, refreshing the entire page in the process.

MVC : Form Submission

View :
@model MVCDemo.Models.StudentDetail

<script type="text/javascript" src="js/script.js"></script>
<script src="http://ajax.googleapis.com/ajax/libs/jquery/1.7.1/jquery.min.js" type="text/javascript"></script>




@{
    ViewBag.Title = "CreateStudent";
}

<h2>CreateStudent</h2>


@using (Html.BeginForm("CreateStudent11", "GetStudents", FormMethod.Post))
{
    @Html.AntiForgeryToken()
   
    <div class="form-horizontal">
        <h4>StudentDetail</h4>
        <hr />
        @Html.ValidationSummary(true, "", new { @class = "text-danger" })
        <div class="form-group">
            @Html.LabelFor(model => model.Name, htmlAttributes: new { @class = "control-label col-md-2" })
            <div class="col-md-10">
                @Html.EditorFor(model => model.Name, new { htmlAttributes = new { @class = "form-control" } })
                @Html.ValidationMessageFor(model => model.Name, "", new { @class = "text-danger" })
            </div>
        </div>

        <div class="form-group">
            @Html.LabelFor(model => model.Class, htmlAttributes: new { @class = "control-label col-md-2" })
            <div class="col-md-10">
                @Html.EditorFor(model => model.Class, new { htmlAttributes = new { @class = "form-control" } })
                @Html.ValidationMessageFor(model => model.Class, "", new { @class = "text-danger" })
            </div>
        </div>

        <div class="form-group">
            @Html.LabelFor(model => model.Section, htmlAttributes: new { @class = "control-label col-md-2" })
            <div class="col-md-10">
                @Html.EditorFor(model => model.Section, new { htmlAttributes = new { @class = "form-control" } })
                @Html.ValidationMessageFor(model => model.Section, "", new { @class = "text-danger" })
            </div>
        </div>

        <div class="form-group">
            @Html.LabelFor(model => model.Address, htmlAttributes: new { @class = "control-label col-md-2" })
            <div class="col-md-10">
                @Html.EditorFor(model => model.Address, new { htmlAttributes = new { @class = "form-control" } })
                @Html.ValidationMessageFor(model => model.Address, "", new { @class = "text-danger" })
            </div>
        </div>

        <div class="form-group">
            <div class="col-md-offset-2 col-md-10">
                <input type="submit" value="Create" id="Submit" class="btn btn-default" onclick="location.href='@Url.Action("GetStudents", "CreateStudent11")'"/>
            </div>
        </div>
    </div>
}

<div>
    @Html.ActionLink("Back to List", "Index")
</div>

@section Scripts {
    @Scripts.Render("~/bundles/jqueryval")
}

Model :

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;

namespace MVCDemo.Models
{
    public class StudentDetails
    {
        public List<StudentDetail> Studentmodel { get; set; }

        public List<StudentDetail> GetStudentList()
        {
            List<StudentDetail> objStudent = new List<StudentDetail>();
            objStudent.Add(new StudentDetail { Id = 1, Name = "Ajay", Class = "1", Section = "2", Address = "Pune" });
            objStudent.Add(new StudentDetail { Id = 1, Name = "Aarav", Class = "1", Section = "2", Address = "Pune" });
            objStudent.Add(new StudentDetail { Id = 1, Name = "Rakhi", Class = "1", Section = "2", Address = "Pune" });
            return objStudent;
        }

        public List<StudentDetail> GetStudentList(int? id)
        {
            List<StudentDetail> objStudent = new List<StudentDetail>();
            objStudent.Add(new StudentDetail { Id = 1, Name = "Ajay", Class = "1", Section = "2", Address = "Pune" });
            if (id == null)
                return objStudent;
            else
            return objStudent.Where(P=>P.Id== id).ToList();
        }


    }
    public class StudentDetail
    {
        public int Id { get; set; }
        public string Name { get; set; }
        public string Class { get; set; }
        public string Section { get; set; }
        public string Address { get; set; }

     
    }
}



Controller :

using MVCDemo.Models;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Mvc;

namespace Student_Application.Controllers
{
    public class GetStudentsController : Controller
    {
        // GET: GetStudents
        public ActionResult Index()
        {
            StudentDetails _objuserloginmodel = new StudentDetails();
            List<StudentDetail> _objStudent = new List<StudentDetail>();
            _objStudent = _objuserloginmodel.GetStudentList();
            _objuserloginmodel.Studentmodel = _objStudent;
            return View(_objuserloginmodel);
        }

        [HttpGet]
        public ActionResult SelectStudent(int? selectedStudent)
        {
            StudentDetails _objuserloginmodel = new StudentDetails();
            List<StudentDetail> _objStudent = new List<StudentDetail>();
            _objStudent = _objuserloginmodel.GetStudentList(selectedStudent);
            _objuserloginmodel.Studentmodel = _objStudent;

            return PartialView("_PartialStudent", _objuserloginmodel);

        }

       
        public ActionResult CreateStudent(StudentDetail objuserloginmodel)
        {
           
            if (!ModelState.IsValid)
            {
                return View("CreateStudent", objuserloginmodel);
            }
            else
            {
                StudentDetail _objuserloginmodel = new StudentDetail();
                return View("CreateStudent", _objuserloginmodel);
            }

        }

        [HttpPost]
        public ActionResult CreateStudent11(StudentDetail objuserloginmodel)
        {
            StudentDetails _objuserloginmodel = new StudentDetails();
            List<StudentDetail> _objStudent = new List<StudentDetail>();
            _objStudent = _objuserloginmodel.GetStudentList();
            _objuserloginmodel.Studentmodel = _objStudent;
            return View("Index",_objuserloginmodel);
        }

    }

    public class SubmitButtonSelector : ActionNameSelectorAttribute
    {
        public string Name { get; set; }
        public override bool IsValidName(ControllerContext controllerContext, string actionName, System.Reflection.MethodInfo methodInfo)
        {
            // Try to find out if the name exists in the data sent from form
            var value = controllerContext.Controller.ValueProvider.GetValue(Name);
            if (value != null)
            {
                return true;
            }
            return false;

        }
    }
}

Jquery Post Get getjson

http://www.itorian.com/2013/02/jquery-ajax-get-and-post-calls-to.html

http://www.codeproject.com/Articles/577776/Filters-and-Attributes-in-ASPNET-MVC

Cares has to be taken while working MVC

1: Partial View always in shared folder
2: Always check for null condition in the partial view
3: $ is not defined means the Jquery and Javascript namespaces are not included
4: bind the proper model to the view. Inproper model wont give the error.
5: Verify with alert("") is your selected value is called in jquery

Updating an MVC Partial View with Ajax

https://cmatskas.com/update-an-mvc-partial-view-with-ajax/

MVC : Calling the Partial View on Selected Index changed


@model MVCDemo.Models.StudentDetails
@{
    ViewBag.Title = "Index";
}

<script type="text/javascript" src="js/script.js"></script>
<script src="http://ajax.googleapis.com/ajax/libs/jquery/1.7.1/jquery.min.js" type="text/javascript"></script>


<script type="text/javascript">
   $(document).ready(function () {
        $(document).ready(function () {
            $("#myDropDown").change(function () {
                var selectedID = $(this).val();
                alert(selectedID);
                var categoryId = $("#myDropDown").val();
                $("#dvCategoryResults").load('@(Url.Action("SelectStudent", "GetStudents", null, Request.Url.Scheme))?selectedStudent=' + selectedID);
            });
        });

    });
</script>




<style>
    table, td, th {
        border: 1px solid green;
        border-collapse: collapse;
    }

    th {
        border: 1px solid black;
        background-color: green;
        color: white;
    }
</style>

<br><br><br><br><br><br>
@Html.DropDownListFor(M => M.Studentmodel, new SelectList(Model.Studentmodel, "ID", "Name",0),"Please Select", new { @id = "myDropDown" })



<br>

<div id="dvCategoryResults">
    @Html.Partial("~/Views/Shared/_PartialStudent.cshtml", Model);
</div>

using MVCDemo.Models;

using System;

using System.Collections.Generic;

using System.Linq;

using System.Web;

using System.Web.Mvc;

namespace Student_Application.Controllers

{

    public class GetStudentsController : Controller

    {

        // GET: GetStudents

        public ActionResult Index()

        {

            StudentDetails _objuserloginmodel = new StudentDetails();

            List<StudentDetail> _objStudent = new List<StudentDetail>();

            _objStudent = _objuserloginmodel.GetStudentList();

            _objuserloginmodel.Studentmodel = _objStudent;

            return View(_objuserloginmodel);

        }

        [HttpGet]

        public ActionResult SelectStudent(int? selectedStudent)

        {

            StudentDetails _objuserloginmodel = new StudentDetails();

            List<StudentDetail> _objStudent = new List<StudentDetail>();

            _objStudent = _objuserloginmodel.GetStudentList(selectedStudent);

            _objuserloginmodel.Studentmodel = _objStudent;

            return PartialView("_PartialStudent", _objuserloginmodel);

        }

    }

}



Partial View


@model MVCDemo.Models.StudentDetails


@if (Model.Studentmodel != null && Model.Studentmodel.Count() > 0)
{



    var grid = new WebGrid(Model.Studentmodel, canSort: false);

    <div>
        @grid.GetHtml(columns:
                                grid.Columns
                                (
                                    grid.Column("ID", "Stud ID"),
                                    grid.Column("Name", "Stud Name"),
                                    grid.Column("Class", "Class"),
                                    grid.Column("Section", "Section"),
                                    grid.Column("Address", "Address")
                                ), mode: WebGridPagerModes.Numeric)
    </div>

}

MVC : how-to-add-a-please-select-item-to-a-dropdown-box-in-asp-net-mvc

http://stackoverflow.com/questions/2648366/how-to-add-a-please-select-item-to-a-dropdown-box-in-asp-net-mvc

MVC : Bind the list to the dropdown

http://www.c-sharpcorner.com/UploadFile/4d9083/binding-dropdownlist-in-mvc-in-various-ways-in-mvc-with-data/

MVC : Bind the collection to Grid

http://www.aspdotnet-pools.com/2014/07/how-to-bind-data-to-webgrid-in-aspnet.html

MVC Question and Answer Part 2

What are the 3 main components of an ASP.NET MVC application?
1. M - Model
2. V - View
3. C - Controller

In which assembly is the MVC framework defined?
System.Web.Mvc

Is it possible to combine ASP.NET webforms and ASP.MVC and develop a single web application?
Yes, it is possible to combine ASP.NET webforms and ASP.MVC and develop a single web application.

What does Model, View and Controller represent in an MVC application?
Model: Model represents the application data domain. In short the applications business logic is contained with in the model.

View: Views represent the user interface, with which the end users interact. In short the all the user interface logic is contained with in the UI.

Controller: Controller is the component that responds to user actions. Based on the user actions, the respective controller, work with the model, and selects a view to render that displays the user interface. The user input logic is contained with in the controller.

What is the greatest advantage of using asp.net mvc over asp.net webforms?
It is difficult to unit test UI with webforms, where views in mvc can be very easily unit tested.

Which approach provides better support for test driven development - ASP.NET MVC or ASP.NET Webforms?
ASP.NET MVC

What are the advantages of ASP.NET MVC?
1. Extensive support for TDD. With asp.net MVC, views can also be very easily unit tested.
2. Complex applications can be easily managed
3. Seperation of concerns. Different aspects of the application can be divided into Model, View and Controller.
4. ASP.NET MVC views are light weight, as they donot use viewstate.

Is it possible to unit test an MVC application without running the controllers in an ASP.NET process?
Yes, all the features in an asp.net MVC application are interface based and hence mocking is much easier. So, we don't have to run the controllers in an ASP.NET process for unit testing.

Is it possible to share a view across multiple controllers?
Yes, put the view into the shared folder. This will automatically make the view available across multiple controllers.

What is the role of a controller in an MVC application?
The controller responds to user interactions, with the application, by selecting the action method to execute and alse selecting the view to render.

Where are the routing rules defined in an asp.net MVC application?
In Application_Start event in Global.asax

Name a few different return types of a controller action method?
The following are just a few return types of a controller action method. In general an action method can return an instance of a any class that derives from ActionResult class.
1. ViewResult
2. JavaScriptResult
3. RedirectResult
4. ContentResult
5. JsonResult

What is the significance of NonActionAttribute?
In general, all public methods of a controller class are treated as action methods. If you want prevent this default behaviour, just decorate the public method with NonActionAttribute.

What is the significance of ASP.NET routing?
ASP.NET MVC uses ASP.NET routing, to map incoming browser requests to controller action methods. ASP.NET Routing makes use of route table. Route table is created when your web application first starts. The route table is present in the Global.asax file.

What are the 3 segments of the default route, that is present in an ASP.NET MVC application?
1st Segment - Controller Name
2nd Segment - Action Method Name
3rd Segment - Parameter that is passed to the action method

Example: http://venkatrangala.com/Employee/Details/5
Controller Name = Customer
Action Method Name = Details
Parameter Id = 5

ASP.NET MVC application, makes use of settings at 2 places for routing to work correctly. What are these 2 places?
1. Web.Config File : ASP.NET routing has to be enabled here.
2. Global.asax File : The Route table is created in the application Start event handler, of the Global.asax file.

What is the adavantage of using ASP.NET routing?
In an ASP.NET web application that does not make use of routing, an incoming browser request should map to a physical file. If the file does not exist, we get page not found error.

An ASP.NET web application that does make use of routing, makes use of URLs that do not have to map to specific files in a Web site. Because the URL does not have to map to a file, you can use URLs that are descriptive of the user's action and therefore are more easily understood by users.

What are the 3 things that are needed to specify a route?
1. URL Pattern - You can include placeholders in a URL pattern so that variable data can be passed to the request handler without requiring a query string.
2. Handler - The handler can be a physical file such as an .aspx file or a controller class.
3. Name for the Route - Name is optional.

Is the following route definition a valid route definition?
{controller}{action}/{id}
No, the above definition is not a valid route definition, because there is no literal value or delimiter between the placeholders. Therefore, routing cannot determine where to separate the value for the controller placeholder from the value for the action placeholder.

What is the use of the following default route?
{resource}.axd/{*pathInfo}
This route definition, prevent requests for the Web resource files such as WebResource.axd or ScriptResource.axd from being passed to a controller.

What is the difference between adding routes, to a webforms application and to an mvc application?
To add routes to a webforms application, we use MapPageRoute() method of the RouteCollection class, where as to add routes to an MVC application we use MapRoute() method.

How do you handle variable number of segments in a route definition?
Use a route with a catch-all parameter. An example is shown below. * is referred to as catch-all parameter.
controller/{action}/{*parametervalues}

What are the 2 ways of adding constraints to a route?
1. Use regular expressions
2. Use an object that implements IRouteConstraint interface

Give 2 examples for scenarios when routing is not applied?
1. A Physical File is Found that Matches the URL Pattern - This default behaviour can be overriden by setting the RouteExistingFiles property of the RouteCollection object to true.
2. Routing Is Explicitly Disabled for a URL Pattern - Use the RouteCollection.Ignore() method to prevent routing from handling certain requests.

What is the use of action filters in an MVC application?
Action Filters allow us to add pre-action and post-action behavior to controller action methods.

If I have multiple filters impleted, what is the order in which these filters get executed?
1. Authorization filters
2. Action filters
3. Response filters
4. Exception filters

What are the different types of filters, in an asp.net mvc application?
1. Authorization filters
2. Action filters
3. Result filters
4. Exception filters

Give an example for Authorization filters in an asp.net mvc application?
1. RequireHttpsAttribute
2. AuthorizeAttribute

Which filter executes first in an asp.net mvc application?
Authorization filter


What are the levels at which filters can be applied in an asp.net mvc application?
1. Action Method
2. Controller
3. Application
[b]Is it possible to create a custom filter?[/b]
Yes

What filters are executed in the end?
Exception Filters

Is it possible to cancel filter execution?
Yes

What type of filter does OutputCacheAttribute class represents?
Result Filter

What are the 2 popular asp.net mvc view engines?
1. Razor
2. .aspx

What symbol would you use to denote, the start of a code block in razor views?
@

What symbol would you use to denote, the start of a code block in aspx views?
<%= %>

In razor syntax, what is the escape sequence character for @ symbol?
The escape sequence character for @ symbol, is another @ symbol

When using razor views, do you have to take any special steps to proctect your asp.net mvc application from cross site scripting (XSS) attacks?
No, by default content emitted using a @ block is automatically HTML encoded to protect from cross site scripting (XSS) attacks.

When using aspx view engine, to have a consistent look and feel, across all pages of the application, we can make use of asp.net master pages. What is asp.net master pages equivalent, when using razor views?
To have a consistent look and feel when using razor views, we can make use of layout pages. Layout pages, reside in the shared folder, and are named as _Layout.cshtml

What are sections?
Layout pages, can define sections, which can then be overriden by specific views making use of the layout. Defining and overriding sections is optional.

What are the file extensions for razor views?
1. .cshtml - If the programming lanugaue is C#
2. .vbhtml - If the programming lanugaue is VB

How do you specify comments using razor syntax?
Razor syntax makes use of @* to indicate the begining of a comment and *@ to indicate the end. An example is shown below.
@* This is a Comment *@

MVC Question And Answer Part1

What are the 3 main components of an ASP.NET MVC application?
1. M - Model
2. V - View
3. C - Controller

In which assembly is the MVC framework defined?
System.Web.Mvc

Is it possible to combine ASP.NET webforms and ASP.MVC and develop a single web application?
Yes, it is possible to combine ASP.NET webforms and ASP.MVC and develop a single web application.

What does Model, View and Controller represent in an MVC application?
Model: Model represents the application data domain. In short the applications business logic is contained with in the model.

View: Views represent the user interface, with which the end users interact. In short the all the user interface logic is contained with in the UI.

Controller: Controller is the component that responds to user actions. Based on the user actions, the respective controller, work with the model, and selects a view to render that displays the user interface. The user input logic is contained with in the controller.

What is the greatest advantage of using asp.net mvc over asp.net webforms?
It is difficult to unit test UI with webforms, where views in mvc can be very easily unit tested.

Which approach provides better support for test driven development - ASP.NET MVC or ASP.NET Webforms?
ASP.NET MVC

What are the advantages of ASP.NET MVC?
1. Extensive support for TDD. With asp.net MVC, views can also be very easily unit tested.
2. Complex applications can be easily managed
3. Seperation of concerns. Different aspects of the application can be divided into Model, View and Controller.
4. ASP.NET MVC views are light weight, as they donot use viewstate.

Is it possible to unit test an MVC application without running the controllers in an ASP.NET process?
Yes, all the features in an asp.net MVC application are interface based and hence mocking is much easier. So, we don't have to run the controllers in an ASP.NET process for unit testing.

Is it possible to share a view across multiple controllers?
Yes, put the view into the shared folder. This will automatically make the view available across multiple controllers.

What is the role of a controller in an MVC application?
The controller responds to user interactions, with the application, by selecting the action method to execute and alse selecting the view to render.

Where are the routing rules defined in an asp.net MVC application?
In Application_Start event in Global.asax

Name a few different return types of a controller action method?
The following are just a few return types of a controller action method. In general an action method can return an instance of a any class that derives from ActionResult class.
1. ViewResult
2. JavaScriptResult
3. RedirectResult
4. ContentResult
5. JsonResult

What is the significance of NonActionAttribute?
In general, all public methods of a controller class are treated as action methods. If you want prevent this default behaviour, just decorate the public method with NonActionAttribute

How can we pass parameters to Static Constructors?

static constructor is called when the execution of the class is started. It is the first member to be execute in the class. at this time how can we pass any parameters to it. if we create object of the class then it will call the non static constructor. so we can not pass any parameters into it at any cost.

As MSDN says, A static constructor is called automatically to initialize the class before the first instance is created. Therefore you can't send it any parameters. If the CLR must call a static constructor how will it know which parameters to pass it?

MongoDB Restore Database command

mongorestore  "path of your dump folder"

Structure of Unit Test class

For the unit test to be recognized by the Team System testing tools, this source-code file must reside in a test project, which in turn is a part of a Visual Studio solution. When you build this project, or the entire solution, the test project is built into an assembly that contains the executable unit test.

All unit test methods are marked with the [TestMethod()] attribute, <TestMethod()> in Visual Basic, and are members of the [TestClass()] class. In turn, this class is defined in the namespaceMicrosoft.VisualStudio.TestTools.UnitTesting. When you generate a unit test, you see that this namespace is included at the beginning of the generated file, in a using or Imports statement.

Unit Test Attributes and Properties

In addition to the [TestMethod()] attribute of the unit test method and the [TestClass()] attribute of its containing class, other attributes are used to enable specific unit-test functionality. Primary among these attributes are[TestInitialize()] and [TestCleanup()]. Use a method marked with [TestInitialize()] to prepare aspects of the environment in which your unit test will run; the purpose of doing this is to establish a known state for running your unit test. For example, you may use a [TestInitialize()] method to copy, alter, or create certain data files that your test will use.

Use a method marked with [TestCleanup()] to return the environment to a known state after a test has run; this might mean deleting files in folders, or returning a database to a known state. An example of this is resetting an inventory database to an initial state after testing a method used in an order-entry application. Furthermore, it is recommended that you use cleanup code in a [TestCleanup()]or ClassCleanup method and not in a finalizer method. Exceptions that are thrown from a finalizer method will not be caught, and can cause unexpected results.

An important property on test classes is the TestContext property. This property contains information including the name of the unit test that is currently running, the deployment directory, the names of log files, and for data-driven testing, the database to which you are connected. The TestContext property returns a TestContext instance. For more information, see Using the TestContext Class.

Unit Test Example

The following code snippet shows a simple unit test written in C#.

[TestMethod()]
public void DebitTest()
{
    string customerName = "Mr. Bryan Walton"; 
    double balance = 11.99; 
    BankAccount target = new BankAccount(customerName, balance);
    double amount = 11.22; 
    target.Debit(amount);
    Assert.AreEqual((System.Convert.ToDouble(0.77)), target.Balance, 0.05); // 0.05 is tolerance for floating-point comparison
    //Assert.Inconclusive("A method that does not return a value cannot be verified.");
}

For another example, see Coding a Data-Driven Unit Test.

Unit Test Outcomes

There are three ways to verify that a unit test has passed:

• Use one or more Assert statements to validate specific outcomes. For more information, see Using Assert Statements.
• Verify that no exception was thrown. It is still advisable to use one or more Assert statements.
• Verify that a particular exception is thrown. You can do this by using the ExpectedExceptionAttribute attribute.

Using Assert Statements

If the Pass or Fail result that a test produces is more informative or important to you than the actions that the test might complete, you should use one or more Assert statements in the test's code.

The test engine assumes that every unit test starts in a passing state. The test remains in that state until an Assert statement produces a result that contradicts the passing state, changing it from Pass to Fail or Inconclusive, or until an exception not specified in an ExpectedExceptionAttribute attribute is thrown. In another words, a unit test without an Assert statement will produce a Pass result every time it is run. This is not necessarily a useless test; you might just want to exercise the code to make sure it runs without throwing an exception. Code exercised in a unit test will be reported as covered in the code-coverage statistics regardless of whether the test produced a decisive result or even contained an Assert statement.

However, to verify that a specific action is being taken or a specific state is being reached, you must use Asserts. Several Assert statements are available to you in the Microsoft.VisualStudio.TestTools.UnitTesting namespace. The various Assert statements give you flexibility; for example, you can force a test to fail by using the statement Assert.Fail(). In addition to these Assert statements, you can, of course, construct your own custom functionality, perhaps by using Assert statements in if blocks.

Regardless of what results a test returns, it passes or fails depending on its Assert statements. If a test contains several Asserts, the state of the test remains Pass until an Assert is encountered that changes the state to Fail or Inconclusive.

Clustered and Non-Clustered Index

1. Introduction

We all know that data entered in the tables are persisted in the physical drive in the form of database files. Think about a table, say Customer (For any leading bank India), that has around 16 million records. When we try to retrieve records for two or three customers based on their customer id, all 16 million records are taken and comparison is made to get a match on the supplied customer ids. Think about how much time that will take if it is a web application and there are 25 to 30 customers that want to access their data through internet. Does the database server do 16 million x 30 searches? The answer is no because all modern databases use the concept of index.

2. What is an Index

Index is a database object, which can be created on one or more columns (16 Max column combination). When creating the index will read the column(s) and forms a relevant data structure to minimize the number of data comparisons. The index will improve the performance of data retrieval and adds some overhead on data modification such as create, delete and modify. So it depends on how much data retrieval can be performed on table versus how much of DML (Insert, Delete and Update) operations.

In this article, we will see creating the Index. The below two sections are taken from my previous article as it is required here. If your database has changes for the next two sections, you can directly go to section 5.

3. First Create Two Tables

To explain these constraints, we need two tables. First, let us create these tables. Run the below scripts to create the tables. Copy paste the code on the new Query Editor window, then execute it.

CREATE TABLE Student(StudId smallint, StudName varchar(50), Class tinyint);

CREATE TABLE TotalMarks(StudentId smallint, TotalMarks smallint);

Go

Note that there are no constraints at present on these tables. We will add the constraints one by one.

4. Primary Key Constraint

A table column with this constraint is called as the key column for the table. This constraint helps the table to make sure that the value is not repeated and also no null entries. We will mark the StudId column of theStudent table as primary key. Follow these steps:

1.       Right click the student table and click on the modify button.

2.       From the displayed layout, select the StudId row by clicking the Small Square like button on the left side of the row.

3.       Click on the Set Primary Key toolbar button to set the StudId column as primary key column.

Now this column does not allow null values and duplicate values. You can try inserting values to violate these conditions and see what happens. A table can have only one Primary key. Multiple columns can participate on the primary key column. Then, the uniqueness is considered among all the participant columns by combining their values.

5. Clustered Index

The primary key created for the StudId column will create a clustered index for the Studid column. A table can have only one clustered index on it.

When creating the clustered index, SQL server 2005 reads the Studid column and forms a Binary tree on it. This binary tree information is then stored separately in the disc. Expand the table Student and then expand theIndexes. You will see the following index created for you when the primary key is created:

With the use of the binary tree, now the search for the student based on the studid decreases the number of comparisons to a large amount. Let us assume that you had entered the following data in the table student:

The index will form the below specified binary tree. Note that for a given parent, there are only one or twoChilds. The left side will always have a lesser value and the right side will always have a greater value when compared to parent. The tree can be constructed in the reverse way also. That is, left side higher and right side lower.

Now let us assume that we had written a query like below:

Select * from student where studid = 103;

Select * from student where studid = 107;

Execution without index will return value for the first query after third comparison.
Execution without index will return value for the second query at eights comparison.

Execution of first query with index will return value at first comparison.
Execution of second query with index will return the value at the third comparison. Look below:

1.       Compare 107 vs 103 : Move to right node

2.       Compare 107 vs 106 : Move to right node

3.       Compare 107 vs 107 : Matched, return the record

If numbers of records are less, you cannot see a different one. Now apply this technique with a Yahoo email user accounts stored in a table called say YahooLogin. Let us assume there are 33 million users around the world that have Yahoo email id and that is stored in the YahooLogin. When a user logs in by giving the user name and password, the comparison required is 1 to 25, with the binary tree that is clustered index.

Look at the above picture and guess yourself how fast you will reach into the level 25. Without Clustered index, the comparison required is 1 to 33 millions.

The above explanation is for easy understanding. Now a days SQL server is using the B-Tree techniques to represent the clustered index. 

Got the usage of Clustered index? Let us move to Non-Clustered index. 

6. Non Clustered Index

A non-clustered index is useful for columns that have some repeated values. Say for example, AccountTypecolumn of a bank database may have 10 million rows. But, the distinct values of account type may be 10-15. A clustered index is automatically created when we create the primary key for the table. We need to take care of the creation of the non-clustered index.

Follow the steps below to create a Non-clustered index on our table Student based on the column class.

1.       After expanding the Student table, right click on the Indexes. And click on the New Index.

2.       From the displayed dialog, type the index name as shown below and then click on the Add button to select the column(s) that participate in the index. Make sure the Index type is Non-Clustered.

3.       In the select column dialog, place a check mark for the column class. This tells that we need a non-clustered index for the column Student.Class. You can also combine more than one column to create the Index. Once the column is selected, click on the OK button. You will return the dialog shown above with the selected column marked in blue. Our index has only one column. If you selected more than one column, using the MoveUp and MoveDown button, you can change order of the indexed columns. When you are using the combination of columns, always use the highly repeated column first and more unique columns down in the list. For example, let use assume the correct order for creating the Non-clustered index is: Class, DateOfBirth, PlaceOfBirth.

4.       Click on the Index folder on the right side and you will see the non-clustered index based on the column class is created for you.

7. How Does a Non-Clustered Index Work?

A table can have more than one Non-Clustered index. But, it should have only one clustered index that works based on the Binary tree concept. Non-Clustered column always depends on the Clustered column on the database.

This can be easily explained with the concept of a book and its index page at the end. Let us assume that you are going to a bookshop and found a big 1500 pages of C# book that says all about C#. When you glanced at the book, it has all beautiful color pages and shiny papers. But, that is not only the eligibility for a good book right? One you are impressed, you want to see your favorite topic of Regular Expressions and how it is explained in the book. What will you do? I just peeped at you from behind and recorded what you did as below:

1.       You went to the Index page (it has total 25 pages). It is already sorted and hence you easily picked up Regular Expression that comes on page Number 17.

2.       Next, you noted down the number displayed next to it which is 407, 816, 1200-1220.

3.       Your first target is Page 407. You opened a page in the middle, the page is greater than 500.

4.       Then you moved to a somewhat lower page. But it still reads 310.

5.       Then you moved to a higher page. You are very lucky you exactly got page 407. [Yes man you got it. Otherwise I need to write more. OK?]

6.       That’s all, you started exploring what is written about Regular expression on that page, keeping in mind that you need to find page 816 also.

In the above scenario, the Index page is Non-Clustered index and the page numbers are clustered index arranged in a binary tree. See how you came to the page 407 very quickly. Your mind actually traversed the binary tree way left and right to reach the page 407 quickly.

Here, the class column with distinct values 1,2,3..12 will store the clustered index columns value along with it. Say for example; Let us take only class value of 1. The Index goes like this:

1: 100, 104, 105

So here, you can easily get all the records that have value for class = 1. Map this with the Book indexexample now. See you all in the next article.