Breaking Changes to Database Engine Features in SQL Server 2005

Banyan VINES Sequenced Packet Protocol (SPP), Multiprotocol, AppleTalk, or NWLink IPX/SPX network protocols

SQL Server 2005 does not support the Banyan VINES Sequenced Packet Protocol (SPP), Multiprotocol, AppleTalk, or NWLink IPX/SPX network protocols. To connect to SQL Server 2005, client applications must use a supported protocol. If an alias is set up that uses one of the unsupported protocols, the alias must be modified to use one of the supported protocols.

If an application connection string specifically uses or loads one of the unsupported protocols, by either specifying NETWORK=DBMSRPCN for RPC, NETWORK=DBMSADSN for Appletalk, or NETWORK=DBMSVINN for Banyan VINES property, or by using an explicit prefix such as spx:server\instance for SPX, bv:server for Banyan VINES, adsp:server for AppleTalk, or rpc:server for multiprotocol, then you must modify your application to use one of the supported protocols.

For more information, see Choosing a Network Protocol.

Winsock proxy

The Winsock proxy cannot be configured by using SQL Server tools. For information about how to configure the Winsock proxy, see the proxy server documentation.

Compressed drives

SQL Server 2005 cannot create or upgrade databases on compressed drives. When you install SQL Server 2005, select an uncompressed drive for system databases and verify that databases to be upgraded are not on compressed drives. However, note that after the database has been upgraded, you can put read-only databases and read-only secondary filegroups on an NTFS compressed file system.

Database compatibility mode

When a database is upgraded to SQL Server 2005 from any earlier version of SQL Server, the database retains its existing compatibility level. If you change the compatibility mode to 90 after you upgrade, the differences in compatibility mode may affect your applications. For more information about these differences, see sp_dbcmptlevel (Transact-SQL).

Filegroups

All databases in the instance of SQL Server must have filegroups set to READ_WRITE before you upgrade to SQL Server 2005. To set the filegroup to READ_WRITE, use ALTER DATABASE.

model database

In SQL Server 2005, the model database contains the following changes:

• Larger minimum size.
  
• Compatibility level is set to 90.
  
• PAGE_VERIFY database option is set to CHECKSUM.
  

Function determinism

The following function expressions are considered nondeterministic in SQL Server 2005 and, therefore, may interfere with indexed view creation:

• References to string literals that are implicitly converted to datetime and smalldatetime.
  
• Implicit conversion of non-Unicode character data between collations.
  

Expressions that involve implicit conversion of character strings to datetime or smalldatetime are considered nondeterministic in SQL Server 2005, unless the compatibility level is set to 80 or earlier. This is because the results depend on the LANGUAGE and DATEFORMAT settings of the server session. For example, the results of the expression CONVERT (datetime, '30 listopad 1996', 113) depend on the LANGUAGE setting, because the string 'listopad' means different months in different languages. Similarly, in the expression DATEADD(mm,3,'2000-12-01'), SQL Server interprets the string '2000-12-01' based on the DATEFORMAT setting.

Implicit conversion of non-Unicode character data between collations is also considered nondeterministic, unless the compatibility level is set to 80 or earlier.

Creating indexes on views that contain these expressions is not allowed in database compatibility level 90. Although existing views that contain these expressions from an upgraded database are maintainable, the query optimizer will not consider them in query plans in either compatibility level 80 or 90. For information about setting the compatibility level, see sp_dbcmptlevel (Transact-SQL).

In the definition of indexed views in SQL Server 2005, you should explicitly convert the literal to the preferred date type by using a deterministic date format style. For a list of which date format styles are deterministic, see CAST and CONVERT (Transact-SQL).

If you use implicit string-to-date conversions in existing indexed views that have been upgraded to SQL Server 2005, you should be certain that the LANGUAGE and DATEFORMAT settings are consistent in your databases and applications to avoid possible indexed view corruption.

Query hints

Query hints in the definitions of indexed views are ignored in compatibility level 80. This may cause some applications to behave differently between compatibility levels 80 and 90. For more information, see Designing Indexed Views, Creating Indexed Views, and Query Hint (Transact-SQL).

Login names

The following fixed server role names are reserved in SQL Server 2005 and cannot be used as user-defined login names:

• sysadmin
  
• serveradmin
  
• setupadmin
  
• securityadmin
  
• processadmin
  
• dbcreator
  
• diskadmin
  
• bulkadmin
  

Before you upgrade to SQL Server 2005, follow these steps:

1. Record the security identifiers (SIDs) of the logins by executing the following statement:
   

   SELECT name, sid 
   FROM master.dbo.syslogins 
   WHERE name IN('sysadmin', 'serveradmin','setupadmin',
    'securityadmin','processadmin', 'dbcreator','diskadmin',
    'bulkadmin')

2. Drop the logins.
   
3. To create new logins, use the sp_addlogin system procedure. In the @sid parameter for each corresponding login, specify the SID that is returned in step 1.
   

Remote login mapping

In earlier versions of SQL Server, logins that come from remote instances of SQL Server can be marked as trusted by using the sp_remoteoption system stored procedure. SQL Server 2005 does not support this method of labeling remote logins. After you upgrade to SQL Server 2005, remote logins will no longer be marked as trusted.

For setting up and managing remote logins, use linked servers and linked server stored procedures. For more information, see Linking Servers.

sys user name

The name sys is reserved in SQL Server 2005 and cannot be used as a user name. Rename the user before you upgrade to SQL Server 2005. If the user is not renamed, the database will be in a suspect state after the upgrade process and will be unavailable until the database is brought online.

