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.

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.

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.

Server Management Objects (SMO) is a very impressive product. SMO (and its previous incarnation, SQL-DMO) is essentially an object-oriented interface into the management of SQL Server installations and databases. It provides an intuitive way for the VB or C# programmer to automate any operation that can be performed via SSMS.

We've run a few SMO articles in the past and to our surprise they have been met with little more than polite indifference. So, what is the problem? Well, firstly, DMO-based scripts worked via OLE automation. They could be run from any scripting language, even TSQL. Many DBAs spent many months DMO-automating their administration tasks only to find that they couldn't be used in SQL Server 2005. To use SMO means Visual Studio and NET Framework only. Microsoft eventually did a U-turn and released DMO for 2005 (although without support for features specific to SQL 2005), but it still created a lot of ill feeling among DBAs.

A more fundamental problem is that the Object Oriented model, while crystal clear to the geeks at Microsoft, is alien to many of the DBAs who were the product's intended users. There is a vast cultural gap between SQL and C#.

In SQL Server 2008, with the introduction of PowerShell and "PowerSMO", I wonder if Microsoft is about to make the same mistake again. PowerShell may be crystal clear to the dome-heads of Redmond, but is as intuitive as runic scripts to many of the DBAs at the coal-face.

Personally, I think that Microsoft would stand a much better chance of having the DBA community embrace the technology with open arms, if they had instead provided a refined relational model for the management of SQL Server (think DMVs, but more versatile, with CRUD operations).

What do you think? Is it high time that Microsoft acknowledged that there is room for a richer diversity of IT culture, and that it is futile to try to impose the C# object-oriented orthodoxy on everyone? Or do DBAs need to "adapt or die"? Add your comments to this blog and the best entry will receive a prize.

Cheers,

Tony.

The Daily WTF recently reported that the Sexual and Violent Offender Registry of Oklahoma had to shut down its website for 'routine maintenance'. It turns out that this routine maintenance was necessary because 10,597 social security numbers from sex offenders had been downloaded, by SQL injection.

Sadly, this is not an isolated case. There has been a recent spate of SQL injection attacks, most of which have been blamed on IIS. However, closer inspection, in this case, reveals that the JavaScript file responsible was merely testing for all the well-known security loopholes, many of which are open due to poor database security.

SQL injection attacks rely on several fundamental, but frustratingly common mistakes:

1. Allow the end-user or the logins assigned to the website direct access to the base tables.
2. Perform inadequate checking of input (inadequate type handling, incorrectly filtered escape characters)
3. Perform no parameterisation of input in the calls to the database.

Without wishing to re-open an old debate, Simple-Talk has long advised that the interface between the application and the SQL Server database should always use stored procedures. However, many are still resistant to the idea.

The attacker who is out to get data also needs to have it delivered on-screen, as was conveniently provided in the Oklahoma incident. Database errors should never be displayed on-screen in a production system. On the contrary, all errors, even 404 errors, should be logged and emailed to production staff as part of the general alerting system for any corporate application.

No longer can developers sit back in their chairs once an application is shown to perform to specification. They must prove that their application can counter all current security threats and be able to report when an attack happens. When implemented, you may be shocked by the vast number of attempted intrusions to which the typical application is subjected.

As always, your views are encouraged. Add your comments to this blog and the three best entries will win a Simple-Talk gift pack, complete with polo shirt and much-coveted USB dongle.

Cheers,

Tony.

"That ain't a database, it's a spreadsheet!"

From the Sayings of Phil Factor

There is a world of difference between an enterprise-level relational database and a 'repository of persistent data'. Until you've had the experience of dealing with a high-volume, high-transaction database with large amounts of data, the truth of this doesn't really hit home.

The constant friction between the relational and the object-oriented model is due to a misunderstanding. Something that works well in the small scale doesn't necessarily cut it at the enterprise level. Programmers tend not to appreciate the importance of indexes, stored procedures, referential integrity, constraints, or even normalisation, until they have experienced an enterprise-scale database. DBAs, in turn, find it hard to repeatedly explain the reasons. The result is often a guerrilla war between the database developers and application developers.

By the same token, where a database is unlikely to become large, or have challenging performance requirements, there is a lot to be said for techniques such as Object-Relational Mapping using Entity Framework or Hibernate, and the use of XML. Anything that can reduce the labour of application development should be considered with an open mind.

But it has to be tested.

From the very first stages of designing a development project, you must test your proposed data-handling architecture against the worst buffeting that a production system can experience, with the data volumes and characteristics you can expect. The interface between the data and application layer has to withstand the stress of real data volumes. It has to withstand attempts at intrusion or malicious damage. There is no substitute for this process, in terms of uncovering any issues with the design of your application.

This week, Red Gate launches SQL Data Generator, which we hope will help a lot with this sort of work. The tool can fill a database with enough data to expose any weaknesses in the design of a database, and the application that uses it. The team have worked hard to make sure the data generated is as close to reality as possible, in order to avoid those bugs that come from developers making assumptions.

So, if you're working with an enterprise scale database, and are tempted to introduce one of the "latest and greatest" enterprise technologies to your application – step forward Object-Relational Mapping, XML Columns, and Entity-Attribute-Value modelling, to name but three –then we strongly suggest you test your solution thoroughly, against millions of database rows and high transaction volumes. You may find it a sobering experience.

