ASE对象名称长度限制

ASE 15之前版本中对象名称长度限制为30字节,ASE 15及以后版本中对象名称长度限制为255字节;这指的是服务器端的对象名称长度限制。

在创建用户对象时,还要考虑到客户端版本对名称长度的限制。

下面举四个例子:

在ASE 12.5上创建一个名称长度为33字节的存储过程:

create procedure
sp_123456789012345678901234567890
as
select getdate()
go

1、使用ASE 12.5的isql连接ASE 12.5服务器,报错:

Msg 103, Level 15, State 1:
Server 'dbainfo', Line 2:
The identifier that starts with 'sp_123456789012345678901234567' is too long.  Maximum length is 30.

2、使用ASE 15.0.3的isql连接ASE 12.5服务器,报错:

Msg 103, Level 15, State 1:
Server 'dbainfo', Line 2:
The identifier that starts with 'sp_123456789012345678901234567' is too long.  Maximum length is 30.

在ASE 15.7上创建一个名称长度为263字节的存储过程:

create procedure
sp_abcdefghigklmnopqrstuvwxyzabcdefghigklmnopqrstuvwxyzabcdefghigklmnopqrstuvwxyzabcdefghigklmnopqrstuvwxyzabcdefghigklmnopqrstuvwxyzabcdefghigklmnopqrstuvwxyzabcdefghigklmnopqrstuvwxyzabcdefghigklmnopqrstuvwxyzabcdefghigklmnopqrstuvwxyzabcdefghigklmnopqrstuvwxyz
as
select getdate()
go

3、使用ASE 12.5的isql连接ASE 15.7服务器,报错:

Msg 103, Level 15, State 205:
Server 'ASE157_2K', Line 2:
The identifier that starts with 'sp_abcdefghigklmnopqrstuvwxyza' is too long. Maximum length is 30.
 

4、使用ASE 15.0.3的isql连接ASE 15.7服务器,报错:

Msg 103, Level 15, State 1:
Server 'ASE157_2K', Line 2:
The identifier that starts with 'sp_abcdefghigklmnopqrstuvwxyzabcdefghigklmnopqrstuvwxyzabcdefghigklmnopqrstuvwxyzabcdefghigklmnopqrstuvwxyzabcdefghigklmnopqrstuvwxyzabcdefghigklmnopqrstuvwxyzabcdefghigklmnopqrstuvwxyzabcdefghigklmnopqrstuvwxyzabcdefghigklmnopqrstuvwxyzabcdefghigklmnopqr' is too long. Maximum length is 255.

通过上面四个例子,可以看到存储过程名称长度超过限制时,主要有两种错误号:Msg 103, Level 15, State 1 和 Msg 103, Level 15, State 205。

着重关注第3个例子,如果Msg 103的State为205的话,说明报错的原因不是达到服务器端对象名称长度上限了,可能是客户端导致的;

ASE 12.5的客户端无法存储超过30字节的对象名称,因此,使用12.5的客户端连接15.x服务器,对象名称长度仍然限制为30字节。

ASE 15中对象名称长度上限为255字节的对象类型包括:表名、分区名、列名、索引名、存储过程名、命名缓存名、用户定义数据类型等;

ASE 15的其它对象类型与ASE 12.5中一样,名称长度上限仍为30字节,包括:服务器名称、主机名、登录名、用户名、密码、角色名、设备逻辑名称、数据库名、段名、游标名称等。

查看Sybase ASE中关于对象长度以及存储方面的限制,使用dbcc serverlimits

下面是ASE 15.7版本上的限制:

1> dbcc traceon(3604)
2> go

1> dbcc serverlimits
2> go
Limits independent of page size:
================================

Server-wide, Database-specific limitAs and sizes

  Max engines per server                                                    : 1024
  Max number of logins per server                                           : 2147516416
  Max number of users per database                                          : 2146484223
  Max number of groups per database                                         : 1032193
  Max number of user-defined roles per server                               : 1024
  Max number of user-defined roles per (user) session                       : 127
  Min database page size                                                    : 2048
  Max database page size                                                    : 16384
  Max database device size, in Tb                                           : 4
  Initial master database logical page count                                : 6656
  Initial model database logical page count                                 : 1536
  Default logical pages in a new database                                   : 1536
  Min size of sybsystemprocs, in MB                                         : 160
  Recommended size of sybsystemprocs, in MB                                 : 172

Database page-specific limits

  APL page header size                                                      : 32
  DOL page header size                                                      : 44
  Max reserved page gap                                                     : 255
  Max fill factor                                                           : 100

Table, Index related limits

  Max number of columns in a table/view                                     : 1024
  Max number of indexes on a table                                          : 250
  Max number of user-keys in a single index on an unpartitioned table       : 31
  Max number of user-keys in a single local index on a partitioned table    : 31
  Max number of user-keys in a single global index on a partitioned table   : 30
  Max number of referential constraints per table                           : 192
  Max number of keys in a referential integrity constraint                  : 16

