New features of SQL Server 2000

New features for 1.2 SQL Server 2000
SQL Server 2000 fully expands the performance reliability and ease of use of SQL Server 7.0 to make it an outstanding database platform for large online transaction processing data warehouses and E-commerce
The new features of SQL Server 2000 are mainly as follows:

1.2.1 Database Enhancements
SQL Server 2000 introduces database and server enhancements and some other new features
1 XML extensible Markup Language Extensible Markup Language support
The relational database engine can return XML document data XML data can be used to insert delete and update data XML is an Extensible Markup Language extensible Markup Language XML language can be used to describe the contents of a dataset and how the data is in the Web The page displays or outputs to a device XML and HTML hypertext Markup Language Hypertext Link Markup Language originates from SGML Standard general Markup Language standard Generic Markup Language SGML is a very complex and huge Language is hard to use when publishing data on the Web. HTML is much simpler and more specialized than SGML, but HTML has a certain amount of data to work with. It's simpler than SGML, but much more powerful than HTML, so XML becomes web An increasingly important language for electronic data exchange in a network or corporate intranet

SQL Server 2000 support for XML is shown in the following areas
.. Access to SQL Server via URL uniform Resource Locator Uniform Resource Location
.. Support Xml-data Mode
.. Can retrieve writing XML data
.. SQL Server OLE DB adds support for XML documents

2 New data type Types
3 types of data are added to SQL Server 2000 bigint sql_variant and table

3 Text type data in a data row
In SQL Server 2000, you can store text and image type data directly into a table's data row instead of to a different data page, which reduces the amount of I/O that is used to store the text and image types and correspondingly reduces the number of I/OS for disk processing data

4 user-defined functions user-defined functions
SQL Server 2000 extends the programmability of Transact-SQL languages users can create their own Transact-SQL functions user-defined functions can return a Quantity value or table

5 Index Enhancement
You can create an index on a computed column it's a big improvement.

6 Full-Text Search Full-text Search Enhancements
The function of change tracking and graphics filtering is added in Full-text search. Graphics filtering allows you to query and create indexes on document data stored in the Image Type column

7 Indexed view Indexed views
Indexed views allow indexes to be created on views this greatly improves the performance of programs that require frequent connection queries

8 Distributed Queries distributed query enhancements
SQL Server 2000 introduces a function named OpenRowset that can specify a specific connection information in a distributed query the functionality of the distributed query optimizer has been further improved grant more SQL operations to OLE DB data sources

9 trigger type instead of and after triggers
Triggers can be created with a FOR clause to specify the timing of the trigger type to be executed by a instead of type or after type of different types of triggers

10 Cascade referential integrity constraints cascading referential Integrity Constraints
Cascading referential integrity constraints control the actions taken to delete or update data with a FOREIGN KEY constraint this control is achieved by adding an on delete or on UPDATE clause in the REFERENCES clause in the CREATE TABLE or ALTER TABLE command

One by one collation promotion
SQL Server 2000 replaces code pages with collation and sort Orders SQL Server 2000 provides more support for collation than previous versions and introduces a Windows collations The new collation collection can specify the database-level or column-level collation

1.2.2 Federated Database Server Federated DB Servers
SQL Server 2000 supports distributed partitioned views to split tables horizontally across multiple servers

More than 1 instances of SQL Server
SQL Server 2000 supports running multiple relational database instances concurrently on the same computer each instance has its own stand-alone system and user database collection application to connect individual instances on the same computer in much the same way as connecting to a SQL Server instance on a different computer

2 Failover Cluster Clustering enhancements
Improved management of Failover clusters you can easily install configuration management a SQL Server Failover cluster

3 Network Library Net-library Enhancements
Simplifies client configuration and supports multiple instance connections on the same computer

4 Kerberos and secure authorization security delegation
SQL Server 2000 uses Kerberos to support mutual authentication between client and server using Kerberos and authorization to support composite authentication and SQL Server registration

5 Back up and restore backup and restore enhancements
SQL Server 2000 introduces a more understandable model to specify options for backup and recovery and also supports the use of transaction log identities to restore work to a specified point or partial recovery of a database

6 Scalability Scalability enhancements for public operations
Enhanced public operations including fast differential backup parallel database consistency checksum parallel scan

7 Replication Enhancements
The larger improvements and enhancements to replication in SQL Server 2000 are mainly divided into the following areas

Replication implementation
SQL Server 2000 improves the processing power of snapshot replication transactional replication and merge replication by adding the following features an alternate snapshot location provides a simple and flexible way to apply an initial snapshot to a subscriber you can store or compress snapshot files to a network storage location FTP nodes or removable media

An additional subscription database allows the simultaneous delivery of databases and replicated data from one subscriber to another, and one or more subscription databases on new subscribers after the database is attached to a new subscriber will automatically receive a pull subscription to a specific publisher's publication

Publication planning through the change publication database can add or remove certain columns from the publication table and reflect these changes to the subscriber

Execute scripting as needed allows you to send SQL scripts that will be executed on all Subscribers

You can run the snapshot script before or after the initial Snapshot Agent is applied to the Subscriber by running a Distribution Agent or Merge Agent on another computer to reduce the number of processes that are processed by the distributor and the Subscriber

