Tony Davis

The Battle Against Lawless Database Design

Tony Davis — Tue, 25 Nov 2008 22:38:00 GMT

One of the most entertaining sessions I attended at the recent PASS conference was "Much Ado: A Panel discussion about Nothing". I never fail to be amazed by the passion and energy that a bunch of database guys will put into a debate about "don't know" (null), and its many nuances.

This debate inevitably can lead to the conclusion that some developers really just don't get null, or three-value logic, or relational databases in general, and yet get put in charge of database design. The result is fundamental design flaws, poorly performing applications, and over-stressed and prematurely-aged DBAs.

From its inception, SQL Server has been relentlessly promoted as the "self maintaining" database. Anyone can use it! Heck, it even tunes itself! You don't need one of those expensive DBAs! No it isn't, no they can't, no it doesn't (it collects some statistics, which is far from the same thing), and yes you do.

SQL Server has travelled a very long way from its "desktop" origins, and yet many IT managers persist in viewing SQL Server applications almost exclusively in terms of the application and so put the whole design in the hands of .NET developers. Many of these developers are database-savvy, but if they are not, the result is:

Poorly normalized databases
Data type abuse (for ultimate flexibility, try sql_variant (max)!)
Absent or badly-written where clauses
No stored procedures, constraints or sometimes even keys
Direct table access and dynamic SQL
An indexing desert….or "chaos indexing"
A null desert….or "chaos null"

The list could go on. Enough is enough. It is time to draw a line in the sand. I encourage you to join the Battle Against Lawless Database Design (or project Baldy as I affectionately call it). Its goal is to encourage Microsoft to put out a strong message of support for SQL Server development DBAs. They need to state, loudly and clearly, that any application built on SQL Server will succeed or fail according to how effectively it uses the database, and that their development team must include someone who knows SQL Server and relational databases intimately, and who is responsible for making sure the database is correctly designed and tuned.

So the next time you encounter one of the problems listed above, cross your arms, adopt a stern posture and say…enough is enough.

I hope to get Joe Celko as the first signed-up member ;).

As always, comments, objections and battle volunteers are greatly appreciated. The best post, added as a comment to this blog, will receive a $50 Amazon gift voucher.

Cheers,

Tony.

PASS 2008 Keynote, Part 2: Kilimanjaro, Madison and Gemini

Tony Davis — Thu, 20 Nov 2008 01:22:00 GMT

Ted Kummert opened by reiterating the message of Microsoft's increased level of support for the PASS conference, and introduced members of the SQLCAT team who were in attendance. Ted's data storage division needed to consider four main "pillars" of development:

Enterprise data platform -- Ted mentioned security advances and a few other things I missed. Ted is not as captivating a speaker as Wayne.
Beyond relational -- supporting new data types such as spatial, filestream column types to deal with large volumes of unstructured data
Dynamic development -- across VS 2008, .NET 3.5 and SQL 2008 introduced entity data model top allow you to deal with code in terms of real business objects (customers etc).
Pervasive insight -- getting business value out of data via advances in their BI and data warehouse capabilities.

Ayad Shommout , a lead technical DBA for CareGroup, came on stage to talk about their experiences moving to SQL Server 2008.

It was disastrous!!!

No, only joking. It was completely seamless…25% performance increase without changing any code, enhanced productivity and great features…the three singled out for special mention were policy-based management, transparent data encryption, and auditing.

Ted moved onto expand on the success story of SQL 2008:

1 million downloads of SQL 2008 RTM
2500 partners offering solutions
Leading performance benchmarks
Fewest vulnerabilities
Fastest adoption.

The figures sound impressive, but where is the stampede of eager SQL 2008 adopters? My impression so far is that people are in no hurry to move over despite the fact that, as I previously reported, there are smaller barriers to migration this time.

SQL Server 2010 (Kilimanjaro)

So where is SQL Server going with Kilimanjaro? In five words: scale up and scale out. Ted spoke of the "innovation" coming in the first half of 2010, with Killimanjaro, and this presaged 30 minutes of quick-fire demos that introduced a lot of new projects in a "high impact, low detail" kind of way, with new acronyms flying thick and fast..

Scale out: Project Madison

Jesse Fountain talked about scaling out, in the form of the Data Warehousing project, "Madison", based on acquired technology, DATAllegro.

In 2 weeks, generated 1 trillion rows (150 Terabytes) for the database and then distributed that data across multiple nodes. He demonstrated monitoring 25 nodes, each with 8 cores, and the speed of executing reporting queries against this vast amount of data, in this "scale out" architecture.

SQL Server Fabric

The next theme was manageability and what was coming in Kilimanjaro to improve this. On came Dan Jones. To address multi-instance management, they were introducing SQL Server Fabric, with a Fabric control point to make it much easier to manage at large scale.

