SQL Server Table Fragmentation

How do you find out the % fragmentation of your table? 

SQL Server stores your table data rows on data page that is normally 8Kb/Page. These 8k pages form a 64Kb container call Extent.

use dbcc showcontig ('YOUR_TABLE_NAME').


If it's fragmented, then run DBCC REINDEX. DBCC REINDEX reorganises your database table and index like defraging your hard drive.


This table describes the information in the result set. Microsoft owns the following tables. More info here.

Statistic	Description
Pages Scanned	Number of pages in the table or index.
Extents Scanned	Number of extents in the table or index.
Extent Switches	Number of times the DBCC statement moved from one extent to another while it traversed the pages of the table or index.
Avg. Pages per Extent	Number of pages per extent in the page chain.
Scan Density [Best Count: Actual Count]	Best count is the ideal number of extent changes if everything is contiguously linked. Actual count is the actual number of extent changes. The number in scan density is 100 if everything is contiguous; if it is less than 100, some fragmentation exists. Scan density is a percentage.
Logical Scan Fragmentation	Percentage of out-of-order pages returned from scanning the leaf pages of an index. This number is not relevant to heaps and text indexes. An out of order page is one for which the next page indicated in an IAM is a different page than the page pointed to by the next page pointer in the leaf page.
Extent Scan Fragmentation	Percentage of out-of-order extents in scanning the leaf pages of an index. This number is not relevant to heaps. An out-of-order extent is one for which the extent containing the current page for an index is not physically the next extent after the extent containing the previous page for an index.
Avg. Bytes free per page	Average number of free bytes on the pages scanned. The higher the number, the less full the pages are. Lower numbers are better. This number is also affected by row size; a large row size can result in a higher number.
Avg. Page density (full)	Average page density (as a percentage). This value takes into account row size, so it is a more accurate indication of how full your pages are. The higher the percentage, the better.

WatiN Login automation

How could you automate web application screen testing?

- Write unit test in Selenium or WatiN (pronounce what-in).

Selenium has action replay feature and Firefox plugin to record a series of steps then generating the script for you. However, my example would be using WatiN as it's lightweight and the project dependency is only 1 library (WatiN.Core). Simple.

The following code demonstrates a unit test script to login to Google account (via Internet Explorer).

1. Download WatiN from here. Extract the WatiN.Core and add this lib to your project reference.
2. Add a test method as below. Fill in a valid Email and Passwd (replace me.gmail.com and xxx).


[TestMethod]
public void LoginGoogle()
{

using(IE _ie = new IE("https://www.google.com/accounts/ServiceLogin?service=mail&passive=true&rm=false&continue=http%3A%2F%2Fmail.google.com%2Fmail%2F%3Fui%3Dhtml%26zy%3Dl&bsv=1eic6yu9oa4y3&scc=1&ltmpl=default&ltmplcache=2"))
{
_ie.TextField(tf => tf.Name.Equals("Email")).TypeText("me@gmail.com");
_ie.TextField(tf => tf.Name.Equals("Passwd")).TypeText("xxx");

_ie.Button(Find.ByValue("Sign in")).Click();

_ie.Link(Find.ByText("Sign out")).Click();
_ie.Close();

}

}

3. Right click on the TestMethod and click Run Tests. Result as follow (click on picture to enlarge) :

4. What you might ask. How do I find out the textbox's name for the test page?
You could use Firefox Firebug or IE7 Developer Tool to find that out.

Ninject 2 MVC Extension binary download

MVC Unit Testing

I have been checking out how to unit test MVC2 web app. Found Ninject. 

Ninject is a dependency injection framework like RhinoMock, Moq. It enables you to unit test your code by injecting mock object at run-time. Nice feature as it is light-weight and no xml dependencies writing.

Git-ting Ninject is a pain

Navigated to Ninject MVC Extension source file on github using browser window. You should see a bunch of source code folders. Navigate into mvc2. 

You have to use the git client to download, compile  the web.mvc library. Not good. Me no like git-ting.

I gave up on the git-ting task after sometimes. Then I found out binaries could be downloaded from github TeamCity build server. Hurray!! No build!! :)

