In SQL Server 2005, if you create a view utilizing the TOP (100) PERCENT and ORDER BY clauses, you will not get back your results in the order specified.

Example:

USE AdventureWorks
GO
CREATE VIEW CustomersOrderedbyTerritory
AS
SELECT TOP(100) PERCENT * FROM Sales.Customer
ORDER BY TerritoryID
GO
SELECT * FROM customersOrderedbyTerritory

You will notice that SELECTing from this view returns  a result set 'ignorant' of the ORDER BY directive. In essence, the optimizer ignores the TOP and ORDER BY clauses.  To achieve the desired result, there is a hot fix available from MSFT which introduces trace flag 168 and also requires you set the database compatibility level to 80 (SQL Server 2000).

Apparently, the problem also extends to Common Table Expressions (CTE) which use the same constructs such as :

WITH territorialcustomers AS
(
SELECT TOP(100) PERCENT * FROM Sales.Customer
ORDER BY TerritoryID
)
SELECT * FROM territorialcustomers

If you don't want to set your compatibility level to 80, you can use TOP (99.999999999999) instead. This somehow fools the optimizer to sort the data the way you want it to. Of course, it is only useful if your table contains less than 10 million records since the 0.000000000001% difference may start to show.

Well, if you insist on using the view, you can avoid the hot fix by using the ORDER BY clause in a table-valued function to retrieve your ordered data .  You can then wrap your view around this UDF.

I have not checked if this behavior has been carried forward to SQL Server 2008.

A substantial part of my development career was spent on the assembly line churning out code based on someone else specifications. There was always somebody else to handle the financial, administrative, marketing and sales aspects of  the whole manufacturing process - what I used to call the 'social' face of software. Now that I am running my own small software outfit and having gone through a few projects, the lessons seem to be coming my way fast and hard on a daily basis. Most of them are the 'social' kind and I will proceed to randomly throw them at you, mainly because I don't want to forget them.

1. Document all proposed requirements (features) to the most atomic level of detail possible. Ambiguous or general statements can fool you into optimism as far as the project timeline and budget is concerned. A specification statement like "The new system should allow employees to view their own data currently stored in the HR management system oracle database" cannot be the basis of a design. Break it down with questions like this:
   • Allow - May suggest security concerns. Will this be integrated security (implicit) or forms-based (explicit)?. Is there some form of identity management in place? Will we have to log (keep track) access to the system somewhere?
   • Employees - How many? Where are they located? Will each computer need some form of installation or setup procedure before using the system?
   • View - Where is the viewpoint? i.e. can the employee view the data while outside the LAN. What will they use to view the data? i.e a browser, a Windows forms application through a VPN? Do we need to encrypt the data? What obstacles lie between the user and the data? Firewalls,
   • Own - Are there people who can view other peoples' data? Who manages all these permissions?
   • Data - Which data exactly? i.e. Salaries, leave (vacation), loans, employment history (vertical and horizontal movement within the firm). How should each be displayed?
   • Oracle - Which version? Oracle client software installation? Who is responsible for this other database? Will they help with scripts to get the data out? Will we be allowed access to some of the data during development? Silently:(Do we have skills to deal with PL/SQL and ADO.NET?). Any documentation available?
   • If most of the above questions can be answered satisfactorily by the client, you may be able to do a better estimate than just a '1 man-week' guess. In fact, you might realize that this could very easily end up at slightly more than '1 man-month'. Remember that I am talking about one requirements specification statement.  
   • You might, of course, want to wear the 'architects' hat and start thinking of performance, availability, scalability, extensibility and interoperability at the statement level. These thoughts will not reduce the value of your requirements and may uncover other concerns and wishes not previously stated.
2. Communicate anything involving an agreed or suggested flux in resources and system features in writing (e-mail) as much as possible. Copy (cc) the communication to all the people relevant to that part of the project. After meetings, e-mail the client highlighting the main points agreed or disagreed during that meeting and ask for confirmation. You may look dumb and slow but this will cover your bases and avoid any ambiguity or disputes later.
3. Perform user acceptance testing as early as sanely possible. Unit and functional tests can be greatly complemented by the almost natural ability of a user to always crash a system. The fact that the software is incomplete only serves to gauge your feature prioritization skills. This could be a better indicator of where the project status instead of just checking off a feature list in a garage or on an Ms Project gantt chart. In any case, a client nod is worth more than any test case scenarios you can cook up. 
4. Demarcate or at least try to understand the boundary between the user and the client. These two entities are important to the closure of the project. The client will pay for the software. Users will use your software as often as necessary. Users will certify your system. The client may not pay if the user is not happy. Your will be supporting users later - despite what the contract says. When the client is the user, you may have an easier job navigating through the acceptance and invoicing phases. If they are not , get a good project liaison to grease the path between them.
5. Listen to all levels of the user spectrum. Every data entry and retrieval scenario should have a 'persona' and a real blood-and-bones user early in the project. Different people have different views of design. You may be able to merge these different views with your own or even learn something new. Not listening creates room for assumptions. Assumptions can spawn undefined elasticity in resources and features. Listening later only serves to widen the delicate gap between project initiation and final invoicing.
6. Know the accounting practices and procedures of the client. What they may consider fast payment processing may be too slow for you. Your bills and obligations may not dance to the same tune as your clients' workflow and traditions. This will also allow you to plan for cash flow.  
7. If you can, start creating user documentation (help files) early. Consider this as a validation of the requirements and testing activities. This also brings the end game nearer.
8. If there is a training clause in the contract, make sure the scope is clear. You may find yourself teaching PC skills, database administration, active directory services or even VB.NET for free or at a lower price than you would have if it was stated clearly from the beginning.
9. If you can help it, get somebody who knows their way around the establishment and has a goodwill for the project as a liaison. Canceled or missed appointments with various people, internal events or staff deployment changes in the client firm can affect your ship date. You may also need help bullying the accounts people - they usually respond better to internal pressure and nagging. A good rapport with the internal IT team can be good for repeat or referential business. It might just help to reduce your medical bills as you struggle to deploy your new software.
10. It may be that you got the job through a competitive process (a.k.a tender). Make sure you get to know why you won/lost the bid and what your competition had to offer in their technical and financial proposals. There will always be a next time and it is very likely that you will meet your competitors again. Such knowledge can only serve to improve your chances. 
11. In case you were wondering, I have little to say about software maintenance contracts. You will not need to do much if the agreement (SLA) is clear and your software approximates the elusive zero-bug state. On the other hand, it is very easy to spend all your money 'maintaining' the software as you respond to every user SOS and hunt down all kinds of bugs.

