Behavior Changes to Database Engine Features in SQL Server 2005

This topic describes the changes in behavior of some Database Engine features in Microsoft SQL Server 2005 from their behavior in earlier versions of SQL Server.

Backup and Recovery

When you restore an existing database, SQL Server 2005 requires that you back up the tail of the log before you restore the database under the full or bulk-logged recovery model. Trying to restore a database before you back up the tail of the log causes an error, unless the RESTORE statement contains either a WITH REPLACE or WITH STOPAT clause. For more information, see Tail-Log Backups.

Cursors

The following table lists the implicit cursor conversions that occur in SQL Server 2000 but do not occur in SQL Server 2005. The workaround is to request the specific type of cursor, instead of depending on implicit conversion.

Condition	In SQL Server 2000, cursor is converted from	To
Query contains inline table-valued function.	Dynamic/keyset	Static
Query references a remote object.	Fast-forward	Keyset
Query contains no tables.	Fast-forward	Static
Query contains table function.	Fast-forward	Static
Query contains derived table.	Fast-forward	Static
Query contains multi-statement table function.	Dynamic Keyset	Keyset to Static Static
Query contains a virtual table.	Dynamic/Keyset	Static
Query contains a view with TOP.	Fast-forward	Static
Query contains updatable partitioned views.	Fast-forward	Static
API cursor query contains an indexed view with a unique access path, but not at the base-table level.	Keyset	Static
API cursor query contains a table with no clustered index or unique key.	Keyset	Static
API cursor result set contains a text, ntext, or image column.	Fast-forward	Dynamic

SQL Server 2005 does not support generating keyset-driven or static Transact-SQL cursors asynchronously. Transact-SQL cursor operations, such as OPEN or FETCH, are ordinarily batched; therefore, there is no need for the asynchronous generation of Transact-SQL cursors. SQL Server 2005 continues to support asynchronous keyset-driven or static API server cursors where low-latency OPEN is a concern because of client roundtrips for each cursor operation.

In SQL Server 2005, when a dynamic cursor is declared over a table with no unique indexes, and a row is deleted outside the cursor, a subsequent refresh of the cursor retrieves a placeholder for the row with NULL data. In earlier versions of SQL Server, the cursor does not return the affected row.

In SQL Server 2005, if different cursor declarations are possible through conditional logic within a batch or stored procedure, INSERT, UPDATE, and DELETE statements executed through the cursor cause a recompile. In SQL Server 2000, these statements do not cause a recompile.

In the following example, the UPDATE statement will cause a recompile of the module.

IF(@some_condition=1)
   DECLARE c CURSOR FOR 
      SELECT region FROM db.dbo.mytable ORDER BY lastname
ELSE
   DECLARE c CURSOR FOR 
      SELECT postalcode FROM db.dbo.mytable ORDER BY firstname
...
FETCH NEXT FROM c
...
UPDATE db.dbo.mytable 
   SET  firstname='a' WHERE CURRENT OF c

To avoid recompilation, rewrite the code in the following way:

IF (@some_condition=1)
BEGIN
   DECLARE c CURSOR FOR 
     SELECT region FROM db.dbo.mytable ORDER BY lastname
   FETCH NEXT FROM c
   UPDATE db.dbo.mytable 
     SET  firstname='a' WHERE CURRENT OF c
END
ELSE
BEGIN
  DECLARE c CURSOR FOR 
    SELECT postalcode FROM db.dbo.mytable ORDER BY firstname
  FETCH NEXT FROM c
  UPDATE db.dbo.mytable 
    SET  firstname='a' WHERE CURRENT OF c
END

In SQL Server 2005, cursor scroll locks acquire a lock upgrade from S (Shared) to U (Update), unless a higher lock hint is specified in the query. SQL Server 2000 transparently adds a U lock hint for scroll lock cursors. The SQL Server 2005 behavior allows for better concurrency but opens up the chance of deadlocks (message 1205) for concurrent cursors. Use the UPDLOCK hint to achieve the behavior you want. For more information, see Table Hint (Transact-SQL).

In SQL Server 2000, when a column or row value that appears more than one time in a cursor fetch buffer is updated, the other occurrences might not reflect the update because of the size of the fetch buffer or the position of the rows within the buffer. In SQL Server 2005, the rows in the fetch buffer are updated so that the values are always consistent, regardless of the fetch buffer size or the position of the rows.

