What's New in SYBASE SQL Server Release 10.0?

Home

Chapter 2: Changes That May Affect Existing Applications

What's New in SYBASE SQL Server Release 10.0?

Chapter 1: New Features in SQL Server Release 10.0

Summary by Command Name, Type, Tables Affected

set Options

The following table summarizes new and changed set options:

Table 1-3: : New set Options

Option Use

set ansinull {on | off} Toggles ANSI treatment of NULL-valued operands in equality (=) and inequality (!=) comparisons. When on , treatment is ANSI `89 compliant.

set ansi_permissions {on | off} Determines whether ANSI-compliant permissions requirements for update and delete statements are checked.

set arithabort [arith_overflow |
numeric_truncation] {on | off} Determines whether SQL Server aborts a query when an arithmetic overflow or numeric truncation error occurs.

set arithignore [arith_overflow] {on | off} Determines whether SQL Server displays a warning message after any query that results in arithmetic overflow.

set chained {on | off} Toggles the chained transaction mode in SQL Server.

set close on endtran {on | off} Forces all open cursors to be closed when the transaction ends ( on ) or to remain open across transactions ( off ).

set cursor rows number
for cursor_name Sets the number of cursor rows returned to the host on each fetch for the specified client cursor. The default is 1.

set dup_in_subquery {on | off} Controls whether a subquery using the in clause returns duplicate values. The default is off ; duplicate rows are not returned. In prior SQL Server releases, a row was returned for each matching row in the subquery. ANSI specifies removing duplicate values from the result set.

set fipsflagger {on | off} Toggles the FIPS flagger. When on , the option prints warning messages for the Transact-SQL extensions to ANSI 89 SQL.

set identity_insert table_name
{on | off} Determines whether a user can explicitly insert a value into table_name 's IDENTITY column. Users can turn this option on for only one table at a time per database.

set quoted_identifier {on | off} Determines whether SQL Server treats strings enclosed in double quotes (") as identifiers.

set role {"sa_role" |"sso_role"
| "oper_role"} {on | off} Toggles the specified role during the current SQL Server session.

set self_recursion {on | off} Switches trigger self-recursion on or off, so that triggers that modify data cause the trigger to fire again.

set string_rtruncation {on | off} Determines if silent truncation of character strings is allowed. When on , treatment is ANSI `89 compliant

set transaction isolation level {1|3} Sets the transaction isolation level. An isolation level of 3 is equivalent of doing select with holdlock .

**Table 1-3: : New set Options**
Option	Use
set ansinull {on \| off}	Toggles ANSI treatment of NULL-valued operands in equality (=) and inequality (!=) comparisons. When on , treatment is ANSI `89 compliant.
set ansi_permissions {on \| off}	Determines whether ANSI-compliant permissions requirements for update and delete statements are checked.
set arithabort [arith_overflow \| numeric_truncation] {on \| off}	Determines whether SQL Server aborts a query when an arithmetic overflow or numeric truncation error occurs.
set arithignore [arith_overflow] {on \| off}	Determines whether SQL Server displays a warning message after any query that results in arithmetic overflow.
set chained {on \| off}	Toggles the chained transaction mode in SQL Server.
set close on endtran {on \| off}	Forces all open cursors to be closed when the transaction ends ( on ) or to remain open across transactions ( off ).
set cursor rows number for cursor_name	Sets the number of cursor rows returned to the host on each fetch for the specified client cursor. The default is 1.
set dup_in_subquery {on \| off}	Controls whether a subquery using the in clause returns duplicate values. The default is off ; duplicate rows are not returned. In prior SQL Server releases, a row was returned for each matching row in the subquery. ANSI specifies removing duplicate values from the result set.
set fipsflagger {on \| off}	Toggles the FIPS flagger. When on , the option prints warning messages for the Transact-SQL extensions to ANSI 89 SQL.
set identity_insert table_name {on \| off}	Determines whether a user can explicitly insert a value into table_name 's IDENTITY column. Users can turn this option on for only one table at a time per database.
set quoted_identifier {on \| off}	Determines whether SQL Server treats strings enclosed in double quotes (") as identifiers.
set role {"sa_role" \|"sso_role" \| "oper_role"} {on \| off}	Toggles the specified role during the current SQL Server session.
set self_recursion {on \| off}	Switches trigger self-recursion on or off, so that triggers that modify data cause the trigger to fire again.
set string_rtruncation {on \| off}	Determines if silent truncation of character strings is allowed. When on , treatment is ANSI `89 compliant
set transaction isolation level {1\|3}	Sets the transaction isolation level. An isolation level of 3 is equivalent of doing select with holdlock .

