Clemens arrives tomorrow and this can't be good....

I stated using Google proper (www.google.com) since it was faster (and still is) to type in DataReader.Read() into Google and get to the MSDN page than search MSDN itself. This was back in about 2000 when Google released the www.google.com/microsoft.html page that indexes all the Microsoft specific sites (including blogs and 3^rd party sites besides MSDN content).   

I installed the Google toolbar as well as the Yahoo, A9 and MSN toolbars and played with them the best I could, but I always came back to Google. Until after its IPO, it removed the “search Microsoft” from its drop down list and I had to bookmark www.google.com/microsoft.html.

I was very excited about Google Desktop and installed it right away. I was marginally impressed. I did not like the web interface-why bother with creating a web server on the user’s machine, if you are going to install custom software why not something easier to manage? That said I started to use it anyway. After some time of using it I came to notice that all I was really concerned with was email and attachments in email, so on the advice of Adam Cogan (gulp) I installed Lookout (which Microsoft has since purchased) and used that extensively-as it is better than Google Desktop for email searches and it integrated into Outlook, I even uninstalled Google Desktop from machine.

So when Microsoft yesterday announced the new MSN toolbar that also performed Desktop Search I was not immediately excited. Like the curious cat that I am, I installed it anyway.

I was surprised! MSN Toolbar/Desktop Search is a far superior product than Google Desktop (and Lookout). Here is why:

The toolbars have the same functionality but Google took about 3 days to archive and MSN about an hour.

MSN search has English Questions Ask it: “What is the capital of the Netherlands” and Encarta will come up along with the answer as well as web links below it. Google just has links.

 MSN has superior local search, but Google is catching up fast.

**Google does have that super cool autcomplete in beta and announced today that it was scanning in textbooks and university libraries.

True Google has Newsgroup archives, but I rarely ever use that-old Newsgroups are not as interesting to me as current ones which now MSN can do via indexing Outlook Express (you have to subscribe to them first though)

Using MSN Toolbar on the desktop is great. I usually don’t have this sort of reaction to software. It has autocomplete and immediate feedback as you type. I typed in “Clem” and it knew I was searching for Clemens Vasters.

It is super smart and real fast. It even indexes data better than Google. It even picked up an XML file of some old sample data from a Conference-Google Desktop did not.

It is still in beta and not perfect (why not index .CS files???), but I am already using it as my main search for the web and desktop. I had been hard code Google user for 4 years-it has not come to an end. 

Come one, come all, this Thursday at the NYC .NET Developers Group, Andrew, Bill and I will give a real in-depth sneak peak on SQL Server 2005 including a tools overview, TSQL, XQuery,XML, Service Broker, OLAP and the Unified Demensional Model. Too bad Clemens will not be there, he is arriving on Friday and spending the weekend at my place for some year end partying. Maybe we will rewrite dasBlog to use SQL Server 2005. (Most likely we will just drink a lot and fall down.)

Did another race this weekend, 10K in Central Park. 8:39 pace, no way I can hold that up for an entire marathon.

Last Name	First Name	Sex/ Age	Bib	Team	City	State	Net Time	Pace/ Mile
GURBISZ	KATHLEEN	F27	5484		NY	NY	51:48	8:21
FORTE	STEPHEN	M32	5448		NEW YORK	NY	53:40	8:39

            DENSE_RANK works exactly like RANK() but will remove the skipping of numbers in the tie.  

Select SalesOrderID, CustomerID,

            DENSE_RANK() Over (Order By CustomerID) as RunningCount

From Sales.SalesOrderHeader

Where SalesOrderID>10000

Order By CustomerID

SalesOrderID CustomerID  RunningCount

------------ ----------- --------------------

43860        1           1

44501        1           1

45283        1           1

46042        1           1

46976        2           2

47997        2           2

49054        2           2

50216        2           2

51728        2           2

57044        2           2

63198        2           2

69488        2           2

44124        3           3

. . . More

NTile(n) will evenly divide all the results into approximately even pieces and assign each piece the same number in the resultset. A perfect example is the percent of 100 (like for an examination in University)  or a percentile of runners in a road race.

Select SalesOrderID, CustomerID,

            NTILE(10000) Over (Order By CustomerID) as RunningCount

From Sales.SalesOrderHeader

Where SalesOrderID>10000

Order By CustomerID

SalesOrderID CustomerID  RunningCount

------------ ----------- --------------------

43860        1           1

44501        1           1

45283        1           1

46042        1           1

46976        2           2

47997        2           2

49054        2           2

50216        2           2

51728        2           3

57044        2           3

63198        2           3

69488        2           3

44124        3           4

. . . More

One last example will bring these all together in one SQL Statement and show the difference between all four ranking functions.

--Ranking All

use adventureworks

Select SalesOrderID as OrderID, CustomerID,

            Row_Number() Over (Order By CustomerID) as RowNum,

            RANK() Over (Order By CustomerID) as Rank,

            DENSE_RANK() Over (Order By CustomerID) as DRank,

            NTILE(10000) Over (Order By CustomerID) as NTile

From Sales.SalesOrderHeader

Where SalesOrderID>10000

