Types of Secondary XML Indexes in SQL Server

These are non-clustered index of the primary XML index. There must be a primary xml index before each secondary xml index. Following are some types of secondary xml indexes:

Path Indexes

The path index is built on the path value columns of the primary XML indexes. This index improves the performance of queries that use paths and values to select data.

For example, if you execute a query that checks for the existence of a product model ID using an XQuery expression as /PD:ProductDescription/@ProductModelID[.=”19”], you can create a path secondary index on the CatalogDescription column of the ProductModel table. In this path index, you can use the primary index created previously.

The following statement creates a Path index on the CatalogDescription column:

CREATE XML INDEX PIdx_ProductModel_CatalogDescription_PATH ON Production.ProductModel (CatalogDesctiption)USING XML INDEX PXML_ProductModel_CatalogDescription FOR PATH

The preceding code create a path index, Pldx_ProductModel_CatalogDesctiption_PATH

Value Indexes

The value indexes contain the same items as path indexes but in the reverse order. It contains the value of the column first and then the path id. This index improves the performance of queries that use paths to select data.

For example, if you execute a query that checks the existence of a node in an XQuery expression such as//Item@ProductID=”1”], you can create a value secondary index by using the primary index created previously.

The following statement creates a value index on the CatalogDesctiption column:

CREATE XML INDEX PIdx_ProductModel_CatalogDesctiption_VALUE ON Production.ProductModel (CatalogDesctiption)
USING XML INDEX PXML_ProductModel_CatalogDescription
FOR VALUE

The preceding code creates a value index,
PIdx_ProductModel_CatalogDescription_VALUE on the CatalogDescription column of the table.

Property Indexes

The property index contains the primary key of the base table, path id, and the clause columns of primary XML indexes. This index improves the performance of queries that use paths to select data.

For example, if you execute a query that returns a value of the node in an XQuery expression, such as /ItemList/Item/@ProductID)[1], you can create a property secondary index on the CatalogDescription column of the ProductModel table by using the following statement:

CREATE XML INDEX PIdx_ProductModel_CatalogDescription_PROPERTY ON Production.ProductModel (CatalogDescription)
USING XML INDEX PXML_ProductModel_CatalogDescription FOR PROPERTY

The preceding code creates a property index, PIdx_ProductModel_CatalogDescription_PROPERTY, on the CatalogDescription column of the table.

You need to consider the following guidelines while creating an XML index:

• XML indexes can be created only on XML columns.
• XML indexes only support indexing a single XML column.
• XML indexes can only be added to tables, views, table-valued variables with XML column or XML variables.
• XML indexes created on a table do not allow you to modify the primary key. To do so, you first need to drop all the XML indexes on the table.

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