set showplan now prints full command names instead of abbreviations.

New and Changed Built-In Functions

The following table lists new and changed built-in functions:

Table 1-4: : New and Changed Built-In Functions

Name Function

col_name Changed to accept a database name as an optional third parameter.

inttohex New datatype conversion function. Returns the platform- independent hexadecimal equivalent of an integer.

hextoint New datatype conversion function. Returns the platform- independent integer equivalent of a hexadecimal string.

index_col Changed to accept a user name as an optional fourth parameter.

lct_admin New system function. Adds a last-chance threshold on the log segment of a pre-10.0 database, or reports on the last- chance threshold status, or wakes up processes waiting for a threshold, depending on the parameter you use.

object_name Changed to accept a database name as an optional second parameter.

proc_role New system function. Checks to see if the user possesses the specified role. Useful for restricting execution of procedures.

show_role New system function. Shows the user's current, active roles.

user New ANSI compatible system function returns the user name.

**Table 1-4: : New and Changed Built-In Functions**
Name	Function
col_name	Changed to accept a database name as an optional third parameter.
inttohex	New datatype conversion function. Returns the platform- independent hexadecimal equivalent of an integer.
hextoint	New datatype conversion function. Returns the platform- independent integer equivalent of a hexadecimal string.
index_col	Changed to accept a user name as an optional fourth parameter.
lct_admin	New system function. Adds a last-chance threshold on the log segment of a pre-10.0 database, or reports on the last- chance threshold status, or wakes up processes waiting for a threshold, depending on the parameter you use.
object_name	Changed to accept a database name as an optional second parameter.
proc_role	New system function. Checks to see if the user possesses the specified role. Useful for restricting execution of procedures.
show_role	New system function. Shows the user's current, active roles.
user	New ANSI compatible system function returns the user name.

In addition, many of the mathematical functions have been changed to accept the new numeric datatypes, as appropriate.

See Volume 1, Chapter 2 of the SYBASE SQL Server Reference Manual for information on these functions.

New System Procedures

The following new system procedures provide some of the functionality for the features described earlier in this summary. The table shows the procedure name and its associated function:

Table 1-5: : New System Procedures

Name Function

sp_addauditrecord Allows users to enter user-defined audit records (comments) into the audit trail.

sp_addthreshold Creates a free-space threshold to monitor space remaining on a database segment.

sp_auditdatabase Establishes auditing of different types of events within a database, or of references to objects within that database from another database.

sp_auditlogin Audits a user's attempts to access tables and views, and/or the text of a user's commands.

sp_auditobject Establishes auditing of accesses to tables and views.

sp_auditoption Enables and disables system-wide auditing and global audit options.

sp_auditsproc Audits the execution of stored procedures and triggers.

sp_bindmsg Binds a user message to an integrity constraint.

sp_checkreswords Checks for reserved words used as identifiers. The procedure is run as part of pre-upgrade.

sp_configurelogin Initializes the security-relevant information for a new Secure SQL Server login.

sp_cursorinfo Reports information about a specific cursor or all cursors which are active.

sp_dbremap Forces changes made by alter database to be recognized by SQL Server. Run this procedure only if instructed by SQL Server messages.

sp_displaylogin Displays information about a login account.

sp_dropthreshold Removes a free-space threshold from a segment.

sp_estspace Estimates the amount of space needed for a table and its indexes.

