::Nathan's Blog

Disorderly Views

2008-08-05T00:51:44Z

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.

Lessons From The Trenches

2008-08-04T22:08:01Z

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.

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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.

Free Download of Refactor! For ASP.NET 2.0

2007-02-03T18:59:00Z

Mark Miller (DevExpress) is offering a free for all download of Refactor! for Visual Studio 2005. It includes 25 refactorings, nine of which are dedicated to ASP.NET development (the other 16 work in VB and C# code-behind files). There is also a full-screen training video available for your viewing pleasure.Having used the Pro version of the tool for some time now, I am happy to see that Mark has decided to make some of the features free for public consumption. The features that are available while working with ASP.NET 2.0 are:

Add Validator
Extract ContentPlaceHolder
Extract ContentPlaceHolder (and create master page)
Extract Style (Class) : Not available inside content pages
Extract Style (id) : Not available inside content pages
Move to Code-behind
Move Style Attributes to CSS
Rename Style
Surround with Update Panel: Does not modify the Web.Config file

T-SQL Wordiness

2006-11-03T01:02:44Z

T-SQL may not be a 'true' programming language but it sure has lots of language features that make manipulating data possible. I spend quite a reasonable amount of time writing T-SQL scripts; either in production or learning or sometimes while teaching. While doing this I have often found myself cursing at the amount of typing required to accomplish otherwise simple constructs. Here is a sampling what I am rambling about:

1. Separation of variable declaration and initialization. Put simply, I would love to be able to do the following, at least for scalar types

DECLARE @var int=0

instead of having to do it over two lines, thus

DECLARE @var int
SET @var=0

2. Creating database objects using IF ..DROP..CREATE. It is just involves too many keystrokes. Oracle PL/SQL has the CREATE OR REPLACE syntax. Why wouldn't the SQL Server team indulge us on this one? For those who are wondering what this is all about, if you want to create a table, you must check if it exists before issuing the CREATE statement. You could go about it like this

IF OBJECT_ID('myTable') IS NOT NULL
DROP TABLE myTable
CREATE TABLE myTable(col1 int)

Please, just give us this instead

CREATE OR REPLACE TABLE myTable(col1 int)

If I am not mistaken, somebody has already asked for this on the Microsoft Connect site. You may want to vote for that feature.

3. Is there any hope for a more compact loop construct instead of this?

DECLARE @var int
SET @var=0
WHILE @var<1000
    BEGIN
    SET @var=@var+1
    --Some code here
   END

You notice that the six uncommented lines are required to do a simple loop. This can quickly increase if you have more than one loop (a.k.a an 'inner' loop). Since I don't expect to see curly brackets in T-SQL anytime soon, what about a FOR...NEXT simple VB-like loop?

I may come off as a truly lazy DBA, but after exposure to other 'programming' languages, I feel that once in a while my productivity is impaired with this chatful state of affairs. I am not sure if there is anything in the ANSI-SQL standard that prohibits these wishes from coming true, but I am definitely sure that the SQL Server programmability team should think about it.

Note To Self: If wishes were horses, beggars would surely ride.

July Refresh of Books Online

2006-07-22T16:49:00Z

The July Refresh of the SQL Server Books Online is now available. This edition of the documentation is supposed to cater for all current editions of SQL Server including Express. Download

Office 2007 Beta 2

2006-05-24T18:20:00Z

The public release of Office 2007 Beta 2 is now available. Download

A login has got to do what a login can do

2006-04-26T17:36:00Z

As an SQL Server admin, there are times when the combination of many users, groups, roles, logins and the various permissions that can be given to each to do stuff on various databases and objects within your server may just be too confusing. Just like the previous sentence.

One way of coming out of this mess unscarthed is to rigorously document every permission granted to every login/group and keep track of it all. I know many people who don't. I don't either. Since you basically can't do much with SQL Server unless 'explicity' granted or 'implicitly' allowed by virtue of your membership in the public or other groups/roles, I am normally much more worried about what I have GRANTED my logins rather than what I have explicitly DENIED them. In other words, most people would love to know who CAN do stuff rather than who CANNOT do stuff. So what else can you do about it? I wrote a script (stored procedure actually) to show me all the stuff that a given user/login can do within a particular database and on its hosting server. Here we go.

CREATE PROC [dbo].[loginperms]

@login varchar(128)

SET NOCOUNT ON

IF (SELECT is_disabled from sys.server_principals WHERE name=@login)=1

SELECT 'The login <' + @login + '> is disabled'

ELSE

SELECT [Object Class]=class_desc, [Object]=CASE class WHEN 100 THEN @@SERVERNAME

WHEN 105 THEN (SELECT name FROM sys.endpoints WHERE endpoint_id=major_id)

ELSE object_name(major_id) END ,[column]='',[Permission]=permission_name,

[Permission Type]=CASE spr.type WHEN 'U' THEN 'Direct' WHEN 'R' THEN 'Inherited' END

FROM sys.server_permissions spm JOIN sys.server_principals spr

ON spm.grantee_principal_id=spr.principal_id

WHERE spr.type NOT IN('C') AND is_disabled=0 AND spr.name=@login

UNION

SELECT [Object Class]=class_desc,Object=CASE class WHEN 100 THEN @@SERVERNAME

WHEN 105 THEN (SELECT name FROM sys.endpoints WHERE endpoint_id=major_id)

ELSE object_name(major_id) END,'' ,permission_name,

[Permission Type]=CASE spr.type WHEN 'U' THEN 'Direct' WHEN 'R' THEN 'Inherited' END

FROM sys.server_permissions spm JOIN sys.server_principals spr

ON spm.grantee_principal_id=spr.principal_id

WHERE spr.type NOT IN('C') AND is_disabled=0 AND spr.name='public'

UNION

SELECT [Object Class]=CASE class WHEN 1 THEN (SELECT REPLACE(type_desc,'_',' ') FROM sys.all_objects WHERE object_id=major_id) ELSE class_desc END ,

Object =CASE class WHEN 0 THEN DB_NAME() ELSE object_name(major_id) END ,

[Column]=CASE minor_id WHEN 0 then '' ELSE COL_NAME(major_id,minor_id) END,permission_name,

[Permission Type]=CASE dpri.type WHEN 'U' THEN 'Direct' WHEN 'R' THEN 'Inherited' END

FROM sys.database_permissions dperm JOIN sys.database_principals dpri

ON dperm.grantee_principal_id=dpri.principal_id

WHERE dpri.name=@login AND state_desc LIKE 'G%' AND major_id>=0

UNION

SELECT [Object Class]=class_desc, Object=CASE class WHEN 0 THEN DB_NAME() ELSE object_name(major_id) END,

[Column]=CASE minor_id WHEN 0 then '' ELSE COL_NAME(major_id,minor_id) END,

permission_name,[Permission Type]=CASE dpri.type WHEN 'U' THEN 'Direct' WHEN 'R' THEN 'Inherited' END

FROM sys.database_permissions dperm JOIN sys.database_principals dpri

ON dperm.grantee_principal_id=dpri.principal_id

WHERE dpri.name='public' AND state_desc LIKE 'G%' AND major_id>=0

You may want to use the procedure like this:

EXECUTE

[dbo].[loginperms] 'DOMAIN\user'

EXECUTE

[dbo].[loginperms] 'sql_login'

I know, it is even easier get this information using the brand new SQL Server Management Objects (SMO). Maybe I will blog about that on another day. In the meantime, let's just be happy with T-SQL.

SQL Server 2005 Books Online April Refresh

2006-04-24T17:26:00Z

The delayed SQL Server 2005 Books Online (BOL) April 2006 refresh should now be up and ready for downloading.

Get it here

Mobile and Embedded Devcon (MEDC) 2006 Europe

2006-04-13T21:37:00Z

Details:

Event: Mobile and Embedded Devcon (MEDC) 2006 Europe

Dates: 6th-8th June 2006

Place: Nice, France

Sessions:

Using Microsoft Windows Embedded Products to Bring to Market a Broad Range of Devices
Windows CE Overview
What's New in the Next Version of Windows CE Tools
Writing Windows CE Services
Stabilizing the Platform
Inside the Windows CE Kernel
Windows CE Memory Tools for Native Code
Windows CE Performance Tools & Techniques
How to write a Windows CE Boot Loader (combine with Secure Bootloader)

Discounts are available if you register before the end of next week. Get more details at http://www.microsoft.com/europe/medc/

Dubai DevCon

2006-04-13T18:09:00Z

What are you doing on the Labour Day weekend? You can attend the First Developers Conference in Dubai and maybe do some shopping while you are there. There will be stuff on ASP.NET, Office 2007 and Vista. Microsoft Middle East has put up the details on their website. Go and register.

PostXing Beta

2006-04-12T18:33:10Z

I just downloaded Chris Frazier's PostXing Beta (2.0.6068.2) and I am using it post this offline. If you can read it, then it works and it rocks!

I was totally unable to use IMHO Instant blogger despite Ryan Rinaldi's suggestions. This might just change the way I've been blogging.

Talking Point

2006-04-10T22:18:00Z

Over the weekend I stumbled across (and survived) the SQLDownUnder Podcast by Greg Low [MVP]. The guy really knows how to make a professional conversation about a difficult topic sound like a chit-chat at the coffee shop. He has already done 13 shows interviewing fellow SQL MVPs {Itzik Ben Gan, Kelan Delaney, Adam Machanic, Tom Moreau, Bill Grazziano etc.} and they all came out superior to anything I have heard on SQL Server for a long time.

When are Steve Kass and Kimberly Tripp stepping up to Greg?

By the way, there is another SQL Server broadcast at the SSWUG (SQL Server Worldwide user Group) site. This one is also being done by another SQL Server MVP, Chuck Boyce. Check it out.

Let's keep talking.

African MVP Links

2006-04-10T20:17:00Z

Here is a list of the 15 MVPs from Africa and the 'spots' where they hang out:

The Active Directory Squad

Evan Erwee - Directory Services
Niyi Omotoyinbo - Directory Services

The C# crowd

Ernst Kuschke - C# - [http://dotnet.org.za/ernst/ ]
Peter MacMahon - C#
Hilton Giesenow - C# [http://dotnet.org.za/hiltong/ ]
Armand du Plessis - C# [http://dotnet.org.za/armand/ ]
Francois Bonin - C# [http://www.francoisbonin.com]

The SharePoint Man

Arno Nel - Sharepoint [http://arnonel.com/blogs/arnonel/default.aspx]

Everybody else!!

Pieter Germishuys - DirectX [ http://www.sadeveloper.net ]
Chris Quirke -Shell/User [http://cquirke.blogspot.com ]
Willy-Peter Schaub - Team System [http://dotnet.org.za/willy/default.aspx]
Edgar Okioga - Visual Basic [http://www.africadotnet.org ]
Conrad Akunga [http://www.africadotnet.org ]
Almero Steyn
Onawale Clement Oladapo

Check them out if and when you have time.

LUG Award: Least Useless Guy

2006-04-10T16:58:00Z

Guess what? Somebody decided that I should join the ranks of guys who spend all their disposable time online helping chaps/chapesses who spend their working time online looking for answers to SQL Server problems. Maybe somebody at Microsoft just doesn't like me. But I am happy to inform the rest of the world that I just became an SQL Server MVP (on April fool's day nevertheless!). When I received the e-mail from the MVP lead, I almost thought it was a prank until I saw the award package land on my desk from the local DHL office.

I will stop asking where Itzik Ben Gan or Kelan Delaney are blogging coz I can now harrass them when they pop up in the MVP newsgroups. Without too many promises, I am sure I will become a better (more regular) blogger and a better(more knowledgeable) DBA. I might even start thinking of writing something serious one of these days.

Although I am filled with enthusiasma (Rory Blythe will crucify me for plagiarism), it can be pretty lonely being an MVP in Africa. We are only 6 in the whole of West, East and Central Africa (WECA) and almost out of reach of most MVP 'connectedness' programs like the summits, regionals, and most serious events in both the geographical and financial dimensions. Hey, we are the emerging market and I am sure the other MVP's may have felt the same at one time.

Thanks to all the guys who made this possible. You are my MVP's (Most Valuable People).

Take 5

2006-01-12T10:43:00Z

From the recently announced MVP awardees list, I see that Africa now has 5 MVPs (I think they are all in development-related categories) outside South Africa. It has taken a very long time [more than a decade]for Microsoft to appreciate and award people in the ever-growing developer community in the WECA (West, East and Central Africa) region. Granted, Africa does not offer much in terms of sales/licenses figures, but I feel that that MSFT is beginning to take serious notice as they push into what they are calling "emerging markets".

Congrats to all the new African MVPs!