Sql Server 7 and Sql server 2000

greenspun.com : LUSENET : OpenKnowledge : One Thread

What is the difference between Microsoft sql server 7.0 and sql server 2000?

-- mukesh (mukesh@correzone.com), November 07, 2003


SQL Server 7.0 has some features, that are not supported in SQL Server 2000.

These server configuration options are not supported in SQL Server 2000:

default sortorder id resource timeout extended memory size spin counter language in cache time slice language neutral full-text unicode comparison style max async IO unicode locale id

SQL Profiler extended stored procedures, such as xp_trace_addnewqueue and xp_trace_generate_event, are not supported in SQL Server 2000.

The SQL-SCM (Service Control Manager) API has been removed and is no longer supported in SQL Server 2000.

So, if you work with SQL Server 7.0 without any problems and not needed in the new SQL Server 2000 features, it's not necessary to upgrade to the new version.

SQL Server 2000 advantages User-Defined Functions.

User-Defined Functions (UDFs) - one or more Transact-SQL statements that 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 this 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. See this link for more details: TPC-C tests

New data types.

There are new data types:

bigint data type sql_variant data type table data type

bigint data type is an 8-byte integer type.

sql_variant data type is a type that allows the storage of data values of different data types.

table data type is a type that 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 specifies that the deletion/updation 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 on the Windows 2000 DataCenter can support up to 32 CPU and up to 64GB physical memory (RAM) on a computer.

XML Support.

SQL Server 2000 can use XML to insert, update, and delete values in the database, and database engine can return data as Extensible Markup Language (XML) documents.

Look at here for more information about new SQL Server 2000 features:

-- Lakshman (smlakshman@hotmail.com), January 13, 2004.

hi ,

http://www.mssqlcity.com/Articles/Compare/SQL70vs2000.htm i think it will help u.

-- hima (gethima@hotmail.com), May 25, 2004.

i give u some of the answar as per my knowledge..

1. New data type - ( bigint , table , varient ) 2. xml have been embedded 3. UDF 4. Instead of trigger

-- Palani (grpalani@yahoo.com), July 17, 2004.

Moderation questions? read the FAQ