sp_helpconstraint Reports information about any integrity constraints specified for a table.

sp_helpthreshold Reports information on all thresholds in the current database or all thresholds for a particular segment.

sp_locklogin Prevents a user from logging in, or displays a list of all locked accounts.

sp_modifylogin Modifies default database, default language, and full name information for a SQL Server login account.

sp_modifythreshold Changes parameters for existing thresholds in a database.

sp_procxmode Displays or changes the transaction modes associated with stored procedures.

sp_remap Upgrades a release 4.8 or higher stored procedure, trigger, rule, default or view to be compatible with release 10.0. Use this on objects that the upgrade procedure failed to remap.

sp_role Grants or revokes roles to a SQL Server login account.

sp_thresholdaction Default threshold procedure that is executed automatically when remaining space on the log segment falls below the last-chance threshold. This procedure is not supplied by Sybase. By creating it yourself, you ensure that it is tailored to your own needs.

sp_unbindmsg Unbinds a user-defined message from a constraint.

sp_volchanged Notifies SQL Server that the operator has performed the requested volume handling during a dump or load .

**Table 1-5: : New System Procedures**
Name	Function
sp_addauditrecord	Allows users to enter user-defined audit records (comments) into the audit trail.
sp_addthreshold	Creates a free-space threshold to monitor space remaining on a database segment.
sp_auditdatabase	Establishes auditing of different types of events within a database, or of references to objects within that database from another database.
sp_auditlogin	Audits a user's attempts to access tables and views, and/or the text of a user's commands.
sp_auditobject	Establishes auditing of accesses to tables and views.
sp_auditoption	Enables and disables system-wide auditing and global audit options.
sp_auditsproc	Audits the execution of stored procedures and triggers.
sp_bindmsg	Binds a user message to an integrity constraint.
sp_checkreswords	Checks for reserved words used as identifiers. The procedure is run as part of pre-upgrade.
sp_configurelogin	Initializes the security-relevant information for a new Secure SQL Server login.
sp_cursorinfo	Reports information about a specific cursor or all cursors which are active.
sp_dbremap	Forces changes made by alter database to be recognized by SQL Server. Run this procedure only if instructed by SQL Server messages.
sp_displaylogin	Displays information about a login account.
sp_dropthreshold	Removes a free-space threshold from a segment.
sp_estspace	Estimates the amount of space needed for a table and its indexes.
sp_helpconstraint	Reports information about any integrity constraints specified for a table.
sp_helpthreshold	Reports information on all thresholds in the current database or all thresholds for a particular segment.
sp_locklogin	Prevents a user from logging in, or displays a list of all locked accounts.
sp_modifylogin	Modifies default database, default language, and full name information for a SQL Server login account.
sp_modifythreshold	Changes parameters for existing thresholds in a database.
sp_procxmode	Displays or changes the transaction modes associated with stored procedures.
sp_remap	Upgrades a release 4.8 or higher stored procedure, trigger, rule, default or view to be compatible with release 10.0. Use this on objects that the upgrade procedure failed to remap.
sp_role	Grants or revokes roles to a SQL Server login account.
sp_thresholdaction	Default threshold procedure that is executed automatically when remaining space on the log segment falls below the last-chance threshold. This procedure is not supplied by Sybase. By creating it yourself, you ensure that it is tailored to your own needs.
sp_unbindmsg	Unbinds a user-defined message from a constraint.
sp_volchanged	Notifies SQL Server that the operator has performed the requested volume handling during a dump or load .

The following procedures are used for chargeback accounting. This feature is now available on all platforms. In earlier releases, it was available only on VMS.

Table 1-6: : Chargeback Accounting System Procedures

Name Function

sp_clearstats Initiates a new accounting period for all server users, or a specified user. Prints out statistics for the previous period by executing sp_reportstats .

sp_reportstats Reports statistics on system usage.

**Table 1-6: : Chargeback Accounting System Procedures**
Name	Function
sp_clearstats	Initiates a new accounting period for all server users, or a specified user. Prints out statistics for the previous period by executing sp_reportstats .
sp_reportstats	Reports statistics on system usage.