In SQL Server 2000, cursors that involve a UNION ALL statement incorrectly return updatability information based on the first set in the UNION. Therefore, some cursors are reported as updatable when they are not. SQL Server 2005 reports UNION ALL results as computed and, therefore, not updatable. If all cursor rows come from a single table or view, you can define a cursor that is updatable by changing the query to use IN or OR clauses, instead of UNION.

In SQL Server 2000, when the CURSOR_CLOSE_ON_COMMIT option is set to ON, all cursors in a connection are closed when the transaction is committed. In SQL Server 2005, only cursors that are opened within the current transaction are closed at transaction commit time. Cursors that are opened before the start of the transaction remain open.

SQL Server 2000 allows some cursor declarations that are not valid and converts them to other types of cursors. SQL Server 2005 does not allow declarations that are not valid. For example, a cursor that requests both updatability and insensitivity is not valid in SQL Server 2005 and fails with an error.

Databases, Data and Log Files

SQL Server 2000 behavior	SQL Server 2005 behavior
The AUTO_CLOSE database option is a synchronous process that can decrease performance when the database is accessed by an application that repeatedly makes and breaks connections to the Database Engine.	The AUTO_CLOSE process is asynchronous. Repeatedly opening and closing the database no longer impairs performance.
Files on raw partitions do not autogrow; therefore, the MAXSIZE and FILEGROWTH parameters are not required when os_file_name specifies a raw partition.	Files on raw partitions can autogrow. The MAXSIZE and FILEGROWTH parameters can be specified.
Permissions are not set on the data and log files of each database.	Permissions are set on the data and log files whenever the following operations are applied to the database: CreatedModified to add a new file AttachedBacked up DetachedRestored

The permissions prevent the files from being accidentally tampered with if they reside in a directory that has open permissions. For more information, see Securing Data and Log Files.

Note:

Microsoft SQL Server 2005 Express Edition does not set data and log file permissions.

Linked Servers and Distributed Queries

A linked server that was defined by using 'SQLOLEDB' as the provider name will be modified to 'SQLNCLI' (SQL Native Client OLE DB Provider) upon upgrade. The sys.sysservers compatibility view shows linked servers that use 'SQLNCLI' as 'SQLOLEDB'. The SQL Server 2005 catalog view sys.servers shows linked servers that use 'SQLNCLI' as 'SQLNCLI'.

Heterogeneous queries and the use of OLE DB providers are not supported when SQL Server is running in fiber mode. Fiber mode is enabled when the lightweight pooling advanced configuration option is set to 1.

The SQL Native Client OLE DB Provider cannot be instantiated out-of-process.

Warning messages from a linked server in SQL Server 2005 are not propagated to the client. The most significant classes of such warnings are the following:

• ANSI-compliant warnings that NULL values have been eliminated in an aggregate calculation
• Arithmetic overflow warnings

For example, the following Transact-SQL statement might generate a warning message if there are NULL values in col1:

SELECT SUM(col1)
FROM <Table>
GROUP BY col2 

In SQL Server 2000 and earlier, linked servers propagate the warning message to the client. In SQL Server 2005, linked servers do not.

In SQL Server 2000, when a remote stored procedure does not run successfully because of compile-time errors such an incorrect parameter binding, the return value/status is set to 0. When this scenario occurs in SQL Server 2005, the return value/status is set to NULL.

Query Processor Architecture

Using parameter values during recompilation is different between SQL Server 2000 and SQL Server 2005 for batches submitted in the following forms:

• Stored procedures
• Using sp_executesql
• Prepared statements

When SQL Server 2000 recompiles these batches, it uses the parameter values with which the batches are called as part of recompilation. When SQL Server 2005 recompiles these queries, it uses the parameter values as they exist just before the statement that causes recompilation. These values may differ from those that were originally passed into the batch. For more information, see Parameters and Execution Plan Reuse.

Security

