About This Blog

Monday, 25 April 2011

Rebuilding an index drops and re-creates the index. This removes fragmentation, reclaims disk space by compacting the pages based on the specified or existing fill factor setting, and reorders the index rows in contiguous pages. When ALL is specified, all indexes on the table/view are dropped and rebuilt in a single transaction. FOREIGN KEY constraints do not have to be dropped in advance.

In general, rebuilding or reorganizing small indexes often does not reduce fragmentation. The pages of small indexes are stored on mixed extents. Mixed extents are shared by up to eight objects, so fragmentation in a small index might not be reduced after reorganizing or rebuilding the index.

To REBUILD all the indexes, the following syntax is sufficient –

ALTERINDEXALLONTableNameREBUILDWITH (FILLFACTOR=Any number between 0 to 100);

Before attempting to REBUILD, we should first find the level of fragmentation using the below tsql –

Wednesday, 13 April 2011

In my last post on Table Valued Parameters, I discussed about the concept and demonstrated with a small example how to implement them. The example I gave used an object of DataTable as a table valued parameter. However, I have come across many queries on “How to pass a collection as a Table Valued Parameter?” My this post will answer this question.

Many of our first reaction to the question will be – “what is the big issue in that” and to a great extent it’s very much correct. However, when we actually start implementing it, it is then that we realize that we are missing something and it is nothing but IEnumerable<SqlDataRecord>interface. All what we need to do is inherit our collection from IEnumerable<SqlDataRecord> interface. This interface requires our collection class to implement a C# custom iterator method named GetEnumerator which ADO.NET will call for each object contained in the collection when you invoke ExecuteNonQuery or any other execute method. Please note that VB .NET doesn’t support custom iterators.

Implementation

First we’ll define theOrder andOrderDetail classes and properties as under:

publicclassOrder

{

publicint OrderId { get; set; }

publicstring OrderNo { get; set; }

publicint CustomerId { get; set; }

publicDateTime Date { get; set; }

}

publicclassOrderDetail

{

publicint OrderId { get; set; }

publicint ItemId { get; set; }

publicdecimal Quantity { get; set; }

publicdecimal Price { get; set; }

publicdecimal Discount { get; set; }

}

Generally,List<Order> andList<OrderDetail> objects serves as collections ofOrder andOrderDetailobjects in our application. But these collections, by default won’t support on their own as input values for TVPs becauseList<T> doesn’t implementIEnumerable<SqlDataRecord>. Hence, we need to add that ourselves. So we’ll define Orders andOrderDetails classes that inheritList <Order>andList <OrderDetail> respectively, andalso implementIEnumerable<SqlDataRecord> to “TVP-enable” them:

I’ll only explain theOrders class; you can then infer any of your own collection classes – implements the custom iterator needed to support TVPs.

First, it inheritsList<Order>, so with an Orders object we can do everything that could be done with List<Order>object. It implicitly also implementsIEnumerable<Order> and explicitly implementsIEnumerable <SqlDataRecord>which means it also has a customer iterator method for ADO.NET to consume when an instance of this collection class is assigned to a SqlDbType.Structured parameter for piping over to SQL Server with a TVP.

Implementing IEnumerable<SqlDataRecord>requires implementing aGetEnumerator method that returns anIEnumerator <SqlDataRecord>. This method first initializes a newSqlDataRecord object with a similar schema of UDTTs that the TVPs are declared as. It then enters a loop that iterates all the elements in the collection. On the first iteration, it sets the column property values of the SqlDataRecord object to the property values of the firstOrderelement, and then issues theyield return statement. Any method which returnsIEnumerator<T>and has ayield return statement in it, is acustom iterator method that is expected to return a sequence ofT objects until the method execution path completes (in this case, when theforeach loop finishes).

When we invokeExecuteNonQuery to run a stored procedure with aSqlDbType.Structured parameter (that is, a TVP), ADO.NET expects the collection passed for the parameter value to implementIEnumerable <SqlDataRecord> so thatIEnumerable<SqlDataRecord>.GetEnumerator can be called internally to fetch each new record for piping over to the server. This method is never called directly.

When the first element is fetched from the collection,GetEnumerator is entered, theSqlDataRecord is initialized and is then populated with values using the Set methods (there’s aSetXXXmethod for each data type). ThatSqlDataRecord “row” is then pushed into the pipeline to the server byyield return. When the next element is fetched from the collection, the GetEnumerator methodresumesfrom the point that ityield returned the previous element, rather than entering GetEnumerator again from the top. This means theSqlDataRecord gets initialized with schema information only once, while its population with one element after another is orchestrated by the controlling ADO.NET code for ExecuteNonQuerythat actually ships oneSqlDataRecord after another to the server.

Search This Blog

About Me

Experience of more than 11+ years in developing & designing Client-Server based applications, smart client applications, web portals and small utility applications using Microsoft technologies.
Strong in designing the Application Architecture, providing out of box solutions & a very good trouble shooter.