See Volume 2 of the SQL Server Reference Manual for information on these procedures.

Changes to System Procedures

The following system procedures have been changed in this release:

Table 1-7: : Changes to System Procedures

Name Changes

sp_addsegment,
sp_dropsegment,
sp_extendsegment These procedures now require a database name as the second argument. This change prevents users from accidentally affecting segments in the wrong databases.

sp_defaultdb
sp_defaultlanguage These procedures have been superseded by sp_modifylogin , which can also change or add "full name" information about a login account. These procedures are still provided by Sybase, but are no longer documented. Use sp_modifylogin instead.

sp_helpdb Now displays information about how much space is left on each disk piece assigned to the database.

sp_helpsegment Now displays information about the amount of free space left on the segment

sp_lock Now displays information whether a lock is associated with a cursor.

sp_who Displays different status values for sleeping processes: recv sleep, send sleep, lock sleep and alarm sleep.

**Table 1-7: : Changes to System Procedures**
Name	Changes
sp_addsegment, sp_dropsegment, sp_extendsegment	These procedures now require a database name as the second argument. This change prevents users from accidentally affecting segments in the wrong databases.
sp_defaultdb sp_defaultlanguage	These procedures have been superseded by sp_modifylogin , which can also change or add "full name" information about a login account. These procedures are still provided by Sybase, but are no longer documented. Use sp_modifylogin instead.
sp_helpdb	Now displays information about how much space is left on each disk piece assigned to the database.
sp_helpsegment	Now displays information about the amount of free space left on the segment
sp_lock	Now displays information whether a lock is associated with a cursor.
sp_who	Displays different status values for sleeping processes: recv sleep, send sleep, lock sleep and alarm sleep.

New Configuration Variables

There are new configuration variables for use with sp_configure :

Table 1-8: : New Configuration Options

Option Function

additional netmem Memory added for use with variable packet sizes

audit queue size Number of audit records in the audit queue

cpu flush Used to configure chargeback accounting (new on non- OpenVMS platforms).

default network packet size Default packet size.

extent i/o buffers Allocates additional memory to be used for buffering data pages during create index and order by read/writes to disk.

identity burning set factor Determines the percentage of potential IDENTITY column values made available in memory. When assigning a new IDENTITY column value, SQL Server chooses the next value from this set. If the server fails before assigning these values, it discards any remaining values in the set, leading to gaps in IDENTITY column values.

i/o flush Used to configure chargeback accounting (new on non- OpenVMS platforms).

maximum network packet size Maximum allowable packet size.

password expiration interval Sets password expiration time.

**Table 1-8: : New Configuration Options**
Option	Function
additional netmem	Memory added for use with variable packet sizes
audit queue size	Number of audit records in the audit queue
cpu flush	Used to configure chargeback accounting (new on non- OpenVMS platforms).
default network packet size	Default packet size.
extent i/o buffers	Allocates additional memory to be used for buffering data pages during create index and order by read/writes to disk.
identity burning set factor	Determines the percentage of potential IDENTITY column values made available in memory. When assigning a new IDENTITY column value, SQL Server chooses the next value from this set. If the server fails before assigning these values, it discards any remaining values in the set, leading to gaps in IDENTITY column values.
i/o flush	Used to configure chargeback accounting (new on non- OpenVMS platforms).
maximum network packet size	Maximum allowable packet size.
password expiration interval	Sets password expiration time.

The serial number field, configuration value 114, has been removed.

New Database Options

There are new database options:

Table 1-9: : New sp_dboption Database Options

Option Function

abort tran on log full Determines whether user processes are suspended (the default) or aborted when the last-chance threshold on a database's log segment is reached.

allow nulls by default Changes the default null type for create table statements from not null (the Transact-SQL default) to null (the ANSI default.)

