Code Simplified – Viral Sarvaiya

Code Simplified – Viral Sarvaiya, Web Developer Friends, dot net Developer, Sql Server Developer

Archive for October, 2009

Export GridView Data into CSVFile In Asp.net

Posted by Viral Sarvaiya on October 30, 2009


Steps.

1) Create Simple Web Application.

2) put the below control in to the page (.aspx)

2.1) GridView (id= GridView1)

2.2) Button (id=button1 and Text =Create CSV File)

3) assign data source to GridView

4) on the button_click event put the following code.


Protected Sub Button1_Click(ByVal sender As Object, ByVal e As EventArgs)
Try
'Create CSV file
Dim objSw As New StreamWriter(Server.MapPath("~/demo.csv"))
'get table from GridView1
Dim objDt As DataTable = DirectCast(GridView1.DataSource, DataSet).Tables(0)
'Get No Of Column in GridView
Dim NoOfColumn As Integer = objDt.Columns.Count
'Create Header
For i As Integer = 0 To NoOfColumn - 1
objSw.Write(objDt.Columns(i))
'check not last column
If i < NoOfColumn - 1 Then
objSw.Write(",")
End If
Next
objSw.Write(objSw.NewLine)
'Create Data
For Each dr As DataRow In objDt.Rows
For i As Integer = 0 To NoOfColumn - 1
objSw.Write(dr(i).ToString())

If i < NoOfColumn - 1 Then
objSw.Write(",")
End If
Next
objSw.Write(objSw.NewLine)
Next
objSw.Close()
Catch ex As Exception
Response.Write("Can Not Generate CSV File")
End Try
End Sub

Enjoy Coding….

Posted in ASP.NET | Tagged: , , , , , | 2 Comments »

How to Get Mouse Coordinates with Javascript

Posted by Viral Sarvaiya on October 29, 2009


This will find X and Y coordinates of the mouse where the mouse moves in browser.


<html>
<head>
<title>Get Mouse Coordinates</title>
<script language="javascript">
var divObj;

document.onmousemove=getMouseCoordinates;

function getMouseCoordinates(event)
{
ev = event || window.event;
divObj.innerHTML = "Mouse X:"+ev.pageX + " Mouse Y:"+ev.pageY;
}

function loadDiv()
{
divObj = document.getElementById("mouseCoord");
}
</script>
</head>

<body onLoad="loadDiv()">
<div id="mouseCoord">Mouse Coordinates position will be displayed here.
</div>
</body>
</html>

Posted in Javascript | Tagged: , , | Leave a Comment »

Get CheckBoxList values using Javascript

Posted by Viral Sarvaiya on October 28, 2009


I have one CheckBoxList control that binds values at runtime from the database, and when I click on a button from the page, I want to get the values (Database Primary Key) from the CheckBoxList, but for the checked checkboxes only.

Here is the code, what I have achieved so far. This code works fine with IE 7, but I am not sure with the FireFox.

ASPX page:


<asp:CheckBoxList ID="CheckBoxList1" runat="server" OnDataBound="CheckBoxList1_DataBound" >
<asp:ListItem Value="First1" Text="First" ></asp:ListItem>
<asp:ListItem Value="second2" Text="Second"></asp:ListItem>
</asp:CheckBoxList>
<asp:Button ID="Button1" runat="server" Text="Button" />

<script language="javascript" type="text/javascript">
function CheckItem()
{
var tbl = document.getElementById('<%= CheckBoxList1.ClientID %>').childNodes[0];
for(var i=0; i<tbl.childNodes.length;i++)
{
for(var k=0; k<tbl.childNodes[i].childNodes.length; k++)
{
if(tbl.childNodes[i].childNodes[k].nodeName == "TD")
{
var currentTD = tbl.childNodes[i].childNodes[k];
for(var j=0; j<currentTD.childNodes.length; j++)
{
if(currentTD.childNodes[j].nodeName == "SPAN")
{
var currentSpan = currentTD.childNodes[j];
for(var l=0; l<currentSpan.childNodes.length; l++)
{
if(currentSpan.childNodes[l].nodeName == "INPUT" && currentSpan.childNodes[l].type == "checkbox")
{
var currentChkBox = currentSpan.childNodes[l];
if(currentChkBox.checked)
{
alert(currentSpan.alt);
}
}
}
}
}
}
}
}
}
</script>

