The English language has, within a lifetime, emerged as the ubiquitous 'international language' of scientific, political and technical communication. On the one hand, learning a single, common language, International English, has made it much easier to participate in and adopt new technologies; on the other hand it must be exasperating to have to use English at international conferences, or on community sites, when your own language has a long tradition of scientific and technical usage. It is also hard to master the subtleties of using a foreign language to explain advanced ideas.

This requires English speakers to be more considerate in their writing. Even if you’re used to speaking English, you may be brought up short by this sort of verbiage…

"Business Intelligence delivering actionable insights is becoming more critical in the enterprise, and these insights require large data volumes for trending and forecasting"

It takes some imagination to appreciate the added hassle in working out what it means, when English is a language you only use at work. Try, just to get a vague feel for it, using Google Translate to translate it from English to Chinese and back again.

"Providing actionable business intelligence point of view is becoming more and more and more business critical, and requires that these insights and projected trends in large amounts of data"

Not easy eh? If you normally use a different language, you will need to pause for thought before finally working out that it really means …

"Every Business Intelligence solution must be able to help companies to make decisions. In order to detect current trends, and accurately predict future ones, we need to analyze large volumes of data"

Surely, it is simple politeness for English speakers to stop peppering their writing with a twisted vocabulary that renders their writing inaccessible to everyone else.

It isn’t just the problem of writers who use long words to give added dignity to their prose. It is the use of Colloquial English. This changes and evolves at a dizzying rate, adding new terms and idioms almost daily. As such, it is almost a new and separate language. By contrast, ‘International English', is gradually evolving separately at its own, more sedate, pace. As such, all native English speakers need to make an effort to learn, and use it, switching from casual colloquial patter into a simpler form of communication that can be widely understood by different cultures, even if gives you less credibility on the street.

Simple-Talk is based, at least in part, on the idea that technical articles can be written simply and clearly in a form of English that can be easily understood internationally, and that they can be written, with a little editorial help, by anyone, and read by anyone, regardless of their native language.

Cheers,

Tony.

When I first joined the industry in the late 90's, Microsoft was in the process of shunting the ODBC driver into the background in favour of OLE-DB and ADO. The ODBC driver was still included in Microsoft' Windows MDAC framework, but it had gained a rather unfair reputation for obtuse connections strings, unreliability and slow data access. They had to include ODBC, as it was and remains, the only way to connect to the more exotic data sources.

Most VB/ASP developers adopted ADO and OLEDB, as they were promised enhanced performance. The connection strings were hardly any less obtuse but developers persevered. Lucky .NET programmers are now provided with ADO.NET, which is the central database access machinery and allows you to connect with SQL Server via OLEDB, SQLClient, or ODBC.

However, things are more confusing for those stuck with unmanaged applications. When SQL Server 2005 appeared, Microsoft chose not to update MDAC to support the new features and data types. Instead, they introduced SQL Native client (SQLNCLI), offering combined ODBC and OLEDB functionality in a single DLL and included support for all the new data types – UDTs, XML, varchar(max) – as well as snapshot isolation, Multiple Active Result Sets, and so on. Any stuffy old unmanaged ADO /MDAC apps that didn't need access to these exciting new features could continue to use MDAC, which Microsoft hurriedly renamed to 'Windows DAC'; otherwise the apps had to be migrated to the SQL Server Native Client.

It does seem that the native client is more powerful and reliable than the MDAC ones, which had a tendency to "break" an application every time a new Windows service pack was applied. Unfortunately, those brave souls who chose to move from MDAC to the native client, in order to exploit the new features of SQL Server entered a minefield. While MDAC was tolerant of less than strict adherence to the specs (such as starting parameter names with "@"), the native client isn't, and will issue errors. There are also all sorts of subtle variations in behaviour to deal with, in regard to connection strings, dealing with failed connections, warning and error handling, and even in basic transaction handling. And it isn't as if the support for new features is complete. Yes, the native client supports the xml data types, and FOR XML queries…but that's it. No other part of XML is supported by the native client. You have to use SQLXML for this.

How did we find ourselves in this mess on an issue as fundamental as database connectivity? Ten years ago Microsoft lost enthusiasm for the ODBC standard, and it is left mainly to "sticks in the mud" like Phil Factor to point out that what we dismissed was actually worth having: a fast, truly open standard for accessing all data sources. If it had been properly nurtured and developed by Microsoft so that it supported the full standard, it would doubtless have been loved by all.

Instead new drivers come and go, along with new standards for data access, such as LINQ and EF, so that people are left paralyzed by choice, and unable even to safely upgrade their applications to support the newest version of the database.

