Hello All, We are going to start new batch from next week. message/call or mail us for more details.

19 December 2012

SQL Server 2005/2008 Table Indexing


1. Overview

Human needs have increased tremendously. Now people are doing much more composite tasks than ever before. The society has become very complex, a person has to work with huge amount of information every day. In order to work with the enormous information, we must have a system where we can store, manipulate and share the information all over the world. It is one of the core reasons for introducing Database Management Systems (DBMS) as well as Relational Database Management Systems (RDBMS) now-a-days.
So, one thing is clear to us that we store and manipulate data / information into a database, where the database contains various types of tables for storing various types of data / information.
Developers can create tables of a database; it would be possible to stop at this point and just start working with our data from here. However this would not be one of our best choices, but why? Because it is not really a good idea for better performances, let’s take an example:
“A library has a huge collection of books, files, etc... A student requests the librarian for a book of Microsoft SQL Server 2008, if we think without an index the librarian had to find this without any help s/he has to search one by one! Which must be time consuming; so with a proper arrangement, that is with the help of an index, it very much easier and faster to find out the desired one.”
There are many ways to create an index, but first of all we need to know what is an index is and how it is held within SQL Server.

2. What is an Index?

A SQL table explanation is not good enough for getting the desired data very quickly or sorting the data in a specific order. What we actually need for doing this is some sort of cross reference facilities where for certain columns of information within a table, it should be possible to get whole records of information quickly. But if we consider a huge amount of data in a table, we need some sort of cross reference to get to the data very quickly. This is where an index within SQL Server comes in.
So an index can be defined as:
  • An index is an on-disk structure associated with a table or views that speed retrieval of rows from the table or view. An index contains keys built from one or more columns in the table or view”. These keys are stored in a structure (B-tree) that enables SQL Server to find the row or rows associated with the key values quickly and efficiently.
  • “An index is a database object created and maintained by DBMS. It is essentially a list of the contents of a column or group of columns. Indexes are ordered so that extremely first search can be computed through them to find data.” 

3. Why Use an Index?

Use of SQL server indexes provide many facilities such as:
  • Rapid access of information
  • Efficient access of information
  • Enforcement of uniqueness constraints
Correct use of indexes can make the difference between a top performing database with high customer satisfaction and a non-performing database with low customer satisfaction.

4. Types of Indexes

SQL Server has two major types of indexes:
  1. Clustered 
  2. Non-Clustered
The index type refers to the way the index is stored internally by SQL Server. So a table or view can contain the two types of indexes.

4.1 Clustered

An index defined as being clustered, defines the physical order that the data in a table is stored. Only one cluster can be defined per table. So it can be defined as:
  • Clustered indexes sort and store the data rows in the table or view based on their key values. These are the columns included in the index definition. There can be only one clustered index per table, because the data rows themselves can be sorted in only one order.
  • The only time the data rows in a table are stored in sorted order is when the table contains a clustered index. When a table has a clustered index, the table is called a clustered table. If a table has no clustered index, its data rows are stored in an unordered structure called a heap.

4.2 Non-Clustered

As a non-clustered index is stored in a separate structure to the base table, it is possible to create the non-clustered index on a different file group to the base table. So it can be defined as:
  • Non-Clustered indexes have a structure separate from the data rows. A non-clustered index contains the non-clustered index key values and each key value entry has a pointer to the data row that contains the key value.
  • The pointer from an index row in a non-clustered index to a data row is called a row locator. The structure of the row locator depends on whether the data pages are stored in a heap or a clustered table. For a heap, a row locator is a pointer to the row. For a clustered table, the row locator is the clustered index key.
  • You can add nonkey columns to the leaf level of the Non-Clustered index to by-pass existing index key limits, 900 bytes and 16 key columns, and execute fully covered, indexed, queries.
More information about indexes can be found at this link.
Both clustered and non-clustered indexes can be unique. This means no two rows can have the same value for the index key. Otherwise, the index is not unique and multiple rows can share the same key value.
Note: Indexes are automatically maintained for a table or view whenever the table data is modified.

5. Uniqueness

