Types of XML Indexes used in SQL Server: Speed up Execution

When a query is based on an XML column, the query processor needs to parse the XML data each time the query is executed. In SQL Server, and XML data value can be of a maximum of two gigabytes (GB).

Therefore, the XML values can be very large and the server might take time to generate the result set. To speed up the execution of the query based on the XML data type, SQL Server allows you to create an index that is based on columns storing XML data values. Such indexes are called XML indexes.

Primary XML Index

This is a clustered B-Tree representation of the nodes in the XML data. When an index is created on a column with the XML data type, an entry will be created for all the nodes in the XML data. Therefore, the index creates several rows of data for each XML value in the column.

You can create XML indexes on XML columns by using the CREATE PRIMARY XML INDEX and CREATE XML INDEX T-SQL commands. For example, the ProductModel table contains the CatalogDescription column that stores XML values. You can create a primary XML index on this column by using the following statement:

CREATE PRIMARY XML INDEX PXML_ProductModel_CatalogDesctiption ON Production.ProductModel (CatalogDescription)

The preceding statement will create an index for all the nodes in the XML data stored in the CatalogDescription column.

Secondary XML Index

This is a non-clustered index of the primary XML index. A primary XML index must exist before any secondary index can be created. After you have created the primary XML index, an additional three kinds of secondary XML indexes can be defined on the table. The secondary XML indexes assist in the XQuery processing.

The three types of secondary XML indexes are:

• Path indexes
• Value indexes
• Property indexes

Guidelines for Creating Indexes in SQL Server

Database Developer need to consider listed guidelines while creating indexes on a table. These guidelines explains about some special features or we can say requirements needed to create an index.

Earlier article was about to create an index with syntax and description of parameters. Each parameter have its own speciality discussed in that article, here we noticed some guidelines that may use in create in index:

• Create clustered indexes on columns that have unique or not null values.
• Do not create an index that is not used frequently. You require time and resources to maintain indexes.
• Create a clustered index before creating a nonclustered index. A clustered index changes the order of rows. A nonclustered index would need to be rebuilt if it is built before a clustered index.
• Create nonclustered indexes on all columns that are frequently used in predicates and join conditions in queries.

Consider an example of an organization that maintains employee details in the Employee table. You can create a clustered index on the EmployeeID attribute of the Employee table by using the following code:

CREATE CLUSTERED INDEX IX_EmployeeID
ON Employee (EmployeeID)
WITH FILLFACTOR = 10

In the preceding code, the FILLFACTOR value of 10 has been specified to reserve a percentage of free space on each data page of the index to accommodate future expansion.

The following example creates a nonclustered index on the ManagerID attribute of the Employee table:

CREATE NONCLUSTERED INDEX IDX_Employee_ManagerID
ON Employee (ManagerID)

When a PRIMARY KEY or UNIQUE constraint is created on a table, an index is created automatically with the same name as the constraint.

How to Create Index using sql Query: Sql Server

Database programmer should create indexes on the most frequently queried column in a table. However, at times, you might need to create an index based on a combination of one or more columns. An index based on one or more columns is called a composite index. A composite index can be based on a maximum of 16 columns. However, you need to consider that indexes with less number of columns use less disk space and involve fewer resources when compared to indexes based on more columns.

To create an index you can use the CREATE INDEX statement. The syntax of the CREATE INDEX statement is:

CRATE [UNIQUE] [CLUSTERED | NONCLUSTERED] INDEX index_name
ON [{database_name.[schema_name]. | schema_name.}]
{table_or_view_name} (column [ASC | DESC] [, …n])
[INCLUDE (column_name [, …n])]
[WITH (<relational_index_option>[, …n])]
[ON {partition_cheme_name (column_name [, …n]) | filegroup_name |DEFAULT) ]
<relation_index_option> : : =
{PAD_INDEX = {ON | OFF}
| FILLFACTOR = fillfactor
| SORT_IN_TEMPDB = {ON | OFF}
| IGNORE_DUP_KEY = {ON | OFF}
| STASTISTICS_NO_RCOMPUTE = {ON | OFF}
| DROP_EXISTING = {ON | OFF}
| ONLINE = {ON | OFF}

Where,

• UNIQUE crates an index where each row should contain a different index value. CLUSTERED specifies a clustered index where data is sorted on the index attribute. NONCLUSTERED specifies a nonclustered index that organizes data logically. The data is not sorted physically.
• Index_name specifies the name of the index.
• Table_name specifies the name of the table that contains the attributes on which the index is to be created.
• Column specifies the column or columns on which the index will be created.
• Column_name specifies the name of the column or columns on which the index would be created.
• ON partition_scheme_name ( column_name ) specifies the partition scheme the specifies the filegroups in which the pattitioned index will be mapped.
• ON filegroup_name specifies the filegroup on which index is created.
• ON DEFAULT specifies that the specified index will be created on the default filegroup.
• PAD_INDEX = { ON | OFF } specifies the index padding, which is OFF, by default.
• FILLFACTOR = 1 to 100 specifies a percentage that indicates how full the leaf level of each index page should become during index creation or rebuild. The default value is 0.
• SORT_IN_TEMPDB = { ON | OFF } specifies about storing temporary sort results in the tempdb.
• IGNORE_DUP_KEY = { ON | OFF } specifies whether a duplicate key value can be inserted or not…
• STATISTICS_NO_RECOMPUTE = { ON | OFF } specifies about recomputing the distribution statistics.
• DROP_EXISTING = { ON | OFF } specifies that the pre-existing clustered, nonclustered, or XML index is dropped and rebuilt.
• ONLINE = { ON | OFF } checks whether the underlying tables and associated indexes are available to query and modify the data during the index operation.

Developer can create online indexes only in the SQL Server 2005 Enterprise Edition.

Non-clustered index in sql
Guidelines to create indexes

How to set primary key in entity framework

Suppose you have a class product, which is work as a table in entity framework. In this class you want to create a primary key. This features provides by System.ComponentModel.DataAnnotations namespace. According to msdn article this namespace provides attributes classes that are used to define metadata. In this example we are creating one or more columns , which is uniquely identified in the table. Suppose, in this table you want to set ProductId as a primary key then simple write 'Key' in brackets before fields. Lets a class

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

/// <summary>
/// Summary description for Product
/// </summary>
public class Product
{
    [Key]
    public int ProductId { get; set; } // Primary Key attribute
    public string  productName { get; set; }
}

How to create database table in entity framework windows form c#

I am very happy from entity framework, thanks to Microsoft. Through easy steps you can easily design database table in entity framework. These easy steps are:
Step-1 : Add entity framework package from package manager console.

Tools  --> Library Package Manager --> Package Manager Console

PM> install-package entityframework

Step-2 : First to create a class, which named as 'student'. This class work as database table in entity framework. like that

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; }
    }
}

Step-3 : Create a connection string 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-4 :  Create an another class, which named as 'DataContext'. This class is inherited from DbContext base class and add student table in it.

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; }

    }
}

Here DataContext class constructor call our base class constructor and pass the connection string , which is define in app.config file. Also add the student table in STUDENTer database.

Step-4 : Add new window form in our project , also create object of DataContext class in form constructor.

public Form1()
        {
            InitializeComponent();
            DataContext dc = new DataContext();
       
        }
Step-5 :  Create break point after DataContext Object.

Step-6 : Run your application

How to get Visitor's IP in ASP.NET

In this article , we will learn how to get visitor's machine IP address and where we use it. Normally this types of query is generated for ptc sites because ptc sites take visitor's ip address in its database table then no buddy cheat with them. This article is very useful, where you establish intranet, through this you can easily determine call request. Here we have two parameters in ServerVariables that is 

HTTP_X_FORWARDED_FOR

REMOTE_ADDR 

In this article both keep different meaning, such as HTTP_X_FORWARDED_FOR parameter used where visitor behind the proxy server, now through this parameter you can easily get 

proxy server ip + client machine ip

Note : Suppose your visitor visit your site through proxy server ip address then you would not get actual ip address of the visitor , through this parameter you can get both (proxy + machine address) address.

REMOTE_ADDR parameter get the router or proxy server ip. Now take an simple example , but in this example we always get 127.0.0.1 because server and client machine are same.

 

Example 

Step-1 : Add Label and Button on webform

Step-2 : Raise click event 

Step-3 : Copy this code and paste into your click handler

Source 

<form id="form1" runat="server">

    <div>

    

        <asp:Label ID="Label1" runat="server"></asp:Label>

        <br />

    

    </div>

    <asp:Button ID="Button1" runat="server" onclick="Button1_Click" 

        Text="Get Ip Address" />

    </form>

Code Behind

 protected void Button1_Click(object sender, EventArgs e)

    {

        string ip;

        ip = Request.ServerVariables["HTTP_X_FORWARDED_FOR"];

        if (ip == "" || ip == null)

            ip = Request.ServerVariables["REMOTE_ADDR"];

        Label1.Text = ip;

    }

Code Generate the following output

How to get cell value in selected row of DataGridView in windows form c#

If you want to get cell value from DataGridview then first to bind grid with data table with any data source. Now, use this code to retrieve the cell value.

Binding Data 

 private void binddata()
        {
            con.Open();
            cmd.CommandText = "select * from [student_record]";
            cmd.Connection = con;
            SqlDataAdapter da = new SqlDataAdapter(cmd);
            DataSet ds = new DataSet();
            da.Fill(ds);
            dataGridView1.DataSource = ds;
            dataGridView1.DataMember = ds.Tables[0].ToString ();
           
        }

Get cell value on message box

  private void button1_Click(object sender, EventArgs e)
        {
            string name = dataGridView1.SelectedRows[0].Cells[1].Value.ToString();
            MessageBox.Show(name);

        }

Code generate the following output