Cheers,

Tony.

Tools such as Visual Basic (VB) and Visual FoxPro succeeded and became wildly popular because they provided simple and effective abstractions of a programming language. This allowed relatively inexperienced developers to rapidly prototype and produce small business applications. The .NET languages and platform that superseded them have grown rapidly in complexity and this sort of simple abstraction has proved much more elusive.

Web Forms attempted to abstract the complexities of the stateless Web model and to speed up the development of Rich Internet Applications (RIA), by making them work more like a traditional desktop application. This was done to minimise the culture-shock for those VB 6 / ASP developers who did not have a strong background in HTTP/HTML Web development. In the event, it confused the VB6 code-jockeys, and presented a complexity-overkill for the seasoned Web developer.

The problems seem to stem from the fact that the event-driven model, which creates state where there is none, and relies heavily on Postback, is fragile and makes needlessly complex the seemingly- straightforward task of rendering HTML reliably to a browser. Criticisms levelled at Web forms include difficulty in testing, bloated ViewStates, a lack of standards-compliance, which made cross-browser support painful, and generally sucking all the fun out of web programming.

As such, the advent of ASP.NET MVC (Model-View-Controller) is a blessed relief to many. It is based on solid and well-established principles of “separation of concerns”, and actually simplifies the process of rendering standard HTML to a range of browsers. And yet, as presented by Scott Guthrie in his recent DevConnections keynote, and reported recently by Laila Lotfi on Simple-Talk, it is aimed squarely at the experienced programmer who wants “full control over their HTML”, and at large-scale applications.

If ASP.NET MVC is for the experienced programmer only, and Web Forms are fundamentally flawed, or as Rob Conery puts it are “an abstraction wrapped in deception covered in lie sauce presented on a plate full of diversion and sleight of hand”, then where does the small shop turn for a simple and rapid development platform for RIAs?

It seems that, for these people, the answers may lie elsewhere. Ruby on Rails (RoR), also based on an MVC design, has grown rapidly in popularity due to a simplicity and clarity of structure that often seems to elude Microsoft frameworks. Likewise, PHP has always been massively popular for its relative simplicity and ubiquity, and it now has it own CakePHP framework, which begs, steals and borrows more than a few tricks from RoR. Similarly, the Python fraternity is warming to Django. On top of this, there are ExtJS, jQuery, MooTools, Dojo, Rialto, Qooxdoo, and a host of other JavaScript platforms, offering an easy route to RIA paradise.

A generation of entrepreneurs grew successful businesses using simple RAD tools such as VB and FoxPro. These simple tools don’t seem to exist for the next generation of predominately web-based developers, at least not in the Microsoft platform.

As always, we’d love to hear what you think. Many thanks to everyone who contributed to the previous Scalar UDFs editorial; the Amazon voucher goes to BuggyFunBunny for an interesting peak into a possible future direction.

Cheers,

Tony.

The doggedness with which certain SQL Server "myths" cling to the coat of the SQL Server community is really quite astonishing. They are the proverbial "sheep ticks"; very annoying and incredibly difficult to dislodge. And even when you think you've managed it, their embedded jaws remain to provoke further irritation.

A classic case in point is the "SELECT…INTO" myth, which states that its use should be avoided as it will cause serious blocking issues in SQL Server. Way back in the days of version 6.5, i.e. circa 1996, SQL Server had a serious problem with SELECT…INTO. Due to limitations in the way page allocation worked, these statements caused massive contention in tembdb. In effect, tempdb was "locked" for the duration of the statement, and so any other process that required access to tempdb was blocked. Many a time, a long-running query would pour its results into a new table via a SELECT…INTO, and effectively lock an entire production application for maybe an hour until KILLed by the DBA. It was an unforgettable experience whenever it happened, remembered vividly both by the users and the harassed DBAs.

The problem was essentially fixed in version 7.0, more than 10 years ago, and yet the memory of the problem lives on so vividly in the minds of many DBAs that, to this day, the myth persists, and SELECT…INTO is effectively banned from the SQL Servers they manage. Of course, other DBAs have accepted that SELECT…INTO has been rendered harmless, and have cautiously released it into the wild. And yet, it seems that the jaws of this particular tick remain resolutely embedded in our skin.