An index can be defined either as unique or non-unique. A unique index ensures that the data contained within the unique index columns appear only once within the table, including “NULL”. A unique index is commonly implemented to support the constraints.
SQL Server automatically enforces the uniqueness of the columns contained within a unique index. If an attempt is made to INSERT a value/data that already exists in the table, then an error will be generated by the SQL Server and finally the attempt to INSERT the data will fail.
A non-unique index is also applicable as there can be duplicate data; a non-unique index has more overhead than a unique index when retrieving data.

6. Creating a Table

What is a table? The foundation of every Relational Database Management System is a database object called table. Every database consists of one or more tables, which stores the database’s data/information. Each table has its own unique name and consists of columns and rows.

Syntax of Creating Table

CREATE TABLE 
    [ database_name.[ owner ] . | owner. ] table_name 
    ( { < column_definition > 
        | column_name AS computed_column_expression 
        | < table_constraint > } [ ,...n ] 
    )

In this article, we will not discuss database tables. I hope that every one knows the basic concepts about database tables.

7. Creating an Index

As we discuss what an index is, why we use an index, types of indexes, etc, we are going to discuss how to create an index. There are two ways of creating an index they are as follows:
  • Creating an Index using SQL Server Table Designer
  • Creating an Index using Transact-SQL
7.1 Creating an Index using by SQL Server Table Designer
SQL Server Table designer is an excellent feature to manage various tables designing creation, modification, indexing and so much more. Here you need to follow the step(s) below to create an index using the SQL Table designer.

Step(s)

  1. Open your SQL Server Enterprise Management Studio and choose your database for creating a table index. Now select your desired table and open the Table Design window. The following figure shows how to do this.
Image1
  1. Figure 1 (How to open SQL Table Designer)
  2. A window will appear where you will find all the available inputs for creating a Table. You just need to input the columns name, data type length and also you may set the columns to allow null value or not. Note that you can set a default value of a column. The following figure will show you how you can perform all of this:
  1. Figure 2 (How to set table columns, data type, length, etc.)
  2. Well, now you know how to create a table. Let's see how to set an index in a table. Select the column by clicking your mouse button, a new popup menu will appear, select/click the Indexes/Keys for opening a new window where you can configure the columns for indexing. The following figure will show how to set an index.
    Figure 2.1 (A) (How to configure an Index)
    Image 2.1 B
    Figure 2.1 (B) (How to Configure an Index)
  3. Now you just select the column from the general properties as shown in figure 3, and add the specify column short order of the index.
    Figure 4 (How to set a Clustered Index)
    You can configure the index as clustered or non-clustered from the Create as clustered properties. Figure 4 shows the configuration of clustered or non-clustered indexes by default.
So we discussed how to create an index by using SQL Server Table Designer. Let’s try to create an index by using Transact-SQL.
7.2 Creating an Index – Transact-SQL
If we want to create an index by using Transact – SQL , we must know the columns detail for index creation. A sample syntax is given below.
Syntax
CREATE INDEX <index_type> <index_name> ON <table_name> (
<column_name1> <index_order>,
<column_name2> <index_order>,
)  
 
CREATE UNIQUE INDEX <index_type> <index_name> ON <table_name> (
<column_name1> <index_order>,
<column_name2> <index_order>,
)


7.3 Drop an Index
Removes one or more indexes from the current database.
The DROP INDEX statement does not apply to indexes created by defining PRIMARY KEY or UNIQUE constraints (created by using the PRIMARY KEY or UNIQUE options of either the CREATE TABLE or ALTER TABLEstatements, respectively). For more information about PRIMARY or UNIQUE KEY constraints, see "CREATE TABLE" or "ALTER TABLE" in this volume.
Syntax
DROP INDEX 'table.index | view.index' [ ,...n ]

8. Index and Statistics

When retrieving data, SQL Server obviously has to make some decisions as to the best way to get to data and return it to the query requesting it. So SQL Server has some basic information to make sensible and accurate choices. This is where statistics comes in. SQL Server keeps statistics on each column, which are updated over a period of time and over a number of inserts or modifications.
More information about indexes and statistics can be found at this link.

9. Summary

In this article, I tried to cover SQL table indexing. I discussed why we use indexes and how they are used to quick and efficiently retrieve the data stored in the table. I also discussed various types of indexes, how to create / drop index, index uniqueness and index statistics as well.
I hope that this article will be helpful to you. Enjoy!



