Chapter 1 - What's New in SQL Server 7.0

  • Article

Microsoft SQL Server version 7.0 introduces these server improvements and new features:

  • Reduced administrative overhead and cost of ownership 

  • Dynamic locking, in which locking granularity is based on costing 

  • Improved query processing for complex queries 

  • New Transact-SQL statements 

  • Trigger enhancements 

  • Cursor enhancements 

  • Increased capacities 

  • Distributed queries and updates 

  • Improved performance and data management with new page and row formats 

  • Improved utilities for Very Large Database (VLDB) support 

  • New SQL Server security model 

  • Programming interfaces 

  • Enhanced data type support 

  • Windows 95/98 support 

  • Replication enhancements 

  • Full-text search 

  • Unicode 

  • Upgrades 

  • Multisite management 

  • Microsoft Repository 

  • Indexing operations 

  • Data warehousing 

  • Microsoft SQL Server OLAP Services 

  • PivotTable Service 

  • Microsoft Proxy Server 

Reduced Administrative Overhead and Cost of Ownership 

  • On-demand memory, by which the memory manager cooperates with its internal components and the operating system to optimize memory use 

  • On-demand disk, by which files grow and shrink automatically 

  • Auto-update statistics, automatically created statistics, statistics on nonindexed columns, and table and row sampling 

  • Autorecompilation of plans 

  • Reduced configuration tasks 

    These settings can be allowed to automatically configure themselves.

    • Connections 

    • Locks 

    • Open objects 

Many server configuration options have been streamlined and simplified. For example, by default the server dynamically adjusts its memory and lock resource use. A database increases allocated resources when necessary without ever committing them and decreases the resources used when they are no longer needed. Earlier versions of SQL Server required manual adjustment of these settings.

Databases now reside on operating-system files instead of on SQL Server logical devices. You can create a database and all its files with a single CREATE DATABASE statement or use SQL Server Enterprise Manager to create new databases and modify (alter) existing databases. Database files expand automatically, eliminating the need for administrators to issue an additional ALTER statement. A new CREATE DATABASE statement syntax incorporates this file-based implementation.

Database files can automatically grow from their originally specified size. When you define a file you can specify a growth increment. Each time the file fills, it increases its size by the growth increment. Each file can also have a maximum size specified. If a maximum size is not specified, the file can continue to grow until it has used all available space on the disk. This feature is especially useful when SQL Server is used as a database embedded in an application where the user does not have ready access to a system administrator. The user can let the files grow automatically to lessen the administrative burden of monitoring the amount of free space in the database and allocating space manually.

A database consists of two or more files: one data file to hold tables and other objects, and another file to hold the transaction log. Each file is used by only one database. A single file cannot be shared by multiple databases. When a database is dropped, its files are also deleted. Note that you can detach a database to preserve the files, rather than only to drop a database.

Dynamic Locking, in Which Locking Granularity is Based on Costing 

  • Full row-level locking is the default. 

  • Dynamically scales to page or table locking if needed. 

SQL Server 7.0 now supports full row-level locking for both data rows and index entries. The lock manager has been optimized to complete lock requests faster and with less internal synchronization. Many OLTP applications can experience increased concurrency, especially when applications append rows to tables and indexes.

The lock manager adjusts the resources it uses for larger databases dynamically, eliminating the need to adjust the locks server configuration option manually. It chooses automatically between page locking (preferable for operations locking rows in many pages) and row-level locking (preferable for operations that affect a relatively small number of rows, or rows scattered throughout a table).

Improved Query Processing for Complex Queries 

  • New hash and merge join 

  • Multiindex operations and row locators 

The query processor has been redesigned to support the large databases and complex queries found in decision support, data warehouse, and OLAP applications. The query processor includes several new execution strategies that can improve the performance of complex queries.

It now uses hash joins and more sophisticated merge joins, as well as advanced hash aggregation techniques. These techniques can scale to larger databases than those supported by the nested loops join technique, which was the primary join technique supported by SQL Server 6.5. SQL Server 7.0 uses index intersection and union techniques on multiple indexes to filter data before it retrieves rows from the tables in the database. All indexes on a table are maintained concurrently as you make data modifications. Constraint evaluations are now considered as part of the query processor's execution plan. These two factors simplify and speed the updating of multiple rows of a table.

The SQL Server 7.0 query processor extracts information from the statistics and regathers statistics automatically, optionally using fast sampling. It also chooses automatically to collect statistics needed by the optimizer. This ensures that the query processor uses the most current statistics and reduces maintenance requirements.

The query optimizer recognizes certain commonly occurring database and query structures, such as star or snowflake schemas, and chooses an execution plan based on a careful cost analysis particularly well-adapted to queries against these structures. For example, the SQL Server 7.0 query optimizer uses the techniques of Cartesian product index manipulation and semi-joins to process queries against star schemas.

The query optimizer has a wide set of execution strategies and many of the optimization limitations of earlier versions of SQL Server have been removed. An improved costing model and compile-time enhancements, such as predicate transitivity and constant folding, greatly improve the quality of query plans.

The query processor supports new query hints, and some query limitations have been loosened or removed. For example, a single query can now reference 256 tables, and the number of internal work tables used by a query (16 in earlier versions) is no longer limited.

The query processor uses OLE DB to communicate with the data-storage components of SQL Server. OLE DB provides the SQL Server 7.0 query processor with distributed and heterogeneous query capabilities. It supports distributed queries between multiple SQL Server 7.0 servers as well as to any OLE DB provider.

SQL Server 7.0 supports parallel execution of a single query across multiple processors. A CPU-bound query that must examine a large number of rows often benefits if portions of its execution plan are run in parallel. SQL Server 7.0 determines automatically which queries will benefit from parallelism and generates a parallel execution plan. If multiple processors are available when the query begins executing, the work is divided across the processors. Parallel query execution is enabled by default.

During query optimization, SQL Server looks for queries that might benefit from parallel execution. For these queries, SQL Server inserts exchange operators into the query execution plan to prepare the query for parallel execution. An exchange operator is an operator in a query execution plan that provides process management, data redistribution, and flow control. After exchange operators are inserted, the result is a parallel query execution plan. A parallel query execution plan can use more than one thread, whereas a serial execution plan, used by a nonparallel query, only uses a single thread for its execution. The actual number of threads used by a parallel query is determined at query plan execution and initialization and is called the degree of parallelism.

New Transact-SQL Statements 

  • ALTER PROCEDURE 

  • ALTER TRIGGER 

  • ALTER VIEW 

  • ALTER TABLE 

  • BULK INSERT 

  • COMMIT WORK 

  • DENY 

  • RESTORE 

  • RESTORE FILELISTONLY 

  • RESTORE HEADERONLY 

  • RESTORE LABELONLY 

  • RESTORE VERIFYONLY 

  • ROLLBACK WORK 

Trigger Enhancements 

  • Recursive triggers 

  • Multiple triggers per INSERT, UPDATE, or DELETE statement 

You can now append multiple triggers of the same type to a single table. For example, a single table can have one delete trigger, three insert triggers, and two update triggers. This enhancement allows you to put different business rules into different triggers. A database option allows triggers to call themselves recursively.

Cursor Enhancements 

  • Local cursors 

  • Cursor variables and parameters 

Increased Capacities 

  • Tables per query 

  • Columns per table 

Distributed Queries and Updates 

  • SQL Server to SQL Server data sources 

  • SQL Server to other OLE DB data sources 

Distributed queries access data that can be stored in multiple data sources on either the same or different computers. These queries take advantage of OLE DB, the emerging standard for data access of nonrelational as well as relational data sources.

Distributed queries provide SQL Server users with access to data stored in:

  • Multiple servers running SQL Server. 

  • Heterogeneous databases. 

  • File systems. 

  • Network sources. 