Feature	SQL Server 2000 behavior	SQL Server 2005 behavior
GRANT ALL	Grants all applicable permissions.	Granting ALL permission on objects and statements has been deprecated. When GRANT ALL is executed, the following occurs: The command succeeds, but only permissions grantable in SQL Server 2000 are granted to the user. You receive the following message: "The ALL permission is deprecated and maintained only for compatibility. It DOES NOT imply ALL permissions defined on the entity." SQL Server 2005 provides additional permissions at various scopes that can be used to manage user permissions. For example, the CONTROL permission can be used to grant ownership-like permissions on an object.
Password comparisons	SQL Server 2000 maintains two versions of each SQL Server login password. One is the actual password supplied by the user, and the other is the password converted by SQL Server to all uppercase letters. This enables case-insensitive validation of passwords. Although this behavior is convenient for many users, it makes password-guessing attacks easier by reducing the number of possible passwords.	Only the actual password is stored. A password entered by a user must match the password stored in the server. If a password does not match the password stored in SQL Server, the login fails. If the precise case of the password characters is forgotten, the password must be reset.
Changing the default language for the sa account	The default language for the SQL Server sa account is the language that is selected during setup or upgrade. In earlier releases of Microsoft SQL Server, running sp_configure to change the default language of the server also updates the default language for the sa account.	To change the default language for the sa account in SQL Server 2005, you must run the sp_defaultlanguage stored procedure, execute the DBCC FREESYSTEMCACHE command, and then start a new session. Running sp_configure to change the default language for the server does not update the default language for the sa account.

System Stored Procedures

The following tables list the changes to parameters in the Database Engine system stored procedures.

Stored procedure	Parameter	Change description
sp_bindefault	@objname	Size changed from nvarchar(517) to nvarchar(776).
sp_bindrule	@objname	Size changed from nvarchar(517) to nvarchar(776).
sp_changeobjectowner	@objname	Size changed from nvarchar(517) to nvarchar(776).
sp_detach_db	@keepfulltextindexfile	Size changed from nvarchar(517) to nvarchar(776).
sp_fulltext_service	@action	Size changed from varchar(20) to nvarchar(100).
sp_fulltext_service	@value	Data type changed from int to sql_variant.
sp_getapplock	@DbPrincipal	Parameter added.
sp_releaseapplock	@DbPrincipal	Parameter added.
sp_setapprole	@fCreateCookie	Parameter added.
sp_setapprole	@cookie	Parameter added.
sp_settriggerorder	@stmttype	Size changed from varchar(10) to varchar(50).
sp_settriggerorder	@namespace	Parameter added.
sp_sproc_columns	@fUsePattern	Parameter added.
sp_stored_procedures	@fUsePattern	Parameter added.
sp_table_privileges	@fUsePattern	Parameter added.
sp_table_privileges_ex	@fUsePattern	Parameter added.
sp_tables	@fUsePattern	Parameter added.
sp_tables_ex	@fUsePattern	Parameter added.

System Tables and Views

The following table lists the changes to the columns in system tables and views.

System table or view	Column	Change description
COLUMNS	ORDINAL_POSITION	Data type changed from smallint to int.
PARAMETERS	ORDINAL_POSITION	Data type changed from smallint to int.
REFERENTIAL_CONSTRAINTS	MATCH_OPTION	Size changed from varchar(4) to varchar(7). Default value changed from 'NONE' to 'SIMPLE'.
REFERENTIAL_CONSTRAINTS	UPDATE_RULE	Size changed from varchar(9) to varchar(11).
REFERENTIAL_CONSTRAINTS	DELETE_RULE	Size changed from varchar(9) to varchar(11).
ROUTINE_COLUMNS	ORDINAL_POSITION	Data type changed from smallint to int.
sysaltfiles	name	Data type changed from nchar(128) to sysname.
sysaltfiles	filename	Data type changed from nchar(260) to nvarchar(260).
sysconfigures	config	Data type changed from smallint to int.
syscursorcolumns	data_type_sql	Data type changed from smallint to int.
sysfiles	name	Data type changed from nchar(128) to sysname.
sysfiles	filename	Data type changed from nchar(260) to nvarchar(260).
sysmessages	severity	Data type changed from smallint to tinyint.
sysperfinfo	cntr_value	Data type changed from int to bigint.
sysprocesses	waittime	Data type changed from int to bigint.
sysprocesses	hostprocess	Size changed from nchar(8) to nchar(10).
sysprocesses	request_id	Column added.
sysprotects	columns	Size changed from varbinary(4000) to varbinary(8000).
sysservers	srvcollation	Data type changed from int to sysname.
sysservers	nonsqlsub	Column added.
sysoledbusers	rmtpassword	Returns only NULL.
sysindexes	keys	Returns only NULL.
sysindexes	statblob	Returns only NULL.
syscomments	compressed	Returns only 0.
sysdevices	size	Returns only 0.
sysobjects	schema_ver	Returns only 0.
sysremotelogins	status	Returns only 0.
sysservers	topologyx	Returns only 0.
sysservers	topologyy	Returns only 0.