3 November 2012

SQL SERVER - 2008 - Configure Database Mail - Send Email From SQL Database

In this article, I discuss about the Database Mail which is used to send the email using SQL Server. Database mail is the replacement for SQLMail with many enhancements. So one should stop using SQLMail and upgrade to the Database Mail.
In order to send mail using Database Mail in SQL Server, there are 3 basic steps that need to be carried out:
  1. Create Profile and Account
  2. Configure Email
  3. Send Email
  4. Step 1: Create Profile and Account

    You need to create a profile and account using the Configure Database Mail Wizard which can be accessed from the Configure Database Mail context menu of the Database Mail node in Management Node.
    This wizard is used to manage accounts, profiles, and Database Mail global settings which are shown below:











    Step 2: Configure Email

    After the Account and the Profile are created successfully, we need to configure the Database Mail. To configure it, we need to enable the Database Mail XPs parameter through the sp_configure stored procedure, as shown here:

    sp_CONFIGURE 'show advanced', 1 GO RECONFIGURE GO sp_CONFIGURE _
     'Database Mail XPs', 1 GO RECONFIGURE GO
     


    Step 3: Send Email

    After all configurations are done, we are now ready to send an email. To send mail, we need to execute a stored procedure sp_send_dbmail and provide the required parameters as shown below:

    USE msdb GO EXEC sp_send_dbmail @profile_name='PinalProfile', _
    @recipients='test@Example.com', @subject='Test message', _
    @body='This is the body of the test message. _
    Congrats Database Mail Received By you Successfully.'
     


    After all validations of the parameters entered are done, certain stored procedures are executed and the mail is queued by Service Broker. Read more at SQL SERVER - Introduction to Service Broker.
    Database Mail keeps copies of outgoing e-mail messages and displays them in the sysmail_allitems, sysmail_sentitems, sysmail_unsentitems, sysmail_faileditems. The status of the mail sent can be seen in sysmail_mailitems table. When the mail is sent successfully, the sent_status field of the sysmail_mailitems table is set to 1 which can again be seen in sysmail_sentitems table. The mails that have failed will have the sent_status field value to 2 and those are unsent will have value 3. The log can be checked in sysmail_log table as shown below:

    SELECT * FROM sysmail_mailitems GO SELECT * FROM sysmail_log GO
     


    Status can be verified using sysmail_sentitems table.

     

8 October 2012

Today i am explaining here how to drag and drop rows from source gridview to destination gridview in Asp.Net using jQuery UI sortable plugin.

HTML Markup
The HTML markup is simple and it contains two ASP.Net GridViews gvSource is the source GridView and the other gvDest is the Destination GridView.
<asp:GridView ID="gvSource" runat="server" CssClass="drag_drop_grid GridSrc" AutoGenerateColumns="false">
    <Columns>
        <asp:BoundField DataField="Item" HeaderText="Item"/>
        <asp:BoundField DataField="Price" HeaderText="Price"/>
    </Columns>
</asp:GridView>
<hr />
<asp:GridView ID="gvDest" runat="server" CssClass="drag_drop_grid GridDest" AutoGenerateColumns="false">
    <Columns>
        <asp:BoundField DataField="Item" HeaderText="Item" />
        <asp:BoundField DataField="Price" HeaderText="Price"/>
    </Columns>
</asp:GridView>
 

Binding the Source and the Destination GridViews
Here I am binding the source ASP.Net GridView with some shopping items and the destination GridView is bound with a dummy row.
 
protected void Page_Load(object sender, EventArgs e)
{
    if (!Page.IsPostBack)
    {
        DataTable dt = new DataTable();
        dt.Columns.AddRange(new DataColumn[2] { new DataColumn("Item"), new DataColumn("Price") });
        dt.Rows.Add("Shirt", 450);
        dt.Rows.Add("Jeans", 3200);
        dt.Rows.Add("Trousers", 1900);
        dt.Rows.Add("Tie", 185);
        dt.Rows.Add("Cap", 100);
        dt.Rows.Add("Hat", 120);
        dt.Rows.Add("Scarf", 290);
        dt.Rows.Add("Belt", 150);
        gvSource.UseAccessibleHeader = true;
        gvSource.DataSource = dt;
        gvSource.DataBind();
 
        dt.Rows.Clear();
        dt.Rows.Add();
        gvDest.DataSource = dt;
        gvDest.DataBind();
    }
}
 