In SQL Server 2000, the allocation system for tempdb was improved to greatly reduce contention issues, but the fact remained that SELECT…INTO, and other processes that made heavy use of tempdb, could still cause significant contention. In response, with SQL 2000 SP3, Microsoft introduced a new trace flag, -T1118. When activated, it forced "uniform extent allocations instead of mixed page allocations" and helped with tempdb scalability. Although still available in SQL Server 2005 and 2008, further architectural improvement has rendered this flag redundant…or has it? Speculation persists to the effect that if you are performing SELECT…INTOs then you should set the -T1118 flag in order to deal with the resulting tempdb contention. And yet others in the community have reported not being able to reproduce a single case where the flag has helped improve throughput in SQL Server 2005 or 2008.

It's not the fact such myths arise that surprises me, but the way they manage to persist for so long, and morph into new forms. Why is this? Is it a lack of clear information? Or difficulty in finding the information that is out there? What other "ticks" do we need to kill off once and for all, and how can we do it? Could a resource such as asksqlservercentral help de-flea the community?

Cheers,

Tony.

Do you suffer from Index Anxiety? Do you lie awake, worrying about whether you are neglecting to defrag your indexes? We’ve all heard the chestnuts: rebuild all indexes that are over 30% fragmented; reorganize those that are between 10-30% fragmented; don't bother at all for small indexes. If you have the database equivalent of an eating disorder, worrying whether your indexes are too fat and fragmented, then I’d like to provide reassurance: they are often designed to be that way.

If Index rebuilding is the database equivalent of a body-image neurosis, then the SQL Server Maintenance Plan Wizard often delivers slimmer's snake-oil: if in doubt, rebuild! Even in cases where at least some effort is made to track index fragmentation, using DBCC SHOWCONTIG or the sys.dm_db_index_physical_stat DMV, I wonder whether the index-rebuild task is done more for reassurance than in confident expectation of a sustained performance boost.

The way it was explained to me is that, like people, indexes gravitate towards a certain natural weight. Some are naturally "skinny" and others naturally "fat". Let’s suppose that you have an index on a "name" column for a table storing members of your website. Entries will be regularly added to and deleted from the index column, space will be created and the index will grow fatter. However, this space will be reused quite effectively. As index entries are removed, the space will be reused as similar entries are added, and entries will naturally migrate from place to place in the index. If you regularly remove that space with a rebuild (or reorganization), all that will happen is that the engine will have to recreate it – working hard in the process – and the index will naturally grow fat again. It's the ultimate yo-yo diet, and it is all to little avail.

I'm not arguing that there aren't valid reasons to rebuild indexes – just betting that over ninety percent of index rebuilds are, at best, a waste of time, if not positively harmful. Too often, it seems to me, there is a missing task, which is to understand exactly how a table and its indexes are used by an application, to know the I/O that the most expensive queries perform, and to determine whether this work really will be reduced, for any significant period of time, by a rebuild. If you are constantly rebuilding indexes to maintain performance, then you need to look at your underlying schema design, and at the way your queries work, and see if there is a way to tune them to reduce range scans, and so on.

Crash Maintenance Plan slimming diets, followed by binge index growth, won't help to give your indexes the perfect figure. It is much better to give your indexes a healthy lifestyle.

As always, we'd love to hear what you think. The best contribution, made as a comment to the blog, will win a $50 Amazon voucher.

Cheers,

Tony.

What is wrong with benchmarking software? Not much, you’d have thought. A while back, Laila Lotfi wrote an editorial on the need for a standard benchmark for Object-Relational mappers, such as Entity Framework and nHibernate. By how much do they really slow down database applications?

When the developers over at x-tensive.com, creators of the DataObjects.Net ORM tool, created a series of benchmarks much as Laila suggested, they published the results on the ORMbattle.NET website. Nobody could have imagined the resulting Brouhaha.

Derision has been heaped on the stated intent of the site, to provide an honest objective comparison of the performance of various different ORM tools for .NET. Instead, it has been portrayed as a "sleazy", "classless", underhand marketing trick using biased, inaccurate and unrealistic benchmark tests that were deliberately designed to show competing tools in the worst possible light.

There many complaints to the effect that the benchmark tests are useless, because the tool "should never be used in that way". If it can be, it almost certainly will be; and it is up to the tool creator to make sure that it stands up as well as its competitors. Although some interesting points emerged about the "bulk processing" nature of the benchmarks, and the manner in which transactions were used, any "knowledge shrapnel arising from the explosion of ORM minds" was largely drowned in a sea of acrimony, personal affront and mud-slinging. I imagine the sight of such a brawl sent a chill down the spine of managers who may have been planning to use ORM technology.