Improved Performance and Data Management with New Page and Row Formats 

  • 8 KB page, 64 KB extent 

  • Mixed extents (multiple tables within one extent to save space for small tables) 

  • Improved storage of text and image columns 

  • Improved data placement to reduce storage requirements 

  • Support for native operating-system files 

  • Filegroups to improve data placement and provide flexibility for maintenance 

These new formats allow the server to scale easily from low-end to high-end systems, improving performance and manageability. They also enable new capabilities in future releases, such as embedded tables, multiple types of rows on a single data page, and databases that can be electronically mailed. There are new disk formats for pages, rows, extents, data files, and log files. The new page and row formats support row-level locking, are extensible for future requirements, and improve performance when large blocks of data are accessed, because each I/O operation retrieves more data.

All database pages are now 8 KB in size, increased from 2 KB. The maximum number of bytes in a row is now 8060 bytes, and the limit on character and binary data types is 8000 bytes, increased from 255 bytes. Tables can now have 1024 columns, a significant increase over the 250 columns supported previously. SQL Server also now supports Unicode data types. For more information, see "Enhanced Data Type Support" later in this topic.

Improved Utilities for Very Large Database (VLDB) Support 

  • Improved online backup (less online contention with the existing server workload) 

  • Differential backup and recovery 

  • Backup and restore files or filegroups 

  • No setup to restore a backup on another server 

  • Backup or restore operations can be restarted 

  • Files can be detached and attached without using backup and restore 

  • Major performance improvement for DBCC 

  • Support for Microsoft Tape Format backup 

  • Improved bulk data load performance 

Backup and restore operations run much faster, have less performance effect on server operations, and have new features.

A differential database backup captures only those data pages that have changed after the last full database backup. Differential backups can often eliminate much of the time the server spends rolling transactions forward when recovering transaction logs. A portion of the database can be restored, or rolled forward, to minimize recovery time in the event of media failure. Restoring a backup is easy because the restore process creates the database and all necessary files automatically.

Backup supports the Microsoft Tape Format, allowing SQL Server backups to share the same tape media with other backups, such as those written by the Microsoft Windows NT® Server Backup program.

The DBCC statements have been redesigned to provide dramatically improved performance. In addition, bulk copy operations now validate constraints and fire triggers as the data is loaded.

The bulk copy utility (bcp) uses OLE DB to communicate with SQL Server, supports all data types, and is much faster. Index maintenance strategies are improved to make loading data into tables with indexes more efficient.

New SQL Server Security Model 

  • Supports Windows NT groups and Windows NT users 

  • Introduces SQL Server application and user roles 

The security architecture is better integrated with Windows NT and provides increased flexibility. Database permissions can now be assigned directly to Windows NT users. You can define SQL Server roles to include not only Windows NT users and groups but also SQL Server users and roles.

A SQL Server user can be a member of multiple SQL Server roles. This allows database administrators to manage SQL Server permissions as Windows NT groups or SQL Server roles, rather than as individual user accounts. Database access and permissions can now be managed using Windows NT groups. New fixed server and database roles such as dbcreator, db_owner, and securityadmin provide more flexibility and improved security than the system administrator login. However, it is no longer necessary to run SQL Server under a sysadmin account.

Programming Interfaces 

  • OLE DB 

    SQL Server 7.0 now provides a native Microsoft OLE DB Provider for SQL Server that supports the OLE DB 2.1 API. The driver also supports bulk copy operations and the ability to obtain metadata for linked tables used in distributed queries. The OLE DB Provider for SQL Server supports all the new data types and features introduced in SQL Server 7.0. 

  • ADO 

    Applications using the ADO API can now use the OLE DB Provider for SQL Server instead of using the Microsoft OLE DB Provider for ODBC over the SQL Server ODBC Driver. The OLE DB Provider for SQL Server exposes more SQL Server functionality than the Microsoft OLE DB Provider for ODBC, and reduces the number of layers the application must use to communicate with SQL Server. 

  • ODBC 

    The SQL Server ODBC Driver has been enhanced to support the ODBC 3.51 API. The driver also now supports the bulk copy functions originally introduced in DB-Library, and the ability to obtain metadata for linked tables used in distributed queries. SQL Server ODBC Driver 3.7 supports all the new data types and features introduced in SQL Server 7.0, and is fully Unicode compliant. 

  • SQL-DMO 

    SQL Server Enterprise Manager is based on a Distributed Management Framework (SQL-DMF). SQL-DMF allows for centralized administration of all SQL Server installations in an organization. The core functionality of SQL-DMF is exposed to applications by three APIs: SQL Namespace, SQL Distributed Management Objects, and Distributed Transformation Services. These APIs are implemented as sets of dual-interface COM interfaces.

    • SQL Namespace 

      The SQL Namespace (SQL-NS) API exposes the user interface (UI) elements of SQL Server Enterprise Manager. This allows applications to include SQL Server Enterprise Manager UI elements such as dialog boxes and wizards. 

    • SQL Distributed Management Objects 

      The SQL Distributed Management Objects (SQL-DMO) API abstracts the use of DDL, system stored procedures, registry information, and operating system resources. SQL-DMO can be used to program all administration and configuration tasks in Microsoft SQL Server. 

    • Distributed Transformation Services 

      The Distributed Transformation Services (DTS) API exposes the services provided by SQL Server to aid in building data warehouses and data marts. These services provide the ability to transfer and transform data between heterogeneous OLE DB and ODBC data sources. Data from objects or the result sets of queries can be transferred at regularly scheduled times or intervals, or on an ad hoc basis. 

Enhanced Data Type Support 

  • Unicode data types: nchar, nvarchar, ntext 

  • uniqueidentifier data type for globally unique ID (GUID) 

  • Maximum size of character and binary data types increased to 8,000 characters 

The maximum length of the char, varchar, and varbinary data types is now 8000 bytes, an increase from the limit of 255 bytes in SQL Server 6.x. The Transact-SQL string functions also support these very long char and varchar values. The use of text and image data types can now be reserved for very large data values. The SUBSTRING function can be used to process text and image columns, allowing T-SQL procedures to process these columns. The handling of nulls and empty strings has been improved. A new uniqueidentifier data type is provided for storing a globally unique identifier (GUID).

Windows 95/98 Support 

SQL Server 7.0 for Microsoft Windows® 95/98 and Windows NT Workstation is a fully-featured RDBMS targeted for workstation and mobile applications. Common source code for all platforms, from Windows 95/98 to clustered systems, resolves compatibility issues. Mobile clients are fully supported with merge replication and conflict resolution.

SQL Server 7.0 for Windows 95/98 is perfect for embedded applications because it provides a fully-featured database engine and core components. It enables lightweight, full-function, and low-cost applications. Features for embedded applications include:

  • Self-tuning and configuration 

  • Small memory footprint 

  • High performance 

  • Complete compatibility with Server versions 

SQL Server 7.0 for Windows 95/98 is fully-featured except for a few limitations imposed by the operating system. For example, SMP, named pipes, full-text search, asynchronous I/O, and integrated security are not supported on Windows 95/98.

Replication Enhancements 

Microsoft's goals for SQL Server 7.0 are to provide leadership in distributed solutions, for a large variety of applications for replication. The replication model continues to build on the "publish and subscribe" metaphor introduced in SQL Server 6.0. New replication interfaces are available for custom heterogeneous applications.

Three major types of replication are available. The type used for an application depends upon requirements for transactional consistency, site autonomy, and the ability to partition the data to avoid conflicts.

  • Snapshot replication takes a snapshot of current data in a publication at a Publisher and replaces the entire replica at a Subscriber on a periodic basis. 

  • Transactional replication distributes transactions to Subscribers as incremental changes are made. 

  • Merge replication allows sites to make autonomous changes to replicated data, and at a later time, merge changes made at all sites. Merge replication does not guarantee transactional consistency. 