Transact-SQL

Feature	SQL Server 2000 behavior	SQL Server 2005 behavior
bcp utility	A user that has INSERT and SELECT permissions on a table can use the bcp utility to bulk load data into that table by using the following command: bcp <target table> in <datafile> -c -T By default, this command disables CHECK constraints and triggers on target tables.	To run the bcp utility, a user must have ALTER permission and INSERT and SELECT permissions on target tables if CHECK constraints and triggers are disabled during the bulk copy process. After you upgrade to SQL Server 2005, bcp commands in applications may fail because of insufficient permissions. This issue can be handled in one of the following ways: Grant ALTER TABLE permission to the user on all tables that are affected by the bcp process. Modify the bcp command to explicitly enforce CHECK constraints and triggers, as shown in the following command: bcp <target table> in <datafile> -c -T -h "CHECK_CONSTRAINTS, FIRE_TRIGGERS"
Built-in system functions	Each reference to built-in functions, such as NEWID and RAND, produces a different result because it is evaluated one time for each outer query reference.	An outer query can make multiple references to columns of views or derived tables. However, if those columns are defined by calling functions such as NEWID and RAND, these multiple references cause the function to be evaluated only one time for each actual call inside the view or derived table. Multiple references to such columns in a subquery do not cause the functions to be evaluated multiple times. This lets you reuse the value produced by these functions in the subquery. For example, in SQL Server 2000, the following query returns two different values. In SQL Server 2005, it returns a single value. SELECT Column1, Column1       FROM (             SELECT RAND() Column1             FROM (             SELECT 1 c             UNION             SELECT 2 c                   ) s             ) t
BULK INSERT	BULK INSERT supports string-to-decimal type conversion for strings representing numeric values that use scientific notation.	The string-to-decimal type conversions used in BULK INSERT follow the same rules as the Transact-SQL CONVERT function. This function rejects strings representing numeric values that use scientific notation. Therefore, BULK INSERT treats such strings as values that are not valid and reports conversion errors. For more information, see BULK INSERT (Transact-SQL).
datetime conversions	String-to-datetime conversions are marked as deterministic. However, this is not true for the styles listed in the following table. For these styles, the conversions depend on the language settings. The following table lists the styles for which the string-to-datetime conversion is nondeterministic. All styles below 1001106 107109 113130

1 With the exception of styles 20 and 21

SQL Server 2005 marks string-to-datetime conversions as nondeterministic.

DBCC CHECKFILEGROUP

If a nonclustered index in the specified filegroup is associated with a table in another filegroup, the index and the base table in the other filegroup are checked.

If a nonclustered index in the specified filegroup is associated with a table in another filegroup, the index is not checked because the base table is not available for validation.

DBCC SHOW_STATISTICS

The rowset returned by DBCC SHOW_STATISTICS does not contain a Name column.

The first rowset returned by DBCC SHOW_STATISTICS contains an additional column that is titled Name. This column appears as the first column in the result set. If you have applications that access the columns returned by DBCC SHOW_STATISTICS by ordinal position, modify them to access the columns by name.

DROP LOGIN

When you execute DROP LOGIN, the login is not dropped if database users are mapped to the login.

When you execute DROP LOGIN, the login is dropped even if database users are mapped to the login.

Expressions in computed columns, CHECK constraints and DEFAULT constraints

The original text of an expression, including white spaces, is preserved in the catalog metadata. For example, a computed column expression entered as c1 + c2 + 1 will appear exactly as entered in the text column in the syscomments system table.

The original text of an expression is decoded and normalized and the output of this operation is stored in the catalog metadata. The semantics of the decoded expression will be equivalent to the original text; however, there are no syntactic guarantees. For example, a computed column expression entered as c1 + c2 + 1 will appear as (([c1]+[c2])+(1)) in the definition column in the sys.computed_columns system catalog view.

Expressions in queries

Unsafe expressions in queries do not always generate a run-time exception.

SQL Server 2005 sometimes evaluates expressions in queries sooner than when they are evaluated in SQL Server 2000. This behavior provides the following important benefits:

• The ability to match indexes on computed columns to expressions in a query that are the same as the computed column expression.
• The prevention of redundant computation of expression results.

However, depending on the nature of the query and the data in the database, run-time exceptions may occur in SQL Server 2005 if the query contains an existing unsafe expression. These run-time exceptions include the following:

• Arithmetic exceptions: zero-divide, overflow, and underflow.
• Conversion failures such as loss of precision and an attempt to convert a nonnumeric string to a number.
• Aggregation over a set of values that are not all guaranteed to be non-null.

These exceptions may not occur in SQL Server 2000 in a specific application that uses specific data. However, a query-plan that is changed because of changing statistics might potentially lead to an exception in SQL Server 2000. These run-time exceptions can be prevented by modifying the query to include conditional expressions such as NULLIF or CASE. For more information, see Troubleshooting Errors and Warnings on Query Expressions.

fn_servershareddrives

Data returned by the system function fn_servershareddrives can be viewed by members of the public role.

Permission change: fn_servershareddrives requires the user to have VIEW SERVER STATE permission on the server.

Important:

This SQL Server 2000 system function is included for backward compatibility. We recommend that you use sys.dm_io_cluster_shared_drives instead.

fn_virtualfilestats

Data returned by the system function fn_virtualfilestats can be viewed by members of the public role.

Permission change: fn_virtualfilestats requires the user to have VIEW SERVER STATE permission on the server.

fn_virtualservernodes

Data returned by the system function fn_virtualservernodes can be viewed by members of the public role.

Permission change: fn_virtualservernodes requires the user to have VIEW SERVER STATE permission on the server.

Important:

This SQL Server 2000 system function is included for backward compatibility. We recommend that you use sys.dm_os_cluster_nodes instead.

HOST_ID

HOST_ID returns a char(8) value.

HOST_ID returns a char(10) value.

Indexes

The indexes described in the "SQL Server 2005 behavior" column are allowed.

The following indexes may be disabled during the upgrade process or require rebuilding because of changes introduced in SQL Server 2005:

• Indexes on computed columns that use CHECKSUM(some_timestamp_column) will be disabled because the behavior has changed for the Transact-SQL CHECKSUM function when it takes a timestamp column as an argument.
• Indexes that contain the character values 0x3390, 0x33ca, or 0x33cb in nvarchar or nchar columns that use a Turkish-based collation may require rebuilding because the sorting behavior of these collations has changed.
• Indexes on computed columns or views in which the view or computed-column expression contains either an implicit conversion from string to datetime or smalldatetime, or an explicit, nondeterministic conversion from string to datetime or smalldatetime will be disabled.

For indexes that require rebuilding because of the changes described in the first two items described earlier, use the following procedure.

1. Examine the SQL Server error log for warning messages 3801, 3803, or 3804.

2. Run DBCC CHECKTABLE on the underlying table to verify there is a problem.

3. If the results of the DBCC statement indicate a problem exists, rebuild the index by using one of the following methods:

   • ALTER INDEX statement with the REBUILD clause
   • CREATE INDEX with the DROP_EXISTING clause
   • DBCC DBREINDEX

4. Identify disabled FOREIGN KEY constraints by using the following statement:

   SELECT * FROM sys.foreign_keys 
   WHERE is_disabled=1;
   

5. Enable any FOREIGN KEY constraints by using the ALTER TABLE CHECK CONSTRAINT statement.
   PRIMARY KEY and UNIQUE constraints are enabled by rebuilding the associated index. This index must be rebuilt before you can enable FOREIGN KEY constraints that reference the PRIMARY KEY or UNIQUE constraint.

For indexes disabled because of the changes described in the third item described earlier, use the following procedure.

1. Identify the indexes on computed columns that have been disabled by using the following statement:

   SELECT object_name(i.object_id) AS
    object_name, i.*
   FROM sys.indexes AS i
   JOIN sys.index_columns AS ic 
   ON i.index_id = ic.index_id 
     AND i.object_id = ic.object_id
   JOIN sys.computed_columns AS
    cc
   ON ic.object_id = cc.object_id 
     AND ic.column_id = cc.column_id
   WHERE i.is_disabled = 1
   

2. For a computed-column index, drop the index, and then change the computed-column definition to use an explicit CONVERT function with a deterministic date style.

3. For an indexed view, drop the view, and then redefine it by using an explicit CONVERT function with a deterministic date style.

4. Re-create the index on the modified computed column or view.

Indexes

Parallel index operations are supported in the SQL Server 2000 Developer, SQL Server 2000 Standard, and SQL Server 2000 Enterprise editions.

