| |
Home |
![[Table of Contents]](/dwicons/b_toc.gif)
|
What's New in SYBASE SQL Server Release 10.0? |
![[-]](/dwicons/i_colpse.gif)
| Chapter 3: Changes in SQL Server Releases 4.8, 4.9, and 4.9.1 |
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.
This summary describes the significant changes in SQL Server Release 4.8.
The new for load option for the create database command speeds up database creation for recovery from media failure or for moving databases. See "create database" in Volume 1 of the SQL Server Reference Manual , or the System Administration Guide .
Transact-SQL now provides the union operator. It allows you to combine the results of two or more queries into a single result set. See "union Operator" in Volume 1 of the SQL Server Reference Manual.
The new negative comparison operator <> (not equal to) is the same as the != operator. See "Expressions" in Volume 1 of the SQL Server Reference Manual.
smallmoney , 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 "Datatypes" in Volume 1 of the SQL Server Reference Manual .
Except in create table , create view , and select into statements, column headings now can include any characters, including blanks and SQL Server keywords, if the column heading is enclosed in quotes.
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.
SQL Server now supports multiple alternate languages simultaneously, through new tables, syscharsets and syslanguages , and new columns, langid ( sysmessages ), language ( syslogins ), csid , and soid ( sysindexes ).
The langinstall utility program installs alternate languages on a server. See the SQL Server Utility Programs manual for your operating system.
bcp , defncopy , and isql now support alternate languages and non-ISO 8859-1 terminals. The console program, discontinued in Release 10.0, also supported alternative languages in earlier releases. See the SQL Server Utility Programs manual for your operating system.
datefirst , dateformat , and language options to the set command change the first weekday, date input format, and alternate language in the current session. See "set" in Volume 1 of the SQL Server Reference Manual .
See "sp_configure" in Volume 2 of the SQL Server Reference Manual for information about these new configuration options.
@@language is the name of the language currently in use. @@langid is the number of the language currently in use.
sp_addlanguage , sp_defaultlanguage , sp_droplanguage , sp_helplanguage , and sp_setlangalias manage alternate languages.
sp_checknames reports names of objects that contain characters outside the 7-bit ASCII character set.
sp_helpsort reports the server's default sort order.
sp_recompile recompiles a table's stored procedures and triggers.
sp_addlogin now assigns a default language when adding a user.
SQL Server can now be installed with a sort order other than the standard binary sort.
Release 4.8 of the SQL Server is explicitly designed to take full advantage of the capabilities of symmetric multiprocessor (SMP) systems. See Chapter 14, "Managing Multiprocessor Servers" , in the System Administration Guide and the System Administration Guide Supplement for your platform for more information.
A new system table, sysengines , allows the System Administrator to monitor engines in the SMP environment.
Four system tables, sysconfigures , sysindexes , syslocks , and sysprocesses , have been modified for SMP.
Four new built-in Transact-SQL functions give information about page allocations to a table or index. sp_spaceused uses these functions to provide information about database space utilization. The functions are: data_pgs , reserved_pgs , used_pgs , and rowcnt .
Error log entries now show the engine involved for each log entry.
The date format is now yy/mm/dd.
The time is now displayed in 24-hour format.
Seconds and hundredths of a second have been added to the time format.
The following configuration variables were added in 4.8:
default sortorder
default language
language in cache
max online engines
min online engines
See "sp_configure" in Volume 2 of the SQL Server Reference Manual or the System Administration Guide for more information.
This summary lists the significant product changes for Release 4.9 of SQL Server:
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 print/raiserror changes and the message-related system procedures, have much wider applications for SQL Server users.
SQL Server supports conversion among a variety of character sets. Character set conversion is initiated using set char_convert within a session or by using the -J flag with the utilities bcp , defncopy , and isql .
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.
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 @@ncharsize . See "Datatypes" in Volume 1 of the SQL Server Reference Manual.
The table sysusermessages , in all databases, stores user-defined messages for user stored procedures. Messages can be added to this table with the new sp_addmessage system procedure and dropped with sp_dropmessage . Messages can be retrieved with sp_getmessage. Combined with the enhancements to print and raiserror described below, application designers can now easily make their stored procedures produce error messages in a user's default language.
print and 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 "print" or "raiserror" in Volume 1 of the SQL Server Reference Manual .
patindex string function now returns a value in bytes or in characters representing the starting position of the specified character expression. See "String Functions" in Volume 1 of the SQL Server Reference Manual
valid_name determines whether a string is legal for use in an identifier.
char_length determines the number of characters in a character expression.
These functions are documented under "String Functions" in Volume 1 of the SQL Server Reference Manual.
@@char_convert indicates whether character set conversion is in effect.
@@client_csname
contains the client's character set name.
@@client_csid contains the client's character set ID.
@@maxcharlen contains the maximum length of a multibyte character in SQL Server's default character set.
@@ncharsize contains the average length, in bytes, of a national character.
The configuration variable default character set id specifies the number of the default character set SQL Server uses. See "sp_configure" in Volume 2 of the SQL Server Reference Manual or Chapter 12, "Fine-Tuning Performance and Operations" , in the System Administration Guide for more information.
sp_indsuspect determines whether an index needs to be rebuilt after a sort order change.
sp_addmessage , sp_getmessage , and sp_dropmessage manage user-defined messages for use in stored procedures.
dbcc (Database Consistency Checker) has two new options for use when SQL Server's sort order or character set is changed: dbcc reindex and dbcc fix_text . dbcc reindex checks and rebuilds character indexes which may have been invalidated by a sort order change. dbcc fix_text calculates the statistics needed to manage text values after changing to a multibyte character set.
This summary lists the significant product changes for Release 4.9.1 of SQL Server. Release 4.9.1 is a maintenance release.
dbcc (Database Consistency Checker) has two new options to use when checking database integrity: dbcc tablealloc and dbcc indexalloc . They perform the same checks as 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 Chapter 11, "Diagnosing System Problems" in the System Administration Guide .
sp_syntax displays the syntax of Transact-SQL statements, system procedures, utilities, and DB-Libraryä 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 sp_syntax system procedure.
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: sp_column_privileges , sp_columns , sp_databases , sp_datatype_info , sp_fkeys , sp_pkeys , sp_server_info , sp_sproc_columns , sp_statistics , sp_stored_procedures , sp_table_privileges , sp_tables . See Volume 2 of the SQL Server Reference Manual for information on using these procedures.
The new configuration variable stack size specifies the size of SQL Server's stack. See Volume 2 of the SQL Server Reference Manual or Chapter 12, "Fine-Tuning Performance and Operations" , in the System Administration Guide.
|
|