auto identity Automatically defines a 10-digit IDENTITY column, syb_identity_col , in each new table that does not specify a primary key, a unique index, or an IDENTITY column. The column is not visible with a select * statement; you must include the column name in the select list to retrieve it.

ddl in tran Allows data definition language in user transactions. The allowed commands are all create and drop commands, except create/drop database , plus grant and revoke commands.

no free space acctg Suppresses free space accounting and the execution of threshold actions on non-log segments of a database.

**Table 1-9: : New sp_dboption Database Options**
Option	Function
abort tran on log full	Determines whether user processes are suspended (the default) or aborted when the last-chance threshold on a database's log segment is reached.
allow nulls by default	Changes the default null type for create table statements from not null (the Transact-SQL default) to null (the ANSI default.)
auto identity	Automatically defines a 10-digit IDENTITY column, syb_identity_col , in each new table that does not specify a primary key, a unique index, or an IDENTITY column. The column is not visible with a select * statement; you must include the column name in the select list to retrieve it.
ddl in tran	Allows data definition language in user transactions. The allowed commands are all create and drop commands, except create/drop database , plus grant and revoke commands.
no free space acctg	Suppresses free space accounting and the execution of threshold actions on non-log segments of a database.

You can change these options with sp_dboption.

Also, the trunc. log on chkpt. option can now be used with or without the periods, that is, trunc log on chkpt is also supported.

New System Tables

There are four new system tables in all databases:

sysconstraints
sysreferences
sysroles
systhresholds

The master database includes the following new system tables:

syslisteners
sysloginroles
syssrvroles

If you install auditing, the sybsecurity database is automatically created, with two tables: sysaudits and sysauditoptions (as well as the default system tables for all user databases).

These are fully documented in Appendix B, "The System Tables", included in both the System Administration Guide and the SQL Server Reference Manual .

Changes to Existing System Tables

The following system tables have changed in this release:

Table 1-10: : Changes to Existing System Tables

Table Changes

master..sysdatabases New columns: status2, audflags, deftabaud, defvwaud, defpraud; mode removed.

master..syslocks New column: class.

master..syslogins New columns: pwdate, audflags, fullname . Formerly reserved columns now used: status, accdate, totcpu , and totio . (The last three columns are used by chargeback accounting, formerly available on VMS only.)

master..sysmessages New column: sqlstate .

master..sysprocesses New columns: tran_name, time_blocked, network_pktsz.

master..sysusages New columns: pad and unreservedpgs .

syscolumns New columns: prec, scale .

syscomments New column: colid2 .

sysindexes New column: status2 . Changed columns: spare1 to oampgtrips, spare2 to ipgtrips .

sysobjects Changed columns: schema to schmacnt, refdate to sysstat2, category to ckfirst . New columns: objspare and audflags .

sysprotects New column: grantor . Changed columns: values for the protecttype column are different. 0 indicates grant with grant , 1 indicates regular grant , and 2 indicates revoke .

systypes New columns: prec, scale, ident, hierarchy .

Table	Changes
master..sysdatabases	New columns: status2, audflags, deftabaud, defvwaud, defpraud; mode removed.
master..syslocks	New column: class.
master..syslogins	New columns: pwdate, audflags, fullname . Formerly reserved columns now used: status, accdate, totcpu , and totio . (The last three columns are used by chargeback accounting, formerly available on VMS only.)
master..sysmessages	New column: sqlstate .
master..sysprocesses	New columns: tran_name, time_blocked, network_pktsz.
master..sysusages	New columns: pad and unreservedpgs .
syscolumns	New columns: prec, scale .
syscomments	New column: colid2 .
sysindexes	New column: status2 . Changed columns: spare1 to oampgtrips, spare2 to ipgtrips .
sysobjects	Changed columns: schema to schmacnt, refdate to sysstat2, category to ckfirst . New columns: objspare and audflags .
sysprotects	New column: grantor . Changed columns: values for the protecttype column are different. 0 indicates grant with grant , 1 indicates regular grant , and 2 indicates revoke .
systypes	New columns: prec, scale, ident, hierarchy .


	Expand Search