Partition related limits

  Max number of partitions in a table                                       : 2147483646
  Max number of keys in a partition condition                               : 31

Cache manager related limits

  Default number of buffers in a named cache                                : 256

General SQL related

  Max size of character literals, sproc parameters                          : 16384
  Max size of local @variables in T-SQL                                     : 16384
  Max number of arguments to stored procedures                              : 32767
  Max number of arguments to dynamic SQL                                    : 32767
  Max number of aggregates in a COMPUTE clause                              : 254
  Max number of arguments to Java methods                                   : 31
  Max number of user tables in a single SQL statement                       : 512
  Max number of internal work tables in a single SQL statement              : 46
  Max number of subqueries in a single statement                            : 50
  Max number of user-supplied expressions in select list                    : 4096
  Max number of referential integrity user tables per query                 : 192
  Max number of referential integrity work tables per query                 : 192

Maximum lengths of different Identifiers

  Max length of server name                                                 : 30
  Max length of host name                                                   : 30
  Max length of login name                                                  : 30
  Max length of user name                                                   : 30
  Max length of password                                                    : 30
  Max length of role name                                                   : 30
  Max length of a device name                                               : 30
  Max length of a database name                                             : 30
  Max length of a segment name                                              : 30
  Max length of cursor name                                                 : 30
  Max length of engine group name                                           : 30
  Max length of dump file name                                              : 30
  Max length of network name                                                : 32
  Max length of IPv4 or IPv6 network address name                           : 64
  Max length of physical name for a device                                  : 127
  Max length of manifest filename used during mount/unmount                 : 127
  Max length of table name                                                  : 255
  Max length of partition name                                              : 255
  Max length of column name                                                 : 255
  Max length of index name                                                  : 255
  Max length of procedure name                                              : 255
  Max length of named-cache name                                            : 255
  Max length of user-defined type name                                      : 255
  Max length of webservice name and its alias                               : 255
  Max size of Java method signature in bytes                                : 16384

Limits as a function of the page size:
======================================

            Item dependent on page size                             : 2048    4096    8192    16384
-----------------------------------------------------------------------------------------------------------

Server-wide, Database-specific limits and sizes

  Min number of virtual pages in master device                      : 11780   22532   45060   90116
  Default number of virtual pages in master device                  : 23556   45060   90116   180228
  Min number of logical pages in master device                      : 11776   11264   11264   11264
  Min number of logical pages in tempdb                             : 2048    1536    1536    1536
  Max size of a database (Tb)                                       : 8       16      32      64

Table-specific row-size limits

  Max possible size of a log-record row on APL log page             : 2014    4062    8158    16350

  Physical Max size of an APL data row, incl row-overheads          : 1962    4010    8106    16298
  Physical Max size of a  DOL data row, incl row-overheads          : 1964    4012    8108    16300

  Max user-visible size of an APL data row                          : 1960    4008    8104    16296
  Max user-visible size of a  DOL data row                          : 1958    4006    8102    16294
  Max user-visible size of a fixed-length column in an APL table    : 1960    4008    8104    16296
  Max user-visible size of a fixed-length column in a  DOL table    : 1958    4006    8102    16294
  Max user-visible size of a variable-length column in an APL table : 1948    3988    8068    16228
  Max user-visible size of a variable-length column in a  DOL table : 1954    4002    8098    16290
  Max user-visible size of an in-row LOB column in an APL table     : 1928    3968    8048    16208
  Max user-visible size of an in-row LOB column in a  DOL table     : 1934    3982    8078    16270

  Max number of rows per APL data page                              : 256     256     256     256
  Max number of rows per DOL data page                              : 166     337     678     1361

Index-specific row-size limits

  Max index row-size, including row-overheads                       : 650     1300    2700    5400
  Max user-visible index row-size                                   : 600     1250    2600    5300

OAM-manager related limits

  Max number of OAM entries per OAM page                            : 250     506     1018    2042

Text-manager related limits

  Max text size available for user data                             : 1800    3600    7650    16200

Cache manager related limits

  Min size of named cache (KB)                                      : 512     1024    2048    4096
  Default size of named cache (KB)                                  : 1024    2048    4096    8192

DBCC execution completed. If DBCC printed error messages, contact a user with System Administrator (SA) role.

再来贴一下ASE 12.5版本上dbcc serverlimits的输出:

1> dbcc traceon(3604)
2> go
DBCC execution completed. If DBCC printed error messages, contact a user with System Administrator (SA) role.
1> dbcc serverlimits
2> go
Limits independent of page size:
================================

