Changes to commands

dbcc

• prsqlcache parameter allows you to print summaries of cached SQL statements, and the purgesqlcache parameter purges all statement cache entries, except those that are currently in use.

• dbcc checkcatalog performs sysindexes consistency checking and adds the fix parameters to fix any errors, if any.

• complete_xact adds 1pc, which heuristically completes a transaction that was subject to a one-phase commit protocol optimization—instead of the regular two-phase commit protocol—by the external transaction manager that was coordinating its completion.

• Adds the stackused parameter, which reports the maximum amount of stack memory used since the server first started.

update statistics, delete statistics

Allow you to grant and revoke permissions for users, roles, and groups.

grant, revoke

Add the default permissions parameter, which allows you to grant and revoke the default permissions for some system tables.

dump, load database

Include a password parameter, allows you to password-protect your database dumps.

union

The maximum number of subqueries within a single side of a union has increased from 16 to 50.

kill

Adds the statusonly parameter, which reports on the progress of a server process ID (spid) in rollback status. It does not terminate the spid.

select *

Descriptions for queries using select * are fully expanded to describe the columns selected.

grant dbcc, set proxy

Issue the following warning when they are issued while set fipsflagger option is enabled.

update, delete

Queries that use update and delete on views with a union all clause are always resolved using worktables in tempdb.

create procedure (SQLJ)

Allows you to define a default value so that you can execute the SQLJ procedure without providing a parameter value.