Before-Upgrade Procedure

Before you upgrade to SQL Server 2005, in each database that contains user sys, do the following:

1. Create a new user.
   

2. Use the following statements to display all permissions that are granted by user sys and granted to user sys.
   

   -- Return permissions granted by user sys.
   SELECT * FROM sysprotects WHERE grantor = USER_ID('sys')
   -- Return permissions granted to user sys.
   SELECT * FROM sysprotects WHERE uid = USER_ID('sys')

3. To transfer ownership of all objects that are owned by sys to the new user, use sp_changeobjectowner.
   
4. Drop user sys.
   
5. To restore the original permissions captured in step 2, use the AS new_user clause of the GRANT statement.
   
6. Modify scripts to reference the new user.
   

After-Upgrade Procedure

If the user sys was not renamed before you upgraded, do the following:

1. Execute the statement ALTER DATABASE db_name SET ONLINE. The database will be in SINGLE_USER mode.
   
2. Follow all steps in the "Before-Upgrade Procedure" section.
   
3. Execute the statement ALTER DATABASE db_name SET MULTI_USER.
   

INFORMATION_SCHEMA.COLUMNS

In SQL Server 2005, the ORDINAL_POSITION column of the INFORMATION_SCHEMA.COLUMNS view is not compatible with the bit pattern that is returned by the COLUMNS_UPDATED function.

To obtain a bit pattern that is compatible with COLUMNS_UPDATED, reference the ColumnID property of the COLUMNPROPERTY system function when you query the INFORMATION_SCHEMA.COLUMNS view, as shown in the following example:

SELECT TABLE_NAME, COLUMN_NAME,
    COLUMNPROPERTY(OBJECT_ID(TABLE_SCHEMA + '.' + TABLE_NAME),
    COLUMN_NAME, 'ColumnID') AS COLUMN_ID
FROM AdventureWorks.INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'Contact';

INFORMATION_SCHEMA column names that match the value '%SCHEMA'

In earlier versions of SQL Server, INFORMATION_SCHEMA column names that match the value '%SCHEMA' return the name of the user. In SQL Server 2005, these columns return the schema name. When a database is upgraded to SQL Server 2005, the schema name is always the same as the user name and any applications that reference these columns will not fail. However, users that implement SQL Server 2005 user-schema separation features in their databases should realize that their application may fail if the expected data is a user name instead of a schema name.

For more information, see User-Schema Separation.

syslockinfo and sp_lock

In SQL Server 2000, the rsc_objid and rsc_indid columns in syslockinfo and the objid and indid columns in sp_lock consistently return the object ID and index ID. In SQL Server 2005, a value of 0 may be returned.

In SQL Server 2000, syslockinfo and sp_lock return a maximum of two rows for any specific lock resource in a single transaction. In SQL Server 2005, when lock partitioning is enabled, multiple rows for the same resource running under one transaction may be returned. There can be up to N + 1 rows returned, where N is the number of CPUs. Also, in SQL Server 2005, GRANTED and WAITING requests can be displayed for the same resource; in SQL Server 2000, these requests cannot be displayed for the same resource For more information, see sp_lock (Transact-SQL) and sys.syslockinfo (Transact-SQL).

System object modification

Direct system catalog updates are not allowed in SQL Server 2005. Any attempt to do this will generate the following error:

"Server: Msg 259, Level 16, State 1, Line 1"

"Ad hoc updates to system catalogs are not allowed."

Modify your SQL scripts to use official and documented APIs. For example, use ALTER DATABASE database_name SET EMERGENCY instead of running an UPDATE statement on the sysdatabases system table.

sysperfinfo

In SQL Server 2005, sysperfinfo returns a bigint value for the cntr_value column. Modify applications that use sysperfinfo to make sure that they can handle the bigint values of the cntr_value column.

In SQL Server 2005, sysperfinfo is a compatibility view. You should use the sys.dm_os_performance_counters dynamic management view instead.

@@VERSION

SQL Server 2005 returns more detailed information than SQL Server 2000, in the format major.minor.build.incremental-build.

DISK INIT

The DISK INIT statement, used in earlier versions of SQL Server to create database or transaction log devices, has been removed from SQL Server 2005. Replace all occurrences of this statement with equivalent CREATE DATABASE or ALTER DATABASE statements.

UPDATETEXT

SQL Server 2005 does not support using text pointers in UPDATETEXT statements that read and write to the same binary large objects (BLOB) by using the same text pointer. Copy the BLOB to a temporary table or to a table variable, and then reassign the value to the original column.

ORDER BY in a view definition

In SQL Server 2005, the ORDER BY clause in a view definition is used only to determine the rows that are returned by the TOP clause. The ORDER BY clause does not guarantee ordered results when the view is queried, unless ORDER BY is also specified in the query itself.

OPENXML

Because of changes to MSXML, OPENXML no longer supports noninteger positional predicates. In SQL Server 2005, MSXML 3.0 is the underlying engine that is used to process XPath expressions that are used within OPENXML queries. MSXML 3.0 has a more conformant XPath 1.0 engine in which the semantics of noninteger values in positional predicates has changed.

For example, the following as an XPath expression a[5.1] will now return no element instead of the fifth <a> element. To correct this, use the rounded value directly. For example, revise the previous example to a[5].

User-defined type 'xml'

In SQL Server 2005, xml is a reserved system type. Use sp_rename to rename the type either before or after you upgrade and modify the application to work with the new type name.

17 November 2008

New content:

• Added an entry under the Features section about column collation matching in table variables.