Where to download the binaries?

Click on this link Ninject web mvc2  and login as guest. Click on the artifact link and you should see the binaries. 

Unzip and reference the Ninject.dll and Ninject.Web.Mvc.dll in visual studio. Voila! You are ready to Ninject.

Windows 7 - Play more

Windows 7 new features

1. Problem Steps Recorder. This app records your screen actions. Type psr in Run command textbox. This is particularly useful for support to troubleshoot window issue
2. Attache vhd. Type diskmgmt.msc. Click on Action -> Attach VHD.
3. Switch display. [Window] key + P.
4. Calibrate display. Type dccw.
5. Virtual WI-FI. Turn your computer into a virtual router. Download it from http://virtualrouter.codeplex.com/
6. Resource Monitor. Type resmon. This is equivalent to WinXP Task Manager.
7. Activate God mode. All control panel settings in 1 place. Create a folder and rename the folder to .{ED7BA470-8E54-465E-825C-99712043E01C}. i.e. GodMode.{ED7BA470-8E54-465E-825C-99712043E01C}.
8. Show Minimize, Maximize menu on running application. Press Ctrl+shift right-click on running application bar (bottom of the screen).
9. Navigate over task bar. [Window] key +T to rotate task bar apps.
10. Use 3M Note. Type StikyNot.exe.
11. Run as different user. Hold down Shift, right-click on shortcut. you will see Run as different user menu.
12. Open jumplist. Hold left mouse button on Task Bar and drag upwards.

Select distinct rows and sum columns in DataTable

How to filter distinct row from a DataTable column?

Use the [DataView].ToTable (bool, string[]) method. The following code returns a distinct category (no repeated rows in same category).

Example:

DataTable newTable = view.ToTable(true, "Category");

How to sum column total from a DataTable?

Use the DataTable Compute () function. This method performs aggregate function on the DataTable based on the filter criteria.

Example:

newTable.Compute("Sum(Sales)","Category='Pasta'");

How to describe Oracle table columns

SELECT column_name
FROM all_Tab_Columns
WHERE table_name= ''
ORDER BY column_name;

Select top 10 records in Oracle

Microsoft TSQL has select top records statement. How do you do it in Oracle PL/SQL?

TSQL:
select top(10) from tblStudents

PL/SQL:
select * from
(select IDStudent, FirstName, LAstName from tblStudents)
where rownum <10

Annoying ASP.NET postback - cursor on top

Scenario

Isn't it annoying that the mouse cursor is positioned to the top of the page for each post back. This happens to components that are not wrapped by a Ajax post-back and with control that has autopostback set to True.

Resolution

For .NET framework 3 and above, set the smartnavigation= true on the aspx Page declaration. Don't worry if the Page tag auto-complete doesn't give you the smartnavigation property.

<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="Default.aspx.cs" Inherits="Demo.Tricks._Default" smartnavigation="true"%>

Result

If a drop-down box at the bottom of the page is selected and post-back to the server, smartnavigation feature will re-position the cursor to where the post-back occurs. Brilliant!!

Window 7 Ultimate Aero preview does not work

Windows 7 user can preview the actual window when they hover their mouse over the application bar (at the bottom of the window). This is known as the Aero effect.

Problem:
The user can only see a rectangular box (no preview). Is my installation faulty? No.

Cause:
The Aero effect is not turn on by default for Windows 7 Ultimate edition. It depends on a window service called Desktop Window Manager Session Manager. The service is set to manual for default installation.

Resolution:
Set the "Desktop Window Manager Session Manager" start mode to "Automatic".

Step-by-step:

1. Type services.exe into the Run textbox and press enter. This will open the window service listing.
2. Locate Desktop Window Manager Session Manager service and right click on "Properties"
3. Set the start mode to Automatic.
4. For instance effect, start the service.
5. Voila.

How to open network connections window

To open "Network Connections" window via command prompt

Type npca.cpl in Start->Run textbox (works with WinXP and Windows 7).

To open "Add Remove program" window via command prompt

Type appwiz.cpl in Start->Run textbox (works with WinXP and Windows 7).