Whether my experiences are out of place is subject to the test of time and the pressure of future projects. In the meantime, they will serve as a reminder that the world is still interesting outside the confines of Visual Studio and the SQL Server query editor.

;-)

Do you routinely open new folders directly, either because you created one on your desktop, through a shortcut, or by using the new context menu in Visual Studio to explore the folders in your solution? Are you constantly adjusting the size of the Explorer windows because they are too large or too small for your tastes? Do you wonder why Vista does not just remember and open all windows at the same size?

Vista stores settings such as size, location, sort type, icon type, etc… for individual folders you open. Some of these settings can be replicated to other folders using Folder Options / View / Apply to Folders, but that method is not very reliable and no longer applies to all folders as it did in previous versions of Windows.  Vista can still later decide that your documents folder is suddenly a music folder, or your apps folder full of only executables is now a picture folder.

There are some registry settings you can apply that will take care of most of theses issues, except window sizes. I first discovered the settings through a link in the comments on Scott Hanselman's post about Vista’s erratic file type views. Later, after I purchased a new laptop I came across a better explanation on the My Digital Life blog. If you are looking for a way to fix this behavior, I would suggest going there first as this fix is an expanded version of it and requires those steps. Come back here before you open any new windows though. Go ahead… I’ll keep myself busy.

Ok, now that you have completed those steps, we can get that pesky window size issue fixed.

First, right click on your desktop and choose New / Folder.

Next, double click the new folder and drag it out or in to your preferred size. You can also set other properties of the window you want to be the default at this point. For instance, I like by default window view to be details view grouped by the type of item. The folder is empty so you will not see any sorting or grouping, but it will be saved.

Close the window and open the registry editor, type regedit.exe in the quick search bar of the start menu.

Important, The next steps involve editing your registry. The standard disclaimer applies. You can cause serious problems if you are not careful in the registry. Follow these steps though and your computer will survive.

Navigate back to the registry key:

[HKEY_CURRENT_USER\Software\Classes\Local Settings\Software\Microsoft\Windows\Shell\Bags\.

You should see a single numbered key under the Bags key. That key represents the settings for the folder you just closed. Open the ‘Shell’ key beneath it and you should see another key with a guid name such as ‘{5C4F28B5-F869-4E84-8E60-F11DB97C5CC7}’. The numbers may be different on your system.

Right click the guid key and choose export. Save the file to your desktop. Use any name you like.

Now that you have a new registry settings file on your desktop, right click the file and choose Edit. You need to make a single change. The second line in the file will be the path in the registry, it will look like:

[HKEY_CURRENT_USER\Software\Classes\Local Settings\Software\Microsoft\Windows\Shell\Bags\4\Shell\{5C4F28B5-F869-4E84-8E60-F11DB97C5CC7}]. 

The piece that needs to be changed is the number after the ‘Bags’ key. In this case it is 4, but your file may be different. Change the number to ‘AllFolders’ so that the line looks like:

[HKEY_CURRENT_USER\Software\Classes\Local Settings\Software\Microsoft\Windows\Shell\Bags\AllFolders\Shell\{5C4F28B5-F869-4E84-8E60-F11DB97C5CC7}].

Save and close the registry settings file. Now to get these settings back into the registry, double click the file and choose ok at the prompt.

Open any folder on your computer and it will look exactly like your first folder. You will need to reset any special folder views you had, such as picture folders. You can do that in two ways. Click the ‘Views’ button at the top of any window or right click in a window and choose ‘Customize This Folder’. Once you have made your change, close the window and Windows will remember the setting for that folder.

I just gave this new search engine a try.  It's called  Cuil (pronounce "Cool")  and its the invention of former Google engineer Anna Patterson.

Here's a pic:

Please update your readers! Ryan Rinaldi: .NET development served with a slice of sarcasm. (RSS)

Here's the link to the beta, go and get it:

Microsoft .NET Framework 3.5 Service pack 1 Beta

Like last year, I'm heading out to the MVP Summit in Redmond.  Here's some information on the event:

The upcoming summit, April 14-17, will feature a keynote from Microsoft CEO Steve Ballmer and closing by Chief Software Architect Ray Ozzie, in addition to several sessions, including topics ranging from social media for peer-to-peer support to new developments such as Silverlight mobile to Skydrive. Nearly 1,800 MVPs are planning to attend.

In conjunction with the summit, the Code Trip (www.thecodetrip.com), which launched from MIX and has been traveling around the western U.S., is an RV housed with Microsoft developer evangelists spreading the word about new technologies and meeting MVPs at each stop. The final destination of the 21 city trip is the MVP summit.