Server-wide, Database-specific limits and sizes

  Max engines per server                                        : 128
  Max number of logins per server                               : 2147516416
  Max number of users per database                              : 2146484223
  Max number of groups per database                             : 1032193
  Max number of user-defined roles per server                   : 1024
  Max number of user-defined roles per (user) session           : 127
  Min database page size                                        : 2048
  Max database page size                                        : 16384
  Initial master database logical page count                    : 3072
  Initial model database logical page count                     : 1024
  Default logical pages in a new database                       : 1024
  Min size of sybsystemprocs, in MB                             : 80
  Recommended size of sybsystemprocs, in MB                     : 90
  Max length of a database-object name                          : 30

Database page-specific limits

  APL page header size                                          : 32
  DOL page header size                                          : 44

Table, Index related limits

  Max number of columns in a table/view                         : 1024
  Max number of indexes on a table                              : 250
  Max number of user-keys in a single index                     : 31
  Max number of referential constraints per table               : 192
  Max number of keys in a referential integrity constraint      : 16

Cache manager related limits

  Default number of buffers in a named cache                    : 256

General SQL related

  Max size of character literals, sproc parameters              : 16384
  Max size of local @variables in T-SQL                         : 16384
  Max number of arguments to stored procedures                  : 2048
  Max number of aggregates in a COMPUTE clause                  : 254
  Max number of arguments to Java methods                       : 31
  Max number of user tables in a single SQL statement           : 50
  Max number of internal work tables in a single SQL statement  : 14
  Max number of subqueries in a single statement                : 16
  Max number of user-supplied expressions in select list        : 4096

Limits as a function of the page size:
======================================

            Item dependent on page size                             : 2048    4096    8192    16384
-----------------------------------------------------------------------------------------------------------

Server-wide, Database-specific limits and sizes

  Min number of virtual pages in master device                      : 6660    12292   24580   49156
  Default number of virtual pages in master device                  : 13316   24580   49156   98308
  Min number of logical pages in master device                      : 6656    6144    6144    6144
  Min number of logical pages in tempdb                             : 1536    1024    1024    1024

Table-specific row-size limits

  Max possible size of a log-record row on APL log page             : 2014    4062    8158    16350

  Physical Max size of an APL data row, incl row-overheads          : 1962    4010    8106    16298
  Physical Max size of a  DOL data row, incl row-overheads          : 1964    4012    8108    16300

  Max user-visible size of an APL data row                          : 1960    4008    8104    16296
  Max user-visible size of a  DOL data row                          : 1958    4006    8102    16294
  Max user-visible size of a fixed-length column in an APL table    : 1960    4008    8104    16296
  Max user-visible size of a fixed-length column in a  DOL table    : 1958    4006    8102    16294
  Max user-visible size of a variable-length column in an APL table : 1948    3988    8068    16228
  Max user-visible size of a variable-length column in a  DOL table : 1954    4002    8098    16290

  Max number of rows per APL data page                              : 256     256     256     256
  Max number of rows per DOL data page                              : 166     337     678     1361

Index-specific row-size limits

  Max index row-size, including row-overheads                       : 650     1300    2700    5400
  Max user-visible index row-size                                   : 600     1250    2600    5300

OAM-manager related limits

  Max number of OAM entries per OAM page                            : 250     506     1018    2042

Text-manager related limits

  Max text size available for user data                             : 1800    3600    7650    16200

Cache manager related limits

  Min size of named cache (KB)                                      : 512     1024    2048    4096
  Default size of named cache (KB)                                  : 1024    2048    4096    8192

DBCC execution completed. If DBCC printed error messages, contact a user with System Administrator (SA) role.
1>

通过对比ASE 15.7以及ASE 12.5的dbcc serverlimits输出,可以看到ASE 12.5中对象名称长度限制均为30字节,ASE 15中部分对象名称长度限制增至255字节部分长度限制仍为30字节。

另外,ASE 12.5.x或ASE 15.7对表或试图的列数上限为均为:1024。但是这还要考虑非定长列的情况,对于APL(allpages)表上所能包含的非定长列数最多为254。在新建或添加列或修改表锁定模式时,如果APL上所有非定长列数超过254,则会报错:

Msg 1767, Level 16, State 1:
Server 'TEST', Line 1:
Number of variable length columns exceeds limit of 254 for allpage locked tables. CREATE TABLE for 'testaplcolnum' failed.

对于DOL(datapages、datarows)表不存在非定长列数上限为254,也就是最终的上限不能超过1024列。

  • 本文链接地址:http://www.sybrepair.com/ase-object-name-length-limit.htm
  • 本文为dbainfo个人原创,请在尊重作者劳动成果的前提下进行转载;
  • 转载务必注明原始出处 : Sybase数据库技术,数据库恢复专家
  • 对《ASE对象名称长度限制》有何疑问或见解,请在本文下方发表;
  • 对网站还有其他问题或建议,请提交在留言板,谢谢!
  • 目前还没有任何评论.
    :wink: :twisted: :roll: :oops: :mrgreen: :lol: :idea: :evil: :cry: :arrow: :?: :-| :-x :-o :-P :-D :-? :) :( :!: 8-O 8)