Dan opened Fabric Explorer from Management Studio, providing storage utilization history on an instance-by-instance basis. On each instance can define policies to define over and under-used resources.

In Visual Studio, he demonstrated how to generate a "DAC Pac" which defines the schema, but also deployment requirements. The DBA imports this into an application library, can refine the policies at that stage, and deploy it to a Fabric.

SQL Data Services

Ted introduced SSDS as the data platform element of Azure Services, extending the data platform to the cloud. First public CTP of SQL Data Services is now available and he encouraged everyone to check it out. And that was that on SSDS! I was wondering what was up with SSDS before this, and I'm definitely none the wiser now.

Managed self-service BI reporting: Project Gemini

Sounds impressive, eh? Building on Report Builder, Kilimanjaro introduces Gemini, "an in-memory, componentized model for building reports"….a merging of the capabilities of Analysis Services and Excel. Donald Farmer came on stage sporting angelic wings to demonstrate.

Things were moving so fast at this stage, that it was hard to keep up. Donald showed how to "mix and match" data from the database with locally stored data from Excel, sorting and filtering 10 million rows of data in Excel in seconds, performing pivot table analysis and various ways to visually filter the data, and then publish to SharePoint for public consumption.

It definitely looks very impressive, and is something we're gong to hear a lot more about. And doubtless it will keep consultants happy and occupied for many years to come.

TD.

PASS 2008 Keynote, Part 1: Wayne Snyder

Tony Davis — Wed, 19 Nov 2008 23:11:00 GMT

This is the first full day of the PASS 2008 conference in the wonderful city of Seattle. After a great pre-con with Bob Beauchemin, a lively SQLServerCentral party and a severe attack of jet lag, I emerged bleary-eyed for the Wednesday morning Keynote address

Usually I like to listen, digest and then blog, but through my press association I have signed up as an industry blogger and this will be my first live blogging experience.

The keynote opens spectacularly enough. The lights dim to the loud refrain of "Born to be wild" and then the spotlights rise on Wayne Snyder, PASS president, sitting astride an impressive hog (motorbike).

Wayne reports on the "explosive year of growth for PASS", with the belated arrival of the new PASS sqlpass.org website, the introduction of free PASS membership (make it free…who would of thought that would work?!) and a 70% increase in the number of PASS chapters to 136.

He stressed the fact that the community run PASS – they choose the speakers for the conference, rate them to determine whether they come back again – but also that PASS is developing a "renewed and closer" relationship with Microsoft. There is one Microsoft developer for every ten attendees at the conference this year, which is impressive and something that would not have happened if Seattle wasn't quite near Redmond. 100 people took up the opportunity to get SQL Server 2008 certified while at the event and I'm not sure if that's good or bad.

After an interlude in which Wayne introduced the PASS board and those up for election, he returned to the growth theme. In a year when training money is low, jobs are soft most conferences are down, PASS had bucked the trend. There are 2445 registrations this year, up from 1528 last year, which is heartening to hear.

Wayne urged everyone to get the most out of the summit. Talk to MS developers, fellow attendees, third party exhibitors and the person sitting next to you at lunch. Sound advice. He signed off by introducing Ted Kummert, Vice President of Data and Storage Platform Division…

TD.

Building Technical Communities

Tony Davis — Tue, 11 Nov 2008 19:19:00 GMT

The SQLServerCentral technical forums have matured and evolved over the course of many years. They were initially stoked by the sweat and toil of a small number of experienced DBAs, including Steve Jones, who were willing to dedicate a substantial amount of their day to answering questions. Slowly, the community grew as more people took on the burden of answering questions and adding to discussions. They didn't do it for personal recognition, just the satisfaction of helping out some fellow professionals along the way.

Until recently, I was convinced that this was the only way to grow a genuine community of users.

However, a new breed of "web2.0 community" has been emerging that aim to get anyone and everyone contributing. These "people-powered" principles have been successfully applied to customer support, community weblogs and technical Q&A sites.

Contributors often receive "peer" approval for their efforts in the form of ticks and badges, or disapproval in for the form of a negative vote (the metaphorical thumbs-down).These sites spring up overnight and before the site is even out of beta, they are a hive of activity and rapid fire responses.

It would seem that sites such as StackOverflow are so compelling that numerous experts immediately jump on board, and so the "tipping point" is reached far more rapidly than with conventional models.

There is a niggling worry, though. Whereas one can argue that everyone's opinion is of equal value, it is more difficult to believe that expertise is so widely distributed. Recently, I've read about several cases of people getting misleading advice from one of the numerous user-powered medical websites that have sprung up. I'm certain that the same thing happens in technical communities, and also that the "web2.0" style ones are far more prone to it than traditional forums.

