Saturday, September 16, 2006

Michael Arrington delivered a presentation to The Future of Web Apps conference held in San Francisco on September 13-14, 2006. According to Dan Farber, who covered the conference for ZD Net, Arrington included Squidoo in a "What were they thinking?" list of companies that he was "not proud that they exist."

Squidoo describes itself as "a new online platform and community that makes it easy for anyone to build a single page--called a lens--on a topic, idea, product or cause he is passionate about. These lenses in turn help finders get unique, human perspectives instead of computer-selected and often irrelevant search results."

Gather is a social sitebuilding startup that claims to be "a place to connect over common interests and passions, to engage in dialog or share different viewpoints." BusinessWeek's Liz Ryan named Gather one of the Top Ten Desktop Diversions of 2006 on March 27, 2006. I'm inclined to give a BusinessWeek columnist far more credence than a blog entrepreneur.

Note: The "best hosted wiki I've seen so far" is StikiPad, based on my first tests of the preceding and a few other hosted wiki sites. StikiPad offers exceptional editing flexibility, doesn't clutter free accounts with AdSense adverts, and has a very intuitive and attractive UI. Here's a link to the default page for my first StikiPad wiki. Click here for a WikiMatrix comparison of JotSpot, PBWiki, Socialtext, StikiPad, and Wetpaint. StikiPad offers free as well as paid—$4.95, $9.95, and $14.95—accounts. (StikiPad is based on the Ruby-based Instiki wiki clone and the Ruby on Rails framework.)

My impression of these wiki sites is that they are simply free (ad-supported) or paid Web site hosts with multi-person editing, comments, or both enabled. For example, PBwiki offers free, $9.95, $24.95, and $34.95 per month options. JotSpot (requires free registration to read) has $9.95, $24.95, $69.95, and $199.95 per month plans. Wikia requires a wiki to "have a large potential audience and be likely to attract enough editors to maintain the wiki." The preceding links open simple test wikis; here's a link to a more complete Wetpaint wiki.

Note: No hosted wiki that I've seen so far, including Socialtext and wiki.com, has features that come close to matching the capabilities of Microsoft's Windows SharePoint Services (WSS) 2.0 or 3.0, which is a free add-in to Windows Server 2003 SP1. Stay tuned for a future post comparing WSS 3.0 and online wiki applications.

Wiki sites enable multi-person page editing, but most wiki sites offer far fewer editing widgets than Squidoo. I've seen nothing so far to convince me that any of these startups have much—if anything—more to offer than Squidoo, Google Pages, or Blogger. For example, Blogger lets you enable team blogging. If you're willing to take the risk, you can enable multi-person editing of Squidoo lenses by disclosing your username and password to trusted colleagues. Alternatively, you can set preferences to Share My Squidoo and enable anyone to edit your lens.

Rick Segal's Take on Arrington's Squidoo Castigation

Canadian venture capitalist Rick Segal takes on Arrington's castigation of Squidoo in a trilogy of posts—Mike Arrington - Sit Down, Michael Arrington Responds, and The Good of Web 2.0. Squidoo donates a portion of advertising revenue after operating expenses to charity, and Squidoo Lensmasters have the option of donating their Ad Sense earnings to charity.

One of Segal's issues is Arrington's insistence on an accounting of Squidoo's donations before considering a retraction of his miscategorization of Squidoo. According to Squidoo's Report page's Squidoo-wide Stats section, Total Money for Charity was $7,194.03 from 40,282 Total Lenses as of 9/19/2006.

