Adaptive Server specifications

Database specifications

Databases per Adaptive Server

A maximum of 32,767 databases per server

Maximum database size

  • 2K page server – 4TB

  • 4K page server – 8TB

  • 8K page server – 16TB

  • 16K page server – 32TB

Minimum allowable sybsystemprocs database

136MB

Required for an upgrade

Maximum size of a database device (disk partition)

224 (4TB)

If the Operating System supports file sizes up to 4TB, then Adaptive Server supports file system devices up to 4TB

Maximum number of database devices per server

231

Maximum number of devices or device pieces per database

Unlimited

Limited by available memory

Maximum number of segments per database

31

Maximum number of login IDs per server

2147516416

Maximum number of users per database

2146484223

Maximum number of groups per database

1032193

Table specifications

User objects per database

231 – 255

Indexes per table

250 (one clustered index)

Rows per table

Limited by available storage

Maximum 232

Columns per composite index

31

Creation of clustered index

1.2*(x + y)

x = total data space in table, y = sum of space of all nonclustered indexes on table, and 20 percent overhead for logging

For sorted data, approximately 20 percent of the table size needed

Maximum size of object name

255

Query specifications

Maximum number of tables participating in a query, for a query without a union, or for each side of a union in a query

64

Maximum of 50 user tables, including result tables, tables referenced by views (the view itself is not counted) correlations and self-joins; maximum of 46 worktables

Maximum number of tables in a “union” query

256

Includes up to 50 user tables and 14 work tables on every side of the union, for a total of up to 256 tables across all sides of the union.

Maximum number of databases participating in one transaction

Unlimited

Includes database where transaction began, all databases changed during transaction, and tempdb, if it is used for results or worktables

Practical number of databases participating in one query

16

Includes each occurrence of each database queried and tempdb, if it is used for results or worktables

Maximum number of tables with referential integrity constraints for a query

192

Procedure specifications

Number of buffers and procedure buffers

Configurable

Limited by amount of RAM and maximum size of shared memory segment

Minimum memory required per stored procedure

2K

Maximum number of parameters per stored procedure

2048

Adaptive Server extended-limit capabilities vary by type of table and the database logical page size. Table 1-2 lists the column and row limits for allpages-locked (APL) tables.

Table 1-2: Allpages-locked (APL) tables

Maximum APL table limits

Number of columns

Column size 2K page

Column size 4K page

Column size 8K page

Column size 16K page

Fixed-length column

1024

1960 bytes

4008 bytes

8104 bytes

16296 bytes

Variable-length column

254

1948 bytes

3988 bytes

8068 bytes

16228 bytes

Table 1-3 lists the column and row limits for data-only-locked (DOL) tables.

Table 1-3: Data row and data page tables.

Maximum DOL table limits

Number of columns

Column size 2K page

Column size 4K page

Column size 8K page

Column size 16K page

Fixed-length column

1024

1958 bytes

4006 bytes

8102 bytes

16294 bytes

Variable-length column

1024

1954 bytes

4002 bytes

8098 bytes

16290 bytes

Database space requirements depend upon the logical page size of the server.If your model database is larger than the minim size listed below, then the minimum size of the database is equal to the model database. Table 1-4 lists the minimum size for each database.

Table 1-4: Database requirements for varying page sizes

Databases

2K page

4K page

8K page

16K page

master database

13MB

26MB

52MB

104MB

model database

3MB

6MB

12MB

24MB

tempdb database

4MB

6MB

12MB

24MB

sybsystemdb database

3MB

6MB

12MB

24MB

sybpcidb database

24MB

48MB

96MB

192MB

Larger logical page sizes can contain more data. Table 1-5 lists the maximum data for each logical page size.

Table 1-5: Data limits for tables according to page size

Tables

2K page

4K page

8K page

16K page

Bytes per index key

600

1250

2600

5300

User-visible row length DOL table

1958

4006

8102

16294

User-visible row length APL table

1960

4008

8104

16296