One of the most notable new features is the immediate-updating Subscribers option, where data replicated by SQL Server 7.0 can be modified at multiple sites. This is a relatively advanced topic, with different solutions appropriate for different applications.

Replication is built directly into SQL Server 7.0 and SQL Server Enterprise Manager, and is not a separate add-on. SQL Server 7.0 replication offers many usability improvements and enhancements, making replication significantly easier to set up, administer, deploy, monitor, and troubleshoot. Wizards are included for most common replication tasks. SQL Server 7.0 also includes enhancements for Internet replication. Anonymous subscriptions and built-in support for Internet distribution simplify data replication to the Internet.

SQL Server 7.0 also includes COM interfaces that open up the store-and-forward replication services. This allows heterogeneous data providers to use the SQL Server 7.0 replication infrastructure to publish their data.

Other replication enhancements:

  • Improved replication performance 

  • Enhanced monitoring of replication activity 

  • All replication objects are scriptable 

Full-text Search 

Full-text search support is provided for character-based data in SQL Server tables. The full-text search facilities can be used to creates special indexes of all pertinent words in selected columns of selected tables. Extensions to the Transact-SQL language exploit these indexes to support linguistic and proximity searches. Several languages are supported.

Administration of full-text indexes can be managed either through SQL Server Enterprise Manager or through a new set of stored procedures. Additional new stored procedures can be used to obtain information about full-text indexes.

Unicode 

SQL Server now supports Unicode data types, which makes it easier to store data in multiple languages within one database by eliminating the problem of converting characters and installing multiple code pages. Unicode stores character data using two bytes for each character rather than one byte. There are 65,536 different bit patterns in two bytes, so Unicode can use one standard set of bit patterns to encode each character in all languages, including languages such as Chinese that have large numbers of characters. Programming languages also support Unicode data types.

The fact that Unicode data needs twice as much storage space is offset by the elimination of the need to convert extended characters between code pages. In SQL Server, the new data types that support Unicode are ntext, nchar, and nvarchar. They are exactly the same as text, char, and varchar, except for the wider range of characters supported and the restrictions on storage space (for instance, 4,000 characters instead of 8,000 characters).

Upgrades 

Databases are easily transferred from SQL Server 6.x to 7.0, by using a fully functional upgrade utility. Customers are able to quickly get up and running on the new version and take advantage of new features with minimal effect on operations.

The following scenarios illustrate how to carry out the upgrade process. Depending on how servers are set up, elements may need more than one scenario, but together they cover all the requirements.

  • Side-by-side 

    Depending on the amount of disk space, the upgrade takes place on a single computer using a disk-to-disk Named Pipes connection or a tape drive. Upgrades can be done over a direct pipeline with enough disk space. Otherwise, the SQL Server Upgrade Wizard can export the SQL Server 6.x catalog data, objects, and databases to a tape. Disk space occupied by SQL Server 6.x is reclaimed, and the SQL Server Upgrade Wizard is run again to import and upgrade the SQL Server 6.x catalog data, objects, and databases. When the upgrade is complete, SQL Server 7.0 immediately takes over as the production server. 

  • Computer-to-computer 

    Install SQL Server 7.0 on one computer and then connect to another computer where the existing SQL Server 6.x installation is installed. The upgrade takes place using a Named Pipes connection to transfer data. When the upgrade is complete, SQL Server 7.0 is running on the new computer on which it is installed, and SQL Server 6.x continues to function on the existing server. 

Multisite Management 

SQL Server has expanded server administration capabilities to manage multiple servers using one centralized server. In this version of SQL Server, you can:

  • Group servers into logical functioning units, such as by departments or business units. 

  • Perform cross-server transactions, such as creating an application that reviews and transfers product inventory from a remote warehouse to a central warehouse. 

  • Create multistep jobs, schedule the job, manage job step flow, and store job success or failure information at a central location. 

Using SQL Server Enterprise Manager, a system administrator can define a multiserver configuration, naming one server as the master server that communicates and distributes jobs, alerts, and event messages to target servers named in the configuration. The system administrator can manage and monitor server performance and the enterprise's database from a central console running SQL Server Enterprise Manager.

Repository 

Microsoft and its Data Warehousing Alliance partners have developed a set of repository extensions to provide a common development infrastructure. The repository encourages sharing of information, such as database schema, metadata, and data transformations, used in data warehousing applications.

Other metadata models will also be generated from the open design process, and the early models continue to evolve. The shared repository provides increased interoperability between the data warehousing and development tools available from many different vendors. Users benefit from easier system administration, a broader selection of compatible products, and the advanced features available from repository-based products.

The new repository extensions help expand the data warehousing market by providing an open, common infrastructure across all data warehouse and database design and analysis tool vendors. Metadata integration is one of the most important challenges that the industry must overcome to facilitate successful data warehouse and data mart implementations.

The open design review period began with the availability of preliminary specifications for data transformation services and online analytical processing (OLAP) extensions to the repository's Open Information Model. These important extensions are two of several information models being added to Microsoft Repository to facilitate the development of a broad selection of compatible products and advanced capabilities for data warehouse systems.

This phase of the open design process concluded in early 1998 with updated specifications, final input and early repository-based data warehousing product demonstrations. Information on obtaining a copy of the specifications and providing feedback during the design review period is available through the Microsoft Web site at https://msdn2.microsoft.com/en-us/magazine/cc164144.aspx .

Indexing Operations 

Indexing operations have been enhanced in several areas. SQL Server now uses index intersection and index union to take advantage of multiple indexes in a single query. Earlier versions of SQL Server employed no more than one index per table in a query.

If a table has a clustered index (and thus a clustering key), the leaf nodes of all nonclustered indexes use the clustering key as the row locator rather than the physical row location identifier. If a table does not have a clustered index, nonclustered indexes continue to use the physical row location identifiers to point to the data pages. When a leaf node of a clustered index is split, the nonclustered indexes do not need to be updated because the row locators are still valid.

Data Warehousing 

SQL Server provides a host of new features for data warehousing and data marts. Some of these features discussed in detail in other sections include:

  • New join algorithms to improve speed for complex queries. 

  • Storage engine optimized for VLDB. 

  • Utilities optimized for handling large database. 

  • Replication, group administration, and desktop version provide wide data distribution. 

  • Parallel query engine. 

OLAP Services 

Designed to support online analytical processing (OLAP) applications, OLAP Services is an essential product for a wide array of enterprise solutions ranging from corporate reporting and analysis to data modeling and decision support.

  • Intelligent aggregations provide significantly smaller databases for improved performance, and faster initial and incremental load times. 

  • Flexible storage architecture equally supports multidimensional OLAP (MOLAP), relational OLAP (ROLAP), and hybrid OLAP. Application requirements determine storage, not OLAP vendor debates. 

  • Ease of use and management lowers the cost of ownership. Widespread use of analysis functions provide comprehensive data modeling and decision support. 

OLAP Services incorporates intelligent aggregation selection, automatically choosing a subset of all possible aggregations from which the remaining aggregations can be quickly calculated when they are needed. The Aggregation Design Wizard provides additional flexibility by allowing the cube designer to specify the tradeoff between disk storage requirements and the amount of precalculated aggregation.

Cubes can be partitioned to spread data across several servers. Data is seamlessly presented to the user as if it were stored in one place. This feature enables the cube designer to make the most effective and efficient use of existing data storage facilities.

The data model provides a great deal of flexibility. DSS supports full MOLAP, ROLAP, and hybrid OLAP implementations, offering the OLAP database designer the opportunity to choose the model most appropriate to the needs of the organization. The underlying data model or data models chosen by the cube designer are invisible to the client application and its user.

PivotTable Service 