Code Behind:


Button1.Attributes.Add("onclick", "javascript:CheckItem();");
CheckBoxList1.DataSource = <Your Dataset>;
CheckBoxList1.DataTextField = "PersonName";
CheckBoxList1.DataValueField = "PersonID";
CheckBoxList1.DataBind();
protected void CheckBoxList1_DataBound(object sender, EventArgs e)
{
CheckBoxList chkList = (CheckBoxList)(sender);
foreach (ListItem item in chkList.Items)
{
item.Attributes.Add("alt", item.Value);
}
}

other way is as below….

this is run in IE as well as FF


function CheckItem() {

var tbl = document.getElementById('<%= CheckBoxList1.ClientID %>');

var chkspecialCount = tbl.getElementsByTagName("input");

var chkspeciallbls = tbl.getElementsByTagName("label");

for (var i = 0; i < chkspecialCount.length; i++) {

if (chkspecialCount[i].checked == true) {

var str = chkspeciallbls[i].innerHTML;

alert(str);

}

}

}

Enjoy Coding….

Reference

http://hspinfo.wordpress.com/2008/08/14/get-checkboxlist-values-using-javascript/

Posted in ASP.NET, Javascript | Tagged: , , , | 2 Comments »

Show Div tag in middle of the page with javascript

Posted by Viral Sarvaiya on October 27, 2009


take a element of Div


<div id="contentmsg" style="position: absolute; right:25%; width: 100px; height:100px;visibility: hidden;">
<img src="images/loading.gif" width="50px" height="50px" />
</div>

suppose we have a dropdown and according to onchange() event this div is visible or hide,


<asp:DropDownList ID="ddlparavalue" runat="server"></asp:DropDownList>

in the server side bind the dropdownlist dynamically and add the attribultes as below


ddlparavalue.Attributes.Add("onchange", "javascript:void HideOrVisibleDDL();")

in the head of the html section write the following code


<script language="javascript" type="text/javascript">
function HideOrVisibleDDL() {

var windowHeight = window.innerHeight ? window.innerHeight : document.documentElement.clientHeight ? document.documentElement.clientHeight : document.body.clientHeight;
var windowWidth = window.innerWidth ? window.innerWidth : document.documentElement.clientWidth ? document.documentElement.clientWidth : document.body.clientWidth;
var IpopTop = ((windowHeight - document.getElementById("contentmsg").offsetHeight) / 2);

document.getElementById("contentmsg").style.top = IpopTop + document.body.scrollTop;
document.getElementById("contentmsg").style.left = (document.body.clientWidth / 2) - 50;

document.getElementById("contentmsg").style.visibility = "visible";

}
</script>

in some case it is not working just becouse of the DOCType tag so please remove the DOCTYPE tag as like below


<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

so remove this tag.

enjoy coding….

Posted in ASP.NET, Javascript | Tagged: , , | 3 Comments »

Ajax Update panel in asp.net

Posted by Viral Sarvaiya on October 26, 2009


What ist the UpdatePanel in ASP.NET Ajax? The answer is easy: When adding an UpdatePanel-control to your page, you can achieve a partial-update of your page on a postback. Only the content in the UpdatePanel is refreshed, the other parts of the page remain unchanged. This topic explains, how you can setup an Ajax-enabled project and use the UpdatePanel control.

for more details click below….

http://www.asp.net/Ajax/Documentation/Live/tutorials/IntroductionUpdatePanel.aspx

http://www.codegod.de/WebAppCodeGod/UpdatePanel-tutorial-ASP-NET-AJAX-AID281.aspx

Posted in ASP.NET | Tagged: , , | Leave a Comment »

Script Manager in Asp.net Ajax

Posted by Viral Sarvaiya on October 26, 2009


The ScriptManager control manages client script for Microsoft ASP.NET AJAX pages. By default, the ScriptManager control registers the script for the Microsoft AJAX Library with the page. This enables client script to use the type system extensions and to support features such as partial-page rendering and Web-service calls.

for more details click below….

http://msdn.microsoft.com/en-us/magazine/cc163354.aspx

http://www.asp.net/Ajax/Documentation/Live/overview/ScriptManagerOverview.aspx

Posted in ASP.NET | Tagged: , , | Leave a Comment »

