Author Topic: What are the features of SQL Server 2000?  (Read 93 times)

0 Members and 1 Guest are viewing this topic.

Offline Corps

  • Sr. Member
  • *
  • Posts: 276
  • Karma: +0/-0
    • View Profile
What are the features of SQL Server 2000?
« on: January 06, 2009, 07:20:01 AM »
What are the new features of SQL Server 2000?
   XML Support
The relational database engine can return data as eXtensible Markup Language (XML) documents. Additionally, XML can also be used to insert, update, and delete values in the database.
   User-Defined Functions
The programmability of Transact-SQL can be extended by creating your own Transact-SQL functions. A user-defined function can return either a scalar value or a table.
   Indexed Views
Indexed views can significantly improve the performance of an application where queries frequently perform certain joins or aggregations. An indexed view allows indexes to be created on views, where the result set of the view is stored and indexed in the database. Existing applications do not need to be modified to take advantage of the performance improvements with indexed views.
   New Data Types
SQL Server 2000 introduces three new data types. The bigint data type is an 8-byte integer type. The sql_variant data type allows the storage of data values of different data types. The table data type allows applications to store results temporarily for later use. It is supported for variables, and as the return type for user-defined functions.
   INSTEAD OF and AFTER Triggers
INSTEAD OF triggers are executed instead of the triggering action (for example, INSERT, UPDATE, DELETE). They can also be defined on views, in which case they greatly extend the types of updates a view can support. AFTER triggers fire after the triggering action. SQL Server 2000 introduces the ability to specify which AFTER triggers fire first and last.
   Full-Text Search Enhancements
Full-text search now includes change tracking and image filtering. Change tracking maintains a log of all changes to the full-text indexed data. You can update the full-text index with these changes by flushing the log manually, on a schedule, or as they occur, using the background update index option. Image filtering allows you to index and query documents stored in image columns. The user provides the document type in a column that contains the file name extension that the document would have had if it were stored as a file in the file system. Using this information, full-text search is able to load the appropriate document filter to extract textual information for indexing.
   Multiple Instances of SQL Server
SQL Server 2000 supports running multiple instances of the relational database engine on the same computer. Each computer can run one instance of the relational database engine from SQL Server version 6.5 or 7.0, along with one or more instances of the database engine from SQL Server 2000. Each instance has its own set of system and user databases. Applications can connect to each instance on a computer similar to the way they connect to instances of SQL Servers running on different computers. The SQL Server 2000 utilities and administration tools have been enhanced to work with multiple instances.
   Index Enhancements
You can now create indexes on computed columns. You can specify whether indexes are built in ascending or descending order, and if the database engine should use parallel scanning and sorting during index creation.
The CREATE INDEX statement can now use the tempdb database as a work area for the sorts required to build an index. This results in improved disk read and write patterns for the index creation step, and makes it more likely that index pages will be allocated in contiguous strips. In addition, the complete process of creating an index is eligible for parallel operations, not only the initial table scan.
   Net-Library Enhancements
The SQL Server 2000 Net-Libraries have been rewritten to virtually eliminate the need to administer Net-Library configurations on client computers when connecting SQL Server 2000 clients to instances of SQL Server 2000. The new Net-Libraries also support connections to multiple instances of SQL Server on the same computer, and support Secure Sockets Layer encryption over all Net-Libraries. SQL Server 2000 introduces Net-Library support for Virtual Interface Architecture (VIA) system-area networks that provide high-speed connectivity between servers, such as between application servers and database servers.
   64-GB Memory Support
Microsoft SQL Server 2000 Enterprise Edition can use the Microsoft Windows 2000 Advanced Windows Extension (AWE) API to support up to 64 GB of physical memory (RAM) on a computer.
   Distributed Query Enhancements
SQL Server 2000 introduces a new OPENDATASOURCE function, which you can use to specify ad hoc connection information in a distributed query. SQL Server 2000 also specifies methods that OLE DB providers can use to report the level of SQL syntax supported by the provider and statistics on the distribution of key values in the data source. The distributed query optimizer can then use this information to reduce the amount of data that has to be sent from the OLE DB data source. SQL Server 2000 delegates more SQL operations to OLE DB data sources than earlier versions of SQL Server. Distributed queries also support the other functions introduced in SQL Server 2000, such as multiple instances, mixing columns with different collations in result sets, and the new bigint and sql_variant data types.
   Updateable Distributed Partitioned Views
SQL Server 2000 introduces enhancements to distributed partitioned views. You can partition tables horizontally across several servers, and define a distributed partitioned view on each member server that makes it appear as if a full copy of the original table is stored on each server. Groups of servers running SQL Server that cooperate in this type of partitioning are called federations of servers. A database federation built using SQL Server 2000 databases is capable of supporting the processing requirements of the largest Web sites or enterprise-level databases.
   Backup and Restore Enhancements
SQL Server 2000 introduces a new, more easily understood model for specifying backup and restore options. The new model makes it clearer that you are balancing increased or decreased exposure to losing work against the performance and log space requirements of different plans. SQL Server 2000 introduces support for recovery to specific points of work using named log marks in the transaction log, and the ability to do partial database restores.
Users can define passwords for backup sets and media sets that prevent unauthorized users from accessing SQL Server backups.
   Scalability Enhancements for Utility Operations
SQL Server 2000 enhancements for utility operations include faster differential backups, parallel Database Console Command (DBCC) checking, and parallel scanning. Differential backups can now be completed in a time that is proportional to the amount of data changed since the last full backup. DBCC can be run without taking shared table locks while scanning tables, thereby, enabling them to be run concurrently with update activity on tables. Additionally, DBCC now takes advantage of multiple processors, thus enabling near-linear gain in performance in relation to the number of CPUs (provided that I/O is not a bottleneck).
   Text in Row Data
SQL Server 2000 supports a new text in row table option that specifies that small text, ntext, and image values be placed directly in the data row instead of in a separate page. This reduces the amount of space used to store small text, ntext, and image data values, and reduces the amount of disk I/O needed to process these values.

Techronnati | where technology never sleeps

What are the features of SQL Server 2000?
« on: January 06, 2009, 07:20:01 AM »

Mountain View


Related Topics

  Subject / Started by Replies Last post
0 Replies
Last post October 01, 2008, 02:02:35 AM
by arpee
0 Replies
Last post January 06, 2009, 07:11:59 AM
by rpmolecule
2 Replies
Last post March 30, 2010, 04:07:57 PM
by TDelight
0 Replies
Last post August 23, 2017, 10:59:22 AM
by GBTalk
0 Replies
Last post August 23, 2017, 11:00:56 AM
by Corps

Posting Disclaimer: Any individual may post a message in this forum and may do so anonymously. Therefore, the sole author is exclusively and entirely responsible for all opinions in that message. They do not represent the official opinions of Techronnati, its administrators or moderators or the Techronnati Management. Techronnati is merely acting as an impartial conduit for constitutionally protected free speech and is not responsible and will not be held liable for the content of such messages. All images and service logos are trademarks of their respective owners.