Parallel index operations that create, drop, or rebuild indexes are available only in the SQL Server 2005 Developer and SQL Server 2005 Enterprise editions.

Users who upgrading from SQL Server 2000 Standard Edition to SQL Server 2005 Standard Edition should know that operations that create, drop, or rebuild indexes are executed serially in SQL Server 2005 Standard Edition and may take longer to finish.

SELECT, INSERT, UPDATE, and DELETE statements are not affected. They will operate in parallel in SQL Server 2005 Standard Edition.

After you upgrade to the SQL Server 2005 Standard Edition, monitor operations that create, drop, or rebuild indexes. You may have to adjust maintenance scripts or planned maintenance activities to allow for additional time for these operations.

To execute parallel index operations, install SQL Server 2005 Enterprise Edition.

ORDER BY clause

Column names in the ORDER BY clause are resolved to columns listed in the select list, regardless if they are qualified.

For example, the following query executes without error:

USE pubs
SELECT au_fname AS 'FName',
  au_lname AS 'LName'
FROM authors a
ORDER BY a.LName

SQL Server ignores the qualifier a in the ORDER BY clause and resolves the column name LName to the select list.

Qualified column names and aliases are resolved to columns of tables listed in the FROM clause. If order_by_expression is not qualified, it must be unique among all columns listed in the SELECT statement.

For example, the following equivalent query returns an error:

USE AdventureWorks
SELECT FirstName AS 'FName',
    LastName AS 'LName'
FROM Person.Contact p
ORDER BY p.LName

SQL Server does not ignore the qualifier p in the ORDER BY clause, and resolves the column name LName to tables listed in the FROM clause. But the FROM clause does not recognize that column LName is a column alias of table p.

SERVERPROPERTY function

The return type of the ProductVersion property in the SERVERPROPERTY function is varchar.

The return type of the ProductVersion property in the SERVERPROPERTY function is nvarchar.

sp_addtype

Any user can execute sp_addtype.

Users must be a member of the db_ddladmin or db_owner database role to execute sp_addtype.

To enable users to create alias data types, one of the following changes must occur:

• To use sp_addtype, add the users to the db_ddladmin or db_owner database role.
• To create an alias data type by using CREATE TYPE, grant CREATE TYPE permission to the users and ALTER permission for the users on the target schema.

sp_altermessage

sp_altermessage can be used to specify whether or not a system message (a message with Message ID < 50000) is to be written to the Windows Application log.

sp_altermessage cannot be used to change the logging behavior of system messages (messages with Message ID < 50000). To audit system messages, use SQL Trace and the User Error Message Event Class. For more information, see Introducing SQL Trace.

sp_changedbowner

Only members of the sysadmin fixed server role, the db_owner fixed database role, or a member of both the db_ddladmin and db_securityadmin fixed database roles can execute sp_changeobjectowner.

A user that can execute this stored procedure by membership in the db_ddladmin and db_securityadmin fixed database roles must also be granted CONTROL permission on the securable. Executing sp_changeobjectowner without CONTROL permission on the target object will cause execution to fail and raise the following error message:

"Msg 15247, Level 16, State 1, Procedure sp_changeobjectowner, Line 17

User does not have permission to perform this action."

sp_help

sp_help returns one result set for functions.

sp_help returns two result sets for functions. The additional result set is for function parameters.

sysindexes

 

sys.sysindexes is included for backward compatibility. However, changes in SQL Server 2005 prevent the view from being fully backward compatible with earlier versions of SQL Server.

System metadata

Members of the public role can query system tables and display metadata from the catalog.

Users querying the catalog can only see rows of metadata for objects that they own or have some permission on, or that they are privileged to view because of a role membership. For more information and recommended corrective actions, see Metadata Visibility Configuration and Troubleshooting Metadata Visibility.

System tables

System tables have a type of 'S'.

System tables are available as compatibility views and have a type of 'V'. Statements that query system tables and include the search criteria type = 'S' will fail.

Modify the statement search criteria to type = 'V'.

or

Migrate to the new catalog views or dynamic management views. For more information, see Mapping SQL Server 2000 System Tables to SQL Server 2005 System Views.

TABLOCK when bulk importing using bcp, BULK INSERT, or OPENROWSET(BULK...)

For bulk importing into a table with a non-empty clustered index, the TABLOCK hint is ignored.

