What's New in SQL Server 7.0: The Essentials

Ron Talmage

There are so many new things about SQL Server 7.0 that it's going to take some time for us to digest all of them. This month, Ron covers a few of the big features.

In this month's column, I'll discuss what seem to me to be the essential new SQL Server 7.0 features -- ones that sort of leap out and virtually shout their presence to a DBA or developer. Let's look at them from four perspectives: the server, the storage engine, the query engine, and new SQL Server tools.

The server perspective
If you're used to being restricted to running SQL Server only under Windows NT, you'll have your entire perspective changed by the fact that SQL Server 7.0 now also runs on Win9x platforms. And that's not all: There's a new "embedded" version of SQL Server 7.0 called MSDE (the Microsoft Database Engine), which will ship with Office 2000 Professional and Microsoft Access 2000. [You'll also have to wait for Office 2000 and/or Access 2000 for an Access Upsizing Wizard that reportedly will only upsize Access 2000 -- not older versions of Access -- to SQL Server 7.0. -- Ed.] Suddenly SQL Server will be running on laptops and desktops for all sorts of purposes that were impossible in the past. Of course, SQL Server 7.0 still runs on Windows NT, but not just any version: It has to be NT 4.0 Server with SP4 or higher, and the NT server must also have IE 4.01 with at least SP1. (It also runs on the NT5 beta.)

Self-management
Another sweeping change will resonate with DBAs: SQL Server's self-management. SQL Server 7.0 can automatically configure its memory usage, grow and shrink disk space usage, and repair itself. You can let SQL Server control memory usage, locks, connections, open objects, and so forth, or configure them yourself, but Microsoft recommends that we let SQL Server do the work. Many memory-related configuration options that you could set in prior releases of SQL Server have simply been eliminated.

Maintenance
We're all used to running DBCC and UPDATE STATISTICS periodically against our SQL Server 6.x databases. The SQL Server 7.0 DBCC command is dramatically faster, replaces the NEWALLOC option, and can actually repair data! In addition, SQL Server 7.0 incorporates a fast failure philosophy, where it's considered better to fail and repair as soon as an error occurs, rather than leave corruption in the database. Accordingly, SQL Server can automatically detect and repair some errors that would have crashed SQL Servers before. (See the discussion of SQL Profiler later in the article.)

SQL Server 7.0 will also automatically update index statistics and can keep statistics on unindexed columns. However, the rate at which the statistics are updated might not be often enough for some tables, so you might still have to schedule UPDATE STATISTICS.

Security and backup
Security and backup are another couple of areas with significant changes. SQL Server 7.0 security is much more integrated with Windows NT, though Windows 9x installations will still have to use standard SQL Server logins and passwords. Database roles replace groups, and fixed server roles can be used to delegate system administrator (sa) tasks. SQL Server 7.0 now uses the industry-standard fuzzy backup strategy, which makes backups much faster and makes possible a new differential database backup option. New BACKUP and RESTORE commands replace DUMP and LOAD.

The storage engine
The larger page size of 8K seems to have had a ripple effect throughout SQL Server 7.0's storage structures. Extents are now 64K (eight pages), and a single row in a page can occupy 8,060 bytes, up from 1,962 bytes in prior releases. Character and binary columns can extend to 8,000 bytes, up from 255. With more rows on a page, it seems natural that SQL Server 7.0 should lock rows. In fact, row locking is the default, and SQL Server will automatically escalate to page or table locking depending on its analysis of the query.

Database and log files
Data storage changes dramatically. Database devices are thankfully gone in SQL Server 7.0, replaced by operating system files. Every SQL Server 7.0 database has at least one data file and one transaction log file, and unlike the old database devices, every SQL Server 7.0 database file can only contain data from one database. By default, SQL Server 7.0 will automatically grow and shrink data and log files, although you can override that.

One surprise is that tempdb is no longer in RAM -- it resides on disk like all other databases. You can, however, move it and other databases from one local disk drive to another with the enhanced ALTER DATABASE command. In addition, you can detach a database from one server, copy its files, and attach it to another server with the new sp_attach_db and sp_detach_db system stored procedures.

Indexes
There's an important but subtle change to indexing. Clustered indexes always keep a unique clustering key for each row in a table and will create a unique key (a uniquifier, in Microsoft parlance) if the indexed columns lack one. If there's a clustered index on a table, all non-clustered indexes on the table will use the clustering key to locate rows in the table. The benefit is that page splits won't require any updates to the non-clustered indexes. But because each non-clustered index contains copies of the clustering key, it's very important to keep clustered indexes as narrow as possible.

Query engine enhancements
Many query and Transact-SQL limitations have been increased or eliminated by SQL Server 7.0. For example, you can now have 256 tables in a query, up from 16. A table can have 1,024 columns, up from 250. And you can now drop a column: In fact, the ALTER TABLE command now allows you to drop a column, add a non-null column to a table, and make a limited set of changes to a column's data type.

New data types
We also get several new data types. The Unicode character data types nchar, nvarchar, and ntext store Unicode data and occupy two bytes per character. To specify Unicode character data in a string, you can prepend it with the ANSI SQL standard capital N, as in SELECT * FROM Authors WHERE au_lname = N'Green'. (The query engine will implicitly convert a character between unicode and non-unicode character strings.) The uniqueidentifier data type specifies that the column will contain a GUID entry, which you can get from the NEWID() function.

Significant changes to T-SQL include SELECT TOP to see a subset of a query's rows, the application of SUBSTRING to text, image, and ntext data types, and the ANSI standard CAST command, equivalent to the CONVERT() function.

Query performance
There are also numerous performance improvements to the query engine. For one thing, SQL Server 7.0 queries can finally use more than one index per table in a query. In addition, the query engine can use more sophisticated merge and hash join techniques, which you can observe by using the new graphical showplan. Be sure to check out the new query governor that will prevent costly queries from running!

There's a new sp_configure option for the query governor as well as a session-specific SET command, and you can also find the option in the Server Properties dialog box, Server Settings tab. (The query governor will prevent from executing queries that exceed a certain cost figure based on the costing numbers returned by SHOWPLAN. If the estimated cost of a query is a reliable measure of how long it would actually take to execute it, then this will be a very welcome feature indeed!) Another important new feature is the ability to distribute your queries across several SQL Server and other databases using linked servers or the new OPENQUERY() function.

Stored procedures
Another important but less visible change is in the way SQL Server 7.0 compiles stored procedures. It delays resolving the names of objects in a stored procedure until runtime (called delayed name resolution), so you can compile stored procedures that reference objects such as tables and other stored procedures that don't yet exist. Beware -- this will bite developers who are in the habit of letting the compile process test their code for references to nonexistent objects!

Tools
Seemingly every tool in the entire SQL Server suite either got a facelift or some other significant change. In Enterprise Manager, the visual data tools allow you to make a physical diagram of a database, browse a table's data and build a query, and visually inspect column properties. Watch out: This utility adds a dt_properties table and several dt_ stored procedures to every database it touches.

Revised tools
SQL Executive is now called SQL Agent and still runs as a service, but tasks are now jobs with potentially many steps, and flow control. Also, you can use SQL Agent to manage jobs on several servers from one master server.

The SQL Server 7.0 Profiler updates the SQL Trace utility. It will get a lot of votes as a favorite tool: Not only can you trace many more events directly to a SQL Server table, you can actually step through scripts line by line, tracing the output. A real prize is the ability to replay a trace file at various speeds, using multiple connections, thereby simulating load on a server. Finally, the xp_trace_setqueryhistory extended stored procedure lets you form a "flight recorder," which will record a set of key events into a blackbox.trc file. You can send this along with the output of sqldiag.exe, a configuration data collection utility, to a support provider in case of a crash.

Replication has been enhanced to include merge replication in order to support mobile applications. In addition, both transactional and snapshot replication support immediate-updating subscribers: A subscriber can update a publisher using two-phase commit, and the result is then replicated to the other subscribers.

Current activity also has a new look in Enterprise Manager, and there are numerous new SQL NT Performance Monitor counters.

New tools
The most noticeable new tools probably are those dealing with Microsoft's data warehouse support.

You can use DTS -- short for Data Transformation Services -- for extracting and moving data from any OLE DB data source to another and transforming the data along the way. Many will find DTS a useful replacement for bcp (though the latter isn't dead and buried and has actually been improved). Once you have a data warehouse in place, you can analyze its data using Microsoft OLAP Services, a standalone server product that comes on the SQL Server 7.0 CD but only runs on an NT server (you can't run it under Win9x). With it you can store, manage, and analyze OLAP data cubes.

Because of the major structural changes to data storage, you can't restore SQL Server 6.x backups to SQL Server 7.0. Instead, Microsoft urges you to use the heavily tested Upgrade Wizard that comes with SQL Server 7.0 to upgrade SQL Server 6.0 and 6.5 databases to 7.0. You can upgrade on the same machine, or from one server to another. Either way, the resulting upgraded database will have a 6.x compatibility mode set on the SQL Server 7.0 server, which you can inspect and change with the sp_dbcmptlevel system stored procedure. This will bite more than a few of us, I fear, because when in 6.x compatibility mode, the new extensions to T-SQL such as SELECT TOP won't work!

Other new tools that might take a while for us to use -- but in the long run might become prominent -- are Full Text Search (the same engine that Index Server uses) and English Query, a "natural language" engine that lets users pose questions in English rather than SQL. The Full Text Search engine will create static indexes on selected character/text columns, so that you can speed up text searches through long comments or other types of character data. English Query is a standalone utility that translates the user's question into SQL Server 6.5 or 7.0 T-SQL SELECT statements.

Ron Talmage is a SQL Server DBA and developer with GTE Enterprise Solutions in Kirkland, WA, and a SQL Server instructor for ST Labs, in Bellevue, WA. Ron is author of the forthcoming SQL Server Administrator's Guide (Prima Publishing), an MCSD, MCP in SQL Server, a SQL Server MVP, and current president of the Pacific Northwest SQL Server Users Group. RonTalmage@compuserve.com.