In a forum, approval or disapproval takes the form of a discussion (a thread) where you're required to state your case clearly, and with proof, and so is subject to true peer review. You cannot correct someone else's advice anonymously. Hitting a "tick" or "thumbs down" button requires no such effort and plays to the "herd instinct": applaud the "leader" when others do so, and "go in for the kill" when you spot a straggler.

Although I was initially awed by the sheer interactivity of the new technical formats, I'm beginning, on closer examination, to suspect that the founders of SQLServerCentral had it right first time around. It is difficult to short-cut the long painstaking road to building a great expertise-driven community. Or am I a young fogey? I'd be very interested to hear your thoughts. As always, add your comments to the editorial blog, and the best comment will receive a $50 Amazon voucher.

Cheers,
Tony.

Reasons to Deprecate

Tony Davis — Fri, 24 Oct 2008 20:39:00 GMT

I'm happy to see features and services of SQL Server deprecated by Microsoft if it is for a good reason. Good reasons include conformance with SQL Standards or rejection by the community of users; I consider Microsoft's commercial convenience to be a bad reason.

I suspect that everything possible has been said about Microsoft dropping Notification Services in SQL Server 2008. It is unprecedented for a service to be removed without an adequate substitute. Actually, there is an unsupported pre-release version of SQL Server 2005 Notification Services Components Package RC1 that has been altered to work with SQL Server 2008, but you have to download it separately. Its fate is sealed, and there is little cheer for the folks who have built complex systems around it, beguiled by the siren words of the Microsoft Marketing Department.

DMO is another SQL Server component that is being phased out. Poor DMO is still hanging on there by its fingernails, however. Microsoft hates it, but any users who have lots of old scripts still need it and like it. The 'softies' keep telling us we don't need it any more now we have Powershell and SMO; but to most DBAs, Powershell is a strange, unintuitive language cast in the UNIX mentality.

The two services are deprecated, but for entirely different reasons. Notification Services wasn't liked much by users, and take-up was poor. It was badly designed, and there has been a smell of death around it for some time. The WMI event model in SQL Server 2005 that used it was absurdly complicated, and it was hurriedly superseded by a new and simpler event handling system, namely Extended Events (XE).

DMO, on the other hand, was deprecated because it was inconvenient. Users liked it because any scripting system that could use OLE automation could use it. Even Perl could be used to automate SQL Server administration tasks. There was a botched attempt to allow DMO's replacement, SMO, to use OLE Automation, but SMO emerged as a .NET Assembly only. Although a positive spin was put on it, the truth was that if you wanted to use SMO, then you now had to write compiled applications with C# or VB.NET, or grit your teeth and learn Powershell. DBAs weren't fooled, and weren't about to re-write hundreds of scripts just because Microsoft didn't want to maintain a scriptable version of SMO.

DMO lives because there is no viable alternative.

Cheers,

Tony.

What's Up With SSDS?

Tony Davis — Tue, 14 Oct 2008 18:48:00 GMT

What's up with SQL Server Data Services (SSDS)? Presumably, we'll get to hear more at PDC on October 27^th, when the SSDS team present their plans, but the current signs aren't encouraging.

When Microsoft announced their "database services in the cloud" at the launch of SSDS, we blinked with amazement. The prospect of being able to use SQL Server remotely, from any website, was intriguing. However, on closer inspection, the Authority Container Entity (ACE) architecture, replete with "flexible entities" and "flexible property bags", smacked of a new mysticism and served to mask the fact that it was just another EAV database.

The marketing blurb spoke of the freedom of not requiring database schemas, as if this was a cause for rejoicing rather than fear: 'Simply add new attributes to your data set when needed, and the system will automatically store, index, and query your data accordingly'. It all seemed a long way removed from the rigors of real commercial applications, and certainly at the 'wacky' end of the scale when compared to Amazon SimpleDB and Google App Engine (GAE).

Who, we wondered, was desperate to get hold of woolly, schema-less authorities, consistency units, containers and entities with only five primitive data types offering neither foreign keys nor joins?

Almost nobody, as it turned out. The Beta program was originally specially restricted, presumably in fear of people being crushed in the stampede. They needn't have worried. The answer to IBM's 'Blue cloud' turned out to be a 'black cloud' of depression. The beta was under-subscribed and later referred to as 'limited'. If one gauges public interest in SSDS from the SSDS forum, then it seems that the public is looking elsewhere.

There has been a degree of backtracking from the initial, heady days of radicalism but in many areas SSDS is still shrouded in vague and largely unfulfilled promises. For example, joins, which would imply support for foreign keys, are promised but there is no sign yet that we'll be allowed schemas after all. The promised LINQ library has failed to materialize; there is still no role-based security; we are promised a 'convergence' with Astoria (ADO.NET Data Services), JavaScript Object Notation (JSON) and the Atom Publication Protocol (AtomPub or APP), but the subject of client tools is still very hazy, with all developments now seeming to be directly using REST and SOAP as the interface.

