By the time you read this, SQL Server 2005 Beta 3 should be publicly available. SQL Server 2005 contains much to look forward to. I've been working with early builds for almost 2 years now, and I'm still discovering new features and improvements almost weekly. All these changes are both bad news and good news: The bad news is that many of the things I knew about SQL Server no longer hold true; the good news is that I get to learn a whole lot of cool new stuff.
Here's a preview of my 10 favorite new features and enhancements. These are the items I find the most exciting, and I'll be researching and writing more about them once Beta 3 makes its debut. For now, I can give you enough information to whet your appetite for starting your own investigations.
1. Included Columns in Nonclustered Indexes
I've talked about the concept of covering indexes in several of my earlier columns (see "The Big Cover Up," September 2001 and "Time for a Tune-Up," August 2001), and the inclusion of columns in nonclustered indexes will provide even more flexibility in building powerful covering indexes. Typically, nonclustered indexes allow a total of 900 bytes for all the keys combined, but SQL Server 2005 will let you add other table columns that won't be included in that limit. Although these additional columns permit the leaf level of the index to store more information, they won't be used for sorting and searching through the index.
2. Online Index Rebuild
You might need to rebuild indexes for several reasons: to remove fragmentation, to reestablish a fillfactor, or to move the index to a new drive or filegroup. In SQL Server 2000, rebuilding a clustered index makes the entire index (and table) unavailable during the rebuild; rebuilding a nonclustered index disallows any updates on the index or table. SQL Server 2005, however, gives you the option of rebuilding an index while keeping all the underlying table data fully available.
The option to rebuild an index online will be available through the new ALTER INDEX command. SQL Server 2005 is moving towards a unified model of object management. Instead of a multitude of stored procedures, commands, or Database Consistency Checker (DBCC) options (e.g., DBCC INDEXDEFRAG, sp_password, sp_rename) that change one aspect of an object, every object will have a CREATE command, a DROP command and an ALTER command, with which you will be able to make any allowable change to the object. There will be a new ALTER LOGIN command in addition to ALTER INDEX, and the existing ALTER commands, such as ALTER TABLE, will become more powerful in terms of the changes they can make. ALTER INDEX, for example, will have a REBUILD WITH (ONLINE = ON) option as well as a REORGANIZE option to remove fragmentation.
3. Availability of Index Metadata
Instead of awkward DBCC commands to return information about your existing index structures, SQL Server 2005 will have two new "dynamic management views" that will give you physical storage information and usage information about your indexes. I'll write plenty more about the dynamic management view concept in future articles, but for now, think of these views as being like the sysprocesses table (which I describe in "Inside Sysprocesses," July 2003). This table isn't a real physical table; rather, it's a pseudo table that's built from internally available information whenever it's queried. The dynamic management views work in much the same way. The two that are most relevant to index structures and usage are dm_db_index_physical_stats and dm_db_index_operational_stats.
The dm_db_index_physical_stats view will replace DBCC SHOWCONTIG and will provide information about fragmentation, index depth, and space usage. You'll be able to pass parameters indicating which tables or indexes you're interested in and how detailed you want the results to be. The dm_db_index_operational_stats view will provide information about how a specified index has been used, including details about the number of table scans, index seeks, and index key updates. In addition, this view will report how much time was spent waiting for locks on index keys to be released.
4. New Storage Structures
One of the new storage structures that I plan to investigate is actually a combination of existing data types. In SQL Server 2005, when you declare a column in a table to be varchar(MAX), SQL Server will let you store anything from an empty string with 0 bytes of data to a column with as many bytes as the system can handle (currently 2GB in SQL Server 2005, though it might increase in future versions). If the data is small enough to fit in the row, SQL Server will store the data as a regular varchar column; if the data is too large to fit in the row, SQL Server will store it as large-object (LOB) data (text, ntext, or image). The great thing is that you can use the normal string manipulation operators to work with the data, no matter how big it is. You'll never have to deal with the subtleties of readtext, writetext, or updatetext.
5. Optimizer Enhancements
SQL Server 2005 includes many internal changes to the query optimizer, many of which Microsoft won't explicitly document. (I'll do my best in upcoming articles to tell you all about the most useful of these changes.) One optimizer enhancement that will be documented is the ability to force only one statement to be recompiled (when your performance testing deems that doing so would be helpful).
The only option that SQL Server 2000 provides is to force an entire stored procedure to be recompiled, no matter how many statements it contains. In SQL Server 2005, a new RECOMPILE hint lets you recompile one query among many. Another new hint, OPTIMIZE FOR, lets you specify a variable value that will be considered for a variable during optimization. (Typically, variable values are unknown during optimization and compilation, so the query optimizer frequently generates suboptimal plans for queries in which variables are involved.)
6. New Isolation Level
I'll probably focus most of my testing and writing on a new isolation level called Snapshot Isolation, which will let SQL Server maintain transactional consistency and avoid conflicts without resorting to blocking. With this new isolation level, processes that are writing data will never block processes that are trying to read that data, and vice versa.
7. Backup and Restore Enhancements
SQL Server 2005 will feature quite a few enhancements to both backup and restore operations; I'll mention just a couple. First, you'll be able to do any backup operation concurrently with any other backup operation. For example, a backup of the database won't prevent a regularly scheduled log backup from occurring. Second, for the purpose of increasing availability, the restore process will let a user into a database much more quickly. You might be aware that SQL Server needs to recover a database after the data is loaded, and recovery consists of a roll-forward phase (i.e., redoing any completed transactions) and a rollback phase (i.e., undoing any transactions that weren't committed). In SQL Server 2005, the database will be usable as soon as the roll-forward phase is finished.
8. Database Snapshots
Another upcoming high-availability feature is database snapshots. A database snapshot will let you efficiently maintain a read-only copy of a database at any point in time. You'll be able to have multiple snapshots of any database, keep track of how the database changes over time, and quickly revert to an earlier point when the need arises.
9. Improved Security Model
Many changes to the SQL Server security model are in store. I described several of these in "Inside SQL Server 2005 Security," May 2004; see that article for more information about these upcoming security enhancements.
10. Improvements in the Tools
Query Analyzer and Enterprise Manager will be combined into one tool called SQL Server Management Studio. The Index Tuning Wizard will be replaced by the Database Tuning Advisor, which is a full-fledged application rather than just a wizard. SQL Server Profiler will still be called Profiler but will simplify how you define which data columns on which events you want to capture and how you want to apply filters. The tracing engine behind Profiler will also be a lot more sophisticated. You'll be able to capture query plans and deadlock traces in XML files, which you then can use to display either the Query Plan or the deadlock chain in a graphical format.
Just for Starters
You may be aware of other new SQL Server enhancements that I haven't mentioned yet, but these 10 are the ones that have the strongest and most immediate grip on my interest. These features are just the tip of the iceberg for SQL Server 2005, but they should be enough to keep us busy for a while.