PivotTable® Service is a companion to OLAP Services that provides client access to OLAP data for custom applications.

  • PivotTable Service's client cache improves performance. 

  • Microsoft Excel pivot tables are accelerated. 

  • Mobile and disconnected analysis is supported. 

PivotTable Service runs on client workstations and enables organizations to use Visual Basic or other languages to develop custom applications that can make use of OLAP data from OLAP Services or data directly from relational databases by using Microsoft OLE DB technology. When it is used with OLAP Services, PivotTable Service automatically apportions processing and caching to the most appropriate locations and enables multiple clients to dynamically access the same cubes.

PivotTable Service can also store cubes locally on a client so you can analyze data without connecting to OLAP Services. This mobile solution enables an analyst to take the data home or on the road for analysis and presentation.

Tools for user analysis and presentation of OLAP data can be developed to use Desktop PivotTable Service. Microsoft technology such as Microsoft ActiveX® controls and Microsoft Office implementations are being created to provide powerful graphical user interfaces. The Microsoft Desktop Data Cube Service also provides interfaces that independent software vendors are using to develop third-party applications.

Microsoft Proxy Server 

The TCP/IP Sockets Net-Library has been enhanced to work with Microsoft Proxy Server and provide secure communication across the Internet. Connections can be made to SQL Server through Proxy Server, providing secured access to data. Unauthorized users are prevented from connecting to private networks. This keeps sensitive data secure by controlling all the permissions and accesses to the listening port. Proxy Server is integrated with Windows NT Server user authentication. Access is blocked to restricted sites by ranges of IP addresses, domains, or individual users to ensure that Internet permissions are used appropriately.

Graphical Administration Improvements and New Features

The easy-to-use interface in Microsoft SQL Server version 7.0 has been enhanced to include the following features:

  • Microsoft Management Console 

  • Web Assistant Wizard 

  • SQL Server Agent 

  • SQL Server Query Analyzer 

  • SQL Server Profiler 

  • Data Transformation Services 

  • Index Tuning Wizard 

Microsoft Management Console 

  • Graphical schema: table, view, and query definition 

  • Task pads, which enable you to quickly perform common tasks 

  • Server- and database-configuration user interfaces and new Windows NT Authentication support 

  • Backup user interface, which supports full, log, and differential backups 

  • Many new wizards, including Create Database, Create Job, Database Maintenance Plan, Index Tuning, SQL Server Upgrade Wizard, Web Assistant, Security, Create Trace, and Full-Text Indexing Wizard 

  • Replication setup and management with wizards 

  • Integrated HTML-based monitoring pages, which can be used in Internet Explorer 

  • Context-sensitive Help 

  • Registration of SQL Server 6.5 servers.

    The 6.5 server can be started and stopped, and selecting the 6.5 server launches the version 6.5 SQL Server Enterprise Manager. The 6.5 SQL Server Enterprise Manager must be installed on the computer. 

  • A Linked Server screen, which lets you browse linked Oracle and Microsoft Access databases 

  • A Current Server Activity screen, which lets you monitor resource usage by users connected to SQL Server 

The Microsoft Management Console (MMC) is the new user interface and framework for Microsoft BackOffice® server management. This shared console provides a convenient and consistent environment for SQL Server and other console administration tools. The SQL Server console is SQL Server Enterprise Manager. MMC's point-and-click user interface is similar to Windows Explorer.

Web Assistant Wizard 

The Web Assistant Wizard has been enhanced in SQL Server 7.0. In addition to exporting SQL Server data out to an HTML file, it can also import tabular data from an HTML file into SQL Server, and post to and read from HTTP and FTP locations.

SQL Server Agent 

  • Unattended job execution and alert/response management 

  • Multitask jobs in Transact-SQL, ActiveScript, or operating-system command files 

  • Multischedule jobs (scheduled or event-driven job execution) 

  • Multiserver jobs (define jobs once to be executed on multiple servers) 

Alerts can be defined to raise a response when an event occurs with a system or user-defined error or severity level. Alert responses are one or more of the following:

  • Sending operator notification 

  • Logging the alert in the Windows NT application log 

  • Executing a job 

SQL Server Agent monitors events in the Windows NT application log. When an event occurs, SQL Server Agent compares the event details with the alerts defined for the environment. If a match is found, SQL Server Agent implements the specified alert response.

SQL Server jobs can increase system efficiency and productivity. Using the job features in SQL Server Enterprise Manager, the following tasks can be defined and edited:

  • Local or multiserver jobs 

  • Job schedules 

  • Single step or multistep jobs 

  • Actions to take on success or failure of each job step 

SQL Server Query Analyzer 

  • Transact-SQL authoring tool with Graphical Showplan, which analyzes the plan and recommends optimal indexes to improve performance 

  • Color-coded editor 

  • Context-sensitive Help and Transact-SQL Help 

SQL Server Query Analyzer provides a way to analyze graphically the plan of a query, execute multiple queries simultaneously, view data, and choose indexes. SQL Server Query Analyzer provides the SHOWPLAN option that is used to report data retrieval methods chosen by the SQL Server optimizer and recommends optimal indexes to improve performance.

SQL Server Profiler 

  • Monitoring, capture, and analysis of SQL Server activity 

  • Monitoring of queries, stored procedures, locks, transactions, and log changes 

  • Replay of captured data on another server 

SQL Server Profiler captures a continuous picture of server activity in real time. You pick the items and events that you want to monitor, including Transact-SQL statements and batches, object usage, locking, security events, and errors. SQL Server Profiler filters these events. You can replay a recorded trace against the same or another server, reexecuting those recorded commands. By focusing on specific events, monitoring and debugging SQL Server is much easier.

Data Transformation Services 

  • OLE DB-based interface for defining and executing data transformations 

  • ActiveScript-based transformations 

  • Support of high-speed bulk interface for fast loading using OLE DB 

  • Support for ODBC-based data sources, including Access, Excel, and flat files 

  • DTS Export and DTS Import wizards for quickly moving data and schema into and out of SQL Server 

  • Scheduled transformations using SQL Server Agent 

Data Transformation Services (DTS) provides the functionality to import, export, and transform data between SQL Server and any OLE DB, ODBC, or text file format. Using DTS, it is possible to build data warehouses and data marts in SQL Server by importing and transforming data from multiple heterogeneous sources interactively or automatically on a regularly scheduled basis (requiring no user intervention). Custom transformation objects can be created that integrate into third-party products.

Importing and exporting data is the process of exchanging data between applications by reading and writing data in a common format. For example, DTS can import data from an ASCII text file into SQL Server. Alternatively, data can be exported from SQL Server to an ODBC data source, or an Excel spreadsheet.

A transformation is the set of operations applied to source data before it can be stored in the destination. For example, DTS allows calculating new values from one or more source fields, or even breaking a single field into multiple values to be stored in separate destination columns. Transformations make it easy to implement complex data validation, scrubbing, and enhancements during import and export.

DTS supports multistep packages, where multiple files can be processed separately, then brought together in a single, final step. Records in a file can be broken up into multiple records in the destination, or multiple records in the source can be aggregated into single records in the destination.

DTS is also integrated with the Microsoft Repository, where it stores metadata, DTS packages, and data lineage, including sources of all transformed data.

DTS only moves schema and data between heterogeneous sources. Triggers, stored procedures, rules, defaults, constraints, and user-defined data types are not converted between heterogeneous data sources.

Index Tuning Wizard 

  • Optimizer chooses the best index and workload analysis determines the best index mix for optimal performance. 

Architecture Enhancements

Microsoft SQL Server version 7.0 simplifies the development, deployment, maintenance, and management of your database applications with its redesigned architecture. SQL Server 7.0 supports applications that span a broad range of platforms: from personal systems, such as desktop and notebook computers, to high-end symmetric multiprocessing (SMP) servers with 8 to 16 processors, several gigabytes of memory, and a terabyte or more of disk storage.

The major architectural advancements include:

  • New on-disk formats for items such as pages, rows, extents, data files, and log files 

    These new formats allow the server to easily scale from low-end to high-end systems, improving performance and manageability. They also enable new capabilities in future releases, such as embedded tables, multiple types of rows on a single data page, and databases that can be electronically mailed. 

  • Upgraded database tools and utilities 

    SQL Server 7.0 includes new backup and restore mechanisms and DBCC algorithms. The bulk copy capabilities are more tightly integrated with the query processor and support the new Unicode and GUID data types. 

  • Row-level locking 

    SQL Server 7.0 supports complete row-level locking on both data pages and index pages. 

  • Redesigned query processor 

    SQL Server 7.0 includes revisions to SQL Server's query-processing capabilities. The improved query processor supports new query execution algorithms such as index intersection and hash joins as well as parallel query execution, designed to speed up the processing of queries over very large databases. The query optimizer uses an improved costing model and new techniques for plan selection. The query process also supports distributed and heterogeneous query capabilities for accessing data from any OLE DB and ODBC data source. 

Additional enhancements include the following:

  • Databases and files 

  • Pages, rows, and columns 

  • Indexes 

  • text and image data 

  • Row-level locking 

  • Transaction log 

  • tempdb database 

  • Query processor 

  • Distributed queries 

Databases and Files 

SQL Server 7.0 simplifies the relationship between a SQL Server database and the Microsoft Windows file system, enabling greater scalability.

In SQL Server 7.0, databases reside on operating-system files instead of on SQL Server logical devices. You can create a database and all its files with a single CREATE DATABASE statement. SQL Server Enterprise Manager can create new databases and modify (alter) existing databases.

Database devices and segments no longer reside on top of the operating-system files; rather, a SQL Server 7.0 database consists of two or more Windows files. Each Windows file is used by only one database. A single file cannot be shared by multiple databases. When a database is dropped, its files are also deleted.

SQL Server 7.0 allows database files to expand automatically, eliminating the need for administrators to issue an additional ALTER statement. SQL Server 7.0 includes new CREATE DATABASE syntax that incorporates this file-based implementation.

Filegroups are supported. Filegroups are similar to the user-defined segments available in earlier versions of SQL Server.

SQL Server 7.0 supports movable databases, the ability to move a database by moving, copying, or e-mailing the database files from one server to another and attaching them to the copy of SQL Server running on the target server.

Pages, Rows, and Columns 

All database pages are now 8 KB in size, increased from 2 KB. The maximum number of bytes in a row is now 8060 bytes, and the limit on character and binary data types is 8000 bytes, increased from 255 bytes. Tables can now have 1024 columns, a significant increase over the 250 columns supported earlier. The new page and row formats support row-level locking, are extensible for future requirements, and improve performance when large blocks of data are accessed, because each I/O operation retrieves more data.

Indexes 

In earlier versions of SQL Server, nonclustered indexes used physical record identifiers (page number, row number) as row locators. For example, if the leaf node of a clustered index (data page) was split, many rows were moved to a new data page, and thus had new physical record identifiers. All of the nonclustered indexes had to be updated with these new physical record identifiers, which could require a significant amount of time and resources.

SQL Server 7.0 uses an improved index design. If a table has a clustered index (and thus a clustering key), the leaf nodes of all nonclustered indexes use the clustering key as the row locator rather than the physical record identifier. If a table does not have a clustered index, nonclustered indexes continue to use the physical record identifiers to point to the data pages. In both cases, the row locator is stable. When a leaf node of a clustered index is split, SQL Server 7.0 does not need to update the nonclustered indexes because the row locators are still valid. If a table does not have a clustered index, page splits do not occur.

Earlier versions of SQL Server employed no more than one index per table in a query. SQL Server 7.0 uses index intersection and index union to take advantage of multiple indexes in a query. SQL Server 7.0 also uses unique row identifiers to join two indexes on the same table.

text and image Data 

Storage of text and image data has been redesigned. SQL Server 7.0 can store more than one text or image value on a single data page. Space for small text or image values is thus used more efficiently. SQL Server 7.0 uses parallel retrieval of text and image data to optimize retrieval of large objects.

Row-level Locking 

The locking subsystem has changed significantly. Most importantly, SQL Server 7.0 now supports full row-level locking for both data rows and index entries. The SQL Server 7.0 lock manager has been optimized to complete lock requests faster and with less internal synchronization. Many OLTP applications can experience increased concurrency, especially when applications append rows to tables and indexes.

The SQL Server 7.0 lock manager adjusts the resources it uses for larger databases dynamically, eliminating the need to adjust the locks server configuration option manually. SQL Server 7.0 chooses automatically between page locking (preferable for table scans) and row-level locking (preferable for inserting, updating, and deleting data).

Transaction Log 

Before SQL Server 7.0, the log was a system table (syslogs) that used ordinary database pages. These log pages were allocated and deallocated just like pages of other tables, and they competed with data pages for space in the memory cache.

The SQL Server 7.0 transaction log is significantly different. The SQL Server 7.0 log consists of one or more log files, each containing a contiguous set of log records. The log is no longer represented as a system table. The SQL Server 7.0 log design enables larger I/Os to the log than were possible earlier.

tempdb Database 

The tempdb system database expands automatically as needed. For example, a reporting application may be submitted that needs a lot of space in tempdb. Rather than failing with an out-of-space error, tempdb grows automatically to the size needed to support the report. SQL Server 7.0 reinitializes tempdb to the configured size the next time the server is started.

Query Processor 

The SQL Server 7.0 query processor has been redesigned to support the large databases and complex queries found in decision support, data warehouse, and OLAP applications. The query processor includes several new execution strategies that can improve the performance of complex queries. It now uses hash join, merge join, and hash aggregation techniques. These techniques can scale to larger databases than those supported by the nested loops join technique only. SQL Server 7.0 uses index intersection and union techniques on multiple indexes to filter data before it retrieves rows from the database. All indexes on a table are maintained concurrently and constraint evaluations are part of the query processor's execution plan. These two factors simplify and speed the updating of multiple rows of a table.

The SQL Server 7.0 query processor automatically generates statistics about index key distributions using efficient sampling algorithms. Distribution statistics reflect how likely it is that an index key value will uniquely identify rows in the table and is used by the query optimizer when it is determining how to access tables. Having the server automatically refresh these statistics ensures that the query optimizer can build efficient query execution plans and eliminates the need to manually update the statistics.

The SQL Server 7.0 query optimizer has a wide set of execution strategies, and many of the optimization limitations of earlier versions of SQL Server have been removed. An improved costing model and compile-time enhancements, such as predicate transitivity and constant folding, greatly improve the quality of query plans. The cost-based optimizer can be rapidly adapted for new or refined execution strategies.

To examine how the SQL Server 7.0 query processor executes a query, view the Plan tab of SQL Server Query Analyzer or the Transact-SQL SHOWPLAN statement output. The query processor supports new query hints, and some query limitations have been loosened or removed. For example, a single query can now reference 256 tables, and the number of internal work tables used by a query (16 in earlier versions) is no longer limited.

Distributed Queries 

The query processor uses OLE DB to communicate with the data-storage components of SQL Server. OLE DB provides the SQL Server 7.0 query processor with distributed and heterogeneous query capabilities. It supports distributed queries between multiple SQL Server 7.0 servers as well as to any OLE DB provider. For more information about distributed queries, see "Distributed Queries" in Microsoft SQL Server Database Developer's Companion or "Distributed Query Architecture" in this volume.

See Also 

In Other Volumes 

"ALTER DATABASE" in Microsoft SQL Server Transact-SQL and Utilities Reference 

"CREATE DATABASE" in Microsoft SQL Server Transact-SQL and Utilities Reference 

"CREATE INDEX" in Microsoft SQL Server Transact-SQL and Utilities Reference 

"Indexes" in Microsoft SQL Server Database Developer's Companion 

"Locking" in Microsoft SQL Server Database Developer's Companion 

"SET SHOWPLAN_ALL" in Microsoft SQL Server Transact-SQL and Utilities Reference 

"SET SHOWPLAN_TEXT" in Microsoft SQL Server Transact-SQL and Utilities Reference 

"sp_lock" in Microsoft SQL Server Transact-SQL and Utilities Reference 

"syslockinfo" in Microsoft SQL Server Transact-SQL and Utilities Reference 

"Analyzing a Query" in Microsoft SQL Server Diagnostics 

Server Enhancements

The redesign of the server architecture results in new and improved server functionality. Changes have been made to the server configuration options and to the server utilities such as backup, restore, DBCC, and bulk copy. Microsoft SQL Server version 7.0 provides for parallel query execution, more powerful security, and improved stored procedure performance.

With SQL Server 7.0, many advancements have been made to SQL Server replication:

  • Configuration options 

  • Backup, restore, DBCC, and bulk copy 

  • Parallel query execution 

  • Security 

  • Stored procedures 

  • Consistency and standards compliance 

  • Replication 

  • SQL Server Enterprise Manager 

  • SQL Server Agent 

  • SQL Server Service Manager 

Configuration Options 

Many server configuration options have been streamlined and simplified. For example, by default the server dynamically adjusts its memory and lock resource use allocated. SQL Server 7.0 increases resources when necessary without over-committing them and decreases the resources used when they are no longer needed. Earlier versions of SQL Server required manual adjustment of these settings.

Backup, Restore, DBCC, and Bulk Copy 

SQL Server 7.0 server utilities, such as backup and restore, run much faster and have less effect on server operations. SQL Server 7.0 also includes a variety of new features designed to support the underlying database architecture and to provide more flexible system management.

SQL Server 7.0 also includes a number of new features designed to reduce database backup and recovery times. A differential backup captures only those data pages that have changed after the last database backup. Many times, differential backups can eliminate much of the time the server spends rolling transactions forward. With SQL Server 7.0, a portion of the database can be restored or rolled forward to minimize recovery time in the event of media failure. Restoring a backup is easy because the restore process automatically creates the database and all necessary files. SQL Server 7.0 supports backups using the Microsoft Tape Format, allowing SQL Server backups to share the same tape media with other backups, such as those written by the Microsoft Windows NT Backup program.

The DBCC statements have been redesigned to provide substantially improved performance. In addition, bulk copy operations now have the option to validate constraints and fire triggers as the data is loaded.

The bulk copy utility (bcp) uses ODBC to communicate with SQL Server and supports all SQL Server 7.0 data types. The SQL Server 7.0 server uses improved index maintenance strategies that make loading data into tables with indexes more efficient than earlier versions. Bulk copy operations are also faster.

Parallel Query Execution 

SQL Server 7.0 supports parallel execution of a single query across multiple processors. A CPU-bound query that must examine a large number of rows often benefits if portions of its execution plan run in parallel. SQL Server 7.0 automatically determines which queries will benefit from parallelism and generates a parallel execution plan. If multiple processors are available when the query begins executing, the work is divided across the processors. Parallel query execution is enabled by default.

Security 

SQL Server 7.0 includes an enhanced security architecture that is better integrated with Windows NT and provides increased flexibility. Database permissions can now be assigned directly to Windows NT users. You can define SQL Server roles to include not only Windows NT users and groups but also SQL Server users and roles. In addition, a SQL Server user can be a member of multiple SQL Server roles. This allows database administrators to manage SQL Server permissions as Windows NT groups or SQL Server roles, rather than as individual user accounts. You can now optionally manage database access and permissions using Windows NT groups. New fixed server and database roles such as dbcreator, db_owner, and securityadmin provide more flexibility and improved security than the system administrator login.

Stored Procedures 

The stored procedure model has been enhanced in SQL Server 7.0 to provide improved performance and increased application flexibility. When a stored procedure is compiled and placed in the procedure cache, that one copy of the compiled plan is shared by all users of the stored procedure. In previous releases, each concurrent user would have a separate copy of the compiled plan. SQL statements submitted through the ODBC SQLPrepare function and the OLE DB ICommandPrepare interface can also share plans. Simple ad hoc plans are shared as well. Prepared and ad hoc plans reuse is supported further by passing parameter markers all the way through to the database. Deferred name resolution allows you to create stored procedures referencing objects that don't yet exist. This provides more flexibility for applications that create and then use tables as part of their processing.

Consistency and Standards Compliance 

SQL Server 7.0 builds on SQL Server's compliance with the SQL-92 standard by focusing on SQL-92 as the preferred SQL dialect. SQL Server 7.0 addresses several inconsistencies in earlier versions of SQL Server, including several differences between actual and documented behaviors. SQL Server 7.0 also fixes several problems in earlier versions of SQL Server on which an application might have accidentally relied. In the small number of cases where a change in the default behavior of SQL Server 7.0 may affect existing applications, options have been provided to retain the pre-7.0 behavior. These options are controlled by the sp_dboption or sp_dbcmptlevel stored procedures.

Replication 

SQL Server 7.0 includes many new capabilities and improvements to the publish and subscribe replication services that were provided by SQL Server 6.5. The most notable feature is update replication. Using update replication, data replicated by SQL Server 7.0 can be modified at multiple sites. Update replication is a relatively advanced topic with different solutions being appropriate for different applications. Thus, SQL Server 7.0 includes several styles of update replication, providing a spectrum of replication solutions with different features and benefits.

The features and architecture of SQL Server 6.5 replication remain at the core of SQL Server 7.0 replication services. As always, replication is built directly into SQL Server 7.0 and SQL Server Enterprise Manager, and is not a separate add-on. SQL Server 7.0 replication offers many usability improvements and enhancements, making replication significantly easier to set up, administer, deploy, monitor, and troubleshoot. Wizards are included for most common replication tasks. SQL Server 7.0 also includes enhancements for Internet replication. Anonymous subscriptions and built-in support for Internet distribution simplify data replication to the Internet.

SQL Server 7.0 also includes COM interfaces that open up the store-and-forward replication services. This allows data providers other than SQL Server to use the SQL Server 7.0 replication infrastructure to publish their data. SQL Server 7.0 now provides completely heterogeneous data-replication services.

SQL Server Enterprise Manager 

SQL Server Enterprise Manager is based on a new common server management environment called Microsoft Management Console (MMC). This shared framework provides a consistent user interface for Microsoft server applications.

Using SQL Server Enterprise Manager, you can examine and configure your SQL Server 7.0 system by setting server properties, database properties, remote server properties, and security properties. You can create and alter tables, views, stored procedures, rules, defaults, and user-defined data types, as well as manage alerts and operators, view error logs, create Web Assistant jobs, create and manage full-text indexes, and import and export data.

SQL Server Enterprise Manager includes full-featured job creation and scheduling. Jobs can be simple, single-step commands that are scheduled to run on a regular basis. Alternatively, they can be complex, multistep jobs that completely control flow and notification options. You can even establish multiple schedules for a single job. Job steps can be created using Transact-SQL, Microsoft Visual Basic Scripting Edititon, Microsoft JScript, or operating-system commands.

SQL Server 7.0 includes these major SQL Server Enterprise Manager features:

  • Multiserver administration 

  • Backup and restore capabilities 

  • User interface for Data Transformation Services 

SQL Server Agent 

SQL Executive is now called SQL Server Agent. SQL Server Agent manages jobs, alerts, operators, and notifications, as well as replication jobs. SQL Server Agent allows multitasking, multischeduling, and multiserver and idle-time jobs.

SQL Server Service Manager 

SQL Server Service Manager is now a taskbar application. SQL Server Service Manager enables you to start, stop, and pause the MSSQLServer, MSDTC, MSSearch (only start and stop are applicable), and SQLServerAgent services, and to view their status at any time. When you select SQL Server Service Manager from the Start menu, the Service Manager icon appears minimized in the taskbar by default. Multiple instances of the Service Manager are represented by multiple icons in the taskbar.

See Also 

In This Volume 

Backward Compatibility

Microsoft Management Console

In Other Volumes 

"BACKUP" in Microsoft SQL Server Transact-SQL and Utilities Reference 

"Backing Up and Restoring Databases" in Microsoft SQL Server Administrator's Companion 

"bcp Utility" in Microsoft SQL Server Transact-SQL and Utilities Reference 

"Configuring Publications for the Internet" in Microsoft SQL Server Distributed Data Operations and Replication 

"Automating Administrative Tasks" in Microsoft SQL Server Administrator's Companion 

"Managing Servers" in Microsoft SQL Server Administrator's Companion 

"Implementing Transactional and Snapshot Replication from Heterogeneous Data Sources" in Microsoft SQL Server Distributed Data Operations and Replication 

"Replicating to Heterogeneous Data Sources" in Microsoft SQL Server Distributed Data Operations and Replication 

"Overview of Replication" in Microsoft SQL Server Distributed Data Operations and Replication 

"RESTORE" in Microsoft SQL Server Transact-SQL and Utilities Reference 

"Managing Security" in Microsoft SQL Server Administrator's Companion 

"Setting Configuration Options" in Microsoft SQL Server Administrator's Companion 

"Trace Flags" in Microsoft SQL Server Transact-SQL and Utilities Reference 

Development Enhancements

Microsoft SQL Server version 7.0 has additional development flexibility and power and easier application development tools.

SQL Server 7.0 is compatible with SQL Server 6.x applications and provides new and enhanced features for developers. SQL Server 7.0 includes internal enhancements that benefit all applications, such as increased query performance, full row-level locking, and new deadlock avoidance strategies and lock escalation policies that should reduce contention problems. SQL Server 7.0 now supports OLE DB as a native programming interface. Improvements to the Transact-SQL language and the ODBC programming API are also included.

Developing certain types of applications, such as data warehousing and Internet/intranet applications, is easier and more efficient when using SQL Server 7.0. Support for debugging and profiling your applications has been enhanced in SQL Server 7.0.

SQL Server 7.0 is well-integrated with other Microsoft development tools. You can debug SQL Server stored procedures using Microsoft Visual C++® and Microsoft Visual Basic.

SQL Server Books Online provides SQL Server-specific information about developing and optimizing the performance of your applications using standard interfaces, such as ODBC, OLE DB, and ADO. This release of SQL Server 7.0 includes a set of ODBC and OLE DB sample programs.

Additional development enhancements include:

  • Microsoft English Query 

  • Transact-SQL 

  • OLE DB 

  • ODBC 

  • Application types 

  • Prototyping, profiling, and debugging 

  • Other programming interfaces 

English Query 

Included on the SQL Server 7.0 compact disc is Microsoft English Query, a development tool designed to give users of your database applications the ability to query a SQL Server database in English. For example, users can simply ask the question, "How many widgets were sold in Washington last year?" instead of writing complex Transact-SQL statements. Features include:

  • Information retrieval from SQL Server using English sentences 

  • Graphical authoring tool for defining databases to English Query 

  • Microsoft ActiveX object support, which enables embedding of English Query in applications 

  • Intersentence referencing that provides drill-down into queries 

  • Question builder (Knowledge Explorer), which uses English concepts to familiarize users with the database contents 

  • Enterprise authoring support, which allows multiple developers to work on the same "model" of the database 

  • Generation of efficient queries for denormalized databases 

  • Author improvements, which describe less frequent database design maps to English concepts 

  • Hot swapping of domains (loading a new version of a connection on top of an old version without restarting) 

  • Context-sensitive Help 

English Query applications are created with the English Query domain editor. Information is provided about the database so that English Query can process English questions about particular tables, fields, and data.

An English Query application can be deployed in several ways, including within a Visual Basic or Microsoft Visual C++ based application, or on a Web page running on Internet Information Services. In the Web scenario, the application is created with a Visual Basic Scripting Edition page, which is designed for use with Microsoft Active Server Pages server scripting.

Transact-SQL 

SQL Server 7.0 offers many new Transact-SQL features, including but not limited to:

  • Unicode data types: nchar, nvarchar, and ntext. For more information about how SQL Server 7.0 implements Unicode, see "UNICODE" or "Data Types" in Microsoft SQL Server Transact-SQL and Utilities Reference. 

  • TOP n [PERCENT] extension to the SELECT statement. 

  • Local cursors, cursor variables, returning cursors as parameters from stored procedures, additional properties for cursors declared through the DECLARE CURSOR statement, and functions and stored procedures for describing cursors. 