Order By CustomerID

OrderID     CustomerID  RowNum               Rank                 DRank             NTile  

----------- ----------- -------------------- -------------------- -------------------- --------------------

43860       1           1                    1                    1                    1

44501       1           2                    1                    1                    1

45283       1           3                    1                    1                    1

46042       1           4                    1                    1                    1

46976       2           5                    5                    2                    2

47997       2           6                    5                    2                    2

49054       2           7                    5                    2                    2

50216       2           8                    5                    2                    2

51728       2           9                    5                    2                    3

57044       2           10                   5                    2                    3

63198       2           11                   5                    2                    3

69488       2           12                   5                    2                    3

44124       3           13                   13                   3                    4

44791       3           14                   13                   3                    4

. . . More

In a few weeks, cell phone numbers are being released to telemarketing
companies and you will start to receive sales calls on cell phones.
Call this number from your cell phone 888-382-1222. It is the national Do
Not Call list. It blocks your number for 5 years. Please pass on,
Here is online form:

            RANK() works a lot like ROW_NUMBER() except that it will not break ties, you will not get a unique value for ties.

Select SalesOrderID, CustomerID,

            RANK() Over (Order By CustomerID) as RunningCount

From Sales.SalesOrderHeader

Where SalesOrderID>10000

Order By CustomerID

SalesOrderID CustomerID  RunningCount

------------ ----------- --------------------

43860        1           1

44501        1           1

45283        1           1

46042        1           1

46976        2           5

47997        2           5

49054        2           5

50216        2           5

51728        2           5

57044        2           5

63198        2           5

69488        2           5

44124        3           13

. . . More

Dense_Rank tomorrow...

SQL Server 2005 adds the functionality of a Ranking expression that can be added to your resultset that is based on a ranking algorithm being applied to a column that you specify. This will come in handy in .NET applications for paging and sorting in a grid as well as many other scenarios.

 The most basic new ranking function is ROW_NUMBER(). ROW_NUMBER() returns a column as an expression that contains the row’s number in the result set. This is only a number used in the context of the resultset, if the result changes, the ROW_NUMBER() will change. The ROW_NUMBER() expression takes an ORDER BY statement with the column you want to use for the row count with an OVER operator as shown here:

Select SalesOrderID, CustomerID,

            Row_Number() Over (Order By SalesOrderID) as RunningCount

From Sales.SalesOrderHeader

Where SalesOrderID>10000

Order By SalesOrderID

Results are shown here:

SalesOrderID CustomerID  RunningCount

------------ ----------- --------------------

43659        676         1

43660        117         2

43661        442         3

43662        227         4

43663        510         5

43664        397         6

43665        146         7

43666        511         8

43667        646         9

...More

Alternatively if you have an ORDER BY clause in your result set different than your ORDER BY in your ROW_NUMBER() expression

--Row_Number using a unique value, different order by

Select SalesOrderID, CustomerID,

            Row_Number() Over (Order By SalesOrderID) as RunningCount

From Sales.SalesOrderHeader

Where SalesOrderID>10000

Order By CustomerID --Different ORDER BY than in Row_NUMBER

The result is shown here:

SalesOrderID CustomerID  RunningCount

------------ ----------- --------------------

43860        1           202

44501        1           843

45283        1           1625

46042        1           2384

46976        2           3318

47997        2           4339

49054        2           5396

...More

If you choose the ROW_NUMBER() function to run against a non-unique column, it will break the tie and still produce a running count so no rows will have the same number. For example, CUSTOMERID can repeat in this example and there will be several ties, SQL Server will just produce a monotonically increasing number, which means nothing other than the number in the result set as shown here:

Select SalesOrderID, CustomerID,

            Row_Number() Over (Order By CustomerID) as RunningCount

From Sales.SalesOrderHeader

Where SalesOrderID>10000

Order By CustomerID

The result are shown here:

SalesOrderID CustomerID  RunningCount

------------ ----------- --------------------

43860        1           1

44501        1           2

45283        1           3

46042        1           4

46976        2           5

47997        2           6

49054        2           7

50216        2           8

51728        2           9

57044        2           10

63198        2           11

69488        2           12

44124        3           13

. . . More

Tomorrow RANK()...

Anyone who ever used illegal steroids should be banned from the game, or Pete Rose should be allowed in. Lacking any integrity at this point Bud Selig (and Kofi Annan) must resign. He presided over this sham and lets the players union walk all over him. We don’t have this problem in the NFL because we have a strong commissioner. In Baseball if you fail a drug test you have too many chances to hide it or try again. Baseball needs to get tough.

We did a New York Road Runners Race in Central Park on Saturday-15k (9.3 miles). The pace we did in this race would put Linda and I on a 4+ hour marathon. Antarctica here we come...

Last Name	First Name	Sex/ Age	Bib	City	State	Finish Time	Pace/ Mile
GURBISZ	KATHLEEN	F27	4288	NEW YORK	NY	1:23:52	9:01
VAROLI	LINDA	F33	5528	NEW YORK	NY	1:25:30	9:11
FORTE	STEPHEN	M32	4142	NEW YORK	NY	1:25:34	9:12