Thursday, February 7, 2008

Error/Exception Sequence in a Page

Error/Exception Sequence

When an unhandled exception is thrown from any click handler at page level (As below), below is the sequence it follows for the propogatation.

protected void Button1_Click(object sender, EventArgs e)


throw new Exception("Arun");


· Page_Error Event Handler - Remove further propgation by Server.ClearError()

· ErrorPage attribute at the Page Level

· Application_Error in Global.asax - Remove further propgation by Server.ClearError()

· Custom Errors in Web.config (If Any) – And if the page is not found at this last level it handles the error to the OS.

Thanks & Regards,

Arun Manglick || Tech Lead

Wednesday, February 6, 2008

Clustered Non-Clustered Indexes

Clustered Indexes

· Determine the physical order of data in a table.

· You can only have one clustered index for each table, as the data pages can only be physically stored in one way

· After a clustered index is created, data pages are physically contiguous, which can assist with the speed of queries.

· For a table with a clustered index, the leaf nodes are the data pages themselves.

· Good candidates - Columns that are

o Queried often in range queries (Between, <, >)

o Order large result sets,

o Used in aggregate functions, and

o Whose contents are distinct (primary or unique keys)

o Lastly, Choose a Smaller Clustered Index Key over a wider column, reducing the size of the index needed and improving I/O operations.

· Bad candidates - Columns that are

o Frequently updated columns and

o Non-unique columns

Non-Clustered Indexes

· It stores index pages separately from the physical data, with pointers to the physical data located in the index pages and nodes. In a way it contains poitner to the Clustered Index leaf pages

· Nonclustered index columns are stored in the order of the index key column values.

· You can have up to 249 nonclustered indexes on each table or indexed view.

· For nonclustered indexes, the leaf node level is the Index Key Coupled To A Bookmark.

· The nonclustered index bookmark points to the B-Tree structure of the table's clustered index (if one exists). If the base able is a heap, the nonclustered index bookmark points to the table row's Row-ID.

· Good candidates - Columns that are

o Used in Where clause

o Order smaller result sets on unique data

o involved in searchable arguments using operators such as =, <, >, IN, EXISTS, and LIKE.

To Index or not to Index?

· Indexes take up space, and should not be added 'just in case'. View Tools for testing the effectiveness of your indexes.

· Indexes can slow down data loads significantly, and may not make sense for tables that are used expressly for data Imports And Staging.

· If your table will not be queried and is used as an intermediate staging ground, leaving indexes off the table may be a better choice.

· The same goes for very small tables where, no matter what, the query optimizer always performs a table scan.

· Keep in mind that heaps become naturally fragmented over time and, if data updates are frequent enough, even small tables can begin to take up more space than necessary. There are those who believe that a clustered index should exist for every table in the database, period.

· One technique for reducing bulk load operations on a table is to remove the index during the load and add the index once the load is finished