If only SSDS had simply been introduced via an (Astoria) front end, the story might have been different. Rather than having to hack together a data layer using REST/SOAP, and committing to a lot of work that it would be difficult to roll back from, a developer could simply use Astoria and SQL Server for development work and then switch over to SSDS without changing any of their code. This would have provided a simple and "safe" migration path from on-premises database (SQL Server) to cloud-based (SSDS). At this point, the advantages of shared database-server resources kick in, in terms of the scalability, availability and consistency of their data tier, as well as the ability to expand capacity incrementally and to manage spikes in usage.

As always, we welcome your thoughts. If you think SSDS is wonderful, and its developers 'rock stars', if you've used the early releases of SSDS and think we're way off the mark, then please let us know about it. The best comment will receive a $50 Amazon gift voucher.

Cheers,

Tony.

And so it came to PASS...

Tony Davis — Tue, 30 Sep 2008 17:10:00 GMT

On 18-21 November this year, in the great city of Seattle, PASS will be holding their 10^th annual conference. If you work with SQL Server, you should attend if you possibly can. It's an independently-organized summit, but with the hand of Microsoft firmly up its sweater. This means plenty of real-world technical sessions from community DBAs who are prepared to "tell it like it is" rather than just give the rose-tinted Microsoft view. It also ensures a healthy attendance of Microsoft developers (one for every ten attendees this year, apparently!) who you can grill for inside information on SQL Server. You can even get SQL Server 2008 certified while at the event, if you so wish to spoil the experience.

PASS, unfortunately, seem to be difficult to deal with and short on information. The PASS conference has a reputation for being poorly organized. For example, there is still only very sketchy session information on the website. Several authors and speakers I've talked to have started comparing unfavourably the experience and expense of the PASS summit with that of emerging community events such as IndyTechFest and SQLSaturday, which are free to attend and often very well-organized. Luckily, PASS has been undergoing some fairly major changes over the past year, with a new management structure, new website, a new package to help people start their own local user groups, free PASS membership, and so on. So, things may well improve, and the PASS summit remains a compelling event, as long as you know how to get the most out of it.

Set Specific Goals for improving the performance of specific parts of your current system, using knowledge you'll gain at the conference. And then go home and implement them.

Talk to People! I've met people who go to all the keynotes, diligently mark out all the sessions they want to attend and spend the week, heads down, trudging between each one. It is not enough. Talk to Microsoft SQL Server developers about specific technical issues. Talk to the tool vendors about possible alternative solutions. Most of all, talk to your fellow DBAs over lunch, in birds-of-a-feather sessions and at the bars and parties. It can help you develop a far-reaching support network.

Don't be ashamed to Party. In my experience, when you bring together, in a relaxed environment, a group of like-minded professionals, they can produce some of the best insights into the real problems DBAs face, and how to handle them. A meeting in the bar with a few fellow DBAs can be just as productive as a conference session or keynote.

Have you had dealings with PASS as a conference attendee, volunteer or speaker? Or in setting up a local users group? What they do well? Where they need to work harder? Have you got any advice on how to get the most benefit from the conference? Please let us know. As usual, the best comment on the blog will win a $50 Amazon voucher.

Cheers,

Tony.

Application Usability and Standards

Tony Davis — Tue, 16 Sep 2008 18:02:00 GMT

Office 2007's 'ribbon UI', now optimistically renamed 'Office Fluent User Interface', was an act of strange corporate madness by Microsoft.

This may seem an unfair statement, since it looks like a genuine attempt to make the application easier to learn for the novice. However, it broke the rules, and signaled that Microsoft no longer cared about maintaining a consistency in the look and feel of all Windows applications.

History tells us that it is a bad idea to change the interface of an established word-processing application. A similar act of folly caused the demise of Wordstar, for example. Word's menus and shortcuts had been learned by its users for eighteen years, so forcing them to adopt a different metaphor, without the choice of the 'classic view', seems reckless. At least we could switch off the ridiculous animated paper-clip (TFC), their previous attempt to dumb down their flagship applications. However, unless you buy one of the many applications that are designed to put back the classic menus, you are condemned to haunt the Ribbon UI (TFR), looking for that elusive Word Count menu item.

Simple-Talk uses Microsoft Word as the de-facto standard for article submission, and most authors I've spoken to are united in their dislike of the new ribbon UI. It may help the novice, but those of us who have the old menu tattooed into their cerebellums are disinclined to thank Microsoft for suddenly changing all the rules.

