Managing indexes with pre-defined Query: SQL Server

In addition to creating indexes in sql server, database developer also need to maintain them to ensure their continued optimal performance. The common index maintenance tasks include disabling, enabling, renaming, and dropping an index. As a database developer, you need to regularly monitor the performance of the index and optimize it.

Disabling Indexes

When an index is disabled, the user is not able to access the index. If a clustered index is disabled then the table data is not accessible to the user. However, the data still remains in the table, but is unavailable for Data Modification Language (DML) operations until the index is dropped or rebuilt.

To rebuild and enable a disabled index, use the ALTER INDEX REBUILD statement or the CREATE INDEX WITH DROP_EXISTING statement.

The following query disables a non-clustered index, IX_EmployeeID, on the Employee table.

ALTER INDEX IX_EmployeeID
ON Employee DISABLE

Enabling Indexes

After an index is disabled, it remains in the disabled state until it is rebuilt or dropped. You can enable a disabled index by rebuilding it through one of the following methods:

• Using the ALTER INDEX statement with the REBUILD clause
• Using the CREATE INDEX statement with the DROP_EXISTING clause
• Using the DBCC DBREINDEX

By using one of the preceding statements, the index is rebuilt and the index status is set to enable. You can rebuild a disabled clustered index, when the ONLINE option is set to ON.

Renaming Indexes

You can rename the current index with the help of the sp_rename system stored procedure.
The following statement renames the IX_JobCandidate_EmployeeID index on the JobCandidate table to IX_EmployeeID.

EXEC sp_rename
‘HumanResources.JobCandidate.IX_JobCandidate_EmployeeID’,
‘IX_EmployeeID’, ‘index’

Dropping Indexes

When you no longer need an index, you can remove it from a database. You cannot drop an index used by either a PRIMARY KEY or UNIQUE constraint, except by dropping the constraint.

The following statement drops the IDX_Employee_ManagerID index on the Employee table:

DROP INDEX IDX_Employee_ManagerID
ON Employee

How to Create Partition Scheme and Clustered Index: SQL Server

After creating the partition function, programmer needs to create a partition scheme to associate it with the partition function. Based on the boundary values defined in the partition function, there will be five partitions. The data of each partition is stored in a filegroup. You should have the same number of filegroups as partitions. If there are five partitions, you need to create five filegroups: fg1, fg2, fg3, fg4, fg5.

The following statements create the PSOrderDate partition scheme, associating it with the PFOrderDate partition function:

CREATE PARTITION SCHEME PSOrderDate
AS PARTITION PFOrderDate
TO (fg1, fg2, fg3, fg4, fg5)

The partition scheme, PSOrderDate, created in the preceding statement directs each partition to a separate filegroup.

Creating a Clustered Index

After creating the partition scheme, you need to associate it with a clustered index. As the clustered index is created on the attribute having unique and non-null values, you can create the index on the SalesOrderID column of the SalesOrderHeader table. To create the partitioned index, you need to associate the clustered index with the partition scheme as follows:

CREATE CLUSTERED INDEX ix_SalesOrderID
ON Sales.MySalesOrderHeader (SalesOrderID)
ON PSOrderDate (OrderDate)

The preceding statement will distribute the table data into five filegroups based on the yearly data of orders stored in the OrderDate column.

How to Create Partitioned Indexes in SQL Server

In SQL Server, indexes can also be partitioned based on the value ranges. Similar to the partitioned tables, the partitioned indexes also improve query performance. Partitioning enables you to manage and access subsets of data quickly and efficiently. When indexes become very large, you can partition the data into smaller, more manageable sections as the data is spread across file groups in a database.

Consider an example. In the Adventure Works database, the SalesOrderHeader table contains the details about the order received by Adventure Works, Inc. As the data in this table is large, the query takes a long time to execute. To solve this problem, you can create a partitioned index on the table. Partitioning an index will distribute the data in the table into multiple filegroups, thereby partitioning the table. This will enable the database engine to read or write data quickly. This also helps in maintaining the data efficiently.

Partitioning is allowed only in the Enterprise Edition of SQL Server. To create a partitioned index, you need to perform the following tasks:

• Create a partition function
• Create a partition scheme.
• Create a clustered index.

Creating a Partition Function

Similar to creating a partitioned table, you need to create a partition function to create a partitioned index. The partition function will determine the boundary values for creating partitions.

