#EDIR DATA#
DOCTYPE='BOOK' PUBLICID='-//SYBOOKS//Default DTD//EN' SYSTEMID=''DOCTYPE='book' PUBLICID='' SYSTEMID=''What's New inSYBASE SQLname='trade' font='symbol' code='228'  charset='fontspecific'Server Release 10.0?What's New in SYBASE SQL Servername='trade' font='symbol' code='228'  charset='fontspecific' Release 10.0?REMAP="titleBO--Body"SYBASE SQL Server Release 10.0REMAP="titleBO--Body"Document ID: 36440-00-1000-04REMAP="titleBO--Body"Change Level: 2REMAP="titleBO--Body"Last Revised: June 17, 1994Principal authorship: Server Publications GroupDocument ID: 36440-00-1000REMAP="discBO--Body"This publication pertains to SYBASE SQL Server Release 10.0 of the SYBASE database management software and to any subsequent release until otherwise indicated in new editions or technical notes. Information in this document is subject to change without notice. The software described herein is furnished under a license agreement, and it may be used or copied only in accordance with the terms of the agreement.Document OrdersREMAP="discBO--Body"Customers may purchase additional copies of any document or the right to make photocopies of documentation for their in-house use.REMAP="discBO--Body"To order additional documents or photocopy rights, U.S. and Canadian customers should call Customer Fulfillment at (800) 685-8225, fax (617) 229-9845.REMAP="discBO--Body"Customers in other countries with a U.S. license agreement may contact Customer Fulfillment via the fax number. All other international customers should contact their Sybase subsidiary or local distributor.REMAP="discBO--Body"Upgrades are provided only at regularly scheduled software release dates.REMAP="discBO--Body"© Copyright Sybase, Inc., 1989, 1994. All rights reserved.REMAP="discBO--Body"No part of this publication may be reproduced, transmitted, or translated in any form or by any means, electronic, mechanical, manual, optical or otherwise, without prior written permission of Sybase, Inc.Sybase TrademarksREMAP="discBO--Body"SYBASE, the SYBASE logo, SyBooks, APT-FORMS, Data Workbench, DBA Companion, Deft, GainMomentum, SA Companion, SQL Debug, SQL Solutions, SQR, Transact-SQL, and VQL are registered trademarks of Sybase, Inc. ADA Workbench, Adaptable Windowing Environment, Application Manager, Applications from Models, APT Workbench, APT-Build, APT-Edit, APT-Execute, APT-Translator, APT-Library, Build Momentum, Camelot, Client/Server Architecture for the Online Enterprise, Client/Server for the Real World, Configurator, DataServer, Data Workbench, Database Analyzer, DB-Library, Deft Analyst, Deft Designer, Deft Educational, Deft Professional, Deft Trial, Developers Workbench, Easy SQR, Embedded SQL, Enterprise Builder, Enterprise Client/Server, Enterprise Meta Server, Enterprise Modeler, Enterprise Momentum, Gain, GainExposure, Insight, Mainframe Access Products (MAP), Momentum, Movedb, Navigation Server, Net-Gateway, Net Library, Object Momentum, OmniSQL Gateway, Omni SQL Server, OmniSQL Access Module, Open Client, Open Gateway, Open Server, Open Solutions, PC APT-Execute, PC DB-Net, PC Net Library, Report Workbench, Report Execute, Open Server, Partnerships that Work, Replication Server, Resource Manager, RW-Display Lib, RW-Library, SA Monitor, Secure SQL Server, Secure SQL Toolset, SQL Code Checker, SQL Edit/TPU, SQL Edit, SQL Monitor, SQL Server, SQL Toolset, SQL Server/CFT, SQL Server/DBM, SQL Station, SQR Toolset, SQR Developers Kit, SQR Workbench, SYBASE Client/Server Interfaces, SYBASE Gateways, SYBASE SQL Lifecycle, Sybase Synergy Program, SYBASE Virtual Server Architecture, SYBASE User Workbench, System 10, Tabular Data Stream, and The Enterprise Client/Server Company are trademarks of Sybase, Inc.REMAP="discBO--Body"All other company and product names used herein may be the trademarks or registered trademarks of their respective companies.Restricted Rights LegendREMAP="discBO--Body"Use, duplication or disclosure by the Government is subject to restrictions set forth in subparagraph (c)(1)(ii) of DFARS 52.227-7013 for the DOD and as set forth in FAR 52.227-19(a)-(d) for civilian agencies.REMAP="discBO--Body"Sybase, Inc., 6475 Christie Avenue, Emeryville, CA 94608REMAP="discBO--Body"NUMBER="1"New Features in SQL Server Release 10.0RENDERAS="SECT1"IntroductionREMAP="BO--Body"This chapter describes the new features for SYBASE SQL Servername='trade' font='symbol' code='228'  charset='fontspecific' Release 10.0. These new features include:MARK="BULL" REMAP="BU--Bullet"New Installation, Upgrade and Configuration UtilityAddition to System DatabasesBackup/Space ManagementNew Security FeaturesCursorsData Definition EnhancementsTrigger, Transaction, and Stored Procedure ChangesQuery/Data Modification ChangesANSI Compliance ChangesSystem Administration ChangesError HandlingMOREINFO="NONE"create index Performance EnhancementsQuery Optimizer EnhancementsBulk Copy Performance EnhancementsSpanish Language Sort OrdersDocument ReorganizationREMAP="BO--Body"The last section of this chapter provides a summary of changes to the following commands and system tables:MARK="BULL" REMAP="BU--Bullet"MOREINFO="NONE"set OptionsNew and Changed Built-In FunctionsNew and Changed System ProceduresNew Configuration OptionsNew Database OptionsNew System TablesChanges to System TablesID="IN201"Upgrading SQL Server
ID="IN212"Installing SQL ServerRENDERAS="SECT1"New Installation and Upgrade UtilityID="IN223"sybinitREMAP="BO--Body"A new installation program, MOREINFO="NONE"sybinit, provides a flexible menu-driven tool for managing configuration of SQL Server and Backup Server. It can be used interactively, or with a resource file if you need to install or upgrade many servers.REMAP="BO--Body"MOREINFO="NONE"sybinit allows you to:MARK="BULL" REMAP="BU--Bullet"Install a new Release 10.0 SQL Server or Backup ServerUpgrade an existing SQL Server from a 4.8 or later release to Release 10.0ID="IN204"Modifying SQL ServerModify an existing 10.0 SQL Server to install and activate auditingInstall languages and character setsConfigure your default language, sort order or character setInstall other Sybase System 10name='trade' font='symbol' code='228'  charset='fontspecific' productsID="IN155"Interfaces fileREMAP="BO--Body"It also helps manage your system's interfaces file, providing a convenient tool for adding, deleting or changing interfaces file entries.REMAP="BO--Body"See your SYBASE SQL Server Installation Guide for information on running MOREINFO="NONE"sybinit.ID="IN176"System procedures
ID="IN1167"sybsystemprocs database
ID="IN308"System databasesRENDERAS="SECT1"Addition to System DatabasesREMAP="BO--Body"Release 1ID="IN1009"master database
ID="IN2510"Catalog stored procedures
0.0 provides many new features, including more than 25 new system procedures. In addition, the Catalog Stored procedures (formerly installed separately) are now included in the default installation procedures. The space requirements for the new system tables and system procedures would nearly double the size requirement for the master database. Since master must reside entirely on a single device, many customers would face serious problems (in some cases requiring disk repartitioning and moving master).REMAP="BO--Body"Release 10.0 solves this problem by removing the largest space users from master in a way that is transparent to ordinary users of SQL Server and requires a minimum of work for the System Administrator who upgrades SQL Server. REMAP="BO--Body"In Release 10.0, the SYBASE system procedures are stored in a new database called sybsystemprocs, rather than in the master database. When your installation or upgrade is complete, Sybase system procedures reside in the new database. Special search routines for executing any procedure name beginning with "sp_" allow SQL Server to locate the system procedures, even if the procedure call is fully qualified with the database name "master". For example, MOREINFO="NONE"master..sp_who finds the procedure.ID="IN2411"Moving system proceduresRENDERAS="SECT2"Benefits of Moving System ProceduresREMAP="BO--Body"The new structure provides several benefits:MARK="BULL" REMAP="BU--Bullet"It shrinks the space used in master dramatically. The size of the master database will not have to change for a long time.It creates greater flexibility for users. Users are able to store larger numbers of local stored procedures in sybsystemprocs without worrying about space considerations on the master device.It can speed recovery of the master database. Under the pre-10.0 structure, if the master device was damaged, a significant portion of recovery time involved the re-creation of the system procedures. Now, if the system procedures are on a separate database device, master can be recovered much more quickly.The new sybsystemprocs database can be restored quickly and easily in case of disk failure without affecting the master database.The sybsystemprocs database can safely use an operating system file, if necessary, as a database device. It will be updated rarely and can be easily restored.It keeps backups of master smaller. Due to system restrictions, backups of master must fit on a single tape or file.RENDERAS="SECT2"The Upgrade ProcessREMAP="BO--Body"When you upgrade an existing SQL Server or install a new SQL Server, you specify the device where you want to install sybsystemprocs. MOREINFO="NONE"sybinit creates the device. For upgrades, it drops all existing SYBASE system procedures from master without affecting any user-created procedures stored in master. ID="IN1812"Databaseslimit ofREMAP="BO--Body"If necessary, the upgrade processes increases the number of open databases allowed on your SQL Server.REMAP="BO--Body"See LINKEND="X14018"Chapter 2
of this manual for information on moving your local procedures from master to sybsystemprocs, and other system administration information relating to this change.ID="IN2813"System databasesID="X80939" RENDERAS="SECT1"Backup/Space ManagementID="IN2114"Backup Server utilityID="X50805" RENDERAS="SECT2"Backup ServerREMAP="BO--Body"The Open Server-based Backup Server utility now handles all dumps and loads for SQL Server. Dumps and loads now work more quickly, with significantly less effect on other SQL Server activities. New dump and load syntax provides more flexibility and more options:ID="IN1315"Dump stripingMARK="BULL" REMAP="BU--Bullet"Dump striping allows you to use up to 32 dump devices in parallel to dump or load a single database or transaction log.ID="IN1616"Multi-file dumpsMulti-file/multi-volume dumps allow one dump to span multiple tapes, or allow multiple dumps to be made to a single tape.ID="INA1317"Network dumpsNetwork dumps allow dumping and loading over the network to or from a device on another machine.ID="IN2818"Tapeslabelling and handlingBackup Server complies with ANSI standard tape labelling and handling.Platform-specific tape handling options support dump/load command syntax specification for volume naming, dismount and load control, tape density, block size, tape capacity, days to retain, initialization, file naming for multi-dump volumes, and listing header or file information instead of loading the files.The system procedure ID="IN4819"sp_volchanged
ID="IN4620"System proceduressp_volchanged
MOREINFO="NONE"sp_volchanged signals volume changes during backups. (OpenVMS users do this with the REPLY command.) It replaces the ID="IN5821"console utility program
MOREINFO="NONE"console utility program. MOREINFO="NONE"sp_volchanged can be submitted from any ordinary SYBASE client to signal volume changes to the Backup Server.Multiple dumps and loads can be managed from one or more local or remote servers.RENDERAS="SECT3"Installing a Backup ServerREMAP="BO--Body"If you do not want to use the new functionality immediately, you can continue to use your existing dump and load routines. Pre-10.0 syntax is compatible with 10.0 syntax. The only exception is that MOREINFO="NONE"dump database and MOREINFO="NONE"dump transaction commands cannot be included in a user-defined transaction in Release 10.0.REMAP="BO--Body"Dump and load commands must use Backup Server. It must be installed and running, and SQL Server must be configured to connect to Backup Server.You cannot load dumps made with earlier releases using 10.0 dump and load commands: pre-10.0 dumps are not compatible with 10.0 SQL Server.REMAP="BO--Body"The SYBASE SQL Server Installation Guide contains information about installing Backup Servers, and the System Administration Guide Supplement for your platform contains information on supported dump devices and information about platform-specific issues.RENDERAS="SECT3"Additional Sources of InformationID="IN2822"dump databaseREMAP="BO--Body"The new syntax for the MOREINFO="NONE"dump databaseID="IN2823"load database
, MOREINFO="NONE"load database, ID="IN3124"dump transaction
MOREINFO="NONE"dump transactionID="IN3125"load transaction
, and MOREINFO="NONE"load transaction commands is documented in Volume 1 of the SQL Server Reference Manual. MOREINFO="NONE"sp_volchanged is documented in Volume 2 of the SQL Server Reference Manual.REMAP="BO--Body"Information about starting and stopping Backup Servers during routine operations can be found in MOREINFO="NONE"startserver in the SQL Server Utility Programs for your operating system, MOREINFO="NONE"shutdown in Volume 1 of the SQL Server Reference Manual, and your System Administration Guide Supplement. See BOOK="unix" LINKEND="X97777""backupserver"
in the SQL Server Utility Programs for information about flags for the MOREINFO="NONE"backupserver executable.REMAP="BO--Body"See BOOK="sag" LINKEND="X31864"Chapter 7,  "Developing a Backup and Recovery Plan"
, in the System Administration Guide, for a complete discussion of Backup Server.ID="IN1626"Space monitoring
ID="IN1727"Threshold ManagerID="X12942" RENDERAS="SECT2"Threshold ManagerID="IN1628"Monitoring space
ID="IN1629"Monitoring space
ID="IN1930"SegmentsthresholdsREMAP="BO--Body"Thresholds monitor how much free space remains on a particular segment. When the amount of free space falls below a threshold, SQL Server automatically executes the associated stored procedure. For example:MARK="BULL" REMAP="BU--Bullet"You can create a threshold and stored procedure to dump the transaction log when space runs low.ID="IN2831"Stored proceduresthresholdsYou can create a threshold and a stored procedure that detects when space for the data segment of a database runs low, so that messages in the error log warn you before space runs out.ID="IN3032"sp_addthreshold
ID="IN4833"System proceduressp_addthresholdREMAP="BO--Body"The system procedures MOREINFO="NONE"sp_addthresholdID="IN3134"sp_dropthreshold
, ID="INA4935"System proceduressp_dropthreshold
MOREINFO="NONE"sp_dropthreshold, ID="IN3336"sp_modifythreshold
MOREINFO="NONE"sp_modifythreshold and ID="IN3137"sp_helpthreshold
ID="IN4938"System proceduressp_helpthreshold
MOREINFO="NONE"sp_helpthreshold create, drop, change and monitor free space thresholds in a database. See Volume 2 of the SQL Server Reference Manual for specific syntax information. BOOK="sag" LINKEND="X63254"Chapter 10,  "Managing Free Space with Thresholds"
, in the System Administration Guide provides additional examples.ID="IN1239"Log segments
ID="IN2140"Last-chance threshold
ID="IN3341"sp_thresholdactionREMAP="BO--Body"In databases that store data and logs on separate segments, SQL Server now installs a last-chance threshold on the log segment. This threshold calls a procedure named ID="IN5142"System proceduressp_thresholdaction
MOREINFO="NONE"sp_thresholdaction. You can use MOREINFO="NONE"sp_modifythreshold to call a different stored procedure instead of MOREINFO="NONE"sp_thresholdaction.REMAP="BO--Body"Since user requirements for threshold procedures vary so widely, Sybase does not provide MOREINFO="NONE"sp_thresholdactionID="IN10843"lct_admin function
; you must write it yourself. A special system function, MOREINFO="NONE"lct_admin, creates last-chance thresholds on databases created under pre-10.0 SQL Servers.REMAP="BO--Body"The new MOREINFO="NONE"abort tran on log full option for ID="IN8544"sp_dboptionsp_dboptionand thresholdsthresholds
MOREINFO="NONE"sp_dboption allows you to choose whether to suspend or kill processes that attempt to write to the log when there is not enough log space.REMAP="BO--Body"See MOREINFO="NONE"sp_addthreshold, MOREINFO="NONE"sp_dboption, and BOOK="sag" LINKEND="X63254"Chapter 10,  "Managing Free Space with Thresholds"
, of the System Administration Guide for examples. RENDERAS="SECT3"Variables Allowed in Dump and Load CommandsID="IN2745"Load commandsand variables
ID="IN2746"Dump commandsand variablesREMAP="BO--Body"As part of the Release 10.0 threshold improvements, you can now use parameters or variables in dump and load commands for database and device names. This allows a single stored procedure (such as MOREINFO="NONE"sp_thresholdaction) to dump multiple databases or transaction logs. See BOOK="ref" LINKEND="X68345""dump database"
MOREINFO="NONE", BOOK="ref" LINKEND="X47924""dump transaction"
MOREINFO="NONE", BOOK="ref" LINKEND="X28090""load database"
and BOOK="ref" LINKEND="X75212""load transaction"
in Volume 1 of the SQL Server Reference Manual.ID="IN847"SecurityID="X23828" RENDERAS="SECT1"New Security FeaturesREMAP="BO--Body"SQL Server Release 10.0 provides features targeted at the C2 level of trust. Many of the security features provide new flexibility for SQL Server users who do not need all of the C2 functionality. The major features are:MARK="BULL" REMAP="BU--Bullet"New system administration rolesNew user identification and authentication features, including password encryption A complete system for auditing activity on SQL ServerID="X61028" RENDERAS="SECT2"System Administration RolesID="IN548"RolesREMAP="BO--Body"In previous releases, SQL Server allowed only one login, "sa", to perform most system administration tasks. SQL Server Release 10.0 recognizes the following security-related operational and administrative roles:ID="IN2549"System Administrator roleMARK="BULL" REMAP="BU--Bullet"System Administrator: required for tasks such as manipulating disk space and memory usage, granting and revoking permission to execute MOREINFO="NONE"create database statements, running diagnostic and repair functions that read data pages or recover data or indexes in a controlled manner, granting and revoking the System Administrator role, and modifying, dropping, and locking server login accounts.ID="IN2850"System Security Officer roleSystem Security Officer: required for security-related tasks such as creating server login accounts, changing passwords, granting and revoking roles, configuring the password expiration interval, and managing the audit system.ID="IN1351"Operator roleOperator: used for backing up and restoring databases.REMAP="BO--Body"These roles can be granted to and revoked from individual login accounts in SQL Server, and a login account may possess more than one role. Roles can be granted permissions on objects and commands, similar to groups. Details on roles can be found in BOOK="sag" LINKEND="X92769"Chapter 2,  "Roles in SQL Server"
, in the System Administration Guide.REMAP="BO--Body"When Release 10.0 of SQL Server is installed, it still has the default "sa" account, which has System Administrator, System Security Officer, and Operator roles enabled. For greater accountability for the highly privileged users in your system, it is recommended that you create individual login accounts for users who are to be granted these privileges, grant them their roles, and then lock the "sa" account. If you have automated scripts or programs that log into SQL Server as "sa", see LINKEND="X39308""Using Roles in SQL Server"
for more information.ID="IN1752"Identifying usersRENDERAS="SECT2"User IdentificationREMAP="BO--Body"There are now a variety of mechanisms SQL Server uses to positively identify an individual user and to enforce accountability and security, such as:ID="IN2153"Login account lockingMARK="BULL" REMAP="BU--Bullet"Login account locking makes it possible to lock a user's account without dropping the user and the user's objects from all databases. ID="IN1654"PasswordslengthA minimum password length of 6 bytes makes passwords more secure. (This does not affect existing user passwords, but all new or changed passwords must be at least 6 bytes.).ID="IN2055"Passwordsencryption
ID="IN5656"syslogins system tablePasswords are stored in master..syslogins in encrypted form.ID="IN2057"PasswordsexpirationYou can enable optional system-wide password expiration.You can enable optional client-side password encryption.Recovery mechanisms are provided in the case of lost or expired passwords.REMAP="BO--Body"New sections in Volume 1 of the SQL Server Reference Manual called BOOK="ref" LINKEND="X96191""Roles"
and BOOK="ref" LINKEND="X16582""Login Management"
provide an overview of the system procedures and other commands used to manage these features.ID="IN858"AuditingRENDERAS="SECT2"AuditingREMAP="BO--Body"SQL Server can be configured to provide an audit trail for events such as:MARK="BULL" REMAP="BU--Bullet"Server logins and logoutsUse of any commands that require a special roleUse of commands that reference a specified object or databaseDeletion of objectsExecution of stored procedures and triggersAny actions performed by a specified userID="IN5459"sybsecurity databaseREMAP="BO--Body"The audit system includes a new database, sybsecurityID="IN5660"sysaudits system table
, which contains the audit trail in a system table called sysaudits. Auditing is described in BOOK="sag" LINKEND="X31147"Chapter 16,  "Auditing"
, in the System Administration Guide.ID="IN761"CursorsID="X33915" RENDERAS="SECT1"CursorsREMAP="BO--Body"SQL Server Release 10.0 provides full support for cursors. The following cursor commands are documented in Volume 1 of the SQL Server Reference Manual:REMAP="BO--Body"MOREINFO="NONE"declare cursor
fetch 
open 
close
deallocateREMAP="BO--Body"A new topic called "Cursors" in Volume 1 of the SQL Server Reference Manual provides an overview of cursors. New keywords that allow updating and deleting at cursor positions are described on the ID="IN8367"delete
MOREINFO="NONE"delete and MOREINFO="NONE"update pages. A new MOREINFO="NONE"set option controls the number of rows returned by a MOREINFO="NONE"fetchID="IN11568"sp_cursorinfo
command. A new system procedure, MOREINFO="NONE"sp_cursorinfo, provides information about the active cursors in a database.REMAP="BO--Body"BOOK="sqlug" LINKEND="X33046"Chapter 15,  "Cursors: Accessing Data Row by Row"
, in the Transact-SQL User's Guide provides examples of cursor use, including cursors in stored procedures.ID="X52362" RENDERAS="SECT1"Data Definition EnhancementsRENDERAS="SECT2"Integrity ConstraintsREMAP="BO--Body"In addition to the rules, triggers, defaults,ID="IN2169"Integrity constraints
ID="IN2770"create table
and unique indexes already provided by SQL Server, you can now specify integrity constraints in the MOREINFO="NONE"create table statement. These include:MARK="BULL" REMAP="BU--Bullet"Unique and primary key constraints to insure unique values in a columnReferential integrity constraints to guarantee that a primary key exists in another table if you are inserting or updating a foreign key tableID="IN1771"Check constraintsCheck constraints to limit the values that can be inserted into a tableDefaults to specify default values for a columnID="IN1872"Unique constraints
ID="IN2373"Primary key constraints
ID="IN3374"Referential integrity constraints
ID="IN875"Defaults
ID="IN1176"Constraints
ID="IN2677"alter tableREMAP="BO--Body"Constraints can be changed or dropped with MOREINFO="NONE"alter table. You can get information about the constraints defined for a table using the newID="IN12378"sp_helpconstraint
MOREINFO="NONE" sp_helpconstraint system procedure.REMAP="BO--Body"See BOOK="ref" LINKEND="X80969""create table"
and BOOK="ref" LINKEND="X68185""alter table" in Volume 1 of the SQL Server Reference Manual for the new syntax. BOOK="sqlug" LINKEND="X80556"Chapter 6,  "Creating Databases and Tables"
, in the Transact-SQL User's Guide describes how to use the constraints along with additional examples.ID="IN579"ViewsID="X50559" RENDERAS="SECT2"Changes to ViewsID="IN6880"distinctdistinctin viewsviewsREMAP="BO--Body"The MOREINFO="NONE"distinctID="IN9681"create viewcreate viewand distinctdistinct
keyword can now be used in view definition, allowing you to create views that do not contain duplicate rows.ID="IN5982"with check option clauseREMAP="BO--Body"The MOREINFO="NONE"with check optionID="IN7983"create viewwith check option
ID="IN2684"create view
clause has been added to the MOREINFO="NONE"create view statement. When a view is created MOREINFO="NONE"with check option, all rows inserted or updated through the view must meet the view criteria.REMAP="BO--Body"ID="IN5985"Batches and create viewBatches create view
ID="IN7986"create viewcreate viewand batchesbatches
MOREINFO="NONE"create view statements can be included in batches with other Transact-SQL® statements. REMAP="BO--Body"See BOOK="ref" LINKEND="X30101""create view"
in Volume 1 of the SQL Server Reference Manual, or BOOK="sqlug" LINKEND="X92935"Chapter 8,  "Views: Limiting Access to Data"
, in the Transact-SQL User's Guide for more information and examples.ID="IN787"SchemasID="X64904" RENDERAS="SECT2"SchemasID="IN2888"create schemaREMAP="BO--Body"The new MOREINFO="NONE"create schema command allows you to create several objects and grant permissions on those objects in a single statement, which can be committed or rolled back as a unit. See BOOK="ref" LINKEND="X48762""create schema"
in Volume 1 of the SQL Server Reference Manual.ID="X16228" RENDERAS="SECT2"DatatypesREMAP="BO--Body"Release 10.0 of SQL Server provides these new datatypes:MARK="BULL" REMAP="BU--Bullet"ID="IN5189"numeric datatype
ID="IN3190"Datatypesnumeric
numeric or ID="IN5191"decimal datatype
ID="IN3192"Datatypesdecimal
decimal (dec) specify exact numeric values, with specified precision and scale to indicate the number of digits and the number of digits to the right of the decimal point.ID="IN6093"double precision datatype
ID="IN4094"Datatypesdouble precision
double precision specifies an approximate numeric type.REMAP="BO--Body"The following changes have been made to existing datatypes:MARK="BULL" REMAP="BU--Bullet"ID="IN4995"float datatype
float now accepts an optional precision specification.ID="IN2996"Datatypesfloat
ID="IN2497"Datatypesdefault lengthThe default length for char, varchar, nchar, and nvarchar columns is 1 in MOREINFO="NONE"create table statements, variable declarations, and parameter specifications.REMAP="BO--Body"All system datatype names are case-insensitive. SQL Server treats numeric constants in queries (such as MOREINFO="NONE"select 2* MOREINFO="NONE"colvalue) as exact numeric types unless they are entered using E notation. Values between 2 31 - 1 and -2 31 without decimal points are treated as int. Values that fall outside the range for integers, or that include a decimal point, are treated as numeric. See LINKEND="X14018"Chapter 2,  "Changes That May Affect Existing Applications"
, for information on how this can affect existing applications.REMAP="BO--Body"The following list shows synonymous datatype names. Names that are new in 10.0 are underlined.MARK="BULL" REMAP="BU--Bullet"ID="IN3398"Datatypescharacter
char, charactervarchar, ID="IN3699"Datatypeschar varying
char varying, character varyingnchar, ID="IN42100"Datatypesnational character
ID="IN37101"Datatypesnational char
national character, national charnvarchar, ID="IN50102"Datatypesnational character varying
national character varying, ID="IN45103"Datatypesnational char varying
ID="IN37104"Datatypesnchar varying
national char varying, nchar varyingID="IN40105"Datatypesdouble precision
double precisionID="IN29106"Datatypesfloat
, float, realID="IN27107"Datatypesdec
decID="IN31108"Datatypesdecimal
, decimal, numericint, integerREMAP="BO--Body"See BOOK="ref" LINKEND="X84316""Datatypes" in Volume 1 of the SQL Server Reference Manual or BOOK="sqlug" LINKEND="X80556"Chapter 6 in the Transact-SQL User's Guide for more information.ID="X42366" RENDERAS="SECT2"Automatic Sequential Values Using the IDENTITY PropertyID="IN23109"TablesIDENTITY columnsREMAP="BO--Body"Each table in a database can have a single IDENTITY column. This column is used to store numbers, such as invoice numbers or employee numbers, that are automatically generated by SQL Server. The value of the IDENTITY column uniquely identifies each row in a table.REMAP="BO--Body"The IDENTITY column must be of type numericID="IN52110"identity property
and scale 0. By default, IDENTITY columns cannot be updated and do not allow null values. Only the table owner, the Database Owner, or a System Administrator can explicitly insert a value into an IDENTITY column after setting MOREINFO="NONE"identity_insert on for the table.ID="IN122111"@@identity global variableREMAP="BO--Body"Each time you insert a row into a table, SQL Server assigns a unique, sequential value to the IDENTITY column. To retrieve the last value inserted into an IDENTITY column, use the @@identityID="IN55112"syb_identity keyword
global variable. To select a table's IDENTITY column, use the MOREINFO="NONE"syb_identity keyword, qualified by the table name where necessary. REMAP="BO--Body"For more information about IDENTITY columns, see BOOK="ref" LINKEND="X36521"IDENTITY Columns
and BOOK="ref" LINKEND="X80969""create table"
, BOOK="ref" LINKEND="X68185""alter table"
, BOOK="ref" LINKEND="X91835""insert"
, BOOK="ref" LINKEND="X35229""select"
, BOOK="ref" LINKEND="X30101""create view"
in Volume 1 of the SQL Server Reference Manual.ID="X17822" RENDERAS="SECT3"Automatic Creation of IDENTITY ColumnsREMAP="BO--Body"System Administrators can use the new MOREINFO="NONE"auto identity database option to automatically include a 10-digit IDENTITY column in new tables. Each time a user creates a table in the database without specifying either a MOREINFO="NONE"primary key, a MOREINFO="NONE"unique index, or an IDENTITY column, SQL Server automatically defines an IDENTITY column for the table. For more information see BOOK="ref" LINKEND="X36521""IDENTITY Columns"
in Chapter 3 of the SQL Server Reference Manual, Volume 1.RENDERAS="SECT1"Transactions, Triggers, and Stored Procedures ChangesID="IN41113"Transactionsdata definition language and
ID="IN40114"Data definition language in transactionsID="X26512" RENDERAS="SECT2"Data Definition Language in TransactionsREMAP="BO--Body"Certain data definition language commands are now allowed in user-defined transactions. These commands include:COLS="3" CHAROFF="50" CHAR="" ALIGN="LEFT"COLNAME="C1" COLWIDTH="99*"COLNAME="C2" COLWIDTH="198*"COLNAME="C3" COLWIDTH="297*"VALIGN="TOP"COLNAME="C1" ALIGN="LEFT" ROTATE="0" MOREROWS="0"MOREINFO="NONE"alter tableCOLNAME="C2" ALIGN="LEFT" ROTATE="0" MOREROWS="0"MOREINFO="NONE"create tableCOLNAME="C3" ALIGN="LEFT" ROTATE="0" MOREROWS="0"MOREINFO="NONE"drop procedureCOLNAME="C1" ALIGN="LEFT" ROTATE="0" MOREROWS="0"MOREINFO="NONE"create defaultCOLNAME="C2" ALIGN="LEFT" ROTATE="0" MOREROWS="0"MOREINFO="NONE"create triggerCOLNAME="C3" ALIGN="LEFT" ROTATE="0" MOREROWS="0"MOREINFO="NONE"drop ruleCOLNAME="C1" ALIGN="LEFT" ROTATE="0" MOREROWS="0"MOREINFO="NONE"create indexCOLNAME="C2" ALIGN="LEFT" ROTATE="0" MOREROWS="0"MOREINFO="NONE"create viewCOLNAME="C3" ALIGN="LEFT" ROTATE="0" MOREROWS="0"MOREINFO="NONE"drop tableCOLNAME="C1" ALIGN="LEFT" ROTATE="0" MOREROWS="0"MOREINFO="NONE"create procedureCOLNAME="C2" ALIGN="LEFT" ROTATE="0" MOREROWS="0"MOREINFO="NONE"drop defaultCOLNAME="C3" ALIGN="LEFT" ROTATE="0" MOREROWS="0"MOREINFO="NONE"drop triggerCOLNAME="C1" ALIGN="LEFT" ROTATE="0" MOREROWS="0"MOREINFO="NONE"create ruleCOLNAME="C2" ALIGN="LEFT" ROTATE="0" MOREROWS="0"MOREINFO="NONE"drop indexCOLNAME="C3" ALIGN="LEFT" ROTATE="0" MOREROWS="0"MOREINFO="NONE"drop viewCOLNAME="C1" ALIGN="LEFT" ROTATE="0" MOREROWS="0"MOREINFO="NONE"create schemaCOLNAME="C2" ALIGN="LEFT" ROTATE="0" MOREROWS="0"COLNAME="C3" ALIGN="LEFT" ROTATE="0" MOREROWS="0"REMAP="BO--Body"ID="IN92115"grant commandgrant commandin transactionstransactions
MOREINFO="NONE"grant ID="IN86116"revoke commandrevokein transactionstransactions
and MOREINFO="NONE"revoke commands are now allowed in transactions. However, some commands such as MOREINFO="NONE"dump database and MOREINFO="NONE"dump transaction are no longer allowed.ID="IN58117"sp_dboptionddl in tranREMAP="BO--Body"A new option for MOREINFO="NONE"sp_dboption, MOREINFO="NONE"ddl in tran, allows enabling or disabling the use of data definition language in transactions. Be sure to read the cautionary statements about using data definition language in transactions, since this activity requires locking system tables. ID="IN58118"ddl in transp_dboptionREMAP="BO--Body"For information about which commands you can use in transactions and about the MOREINFO="NONE"ddl in tran option, see BOOK="ref" LINKEND="X19352""Transactions"
in Volume 1 of the SQL Server Reference Manual.ID="IN61119"@@transtate global variable
ID="IN29120"Transaction state informationID="X74606" RENDERAS="SECT2"Transaction State Information: @@transtateID="IN42121"Global variables@@transtate
ID="IN61122"@@transtate global variableREMAP="BO--Body"SQL Server can now return information about the state of a transaction. This status information indicates whether the transaction succeeded, whether a single statement was rolled back, or whether an entire transaction was rolled back. The information is available in the new global variable, @@transtate. REMAP="BO--Body"See BOOK="ref" LINKEND="X19352""Transactions"
in Volume 1 of the SQL Server Reference Manual. "Variables" in Volume 1 of the SQL Server Reference Manual also provides a list of the status values.ID="X53450" RENDERAS="SECT2"New ID="IN31123"rollback trigger
rollback triggerID="IN71124"TriggersTriggersand rollback triggerrollback trigger
CommandREMAP="BO--Body"The rollback feature has been expanded to include MOREINFO="NONE"rollback trigger. Now, you can choose to roll back an entire transaction using MOREINFO="NONE"rollback transaction, or to roll back statements up to the first data modification that fired a trigger using MOREINFO="NONE"rollback trigger. See BOOK="ref" LINKEND="X80111""rollback trigger"
and "BOOK="ref" LINKEND="X19352"Transactions
" in Volume 1 of the SQL Server Reference Manual for more information.ID="IN23125"Triggersself-recursionID="X85110" RENDERAS="SECT2"Trigger Self-RecursionID="IN53126"set self_recursionREMAP="BO--Body"A new MOREINFO="NONE"set option, MOREINFO="NONE"self_recursionID="IN23127"Self-recursive triggers
, allows triggers to fire self-recursively: to refire as a result of data modifications made by the trigger itself. By default, when a trigger causes data modifications that would also cause the trigger to fire, the trigger is prevented from firing. See BOOK="ref" LINKEND="X64136""set"
in Volume 1 of the SQL Server Reference Manual for more information.ID="IN29128"Stored proceduressize limitsRENDERAS="SECT2"Stored Procedure Size Limits RemovedID="IN23129"Size limits for objects
ID="IN17130"Batch size limitsREMAP="BO--Body"Release 10.0 of SQL Server eliminates the size limit for compiled stored procedures and batches. To accommodate this change, the maximum amount of text allowed for a stored procedure has been increased to 16ID="IN58131"syscomments system table
Mb. This increase also applies to objects whose text is stored in the syscomments system table, such as triggers, views, defaults, rules, and constraints.ID="X95311" RENDERAS="SECT1"Query/Data Modification ChangesID="IN18132"Conversionchanges
ID="IN9133"DatatypesID="X62209" RENDERAS="SECT2"Datatype Conversion ChangesREMAP="BO--Body"Changes were made to datatype conversion rules for this release to provide greater consistency across all SYBASE products. MARK="BULL" REMAP="BU--Bullet"Formerly disallowed conversions to and from binary datatypes are now allowed.Conversion from integer to character now returns an error instead of "**" if the character value is not long enough to store the converted value.ID="IN19134"DatatypeshierarchySome changes were made to the "datatype hierarchy" that controls the output datatype of mixed-mode datatype operations. REMAP="BO--Body"See LINKEND="X14018"Chapter 2,  "Changes That May Affect Existing Applications"
, for information on how these changes may affect existing applications. The new hierarchy is included in the "Datatypes" section of Volume 1 in the SQL Server Reference Manual. REMAP="BO--Body"Additional information about datatype conversions is provided in "Datatype Conversion Functions" in Volume 1 of the SQL Server Reference Manual and BOOK="sqlug" LINKEND="X41629"Chapter 9,  "Using the Built-In Functions in Queries"
in the Transact-SQL User's Guide.RENDERAS="SECT2"New Hex Conversion FunctionsREMAP="BO--Body"Two new functions provide platform-independent conversions between integer values and hexadecimal strings. See "Datatype Conversion Functions" in Volume 1 of the SQL Server Reference Manual and BOOK="sqlug" LINKEND="X41629"Chapter 9,  "Using the Built-In Functions in Queries"
in the Transact-SQL User's GuideID="IN106136"inttohex function
for information about MOREINFO="NONE"inttohexID="IN106137"hextoint function
and MOREINFO="NONE"hextointID="IN20138"Datatypesconversion
.ID="X32661" RENDERAS="SECT2"Subquery ChangesID="IN10139"SubqueriesREMAP="BO--Body"The behavior of certain subqueries has changed. See LINKEND="X14018"Chapter 2,  "Changes That May Affect Existing Applications"
for detailed information about these changes, and how they may affect your existing applications.ID="X88522" RENDERAS="SECT2"Grouping on bit Columns AllowedID="IN50140"group by clauseREMAP="BO--Body"You can now use MOREINFO="NONE"group by on bit ID="IN47141"Datatypesbit
columns.RENDERAS="SECT2"Random Number Generator ImprovementsID="IN98142"rand functionREMAP="BO--Body"The MOREINFO="NONE"rand function now uses the output of a 32-bit pseudo-random integer generator. See BOOK="ref" LINKEND="X67592""Mathematical Functions"
in Volume 1 of the SQL Server Reference Manual for more information.RENDERAS="SECT2"between Predicate ChangesID="IN59143"between predicate changeREMAP="BO--Body"In some cases, the MOREINFO="NONE"between predicate in Transact-SQL returned results whether the values supplied were in the proper order (lower number first after the predicate) or swapped (higher number first). In Release 10.0, a query with swapped MOREINFO="NONE"between values returns no rows.ID="IN15144"ColumnheadingsID="X90661" RENDERAS="SECT2"select into ID="IN46145"select into
Column HeadingsREMAP="BO--Body"In a MOREINFO="NONE"select intoID="IN39146"Aggregate functionscolumn headings and
statement, column headings must be provided for any select list item that includes aggregate functions or expressions. See LINKEND="X14018"Chapter 2
for examples of expressions that require headings, and for examples and syntax.ID="IN39147"FunctionsAggregate and column headingsRENDERAS="SECT2"Column AliasID="IN45149"as keywordREMAP="BO--Body"You can now use the keyword MOREINFO="NONE"as in MOREINFO="NONE"select statements between the select expression and the alias name. For example:REMAP="UI--UserInput" FORMAT="LINESPECIFIC"select au_lname as lastname from authorsID="IN19150"Dynamic SQL support
ID="IN19151"Precompiler supportID="X21084" RENDERAS="SECT2"Full Dynamic SQL/Precompiler SupportREMAP="BO--Body"SQL Server Release 10.0 provides full support for host variables and Dynamic SQL. Full documentation can be found in Embedded SQL/C and Embedded SQL/COBOL Programmer's Guides.RENDERAS="SECT2"Right Truncation of Character StringsREMAP="BO--Body"In previous releases, SQL Server silently truncated char, nchar, varchar, and nvarchar strings when an MOREINFO="NONE"insert or MOREINFO="NONE"updateID="IN57154"set string_rtruncation
entered strings longer than the specified column length. A new MOREINFO="NONE"set option, MOREINFO="NONE"string_rtruncation, controls silent truncation of character strings. Set this option MOREINFO="NONE"on to prohibit silent truncation and enforce ANSI behavior. See BOOK="ref" LINKEND="X64136""set"
in Volume 1, Chapter 1 of the SQL Server Reference Manual for more information.RENDERAS="SECT1"ANSI CompatibilityREMAP="BO--Body"SQL Server Release 10.0 meets SQL89 /FIPS 127-1 and SQL92/FIPS 127-2 standards. In addition to major features such as declarative integrity constraints and cursors, which are described elsewhere in this chapter, the following changes are included in Release 10.0. Since some of ANSI behavior is not compatible with existing SQL Server applications, Transact-SQL providesMOREINFO="NONE" set options that allow you to toggle these behaviors. ID="X16865" RENDERAS="SECT2"set Commands Required for ANSI ComplianceID="IN103155"ANSI compatibilityset options forREMAP="BO--Body"Compliant behavior is enabled by default for all Embedded SQLname='trade' font='symbol' code='228'  charset='fontspecific' precompiler applications. Other applications needing to match ANSI behavior can use option settings in LINKEND="X29248"Table 1-1: set Options for ANSI Compliance
for entry level SQL92 compliance.TOCENTRY="1"LBL="Table 1-1: " ID="X29248"set Options for ANSI ComplianceCOLS="2" CHAROFF="50" CHAR="" ALIGN="LEFT"COLNAME="C1" COLWIDTH="5*"COLNAME="C2" COLWIDTH="2*"VALIGN="BOTTOM"COLNAME="C1" ALIGN="LEFT" ROTATE="0" MOREROWS="0"OptionCOLNAME="C2" ALIGN="LEFT" ROTATE="0" MOREROWS="0"SettingVALIGN="TOP"COLNAME="C1" ALIGN="LEFT" ROTATE="0" MOREROWS="0"MOREINFO="NONE"ansi_permissionsCOLNAME="C2" ALIGN="LEFT" ROTATE="0" MOREROWS="0"MOREINFO="NONE"onCOLNAME="C1" ALIGN="LEFT" ROTATE="0" MOREROWS="0"MOREINFO="NONE"ansinullCOLNAME="C2" ALIGN="LEFT" ROTATE="0" MOREROWS="0"MOREINFO="NONE"onCOLNAME="C1" ALIGN="LEFT" ROTATE="0" MOREROWS="0"MOREINFO="NONE"arithabort COLNAME="C2" ALIGN="LEFT" ROTATE="0" MOREROWS="0"MOREINFO="NONE"offCOLNAME="C1" ALIGN="LEFT" ROTATE="0" MOREROWS="0"MOREINFO="NONE"arithabort numeric_truncationCOLNAME="C2" ALIGN="LEFT" ROTATE="0" MOREROWS="0"MOREINFO="NONE"onCOLNAME="C1" ALIGN="LEFT" ROTATE="0" MOREROWS="0"MOREINFO="NONE"arithignoreCOLNAME="C2" ALIGN="LEFT" ROTATE="0" MOREROWS="0"MOREINFO="NONE"offCOLNAME="C1" ALIGN="LEFT" ROTATE="0" MOREROWS="0"MOREINFO="NONE"chainedCOLNAME="C2" ALIGN="LEFT" ROTATE="0" MOREROWS="0"MOREINFO="NONE"onCOLNAME="C1" ALIGN="LEFT" ROTATE="0" MOREROWS="0"MOREINFO="NONE"close on endtranCOLNAME="C2" ALIGN="LEFT" ROTATE="0" MOREROWS="0"MOREINFO="NONE"onCOLNAME="C1" ALIGN="LEFT" ROTATE="0" MOREROWS="0"MOREINFO="NONE"fipsflaggerCOLNAME="C2" ALIGN="LEFT" ROTATE="0" MOREROWS="0"MOREINFO="NONE"onCOLNAME="C1" ALIGN="LEFT" ROTATE="0" MOREROWS="0"MOREINFO="NONE"quoted_identifierCOLNAME="C2" ALIGN="LEFT" ROTATE="0" MOREROWS="0"MOREINFO="NONE"onCOLNAME="C1" ALIGN="LEFT" ROTATE="0" MOREROWS="0"MOREINFO="NONE"string_rtruncationCOLNAME="C2" ALIGN="LEFT" ROTATE="0" MOREROWS="0"MOREINFO="NONE"onCOLNAME="C1" ALIGN="LEFT" ROTATE="0" MOREROWS="0"MOREINFO="NONE"transaction isolation levelCOLNAME="C2" ALIGN="LEFT" ROTATE="0" MOREROWS="0"MOREINFO="NONE"3REMAP="BO--Body"The following sections describe the differences between standard behavior and the default Transact-SQL behavior. For more information on setting these options, see BOOK="ref" LINKEND="X64136""set"
in Chapter 1 of the SQL Server Reference Manual, Volume 1.RENDERAS="SECT2"FIPS FlaggerREMAP="BO--Body"For customers writing applications that must conform to the ANSI standard, SQL Server provides a MOREINFO="NONE"set fipsflagger option to flag incompatible syntax. When this option is turned on, all commands containing Transact-SQL extensions that are not allowed in entry level SQL92 generate an informational message. See BOOK="ref" LINKEND="X64136""set"
in Volume 1, Chapter 1 of the SQL Server Reference Manual for more information. RENDERAS="SECT2"SQLSTATE Messages and CodesREMAP="BO--Body"By default, SQL Server returns SQLSTATE values to embedded SQL applications, as required by entry level SQL92. SQLSTATE values are included in a new column in sysmessages, when SQLSTATE codes are defined in the standard. Some of the MOREINFO="NONE"set commands listed in LINKEND="X29248"Table 1-1: set Options for ANSI Compliance
cause the associated message text to be delivered to client applications such as MOREINFO="NONE"isql.RENDERAS="SECT2"The escape Clause in the like PredicateREMAP="BO--Body"ID="IN72159"escape clause and like
SQL Server now supports the ANSI MOREINFO="NONE"escape clause, which allows you to specify an escape character in the MOREINFO="NONE"like predicate to search for wildcard characters. This is in addition to Transact-SQL's use of square brackets for the same purpose. See BOOK="ref" LINKEND="X18218""Wildcard Characters"
in Volume 1, Chapter 3 of the SQL Server Reference Manual.ID="IN54160"Comments, ANSI-styleRENDERAS="SECT2"ANSI-Style CommentsID="IN76161"-\- (minus signs)-\ minus signsas comment markercomment markerREMAP="BO--Body"In Transact-SQL, comments are delimited by /* */ pairs, and can be nested. Transact-SQL now also supports ANSI-style comments, which consist of any string beginning with two unspaced minus signs, a comment, and a terminating newline:REMAP="UI--UserInput" FORMAT="LINESPECIFIC"select "hello" -- this is a commentREMAP="BO--Body"This syntax conflicts with the subtraction of a negative number. See LINKEND="X14018"Chapter 2,  "Changes That May Affect Existing Applications"
, for information on how this can affect existing applications. Transact-SQL's /* */ comments are still fully supported, and the "- -" within Transact-SQL comments is not recognized.RENDERAS="SECT2"Changes to set Options arithabort and arithignoreREMAP="BO--Body"Changes were made to the MOREINFO="NONE"set options ID="IN29162"set arithabort
MOREINFO="NONE"arithabort and ID="IN30163"set arithignore
MOREINFO="NONE"arithignore to allow compliance with the SQL92 standard: MARK="BULL" REMAP="BU--Bullet"MOREINFO="NONE"arithabort arith_overflow determines how SQL Server handles arithmetic overflows and divide-by-zero errors during implicit and explicit conversions. Set this optionMOREINFO="NONE" on to roll back the entire transaction or batch in which the error occurs. Set this option MOREINFO="NONE"off to abort the statement that contains the error and continue processing other statements in the transaction or batch.MOREINFO="NONE"arithabort numeric_truncation determines how SQL Server handles loss of scale during implicit conversions to a numeric or decimal type. Set this optionMOREINFO="NONE" on to abort the statement that contains the error and continue processing other statements in the transaction or batch. Set it MOREINFO="NONE"off to truncate the results as necessary and continue processing.MOREINFO="NONE"arithignore arith_overflow determines whether SQL Server ignores arithmetic overflow and divide-by-zero errors. Set this option MOREINFO="NONE"off to print a warning message when these errors occur. Set it MOREINFO="NONE"on to ignore these errors and print no messages.REMAP="BO--Body"If you have used these options in your applications, examine them to ensure that they are still producing the desired behavior.RENDERAS="SECT2"Synonymous KeywordsREMAP="BO--Body"Several keywords have been added for ANSI compatibility that are synonymous with existing Transact-SQL keywords.TOCENTRY="1"LBL="Table 1-2: "ANSI-Compatible Keyword SynonymsCOLS="2" CHAROFF="50" CHAR="" ALIGN="LEFT"COLNAME="C1" COLWIDTH="4*"COLNAME="C2" COLWIDTH="5*"VALIGN="BOTTOM"COLNAME="C1" ALIGN="LEFT" ROTATE="0" MOREROWS="0"Pre-Release 10.0 Transact-SQLCOLNAME="C2" ALIGN="LEFT" ROTATE="0" MOREROWS="0"Additional SyntaxCOLNAME="C1" ALIGN="LEFT" ROTATE="0" MOREROWS="0"COLNAME="C2" ALIGN="LEFT" ROTATE="0" MOREROWS="0"VALIGN="TOP"COLNAME="C1" ALIGN="LEFT" ROTATE="0" MOREROWS="0"MOREINFO="NONE"tranMOREINFO="NONE"transactionCOLNAME="C2" ALIGN="LEFT" ROTATE="0" MOREROWS="0"MOREINFO="NONE"work COLNAME="C1" ALIGN="LEFT" ROTATE="0" MOREROWS="0"MOREINFO="NONE"anyCOLNAME="C2" ALIGN="LEFT" ROTATE="0" MOREROWS="0"MOREINFO="NONE"some COLNAME="C1" ALIGN="LEFT" ROTATE="0" MOREROWS="0"MOREINFO="NONE"grant all COLNAME="C2" ALIGN="LEFT" ROTATE="0" MOREROWS="0"MOREINFO="NONE"grant all privileges COLNAME="C1" ALIGN="LEFT" ROTATE="0" MOREROWS="0"MOREINFO="NONE"revoke all COLNAME="C2" ALIGN="LEFT" ROTATE="0" MOREROWS="0"MOREINFO="NONE"revoke all privileges COLNAME="C1" ALIGN="LEFT" ROTATE="0" MOREROWS="0"MOREINFO="NONE"max (MOREINFO="NONE"expression)COLNAME="C2" ALIGN="LEFT" ROTATE="0" MOREROWS="0"MOREINFO="NONE"max ([MOREINFO="NONE"all |MOREINFO="NONE" distinct])MOREINFO="NONE" expressionCOLNAME="C1" ALIGN="LEFT" ROTATE="0" MOREROWS="0"MOREINFO="NONE"min (MOREINFO="NONE"expression)COLNAME="C2" ALIGN="LEFT" ROTATE="0" MOREROWS="0"MOREINFO="NONE"min ([all | distinct]) MOREINFO="NONE"expressionCOLNAME="C1" ALIGN="LEFT" ROTATE="0" MOREROWS="0"MOREINFO="NONE"user_name built-in functionCOLNAME="C2" ALIGN="LEFT" ROTATE="0" MOREROWS="0"MOREINFO="NONE"user keywordREMAP="BO--Body"The new MOREINFO="NONE"work keyword is synonymous with MOREINFO="NONE"tran andMOREINFO="NONE" transaction only with the MOREINFO="NONE"commit transaction and MOREINFO="NONE"rollback transaction commands, not with the MOREINFO="NONE"begin transaction command.REMAP="BO--Body"See LINKEND="X16228""Datatypes"
of this document for a list of synonymous datatypes.RENDERAS="SECT2"Chained Transactions and Isolation LevelsID="IN18166"TransactionsmodesREMAP="BO--Body"SQL Server now provides ANSI-compliant "chained" transaction behavior as an option. In chained mode, all data retrieval and modification commands (MOREINFO="NONE"delete, MOREINFO="NONE"insert, MOREINFO="NONE"open, MOREINFO="NONE"fetch, MOREINFO="NONE"select, and MOREINFO="NONE"update) implicitly begin a transaction. Since such behavior is incompatible with many Transact-SQL applications, Transact-SQL style (or "unchained") transactions remain the default. Before you change the default transaction mode or isolation level, be sure to read the sections in the Transact-SQL User's Guide and Volume 1 of the SQL Server Reference Manual that describe how these changes can affect existing applications and stored procedures.ID="IN31167"set chained modeREMAP="BO--Body"Chained transaction mode can be initiated with a new option to the MOREINFO="NONE"set command. Another MOREINFO="NONE"set 
option controls transaction isolation levels. See BOOK="ref" LINKEND="X19352""Transactions"
in Volume 1 of the SQL Server Reference Manual for more information.RENDERAS="SECT2"Delimited IdentifiersREMAP="BO--Body"ID="IN40171"Identifiersquoted
SQL Server now supports delimited identifiers for table, view, and column names. Delimited identifiers are object names enclosed within double quotation marks. Using them allows you to avoid certain restrictions on object names.REMAP="BO--Body"Delimited identifiers can begin with non-alphabetic characters, including characters that would not otherwise be allowed, or even be Transact-SQL reserved words. They cannot exceed 28 bytes.REMAP="BO--Body"ID="IN56172"set quoted identifier
Set the new MOREINFO="NONE"quoted_identifier option MOREINFO="NONE"on to allow delimited identifiers. While the option is on, do not use double quotes around character or date strings; use single quotes instead. Delimiting strings with double quotes causes SQL Server to treat them as identifiers.Delimited identifiers cannot be used with some system procedures, cannot be used with MOREINFO="NONE"bcp, and may not be supported by all client software.RENDERAS="SECT2"Treatment of Nulls REMAP="BO--Body"ID="IN13173"NULL operands
ID="IN43174"Equality comparisons
ID="IN19175"Aggregate functions
ID="IN47176"set ansinull
A new MOREINFO="NONE"set option, MOREINFO="NONE"ansinull, determines whether or not evaluation of NULL-valued operands in SQL equality (=) or inequality (!=) comparisons and in aggregate functions is ANSI-compliant. This option does not affect how SQL Server evaluates NULL values in other kinds of SQL statements, such as MOREINFO="NONE"create table.ID="IN27177"Truncationcharacter string
ID="IN27178"Character string truncationRENDERAS="SECT2"Right Truncation of Character StringsREMAP="BO--Body"ID="IN57179"set string_rtruncation
A new MOREINFO="NONE"set option, MOREINFO="NONE"string_rtruncation, controls silent truncation of character strings for ANSI compatibility. Set this option MOREINFO="NONE"on to prohibit silent truncation and enforce ANSI behavior.RENDERAS="SECT2"Permissions Required for update and delete StatementsREMAP="BO--Body"A new MOREINFO="NONE"set option, MOREINFO="NONE"ansi_permissions, determines what permissions are required for MOREINFO="NONE"delete and MOREINFO="NONE"update statements. When this option is MOREINFO="NONE"on, SQL Server uses SQL92's more stringent permissions requirements for these statements. Because this behavior is incompatible with many existing applications, the default setting for this option is MOREINFO="NONE"off. ID="IN22180"Permissionschanges toID="X86249" RENDERAS="SECT1"Enhancements to PermissionsREMAP="BO--Body"Object and command permissions in SQL Server have been enhanced with the following new options.RENDERAS="SECT2"grant with grant optionID="IN101181"grant commandwith grant optionREMAP="BO--Body"A new clause for the MOREINFO="NONE"grantID="IN104182"with grant option to grant command
command, MOREINFO="NONE"with grant option, allows a permission holder to pass MOREINFO="NONE"grant capability, along with the specific permission, to other users, though not to groups, roles, or MOREINFO="NONE"publicID="IN102183"cascade option to revoke command
. A corresponding clause, MOREINFO="NONE"cascade, for the ID="IN99184"revoke commandcascade option
MOREINFO="NONE"revoke command, allows the privilege holder to revoke permissions from all users who obtained it via MOREINFO="NONE"with grant option. See BOOK="ref" LINKEND="X69087""grant"
and BOOK="ref" LINKEND="X29921""revoke"
in Volume 1, Chapter 1 of the SQL Server Reference Manual for syntax information. BOOK="sag" LINKEND="X24272"Chapter 5,  "Managing User Permissions"
, of the System Administration Guide provides a full discussion and examples of this new option.RENDERAS="SECT2"Granting to RolesID="IN59185"revoke commandroles and
ID="IN58186"grant commandroles andREMAP="BO--Body"You can grant and revoke object and command permissions to all users who have been granted a specific role. The roles are MOREINFO="NONE"sa_role, MOREINFO="NONE"sso_role, and MOREINFO="NONE"oper_role. Permissions granted to roles override permissions granted to individuals or groups.ID="IN24187"Packet size, configuringID="X81092" RENDERAS="SECT2"Configurable Packet SizeID="IN47188"sp_configureREMAP="BO--Body"NewMOREINFO="NONE" sp_configure options allow System Administrators to reset the network packet size used by SQL Server. Larger packet sizes can yield performance improvements when transferring large amounts of data in or out of SQL Server. See BOOK="sag" LINKEND="X26541"Chapter 12,  "Fine-Tuning Performance and Operations"
of the System Administration Guide for full information.ID="IN94189"bcp utilityREMAP="BO--Body"New command line options for MOREINFO="NONE"isql and MOREINFO="NONE"bcp allow you to set the packet size for an individual session. See the SQL Server Utility Programs manual for your operating system for information about using these options. Open Clientname='trade' font='symbol' code='228'  charset='fontspecific' Client-Library documentation includes information on using variable packet sizes.ID="IN21190"Chargeback accountingID="X21085" RENDERAS="SECT2"Chargeback AccountingREMAP="BO--Body"Chargeback accounting provides a mechanism for tracking CPU and I/O usage for each server user. This feature was previously available only on VMS.ID="IN28191"sp_clearstatsREMAP="BO--Body"See BOOK="ref" LINKEND="X43748""sp_clearstats"
and BOOK="ref" LINKEND="X30761""sp_reportstats"
in Volume 2, Chapter 1 of the SQL Server Reference Manual for information on getting reports and restarting the accounting interval. See BOOK="ref" LINKEND="X60334""sp_configure"
ID="IN37192"sp_configurecpu flush
for information on the MOREINFO="NONE"cpu flushID="IN37193"sp_configurei/o flush
and MOREINFO="NONE"i/o flush configuration variables that affect chargeback accounting.RENDERAS="SECT2"New dbcc OptionsREMAP="BO--Body"These changes have been made to MOREINFO="NONE"dbcc, the database consistency checker:MARK="BULL" REMAP="BU--Bullet"A MOREINFO="NONE"fixID="IN76194"dbcc checkallocfix option
option has been added to MOREINFO="NONE"dbcc checkalloc, so this command can now fix certain allocation errors.A MOREINFO="NONE"skip_ncindexID="IN67195"dbcc checkdbskip_ncindex option
option has been added to MOREINFO="NONE"dbcc checkdbID="IN70196"dbcc checktableskip_ncindex option
and MOREINFO="NONE"dbcc checktable. When you use this option, MOREINFO="NONE"dbcc skips checking the nonclustered indexes. Depending on the number of indexes on your table or in your database, this can speed up the performance of the command by up to 40 percent.RENDERAS="SECT2"ID="IN55197"kill command changes
kill Command EnhancementsREMAP="BO--Body"The MOREINFO="NONE"kill command, used to terminate SQL Server sessions, can now terminate more ID="IN49198"sp_whochanges
kinds of sessions. In previous releases, some of these sessions could not be terminated (except by rebooting SQL Server), or would only actually terminate when the processes stopped sleeping. Now, these sessions can be terminated immediately. The MOREINFO="NONE"sp_who system procedure now displays different status values for these sessions. For more information, see BOOK="ref" LINKEND="X67442""kill"
in Volume 1, Chapter 1 of the SQL Server Reference Manual or BOOK="sag" LINKEND="X57130"Chapter 11,  "Diagnosing System Problems"
of the System Administration Guide.RENDERAS="SECT2"shutdownID="IN59199"shutdown command changes
ID="IN35200"Backup Server utilityshutting down
Command EnhancementsREMAP="BO--Body"The MOREINFO="NONE"shutdown command, used to shutdown a SQL Server, can now be used to shut down a Backup Server. The Backup Server must be listed in your sysservers table and in the interfaces file for the SQL Server from which you execute the MOREINFO="NONE"shutdown command.REMAP="BO--Body"Unless you use the MOREINFO="NONE"nowait option, MOREINFO="NONE"shutdown waits for active dumps and loads to complete. Once you issue a MOREINFO="NONE"shutdown command to a Backup Server, no new dumps or loads to the Backup Server can start.RENDERAS="SECT2"tempdb ChangesREMAP="BO--Body"By default, all users now have MOREINFO="NONE"create
table permission in tempdb. See BOOK="ref" LINKEND="X86056""Temporary Tables"
in Volume 1, Chapter 3 of the SQL Server Reference Manual for more information.ID="IN15201"Space availableRENDERAS="SECT2"Additional Information on Space UsageREMAP="BO--Body"System procedures now display additional information about space available in databases:MARK="BULL" REMAP="BU--Bullet"ID="IN24202"sp_helpdb
MOREINFO="NONE"sp_helpdb MOREINFO="NONE"database_name displays information about how much space is left on each disk piece assigned to the databaseID="IN29203"sp_helpsegment
MOREINFO="NONE"sp_helpsegment MOREINFO="NONE"segment_name displays information about the amount of free space left on the segmentID="X83836" RENDERAS="SECT1"Error Handling REMAP="BO--Body"ID="IN14204"Error handling
ID="IN24205"raiserror
MOREINFO="NONE"raiserror can now return the name of the tables and columns causing an error, along with the error number and text. See BOOK="ref" LINKEND="X60573""raiserror"
in Volume 1 of the SQL Server Reference Manual.RENDERAS="SECT1"create index ID="IN47206"create index
Performance EnhancementsREMAP="BO--Body"Internal improvements have enhanced the speed of the MOREINFO="NONE"create index command. In addition, you can increase the speed of creating indexes with a new MOREINFO="NONE"sp_configure option. ID="IN18207"Index enhancementsREMAP="BO--Body"When SQL Server creates an index, it reads and writes pages to disk for intermediate sort results and writes out the final index pages one page at a time. With Release 10.0, a System Administrator can allocate buffers so that MOREINFO="NONE"create index can perform these reads and writes an extent at a time, thereby making more efficient use of the disk and increasing the speed of the MOREINFO="NONE"create index command. If you wish to use this option, there are two important considerations:MARK="BULL" REMAP="BU--Bullet"The buffers are added to the memory allocated by SQL Server. Setting the value too large can make it impossible for SQL Server to start if it cannot acquire enough memory.The first user who starts a MOREINFO="NONE"create index command acquires all of the allocated buffers; all subsequent MOREINFO="NONE"create index commands that are started before the first one completes will use regular page-at-a-time disk I/O. For best performance, you should create indexes when other users are not likely to contend for the resource, or coordinate the creation of large indexes among table owners.ID="IN66208"sp_configureextent i/o buffersREMAP="BO--Body"See BOOK="sag" LINKEND="X26541"Chapter 12,  "Fine-Tuning Performance and Operations"
for information about the new MOREINFO="NONE"sp_configure option, MOREINFO="NONE"extent i/o buffers. The section contains suggestions for choosing the correct buffer size.ID="IN33209"Query optimizerRENDERAS="SECT1"Improvements to the Query OptimizerREMAP="BO--Body"Improvements to the query optimizer have increased SQL Server's accuracy in evaluating the cost of using indexes. You must run MOREINFO="NONE"update statisticsID="IN57210"update statistics
on your indexes in order to take advantage of these changes, although any previously created indexes continue to work.REMAP="BO--Body"This change involves the way that the query optimizer assesses the cost of using indexes with compound keys on the inner table of a join. Prior to Release 10.0, the optimizer computed a value for the entire key, and would often underestimate the cost of using an index if the query used only one or a few of the columns. In Release 10.0, SQL Server maintains statistics for each prefix of columns in the index, that is, for the first column, the first and second columns, the first, second, and third columns, and so on.REMAP="BO--Body"The statistics used by the optimizer are generated by the MOREINFO="NONE"create index command, and are updated by the MOREINFO="NONE"update statistics command. Indexes created under earlier versions of SQL Server still work in Release 10.0, but in order to take advantage of the new behavior described here, you must run the MOREINFO="NONE"update statistics command on the table or index.ID="IN9211"Bulk copyID="X93313" RENDERAS="SECT1"Bulk Copy Performance EnhancementsID="IN24212"Packet size, configuringREMAP="BO--Body"Internal improvements, transparent to users, result in faster bulk copy speeds. In addition, configurable packet sizes can increase bulk copy throughput. See BOOK="unix" LINKEND="X12769""bcp"
in the SQL Server Utility Programs manual and BOOK="sag" LINKEND="X26541"Chapter 12,  "Fine-Tuning Performance and Operations"
, in the System Administration Guide for information about configuring packet size.ID="IN18213"Spanish sort order
ID="IN18214"Collating sequence
ID="IN10215"Sort orderRENDERAS="SECT1"Spanish Collating OrdersREMAP="BO--Body"Release 10.0 supports these Spanish dictionary sort orders:MARK="BULL" REMAP="BU--Bullet"Case sensitiveCase insensitiveCase and accent insensitiveREMAP="BO--Body"To change the sort order on an existing SQL Server to use a Spanish collating sequence, follow the directions in BOOK="sag" LINKEND="X60641"Chapter 17,  "Language, Sort Order, and Character Set Issues"
in the System Administration Guide. Instructions for installing a new SQL Server using a Spanish collating sequence are in your SYBASE SQL Server Installation Guide.ID="X81900" RENDERAS="SECT1"Document ReorganizationREMAP="BO--Body"The SQL Server documentation set has been reorganized for this release. MARK="BULL" REMAP="BU--Bullet"The Commands Reference Manual has been split into two volumes:MARK="DASH" REMAP="BS--BulletSub"Transact-SQL commands, functions, and special topics (such as Locking and Transactions) are now documented in Volume 1 of the SYBASE SQL Server Reference Manual. The SYBASE system procedures and catalog stored procedures are now documented in Volume 2 of the SYBASE SQL Server Reference Manual.The SYBASE SQL Server Utility Programs now documents the utility programs for your operating system.The Transact-SQL User's Guide has been reorganized, with the first part tailored to new SQL users and the more advanced and complex concepts presented in the second part.RENDERAS="SECT1"Summary by Command Name, Type, Tables AffectedRENDERAS="SECT2"setID="IN55216"set options overview
OptionsREMAP="BO--Body"The following table summarizes new and changed MOREINFO="NONE"set options:TOCENTRY="1"LBL="Table 1-3: "New set OptionsCOLS="2" CHAROFF="50" CHAR="" ALIGN="LEFT"COLNAME="C1" COLWIDTH="9*"COLNAME="C2" COLWIDTH="11*"VALIGN="BOTTOM"COLNAME="C1" ALIGN="LEFT" ROTATE="0" MOREROWS="0"OptionCOLNAME="C2" ALIGN="LEFT" ROTATE="0" MOREROWS="0"UseVALIGN="TOP"COLNAME="C1" ALIGN="LEFT" ROTATE="0" MOREROWS="0"MOREINFO="NONE"set ansinull {on | off}COLNAME="C2" ALIGN="LEFT" ROTATE="0" MOREROWS="0"Toggles ANSI treatment of NULL-valued 
operands in equality (=) and inequality (!=) 
comparisons. When MOREINFO="NONE"on, treatment is ANSI 
`89 compliant.COLNAME="C1" ALIGN="LEFT" ROTATE="0" MOREROWS="0"MOREINFO="NONE"set ansi_permissions {on | off} COLNAME="C2" ALIGN="LEFT" ROTATE="0" MOREROWS="0"Determines whether ANSI-compliant 
permissions requirements for MOREINFO="NONE"update and 
MOREINFO="NONE"delete statements are checked.COLNAME="C1" ALIGN="LEFT" ROTATE="0" MOREROWS="0"MOREINFO="NONE"set arithabort [arith_overflow | MOREINFO="NONE"numeric_truncation] {on | off}COLNAME="C2" ALIGN="LEFT" ROTATE="0" MOREROWS="0"Determines whether SQL Server aborts a 
query when an arithmetic overflow or 
numeric truncation error occurs.COLNAME="C1" ALIGN="LEFT" ROTATE="0" MOREROWS="0"MOREINFO="NONE"set arithignore [arith_overflow] 
{on | off}COLNAME="C2" ALIGN="LEFT" ROTATE="0" MOREROWS="0"Determines whether SQL Server displays a 
warning message after any query that 
results in arithmetic overflow.COLNAME="C1" ALIGN="LEFT" ROTATE="0" MOREROWS="0"MOREINFO="NONE"set chained {on | off} COLNAME="C2" ALIGN="LEFT" ROTATE="0" MOREROWS="0"Toggles the chained transaction mode in 
SQL Server.COLNAME="C1" ALIGN="LEFT" ROTATE="0" MOREROWS="0"MOREINFO="NONE"set close on endtran {on | off} COLNAME="C2" ALIGN="LEFT" ROTATE="0" MOREROWS="0"Forces all open cursors to be closed when 
the transaction ends (MOREINFO="NONE"on) or to remain open 
across transactions (MOREINFO="NONE"off).COLNAME="C1" ALIGN="LEFT" ROTATE="0" MOREROWS="0"MOREINFO="NONE"set cursor rows numberMOREINFO="NONE"for cursor_nameCOLNAME="C2" ALIGN="LEFT" ROTATE="0" MOREROWS="0"Sets the number of cursor rows returned to 
the host on each MOREINFO="NONE"fetch for the specified 
client cursor. The default is 1.COLNAME="C1" ALIGN="LEFT" ROTATE="0" MOREROWS="0"MOREINFO="NONE"set dup_in_subquery {on | off} COLNAME="C2" ALIGN="LEFT" ROTATE="0" MOREROWS="0"Controls whether a subquery using the MOREINFO="NONE"in 
clause returns duplicate values. The 
default is MOREINFO="NONE"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.COLNAME="C1" ALIGN="LEFT" ROTATE="0" MOREROWS="0"MOREINFO="NONE"set fipsflagger {on | off} COLNAME="C2" ALIGN="LEFT" ROTATE="0" MOREROWS="0"Toggles the FIPS flagger. When MOREINFO="NONE"on, the 
option prints warning messages for the 
Transact-SQL extensions to ANSI 89 SQL. COLNAME="C1" ALIGN="LEFT" ROTATE="0" MOREROWS="0"MOREINFO="NONE"set identity_insert table_nameMOREINFO="NONE" {on | off} COLNAME="C2" ALIGN="LEFT" ROTATE="0" MOREROWS="0"Determines whether a user can explicitly 
insert a value into table_name's IDENTITY 
column. Users can turn this option MOREINFO="NONE"on for 
only one table at a time per database.COLNAME="C1" ALIGN="LEFT" ROTATE="0" MOREROWS="0"MOREINFO="NONE"set quoted_identifier {on | off} COLNAME="C2" ALIGN="LEFT" ROTATE="0" MOREROWS="0"Determines whether SQL Server treats 
strings enclosed in double quotes (") as 
identifiers.COLNAME="C1" ALIGN="LEFT" ROTATE="0" MOREROWS="0"MOREINFO="NONE"set role {"sa_role" |"sso_role"MOREINFO="NONE"| "oper_role"} {on | off}COLNAME="C2" ALIGN="LEFT" ROTATE="0" MOREROWS="0"Toggles the specified role during the 
current SQL Server session.COLNAME="C1" ALIGN="LEFT" ROTATE="0" MOREROWS="0"MOREINFO="NONE"set self_recursion {on | off} COLNAME="C2" ALIGN="LEFT" ROTATE="0" MOREROWS="0"Switches trigger self-recursion on or off, so 
that triggers that modify data cause the 
trigger to fire again.COLNAME="C1" ALIGN="LEFT" ROTATE="0" MOREROWS="0"MOREINFO="NONE"set string_rtruncation {on | off} COLNAME="C2" ALIGN="LEFT" ROTATE="0" MOREROWS="0"Determines if silent truncation of character 
strings is allowed. When MOREINFO="NONE"on, treatment is 
ANSI `89 compliantCOLNAME="C1" ALIGN="LEFT" ROTATE="0" MOREROWS="0"MOREINFO="NONE"set transaction isolation level 
{1|3}COLNAME="C2" ALIGN="LEFT" ROTATE="0" MOREROWS="0"Sets the transaction isolation level. An 
isolation level of 3 is equivalent of doing 
MOREINFO="NONE"select with holdlock.REMAP="BO--Body"ID="IN47217"set showplan
MOREINFO="NONE"set showplan now prints full command names instead of abbreviations.ID="IN27218"Built-in functionsoverview
ID="IN18219"FunctionsoverviewID="X91059" RENDERAS="SECT2"New and Changed Built-In FunctionsREMAP="BO--Body"The following table lists new and changed built-in functions:TOCENTRY="1"LBL="Table 1-4: "New and Changed Built-In FunctionsCOLS="2" CHAROFF="50" CHAR="" ALIGN="LEFT"COLNAME="C1" COLWIDTH="7*"COLNAME="C2" COLWIDTH="33*"VALIGN="BOTTOM"COLNAME="C1" ALIGN="LEFT" ROTATE="0" MOREROWS="0"NameCOLNAME="C2" ALIGN="LEFT" ROTATE="0" MOREROWS="0"FunctionVALIGN="TOP"COLNAME="C1" ALIGN="LEFT" ROTATE="0" MOREROWS="0"MOREINFO="NONE"col_nameCOLNAME="C2" ALIGN="LEFT" ROTATE="0" MOREROWS="0"Changed to accept a database name as an optional third 
parameter.COLNAME="C1" ALIGN="LEFT" ROTATE="0" MOREROWS="0"MOREINFO="NONE"inttohexCOLNAME="C2" ALIGN="LEFT" ROTATE="0" MOREROWS="0"New datatype conversion function. Returns the platform-
independent hexadecimal equivalent of an integer.COLNAME="C1" ALIGN="LEFT" ROTATE="0" MOREROWS="0"MOREINFO="NONE"hextointCOLNAME="C2" ALIGN="LEFT" ROTATE="0" MOREROWS="0"New datatype conversion function. Returns the platform-
independent integer equivalent of a hexadecimal string.COLNAME="C1" ALIGN="LEFT" ROTATE="0" MOREROWS="0"MOREINFO="NONE"index_colCOLNAME="C2" ALIGN="LEFT" ROTATE="0" MOREROWS="0"Changed to accept a user name as an optional fourth 
parameter.COLNAME="C1" ALIGN="LEFT" ROTATE="0" MOREROWS="0"MOREINFO="NONE"lct_adminCOLNAME="C2" ALIGN="LEFT" ROTATE="0" MOREROWS="0"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.COLNAME="C1" ALIGN="LEFT" ROTATE="0" MOREROWS="0"MOREINFO="NONE"object_nameCOLNAME="C2" ALIGN="LEFT" ROTATE="0" MOREROWS="0"Changed to accept a database name as an optional second 
parameter.COLNAME="C1" ALIGN="LEFT" ROTATE="0" MOREROWS="0"MOREINFO="NONE"proc_roleCOLNAME="C2" ALIGN="LEFT" ROTATE="0" MOREROWS="0"New system function. Checks to see if the user possesses 
the specified role. Useful for restricting execution of 
procedures.COLNAME="C1" ALIGN="LEFT" ROTATE="0" MOREROWS="0"MOREINFO="NONE"show_roleCOLNAME="C2" ALIGN="LEFT" ROTATE="0" MOREROWS="0"New system function. Shows the user's current, active 
roles.COLNAME="C1" ALIGN="LEFT" ROTATE="0" MOREROWS="0"MOREINFO="NONE"userCOLNAME="C2" ALIGN="LEFT" ROTATE="0" MOREROWS="0"New ANSI compatible system function returns the user 
name.REMAP="BO--Body"In addition, many of the mathematical functions have been changed to accept the new numeric datatypes, as appropriate.REMAP="BO--Body"See Volume 1, Chapter 2 of the SYBASE SQL Server Reference Manual for information on these functions.ID="X86658" RENDERAS="SECT2"New System ProceduresREMAP="BO--Body"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:TOCENTRY="1"LBL="Table 1-5: "New System ProceduresCOLS="2" CHAROFF="50" CHAR="" ALIGN="LEFT"COLNAME="C1" COLWIDTH="11*"COLNAME="C2" COLWIDTH="29*"VALIGN="BOTTOM"COLNAME="C1" ALIGN="LEFT" ROTATE="0" MOREROWS="0"NameCOLNAME="C2" ALIGN="LEFT" ROTATE="0" MOREROWS="0"FunctionVALIGN="TOP"COLNAME="C1" ALIGN="LEFT" ROTATE="0" MOREROWS="0"MOREINFO="NONE"sp_addauditrecordCOLNAME="C2" ALIGN="LEFT" ROTATE="0" MOREROWS="0"Allows users to enter user-defined audit records 
(comments) into the audit trail.COLNAME="C1" ALIGN="LEFT" ROTATE="0" MOREROWS="0"MOREINFO="NONE"sp_addthresholdCOLNAME="C2" ALIGN="LEFT" ROTATE="0" MOREROWS="0"Creates a free-space threshold to monitor space 
remaining on a database segment.COLNAME="C1" ALIGN="LEFT" ROTATE="0" MOREROWS="0"MOREINFO="NONE"sp_auditdatabaseCOLNAME="C2" ALIGN="LEFT" ROTATE="0" MOREROWS="0"Establishes auditing of different types of events within a 
database, or of references to objects within that database 
from another database.COLNAME="C1" ALIGN="LEFT" ROTATE="0" MOREROWS="0"MOREINFO="NONE"sp_auditloginCOLNAME="C2" ALIGN="LEFT" ROTATE="0" MOREROWS="0"Audits a user's attempts to access tables and views, 
and/or the text of a user's commands.COLNAME="C1" ALIGN="LEFT" ROTATE="0" MOREROWS="0"MOREINFO="NONE"sp_auditobjectCOLNAME="C2" ALIGN="LEFT" ROTATE="0" MOREROWS="0"Establishes auditing of accesses to tables and views.COLNAME="C1" ALIGN="LEFT" ROTATE="0" MOREROWS="0"MOREINFO="NONE"sp_auditoptionCOLNAME="C2" ALIGN="LEFT" ROTATE="0" MOREROWS="0"Enables and disables system-wide auditing and global 
audit options.COLNAME="C1" ALIGN="LEFT" ROTATE="0" MOREROWS="0"MOREINFO="NONE"sp_auditsproc COLNAME="C2" ALIGN="LEFT" ROTATE="0" MOREROWS="0"Audits the execution of stored procedures and triggers.COLNAME="C1" ALIGN="LEFT" ROTATE="0" MOREROWS="0"MOREINFO="NONE"sp_bindmsgCOLNAME="C2" ALIGN="LEFT" ROTATE="0" MOREROWS="0"Binds a user message to an integrity constraint.COLNAME="C1" ALIGN="LEFT" ROTATE="0" MOREROWS="0"MOREINFO="NONE"sp_checkreswordsCOLNAME="C2" ALIGN="LEFT" ROTATE="0" MOREROWS="0"Checks for reserved words used as identifiers. The 
procedure is run as part of pre-upgrade.COLNAME="C1" ALIGN="LEFT" ROTATE="0" MOREROWS="0"MOREINFO="NONE"sp_configureloginCOLNAME="C2" ALIGN="LEFT" ROTATE="0" MOREROWS="0"Initializes the security-relevant information for a new 
Secure SQL Server login.COLNAME="C1" ALIGN="LEFT" ROTATE="0" MOREROWS="0"MOREINFO="NONE"sp_cursorinfoCOLNAME="C2" ALIGN="LEFT" ROTATE="0" MOREROWS="0"Reports information about a specific cursor or all cursors 
which are active.COLNAME="C1" ALIGN="LEFT" ROTATE="0" MOREROWS="0"MOREINFO="NONE"sp_dbremapCOLNAME="C2" ALIGN="LEFT" ROTATE="0" MOREROWS="0"Forces changes made by MOREINFO="NONE"alter database to be recognized 
by SQL Server. Run this procedure only if instructed by 
SQL Server messages.COLNAME="C1" ALIGN="LEFT" ROTATE="0" MOREROWS="0"MOREINFO="NONE"sp_displayloginCOLNAME="C2" ALIGN="LEFT" ROTATE="0" MOREROWS="0"Displays information about a login account.COLNAME="C1" ALIGN="LEFT" ROTATE="0" MOREROWS="0"MOREINFO="NONE"sp_dropthresholdCOLNAME="C2" ALIGN="LEFT" ROTATE="0" MOREROWS="0"Removes a free-space threshold from a segment.COLNAME="C1" ALIGN="LEFT" ROTATE="0" MOREROWS="0"MOREINFO="NONE"sp_estspaceCOLNAME="C2" ALIGN="LEFT" ROTATE="0" MOREROWS="0"Estimates the amount of space needed for a table and its 
indexes.COLNAME="C1" ALIGN="LEFT" ROTATE="0" MOREROWS="0"MOREINFO="NONE"sp_helpconstraintCOLNAME="C2" ALIGN="LEFT" ROTATE="0" MOREROWS="0"Reports information about any integrity constraints 
specified for a table.COLNAME="C1" ALIGN="LEFT" ROTATE="0" MOREROWS="0"MOREINFO="NONE"sp_helpthresholdCOLNAME="C2" ALIGN="LEFT" ROTATE="0" MOREROWS="0"Reports information on all thresholds in the current 
database or all thresholds for a particular segment.COLNAME="C1" ALIGN="LEFT" ROTATE="0" MOREROWS="0"MOREINFO="NONE"sp_lockloginCOLNAME="C2" ALIGN="LEFT" ROTATE="0" MOREROWS="0"Prevents a user from logging in, or displays a list of all 
locked accounts.COLNAME="C1" ALIGN="LEFT" ROTATE="0" MOREROWS="0"MOREINFO="NONE"sp_modifyloginCOLNAME="C2" ALIGN="LEFT" ROTATE="0" MOREROWS="0"Modifies default database, default language, and full 
name information for a SQL Server login account.COLNAME="C1" ALIGN="LEFT" ROTATE="0" MOREROWS="0"MOREINFO="NONE"sp_modifythresholdCOLNAME="C2" ALIGN="LEFT" ROTATE="0" MOREROWS="0"Changes parameters for existing thresholds in a 
database.COLNAME="C1" ALIGN="LEFT" ROTATE="0" MOREROWS="0"MOREINFO="NONE"sp_procxmodeCOLNAME="C2" ALIGN="LEFT" ROTATE="0" MOREROWS="0"Displays or changes the transaction modes associated 
with stored procedures.COLNAME="C1" ALIGN="LEFT" ROTATE="0" MOREROWS="0"MOREINFO="NONE"sp_remapCOLNAME="C2" ALIGN="LEFT" ROTATE="0" MOREROWS="0"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 MOREINFO="NONE"upgrade 
procedure failed to remap.COLNAME="C1" ALIGN="LEFT" ROTATE="0" MOREROWS="0"MOREINFO="NONE"sp_roleCOLNAME="C2" ALIGN="LEFT" ROTATE="0" MOREROWS="0"Grants or revokes roles to a SQL Server login account.COLNAME="C1" ALIGN="LEFT" ROTATE="0" MOREROWS="0"MOREINFO="NONE"sp_thresholdactionCOLNAME="C2" ALIGN="LEFT" ROTATE="0" MOREROWS="0"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.COLNAME="C1" ALIGN="LEFT" ROTATE="0" MOREROWS="0"MOREINFO="NONE"sp_unbindmsgCOLNAME="C2" ALIGN="LEFT" ROTATE="0" MOREROWS="0"Unbinds a user-defined message from a constraint.COLNAME="C1" ALIGN="LEFT" ROTATE="0" MOREROWS="0"MOREINFO="NONE"sp_volchangedCOLNAME="C2" ALIGN="LEFT" ROTATE="0" MOREROWS="0"Notifies SQL Server that the operator has performed the 
requested volume handling during a MOREINFO="NONE"dump or MOREINFO="NONE"load.REMAP="BO--Body"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.MOREINFO="NONE"TOCENTRY="1"LBL="Table 1-6: "Chargeback Accounting System ProceduresCOLS="2" CHAROFF="50" CHAR="" ALIGN="LEFT"COLNAME="C1" COLWIDTH="11*"COLNAME="C2" COLWIDTH="29*"VALIGN="BOTTOM"COLNAME="C1" ALIGN="LEFT" ROTATE="0" MOREROWS="0"NameCOLNAME="C2" ALIGN="LEFT" ROTATE="0" MOREROWS="0"FunctionVALIGN="TOP"COLNAME="C1" ALIGN="LEFT" ROTATE="0" MOREROWS="0"MOREINFO="NONE"sp_clearstatsCOLNAME="C2" ALIGN="LEFT" ROTATE="0" MOREROWS="0"Initiates a new accounting period for all server users, or 
a specified user. Prints out statistics for the previous 
period by executing MOREINFO="NONE"sp_reportstats. COLNAME="C1" ALIGN="LEFT" ROTATE="0" MOREROWS="0"MOREINFO="NONE"sp_reportstatsCOLNAME="C2" ALIGN="LEFT" ROTATE="0" MOREROWS="0"Reports statistics on system usage. REMAP="BO--Body"See Volume 2 of the SQL Server Reference Manual
for information on these procedures.RENDERAS="SECT2"Changes to System ProceduresREMAP="BO--Body"The following system procedures have been changed in this release:TOCENTRY="1"LBL="Table 1-7: "Changes to System ProceduresCOLS="2" CHAROFF="50" CHAR="" ALIGN="LEFT"COLNAME="C1" COLWIDTH="11*"COLNAME="C2" COLWIDTH="29*"VALIGN="BOTTOM"COLNAME="C1" ALIGN="LEFT" ROTATE="0" MOREROWS="0"NameCOLNAME="C2" ALIGN="LEFT" ROTATE="0" MOREROWS="0"ChangesVALIGN="TOP"COLNAME="C1" ALIGN="LEFT" ROTATE="0" MOREROWS="0"MOREINFO="NONE"sp_addsegment,sp_dropsegment,sp_extendsegmentCOLNAME="C2" ALIGN="LEFT" ROTATE="0" MOREROWS="0"These procedures now require a database name as the 
second argument. This change prevents users from 
accidentally affecting segments in the wrong databases.COLNAME="C1" ALIGN="LEFT" ROTATE="0" MOREROWS="0"MOREINFO="NONE"sp_defaultdbsp_defaultlanguageCOLNAME="C2" ALIGN="LEFT" ROTATE="0" MOREROWS="0"These procedures have been superseded by 
MOREINFO="NONE"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 MOREINFO="NONE"sp_modifylogin instead.COLNAME="C1" ALIGN="LEFT" ROTATE="0" MOREROWS="0"MOREINFO="NONE"sp_helpdbCOLNAME="C2" ALIGN="LEFT" ROTATE="0" MOREROWS="0"Now displays information about how much space is left 
on each disk piece assigned to the database.COLNAME="C1" ALIGN="LEFT" ROTATE="0" MOREROWS="0"MOREINFO="NONE"sp_helpsegmentCOLNAME="C2" ALIGN="LEFT" ROTATE="0" MOREROWS="0"Now displays information about the amount of free 
space left on the segmentCOLNAME="C1" ALIGN="LEFT" ROTATE="0" MOREROWS="0"MOREINFO="NONE"sp_lockCOLNAME="C2" ALIGN="LEFT" ROTATE="0" MOREROWS="0"Now displays information whether a lock is associated 
with a cursor.COLNAME="C1" ALIGN="LEFT" ROTATE="0" MOREROWS="0"MOREINFO="NONE"sp_whoCOLNAME="C2" ALIGN="LEFT" ROTATE="0" MOREROWS="0"Displays different status values for sleeping processes: 
recv sleep, send sleep, lock sleep and alarm sleep.ID="IN101220"Variables, configuration. See sp_configure
ID="IN100221"Configuration variables. See sp_configureID="X69633" RENDERAS="SECT2"New Configuration VariablesREMAP="BO--Body"There are new configuration variables for use with MOREINFO="NONE"sp_configure:TOCENTRY="1"LBL="Table 1-8: "New Configuration OptionsCOLS="2" CHAROFF="50" CHAR="" ALIGN="LEFT"COLNAME="C1" COLWIDTH="11*"COLNAME="C2" COLWIDTH="29*"VALIGN="BOTTOM"COLNAME="C1" ALIGN="LEFT" ROTATE="0" MOREROWS="0"OptionCOLNAME="C2" ALIGN="LEFT" ROTATE="0" MOREROWS="0"FunctionVALIGN="TOP"COLNAME="C1" ALIGN="LEFT" ROTATE="0" MOREROWS="0"MOREINFO="NONE"additional netmemCOLNAME="C2" ALIGN="LEFT" ROTATE="0" MOREROWS="0"Memory added for use with variable packet sizesCOLNAME="C1" ALIGN="LEFT" ROTATE="0" MOREROWS="0"MOREINFO="NONE"audit queue sizeCOLNAME="C2" ALIGN="LEFT" ROTATE="0" MOREROWS="0"Number of audit records in the audit queueCOLNAME="C1" ALIGN="LEFT" ROTATE="0" MOREROWS="0"MOREINFO="NONE"cpu flushCOLNAME="C2" ALIGN="LEFT" ROTATE="0" MOREROWS="0"Used to configure chargeback accounting (new on non-
OpenVMS platforms).COLNAME="C1" ALIGN="LEFT" ROTATE="0" MOREROWS="0"MOREINFO="NONE"default network 
packet sizeCOLNAME="C2" ALIGN="LEFT" ROTATE="0" MOREROWS="0"Default packet size.COLNAME="C1" ALIGN="LEFT" ROTATE="0" MOREROWS="0"MOREINFO="NONE"extent i/o buffersCOLNAME="C2" ALIGN="LEFT" ROTATE="0" MOREROWS="0"Allocates additional memory to be used for buffering 
data pages during MOREINFO="NONE"create index and MOREINFO="NONE"order by read/writes 
to disk.COLNAME="C1" ALIGN="LEFT" ROTATE="0" MOREROWS="0"MOREINFO="NONE"identity burning set 
factorCOLNAME="C2" ALIGN="LEFT" ROTATE="0" MOREROWS="0"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.COLNAME="C1" ALIGN="LEFT" ROTATE="0" MOREROWS="0"MOREINFO="NONE"i/o flushCOLNAME="C2" ALIGN="LEFT" ROTATE="0" MOREROWS="0"Used to configure chargeback accounting (new on non-
OpenVMS platforms).COLNAME="C1" ALIGN="LEFT" ROTATE="0" MOREROWS="0"MOREINFO="NONE"maximum network 
packet sizeCOLNAME="C2" ALIGN="LEFT" ROTATE="0" MOREROWS="0"Maximum allowable packet size.COLNAME="C1" ALIGN="LEFT" ROTATE="0" MOREROWS="0"MOREINFO="NONE"password 
expiration intervalCOLNAME="C2" ALIGN="LEFT" ROTATE="0" MOREROWS="0"Sets password expiration time.REMAP="BO--Body"The serial number field, configuration value 114, has been removed.RENDERAS="SECT2"New Database OptionsREMAP="BO--Body"There are new database options:TOCENTRY="1"LBL="Table 1-9: "New sp_dboption Database OptionsCOLS="2" CHAROFF="50" CHAR="" ALIGN="LEFT"COLNAME="C1" COLWIDTH="11*"COLNAME="C2" COLWIDTH="29*"VALIGN="BOTTOM"COLNAME="C1" ALIGN="LEFT" ROTATE="0" MOREROWS="0"OptionCOLNAME="C2" ALIGN="LEFT" ROTATE="0" MOREROWS="0"FunctionVALIGN="TOP"COLNAME="C1" ALIGN="LEFT" ROTATE="0" MOREROWS="0"MOREINFO="NONE"abort tran on log 
fullCOLNAME="C2" ALIGN="LEFT" ROTATE="0" MOREROWS="0"Determines whether user processes are suspended (the 
default) or aborted when the last-chance threshold on a 
database's log segment is reached.COLNAME="C1" ALIGN="LEFT" ROTATE="0" MOREROWS="0"MOREINFO="NONE"allow nulls by 
defaultCOLNAME="C2" ALIGN="LEFT" ROTATE="0" MOREROWS="0"Changes the default null type for MOREINFO="NONE"create table statements 
from MOREINFO="NONE"not null (the Transact-SQL default) MOREINFO="NONE"to null (the ANSI 
default.)COLNAME="C1" ALIGN="LEFT" ROTATE="0" MOREROWS="0"MOREINFO="NONE"auto identityCOLNAME="C2" ALIGN="LEFT" ROTATE="0" MOREROWS="0"Automatically defines a 10-digit IDENTITY column, 
MOREINFO="NONE"syb_identity_col, in each new table that does not specify 
a MOREINFO="NONE"primary key, a MOREINFO="NONE"unique index, or an IDENTITY column. 
The column is not visible with a MOREINFO="NONE"select * statement; you 
must include the column name in the select list to 
retrieve it.COLNAME="C1" ALIGN="LEFT" ROTATE="0" MOREROWS="0"MOREINFO="NONE"ddl in tranCOLNAME="C2" ALIGN="LEFT" ROTATE="0" MOREROWS="0"Allows data definition language in user transactions. 
The allowed commands are all MOREINFO="NONE"create and MOREINFO="NONE"drop 
commands, except MOREINFO="NONE"create/drop database, plus MOREINFO="NONE"grant and 
MOREINFO="NONE"revoke commands.COLNAME="C1" ALIGN="LEFT" ROTATE="0" MOREROWS="0"MOREINFO="NONE"no free space acctgCOLNAME="C2" ALIGN="LEFT" ROTATE="0" MOREROWS="0"Suppresses free space accounting and the execution of 
threshold actions on non-log segments of a database. REMAP="BO--Body"You can change these options with MOREINFO="NONE"sp_dboption.REMAP="BO--Body"Also, the MOREINFO="NONE"trunc. log on chkpt. option can now be used with or without the periods, that is, MOREINFO="NONE"trunc log on chkpt is also supported.ID="IN13223"System tablesID="X69513" RENDERAS="SECT2"New System TablesREMAP="BO--Body"There are four new system tables in all databases:MARK="BULL" REMAP="BU--Bullet"sysconstraints sysreferences sysroles systhresholds REMAP="BO--Body"The master database includes the following new system tables:MARK="BULL" REMAP="BU--Bullet"syslistenerssysloginrolessyssrvrolesID="IN22224"Auditingsystem tables
ID="IN54225"sybsecurity databaseREMAP="BO--Body"If you install auditing, the sybsecurity ID="IN56226"sysaudits system table
database is automatically created, with two tables: sysauditsID="IN62227"sysauditoptions system table
and sysauditoptions (as well as the default system tables for all user databases).REMAP="BO--Body"These are fully documented in Appendix B, "The System Tables", included in both the System Administration Guide and the SQL Server Reference Manual.ID="IN13228"System tablesID="X86552" RENDERAS="SECT2"Changes to Existing System TablesREMAP="BO--Body"The following system tables have changed in this release:TOCENTRY="1"LBL="Table 1-10: "Changes to Existing System TablesCOLS="2" CHAROFF="50" CHAR="" ALIGN="LEFT"COLNAME="C1" COLWIDTH="11*"COLNAME="C2" COLWIDTH="29*"VALIGN="BOTTOM"COLNAME="C1" ALIGN="LEFT" ROTATE="0" MOREROWS="0"TableCOLNAME="C2" ALIGN="LEFT" ROTATE="0" MOREROWS="0"ChangesVALIGN="TOP"COLNAME="C1" ALIGN="LEFT" ROTATE="0" MOREROWS="0"master..sysdatabasesCOLNAME="C2" ALIGN="LEFT" ROTATE="0" MOREROWS="0"New columns: status2, audflags, deftabaud, defvwaud, 
defpraud; mode removed.COLNAME="C1" ALIGN="LEFT" ROTATE="0" MOREROWS="0"master..syslocksCOLNAME="C2" ALIGN="LEFT" ROTATE="0" MOREROWS="0"New column: class.COLNAME="C1" ALIGN="LEFT" ROTATE="0" MOREROWS="0"master..sysloginsCOLNAME="C2" ALIGN="LEFT" ROTATE="0" MOREROWS="0"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.)COLNAME="C1" ALIGN="LEFT" ROTATE="0" MOREROWS="0"master..sysmessagesCOLNAME="C2" ALIGN="LEFT" ROTATE="0" MOREROWS="0"New column: sqlstate.COLNAME="C1" ALIGN="LEFT" ROTATE="0" MOREROWS="0"master..sysprocesses COLNAME="C2" ALIGN="LEFT" ROTATE="0" MOREROWS="0"New columns: tran_name, time_blocked, network_pktsz.COLNAME="C1" ALIGN="LEFT" ROTATE="0" MOREROWS="0"master..sysusages COLNAME="C2" ALIGN="LEFT" ROTATE="0" MOREROWS="0"New columns: pad and unreservedpgs.COLNAME="C1" ALIGN="LEFT" ROTATE="0" MOREROWS="0"syscolumnsCOLNAME="C2" ALIGN="LEFT" ROTATE="0" MOREROWS="0"New columns: prec, scale.COLNAME="C1" ALIGN="LEFT" ROTATE="0" MOREROWS="0"syscommentsCOLNAME="C2" ALIGN="LEFT" ROTATE="0" MOREROWS="0"New column: colid2.COLNAME="C1" ALIGN="LEFT" ROTATE="0" MOREROWS="0"sysindexes COLNAME="C2" ALIGN="LEFT" ROTATE="0" MOREROWS="0"New column: status2. Changed columns: spare1 to 
oampgtrips, spare2 to ipgtrips.COLNAME="C1" ALIGN="LEFT" ROTATE="0" MOREROWS="0"sysobjects COLNAME="C2" ALIGN="LEFT" ROTATE="0" MOREROWS="0"Changed columns: schema to schmacnt, refdate to 
sysstat2, category to ckfirst. New columns: objspare and 
audflags.COLNAME="C1" ALIGN="LEFT" ROTATE="0" MOREROWS="0"sysprotects COLNAME="C2" ALIGN="LEFT" ROTATE="0" MOREROWS="0"New column: grantor. Changed columns: values for the 
protecttype column are different. 0 indicates MOREINFO="NONE"grant with 
grant, 1 indicates regular MOREINFO="NONE"grant, and 2 indicates MOREINFO="NONE"revoke.COLNAME="C1" ALIGN="LEFT" ROTATE="0" MOREROWS="0"systypes COLNAME="C2" ALIGN="LEFT" ROTATE="0" MOREROWS="0"New columns: prec, scale, ident, hierarchy.REMAP="BO--Body"NUMBER="2" ID="X14018"Changes That May Affect Existing ApplicationsRENDERAS="SECT1"IntroductionREMAP="BO--Body"This chapter describes the changes to Release 10.0 of SQL Server that may affect existing applications. These changes are:MARK="BULL" REMAP="BU--Bullet"New Transact-SQL keywordsPassword changesAddition of sybsystemprocs databaseRemote access configured "on" in all existing databases for Backup ServerChanges to dump scripts Changes to renaming databasesDatatype changes:MARK="DASH" REMAP="BS--BulletSub"Display Format ChangesChanges to the datatype hierarchy and defaultsOverflow changes, including float-to-character, numeric-to-numeric, and integer-to-character conversions.Change to MOREINFO="NONE"betweenAddition of ANSI-style comments using "--"Addition of ANSI-compliant permissions requirements for MOREINFO="NONE"update and MOREINFO="NONE"delete statementsAddition of ANSI-compliant treatment of nulls when MOREINFO="NONE"set ansinull is MOREINFO="NONE"onChained transaction mode and its effect on stored proceduresRole changes that may affect scriptsRepeated table names in the MOREINFO="NONE"from clause of an MOREINFO="NONE"update or MOREINFO="NONE"select statement now causes an errorColumn names required for MOREINFO="NONE"select into when the select list contains expressions or aggregatesChanges to correlation name handlingChanges to subquery behaviorREMAP="BO--Body"In addition, you may want to review applications using "browse mode", to take advantage of cursors. ID="IN121"Keywordsnew
ID="IN252"Keywordsfinding existingRENDERAS="SECT1"New Transact-SQL KeywordsREMAP="BO--Body"The following keywords are new "reserved" words in 10.0 SQL Server. They cannot be used as object names or column namesTOCENTRY="1"LBL="Table 2-1: "New Reserved WordsCOLS="3" CHAROFF="50" CHAR="" ALIGN="LEFT"COLNAME="C1" COLWIDTH="1*"COLNAME="C2" COLWIDTH="1*"COLNAME="C3" COLWIDTH="1*"VALIGN="TOP"COLNAME="C1" ALIGN="LEFT" ROTATE="0" MOREROWS="0"arith_overflowCOLNAME="C2" ALIGN="LEFT" ROTATE="0" MOREROWS="0"identity_insertCOLNAME="C3" ALIGN="LEFT" ROTATE="0" MOREROWS="0"referencesCOLNAME="C1" ALIGN="LEFT" ROTATE="0" MOREROWS="0"atCOLNAME="C2" ALIGN="LEFT" ROTATE="0" MOREROWS="0"isolationCOLNAME="C3" ALIGN="LEFT" ROTATE="0" MOREROWS="0"replaceCOLNAME="C1" ALIGN="LEFT" ROTATE="0" MOREROWS="0"authorizationCOLNAME="C2" ALIGN="LEFT" ROTATE="0" MOREROWS="0"keyCOLNAME="C3" ALIGN="LEFT" ROTATE="0" MOREROWS="0"roleCOLNAME="C1" ALIGN="LEFT" ROTATE="0" MOREROWS="0"cascadeCOLNAME="C2" ALIGN="LEFT" ROTATE="0" MOREROWS="0"levelCOLNAME="C3" ALIGN="LEFT" ROTATE="0" MOREROWS="0"rowsCOLNAME="C1" ALIGN="LEFT" ROTATE="0" MOREROWS="0"checkCOLNAME="C2" ALIGN="LEFT" ROTATE="0" MOREROWS="0"mirrorCOLNAME="C3" ALIGN="LEFT" ROTATE="0" MOREROWS="0"schemaCOLNAME="C1" ALIGN="LEFT" ROTATE="0" MOREROWS="0"closeCOLNAME="C2" ALIGN="LEFT" ROTATE="0" MOREROWS="0"nationalCOLNAME="C3" ALIGN="LEFT" ROTATE="0" MOREROWS="0"sharedCOLNAME="C1" ALIGN="LEFT" ROTATE="0" MOREROWS="0"constraintCOLNAME="C2" ALIGN="LEFT" ROTATE="0" MOREROWS="0"noholdlockCOLNAME="C3" ALIGN="LEFT" ROTATE="0" MOREROWS="0"someCOLNAME="C1" ALIGN="LEFT" ROTATE="0" MOREROWS="0"currentCOLNAME="C2" ALIGN="LEFT" ROTATE="0" MOREROWS="0"numeric_truncationCOLNAME="C3" ALIGN="LEFT" ROTATE="0" MOREROWS="0"stripeCOLNAME="C1" ALIGN="LEFT" ROTATE="0" MOREROWS="0"cursorCOLNAME="C2" ALIGN="LEFT" ROTATE="0" MOREROWS="0"ofCOLNAME="C3" ALIGN="LEFT" ROTATE="0" MOREROWS="0"syb_identityCOLNAME="C1" ALIGN="LEFT" ROTATE="0" MOREROWS="0"deallocateCOLNAME="C2" ALIGN="LEFT" ROTATE="0" MOREROWS="0"onlyCOLNAME="C3" ALIGN="LEFT" ROTATE="0" MOREROWS="0"syb_restreeCOLNAME="C1" ALIGN="LEFT" ROTATE="0" MOREROWS="0"doubleCOLNAME="C2" ALIGN="LEFT" ROTATE="0" MOREROWS="0"openCOLNAME="C3" ALIGN="LEFT" ROTATE="0" MOREROWS="0"userCOLNAME="C1" ALIGN="LEFT" ROTATE="0" MOREROWS="0"endtranCOLNAME="C2" ALIGN="LEFT" ROTATE="0" MOREROWS="0"optionCOLNAME="C3" ALIGN="LEFT" ROTATE="0" MOREROWS="0"user_optionCOLNAME="C1" ALIGN="LEFT" ROTATE="0" MOREROWS="0"escapeCOLNAME="C2" ALIGN="LEFT" ROTATE="0" MOREROWS="0"precisionCOLNAME="C3" ALIGN="LEFT" ROTATE="0" MOREROWS="0"varyingCOLNAME="C1" ALIGN="LEFT" ROTATE="0" MOREROWS="0"fetchCOLNAME="C2" ALIGN="LEFT" ROTATE="0" MOREROWS="0"primaryCOLNAME="C3" ALIGN="LEFT" ROTATE="0" MOREROWS="0"workCOLNAME="C1" ALIGN="LEFT" ROTATE="0" MOREROWS="0"foreignCOLNAME="C2" ALIGN="LEFT" ROTATE="0" MOREROWS="0"privilegesCOLNAME="C3" ALIGN="LEFT" ROTATE="0" MOREROWS="0"COLNAME="C1" ALIGN="LEFT" ROTATE="0" MOREROWS="0"identityCOLNAME="C2" ALIGN="LEFT" ROTATE="0" MOREROWS="0"readCOLNAME="C3" ALIGN="LEFT" ROTATE="0" MOREROWS="0"REMAP="BO--Body"You must change all database names that are new reserved words before you can upgrade an earlier version of the server. You can change table, view, and column names or use delimited identifiers. Once you upgrade to Release 10.0, you cannot use database objects whose names are new reserved words until you modify your procedures, SQL scripts, and applications.ID="IN343"Identifiersfinding reserved words
ID="IN314"sp_checkreswordsREMAP="BO--Body"A new system procedure, ID="IN495"System proceduressp_checkreswords
MOREINFO="NONE"sp_checkreswords, checks for reserved words used as identifiers and reports the object names. For information on running this procedure before you upgrade, see your SYBASE SQL Server Installation Guide.REMAP="BO--Body"If you choose to leave some of these reserved words as identifiers until after you upgrade to 10.0, you can run the procedure MOREINFO="NONE"sp_checkreswords at any time. See BOOK="ref" LINKEND="X60474""sp_checkreswords"
in Volume 2 of the SQL Server Reference Manual for information about running this procedure and for detailed information about upgrading your applications.ID="IN166"PasswordslengthRENDERAS="SECT1"Password ChangesID="IN157"Passwordsaging
ID="IN148"PasswordsnullREMAP="BO--Body"SQL Server no longer allows NULL passwords. All passwords must be at least 6 bytes in length. Existing passwords containing less than 6 bytes are not changed during upgrade, but all future uses of MOREINFO="NONE"sp_password will require a 6-character new password.ID="IN539"sp_configurepassword expiration levelREMAP="BO--Body"You can globally force users to change passwords within a specified time period using the MOREINFO="NONE"password expiration level option to MOREINFO="NONE"sp_configure. This feature provides last-chance warning messages when a user logs in with a password that is about to expire. Users whose passwords have expired can log in, but they can execute MOREINFO="NONE"sp_passwordID="IN11110"sp_password
only.REMAP="BO--Body"If you have applications that include embedded passwords, you will need to update them each expiration period if you turn on password expiration. REMAP="BO--Body"See BOOK="sag" LINKEND="X26541"Chapter 12,  "Fine-Tuning Performance and Operations"
, in the System Administration Guide for more information.ID="IN2911"Permissionssystem procedures
ID="IN5712"sybsystemprocs databaseRENDERAS="SECT1"Addition of sybsystemprocs Database REMAP="BO--Body"In Release 10.0, all Sybase-provided system procedures are now stored in a database called sybsystemprocs rather than in the master database. Possible effects on user applications are:MARK="BULL" REMAP="BU--Bullet"Permissions on system procedures need to be changed in sybsystemprocs.System Administrators need to decide where to place their own procedures.ID="IN7213"open databases configuration variableUpgrade may increase the MOREINFO="NONE"open databases configuration variable, if adding sybsystemprocs requires it. You should verify the value for your needs on your server.Scripts that perform backups and MOREINFO="NONE"dbcc may need to change.Recovery of master database and system databases has changed.RENDERAS="SECT2"Changing Permissions on System ProceduresREMAP="BO--Body"If you have changed the default permissions on any system procedures, you must MOREINFO="NONE"grant or MOREINFO="NONE"revoke the permissions on those procedures in sybsystemprocs after upgrade.ID="IN2414"Stored proceduresmovingRENDERAS="SECT2"Moving Your Own ProceduresREMAP="BO--Body"To move your own procedures to sybsystemprocs, you must:MARK="BULL" REMAP="BU--Bullet"Change all references to system tables or other objects that exist only in master to reference the master database explicitly.Add checks to be sure that the procedure is not being run from within a transaction.REMAP="BO--Body"Once these steps have been performed, you can create the procedure in the sybsystemprocs database and drop it from master.RENDERAS="SECT3"Changing References to master's System TablesREMAP="BO--Body"If you want to move your locally-created system procedures to sybsystemprocs, and they reference system tables in master, you must qualify all of the tables names with the database name. REMAP="BO--Body"For example, if your procedure references syslogins, you must change the reference to master..syslogins. When you create a stored procedure, SQL Server checks to see that all of the tables that are referenced in the procedure exist. You cannot create the procedure in sybsystemprocs without the explicit database reference.RENDERAS="SECT3"Checking for the Existence of TransactionsREMAP="BO--Body"A stored procedure in sybsystemprocs should never modify the tables in master inside a transaction, because this could create problems with recovery.REMAP="BO--Body"SYBASE system procedures include a check, similar to this:REMAP="UIW--UserInputWide" FORMAT="LINESPECIFIC"if @@trancount > 0begin    print "Can't run this procedure from within a transaction"    return 1endID="IN2415"Open databases, limit ofRENDERAS="SECT2"Configuring Open DatabasesREMAP="BO--Body"By default, SQL Server is configured to allow up to 10 open databases. The upgrade process will reconfigure the MOREINFO="NONE"open databases variable if the addition of sybsystemprocs requires an additional open database.RENDERAS="SECT2"Changing Backup and dbcc ProceduresREMAP="BO--Body"If you add your own procedures to sybsystemprocs or change the default permissions on the system procedures, you should add sybsystemprocs to your regular backup schedule.REMAP="BO--Body"You should also include sybsystemprocs in your regular MOREINFO="NONE"dbcc checks.ID="IN5416"Recovery changes, master deviceRENDERAS="SECT2"Changes in Master RecoveryREMAP="BO--Body"Due to the addition of sybsystemprocs and also Backup Server changes in system tables and system procedures, the process of recovering system databases in case of a failure of the master device has changed. See BOOK="sag" LINKEND="X40994"Chapter 9,  "Backing Up and Restoring the System Databases", in the System Administration Guide for more information.ID="IN1317"Remote accessRENDERAS="SECT1"Remote Access and the Backup ServerREMAP="BO--Body"All dump and load operations in Release 10.0 of SQL Server are executed through remote procedure calls to the Backup Server. To allow SQL Server to communicate with the Backup Server, the configuration variable MOREINFO="NONE"remote access must be turned on. Leave this variable turned on unless you have security concerns. You must be a System Security Officer to set MOREINFO="NONE"remote accessID="IN6118"sp_configureremote access
.REMAP="BO--Body"Turning on MOREINFO="NONE"remote access does not affect any of the other conditions needed to execute remote procedure calls between servers. This setting, in itself, does not represent a security concern, because server-to-server communication requires additional system administration actions to enable remote procedure calls. These actions include making entries for the remote servers in master..sysservers; and making entries for remote users in master..sysremotelogins.REMAP="BO--Body"If you wish to leave MOREINFO="NONE"remote access disabled except during a dump or load, you must always issue the following command before issuing the dump or load command:REMAP="UI--UserInput" FORMAT="LINESPECIFIC"sp_configure "remote access", 1REMAP="BO--Body"After you complete the dump, turn MOREINFO="NONE"remote access off again with the command:REMAP="UI--UserInput" FORMAT="LINESPECIFIC"sp_configure "remote access", 0REMAP="BO--Body"The MOREINFO="NONE"remote access configuration variable is dynamic: you do not have to restart SQL Server for the MOREINFO="NONE"sp_configure command to take effect.ID="IN5919"dump transactionchanges
ID="IN5620"dump databasechanges
ID="IN1421"Backup changes
ID="IN1922"Dump script changesRENDERAS="SECT1"Changes to Dump ScriptsREMAP="BO--Body"Backup facilities have changed in Release 10.0 of SQL Server. This section describes the minimum changes needed to existing dump scripts.REMAP="BO--Body"The single feature that can most affect your current use of tapes and dump commands is Backup Server's ability to make multiple dumps to a single tape. The new dump is placed after the last existing file on the current tape volume.ID="IN1123"Null deviceAll dumps to the "null device" (/dev/null on UNIX or any device starting with "NL" on VMS) are now prohibited.REMAP="BO--Body"Here are some guidelines for backing up your databases immediately after upgrading to 10.0:MARK="BULL" REMAP="BU--Bullet"If you use new tapes, or tapes without ANSI labels, existing dump scripts overwrite the entire tape.If you use single-file media (for example, quarter-inch cartridge) with ANSI labels, and the expiration dates on the tapes have expired, existing dump scripts will overwrite the tapes.If the expiration date on a single-file tape has not been reached, you will be asked to confirm the overwrite; a positive response will overwrite the existing tape; a negative response initiates a request for a volume change, and tests are repeated on the new volume.ID="IN2324"Tapesand Backup ServerIf you use multi-file tape media, and do not change your dump scripts, the dump will be appended to the existing files on the tape.If you want to overwrite existing tapes that have ANSI labels, you must append the MOREINFO="NONE"with init clause to existing dump commands:REMAP="UI--UserInput" FORMAT="LINESPECIFIC"dump databasse mydb     to datadump1    with initREMAP="UN--Under"You can also use operating system commands to erase or truncate the tape.REMAP="BO--Body"The following diagram shows the logic used in tape label checking on a dump command used without the MOREINFO="NONE"init option:  FLOAT="0"FORMAT="UNKNOWN" FILEREF="fig2_1.xwd"LBL="Figure 2-1: "Checking Tape Format and Expiration Dates for Dump CommandsID="IN1825"Renaming databasesRENDERAS="SECT1"Changes to Renaming DatabasesREMAP="BO--Body"If any table in the database references-or is referenced by-a table in another database,  MOREINFO="NONE"sp_renamedbID="IN4626"sp_renamedb
ID="IN6427"System proceduressp_renamedb
cannot rename the database. It produces the following error message:REMAP="CO--ComputerOutput" FORMAT="LINESPECIFIC"Database 'database_name' has references to otherdatabases. Drop those references and try again.REMAP="BO--Body"Execute the following query to determine which tables and external databases have foreign key constraints on primary key tables in the current database:REMAP="UI--UserInput" FORMAT="LINESPECIFIC"select object_name(tableid), db_name(frgndbname) from sysreferenceswhere frgndbname is not nullREMAP="BO--Body"Execute the following query to determine which tables and external  databases have primary key constraints for foreign key tables in the current  database:REMAP="UI--UserInput" FORMAT="LINESPECIFIC"select object_name(reftabid), db_name(pmrydbname) from sysreferenceswhere pmrydbname is not nullREMAP="BO--Body"Before renaming the database, you must useMOREINFO="NONE" alter table to drop the cross-database constraints in these tables.  See BOOK="ref" LINKEND="X99854""sp_renamedb"
in Volume 2 of the SQL Server Reference Manual for more information about renaming databases.ID="IN5028"convert changes
ID="IN929"DatatypesRENDERAS="SECT1"Datatype and Conversion ChangesID="IN2230"Display format changesRENDERAS="SECT2"Display Format ChangesID="IN4731"isql utilityREMAP="BO--Body"The MOREINFO="NONE"isql display format for approximate-numeric datatypes now displays additional digits of precision. Maximum units of precision for storage and display are machine dependent. real values now display up to 9 digits of precision; float values, up to 17 digits. Values are rounded to the last digit on display. Previously, only six places to the right of the decimal point were displayed.REMAP="BO--Body"All values requiring more digits than the maximum are displayed in scientific notation, that is, a float value "1e18" is displayed as such, rather than 1000000000000000000.000000. Note that the new exact numeric types, decimal and numeric, display the entire number.ID="IN2032"Default type changesRENDERAS="SECT2"Default Type ChangesID="IN1633"Numeric literalsREMAP="BO--Body"Numeric literals in SQL statements are now treated as exact numeric types, int or numeric, unless they are entered using scientific notation. Here are examples of differences you might see:TOCENTRY="1"LBL="Table 2-2: "Changes in Output Using Numeric LiteralsCOLS="3" CHAROFF="50" CHAR="" ALIGN="LEFT"COLNAME="C1" COLWIDTH="15*"COLNAME="C2" COLWIDTH="9*"COLNAME="C3" COLWIDTH="9*"VALIGN="BOTTOM"COLNAME="C1" ALIGN="LEFT" ROTATE="0" MOREROWS="0"SQL StatementCOLNAME="C2" ALIGN="LEFT" ROTATE="0" MOREROWS="0"Pre-10.0 ServersCOLNAME="C3" ALIGN="LEFT" ROTATE="0" MOREROWS="0"10.0VALIGN="TOP"COLNAME="C1" ALIGN="LEFT" ROTATE="0" MOREROWS="0"MOREINFO="NONE"select 2147483648COLNAME="C2" ALIGN="LEFT" ROTATE="0" MOREROWS="0"integer overflowCOLNAME="C3" ALIGN="LEFT" ROTATE="0" MOREROWS="0"2147483648.COLNAME="C1" ALIGN="LEFT" ROTATE="0" MOREROWS="0"MOREINFO="NONE"select 2*1.12345678COLNAME="C2" ALIGN="LEFT" ROTATE="0" MOREROWS="0"2.246914COLNAME="C3" ALIGN="LEFT" ROTATE="0" MOREROWS="0"2.24691356.COLNAME="C1" ALIGN="LEFT" ROTATE="0" MOREROWS="0"MOREINFO="NONE"select 2.8 * 5COLNAME="C2" ALIGN="LEFT" ROTATE="0" MOREROWS="0"14.000000COLNAME="C3" ALIGN="LEFT" ROTATE="0" MOREROWS="0"14.0ID="IN2734"Datatypeshierarchy changesRENDERAS="SECT2"Datatype Hierarchy ChangesREMAP="BO--Body"Each system datatype has a datatype hierarchy, stored in the systypes system table. User-defined datatypes inherit the hierarchy of the system types on which they are based. The following query ranks the system datatypes by hierarchy:REMAP="UI--UserInput" FORMAT="LINESPECIFIC"select name,hierarchyfrom systypesorder by hierarchyREMAP="CO--ComputerOutput" FORMAT="LINESPECIFIC"name                         hierarchy ------------------------------ ---------floatn                               1float                                2datetimn                             3datetime                             4real                                 5numericn                             6numeric                              7decimaln                             8decimal                              9moneyn                              10money                               11smallmoney                          12smalldatetime                       13intn                                14int                                 15smallint                            16tinyint                             17bit                                 18varchar                             19sysname                             19nvarchar                            19char                                20nchar                               20varbinary                           21timestamp                           21binary                              22text                                23image                               24(28 rows affected) REMAP="BO--Body"The datatype hierarchy determines the results of computations using values of different datatypes. The result value is assigned the datatype that is closest to the top of the list. REMAP="BO--Body"In earlier releases of SQL Server, there were certain exceptions to this rule that have been eliminated in Release 10.0. Specifically, float and numericID="IN4935"money datatype 
computations combined with moneyID="IN5336"smallmoney datatype
or smallmoney.TOCENTRY="1"LBL="Table 2-3: "Changes to Datatype HierarchyCOLS="3" CHAROFF="50" CHAR="" ALIGN="LEFT"COLNAME="C1" COLWIDTH="15*"COLNAME="C2" COLWIDTH="9*"COLNAME="C3" COLWIDTH="9*"VALIGN="BOTTOM"COLNAME="C1" ALIGN="LEFT" ROTATE="0" MOREROWS="0"DatatypesCOLNAME="C2" ALIGN="LEFT" ROTATE="0" MOREROWS="0"Pre-10.0 ServersCOLNAME="C3" ALIGN="LEFT" ROTATE="0" MOREROWS="0"10.0VALIGN="TOP"COLNAME="C1" ALIGN="LEFT" ROTATE="0" MOREROWS="0"money and numericCOLNAME="C2" ALIGN="LEFT" ROTATE="0" MOREROWS="0"N/ACOLNAME="C3" ALIGN="LEFT" ROTATE="0" MOREROWS="0"numericCOLNAME="C1" ALIGN="LEFT" ROTATE="0" MOREROWS="0"money and floatCOLNAME="C2" ALIGN="LEFT" ROTATE="0" MOREROWS="0"moneyCOLNAME="C3" ALIGN="LEFT" ROTATE="0" MOREROWS="0"floatREMAP="BO--Body"Workarounds include:MARK="BULL" REMAP="BU--Bullet"If you are combining money and literals or variables, and need results of money type, use money literals or variables:REMAP="UI--UserInput" FORMAT="LINESPECIFIC"select moneycol * $2.5 from mytableIf you are combining money with a float or numeric column, use the MOREINFO="NONE"convert function:REMAP="UI--UserInput" FORMAT="LINESPECIFIC"select convert (money, moneycol * percentcol)    from debts, interestRENDERAS="SECT2"Overflow ChangesID="IN3937"Overflow conditions, conversion changesREMAP="BO--Body"More information on overflow conditions is provided, and overflow behavior has changed for some datatypes. Additional error messages provide more information about the specific cause of the overflow problem.ID="IN2638"Floating point conversionsRENDERAS="SECT3"Floating Point-to-Character ConversionsREMAP="BO--Body"In previous releases, conversion from floating point to character in some cases allowed some truncation of floating point decimal data without warning, and in other cases generated warning messages. REMAP="BO--Body"Now, all conversions to character data of any length succeed only if no decimal digits are lost. The length of the character string varies depending upon the number being converted and the accuracy of floating point numbers supported by the platform. To guarantee success, use a target of 25 characters.ID="IN1939"Numeric conversionsRENDERAS="SECT3"Numeric-to-Numeric Conversions Requiring TruncationID="IN2940"Truncationconversion changesREMAP="BO--Body"All conversions to money datatypes round to four places. When an explicit conversion of one numeric value to another results in a loss of scale, the results are truncated without warning. For example, when you explicitly convert a float, numeric, or decimal type to an integer, SQL Server assumes you really want the result to be an integer and truncates all numbers to the right of the decimal point.ID="IN10641"arithabort optionnumeric_truncationREMAP="BO--Body"During implicit conversions to numeric or decimal types, loss of scale generates a scale error. Use the MOREINFO="NONE"arithabort numeric_truncation option to determine how serious such an error is considered. The default setting, MOREINFO="NONE"arithabort numeric_truncation on, aborts the statement that causes the error but continues to process other statements in the transaction or batch. If you set MOREINFO="NONE"arithabort numeric_truncation off, SQL Server truncates the query results and continues processing. This option is set MOREINFO="NONE"on by default.ID="IN1942"Integer conversionsRENDERAS="SECT3"Integer-to-Character ConversionsREMAP="BO--Body"Conversions from integer types to character types now return a buffer overflow error (instead of "*") on overflow.ID="IN2443"Conversionerror messageRENDERAS="SECT2"Changes to Conversion Error MessagesREMAP="BO--Body"The text of the messages for unsupported datatype conversions (Message 529) now reads:REMAP="CO--ComputerOutput" FORMAT="LINESPECIFIC"Explicit conversion from datatype 'type' to 'type' is not allowed.REMAP="BO--Body"New error messages provide additional information about datatype conversion errors: TOCENTRY="1"LBL="Table 2-4: "New Type Conversion Error MessagesCOLS="2" CHAROFF="50" CHAR="" ALIGN="LEFT"COLNAME="C1" COLWIDTH="3*"COLNAME="C2" COLWIDTH="17*"VALIGN="BOTTOM"COLNAME="C1" ALIGN="LEFT" ROTATE="0" MOREROWS="0"Error 
NumberCOLNAME="C2" ALIGN="LEFT" ROTATE="0" MOREROWS="0"MessageVALIGN="TOP"COLNAME="C1" ALIGN="LEFT" ROTATE="0" MOREROWS="0"241COLNAME="C2" ALIGN="LEFT" ROTATE="0" MOREROWS="0"Scale error during [explicit | implicit] conversion of datatype value 
`value' to a datatype field.COLNAME="C1" ALIGN="LEFT" ROTATE="0" MOREROWS="0"245COLNAME="C2" ALIGN="LEFT" ROTATE="0" MOREROWS="0"Domain error during [explicit | implicit] conversion of datatype 
value `value' to a datatype field.COLNAME="C1" ALIGN="LEFT" ROTATE="0" MOREROWS="0"247COLNAME="C2" ALIGN="LEFT" ROTATE="0" MOREROWS="0"Arithmetic overflow during [explicit | implicit] conversion of 
datatype value `value' to a datatype field.COLNAME="C1" ALIGN="LEFT" ROTATE="0" MOREROWS="0"249COLNAME="C2" ALIGN="LEFT" ROTATE="0" MOREROWS="0"Syntax error during [explicit | implicit] conversion of datatype 
value `value' to a datatype field.COLNAME="C1" ALIGN="LEFT" ROTATE="0" MOREROWS="0"265COLNAME="C2" ALIGN="LEFT" ROTATE="0" MOREROWS="0"Insufficient result space for [explicit | implicit] conversion of 
datatype value `value' to a datatype field.RENDERAS="SECT1"Change to betweenID="IN5944"between predicate changeREMAP="BO--Body"The MOREINFO="NONE"between predicate is used in MOREINFO="NONE"where clauses, such as this:REMAP="UI--UserInput" FORMAT="LINESPECIFIC"where colx between val1 and val2REMAP="BO--Body"val1 must be less than val2. In a few situations, the MOREINFO="NONE"between predicate returned correct values, even when val2 < val1. In Release 10.0, these values must be given in the correct order; check your applications that use MOREINFO="NONE"between if you think they may be dependent on the incorrect behavior.RENDERAS="SECT1"ANSI-Style CommentsID="IN7645"-\- (minus signs)-\ minus signsas comment markercomment marker
ID="IN5446"Comments, ANSI-styleREMAP="BO--Body"To comply with ANSI 89, SQL Server now supports ANSI-style comments, which consist of two dash or minus-sign characters. All characters after "--" are ignored. This could cause problems in any SQL query that uses the subtraction of a negative number, such as this:REMAP="UI--UserInput" FORMAT="LINESPECIFIC"select 5--2REMAP="BO--Body"This query no longer returns the value "7"; it now returns "5". All characters from "--" to the end of the line are ignored.REMAP="BO--Body"You can correct any constructions like these by inserting a space between the minus signs, or by enclosing "-2" in parentheses:REMAP="UI--UserInput" FORMAT="LINESPECIFIC"select 5-(-2)RENDERAS="SECT1"ANSI-Compliant Permissions Requirements for update and delete REMAP="BO--Body"SQL Server now supports ANSI-compliant permissions requirements for MOREINFO="NONE"update and MOREINFO="NONE"delete statements. The following table summarizes these requirementsPGWIDE=1 TOCENTRY="1"LBL="Table 2-5: "ANSI Permissions for update and deleteCOLS="3" CHAROFF="50" CHAR="" ALIGN="LEFT"COLNAME="C1" COLWIDTH="4*"COLNAME="C2" COLWIDTH="11*"COLNAME="C3" COLWIDTH="11*"VALIGN="BOTTOM"COLNAME="C1" ALIGN="LEFT" ROTATE="0" MOREROWS="0"COLNAME="C2" ALIGN="LEFT" ROTATE="0" MOREROWS="0"Permissions Required with
set ansi_permissions offCOLNAME="C3" ALIGN="LEFT" ROTATE="0" MOREROWS="0"Permissions Required with
set ansi_permissions onVALIGN="TOP"COLNAME="C1" ALIGN="LEFT" ROTATE="0" MOREROWS="0"MOREINFO="NONE"updateCOLNAME="C2" ALIGN="LEFT" ROTATE="0" MOREROWS="0"MOREINFO="NONE"name='bull' font='symbol' code='168'  charset='fontspecific' update permission on columns where 
values are being setCOLNAME="C3" ALIGN="LEFT" ROTATE="0" MOREROWS="0"name='bull' font='symbol' code='168'  charset='fontspecific' MOREINFO="NONE"update permission on columns where 
values are being set
And...
name='bull' font='symbol' code='168'  charset='fontspecific' MOREINFO="NONE"select permission on all columns 
appearing in MOREINFO="NONE"where clause
name='bull' font='symbol' code='168'  charset='fontspecific' MOREINFO="NONE"select permission on all columns right 
side of MOREINFO="NONE"set clauseCOLNAME="C1" ALIGN="LEFT" ROTATE="0" MOREROWS="0"MOREINFO="NONE"deleteCOLNAME="C2" ALIGN="LEFT" ROTATE="0" MOREROWS="0"MOREINFO="NONE"name='bull' font='symbol' code='168'  charset='fontspecific' delete permission on columns where 
values are being setCOLNAME="C3" ALIGN="LEFT" ROTATE="0" MOREROWS="0"MOREINFO="NONE"name='bull' font='symbol' code='168'  charset='fontspecific' delete permission on the table from 
which rows are being deleted
And...
MOREINFO="NONE"name='bull' font='symbol' code='168'  charset='fontspecific' select permission on all columns 
appearing in MOREINFO="NONE"where clause:REMAP="BO--Body"If you attempt to MOREINFO="NONE"update or MOREINFO="NONE"delete without having all the required permissions, an exception is generated and the transaction is rolled back. In this case, you will see the following message:REMAP="COW--CompOutWide" FORMAT="LINESPECIFIC"permission_type permission denied on object object_name databasedatabase_name, owner object_ownerREMAP="BO--Body"If this occurs, you need to be granted MOREINFO="NONE"select permission on all relevant columns by the column owner. RENDERAS="SECT1"ANSI-Compliant Treatment of NullsREMAP="BO--Body"SQL Server now supports ANSI `89 -compliant treatment of NULL value operands in equality (=) and inequality (!=) comparisons. REMAP="BO--Body"When MOREINFO="NONE"set ansinull is MOREINFO="NONE"on, SQL Server returns an error message if a NULL value operand is eliminated from equality (=) and inequality (!=) comparisons and aggregate functions. ID="IN2047"Chained transactionsRENDERAS="SECT1"Switching to Chained Transaction ModeREMAP="BO--Body"Stored procedures written to use regular Transact-SQL transaction mode may be incompatible with ANSI chained transaction mode. In Release 10.0, all transactions will be tagged to identify which mode they use. If you intend to convert existing SQL Server applications to use chained transaction mode, see BOOK="ref" LINKEND="X19352""Transactions"
in Volume 1 of the ID="IN11348"sp_procxmode
SQL Server Reference Manual and MOREINFO="NONE"sp_procxmode in Volume 2 for information about changing transaction modes. ID="IN549"RolesID="X39308" RENDERAS="SECT1"Using Roles in SQL ServerREMAP="BO--Body"See LINKEND="X61028""System Administration Roles"
for a description of the new roles. There are two ways to use roles in the SQL Server:MARK="BULL" REMAP="BU--Bullet"As before, use the "sa" account for system administration tasks. When SQL Server is installed, this account has System Administrator, System Security Officer, and Operator roles enabled.After installing SQL Server, use the "sa" account to create new server logins for users who are to be granted roles. Grant the correct roles to these users, and then lock the "sa" account. ID="IN2550"System Administrator roleREMAP="BO--Body"The second method increases user accountability for these highly privileged users, as they perform system administration tasks under their own logins.REMAP="BO--Body"However, if you decide to lock the "sa" account, be sure to check all scripts that may contain the "sa" login name and password. These can include scripts that perform backups, run MOREINFO="NONE"bcp, or perform MOREINFO="NONE"dbcc checking. The scripts cannot run if they are meant to run as "sa" and that account is locked. Change the logins in those scripts to the name of one of the users with the correct role.RENDERAS="SECT1"Repeated Table Names in fromID="IN2051"Repeated table names
ClausesID="IN4652"from clauseREMAP="BO--Body"In ANSI 89, the following construction is not allowed:REMAP="UI--UserInput" FORMAT="LINESPECIFIC"select * from table1, table1    [where_clause]REMAP="BO--Body"In earlier releases, SQL Server handled the MOREINFO="NONE"select by returning only the first table and ignoring the self-join. In Release 10.0, this syntax returns an error message.REMAP="BO--Body"The correct syntax uses table correlation names:REMAP="UI--UserInput" FORMAT="LINESPECIFIC"select * from table1 t1, table1 t2    [where_clause]REMAP="BO--Body"A similar construction using repeated table names in an MOREINFO="NONE"update statement also returns an error message:REMAP="UI--UserInput" FORMAT="LINESPECIFIC"update table1    set a = a + 1    from table1    where b = 5ID="IN4153"updateREMAP="BO--Body"The MOREINFO="NONE"update statement also creates a self-join on the table named after MOREINFO="NONE"update and the table in the MOREINFO="NONE"from clause. It returns unexpected results, because it updates all rows from the first-mentioned table, seemingly ignoring the restriction in the MOREINFO="NONE"where clause. The correct syntax also uses table correlation names in the MOREINFO="NONE"from clause:REMAP="UI--UserInput" FORMAT="LINESPECIFIC"update table1     set a = a + 1    from table1 t1    where t1.b = 5REMAP="BO--Body"Note that the ANSI 89 standard does not include the use of the MOREINFO="NONE"from clause in an MOREINFO="NONE"update statement.RENDERAS="SECT1"Column Names Required for select intoID="IN1554"Columnheadings
with ExpressionsID="IN4655"select intoREMAP="BO--Body"Previous releases of SQL Server allowed NULL column heading in tables created by MOREINFO="NONE"select into. These NULL headings resulted from any use of an expression in the select list: an aggregate function, constant, built-in function, arithmetic expression, concatenation, and so on. In Release 10.0, you must provide a column heading. The heading must be a valid identifier, that is, it must begin with an alphabetic character or an underscore, and contain only letters, numbers, and a limited set of symbols (#, @, _, $, name='yen' font='' code='165'  charset='fontspecific', or £). It cannot contain embedded spaces.REMAP="BO--Body"You should check all applications that use MOREINFO="NONE"select into. Examples of select list items that require headings are:MARK="BULL" REMAP="BU--Bullet"An aggregate function:REMAP="UI--UserInput" FORMAT="LINESPECIFIC"MOREINFO="NONE"avg(advance)Arithmetic expression:REMAP="UI--UserInput" FORMAT="LINESPECIFIC"MOREINFO="NONE"colname * 2String concatenation:REMAP="UI--UserInput" FORMAT="LINESPECIFIC"MOREINFO="NONE"au_lname + ", " + au_fnameBuilt-in function:REMAP="UI--UserInput" FORMAT="LINESPECIFIC"MOREINFO="NONE"substring(au_lname,1,5)Constant:REMAP="UI--UserInput" FORMAT="LINESPECIFIC"MOREINFO="NONE""Result"REMAP="BO--Body"There are three ways to specify the column heading:MARK="BULL" REMAP="BU--Bullet"Before the expression or aggregate function, with an "=":REMAP="UI--UserInput" FORMAT="LINESPECIFIC"select title_id, avg_advance = avg(advance)    into #tempdata    from titlesAfter the expression or aggregate function:REMAP="UI--UserInput" FORMAT="LINESPECIFIC"select title_id, avg(advance) avg_advance     into #tempdata    from titlesAfter the expression or aggregate function, with MOREINFO="NONE"as:REMAP="UI--UserInput" FORMAT="LINESPECIFIC"select title_id, avg(advance)as avg_advance     into #tempdata    from titlesREMAP="BO--Body"The column names must be valid identifiers, unless you are using the delimited identifier feature.ID="IN1756"Correlation namesRENDERAS="SECT1"Changes to Correlation Name HandlingREMAP="BO--Body" In Release 10.0, queries that include correlation names conform to ANSI requirements. In earlier releases, statements that specified correlation names but did not use them consistently still returned results. These statements now return errors. For example, this statement is incorrect: REMAP="UI--UserInput" FORMAT="LINESPECIFIC"select title_id     from titles t    where titles.type = "trad_cook"REMAP="BO--Body"The correct query is:REMAP="UI--UserInput" FORMAT="LINESPECIFIC"select title_id     from titles t    where t.type = "trad_cook"ID="IN3257"Subqueriesand correlation namesREMAP="BO--Body"When this same type of correlation is included in a subquery, no error is reported, but queries may return different results. Here is an example:REMAP="UI--UserInput" FORMAT="LINESPECIFIC"select * from mytablewhere columnA =    (select min(columnB) from mytable m         where mytable.columnC = 10)REMAP="BO--Body"In earlier releases, the reference to mytable.columnC in the subquery referred to the mytable in the subquery. Now, this query is a correlated subquery, and mytable.columnC refers to the outer table mytable. REMAP="BO--Body"If the query needs to refer to the mytable in the subquery, the correct statement is:REMAP="UI--UserInput" FORMAT="LINESPECIFIC"select * from mytablewhere columnA =    (select min(columnB) from mytable m        where m.columnC = 10)ID="IN2458"SubqueriesRENDERAS="SECT1"Subquery ChangesREMAP="BO--Body"Several problems relating to subqueries have been fixed in Release 10.0 of SQL Server. This section describes old and new behavior in detail. Examine applications that use subqueries to determine if your application relies on behavior that has been corrected. This may especially be true of the first two items below.REMAP="BO--Body"The following types of subqueries have changed:MARK="BULL" REMAP="BU--Bullet"Correlated subqueries using MOREINFO="NONE"in and MOREINFO="NONE"anySubqueries using MOREINFO="NONE"not in when the subquery returns NULL values MOREINFO="NONE"in and MOREINFO="NONE"any subqueries combined with MOREINFO="NONE"or>MOREINFO="NONE"all and <MOREINFO="NONE"all with subqueries that return no rowsSubqueries that suppressed duplicate values from the outer queryAggregate queries with MOREINFO="NONE"exists when there are duplicate valuesCorrelated subqueries with MOREINFO="NONE"distinct when used with MOREINFO="NONE"inREMAP="BO--Body"In addition, support has been added for:MARK="BULL" REMAP="BU--Bullet"The use of =MOREINFO="NONE"all to introduce subqueriesThe use of aggregates in MOREINFO="NONE"where clauses, if the following conditions are true:MARK="DASH" REMAP="BS--BulletSub"The aggregate appears in a subquery that is in a MOREINFO="NONE"having clauseThe aggregated value refers only to tables named in the MOREINFO="NONE"from clause of the outer queryThe aggregated value does not refer to tables named in the MOREINFO="NONE"from clause of the subquery itselfREMAP="BO--Body"An MOREINFO="NONE"in subquery (or a subquery using MOREINFO="NONE"any) performs an existence check. The subquery evaluates to TRUE or FALSE. Correlated subqueries return a single TRUE or FALSE value for each row in the outer query. A subquery should not cause rows from the outer query to be returned more than once each.REMAP="BO--Body"Subqueries using the MOREINFO="NONE"in predicate used to return duplicates. The same is true for subqueries using MOREINFO="NONE"any. For example, using the pubs2 database:REMAP="UI--UserInput" FORMAT="LINESPECIFIC"select pub_name    from publishers    where pub_id in        (select pub_id             from titles)REMAP="BO--Body"Or:REMAP="UI--UserInput" FORMAT="LINESPECIFIC"select pub_name    from publishers    where pub_id = any        (select pub_id             from titles)REMAP="BO--Body"In pre-10.0 releases of Transact-SQL, these queries both returned a pub_name for each row in the inner query:REMAP="CO--ComputerOutput" FORMAT="LINESPECIFIC"pub_name----------------New Age BooksNew Age BooksNew Age BooksNew Age BooksNew Age BooksBinnet & HardleyBinnet & HardleyBinnet & HardleyBinnet & HardleyBinnet & HardleyBinnet & HardleyBinnet & HardleyAlgodata InfosystemsAlgodata InfosystemsAlgodata InfosystemsAlgodata InfosystemsAlgodata InfosystemsAlgodata InfosystemsREMAP="BO--Body"They now return:REMAP="CO--ComputerOutput" FORMAT="LINESPECIFIC"pub_name------------------------New Age BooksBinnet & HardleyAlgodata InfosystemsID="INA5459"set dup_in_subqueryREMAP="BO--Body"If you have applications which depend on the pre-10.0 Transact-SQL behavior, use the following set command until you can rewrite the existing queries as joins, or add indexes to the tables the queries operate on:REMAP="UI--UserInput" FORMAT="LINESPECIFIC"set dup_in_subquery onset dup_in_subquery on is provided only as an upgrade path to SQL Server Release 10.0. It will not be supported in future releases. See the Release Bulletin for more information.ID="IN5160"not in predicateRENDERAS="SECT2"Subqueries Using not in with NULLREMAP="BO--Body"A subquery using the MOREINFO="NONE"not in predicate returns a set of values for each row in the outer query. If the value from the outer query is not in this set, the MOREINFO="NONE"not in returns TRUE. If the set returned by the subquery contains no matching value, but it does contain a NULL, the MOREINFO="NONE"not in returns UNKNOWN. This is because NULL stands for "value unknown", and it is impossible to tell whether the value you're looking for is in a set containing an unknown value. In earlier releases, SQL Server erroneously considered this case to be TRUE.REMAP="BO--Body"For example, using the pubs2 database:REMAP="UI--UserInput" FORMAT="LINESPECIFIC"select pub_id    from publishers    where $100.00 not in        (select price         from titles         where titles.pub_id = publishers.pub_id)REMAP="BO--Body"In previous releases, this query returned:REMAP="CO--ComputerOutput" FORMAT="LINESPECIFIC"pub_id------073608771389REMAP="BO--Body"It now returns the correct result:REMAP="CO--ComputerOutput" FORMAT="LINESPECIFIC"pub_id------0736RENDERAS="SECT2"ID="IN9661"any predicate
in and any Subqueries Using orREMAP="BO--Body"When a subquery using the MOREINFO="NONE"any or MOREINFO="NONE"in predicate is combined with MOREINFO="NONE"or in a logical expression, the query should return rows if the subquery evaluates to FALSE, when the other clause of the logical expression evaluates to TRUE. The same is true of subqueries using MOREINFO="NONE"any. For example, using the pubs2 database:REMAP="CO--ComputerOutput" FORMAT="LINESPECIFIC"select pub_name    from publishers    where pub_id in        (select pub_id             from titles             where title = `No Such Book')    or pub_id = `1389'REMAP="BO--Body"Or:REMAP="CO--ComputerOutput" FORMAT="LINESPECIFIC"select pub_name    from publishers    where pub_id = any        (select pub_id             from titles            where title = `No Such Book')    or pub_id = `1389`REMAP="BO--Body"These queries both returned no result rows in pre-10.0 SQL Server.REMAP="BO--Body"They now return:REMAP="CO--ComputerOutput" FORMAT="LINESPECIFIC" pub_name      -----------------------------  Algodata Infosystems  RENDERAS="SECT2"> all and < allID="IN4862"all predicate
When the Subquery Matches No RowsREMAP="BO--Body"When a subquery using the > MOREINFO="NONE"all or < MOREINFO="NONE"all predicate matches no rows, the subquery should evaluate to TRUE. Instead, it evaluated to FALSE in pre-10.0 SQL Server. For example, using the pubs2 database:REMAP="UI--UserInput" FORMAT="LINESPECIFIC"select title    from titles    where advance > all        (select advance         from publishers, titles        where titles.pub_id = publishers.pub_id    and pub_name = `No Such Publisher')REMAP="BO--Body"This query returned no rows. It now returns all of the rows, the correct result.ID="IN3563"Correlated subqueriesRENDERAS="SECT2"Correlated Subqueries Suppressing Duplicates from Outer QueryID="IN1464"Duplicate rowsREMAP="BO--Body"A correlated subquery returning a data value to the outer query sometimes caused the outer query to fail to return duplicate rows if all of the columns in the outer query are also used in the subquery. For example, using the pubs2 database:REMAP="UI--UserInput" FORMAT="LINESPECIFIC"select pub_id,     (select count(*)     from publishers    where publishers.pub_id = titles.pub_id)from titlesREMAP="BO--Body"In previous versions, this query returned these results:REMAP="CO--ComputerOutput" FORMAT="LINESPECIFIC" pub_id              ------ -----------  0736             5  0877             7  1389             6 REMAP="BO--Body"It now returns:REMAP="CO--ComputerOutput" FORMAT="LINESPECIFIC" pub_id              ------ -----------  1389             6  1389             6  0736             5  1389             6  0877             7  0877             7  0877             7  1389             6  1389             6  1389             6  0877             7  0736             5  0736             5  0736             5  0736             5  0877             7  0877             7  0877             7 ID="IN5165"exists predicateRENDERAS="SECT2"Aggregate Queries with exists SubqueriesREMAP="BO--Body"A query that selects an aggregate and has a correlated subquery that uses the MOREINFO="NONE"exists predicate produced the wrong answer if the table in the subquery's MOREINFO="NONE"from list had duplicates. For example, using the pubs2 database:REMAP="UI--UserInput" FORMAT="LINESPECIFIC"select count(*)    from publishers    where exists        (select *         from titles        where titles.pub_id = publishers.pub_id)REMAP="BO--Body"In pre-10.0 SQL Server, this query returned the value 18. It now returns the correct response, 3. It is similar to the first problem in this discussion, as it has to do with duplicates in an "existence-type" subquery to cause duplicates in the outer query.ID="IN3366"Correlated subqueriesRENDERAS="SECT2"Correlated Subqueries Using distinct and the inID="IN7867"distinctdistinctin subqueriessubqueries
PredicateREMAP="BO--Body"A correlated subquery introduced with the MOREINFO="NONE"in predicate and having a MOREINFO="NONE"select distinct in the subquery previously returned no rows. For example, using the pubs2 database:REMAP="UI--UserInput" FORMAT="LINESPECIFIC"select pub_name    from publishers    where pub_id in        (select distinct pub_id         from titles         where pub_id = publishers.pub_id)REMAP="BO--Body"This query returned no rows in pre-10.0 SQL Server. It now returns:ID="IN2268"SubqueriesREMAP="UI--UserInput" FORMAT="LINESPECIFIC" pub_name                                  ----------------------------------------  New Age Books                             Binnet & Hardley                          Algodata Infosystems   (3 rows affected)REMAP="BO--Body"NUMBER="3"Changes in SQL Server Releases 4.8, 4.9, and 4.9.1RENDERAS="SECT1"IntroductionREMAP="BO--Body"This chapter lists the changes to Releases 4.8, 4.9, and 4.9.1 of SQL Server. This information is provided mainly as a convenience for customers who have not upgraded to one of these earlier versions.RENDERAS="SECT1"Summary of Changes, SQL Server Release 4.8REMAP="BO--Body"This summary describes the significant changes in SQL Server Release 4.8.RENDERAS="SECT2"Fast create database for Database RecoveryID="IN841"for load option to create databaseREMAP="BO--Body"The new MOREINFO="NONE"for loadID="IN302"create database
option for the MOREINFO="NONE"create database command speeds up database creation for recovery from media failure or for moving databases. See BOOK="ref" LINKEND="X75308""create database"
in Volume 1 of the SQL Server Reference Manual, or the System Administration Guide.RENDERAS="SECT2"ID="IN493"union operator
union OperatorREMAP="BO--Body"Transact-SQL now provides the MOREINFO="NONE"union operator. It allows you to combine the results of two or more queries into a single result set. See BOOK="ref" LINKEND="X56548""union Operator"
in Volume 1 of the SQL Server Reference Manual.RENDERAS="SECT2"New "Not Equals" OperatorREMAP="BO--Body"The new negative comparison operator <> (not equal to) is the same as the != operator. See BOOK="ref" LINKEND="X20204""Expressions"
in Volume 1 of the SQL Server Reference Manual.RENDERAS="SECT2"New DatatypesID="IN534"smallmoney datatypeREMAP="BO--Body"smallmoneyID="IN565"smalldatetime datatype
, smalldatetime, and real are smaller versions of money, datetime, and float, respectively. They require 4 bytes of storage, rather than the 8 bytes required by their larger counterparts. See BOOK="ref" LINKEND="X84316""Datatypes"
in Volume 1 of the SQL Server Reference Manual.RENDERAS="SECT2"Quoted Strings in Column HeadingsREMAP="BO--Body"Except in MOREINFO="NONE"create table, MOREINFO="NONE"create view, and MOREINFO="NONE"select into ID="IN1510"Columnheadings
statements, column headings now can include any characters, including blanks and SQL Server keywords, if the column heading is enclosed in quotes.RENDERAS="SECT2"International FeaturesREMAP="BO--Body"SQL Server Release 4.8 contains all of the enhancements featured in   SQL Server Release 4.2, the International Release.   See the System Administration Guide and the System Administration Guide Supplement for your platform, the indexed entries in SQL Server Reference Manual, and Transact-SQL User's Guide, for more information on these features.RENDERAS="SECT3"System Tables for Alternate Language SupportID="IN5811"syscharsets system tableREMAP="BO--Body"SQL Server now supports multiple alternate languages simultaneously, through new tables, syscharsetsID="IN5912"syslanguages system table
and syslanguages, and new columns, langidID="IN5813"sysmessages system table
(sysmessages), language (ID="IN5614"syslogins system table
syslogins), csid, and soid (ID="IN5715"sysindexes system table
sysindexes).RENDERAS="SECT3"New Utility ProgramID="IN1916"Languagesalternate
ID="IN6217"langinstall utility programREMAP="BO--Body"The MOREINFO="NONE"langinstallID="IN2018"Languagesinstalling
utility program installs alternate languages on a server. See the SQL Server Utility Programs manual for your operating system.ID="IN1919"Alternate languagesRENDERAS="SECT3"Changed Utility ProgramsREMAP="BO--Body"MOREINFO="NONE"bcp, MOREINFO="NONE"defncopy, and MOREINFO="NONE"isql now support alternate languages and non-ISO 8859-1 terminals. The MOREINFO="NONE"consoleID="IN5820"console utility program
program, discontinued in Release 10.0, also supported alternative languages in earlier releases. See the SQL Server Utility Programs manual for your operating system.RENDERAS="SECT3"New Transact-SQL set Command OptionsREMAP="BO--Body"ID="IN2821"set datefirst
MOREINFO="NONE"datefirstID="IN2922"set dateformat
, MOREINFO="NONE"dateformatID="IN1723"Date input format
ID="IN2724"set language
, and MOREINFO="NONE"language options to the MOREINFO="NONE"set command change the first weekday, date input format, and alternate language in the current session. See BOOK="ref" LINKEND="X64136""set"
in Volume 1 of the SQL Server Reference Manual.ID="IN4825"sp_configuredefault sortorder id
ID="IN4526"sp_configurelanguage in cache
ID="IN4427"sp_configuredefault languageRENDERAS="SECT3"Configuration VariablesREMAP="BO--Body"See BOOK="ref" LINKEND="X60334""sp_configure"
in Volume 2 of the SQL Server Reference Manual for information about these new configuration options.RENDERAS="SECT3"New Global VariablesREMAP="BO--Body"@@language
is the name of the language currently in use. 
@@langid is the number of the language currently in use.ID="IN4532"System proceduressp_recompileRENDERAS="SECT3"New System ProceduresREMAP="BO--Body"ID="INA2940"sp_addlanguage
MOREINFO="NONE"sp_addlanguageID="INA3341"sp_defaultlanguage
, MOREINFO="NONE"sp_defaultlanguage, MOREINFO="NONE"sp_droplanguage, MOREINFO="NONE"sp_helplanguage, and ID="IN3042"sp_setlangalias
MOREINFO="NONE"sp_setlangalias
manage alternate languages.
ID="IN1743"Languagesaliases
ID="IN1444"Languageshelp
ID="IN1645"Default language
ID="IN1846"Languagesdropping
ID="IN1647"LanguagesaddingREMAP="BO--Body"ID="IN2848"sp_checknames
MOREINFO="NONE"sp_checknames reports names of objects that contain characters outside the 7-bit ASCII character set.REMAP="BO--Body"ID="IN2649"sp_helpsort
MOREINFO="NONE"sp_helpsortID="IN1850"Default sort order
ID="IN1551"Sort orderhelp
reports the server's default sort order.REMAP="BO--Body"ID="IN2752"sp_recompile
MOREINFO="NONE"sp_recompile ID="IN2953"Stored proceduresrecompiling
ID="IN2054"Triggersrecompiling
ID="IN2955"Recompiling stored procedures
recompiles a table's stored procedures and triggers.RENDERAS="SECT3"Changed System ProceduresREMAP="BO--Body"ID="IN1656"Default language
ID="IN2657"sp_addlogin
MOREINFO="NONE"sp_addlogin now assigns a default language when adding a user.RENDERAS="SECT3"Alternate Sort Order SupportREMAP="BO--Body"SQL Server can now be installed with a sort order other than the standard binary sort.ID="IN3358"Symmetric multiprocessor featuresRENDERAS="SECT2"Symmetric Multiprocessor FeaturesREMAP="BO--Body"Release 4.8 of the SQL Server is explicitly designed to take full advantage of the capabilities of symmetric multiprocessor (SMP) systems. See BOOK="sag" LINKEND="X53199"Chapter 14,  "Managing Multiprocessor Servers"
, in the System Administration Guide and the System Administration Guide Supplement for your platform for more information.RENDERAS="SECT2"Changes to System TablesREMAP="BO--Body"A new system table, ID="IN5759"sysengines system table
sysengines, allows the System Administrator to monitor engines in the SMP environment.ID="IN6060"sysconfigures system tableREMAP="BO--Body"Four system tables, sysconfiguresID="IN5761"sysindexes system table
, sysindexesID="IN5562"syslocks system table
, syslocksID="IN5963"sysprocesses system table
, and sysprocesses, have been modified for SMP.RENDERAS="SECT2"New Built-In FunctionsID="IN2764"sp_spaceusedREMAP="BO--Body"Four new built-in Transact-SQL functions give information about page allocations to a table or index.MOREINFO="NONE" sp_spaceusedID="IN5265"data_pgs function
uses these functions to provide information about database space utilization. The functions are: MOREINFO="NONE"data_pgsID="IN5666"reserved_pgs function
, MOREINFO="NONE"reserved_pgs, ID="IN5267"used_pgs function
MOREINFO="NONE"used_pgsID="IN5068"rowcnt function
, and MOREINFO="NONE"rowcnt.RENDERAS="SECT2"Changes to Error Log FormatID="IN1069"Error logsREMAP="BO--Body"Error log entries now show the engine involved for each log entry.REMAP="BO--Body"The date format is now yy/mm/dd.REMAP="BO--Body"The time is now displayed in 24-hour format.REMAP="BO--Body"Seconds and hundredths of a second have been added to the time format. RENDERAS="SECT2"New Configuration VariablesREMAP="BO--Body"The following configuration variables were added in 4.8:REMAP="BO--Body"ID="IN7370"default sortorder configuration option
MOREINFO="NONE"default sortorder ID="IN4571"sp_configuredefault sortorderREMAP="BO--Body"ID="IN7272"default language configuration option
ID="IN4473"sp_configuredefault language
MOREINFO="NONE"default languageREMAP="BO--Body"ID="IN7374"language in cache configuration option
ID="IN4575"sp_configurelanguage in cache
MOREINFO="NONE"language in cache     REMAP="BO--Body"ID="IN7476"max online engines configuration option
ID="IN4677"sp_configuremax online engines
MOREINFO="NONE"max online enginesREMAP="BO--Body"ID="IN7478"min online engines configuration option
ID="IN4679"sp_configuremin online engines
MOREINFO="NONE"min online engines     REMAP="BO--Body"See BOOK="ref" LINKEND="X60334""sp_configure"
in Volume 2 of the SQL Server Reference Manual or the System Administration Guide for more information.RENDERAS="SECT1"Summary of Changes, SQL Server Release 4.9REMAP="BO--Body"This summary lists the significant product changes for Release 4.9 of SQL Server: MARK="BULL" REMAP="BU--Bullet"A wide set of changes to support multibyte character setsNew options to the MOREINFO="NONE"dbcc commandID="IN2480"Multibyte character setsRENDERAS="SECT2"Multibyte Character Set FeaturesREMAP="BO--Body"SQL Server now supports multibyte character sets, including EUC-JIS, SHIFT-JIS, and DEC-Kanji, for use in Asian installations. The following changes and features were introduced to provide flexible language support, but some of them, such as the MOREINFO="NONE"print/raiserror changes and the message-related system procedures, have much wider applications for SQL Server users.ID="IN2481"Character setconversionRENDERAS="SECT3"Character Set ConversionREMAP="BO--Body"SQL Server supports conversion among a variety of character sets. Character set conversion is initiated using MOREINFO="NONE"setID="IN3182"set char_convert
MOREINFO="NONE"char_convert within a session or by using the MOREINFO="NONE"-J flag with the utilities MOREINFO="NONE"bcp, MOREINFO="NONE"defncopy, and MOREINFO="NONE"isql.RENDERAS="SECT3"Changing Sort Orders and Character SetsREMAP="BO--Body"The System Administrator can now change the default, installed sort order, or character set used by SQL Server. See the System Administration Guide Supplement for your platform.RENDERAS="SECT3"New DatatypesID="IN4883"nchar datatypeREMAP="BO--Body"The national character datatypes nchar
and nvarchar
allow a user to define a column length as containing a specific number of multibyte characters. The number of bytes in a character is contained in the new global variable MOREINFO="NONE"@@ncharsize. See BOOK="ref" LINKEND="X84316""Datatypes"
in Volume 1 of the SQL Server Reference Manual.RENDERAS="SECT3"New System TableID="IN6289"sysusermessages system tableREMAP="BO--Body"The table MOREINFO="NONE"sysusermessages, in all databases, stores user-defined messages for user stored procedures. Messages can be added to this table with the new MOREINFO="NONE"sp_addmessage system procedure and dropped with MOREINFO="NONE"sp_dropmessage. Messages can be retrieved with MOREINFO="NONE"sp_getmessage. Combined with the enhancements to MOREINFO="NONE"print and MOREINFO="NONE"raiserror described below, application designers can now easily make their stored procedures produce error messages in a user's default language.RENDERAS="SECT3"Enhancements to print and raiserror REMAP="BO--Body"ID="IN5090"print arguments
MOREINFO="NONE"printID="IN5491"raiserrorarguments
and MOREINFO="NONE"raiserror now recognize formatted output with arguments. Format strings can contain up to 20 unique place holders in any order. To allow reordering of the arguments when format strings are translated to a language with a different grammatical structure, the place holders are numbered. See BOOK="ref" LINKEND="X69372""print"
or BOOK="ref" LINKEND="X60573""raiserror"
in Volume 1 of the SQL Server Reference Manual.RENDERAS="SECT3"Changed String FunctionREMAP="BO--Body"ID="IN2392"patindex
MOREINFO="NONE"patindex string function now returns a value in bytes or in characters representing the starting position of the specified character expression. See BOOK="ref" LINKEND="X22985""String Functions"
in Volume 1 of the SQL Server Reference ManualRENDERAS="SECT3"New FunctionsREMAP="BO--Body"ID="IN2593"valid_name
MOREINFO="NONE"valid_name determines whether a string is legal for use in an identifier.REMAP="BO--Body"ID="IN2694"char_length
MOREINFO="NONE"char_length determines the number of characters in a character expression.REMAP="BO--Body"These functions are documented under "String Functions" in Volume 1 of the SQL Server Reference Manual.RENDERAS="SECT3"New Global VariablesREMAP="BO--Body"MOREINFO="NONE"@@char_convert
indicates whether character set conversion is in effect.REMAP="BO--Body"MOREINFO="NONE"@@client_csname
contains the client's character set name.MOREINFO="NONE"@@client_csid contains the client's character set ID.     REMAP="BO--Body"MOREINFO="NONE"@@maxcharlenID="IN31103"Multibyte character setslength
contains the maximum length of a multibyte character in SQL Server's default character set.REMAP="BO--Body"MOREINFO="NONE"@@ncharsize contains the average length, in bytes, of a national character.RENDERAS="SECT3"Configuration VariablesREMAP="BO--Body"The configuration variable ID="IN82106"default character set id configuration variable
ID="IN21107"Character set default
ID="IN21108"Default character set
MOREINFO="NONE"default character set MOREINFO="NONE"id specifies the number of the default character set SQL Server uses. See BOOK="ref" LINKEND="X60334""sp_configure"
in Volume 2 of the SQL Server Reference Manual or BOOK="sag" LINKEND="X26541"Chapter 12,  "Fine-Tuning Performance and Operations"
, in the System Administration Guide for more information.RENDERAS="SECT3"New System ProceduresREMAP="BO--Body"ID="IN28109"sp_indsuspect
MOREINFO="NONE"sp_indsuspectID="IN30110"Sort order changesand indexes
ID="IN15111"Suspect indexes
determines whether an index needs to be rebuilt after a sort order change.REMAP="BO--Body"ID="IN28112"sp_addmessage
MOREINFO="NONE"sp_addmessageID="IN28113"sp_getmessage
, MOREINFO="NONE"sp_getmessageID="IN21114"User-defined messages
ID="IN29115"sp_dropmessage
, and MOREINFO="NONE"sp_dropmessage manage user-defined messages for use in stored procedures. RENDERAS="SECT2"dbcc Command EnhancementsREMAP="BO--Body"MOREINFO="NONE"dbcc (Database Consistency Checker) has two new options for use when SQL Server's sort order or character set is changed: MOREINFO="NONE"dbcc reindex andMOREINFO="NONE" dbcc fix_text. MOREINFO="NONE"dbcc reindex checks and rebuilds character indexes which may have been invalidated by a sort order change.MOREINFO="NONE" dbcc fix_text calculates the statistics needed to manage text values after changing to a multibyte character set.RENDERAS="SECT1"Summary of Changes, SQL Server Release 4.9.1REMAP="BO--Body"This summary lists the significant product changes for Release 4.9.1 of SQL Server. Release 4.9.1 is a maintenance release.RENDERAS="SECT2"dbcc Command EnhancementsREMAP="BO--Body"MOREINFO="NONE"dbccID="IN30116"dbcc tablealloc
(Database Consistency Checker) has two new options to use when checking database integrity: MOREINFO="NONE"dbcc tablealloc andID="IN30117"dbcc indexalloc
MOREINFO="NONE" dbcc indexalloc. They perform the same checks as MOREINFO="NONE"dbcc checkalloc but on individual tables and indexes. These options provide faster consistency checking, and can automatically correct certain kinds of consistency problems. For more information see BOOK="sag" LINKEND="X57130"Chapter 11,  "Diagnosing System Problems"
in the System Administration Guide.RENDERAS="SECT2"New System ProceduresREMAP="BO--Body"ID="IN24118"sp_syntax
MOREINFO="NONE"sp_syntax displays the syntax of Transact-SQL statements, system procedures, utilities, and DB-Libraryname='trade' font='symbol' code='228'  charset='fontspecific' routines. (For Release 10.0, Client Library is also included.) See the System Administration Guide Supplement for information on installing the sybsyntax database and Volume 2 of the SQL Server Reference Manual for information on using the MOREINFO="NONE"sp_syntax system procedure.REMAP="BO--Body"Twelve new system procedures allow users easy access to information about database gateways as well as SQL Server. These stored procedures are compatible with the catalog interface for the Open Database Connectivity (ODBC) API. The procedures are: MOREINFO="NONE"sp_column_privilegesID="IN25119"Catalog stored procedures
, MOREINFO="NONE"sp_columns, MOREINFO="NONE"sp_databases, MOREINFO="NONE"sp_datatype_info, MOREINFO="NONE"sp_fkeys, MOREINFO="NONE"sp_pkeys, MOREINFO="NONE"sp_server_info, MOREINFO="NONE"sp_sproc_columns, MOREINFO="NONE"sp_statistics, MOREINFO="NONE"sp_stored_procedures, MOREINFO="NONE"sp_table_privileges, MOREINFO="NONE"sp_tables. See Volume 2 of the SQL Server Reference Manual for information on using these procedures.RENDERAS="SECT2"Configuration VariableID="IN68120"stack size configuration variableREMAP="BO--Body"The new configuration variable ID="IN38121"sp_configurestack size
MOREINFO="NONE"stack size specifies the size of SQL Server's stack. See Volume 2 of the SQL Server Reference Manual or BOOK="sag" LINKEND="X26541"Chapter 12,  "Fine-Tuning Performance and Operations"
, in the System Administration Guide.REMAP="BO--Body"REMAP="BO--Body"