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.

This chapter is from the book

This chapter is from the book

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.

User-Defined Functions

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 proceduresthey 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 valueeither 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."

Indexed Views

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."

New Datatypes

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:

bigintAn 8-byte integer that can store values from
263 (9223372036854775808) through 263-1
(9223372036854775807).

sql_variantA 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."

XML Support

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.

Log Shipping

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."