Snapshot ActiveX control makes it easier to create snapshots programmatically

More snapshot script options that support passing indexes extended attributes and constraints on subscribers

Merge replication
The improvement of merge replication in SQL Server 2000 is mainly shown in the following aspects

. Improve server to server performance allow concurrent processing of merge Agents
Optimization of data change decisions related to replication data on subscribers
Dynamic snapshots provides a more efficient snapshot initialization application when dynamic filtering is used
Merge vertical filters for publications
Provides more powerful dynamic filtering through user-defined functions
.
Use alternate synchronization partner when synchronizing data alternate synchronization partners When you use an alternate synchronization partner, subscribers to the merged publication can synchronize data with any specified server as long as the server has the same data as the original subscriber

Automatically manage identity value ranges each publication contains an identity identity in the topology of merge replication Liedan when inserting new lines on a subscriber automatic management of the range of identity values that is available on the Subscriber ensures that the same identity value is not assigned to the insert row of the different subscription database and that the PRIMARY key constraint is not violated it uses the queued modification option in snapshot replication and transactional replication, or you can use this feature

Supports the use of postmark timestamp columns in publication tables
Improved management of consolidation tracking data growth

Several new merge replication conflict resolutions are available, including the interactive resolver this conflict resolver provides a user interface for immediate manual conflict resolution in addition, it also provides the first/last change of append/average priority policy based on column value maximum/minimum value and merges by adding different text values

Contains new options to confirm the rights and security of the Subscriber to load data changes to publishers
The new COM interface is used to support heterogeneous data sources as publishers
Snapshot reinitialization allows loading changes for subscribers and publishers before new snapshots are applied

Transactional replication
In SQL Server2000 The improvement of transactional replication in China mainly includes the following several aspects. Parallel snapshot processing guarantees that the publication table can be modified even when the initial snapshot is created to improve error handling ability to skip the specified error and continue replicating the data. Confirmation of replicated data at the Subscriber including confirmation of vertical split through SQL Server Enterprise Manager can also use the confirmation

. Publish an indexed view
That includes copying options that store the modification transactions of data on the subscriber in the queue, excluding modifications
. Include delivery replication data options
..
Queued Updating queued updating
Queued updating is a new feature of SQL Server 2000 it allows subscribers of snapshot replication or transactional replication to update published data without requiring an active network connection between the Subscriber and the publisher

Changes that occur when a publication is created with the queued updating option and when the Subscriber performs an insert UPDATE DELETE operation on the replicated data are stored in the queue when the network connection between the Subscriber and publisher is restored, the queue transaction is applied asynchronously to the publisher

Because the subscriber's update to the replicated data is reflected asynchronously to the publisher so if the same data can be updated by other Subscribers or publishers themselves it is very easy to create a conflict resolution policy that can be used to detect conflict resolution by creating a publication

Converting replicated data
SQL Server 2000 new features convert transformable subscriptions to a convertible subscription the conversion of the data to a convertible subscription is primarily the use of Data Transformation Services DTS Features such as data movement transfer map filtering can be customized to deliver replication data based on the specific requirements of each subscriber it can be used in snapshot replication and transactional replication to set this option when creating a publication

Availability of replication
SQL Server Enterprise Manager in SQL Server 2000 has greatly improved implementation monitoring and management replication, mainly in the following areas

There is a centralized replication folder in the SQL Server Enterprise Manager tree that is used to organize all subscriptions and publishing on servers managed by SQL Server Enterprise Manager

Through Windows Active Directory, you can browse subscription publications and you must have the appropriate browsing order rights

Multiple replication distributors can be monitored on a single node in SQL Server Enterprise Manager

Wizards for Create publication create Push Subscription create Pull Subscription Wizards
Contains standard and advanced options you can set advanced options in the Welcome interface of each replication wizard by selecting Show advanced Options

Two new replication wizards added create dynamic Snapshot Wizard and transform published data Wizard the former is primarily for dynamic snapshot creation using dynamically filtered merge replication The latter mainly transforms replicated data in snapshot replication or transactional replication

8 Data Transformation Services Enhancements
As shown in the following areas
.. Support Keys and constraints supports keys and constraints
DTS support keys and constraints you can use the Data Import Export Wizard to move a primary key foreign key contract from the source table to the target table
Beam
.. New custom task, custom tasks
SQL Server Watts DTS Designer and DTS object models provide new custom task capabilities to create packages that perform tasks or set variables that are based on live environment properties, including from or to the Internet and FTP Site import data send data asynchronously run packages create packages that send messages to each other create packages that execute other packages and include multiple package executions in the same transaction
.. To protect a DTS package in a VB file
Save the DTS package to Microsoft. Visual Basic files allow packages created by the DTS Import Wizard DTS Export Wizard or DTS Designer to be combined with Visual Basic programs or be used as prototypes by Visual Basic developers who need to reference DTS object model components

9 SQL Server Analysis Services
OLAP Online Analytical Processing online analysis process services in SQL Server 7.0 transition to profiling services in SQL Server 2000 The Analysis Service Analysis service also includes new data mining information M ining function

SQL Server Meta Data Services
Storage room in SQL Server 7.0 repository part of SQL Server 2000 is converted to metadata services meta data Service