Implementing Drag and Drop Functionality using jQuery
I am using the jQuery UI Sortable Plugin to implement the Drag and Drop functionality between the source and the destination GridViews.
 
<script src="http://ajax.aspnetcdn.com/ajax/jquery/jquery-1.8.0.js" type="text/javascript"></script>
<script src="http://ajax.aspnetcdn.com/ajax/jquery.ui/1.8.22/jquery-ui.js"></script>
<link rel="Stylesheet" href="http://ajax.aspnetcdn.com/ajax/jquery.ui/1.8.10/themes/redmond/jquery-ui.css" />
<script type="text/javascript">
    $(function () {
        $(".drag_drop_grid").sortable({
            items: 'tr:not(tr:first-child)',
            cursor: 'crosshair',
            connectWith: '.drag_drop_grid',
            axis: 'y',
            dropOnEmpty: true,
            receive: function (e, ui) {
                $(this).find("tbody").append(ui.item);
            }
        });
        $("[id*=gvDest] tr:not(tr:first-child)").remove();
    });
</script>
 
 
Below are the CSS styles for the source and the destination GridViews
<style type="text/css">
    .GridSrc td
    {
        background-color: #A1DCF2;
        color: black;
        font-size: 10pt;
        font-family:Arial;
        line-height: 200%;
        cursor: pointer;
        width:100px
    }
    .GridSrc th
    {
        background-color: #3AC0F2;
        color: White;
        font-family:Arial;
        font-size: 10pt;
        line-height: 200%;
        width:100px;
    }
    .GridDest td
    {
        background-color: #eee !important;
        color: black;
        font-family:Arial;
        font-size: 10pt;
        line-height: 200%;
        cursor: pointer;
        width:100px
    }
    .GridDest th
    {
        background-color: #6C6C6C !important;
        color: White;
        font-family:Arial;
        font-size: 10pt;
        line-height: 200%;
        width:100px
    }
</style>
 
 
 

17 September 2012

Stop auto-fill in browsers for textbox


Today’s browsers like Chrome, Firefox, Internet Explorer and Safari has functionality of auto complete values in TextBoxes. If you have enabled this features in your browser, then each and every time when you start to enter value in TextBox you get a drop down of prefilled values in that TextBox. This feature of browser can be disabled by the programming for a specific web form like payment form and other confidential information form of a web application.
In chrome browser, we can enable auto-fill as shown below:
Suppose we have a below form for online payment of product by credit card or debit card then it is mandatory to stop auto complete functionality of browser so that browser doesn’t save the confidential information of a customer’s credit card or debit card.
We can turn off auto-fill for our complete form by setting autocomplete attribute value to off as shown below:
<form id="Form1" method="post" runat="server" autocomplete="off">
</form>
We can also turn off auto-fill for a particular TextBox by setting autocomplete attribute value to off as shown below:
<asp:TextBox Runat="server" ID="txtConfidential" autocomplete="off">
</form>
We can also do this from code behind also like as:
txtConfidential.Attributes.Add("autocomplete", "off");










Maintain Scroll Position on Postback in ASP.NET 2.0


This article shows how to allows pages to automatically maintain the current scroll position across postbacks.
The MaintainScrollPositionOnPostback page directive attribute allows to do that.
This feature is useful for large pages where scrolling is necessary to view input controls down further on the page.
There are three ways of applying the property to a web page.
  1. You can set it programmatically 
    Page.MaintainScrollPositionOnPostBack = true;
  2. In the page declaration 
    <%@ Page MaintainScrollPositionOnPostback="true" %>
  3. Or in the web.configs <system.web> section. 
    <pages maintainScrollPositionOnPostBack="true" />
This feature is an absolute must-have on large web pages built for postback scenarios.
A simple but very useful feature.
Smartnavigation = true  implemented the same feature in 1.1 framework 
SmartNavigation only had "issues" and it only worked in IE but the new MaintainScrollPositionOnPostback apparently works in most common browsers.