Application developers who are creating Windows applications have certain constraints to which they have to work. This has been a consensus amongst application providers since Xerox developed the 'Windows' computer in 1981. We work to strict style guides and deviate only when necessary; this is one of the foundations of good user-interface design.. Leaving to one side the obvious things such as context popup menus or resizing, Windows applications must have a menu, and the menu layout and structure has to adhere to standards. It also has to have keyboard shortcuts so that you can use it without a mouse.

There are good reasons for all this: if all Windows applications work the same way, then the end user can comfortably use several applications at once, and there is less need for retraining. This is in everybody's interests, because the cost of retraining in the corporate setting is one of the highest costs in rolling out an application.

If there is to be anarchy over even the simplest elements of consistency in the user interface, then there is little point in having Microsoft Windows. Following the lead of Office 2007, we can all adopt our own user interfaces, without worrying too much about the cognitive dissonance in the end user. Or we can all use Adobe Air instead.

Nobody objects to innovation: we need to select the good new ideas in user interface design and use them. However, what I and many other do object to is having the established UI standards torn up at the drop of a hat, and the 'new ideas' forced on us without the option of being able to turn them off. To do that is to abuse your monopoly.

Am I being unkind to Microsoft about this? Should we now rewrite all our applications to make them work the same way as TFR (the 'fluent' Ribbon)? Does anyone know where Word Count has got to? What is the point of adhering to standards if the industry leader doesn't want to conform to them? Please let us know: The best comment on the blog will win a $50 amazon voucher, of course.

Cheers,

Tony.

Application Usability and the JFDI Button

Tony Davis — Tue, 02 Sep 2008 20:14:00 GMT

It is strange how we, as software developers, get caught up in using conventions in applications that seem to be designed merely to irritate the users. Why do we do it? I think that it is because we tend to think that whatever Microsoft does is best practice.

One of the worst habits is to have unnecessary pop-ups and dialog-boxes. Vista is horrible in pursuing this vice. Even in response to the seemingly most straightforward of request for action, it apparently cannot resist subjecting the user to an onslaught of silly, nannying dialog boxes. "Are you sure you want to do xxx?" Yes…Click. "This requires your authority to yyy?" YES, I know…Click. "You are about to zzzz, click OK to Confirm" Yes, Yes, YES!!! For goodness sake.

The worst of these dialog boxes pop up after a short delay, usually at the beginning of a long copying job, and just after one has nipped off to the pub for lunchtime refreshment. One returns an hour later only to find that the process hasn't even started and instead the dreaded "Are you sure you wish to overwrite xxxx?" dialog box is gazing limply at you from the screen.

Phil Factor suggested to me that every application or operating system should have a JFDI button next to the 'OK' and 'Cancel' button, meaning 'Just Flaming Do It!'…or words to that effect. This button can be clicked aggressively, and would force Windows to bypass any further wearying questions that it might have liked to ask you before actually doing the FO (Flamingly Obvious). The forceful punching of the JFDI button should be accompanied by a new Windows sound; perhaps a sharp slap followed by a squeak would be appropriate.

Probably the most ubiquitous and grisly example of this excessive nannying is the "cute" help feature. It is bad enough to have a 'Welcome to xxxxx' message from an inanimate box. Surely there is no-one out there who actively enjoys being given unhelpful advice by an animated paper-clip or cartoon dog? Mercifully its influence seems to be diminished slightly nowadays, but there are still numerous third-party applications that can't seem to resist the allure of the playful animation. They are great for presentations (well, actually, great is overstating it) but awful in software one has to use as part of one's everyday work, where their appeal rating reaches zero after about day 2 of use.

I loathe familiarity from a machine. I'm faintly irritated by talk of 'My this' or 'My that'. I openly flinch when my operating system tries to engage me in conversation. Just do what it is I'm asking you to do….now!

Perversely, of course, at times when you actually could do with a hint or pointer, the nannying application that seconds earlier was testing your patience to its very limit with inane, pointless questions, suddenly becomes resolutely tight-lipped. This practice is chiefly exemplified by the "options" screens in many applications. Half the options are grayed out including, inevitably, the option that you want to set. Why? Who knows? There is usually no help text, or tooltips. The application isn't going to give up the secret of why those options are grayed out, or what you have to do in order to get to the option.

Amnesia is another Microsoft affliction that seems to have caught on. If you always store your documents in a particular path, why continue to insist on defaulting to 'My Documents'. If you always change the 'open' dialog box to reveal details, why always revert to icons?

Usability isn't a new science. The man-machine interface has been studied since the creation of the first Visual Display Unit. Why then are such elementary mistakes made? Why are there so many applications that, far from helping the user, seem intent on actively inducing bad temper?

