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:

Created	Modified to add a new file
Attached	Backed up
Detached	Restored

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.

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.

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:

	Copy Code
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:
  

  	Copy Code
  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.

	Copy Code
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 1001	106
107	109
113	130

^{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:
   

   	Copy Code
   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:
   

   	Copy Code
   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:

	Copy Code
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:

	Copy Code
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.

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."