About This Blog

.net & SQL Samples, programming tips and tricks, performance tips, guidelines, and best practices

Monday 25 April 2011

Rebuild all the indexes of a table & Indexed views in a Database

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 –
ALTER INDEX ALL ON TableName REBUILD WITH (FILLFACTOR = Any number between 0 to 100);

Before attempting to REBUILD, we should first find the level of fragmentation using the below tsql –
SELECT * FROM sys.dm_db_index_physical_stats(db_id(), object_id('tablename'), NULL, NULL , 'DETAILED')

Microsoft Recommends
REBUILD IF > 30% framgmented
REORGANIZE IF >5 % and < 30% framgmented

I have prepared the following TSQL to rebuild all the indexes on the all the tables and all the indexed views of the database in use.

USE DatabaseName

DECLARE @fillfactor INT

SET @fillfactor = 90

SELECT @tsql =
           ';' + 'ALTER INDEX ALL ON ' + o.name + ' REBUILD WITH (FILLFACTOR = ' + CONVERT(VARCHAR(3),@fillfactor) + ')'
           sysobjects o
          INNER JOIN sysindexes i
           ON o.id = i.id
           o.xtype IN ('U','V')
           AND i.name IS NOT NULL
          FOR XML PATH('')), 1,1,'')

--PRINT @tsql         
EXEC sp_executesql @tsql

Wednesday 13 April 2011

Pass a collection of object as Table Valued Parameter in SQL Server 2008


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.

First we’ll define the Order and OrderDetail classes and properties as under:
  public class Order
    public int OrderId { get; set; }
    public string OrderNo { get; set; }
    public int CustomerId { get; set; }
    public DateTime Date { get; set; }

  public class OrderDetail
    public int OrderId { get; set; }
    public int ItemId { get; set; }
    public decimal Quantity { get; set; }
    public decimal Price { get; set; }
    public decimal Discount { get; set; }
Generally, List<Order> and List<OrderDetail> objects serves as collections of Order and OrderDetail objects in our application. But these collections, by default won’t support on their own as input values for TVPs because List<T> doesn’t implement IEnumerable<SqlDataRecord>. Hence, we need to add that ourselves. So we’ll define Orders and OrderDetails classes that inherit List <Order> and List <OrderDetail> respectively, and also implement IEnumerable<SqlDataRecord> to “TVP-enable” them:
  public class Orders : List<Order>, IEnumerable<SqlDataRecord>
    IEnumerator<SqlDataRecord> IEnumerable<SqlDataRecord>.GetEnumerator()
      var sdr = new SqlDataRecord(
      new SqlMetaData("OrderId", SqlDbType.Int),
      new SqlMetaData("OrderNo", SqlDbType.VarChar),
      new SqlMetaData("CustomerId", SqlDbType.Int),
      new SqlMetaData("Date", SqlDbType.Date));

      foreach (Order o in this)
        sdr.SetInt32(0, o.OrderId);
        sdr.SetString(1, o.OrderNo);
        sdr.SetInt32(2, o.CustomerId);
        sdr.SetDateTime(3, o.Date);

        yield return sdr;

  public class OrderDetails : List<OrderDetail>, IEnumerable<SqlDataRecord>
    IEnumerator<SqlDataRecord> IEnumerable<SqlDataRecord>.GetEnumerator()
      var sdr = new SqlDataRecord(
      new SqlMetaData("OrderId", SqlDbType.Int),
      new SqlMetaData("ItemId", SqlDbType.Int),
      new SqlMetaData("Quantity", SqlDbType.Decimal),
      new SqlMetaData("Price", SqlDbType.Decimal),
      new SqlMetaData("Discount", SqlDbType.Decimal)

      foreach (OrderDetail od in this)
        sdr.SetInt32(0, od.OrderId);
        sdr.SetInt32(1, od.ItemId);
        sdr.SetDecimal(2, od.Quantity);
        sdr.SetDecimal(3, od.Price);
        sdr.SetDecimal(4, od.Discount);

        yield return sdr;
I’ll only explain the Orders class; you can then infer any of your own collection classes – implements the custom iterator needed to support TVPs.
First, it inherits List<Order>, so with an Orders object we can do everything that could be done with List<Order>object. It implicitly also implements IEnumerable<Order> and explicitly implements IEnumerable <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 a GetEnumerator method that returns an IEnumerator <SqlDataRecord>. This method first initializes a new SqlDataRecord 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 first Order element, and then issues the yield return statement. Any method which returns IEnumerator<T> and has a yield return statement in it, is a custom iterator method that is expected to return a sequence of objects until the method execution path completes (in this case, when the foreach loop finishes).
When we invoke ExecuteNonQuery to run a stored procedure with a SqlDbType.Structured parameter (that is, a TVP), ADO.NET expects the collection passed for the parameter value to implement IEnumerable <SqlDataRecord> so that IEnumerable<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, the SqlDataRecord is initialized and is then populated with values using the Set methods (there’s a SetXXX method for each data type). That SqlDataRecord “row” is then pushed into the pipeline to the server by yield return. When the next element is fetched from the collection, the GetEnumerator method resumes from the point that it yield returned the previous element, rather than entering GetEnumerator again from the top. This means the SqlDataRecord gets initialized with schema information only once, while its population with one element after another is orchestrated by the controlling ADO.NET code for ExecuteNonQuery that actually ships one SqlDataRecord after another to the server.