Perhaps the great divide between the object and relational cultures is there for a reason. What do you think? Add a comment to my blog, and the best contribution will receive a $50 Amazon voucher.

The winner of the voucher for their contribution to my previous "Not the right place" editorial is TadRichard.

Cheers,

Tony.

It is strange to see the heat generated over arguments about how code should be formatted. With Visual Studio, of course, it isn't much of an issue, as it is done for you, but the closer you get to the 'live free and die' communities of the LAMP platforms, the more contentious it gets. Likewise, SQL Server's TSQL inhabits a strange land where there seems little consensus over the correct way to lay it out.

There are some great tools for doing automatic layout of code. Visual Studio sets a high standard for this, but other IDEs have caught up. Visual Slickedit is hard to beat as a general-purpose programmers' editor, with facilities to tidy up almost any language. Nevertheless, for the most part, each language has its favourite tool. A few examples that spring to mind:

• A perennial favourite for .NET code is Jetbrains ReSharper, thought by many to offer better formatting capabilities than native Visual studio.
• For PHP, I've always liked Waterproof's PHPedit, and their command-line phpCodeBeautifier works well and is free.
• There is nothing to beat HTMLTidy for tackling HTML and XML layout
• Topstyle is the only tool I know of that sorts out CSS layout properly
• Aptana studio does a great job in laying out JavaScript.
• SQL Refactor is an essential tool for untidy SQL Server 2005, programmers. I've found very little else to touch it as a SQL code beautifier.

 I was recently re-reading Joe Celco's excellent book 'SQL Programming Style' and it struck me that there really wasn't much in what he says about the layout of code that one could argue with. However, the feedback that Red Gate gets over the SQL Refactor product is that providing 40 different options for laying out SQL code just isn't enough.

After years of 'anything goes' SQL coding, it is hard to get any consensus on the right way to lay it out. Every large IT department seems to have its own standard, ranging from incredibly strict, to "everything in lower case". I have a sneaking tolerance towards the latter, liberal approach but, as the editor of a website that has a lot of SQL on it, I have my own reasons to hope for some sort of consensus.

It is worse still for our sister website, SQLServerCentral.com. In this case, people describe their coding problems in forums, and sometimes paste in code that makes one involuntarily flinch. I can see how years of suffering in this way occasionally turns Joe Celco from his usual mild Dr Jeckell persona into a Forum Mr Hyde. Jeff Moden is now famous for his attempts to try to persuade forum users to adopt a Forum etiquette that includes writing SQL that can easily be understood by others.

In short, there would seem to be an obvious need to tighten up standards somewhat. So, how's this for a suggestion:

Identifiers:

• No Reverse Hungarian notation
• Use ISO-11179 where appropriate
• Up to 30 characters.
• Avoid abbreviations.
• Avoid quoted (delimited) identifiers,
• Use standard postfixes
• Use only letters, digits and underscores for names
• Scalars should be in lower case
• Schema object names should be capitalised
• Reserved words should be in upper case.

Layout:

• Use a comma, followed by one space (or newline), as a delimiter in a list.
• Use one space only between language tokens
• Use a tab-space of three ems (spaces)
• Put each clause on a new line, indenting if it is a subordinate clause or subquery.

Disagree? We'd love to hear from you - jsut add your suggestions as a comment to this blog (you'll need to be signed in). The best contribution will receive a $50 Amazon voucher!

Cheers,

Tony.

By now, most of you will have read the news of the delay in releasing SQL Server 2008. It all seems fairly typical Microsoft, though I wouldn't want to be overly critical, even if the bizarre blog announcement, written in strangled Dalek-speak, was almost beyond parody (although Phil Factor had a good attempt).

In reality, it isn't a huge delay, and the sense that the product is slipping has probably been exaggerated by the fatuously-entitled 'Heroes Happen Here' launch wave, which attempted to roll three "launches" into one. This meant that the VS2008 launch was too late, the Windows Server 2008 launch was about right, and the SQL Server 2008 launch was much too early.

Compared with the two-year delay to SQL Server 2005, mostly caused by security issues, the current progress seems acceptable. Nobody wants a product with bugs in it and a subsequent trail of fat service packs. The only time to ship a product is when everyone agrees it is ready. Within reason, the date of release isn't the big issue; it is whether the product will be sufficiently improved to make upgrading an obvious business decision for the companies that use SQL Server.

Nobody who lives by producing commercial software can really condemn product slippages. Certainly, Red Gate has slipped a product or two in its time (SQL Prompt 3 being one), but the overall goal was to ensure that the tool, when it finally arrived, was good and fit for purpose.

OK, a 2-year delay on SQL Server 2005 was excessive, but when it finally arrived, it was pretty good. Or was it? We all have our bugbears, the parts of SQL Server we like to criticize, such as SSMS. However, it would be fascinating to hear your nominations for the one aspect of SQL Server 2005 that could have done with another year's development before it saw the light of day, and why.

Post your nominations as a comment to my blog. All entries will go into a draw for a $50 Amazon voucher!

Cheers,

Tony.