We propose to compile a list if the very worst irritations and quirks that Simple-Talk readers have discovered in Microsoft or third party software. Our hope is that this list of shame gets pinned up on company notice-boards around the world. It will certainly be going up on ours, and we'd love to hear your nominations!

There will be a $50 Amazon gift voucher for the best nomination and three runners-up prizes of the much-coveted Simple-Talk gift bag, so add your suggestions as a comment to this blog (you will need to be signed in)!

Cheers,

Tony.

SQL Server 2008: Refinement but no Fireworks

Tony Davis — Mon, 18 Aug 2008 20:52:00 GMT

The SQL Server platform expanded substantially with the arrival of SQL Server 2005, introducing many features that people now rely on every day (Try-Catch error handling, DDL triggers), as well as a few that captured people's attention but ultimately left many scratching their heads (CLR integration).

SQL Server 2008, just released, is not a radical product in the same way as SQL Server 2005. Of course, there have been polite murmurs of interest around such features as Resource Governor, Transparent Data Encryption, table-valued parameters, policy-based management, the new GIS data types and functions, data and backup compression, and the MERGE statement. However, in terms of added bells and whistles, there seems not to be a standout reason to upgrade to SQL 2008, and maybe this is not a bad thing.

With SQL 2008, the improvements are more of a massive tidy-up. Without a doubt, SQL Server 2008 is a much better, and more usable, product than its predecessor and one that most of us will be happy to move to because there is no demand for any code re-engineering, or radical cultural changes in the users. SS 2008 works much like SS 2005, but with the lumps ironed out.

It's clear that most parts of the system have had been refined. The improvements to Reporting services, SSAS and SSIS are typical; no real fireworks, but everything just works a little better and faster. Some new features that have not been given much publicity are quite astonishing, my personal favorite being the new data mining add-ins for Office 2007, which really do make light work of some complex analysis, and do so with quite a bit of style.

It is easy to joke that SQL Server 2008 is a strange name for the biggest Service Pack ever, but there is a certain truth in that. There isn't any reason to stay with SQL Server 2005, just as it is generally wise to apply the latest service pack. Unencumbered by any of the wild changes in the features that we saw in the development of SQL Server 2005, SQL 2008 has slid quietly into view showing all the hallmarks of a product that has been driven by the requests of the ordinary user rather than the bleatings of the large corporate users.

As always, we'd love to hear what you think. Maybe you agree, or maybe you're actually rather disappointed with the limited scope of the SQL 2008, or the lack of advancements in areas such as reporting services. Either way, we look forward to your comments. The best entry will receive a $50 Amazon voucher

Cheers,

Tony.

Anxiety, Dissent and the Entity Framework

Tony Davis — Wed, 06 Aug 2008 22:05:00 GMT

There is trouble afoot amongst the MVPs who are specialising in Object-Relational mapping (ORM). When the NHibernate mafia, a.k.a. ALT.NET, released their ADO .NET Entity Framework Vote of No Confidence, many of us despaired. Suddenly, it looked more like ANGST.NET than ALT.NET. As I scanned through the long list of names who had signed the document, my heart sank a little.

It's not that it doesn't have some valid points to make; it does (though scarcely enough, some might argue, to justify a "petition"). For example, it quite reasonably points that SubVersion doesn't really work with the Entity Framework, and that 'lazy loading' is better than 'explicit loading' for entity-based applications.

The problem is that a lot of this gets lost amongst lurid warnings of "unresolved issues" and "technical misgivings" and the "potential future risk they pose to Microsoft customer projects". By signing the petition, the supporters were not just agreeing to the reasonable points it makes, but also to its alarmist tone in general. The average manager reading the document is likely to conclude that there is a 'bad smell' around ORM wrappers, and avoid them altogether.

NHibernate is not a general solution. It is fine for the .NET virtuosos, but it is not ORM for the common man and it does not make itself look any better by casting aspersions on Entity Framework. There is plenty of room for a Microsoft take on the long-standing problem of 'impedance mismatch'. At Simple-Talk, we regard Entity Framework in a positive light because of its logical fit with LINQ and Astoria. It is easy to use, and it bridges the conceptual gap between the relational world and the 'object persistence' brigade. It is true that it is just as easy to commit sins against a large relational database with Entity Framework as it was with the old ADO.NET and recordsets, but at least its use should uncover no new horrors for the database administrator.

Microsoft gets a pasting when they develop applications in secrecy, and rightly so. However, the EF has been developed via open discussion and dialog with the developer community, and yet they still get into trouble, presumably because the signatories feel their expertise and opinion are being ignored. And yet ultimately, surely, any project must be allowed to make its own decisions in line with its remit and its place in the wider strategy.