Consider an example. The queries on the SalesOrderHeader table are mostly base on the OrderData column. The sales manager of Adventure Works requires the details of the orders received, on a yearly basis. The table contains the details of orders for the last five years beginning from 2001. Based on this information, you can create a partition function as follows:

CREATE PARTITION FUNCTION PFOrderDate (date time)
AS RANGE RIGHT FOR VALUES (‘2002-01-01’, ‘2003-01-01’, ‘2004-01-01’, ‘2005-01-01’)

The preceding statement creates a partition function, PFOrderDate, by using the date time data type. It specifies four boundary values. Therefore, there will be five partitions. As range right is specified for partitioning, the first partition will contain data less than the first boundary value, 2002-01-01. The second partition will contain data greater than or equal to 2002-01-01 and less than or equal to 2003-01-01. Similarly, other partitions will store data.

How to bind Gridview using strong model binding in asp.net

In our previous article, we have already learn how to bind GridView using ado.net, Entityframework, etc. Now, today we will learn strong model binding. Follow my steps
Step-1 :  Create a course.cs class, which is include in my previous article
Step-2 : Create a another class, which named as DataContext.cs class also include in my previous article.
Step-3 : After adding the two class in the project , add a new web form, which named as "getItem.aspx".
Step-4 : Add GridView control on web form
Step-5 :  Your source code looking like this

<form id="form1" runat="server">
    <div>
    
    </div>
        <asp:GridView ID="GridView1" runat="server" DataKeyNames ="ID" AutoGenerateColumns="false" ItemType="course" SelectMethod="getCourse">
            <Columns>
                
                <asp:BoundField DataField="ID" HeaderText="Course_Id" />
                  <asp:BoundField DataField="ccode" HeaderText="Course_code" />
                 <asp:BoundField DataField="cname" HeaderText="Course_Name" />


            </Columns>
        </asp:GridView>
    </form>

Here we add two extra attribute, which names as SelectMethod and ItemType. ItemType define the whole structure of your code. Suppose your course class inside in a namespace then your ItemType is
namespace.class name. Using the select method you can retrieve table data from database.

Step-6 : copy this code and paste into your code behind page

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

public partial class getitem : System.Web.UI.Page
{
    private readonly DataContext dc = new DataContext();
    protected void Page_Load(object sender, EventArgs e)
    {

    }
    public IQueryable<course> getCourse()
    {
        return dc.courses;
    }

}
First to create DataContext object, which is used for retrieving data from database. Also create Select method which return  IQueryable type.

Code generate the following output

How to Bind Gridview using Entity Framework in ASP.NET

In our previous article, we have already learned that how to bind gridview using ado.net. Today we will learn simplest technique to bind gridview using entity framework. Lets start to bind.

Step-1 : Add a Course class with some attributes like

using System;

using System.Collections.Generic;

using System.Linq;

using System.Web;

/// <summary>

/// Summary description for course

/// </summary>

public class course

{

    public int ID { get; set; }

    public string cname { get; set; }

    public string ccode { get; set; }

}

In this code ID is the primary key of the table. Learn How to make primary key in entity framework.

Step-2 : Add a DataContext class for creating database with some table like.

using System;

using System.Collections.Generic;

using System.Data.Entity;

using System.Linq;

using System.Web;

/// <summary>

/// Summary description for DataContext

/// </summary>

public class DataContext : DbContext

{

public DataContext():base ("connection1")

{

//

// TODO: Add constructor logic here

//

}

  

    public DbSet<course> courses { get; set; }

}

DataContext class inherit from DbContext class which is inside in System.Data.Entity namespace. Learn How to download it. 

Here we pass ConnectionString in base class constructor as a parameter. Also add Course table in DataContext using DbSet. 

Step-3 : Add a new webform, which named as "getItem.aspx". Also add a gridView control onit from toolBox.

Step-4 : Add this code in codebehind page.

using System;

using System.Collections.Generic;

using System.Linq;

using System.Web;

using System.Web.UI;

using System.Web.UI.WebControls;

public partial class getitem : System.Web.UI.Page

{

    DataContext dc = new DataContext();

    protected void Page_Load(object sender, EventArgs e)

    {

        if (!Page.IsPostBack)

        {

            bindgrid();

        }

    }

    private void bindgrid()

    {

        var item = from d in dc.courses

                   select d;

        GridView1.DataSource = item.ToList();

        GridView1.DataBind();

        

    }

}

Code Generate the following output

How to download EntityFramework.dll file, Add System.Data.Entity namespace