Identifiers can now be a maximum of 128 characters, increased from the 30 characters of earlier versions. In addition, the left square bracket ([) and right square bracket (]) can be used for delimiting identifiers in addition to the SQL-92 standard double quotation mark ("). For more information, see "Using Identifiers" in Microsoft SQL Server Database Developer's Companion.

SQL Server 7.0 gives you complete flexibility to design and redesign tables. You can remove an existing column and its data from a table. You no longer need to export data in a separate step. Prior to SQL Server 7.0, only nullable columns could be added to a table. Now, you can add nonnullable columns without having to import data in a separate step.You can also change the datatype of existing columns.

SQL Server 7.0 includes views for the ANSI/ISO schema information tables as defined in SQL-92, providing a standard way to examine metadata of a SQL Server database.

SQL Server 7.0 makes revising database objects easier by allowing you to change the definition of a procedure, trigger, or view in place without disturbing permissions or dependencies.

SQL Server 7.0 uses deferred name resolution in stored procedures, triggers, and statement batches. For example, you can now create a procedure that references a table that does not exist when the procedure is created, providing greater flexibility to applications that create tables at run time.

You can now append multiple triggers of the same type to a single table. For example, a single table can have one delete trigger, three insert triggers, and two update triggers. This enhancement allows you to put different business rules into different triggers. A database option allows triggers to call themselves recursively.

The maximum length of the char, varchar, binary, and varbinary data types is now 8000 bytes, an increase from the limit of 255 bytes in SQL Server 6.x. The Transact-SQL string functions also support these very long char and varchar values. You can reserve the use of text and image data types for very large data values. You can now use the SUBSTRING function to process text and image columns. The handling of NULLs and empty strings has been improved. SQL Server 7.0 also includes a new uniqueidentifier data type for storing a globally unique identifier (GUID).

The SQL Server 7.0 query processor uses new execution strategies and algorithms (including hash, sort, and merge iterators) to provide improved performance.

For more information about new Transact-SQL features, see "New Features in Transact-SQL" in Microsoft SQL Server Transact-SQL and Utilities Reference. 

OLE DB 

SQL Server 7.0 includes a native OLE DB Provider for SQL Server that complies with the OLE DB 2.0 specification. The provider fully supports SQL Server 7.0 and 6.5.

The OLE DB Provider for SQL Server also supports the following SQL Server-specific interfaces:

  • IRowsetFastLoad to expose bulk copy. 

  • ISQLServerErrorInfo to expose SQL Server-specific error information. 

The OLE DB Provider for SQL Server supports a LINKED SERVERS rowset for querying the catalogs of heterogeneous OLE DB data sources linked to SQL Server.

The OLE DB Provider for SQL Server fully supports applications using:

  • ActiveX Data Objects (ADO) 

  • OLE DB 

It is recommended that you use OLE DB for low-level development (within the COM environment) of your data access infrastructure. For example, use OLE DB if you are developing a middle-tier data provider that needs to expose a private interface other than ADO, or if you need to share rowsets with other OLE DB consumers. It is recommended that you use ADO for developing your business applications.

The SQL Server 7.0 server engine uses OLE DB for communication:

  • Between internal components, such as the storage engine and relational engine. 

  • Between SQL Server installations when using remote stored procedures. 

  • As the interface to other data sources for distributed queries. 

ODBC 

SQL Server 7.0 includes an updated SQL ODBC Server 3.7 driver that is compliant with the Open Database Connectivity 3.51 specification and the ODBC 3.7 Driver Manager. The SQL Server ODBC driver fully supports SQL Server 7.0 and 6.x servers. It is recommended that you use ODBC for low-level development (outside of the COM environment) of SQL database applications. Use ADO with the OLE DB Provider for SQL Server to develop your business applications, and use OLE DB with the OLE DB Provider for SQL Server to develop your data access infrastructure in the COM environment.

The SQL Server ODBC driver includes a new set of SQL Server bulk copy functions that are identical to the DB-Library bulk copy functions.

Descriptors allow you to execute procedures using named parameters (earlier, only positional parameters were supported) and to share bindings between statements. Diagnostic arrays provide more detailed SQL Server error information, including the severity, state, procedure name, and line number of SQL Server messages. Quick rebinding by offset allows you to use an existing set of bindings with a new memory location, which eliminates many redundant ODBC function calls and improves performance. Connection pooling provides increased performance to applications that make and break the same connection to SQL Server. For more information, see your Microsoft ODBC documentation in the Microsoft Data Access 2.0 SDK.

ODBC applications can also use auto-fetch fast-forward cursors introduced in SQL Server 7.0. When an application can cache the entire result set of an SQL statement, an auto-fetch fast-forward cursor automatically fetches all the rows into the application and then closes the cursor at the time the statement is executed. This improves the ODBC driver's ability to execute multiple statements on a single connection.

The Microsoft Server DSN Configuration Wizard makes creating and managing SQL Server data sources easier. File DSN support makes distributing data sources easier. The SQL Server ODBC driver also has other improvements when connecting to SQL Server 7.0 servers, such as establishing more efficient connections that are completed in a single roundtrip using default settings.

The SQL Server ODBC 3.7 driver fully supports programs using:

  • OLE DB Provider for ODBC 

  • Data Access Objects (DAO) 

  • Remote Data Objects (RDO) 

The driver also fully supports Web pages using:

  • Active Server Pages (ASP) 

  • Internet Database Connector (IDC) 

Through the OLE DB Provider for ODBC, the SQL Server ODBC 3.7 driver also supports applications using OLE DB and ActiveX Data Objects, although it is recommended that these applications instead use the native OLE DB Provider for SQL Server.

Application Types 

SQL Server 7.0 includes features and enhancements specifically designed for certain types of applications.

  • Data warehousing applications 

    SQL Data Transformation Services (DTS) provides a set of COM objects based on OLE DB that let you define and execute complex data conversions between OLE DB data providers. You can use Visual Basic Scripting Edition or Microsoft JScript to create data transformation scripts. 

  • Replication applications 

    Replication programming components include COM objects you can use to distribute data from third-party data sources. They also include Microsoft ActiveX controls that applications can use to implement distribution and merge functionality without dependency on SQL Server Agent. 

  • Internet and intranet applications 

    The Web Assistant Wizard has been enhanced in SQL Server 7.0. In addition to exporting SQL Server data out to an HTML file, it can also import tabular data from an HTML file into SQL Server, and post to and read from HTTP and FTP locations. The TCP/IP Sockets Net-Library has been enhanced to work with Microsoft Proxy Server and provide secure communication across the Internet. SQL Server 7.0 also makes replicating your data across the Internet easy with anonymous subscriptions and built-in support for Internet distribution. 

  • Distributed applications 

    You can use MS DTC for distributing transactions among multiple SQL Server installations using the two-phase commit protocol. In addition, the store-and-forward replication services of SQL Server 7.0 are exposed through COM interfaces, allowing your application to use the SQL Server 7.0 replication infrastructure to publish your data. 

  • Administration tools 

    SQL Distributed Management Objects (SQL-DMO) has been redesigned and expanded to reflect SQL Server 7.0 features and architecture. SQL Server 7.0 SQL-DMO objects are very similar to the SQL Server 6.x SQL-DMO objects (with some exceptions, such as the replication hierarchy and the Device object). 

Prototyping, Profiling, and Debugging 

SQL Trace has been completely redesigned and is now called SQL Server Profiler. SQL Server Profiler captures a continuous picture of server activity in real time. You pick the items and events to monitor, including Transact-SQL statements and batches, object usage, locking, security events, and errors. SQL Server Profiler can filter these events, showing only the events that you care about. You can replay a recorded trace against the same or another server, reexecuting those recorded commands. By focusing on specific events, monitoring and debugging SQL Server issues is much easier.

ISQL/w is now called SQL Server Query Analyzer. SQL Server Query Analyzer implements a color-coded editor, Graphical Showplan, and Help integration for checking syntax. SQL Server Query Analyzer communicates with SQL Server through ODBC.

SQL Server 7.0 also includes the osql command prompt utility, which uses ODBC to communicate with SQL Server. This utility is similar to isql, which uses the DB-Library interface.

Other Programming Interfaces 

SQL Server Setup includes options for installing the programming libraries and sample programs. All programming libraries are located in a single \Lib directory, and all programming headers are located in a single \Include directory.

SQL Server 7.0, like earlier versions, includes these programming interfaces:

  • Embedded SQL for C for running batch jobs.

    Embedded SQL for C now supports building digital Alpha applications. 

  • Open Data Services, for writing extended stored procedures. 

  • DB-Library for C. 

    These libraries are still included and supported at their version 6.5 level. They are not enhanced for SQL Server 7.0. 

See Also 

In This Volume 

Backward Compatibility

SQL Server Query Analyzer

In Other Volumes 

"ALTER TABLE" in Microsoft SQL Server Transact-SQL and Utilities Reference 

"ALTER PROCEDURE" in Microsoft SQL Server Transact-SQL and Utilities Reference 

"ALTER TRIGGER" in Microsoft SQL Server Transact-SQL and Utilities Reference 

"ALTER VIEW" in Microsoft SQL Server Transact-SQL and Utilities Reference 

"CREATE PROCEDURE" in Microsoft SQL Server Transact-SQL and Utilities Reference 

"CREATE TRIGGER" in Microsoft SQL Server Transact-SQL and Utilities Reference 

"char and varchar" in Microsoft SQL Server Transact-SQL and Utilities Reference 

"binary and varbinary" in Microsoft SQL Server Transact-SQL and Utilities Reference 

"uniqueidentifier" in Microsoft SQL Server Transact-SQL and Utilities Reference 

"Overview of Data Transformation Services" in Microsoft SQL Server Distributed Data Operations and Replication 

"New Features in Transact-SQL" in Microsoft SQL Server Transact-SQL and Utilities Reference 

"Programming Open Data Services" in SQL Server Books Online

"osql Utility" in Microsoft SQL Server Transact-SQL and Utilities Reference 

"Programming Embedded SQL for C" in SQL Server Books Online

"Programming ODBC SQL Server Applications" in Microsoft SQL Server Building Applications 

"Developing SQL-DMO in Applications" in Microsoft SQL Server Distributed Management Objects 

"Configuring Publications for the Internet" in Microsoft SQL Server Distributed Data Operations and Replication 

"Using the Web Assistant Wizard" in Microsoft SQL Server Administrator's Companion 

"Monitoring with SQL Server Profiler" in Microsoft SQL Server Administrator's Companion