How to find out recently run queries in SQL Server 2005?

Posted by Viral Sarvaiya on October 20, 2009



Select

dmStats.last_execution_time as ‘Last Executed Time’,

dmText.text as ‘Executed Query’

from

sys.dm_exec_query_stats as dmStats

Cross apply

sys.dm_exec_sql_text(dmStats.sql_handle) as dmText

Order By

dmStats.last_execution_time desc

Posted in Sql Server | Tagged: , | Leave a Comment »

Debugging JavaScript Codes in VisualStudio.Net

Posted by Viral Sarvaiya on October 20, 2009


Follow these steps to enable script debugging in VS.NET

Tools->Internet Options…->Advanced->Disable Script Debugging [Un-check]

1.gif

2.gif

4.gif

When you’ve enabled Script Debugging, then in the IE go to,  View->Script Debugger->Open

5.gif

Once you clicked Open, the page reloads and activates the script debugger, you can set break-point and do F10 & F11.

Alternately, you can put a line like this in the java-script code where you need to break

debugger;

Example;

<div id="highlighter_826276">
<div>
<div><span style="margin-left: 0!important;"><code>function</code> <code>Debug()</code></span></div>
<div><span style="margin-left: 0!important;"><code>{ </code></span></div>
<div><code> </code><span style="margin-left: 28px!important;"><code>debugger;</code></span></div>
<div><code> </code><span style="margin-left: 28px!important;"><code>alert(</code><code>'Break at above statement'</code><code>); </code></span></div>
<div><span style="margin-left: 0!important;"><code>}</code></span></div>
<div>

Posted in ASP.NET, Javascript | Tagged: , , | Leave a Comment »

Data types in Sql Server2005

Posted by Viral Sarvaiya on October 13, 2009