Note: Here's how Squidoo distributes its income: "We divide up the money we receive in a very public way. First, we pay our bills. That's direct out of pocket expenses like rent and servers and salary and benefits expenses (our CEO doesn't take a salary, and neither does our board of directors). Then, with no other deductions, we pay 5% of our post-expense revenue directly to the charity pool, 50% directly to our lensmasters and retain the rest to pay off investors and employees." (From Everything about getting paid, "11. Does Squidoo Make a Profit?")

Arrington's Squidoo-related posts and comments demonstrate to me that he harbors personal enmity for Squidoo and/or Seth Godin. The question for TechCrunch readers is the extent to which this animus biases Arrington's "Web 2.0" reviews.

Monday, September 11, 2006

SQL Server 2000 introduced the TOP n [PERCENT] operator to enable restricting the number of rows in the resultset of a view or inline table-valued function (TVF). In SQL Server 2000, starting the SQL statement for a view or TVF with SELECT TOP n [PERCENT] ... enables adding an ORDER BY clause to order the query's internal rowset so as to return the correct resultset. As a byproduct of that operation, views and TVFs returned resultsets sorted in accordance with the ORDER BY clause. A SELECT TOP 100 PERCENT query with an ORDER BY clause in SQL Server 2000 or MSDE 2000 views and TVFs returns all rows sorted. SQL Server Management Studio [Express] and the Microsoft Access Upsizing Wizard expect—and the Wizard depends on—this behavior. SQL Server 2005 [Express] does not respect the SELECT TOP 100 PERCENT operator or it's SQL Server 2005-only functional equivalent—SELECT TOP (100) PERCENT—and ignores the ORDER BY clause in views and TVFs. Thus resultsets are not sorted.ANSI SQL specifies that ORDER BY clauses are valid only in the top-most (outer) SELECT block of a query. But no ANSI SQL version supports the TOP operator, so the ANSI rules don't apply to SELECT TOP queries. Most SQL Server DBAs don't permit use of ordered views or TVFs, preferring to apply the sort with an ORDER BY clause in the SELECT block that invokes the view or TVF. Visual Studio and Microsoft Access developers commonly use the shortcut.As mentioned earlier, SQL Server 2005's corresponding TOP (100) PERCENT function also enables adding an ORDER BY clause to a view or TVF. However, the SQL 2005 query optimizer ignores the TOP (100) PERCENT and ORDER BY clauses and does not return a result set with the specified custom sort. The query optimizer "optimizes out" the TOP (100) PERCENT function as redundant, which deactivates the ORDER BY clause without warning. Fortunately, there's a workaroud that I'll discuss shortly.SQL Server Books Online's ORDER BY Clause (Transact-SQL) topic has a note that states:

When ORDER BY is used in the definition of a view, inline function, derived table, or subquery, the clause is used only to determine the rows returned by the TOP clause. The ORDER BY clause does not guarantee ordered results when these constructs are queried, unless ORDER BY is also specified in the query itself.

The upshot of this note is that there is no guarantee whatsoever that SELECT TOP ... ORDER BY ... query constructs will return rows in sequence specified by the ORDER BY clause. My experience—and that of most other commentators on this issue—is that SQL Server 2000 returns rows in the expected order for all valid TOP integer and TOP float PERCENT values, as does SQL Server 2005 for valid integer and all float values except100.The da Vinci Toolset Obscures the IssueThe Visual Data Tools (VDT)—more commonly called the da Vinci toolset—first appeared in the SQL Server 7.0 version of SQL Server Enterprise Manager (codenamed "Starfighter"). SQL Server 2005 Management Studio (SSMS), SQL Server Management Studio Express (SSMSX), Visual Studio 2005, and Access 2007 use the current da Vinci toolset version. The da Vinci toolset's query parser automatically adds a TOP (100) PERCENT operator when you save or execute an ALTER VIEW or ALTER FUNCTION definition that contains and ORDER BY clause in its SELECT block.
Note: SQL Server Express (SSX) SP1 offers SSMSX as an individual download or bundled with SSX SP1 in SSX with Advanced Services SP1.Here's an example of a TOP (100) PERCENT query (Access's Alphabetical List of Products) in SSMSX's view design window:

Notice that the resultset in the lower Results pane is ordered alphabetically by the ProductName column. However, when you right-click the Alphabetical List of Products item in Object Explorer and choose Open View, the resultset isn't ordered by ProductName (or any other set of column values), as seen here:
The sorted display of the Results pane is undoubtedly due to the SELECT query in the SQL pane being the outermost query at this point. This ambiguous presentation for views is likely to lead unsuspecting developers down the primrose path.