This namespace take some classes of entity framework for accessing data. In this article we will learn how to add System.Data.Entity namespace because Entity framework.dll missing by default. So first to add entityframework.dll file as a reference.
How to download
Step-1 : Select Package Manager console from Tool menu.

Step-2 : Write
PM> Install-package entityframework

Note : Internet connection must.
Now, Using this method you can download latest version of entityframework.

How to populate comboBox in windows form c#

ComboBox is a collection, in which we can take Text as well as value of the text. After array, developer designed comboBox or DropDownList because array contains one attribute of object, its does not take two value at a time. Suppose you want to store employee name and age into memory. Where we would not use array. Here we will bind the comboBox in different manner like
Method-1 : Bind ComboBox from dataset and datatable using ADO.NET
Method-2 : Bind ComboBox using Entity framework.

Method-1

I have a database table like Students, which take some value. The Data View of table, which is stored in database.

C# Code for binding ComboBox (Binding from DataReader)

using System;
using WindowsFormsApplication10;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.Entity;
using System.Data.SqlClient;

namespace WindowsFormsApplication10
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }

        private void Form1_Load(object sender, EventArgs e)
        {
           
            SqlConnection con = new SqlConnection();
            con.ConnectionString = "Data Source=(LocalDB)\\v11.0; Initial Catalog=STUDENTer; Integrated Security=true";
            con.Open();
            SqlCommand cmd = new SqlCommand();
            cmd.CommandText = "select * from Students";
            cmd.Connection = con;
            SqlDataReader rd = cmd.ExecuteReader();
            while (rd.Read ())
            {
                string name = rd.GetString(1);
                comboBox1.Items.Add(name);

            }

        }
    }
}

Binding from DataTable

 private void Form1_Load(object sender, EventArgs e)
        {
           
            SqlConnection con = new SqlConnection();
            con.ConnectionString = "Data Source=(LocalDB)\\v11.0; Initial Catalog=STUDENTer; Integrated Security=true";
            con.Open();
            SqlCommand cmd = new SqlCommand();
            cmd.CommandText = "select * from Students";
            cmd.Connection = con;
            SqlDataReader rd = cmd.ExecuteReader();
            DataTable dt = new DataTable();
            dt.Load(rd);
            comboBox1.DataSource = dt;
            comboBox1.DisplayMember = "name";

        }

Binding from DataSet

 private void Form1_Load(object sender, EventArgs e)
        {
           
            SqlConnection con = new SqlConnection();
            con.ConnectionString = "Data Source=(LocalDB)\\v11.0; Initial Catalog=STUDENTer; Integrated Security=true";
            con.Open();
            SqlCommand cmd = new SqlCommand();
            cmd.CommandText = "select * from Students";
            cmd.Connection = con;
            SqlDataAdapter da = new SqlDataAdapter(cmd);
            DataSet ds = new DataSet();
            da.Fill(ds);
            comboBox1.DataSource = ds.Tables[0];
            comboBox1.DisplayMember = "name";

        }

Method-2

If you want to bind combobox using entity framework or you can say strong model binding, just follow my steps.
Step-1 : One class, which name as "Student.cs", which works as database table.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace WindowsFormsApplication10
{
   public class Student
    {
        public int Id { get; set; }
        public string name { get; set; }
    }
}
In this class we are taking two member first is Id and second is name.
Step-2 : Take another class, which name as "DataContext", which is used for creating database also used for inserting table in it. Look like

using System;
using System.Collections.Generic;
using System.Data.Entity;
using System.Linq;
using System.Text;

namespace WindowsFormsApplication10
{
    class DataContext: DbContext
    {
        public DataContext() :
            base("connectionstring1")
        {
           

        }
        public DbSet<Student> Students { get; set; }

    }
}

In this code, DataContext class inherited from DbContext class. Base class constructor create database using conectionstring, which is defined in App.Config file. like

<connectionStrings>

    <add name="connectionstring1" providerName="System.Data.SqlClient" connectionString="Data Source=(LocalDB)\v11.0; Initial Catalog=STUDENTer; Integrated Security=true" />

  </connectionStrings>

Step-3 : Add ComboBox control on form
Step-4 : Copy this code and paste into your form load event
private void Form1_Load(object sender, EventArgs e)
        {
            DataContext dc=new DataContext();
            var stu = from student in dc.Students
                      select new
                      {
                         student.name
                      };
            comboBox1.DataSource = stu.ToList();
            comboBox1.DisplayMember = "name";

        }

Code Generate the following output