Dissent is a natural and healthy part of all development communities. And occasionally this dissent will need to be expressed loudly and possibly with the aid of pointed sticks. But the timing, and tone, is wrong. Angst-ridden heckling isn't the best way to encourage Microsoft to take advice from the user communities, rather than battening down the hatches and slopping out the product when it's 'done'.

Cheers,

Tony.

The myth of over-normalization

Tony Davis — Mon, 21 Jul 2008 21:36:00 GMT

I’ve always been suspicious of denormalizing an OLTP database. Denormalisation is a strange activity that is supposed to take place after a database has been normalized, and is assumed to be necessary in order to reduce the number of joins in queries to a tolerable level. C.J. Date is quite clear on this; well, he is slightly less opaque than usual: any denormalization to a level below 5NF is a ‘bad thing’ (he says ‘contraindicated’).

In practice, normalization to fifth Normal Form is unusual. Normally, the Database designer reaches for his hat and coat after reaching Boyce/Codd Normal Form (BCNF), which is 4NF, and few databases I’ve ever seen are even reliably BCNF.

Why does one ever normalize a database? It is often said that it is to avoid logical inconsistencies, and to avoid insertion, delete and update anomalies. Also, to avoid redundancy, or duplication, of data. I think there is more to it than that. It also ensures that your data model makes logical sense. If, at the end of the normalization process, you arrive at a set of tables that correspond to simple, easily understood entities, then the chances are that you have got a database model that will sail through the inevitable changes in scope, changes in the application, extensions and so on. If you don’t, then it is time to tear up your database design and start again. Normalization isn’t like sprinkling on fairy-dust; it is a way of testing and ‘proving’ your design.

Too often, denormalization is suggested as the first thing to consider when tackling query performance problems. It is said to be a necessary compromise to be made when a rigorous logical design hits an inadequate database system. As the saying goes, “Normalize ‘til it hurts, then denormalize ‘til it works”. In fact, Denormalization always leads eventually to tears. It complicates updates, deletes and inserts; it renders your database difficult to modify. Maybe once there was an excuse for a spot of denormalization, but on a recent version of SQL Server or Oracle, with indexed or materialized views, and covering indexes, the performance hit from multiple joins in a query is negligible. If your database is slow, it isn’t because it is ‘over-normalized’!

As always, we'd like to hear what you think. The best comment, according to our distinguished panel of judges, will receive a $50 Amazon voucher, and three runners-up will get a Simple-Talk gift pack.

Cheers,

Tony.

VB.NET: The Ugly Duckling

Tony Davis — Tue, 08 Jul 2008 16:35:00 GMT

One of the greatest pleasures of programming in C# is that wonderful, giddy, feeling of superiority one has over VB.NET programmers. In C#, one has a vague awareness that what one is doing has elegance and style. It must be the effect of the curly braces. By contrast, VB.NET steadfastly maintains its status as the 'ugly duckling'.

Is this really justified? After all, a routine that is programmed in any of the.NET Framework languages will be compiled to almost the same IL opcode sequence and the JIT-compiler will produce very similar CPU instructions: Therefore, VB.NET is likely to be as fast as C# or any other .NET Framework language. It is also just as versatile: after all, Microsoft used it to write the Silverlight JavaScript compiler, and the current VBx (VB 10) compiler was written in VB.NET.

The negative perception is due, in part, to the fact that the first 'DotNet' version, Visual Basic.NET 7, gave every appearance of having been slapped together in a big hurry. It had fewer features than VB 6, and abandoned VB's greatest asset: that of being a dynamic language for rapid application development. Despite this, Visual Basic became the most popular .NET language (Heffner 2005, Forrester Research) with over twice as many users as C#. Visual Basic Express Edition is by far the most popular download and all indications from postings in blogs and forums is that Visual Basic is the most popular of the DotNet languages. Of course, there was already a large reservoir of skilled VB programmers in the industry, but given that by 2005 most VB programmers had long ago added C# to their skill-set, its continued popularity indicates that VB has an appeal all of its own.

VB 8 quietly and steadily closed the gap on C# and VB 9, the current version, introduced all sorts of useful features such as XML literals, XML axis properties, and Smart Query expressions. The forthcoming version, Visual Basic .NET 10.0 (VBx), could be the release that finally cures its inferiority complex once and for all. It uses the Dynamic Language Runtime (DLR) so can be used for application scripting, just like VBScript and VBA. It also allows dynamic methods and type generation. Use of the DLR makes late' or 'dynamic' binding almost as fast as static binding, and fully returns VB to the immediacy that VB 6 had as a quick way to get scripting done.