Note: SSMX and a Microsoft Access query from upsized Northwind.accdb is used as the example, because many more data-intensive application developers use (or will use) SQL Server (2005) Express (SSX) with SSMSX and Visual Studio Express editions or Access than SSMS and SQL Server 2005 Standard or Enterprise editions. (Visual Studio Express editions don't include the VDT.)The da Vinci Tools in the Access 2007 Project Designer
Microsoft Access 2000, 2002, 2003, and 2007 use the da Vinci toolset as the Project Designer for Access Data Projects (ADPs). However, the Access 2007 Beta 2 Technical Refresh version shown here appears to use a slightly different query parser. Adding a sort to a view or inline TVF adds the SQL Server 2000-style TOP 100 PERCENT operator (without the parenthesis for backward compatibility with the Microsoft Desktop Engine (MSDE) 2000.)
Note: Access 2007 Beta 2 TR won't display the contents of the Diagram or Grid panes if you or SSMS[X] add parenthesis to the TOP operator. Addition of parenthesis by SSMS[X] prevents round-tripping of view or TVF designs to Access 2007 without loss of Diagram and Grid panes.
Running the preceding query displays the unordered resultset in Access's Datasheet View, as shown here:
Access substitutes it's Datasheet View for SSMS[X]'s Results pane. The preceding resultset obviously isn't ordered by ProductName, so developers will know immediately that the sort wasn't applied.

Note: In Access 2007 Beta 2, clicking the Tools group's Run (!) button of the Function & View Tools, Design ribbon doesn't update the row order of an open Datasheet View. You must close and reopen the Datasheet View to see the effect of a change to the view or inline TVF row order.
A Simple Workaround for the TOP (100) PERCENT ProblemIf you must apply a custom sort order to views and TVFs, which is generally considered to be a hack, substitute TOP (VeryLargeInteger) for TOP (100) PERCENT. The largest integer that the query parser will accept is MAX(int) or 2147483647. This limitation appears to be for backward compatibility, because SQL Server 2005 converts TOP (n) values to the bigint data type.
Here's the view's new query in SSMSX:
In this case, the Results pane above reflects reality, as shown below:
The TOP 2147483647 operator also behaves correctly in Access views and queries. However, Access developers have the option to set the Order By property of a view or inline TVF toColumn1 [DESC][,Column2 [DESC] ... ]on the Data page of the ObjectName Properties dialog. This property specifies the ORDER BY clause of the outer query. Visual Studio developers and others might need to alter their TableAdapters or SELECT queries to apply an ex post facto sort order.

Note: SQL Server 2005 TOP (n) views are updatable if the view without the TOP (n) function is updatable.
Conclusion
It's surprising that the SQL Server tools group didn't alter the query parser to replace TOP (100) Percent with TOP (2147483647). The group also should have fixed—or warned users about—the ambiguous presentation in the Results pane for views.

The appropriate solution for SQL Server 2005 [Express] SP2 is to require SQL Server 2005's query processor to respect SELECT TOP (100) PERCENT ... ORDER BY views and TVFs by emulating SQL Server 2000 behavior, and change the party line by guaranteeing that SELECT TOP ... ORDER BY views and TVFs return a sorted resultset.

I haven't tested the workaround exhaustively, but it's likely to work in most or all cases. However, you're far better off from a performance and reusability standpoint to follow the ANSI SQL rules and apply the ORDER BY clause within the outermost query block, not in views or inline TVFs.

Tip: Clear at least the Application log and reboot before running the project. Otherwise, you'll find that Iexplore.exe will consume almost all available memory while it attempts to generate an RSS file with several thousand items.

Press F5 to build and run the project. If you have IE 7.0 RC1 or a late beta, the page appears as shown here:

If you're familiar with Dave Winer's RSS 2.0 spec, you'll notice some missing items—such as the top-level <rss version="2.0"> element—and non-conforming <publisher> and <author> tags under the <channel> element. <item> elements also have non-conforming <date> and <creator> tags, and dates that don't conform to RSS's requirement for the RFC 822 date/time format.

Adapting the Page for Feed Readers

IE 7.0 ordinarily displays RSS and ATOM files with a style sheet. For example, here's the start of the IE 7.0 feed page for the OakLeaf Systems blog as of this post's date:

I wanted to see how IE 7.0's style sheet would handle the feeds generated by Jim's project. The missing top-level <rss version="2.0"> element prevents IE 7.0 from applying its feed style sheet. I believe that other non-conforming elements might prevent some readers from displaying the data correctly. I also wanted to add hierarchical <category> tags to see if IE 7.0 would filter events by category correctly.

So I added the <?xml version='1.0' encoding='UTF-8'?> XML declaration and the <rss version="2.0"> element pair, then made other minor corrections and a few additions to bring the feed up to the RSS 2.0 standard. Here's the result before subscribing to the feed in IE 7.0:

Note: The five filter categories are EventLog, EventLog/Entries, EventLog/Entries/Application, EventLog/Entries/Information, EventLog/Entries/Application/Warning. Filtering works as expected.

And here's the transformed page after subscribing to the feed:

If you publish the Website to IIS, run Cassini on a fixed port, or just copy the current URL—http://localhost:1686/XLinqRSS/Default.aspx—for this example you can subscribe to the feed with any popular reader. For example, here's Dare Obasanjo's RSS Bandit open-source reader displaying the OAKLEAF-MS15 feed:

You can read an abbreviated version of the updated RSS 2.0 feed code here and see the modified Visual Basic 9.0 source code for the updated WriteRSS20 subprocedure here.

The dual Web role application has been running in Microsoft's South Central US (San Antonio) data center since September 2009. I believe it is the oldest continuously running Windows Azure application.

About Me

I'm a Windows Azure Insider, a retired Windows Azure MVP, the principal developer for OakLeaf Systems and the author of 30+ books on Microsoft software. The books have more than 1.25 million English copies in print and have been translated into 20+ languages.

Full disclosure: I make part of my livelihood by writing about Microsoft products in books and for magazines. I regularly receive free evaluation software from Microsoft and press credentials for Microsoft Tech•Ed and PDC. I'm also a member of the Microsoft Partner Network.