Data type name Number of bytes Comments
Integer tinyint 1
smallint 2
int 4
bigint 8
Exact numeric decimal[p[,s]] 5-17
numeric[p[,s]] 5-17
Appropiate numeric float[(n)[ 8
real 4
Monetary smallmoney 4
money 8
Date and time smalldatetime 4
datetime 8
Characters char[(n)] 0-8000 non-Unicode
varchar[(n)] 0-8000 non-Unicode
varchar(max) 0-2 GB non-Unicode, 16 bytes pointer on row, ,preferred over text data type
text 0-2 GB non-Unicode, 16 bytes pointer or in row, obsolete, varchar(max) prefered
nchar[(n)] 0-8000 max 4000 unicode characters
nvarchar[(n)] 0-8000 max 4000 unicode characters
nvarchar(max) 0-2 GB 16 bytes pointer or in row, ,preferred over ntext data type
ntext 0-2 GB 16 bytes pointer, obsolete, nvarchar(max) prefered
Binary binary[(n)] 0-8000
varbinary[(n)] 0-8000
varbinary(max) 0-2 GB 16 bytes pointer or in row, ,preferred over image data type
Image image 0-2 GB 16 bytes pointer, obsolete, varbinary(max) prefered
Global identifier uniqueidentifier 16
XML xml 0-2GB 16 bytes pointer
Special bit 1 1 byte for every 8 bit columns
cursor 0-8
timestamp 8 one column per table
sysname 256
table
sql_variant 0-8016

Posted in Sql Server | Tagged: , , | Leave a Comment »

Index in sqlserver 2005

Posted by Viral Sarvaiya on October 12, 2009


Here we discuss about the clustered and non clustered indexex, The Query Optimizer, to create an index, To create a unique index, To create a clustered index, To create full-textindexes , To change index properties, To rename an index, to delete an index, To specify a fill factor for an index, To create an XML index and To delete XMLIndexes.

When data volumes increase, organizations are faced with problems relating to data retrieval and posting. They feel the need for a mechanism that will increase the speed of data access. An index, like the index of a book, enables the database retrieve and present data to the end user with ease. An index can be defined as a mechanism for providing fast access to table rows and for enforcing constraints.

An index can be created by selecting one or more columns in a table that is being searched. It is a kind of ‘on disk’ structure associated with the table or view and contains keys that are built from one or more of the columns in the table or view. This structure known as B-Tree helps the SQL Server find the row or rows associated with the key values. Indexes can be created on computed columns or xml columns also.

Indexes can be clustered or non clustered.

A clustered index stores data rows in the table based on their key values. Each table can have only one clustered index as the key values in the data rows are unique and the index is built on the unique key column. When a table has a clustered index, it is known as a clustered table. Non-Clustered indexes have structures that are different from the data rows. A non clustered index key value is used to point to data rows that contain the key value. This value is known as row locator. The structure of the row locator is determined on the basis of the type of storage of the data pages. If the data page is stored as a heap, a row locator becomes a pointer to a row. If the data page is stored in a clustered table the row locator is a clustered index key. Clustered and Non clusteredindexes can be unique and indexes are automatically maintained for a table or view whenever the data table is modified.

SQL Server 2005 permits users add non-key columns to leaf level of the non clustered index for by passing existing index key limits and to execute fully covered index queries.

When the primary key and unique constraints of a table column are defined an automatic index is created.

The Query Optimizer uses indexes to reduce disk I/O operations and use of system resources while querying on data. Queries which contain SELECT, UPDATE or DELETE statements requireindexes for optimal performance. When a query is executed, each available method is evaluated for retrieving data and the most efficient one is selected by the Query optimizer. The methodology used may be table scans or index scans. In table scans I/O operations are many and resource intensive as all rows in a table are scanned to find the relevant ones. Index scans are used to searchthe index key columns to find the storage location of rows needed by the query and as the Index contains very few columns, the query executes faster.

SQL Server 2005 provides the user with a new Transact-SQL DDL statement for modifying relational and XML indexes. The CREATE INDEX statement is enhanced to support XML index syntax, partitioning and the included columns. A number of new index options have been added including the ONLINE option that allows for concurrent user access to underlying data during index operations.

To create an index

1. In Object Explorer, right-click the table for which you want to create an index and click Modify.

2. The table opens in Table Designer.

3. From the Table Designer menu, click Indexes/Keys.

4. In the Indexes/Keys dialog box, click Add.

5. Select the new index in the Selected Primary/Unique Key or Index list and set properties for the index in the grid to the right.

6. Specify any other settings for the index and click Close.

7. The index is created in the database when you save the table.

SQL Server allows users create unique indexes on unique columns such as the identity number of the employee or student or whatever is the unique key by which the component data are identified. A set of columns also can be used to create a unique index. The DBA can set the option of ignoring duplicate keys in a unique index if required. The default is No.

To create a unique index

  1. In Object Explorer, right-click the table and click Modify.
  2. The table opens in Table Designer.
  3. From the Table Designer menu, click Indexes/Keys.
  4. Click Add. The Selected Primary/Unique Key or Index list displays the system-assigned name of the new index.

5. In the grid, click Type.

6. Choose Index from the drop-down list to the right of the property.

7. Under Column name, select the columns you want to index. You can select up to 16 columns. For optimal performance, select only one or two columns per index. For each column you select, indicate whetherthe index arranges values of this column in ascending or descending order.

8. In the grid, click Is Unique.

9. Choose Yes from the drop-down list to the right of the property.

10. Select the Ignore duplicate keys option if you want to ignore new or updated data that would create a duplicate key in the index (with the INSERT or UPDATE statement).

11. The index is created in the database when you save the table or diagram.

Please note that unique indexes cannot be created on a single column if the column contains NULL in more than one row. Similarly indexes cannot be created on multiple columns if the combination of the columns contains NULL in some rows. The NULL values are treated as duplicate values.

Clustered indexes can be created in SQL Server databases. In such cases the logical order of the index key values will be the same as the physical order of rows in the table. A table can have only one clustered index.

To create a clustered index

1. In Object Explorer, right-click the table for which you want to create a clustered index and click Modify.
2. The table opens in Table Designer.
3. From the Table Designer menu, click Indexes/Keys.
4. In the Indexes/Keys dialog box, click Add.
5. Select the new index in the Selected Primary/Unique Key or Index list.
6. In the grid, select Create as Clustered, and choose Yes from the drop-down list to the right of the property.

7. The index is created in the database when you save the table.

A full text index is used when a full text search is required to be performed on all the text based columns of the database. This index relies on a regular index which has to be created before a full text index is created. The regular index is created on a single, non null column. Usually a column with small values is selected for the indexation in a regular index. Often a Catalog is created using an external tool such as SQL Server Management Studio. Textual data from different text file formats are to be stored as image type files before Full text search can be done on the data.

To create full-text indexes

  1. In Object Explorer, right-click the table for which you want to create a full-text index and click Modify.
  2. The table opens in Table Designer.
  3. From the Table Designer menu, click Fulltext Index.

4. The Full-text Index dialog box opens. If the database is not enabled for full text indexing the dialog box will have the add button disabled. To enable full text indexing for the database, right click the database>Click properties and check the Full text indexing check box.

5. Then create a catalog by right clicking on Storage>Full Text Catalog and creating a new Catalog and entering the required information in the dialog box that opens.

6. Now open the Full Text Index property dialog box by clicking on it in the Table Designer menu.

7. Click Add.
8. Select the new index in the Selected Full-text Index list and set properties for the index in the grid to the right.
9. Your index is automatically saved in the database when you save your table in Table Designer. The index is available for modification as soon as you create it.

To change index properties

1. In Object Explorer, right-click the table you want to open and click Modify.
2. From the Table Designer menu, click Indexes/Keys.
3. Select the index from the Selected Primary/Unique Key or Index list.
4. Change properties in the grid.
5. The changes are saved to the database when you save the table.

System defined names are assigned to indexes based on the database file name. If multiple indexes are created on a table the index names are incremented numerically with _1, _2 etc. An index can be renamed to be unique to a table. Since the automatically created index bears the same name as the primary key or unique constraint in a table, another index cannot be renamed later to match the primary key or unique constraint.

To rename an index

1. In Object Explorer, right-click the table with the index you want to rename and click Modify.
2. From the Table Designer menu, click Indexes/Keys.
3. Select the index from the Selected Primary/Unique Key or Index list.
4. In the grid, click Name and type a new name into the text box.

5. The changes are saved to the database when you save the table.

Indexes can be deleted. Usually an index is considered for deletion when the performance of the INSERT,UPDATE and DELETE operations are hindered by the Index.

To delete an index

1. In Object Explorer, right-click the table with indexes you want to delete and click Modify.
2. From the Table Designer menu, click Indexes/Keys.
3. In the Indexes/Keys dialog box, select the index you want to delete.
4. Click Delete.
5. The index is deleted from the database when the table is saved.
6. A similar procedure can be followed for deleting a full text index by selecting Full text index from the Table Designer and selecting the index name and clicking delete button.

Microsoft SQL Server database uses a fill factor to specify how full each index page can be. The percentage of free space allotted to an index is defined as the fill factor. This is an important aspect of indexing as the amount of space to be filled by an index has to be determined by the DBA so that performance is not retarded.

To specify a fill factor for an index

1. In Object Explorer, right-click the table with an index for which you want to specify a fill factor and click Modify.
2. The table opens in Table Designer.
3. From the Table Designer menu, click Indexes/Keys.
4. The Indexes/Keys dialog box opens.
5. Select the index in the Selected Primary/Unique Key or Index list.
6. In the Fill Factor box, type a number from 0 to 100. The value of 100 implies that the index will fill up completely and the storage space required will be minimal. This setting is recommended only for cases where data is unlikely to change. If data is likely to undergo addition and modification, it is better to set a lower value. Storage space required would be in proportion to the value set.

XML indexes cannot be created using the Index/Keys dialog box. One or more XML indexes can be created for xml data type columns on the basis of a primary xml index. Deleting the primary xml index will result in the deletion of all indexes created on the base of the primary index.

To create an XML index

1. In Object Explorer, right-click the table for which you want to create an XML index and click Modify. 2. The table opens in Table Designer.
3. Select the xml column for the index.
4. From the Table Designer menu, click XML Index.

5. In the XML Indexes dialog box, click Add.

//

6. Select the new index in the Selected XML Index list and set properties for the index in the grid to the right.

To delete XML Indexes

1. In Object Explorer, right-click the table with the XML index you want to delete and click Modify.
2. The table opens in Table Designer.
3. From the Table Designer menu, click XML Index.
4. The XML Index dialog box opens.
5. Click the index you want to delete in the Selected XML Index column.
6. Click Delete.

Maintain indexes in a SQL Server 2005 database

Dealing with Fragmented indexes, Reorganizing an Index, Rebuilding an Index, Disabling Non-clustered Indexes to Reduce Disk Space During Rebuild Operations, Rebuilding Large Indexes, Setting Index Options, Page Splits and Performance Considerations, Max Degree of Parallelism, Online Index Operations, Partition Index Operations, Statistical Information, Asynchronous Statistics Updates, Disabling Automatic Statistics, Statistics after Upgrading a Database to SQL Server 2005, Bulk copy options and Index operation logging.

The DBA has to ensure optimal performance of the database. One of the key elements in database maintenance is to ensure that minimum disk I/O is performed in queries. Making good indexes is a means to this end. The activities a database administrator performs to maintain his indexes are:

  1. Reorganizing and rebuilding indexes. This process involves defragmenting of indexes and optimization of disk space.
  2. Using the Fill factor option to fine tune the index data storage and performance.
  3. Performing index operations online by using the LINE index option to provide user access to data during index operations
  4. Configuring parallel Index operations by using the max degree of parallelism option.
  5. Using the Query optimizer to develop optimal query plans based on statistics.
  6. Improving the performance of bulk copy operations on tables with clustered and non clustered indexes.
  7. Selecting suitable Recovery model of the database for index operations and listing the types of logging available.

Dealing with Fragmented indexes

Though the SQL Server 2005 data base engine automatically maintains indexes whatever the operations made on the database, the modifications can cause a degradation of the index over time. This is turn will degrade query performance. To help the DBA over come the problems of fragmentedindexes, SQL Server 2005 provides an option of reorganizing or rebuilding the index. This option can be used on whole indexes or partitioned indexes.

Fragmentation of an index can be recognized by a process of analysis using the sys.dm_db_index_physical_stats function. This function detects fragmentation of a particular index or allindexes in the database. For partitioned indexes , the information is provided for each partition. The results of this operation are displayed in the AvgFragmentation column. The AvgFragmentSize column describes the average number of pages in one fragment in an index. Once the fragmentation size and value is known it has to be evaluated against the parameters for correction provided with SQL Server 2005. If the AvgFragmentation value is <=30% the corrective would be to ALTER INDEX REORGANIZE else the corrective would be to ALTER INDEX REBUILD WITH (ONLINE=ON). The first option is always done online while the second option can be used online or offline. The syntax would be as under:

USE Exforsys;
GO
SELECT IndexName, AvgFragmentation
FROM sys.dm_db_index_physical_stats (N’Employeetransact.EmployeeID’, DEFAULT, DEFAULT, N’Detailed’);
GO

Reorganizing an Index

In SQL Server 2005 the ALTER INDEX REORGANIZE statement has replaced the DBCC INDEXDEFRAG statement. A single partition of a partitioned index can be reorganized using this statement.

When an index is reorganized the leaf level of the clustered and non clustered indexes on tables and views are reorganized and reordered to match the logical order—i.e. left to right of the leaf nodes. The index is organized within the allocated pages and if they span more than one file they are reorganized one at a time. No pages are migrated between files. Moreover, pages are compacted and empty pages created as a consequence are removed and thedisk space released. The compaction is determined by the fill factor value in sys.indexes catalog view. Large object data types contained in clustered index or underlying tables will also be compacted by default if the LOB clause is present..

The good news is that the reorganize process is economical on the system resources and is automatically performed online. There are no long term blocking locks which jam up the works!

DBAs are advised to reorganize the index when it is minimally fragmented. Heavily fragmented indexes will require rebuilding.

Rebuilding an Index

When an index is rebuilt, it is dropped and a new one is created. In the process fragmentation is removed and disk space is reclaimed. The fill factor setting is used to reorder the pages after compacting in a sequential order. Performance is improved and number of page reads is reduced. The following methods are used to drop and rebuild the index.

1. ALTER INDEX with the REBUILD clause.
2. CREATE INDEX with the DROP_EXISTING clause.

Each of these functions have their own advantages and disadvantages.

Disabling Non-clustered Indexes to Reduce Disk Space During Rebuild Operations

When a rebuilding operation is performed it is a best practice to disable the non-clustered indexes. Diabling the non-clustered index implies that the data rows are deleted but the definition is retained in metadata. The index is enabled after it is rebuilt.

Rebuilding Large Indexes

Indexes which have more than 128 extents must be rebuilt in two phases—the logical and the physical. The logical phase the allocation units of the index are marked for deallocation, the data rows are copied and sorted before they are moved to new allocation units in the rebuilt index. The physical phase involves dropping of the allocation units marked for deallocation in short transaction without locks.

Setting Index Options

When reorganizing an index, the index options cannot be specified as a rule. However, the ALTER INDEX REBUILD and CREATE INDEX WITH DROP_EXISTING allows users set the options such as PAD_INDEX, FILLFACTOR, SORT_IN_TEMPDB, IGONORE_DUP_KEY and STATISTICS_NORECOMPUT etc. Additionally the ALTER INDEX statement allows the specification of ALLOW_PAGE_LOCKS and ALLOW_ROW_LOCKS

Page Splits and Performance Considerations

A page split is a process by which a new row added to a table is some rows in the table are moved to a new page to make room for the new records. This happens during reorganization at the risk of being resource intensive and causing fragmentation. To reduce the risk the fill factor has to be correctly selected. Else the index will have to be frequently rebuilt.

Max Degree of Parallelism

When several processors are used to perform the scan and sort operations on indexes, the number of processors to be used is to be specified. This can be specified in the configuration option max degree of parallelism and also by the current workload. This option limits the number of processors that can be used in parallel. This option is available only for the Enterprise version of the edition.

When the DBA wants to manually configure the number of processors that can be used to run the index statement the MAXDOP index statement is used. This limits the number of processors to be used during an index operation of the query optimizer. This option overrides the max degree of parallelism option for the query. The MAXDOP index option cannot be specified for the ALTER INDEX REORGANIZE statement.

Online Index Operations

Concurrent index operations can be performed during Online index operations. The MAXDOP operation can be used ot control the number of processors dedicated ot online index operations.

Partition Index Operations

Partition index operations can be memory resource intensive if the query optimizer applies degrees of parallelism.

Statistical Information

Statistical information can be created about the distribution of values in a column. This is used by the query optimizer to determine the optimal query plan by estimating the cost of using an index to evaluate a query. These values are sorted by thedatabase engine on which the statistics is being built and a histogram is created for a maximum of 200 values separated by intervals. Additional information is introduced on statistics created on char, varchar, varchar(max), nchar, nvarchar(max), text and ntext columns. This is known as string summary. The string summary helps the query optimizer estimate the selectivity of query predicates on string patterns. This makes for accurate estimates of result set sizes and frequently better query plans.

When the query optimizer is configured to automatically store statistical information about indexed columns, statistics on the columns are automatically generated without indexes that are used in a predicate.

Asynchronous Statistics Updates

The AUTO_UPDATE_STATISTICS_ASYNC option can be used to ensure that the query optimizer is prevented from returning a result set while it waits for the out of date statistics to be updated and compiled. The out of date statistics are put on queue for updating by a worker thread in a background process and the query and the concurrent queries compile immediately. This option is to be set at database level and determines the update method all statistics at the database.

Disabling Automatic Statistics

Automatic statistics can be disabled for a particular column or index by using the sp_autostats system stored procedure or the STATISTICS_NORECOMPUTE clause of the CREATE INDEX statement. There are other clauses also which can be used with the update statement etc to prevent automatic generation of statistics. However, the statistics can be updated manually using the sp_createstats system stored procedure.

Statistics after Upgrading a Database to SQL Server 2005

When the user upgrades the version of SQL server the statistics of the earlier version is treated as out of date. On first use, the statistics will have to be updated using the AUTO_UPDATE_STATISTICS database option.

Bulk copy options and Index operation logging.

Bulk copy options are useful to copy data into a table without a non clustered index. Logging the index operations minimally during this process makes it more efficient and reduces the possibility of the index operation filling the log. However the option depends on whether the table is indexed or not and whether the table is empty or not. If the table is empty, both data and index pages are minimally logged. If the table has no clustered index, data pages are always minimally logged. If the table is empty, the index pages are minimally logged. In non empty tables index pages are fully logged.

reference :

http://www.exforsys.com/tutorials/sql-server-2005/sql-server-defining-indexes.html

http://www.exforsys.com/tutorials/sql-server-2005/maintaining-sql-server-indexes.html

Posted in Sql Server | Tagged: , , | Leave a Comment »

 
%d bloggers like this: