What's New in SQL Server 2000
This sample chapter explores the new features provided in SQL Server 2000, as well as many of the enhancements to previously available features.
The upgrade from SQL Server 6.5 to 7.0 was pretty significant. In addition to many new features, the underlying SQL Server architecture changed considerably. The upgrade from SQL Server 7.0 to 2000 is more of a series of enhancements, additions, and improvements. The architectural differences between the two are minimal. As a matter of fact, a database on a 7.0 SQL Server can be migrated over to SQL Server 2000 via a simple dump and restore.
New SQL Server 2000 Features
What does SQL Server 2000 have to offer over SQL Server 7? The following is a list of the new features provided in SQL Server 2000:
- User-defined functions
- Indexed views
- Distributed partitioned views
- INSTEAD OF and AFTER triggers
- New datatypes
- Cascading RI constraints
- Multiple SQL Server instances
- XML support
- Log shipping
The rest of this section takes a closer look at each of these new features and provides a reference to subsequent chapters where more information about the new feature can be found.
SQL Server has always provided the ability to store and execute SQL code routines via stored procedures. In addition, SQL Server has always supplied a number of built-in functions. Functions can be used almost anywhere an expression can be specified in a query. This was one of the shortcomings of stored proceduresthey couldn't be used inline in queries in select lists, where clauses, and so on. Perhaps you want to write a routine to calculate the last business day of the month. With a stored procedure, you have to exec the procedure, passing in the current month as a parameter and returning the value into an output variable, and then use the variable in your queries. If only you could write your own function that you could use directly in the query just like a system function. In SQL Server 2000, you can.
SQL Server 2000 introduces the long-awaited support for user-defined functions. User-defined functions can take zero or more input parameters and return a single valueeither a scalar value like the system-defined functions, or a table result. Table-valued functions can be used anywhere table or view expressions can be used in queries, and they can perform more complex logic than is allowed in a view.
For more information on defining, managing, and using user-defined functions, see Chapter 16, "User-Defined Functions."
Views are often used to simplify complex queries, and they can contain joins and aggregate functions. However, in the past, queries against views were resolved to queries against the underlying base tables, and any aggregates were recalculated each time you ran a query against the view. In SQL Server 2000 Enterprise or Developer Edition, you can define indexes on views to improve query performance against the view. When creating an index on a view, the result set of the view is stored and indexed in the database. Existing applications can take advantage of the performance improvements without needing to be modified.
Indexed views can improve performance for the following types of queries:
Joins and aggregations that process many rows
Join and aggregation operations that are performed frequently within many queries
Decision support queries that rely on summarized, aggregated data that is infrequently updated
For more information on designing, using, and maintaining indexed views, see Chapter 13, "Creating and Managing Views in SQL Server."
Distributed Partitioned Views
SQL Server 7.0 provided the ability to create partitioned views using the UNION ALL statement in a view definition. It was limited, however, in that all the tables had to reside within the same SQL Server where the view was defined. SQL Server 2000 expands the ability to create partitioned views by allowing you to horizontally partition tables across multiple SQL Servers. The feature helps you scale out one database server to multiple database servers, while making the data appear as if it comes from a single table on a single SQL Server. In addition, partitioned views are now able to be updated.
For more information on defining and using distributed partitioned views, see Chapter 13, "Creating and Managing Views in SQL Server," and Chapter 24, "Administering Very Large SQL Server Databases."
INSTEAD OF and AFTER Triggers
In versions of SQL Server prior to 7.0, a table could not have more than one trigger defined for INSERT, UPDATE, and DELETE. These triggers only fired after the data modification took place. SQL Server 7.0 introduced the ability to define multiple AFTER triggers for the same operation on a table. SQL Server 2000 extends this capability by providing the ability to define which AFTER trigger fires first and which fires last. (Any other AFTER triggers besides the first and last will fire in an undetermined order.)
SQL Server 2000 also introduces the ability to define INSTEAD OF triggers. INSTEAD OF triggers can be specified on both tables and views. (AFTER triggers can still only be specified on tables.) If an INSTEAD OF trigger is defined on a table or view, the trigger will be executed in place of the data modification action for which it is defined. The data modification is not executed unless the SQL code to perform it is included in the trigger definition.
For more information on creating, managing, and using INSTEAD OF and AFTER triggers, see Chapter 15, "Creating and Managing Triggers."
SQL Server 2000 introduces three new datatypes. Two of these can be used as datatypes for local variables, stored procedure parameters and return values, user-defined function parameters and return values, or table columns:
bigintAn 8-byte integer that can store values from 263 (9223372036854775808) through 263-1 (9223372036854775807).
sql_variantA variable-sized column that can store values of various SQL Server-supported data types, with the exception of text, ntext, timestamp, and sql_variant.
The third new datatype, the table datatype, can be used only as a local variable datatype within functions, stored procedures, and SQL batches. The table datatype cannot be passed as a parameter to functions or stored procedures, nor can it be used as a column datatype. A variable defined with the table datatype can be used to store a result set for later processing. A table variable can be used in queries anywhere a table can be specified.
For more information on using the new datatypes, see Chapter 27, "Using Transact-SQL in SQL Server 2000."
Text in Row Data
In previous versions of SQL Server, text and image data was always stored on a separate page chain from where the actual data row resided. The data row contained only a pointer to the text or image page chain, regardless of the size of the text or image data. SQL Server 2000 provides a new text in row table option that allows small text and image data values to be placed directly in the data row, instead of requiring a separate data page. This can reduce the amount of space required to store small text and image data values, as well as reduce the amount of I/O required to retrieve rows containing small text and image data values.
For more information on specifying text and image datatypes for tables, see Chapter 10, "Creating and Managing Tables in SQL Server." For more information on how text and image data is stored in tables, see Chapter 30, "SQL Server Internals."
Cascading RI Constraints
In previous versions of SQL Server, referential integrity (RI) constraints were restrictive only. If an insert, update, or delete operation violated referential integrity, it was aborted with an error message. SQL Server 2000 provides the ability to specify the action to take when a column referenced by a foreign key constraint is updated or deleted. You can still abort the update or delete if related foreign key records exist by specifying the NO ACTION option, or you can specify the new CASCADE option, which will cascade the update or delete operation to the related foreign key records.
See Chapter 12, "Data Integrity," for more information on using the new options with foreign key constraints.
Multiple SQL Server Instances
Previous versions of SQL Server supported the running of only a single instance of SQL Server at a time on a computer. Running multiple instances or multiple versions of SQL Server required switching back and forth between the different instances, requiring changes in the Windows registry. (The SQL Server Switch provided with 7.0 to switch between 7.0 and 6.5 performed the registry changes for you.)
SQL Server 2000 provides support for running multiple instances of SQL Server on the same system. This allows you to simultaneously run one instance of SQL Server 6.5 or 7.0 along with one or more instances of SQL Server 2000. Each SQL Server instance runs independently of the others and has its own set of system and user databases, security configuration, and so on. Applications can connect to the different instances in the same way they connect to different SQL Servers on different machines.
This feature provides the ability to run an older version of SQL Server alongside SQL Server 2000, as well as the ability to run separate environments (for example, a development and test environment) on the same computer.
For more information on setting up one or more SQL Server instances, see Chapter 4, "Installing a New SQL Server Instance."
Extensible Markup Language has become a standard in Web-related programming to describe the contents of a set of data and how the data should be output or displayed on a Web page. XML, like HTML, is derived from the Standard Generalize Markup Language (SGML). When linking a Web application to SQL Server, a translation needs to take place from the result set returned from SQL Server to a format that can be understood and displayed by a Web application. Previously, this translation needed to be done in a client application.
SQL Server 2000 provides native support for XML. This new feature provides the ability to do the following:
Return query result sets directly in XML format.
Retrieve data from an XML document as if it were a SQL Server table.
Access SQL Server through a URL using HTTP. Through Internet Information Services (IIS), you can define a virtual root that gives you HTTP access to the data and XML functionality of SQL Server 2000.
The Enterprise Edition of SQL Server 2000 now supports log shipping, which you can use to copy and load transaction log backups from one database to one or more databases on a constant basis. This allows you to have a primary read/write database with one or more read-only copies of the database that are kept synchronized by restoring the logs from the primary database. The destination database can be used as a warm standby for the primary database, for which you can switch users over in the event of a primary database failure. Additionally, log shipping provides a way to offload read-only query processing from the primary database to the destination database.
This capability was available in previous versions of SQL Server, but it required the DBA to manually set up the process and schedule the jobs to copy and restore the log backups. SQL Server 2000 officially supports log shipping and has made it easier to set up via the Database Maintenance Plan Wizard. This greatly simplifies the process by automatically generating the jobs and configuring the databases to support log shipping.
For more information on configuring and using log shipping, see Chapter 25, "Data Replication."