A Comparison of SQL Server 7.0 with SQL Server 2000

SQL Server 2000 Advantages

• User-Defined Functions: User-Defined Functions (UDFs) -- one or more Transact-SQL statements can be used to encapsulate code for reuse. User-defined functions cannot make a permanent changes to the data or modify database tables. UDF can change only local objects for a UDF, such as local cursors or variables.
  
  
• Distributed Partitioned Views: Distributed partitioned views allow you to partition tables horizontally across multiple servers. So, you can scale out one database server to a group of database servers that cooperate to provide the same performance levels as a cluster of database servers. Due to distributed partitioned views, SQL Server 2000 now on the first place in the tpc-c tests.
  
  
• New Data Types: These include: bigint, an 8-byte integer type; sql_variant, a data type that allows the storage of data of different data types; and the table data type, which allows applications to store results temporarily for later use.
  
  
• INSTEAD OF and AFTER Triggers: There are INSTEAD OF and AFTER Triggers in SQL Server 2000. INSTEAD OF triggers are executed instead of the INSERT, UPDATE or DELETE triggering action. AFTER triggers are executed after the triggering action.
  
  
• Cascading Referential Integrity Constraints: There are new ON DELETE and ON UPDATE clauses in the REFERENCES clause of the CREATE TABLE and ALTER TABLE statements.
  
  The ON DELETE clause controls what actions are taken if you attempt to delete a row to which existing foreign keys point. The ON UPDATE clause defines the actions that are taken if you attempt to update a candidate key value to which existing foreign keys point.
  
  The ON DELETE and ON UPDATE clauses have two options:
  
  · NO ACTION
  · CASCADE
  
  NO ACTION specifies that the deletion/update fail with an error.
  
  CASCADE specifies that all the rows with foreign keys pointing to the deleted/updated row are also deleted/updated.
  
  
• 32 CPU and 64GB Memory Support: SQL Server 2000 Enterprise Edition running under Windows 2000 DataCenter can support up to 32 CPUs and up to 64GB of physical memory (RAM) on a computer.
  
  
• XML Support: SQL Server 2000 can use XML to insert, update, and delete values in the database, and the database engine can return data as Extensible Markup Language (XML) documents.

If any of the new features of SQL Server 2000 can make you database more productive, then you should seriously consider making the upgrade from from 7.0 to SQL Server 2000. This, of course, assumes that your hardware can support SQL Server 2000.

Published with the express written permission of the author. Copyrighted by the author.