We all love Java and its cousin C#, of course, but once the Dynamic Language Runtime, and the corresponding Da Vinci Machine, are perfected, I suspect the language debate will change. Both the Java Virtual Machine and .NET have, so far, constricted the effective choice of development languages because they didn't support dynamically-typed languages such as Python and Ruby. Once this is fixed, it will result in greater choice and allow the programmer to choose the language that best fits the task in hand, rather than the one best honed to the inherent limitations of the framework.

Cheers,

Tony.

SQL Server Tumbleweed Awards

Tony Davis — Tue, 24 Jun 2008 19:35:00 GMT

Several parts of SQL Server look as though they were started and then suddenly abandoned. The classic example is the TEXT datatype. Phil Factor has a theory on most things, and in this case it's that the programmer responsible for implementing the TEXT datatype at Microsoft succumbed to the unbearable pressure of the job, one day, leaping up in his cubicle, throwing off all his clothes, and flinging himself through the window, shouting 'I must join the penguins'. As a memorial to their departed colleague, his sorrowful team left the code in SQL Server at exactly the point it had been abandoned.

Anyone who has tried to use the TEXT (or IMAGE) datatype will immediately sympathize with Phil's theory. It has so many restrictions placed on it, and so many quirks, that using it is like training a mule to jump through hoops.

Two other examples spring to mind, of features that seem to have been abandoned in mid-development. The first is the SSMS Templates. The macro processor that allows actual values to be substituted for placeholders is valuable, but curiously primitive in its implementation; more like a 'proof of concept' exercise. The datatype field doesn't seem to be used at all, leaving just a crude system of string substitution with no attempt at validation. It is odd that such a useful system has never been developed further.

The second is SQLCMD. Anyone who bases a robust scripting system on SQLCMD soon becomes frustrated by its limitations. Why are there so many differences between the commands in the command-line version of the tool and the SSMS 'SQLCMD mode'? Why can't variables be used in expressions? Even the simplest conditional statement is impossible. One cannot perform any operations such as concatenation or simple maths on values within variables. This is no harder than a second-year undergraduate project.

I wonder if the SQL Server product is now so colossal that not even Microsoft's highly-advanced project management techniques can prevent the odd component from falling through the cracks. The TEXT data type has, to general relief, been replaced by VARCHAR(MAX), but the issues with SSMS Templates and SQLCMD remain.

Templates provide arguably the first, and best, productivity aid that a database developer should use and yet, under-developed and under-publicized by Microsoft, they remain a mystery to all but a few. SQLCMD is extraordinarily powerful, and many DBAs reach for it rather than SSMS, only to become frustrated by the inconsistencies and weaknesses of its scripting variables.

It reminds me of those, somehow elegiac, "work in progress" signs that one sometimes encounters on a remote back road, where no-one seems sure what work was planned or whether it will ever be completed. We encourage your own nominations for Simple-Talk's 'SQL Server TumbleWeed' award. The winning nomination, added as a comment to the editorial blog, will receive a $50 Amazon voucher, and three runners-up will get a Simple-Talk gift pack.

Cheers,

Tony.

CLR, beer and turkey

Tony Davis — Fri, 06 Jun 2008 15:07:00 GMT

It is strange that two important programming techniques in a SQL Server Database can't be done in TSQL. The first is, of course, being able to read multiple results from a stored procedure. You can do it in ODBC/ADO and so on, but not in TSQL. The other obvious failing is that one cannot write user aggregate functions (which perform a calculation on a set of values and returns a single value) in the same way as scalar or table functions, except by using the CLR integration. TSQL can't help you to add your own functions to SUM(), COUNT(), MAX() MIN() etc.

CLR integration was, along with Notification Services, the surprising turkey of the SQL Server 2005 launch. In reality, it was often doubling the development time over T-SQL procedures/functions. Whereas the task of building a function in T-SQL is trivial (load up management studio, edit the template, save and test), the construction of a CLR function was only made easy in the expensive VS Pro. Even so, there were two extra steps and a lot more for us who had only VS Standard. Microsoft had failed to build the CLR development process into Management Studio. CLR integration was over-sold as an alternative to TSQL rather than a replacement for Extended Stored procedures.

Where you have to construct a custom aggregate function, then CLR is the only way there is, so should that be enough to send us scurrying to our cheque-books to buy VS Pro? We had an editorial discussion about doing a workbench on CLR Aggregate functions. Phil loved the idea, but insisted on a really useful example, that readers would actually want. Even a couple of Adnams Broadsides failed to bring from any of us an example besides concatenation, and that is already in Books-on-Line, and can be done without CLR. The idea is still there on the back-burner.

So, what are you using CLR for? Are you writing user aggregation functions, or using it to access powerful CLR libraries such as Regex? Even if you've looked at it and abandoned the idea of using it, we'd be interested to hear why. Usual rules apply – a prize to the best contribution.

Cheers,

Tony.