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:
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:
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.
SQL Server 7.0 offers many new Transact-SQL features, including but not limited to:
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.
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.
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:
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:
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:
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:
The driver also fully supports Web pages using:
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.
SQL Server 7.0 includes features and enhancements specifically designed for certain types of 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 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.
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.
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.
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).
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.
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 now supports building digital Alpha applications.
These libraries are still included and supported at their version 6.5 level. They are not enhanced for SQL Server 7.0.