Mark Mintoff My superpower is common sense


Checking and Fixing Index Fragmentation and Statistics in SQL Server

In recent experience, a query which took an hour to execute, executed for three consecutive days with no end in sight. The query had to be terminated early.

What caused this was a high index fragmentation and incorrect table statistics, which lead to an invalid execution plan to be created.

The following script checks and reports on the index fragmentation of all tables within a database:

 Microsoft recommends the following corrective statements:

  • For index fragmentation 5-30%; ALTER INDEX REORGANIZE
  • For index fragmentation greater than 30%; ALTER INDEX REBUILD

To that end, I created the following script to automatically Reorganize or Rebuild fragmented indices and update the statistics on the table:

 After running the above, the same query that was stuck running for three days, completed in just sixteen minutes; an improvement even on the original execution time!

VN:F [1.9.22_1171]
Rating: 5.0/5 (1 vote cast)

Microsoft Excel Mod Problem

There exists a problem in Microsoft Excel's Mod function, whereby performing modulo on decimal numbers results in incorrect remainders.

For instance, MOD(0.17, 0.17) results in 0 (as expected), whereas MOD(0.51, 0.17) results in 0.17 (incorrect).

In fact, a modulo on the following multiples of 0.17 has resulted in many erroneous reports of remainders:


The reasoning behind this is that internally a float data type is used which results in an incorrect modulo to be calculated. In fact, if you try this out in C# using float or double, you will get the exact same results.

The solution, I have found is to use the formula for modulo:
    x - (y * INT(x/y))

To this end, I have created a simple function through VBA which achieves a correct result by enforcing the usage of the decimal data type.


VN:F [1.9.22_1171]
Rating: 5.0/5 (2 votes cast)

How long will my database be IN RECOVERY? (SQL 2008)

I write this as I stare at a screen waiting for a critical database to return out of being In Recovery. Have you ever been there? Have you ever had an unexpected shutdown or error on the production server, while you were running a large transaction? If your answer is "Yes", then inevitably you are wondering how long it is going to take.

To this end, I have devised a simple query which will loop through all databases on the current server and give a report on the database recovery process for any that are in recovery. This will give you an estimate of how long you have left twiddling your thumbs and hoping you did not lose any data.

Enjoy and I hope you did not lose any data!

VN:F [1.9.22_1171]
Rating: 0.0/5 (0 votes cast)

ASP.Net Update Panel Flickering Scroll Issue

There appears to be an issue with the ASP.Net Update Panel which causes scroll position to be lost in between page reloads, making the page appear jumpy.

This is a very simple, yet effective fix:

The secret is this little nugget of javascript written exactly after the Script Manager:

This disallows the resetting of the scroll position, meaning that the original scroll position is maintained.

VN:F [1.9.22_1171]
Rating: 5.0/5 (3 votes cast)

T-SQL IsNullOrEmpty

Lately (because of my line of work) I am using SQL Server more than I am using Visual Studio. I miss some of the functions provided in C# however.

One of them is String.IsNullOrEmpty and SQL Server does not natively have it.

Dead simple, but a comfort in long CASE statements:

Usage is simple and according to expectations:

Unfortunately I do not know of a way to avoid the "= 1" as I do not think boolean/bit values are handled the same in SQL.


VN:F [1.9.22_1171]
Rating: 0.0/5 (0 votes cast)

Regex Split by Comma not Surrounded by Quotes

Recently I had a requirement where I had to programmatically extract data from a very messy CSV file and populate a table. The data contained addresses, which included commas in them. In order to distinguish between a comma which separates columns and a comma in the text, the text was surrounded by double quotes.

The solution is obvious; use Regex. The regular expression however is not so straight-forward.

In C#, with escape characters:

Works like a charm.

VN:F [1.9.22_1171]
Rating: 4.9/5 (19 votes cast)

Developer Friendly Windows Search

As a developer, I find Windows' search incredibly lacking. I can never find the files I want, especially if I'm trying to find a particular pattern of text within the files. To this end, I resorted to making my own simple search algorithm:

I hope you find this to be as useful as I do.

VN:F [1.9.22_1171]
Rating: 5.0/5 (3 votes cast)

C# ASP.Net Custom HTML Button

I have opted to create an ASP.Net button (postback and event handle) from scratch.

Why? Because I can. Also because it allows me more control over the visual presentation of a button.

This little endeavor is still a work in progress however it supports a server-side click event, a client-side click event, button text, an icon image and works within ASP Update Panels as well.

The markup is relatively straight-forward:

I am using an

  • as the root element, because most of the time I make use of a
      -based layout

      The back-end for the button is as follows:

      As you can see, I am making use of the IPostBackEventHandler and registering the control with the ScriptManager as a PostBackControl or an AsyncPostBackControl, depending on whether or not the control is contained within an Update Panel.

      Currently, I am hoping there is a more efficient way of detecting whether the button is within an Update Panel, but the while loop will do for now.

      I have written a small amount of CSS for the button, in order to make it look good:

      In a perfect world, I would not need to write CSS to support anything less than IE8, but this CSS breaks on IE7. I would imagine it would do very much the same thing on IE6. However, I actually dedicated (wasted) an hour towards trying to get this to work on IE7:

      Usage is simple and can be figured out from the download:

      What I have provided is a veritable playground of design customization in lieu of the standard button whilst more or less retaining the same functionality of the standard button.

      Any customization or features are completely up to you. Happy coding/designing!

      VN:F [1.9.22_1171]
      Rating: 0.0/5 (0 votes cast)
  • 26Oct/120

    C# Programmatic Active Directory Impersonation

    Recently, a requirement involving shared folders forced me to dabble into Active Directory account impersonation.

    The following is what I ended up doing:

    Usage is pretty simple:

    And you're good to go. I managed to browse through the shared folder programmatically and copy over the files needed for my ETL.

    VN:F [1.9.22_1171]
    Rating: 0.0/5 (0 votes cast)

    C# Console Progress Bar

    At work, I sometimes find myself needing to make Console applications which take some time. For example, I have a few console applications which parse dump files into objects and then insert the data into a database. Usually, I represent the progress of these applications with a simple incremental percentage display, however I thought I'd create a generic method which would display an ASCII progress bar.

    The code itself is pretty self explanatory and probably more verbose than it really needs to be, but it gets the job done and looks good. It is a nice throwback to Pascal and QBasic for me.

    EDIT: Amended code to append characters to a string before writing on screen. This fixes the flickering problem.

    VN:F [1.9.22_1171]
    Rating: 5.0/5 (13 votes cast)