This comes at an unfortunate time. The IT industry is increasingly coming to suspect that the performance and scalability issues that come from use of ORMs outweigh the benefits of ease and the speed of development. DBAs will point unfailingly at the poorly optimised SQL that these ORM tools often produce. ORM supporters often accuse developers of not understanding of their tool and its features. If the latter is true, then such benchmarks are doubly important. It is not easy to produce realistic and fair benchmarks, especially for complex ORM tools, but if the community engages and perseveres, meaningful comparisons can be achieved, and one can learn a great deal from the process. We are crying out for objective benchmarks and if the ORM industry itself cannot hope to agree on how to do it, then perhaps benchmarks will have to be imposed on them.

As always, we'd love to hear what you think.

Cheers,

Tony.

For the older generation of SQL developers, discouraging the use of views is tantamount to accusing a respectable aging dowager of immorality. No way! They've always been such benign and conventional database objects.

Views are virtual tables, constructed from base tables and other views. They behave just like tables, and are a very simple means to "pre-assemble" data into an easily-queried format for the end user, thus protecting them from the underlying complexity of the SQL, and providing a layer of protection to your base tables.

Views are doubtless an occasionally-useful tool for the database developer, when designed wisely. Unfortunately, abuse of views has increased proportionately with the widening mismatch between the relational model and the object-oriented world of C# development. The result has been the increasing use of "Godzilla Views" that attempt to deliver entire business objects to LINQ or EF users, hiding dozens of tables, hundreds of columns, and almost always returning far more data than is really necessary. Like Godzilla, such views will inevitably turn on their creators and wreak awful havoc on the databases they infest, through the performance and maintenance problems they cause.

So when should you use views? Under what circumstances is it a bad idea? Much of the SQL literature is curiously silent on the issue. Surely, in the light of advice discouraging their use, it is time to take a reasoned stand and start a debate about what guidance IT departments should give over the use of views. I'd like to start by suggesting a few 'best practices'.

• DO use views as the interface to grid controls, and so on, in applications that are designed to only work against table structures. You should never expose base tables to data controls
• DON'T use views as a general-purpose means to deal with ad-hoc, or parameterized queries from applications. Use a properly-designed interface that is based on stored procedures, wherever possible.
• DO use views to promote flexibility and portability – for example, isolating all query cross-references to a remote database in a single view.
• DO consider the careful use of Table-Valued UDFs as an alternative to views, where you also need to perform processing, pass parameters and so on.

Help us finish the list, and correct the advice we've given. Better still, if you have view code on which you'd like a second opinion, send it to us and we'll get our authors to review it and potentially offer alternative solutions. We'd also like to hear of real instances of Godzilla views of frightening complexity which have caused difficulties with production systems. The best example will receive a prize.

Cheers,

Tony.

There are many good reasons for building your data integrity logic into constraints. When such business rules become part of your DDL, they can never be circumvented. Any change to a rule can be enforced by a single update to a constraint in the database. It is a simple and safe architecture for managing your data integrity. But is it always that simple in practice?

It is much better to put this logic in the database than to have similar logic spread scattergun throughout the many tiers and applications that access the database. With the latter approach, one then faces the task of ensuring that the rules are consistent in all places, and that no process can bypass the checks.

However, even with all your integrity logic safely corralled into the database DDL, things can still get complicated, and difficult to track, pretty quickly. Most DBAs will employ the full range of simple CHECK, DEFAULT, UNIQUE and KEY constraints that they have available. They may also have a few more complex rules to enforce that require UDF-based constraints. Of course, there are some checks that you can't realistically do in constraints, so you'll have some triggers thrown into the mix as well. In addition, you may have some "cascading" Referential Integrity requirements (such as "cyclic cascades") that are not supported in SQL Server, so you'll implement these rules using stored procedures.

At this point your logic is beginning to look a little less neat. Although it is all within the database, it can become very easy to forget or miss something when the business rules change and need to be updated. At least, it will take some time to locate all the places where changes need to be made.

If it is not possible to satisfy all integrity rules using constraints alone, then should one, instead, implement them all using triggers, so that all the rules really are in one place and can be maintained easily? This wouldn't be a free lunch. The cost-based optimizer uses the information it finds in constraints to optimize query execution. For example, if a query being optimised uses the LOWER function, and you already have a constraint in place on the column that restricts all entries to lowercase, then the optimizer can, if conditions are right, ignore the function altogether and arrive at a faster execution plan using just the constraint.

How much performance are you actually missing out on? After all, the optimizer won't always use constraints, if they are not trusted because they were disabled at one point, or if they use functions. Does it outweigh the relative advantage of having a single, consistent "layer" of triggers, for example, to implement your rules?

What would you do? What is your overall strategy towards implementing, and easing the management of, business rules in the database?

Cheers,

Tony.