
	About Us
	SSWUG Articles
	Articles

	Administering
	Comparison
	General
	Know How
	Replication
	Tuning
	Undocumented
	UDF
	SQL 6.5

	FAQ
	Scripts
	Tips
	Test Exams
	Advertise
	Download
	History
	Search
	Traffic
	Related Links


Add to Favorites

The comparison of Access 97/2000 with SQL Server 7.0/2000

Sergey A. Vartanyan
svartany@yahoo.com

Introduction
Access 97/2000
SQL Server 7.0/2000
Literature

Introduction

Often people in newsgroups ask about some comparison of Access and SQL Server databases. In this article, I want to tell you about some general characteristics of Access 97/2000 and SQL Server 7.0/2000, about their restrictions, and about the comparison of these products.

Access 97/2000

Microsoft offers a full family of database tools for the desktop, the server, and for open connectivity. For the desktop Microsoft offers Microsoft Access 97/2000.
The Microsoft Access 97/2000 is a full-featured multiuser relational database management system that designed for the Microsoft Windows operating systems (such as Windows 9x, Windows NT, Windows 2000). Access 97/2000 is extremely visually oriented and easy to use. It makes extensive use of drag-and-drop and visual design for queries, forms, and reports.

Access 97/2000 comes with an integrated development environment (IDE), including incremental compilation, a fully interactive visual debugger, breakpoints, and single step-through. These capabilities combine to make Microsoft Access an extremely powerful platform for developing client-server database solutions.

There are general Access 97/2000 characteristics:

Object	Maximum sizes/numbers
Database size	1 Gb
Number of characters in an object name	64
Number of characters in a password	14
Number of characters in a user name or group name	20
Number of concurrent users	255
Number of characters in a table name	64
Number of characters in a field name	64
Number of fields in a table	255
Number of characters in a Text field	255
Number of characters in a Memo field	65,535 / 1 Gb

Though the maximum database size is 1 Gb, but your database can include linked tables in other files, and its total size is limited only by available storage capacity.

Number of characters in a Memo field can be up to 65,535 characters when entering data through the user interface or can be up to 1 Gb when entering data programmatically.

Access 2000 can lock on the row level in comparison with Access 97 page level's lock.

SQL Server 7.0/2000

For the server engine in client-server solutions Microsoft provides the Microsoft SQL Server 7.0/2000.

There are no many differences in the internal architecture between SQL Server 7.0 and SQL Server 2000. SQL Server 2000 has the same features as SQL Server 7.0: autogrow features, new storage engine, complete row level locking and so on. So, the general characteristics of these versions are the same.

There are general SQL Server 97/2000 characteristics:

Object	Maximum sizes/numbers
Batch size	65,536 * Network Packet Size
Bytes per short string column	8,000
Bytes per text, ntext,or image column	2 GB-2
Bytes per index	900
Bytes per foreign key	900
Bytes per primary key	900
Bytes per row	8,060
Bytes in source text of a stored procedure	Lesser of batch size or 250 MB
Clustered indexes per table	1
Columns per index	16
Columns per foreign key	16
Columns per primary key	16
Columns per base table	1,024
Columns per SELECT statement	4,096
Columns per INSERT statement	1,024
Connections per client	Maximum value of configured connections
Database size	1,048,516 TB
Databases per instance of SQL Server	32,767
Filegroups per database	256
Files per database	32,767
File size (data)	32 TB
Identifier length (in characters)	128
Locks per connection	Max. locks per server
Nested stored procedure levels	32
Nested subqueries	32
Nested trigger levels	32
Nonclustered indexes per table	249
Objects in a database	2,147,483,6474
Parameters per stored procedure	1,024
REFERENCES per table	253
Rows per table	Limited by available storage
Tables per database	Limited by number of objects in a database
Tables per SELECT statement	256
Triggers per table	Limited by number of objects in a database
UNIQUE indexes or constraints per table	249 nonclustered and 1 clustered

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!

Literature

1. SQL Server Books Online

2. Maximum Capacity Specifications

3. ACC: Choosing Database Tools White Paper Available in Download Center

4. Microsoft SQL Server: Microsoft Access 2000 Data Engine Options

Visit The SQL Server Worldwide User's Group for all the latest news and information about SQL Server, Oracle, DB2 and XML for developers and administrators.

(c) 1997, 2005 Bits on the Wire, Inc