For bulk importing into a table with a non-empty clustered index, the TABLOCK hint acquires an X lock on the table. This behavior prevents bulk importing data in parallel. If you want to perform a parallel bulk import in this case, do not use TABLOCK. For more information about bulk loading in parallel, see Guidelines for Optimizing Bulk Import.

Triggers

Direct recursion of triggers occurs only when a trigger fires and performs an action that causes the same trigger to fire again.

Direct recursion of triggers occurs in either of the following circumstances:

• A trigger fires and performs an action that causes the same trigger to fire again.
• The same trigger is called again, but after a trigger of a different type (AFTER or INSTEAD OF) is called.

Indirect recursion occurs when a trigger fires and performs an action that causes another trigger of the same type (AFTER or INSTEAD OF) to fire. This second trigger performs an action that causes the original trigger to fire again.

For more information, see the Recursive Triggers section under Using Nested Triggers.

Triggers

When an UPDATE or DELETE statement is issued against a partitioned view, either local or distributed, any UPDATE or DELETE triggers that are defined on the base tables of the view fire. These include triggers on tables that are not affected by the update or delete operation.

When an UPDATE or DELETE statement is issued against a partitioned view, an UPDATE or DELETE trigger fires only if the base table on which the trigger is defined is affected by the update or delete operation. For more information, see DML Trigger Execution.

UPDATE() function

The UPDATE() function does not detect changes to timestamp columns. For these columns, an IF UPDATE() clause inside a trigger body returns FALSE, regardless of whether the columns are updated.

The UPDATE() function detects changes to timestamp columns. For these columns, an IF UPDATE() clause inside a DML trigger body returns TRUE if the columns are updated.

User-defined functions

User-defined functions cannot include nondeterministic built-in system functions.

Transact-SQL user-defined functions can include most nondeterministic built-in system functions. For a complete list of allowed built-in functions, see Creating User-defined Functions (Database Engine).

varchar, nvarchar, and varbinary data types

A zero-length string or binary value that is used as the definition of a computed table column creates a column of type varchar(0), nvarchar(0), or varbinary(0).

A zero-length string or binary value used as the definition of a computed table column creates a column of type varchar(1), nvarchar(1), or varbinary(1). This change in behavior affects only the data type of the computed column but not the computed value.

Modify applications that examine the length of computed column data types to expect a minimal length of 1 byte for varchar and varbinary columns, and 2 bytes for nvarchar columns.

Virtual table access

Virtual tables can be accessed by guests or members of the public role.

VIEW SERVER STATE permission and SELECT permission are required to access a virtual table, such as sysprocesses.

WITH CHECK OPTION on views

Insert and update operations are allowed on views that specify the WITH CHECK OPTION clause and that are created over remote tables, even when those operations fall outside the bounds of the SELECT statement of the view.

SQL Server 2005 acknowledges the WITH CHECK OPTION clause when insert and update operations are performed on views created over tables from remote data sources.

If insert and update operations fail against views created over remote tables because of the WITH CHECK OPTION clause, and you do not want this behavior, modify the view by not specifying the WITH CHECK OPTION clause.

For more information, see CREATE VIEW (Transact-SQL), ALTER VIEW (Transact-SQL), and Modifying Data Through a View.

xp_cmdshell

When an error occurs within the execution of xp_cmdshell, an error message is raised but execution is not terminated.

When an error occurs within the execution of xp_cmdshell, an error message is raised and execution is terminated.

See Also

Reference

Breaking Changes to Database Engine Features in SQL Server 2005
Deprecated Database Engine Features in SQL Server 2005
Discontinued Database Engine Functionality in SQL Server 2005

Other Resources

SQL Server 2005 Database Engine Backward Compatibility
sp_dbcmptlevel (Transact-SQL)

Help and Information

Getting SQL Server 2005 Assistance

Change History

Release	History
17 July 2006	New content: In the table that lists the Transact-SQL behavior changes, added the entry "Expressions in computed columns, CHECK constraints and DEFAULT constraints." In the table that lists the Transact-SQL behavior changes under the "SQL Server 2005 behavior" column, changed the statement for the entry "varchar, nvarchar, and varbinary data types." The data type of a computed table column was changed from nvarchar(2) to nvarchar(1).
5 December 2005	New content: Added information about cursor declarations that are not valid. Added "Changing the default language for the sa account" content. Added "DBCC CHECKFILEGROUP" content. Added "TABLOCK when bulk importing" content. Added "Triggers" content. Changed content: Corrected the content in "varchar, nvarchar, and varbinary data types."