Just a Spoonful of Sugar...

Even though I've been working with SQL Server 2005 for more than 2 years, I'm still constantly amazed by the benefits you get from just installing the product and making some minor tweaks. Last month in "Something for (Almost) Nothing" (InstantDoc ID 47982), I looked at a few of the many benefits that you get just by upgrading your SQL Server 2000 systems to SQL Server 2005.You get these out-of-the-box benefits immediately when you upgrade, with no additional work.This month, I look at some of the benefits that SQL Server 2005 yields with just a small amount of work. Some of these benefits require no more than turning on a setting, whereas others require minimal changes to the server or applications that access the database.

A great example of a quick and simple benefit is in the index space. In SQL Server 2000 and earlier versions, you have to take the database offline to perform maintenance on your table indexes. In the Enterprise Edition of SQL Server, all you need to do in most cases is use the ALTER INDEX REBUILD, ONLINE = ON T-SQL statement, which replaces the DBCC DBREINDEX call. This statement automatically rebuilds your indexes while keeping the database online for users to access. SQL Server 2005 achieves this feat by keeping two copies of the index and having users work with the old copy during the index maintenance operation. SQL Server then switches to the new indexes once they've been updated.This feature does slightly affect performance because SQL Server needs to maintain two copies of the index; but this is a small price to pay for the benefits of not having to take the database offline during the operation. Note that online index rebuilds aren't available on tables containing LOB data; you still need to rebuild those indexes offline.

One area in which many people have thought SQL Server is inferior to Oracle is in locking and isolation for applications. It's true that in SQL Server 2000, the number of concurrency options is limited compared to the options Oracle offers. But in SQL Server 2005, that limitation has been rectified with the addition of Snapshot Isolation.You can choose from one of two primary forms of Snapshot Isolation depending on whether you plan to modify any data. The first form is Read Committed with Statement Level Snapshot.This feature is basically an automatic version of what a developer would think of as "optimistic locking," with the added benefit that the data you're accessing on the server is consistent without requiring you to move the data away from the server.With Read Committed with Statement Level Snapshot, whenever you use a READ COMMITTED lock on some part of the database, SQL Server creates a snapshot of the data for you to work with.Other users continue to access the underlying data in the table, so your operation won't block them from performing work. This snapshot option is best when you're working on long-running transactions that require data that's consistent with the START EACH statement within the transaction and that won't alter the data. No mechanism in SQL Server 2005 performs conflict-resolution with any changes that may have occurred during the course of the transaction.To use this option,you enable Snapshot Isolation on a per-database level.You turn the option on by making an ALTER DATABASE call, then using the SET READ_COMMITTED_SNAPSHOT ON call.

The second form of Snapshot Isolation is called Transaction-level Snapshot Isolation. This form of isolation ensures that the data you use throughout the transaction is consistent with the data as it was at the beginning of the transaction. This option requires a little more work to implement because you need to be aware that on long-running transactions, the data you're working with might not be the most recent version of the data; you might want to alter the architecture of your transaction to take this into account.

Indexing and Snapshot Isolation both help you keep the database available to users who are trying to access data. Many other new features also bring added benefits to users. In my August column,"A Report Builder for Everyone" (Instant Doc ID 46726), I introduced you to Report Builder, a new Reporting Services tool that lets you give your users a simple custom report designer.This easy-to-leverage SQL Server 2005 feature, which Figure 1 shows, requires no changes to the database. All you need to do to give your users access to Report Builder functionality is define someVisual Studio 2005 Report Models for your database and deploy them to your server. The Reporting Services Web page (http://www.microsoft.com/sql/reporting/default.mspx) already contains a link to Report Builder, and users deploy the application by using ClickOnce technology. Users will be amazed at the amount of functionality you give them, and only you will know how little work it took.

Another SQL Server 2005 feature that requires only a little work to leverage is the new large object (LOB) storage, which can significantly improve the performance of any table that stores large text or binary objects.When you change your existing text or image columns to either varchar(max) or varbinary(max) types, SQL Server 2005 will process these columns far more efficiently. Using a variable-length data type with the MAX specifier as a substitute for the large object data types text, ntext, and image provides several advantages.You don't need explicit pointer manipulation because SQL Server internally determines when to store values inline and when to use a pointer. In addition, you can now use a unified programming model for small and large data. Basically, SQL Server 2005 treats these columns as regular varchar or varbinary columns until their size exceeds the limits of those types, and only at that point does it start storing the data as LOB data.This intelligent feature can significantly improve performance and storage characteristics of data in a table that contains a LOB field but actually stores data in that field in only a small number of the rows.

Many other SQL Server 2005 features—such as many of the security features and the T-SQL enhancements—require just a small amount of work to yield benefits, but I'll have to cover them in another column. Next month, I'll take a look at some SQL Server 2005 features that require a more significant amount of work but will revolutionize the way most people look at designing and building database systems.

Matt Nunn (mattnunn@microsoft.com), a senior product manager with Microsoft's SQL Server team, is responsible for driving technical readiness for developers working with SQL Server 2005. Before joining Microsoft, he worked for several companies as a technical manager and consultant using various development platforms—including Visual Basic, Visual Basic .NET, and Borland Delphi—to develop database applications that use SQL Server.

From the Blogs

Many organizations today cannot use public cloud solutions because of security concerns, administrative challenges and functional limitations. However, they still need a centralized platform where end users can conduct self-service analytics in an IT-enabled environment....More

It is crucial to move away from data and analytics stored on individual desktop computers. Today’s solutions must promote holistic, collective intelligence. The strong, continued alliance between Microsoft and Pyramid Analytics helps make all this possible....More

To become a truly data-driven enterprise, many business leaders recognize that they must extend the capabilities of self-service business intelligence (BI) and analytics to more of their business users. Many BI tools tackle part of this need, but they don’t offer a complete enterprise solution....More