Backup and restore

DUMP statement

BACKUP

Backup and restore

LOAD statement

RESTORE

Backup and restore

BACKUP LOG WITH NO_LOG

None.

The transaction log is automatically truncated when the database is using the simple recovery model. If you need to remove the log backup chain from a database, switch to the simple recovery model.

Backup and restore

BACKUP LOG WITH TRUNCATE_ONLY

None.

The transaction log is automatically truncated when the database is using the simple recovery model. If you need to remove the log backup chain from a database, switch to the simple recovery model.

Backup and restore

BACKUP TRANSACTION

BACKUP LOG

Backup and restore

BACKUP { DATABASE | LOG } WITH PASSWORD

None.

Backup and restore

BACKUP { DATABASE | LOG } WITH MEDIAPASSWORD

None.

Backup and Restore

RESTORE { DATABASE | LOG } … WITH DBO_ONLY

RESTORE { DATABASE | LOG } … … WITH RESTRICTED_USER

Backup and restore

RESTORE { DATABASE | LOG } WITH PASSWORD

None.

Backup and restore

RESTORE { DATABASE | LOG } WITH MEDIAPASSWORD

None.

Compatibility levels

60 and 65 compatibility levels

None.

Compatibility levels

70 compatibility level

None.

DBCC

DBCC CONCURRENCYVIOLATION

None.

Extended stored procedure programming

srv_getuserdata

srv_setuserdata

Use CLR Integration instead.

Full-text search

sp_fulltext_service action values clean_up, connect_timeout, and data_timeout return zero.

None.

Instance options

SET REMOTE_PROC_TRANSACTIONS

sp_configure 'remote proc trans'

Use linked servers and distributed queries.

sp_addlinkedserver

Remote servers

Use of remote servers

sp_addserver to create remote servers

Use linked servers.

sp_addlinkedserver to create linked servers

Security

sp_addalias

sp_dropalias

sp_addgroup

sp_changegroup

sp_dropgroup

sp_helpgroup

Superseded by roles

Security

SETUSER

EXECUTE AS

System tables

syssegments

None.

Backup and restore

sp_helpdevice

sys.backup_devices

Collations

Hindi

Lithuanian_Classic

SQL_AltDiction_CP1253_CS_AS

None.

These collations exist in Microsoft SQL Server 2005, but are not visible through fn_helpcollations.

Compatibility level

80 compatibility level

None.

For more information about compatibility levels, see sp_dbcmptlevel (Transact-SQL).

Data types

sp_addtype

CREATE TYPE

Data types

timestamp syntax for rowversion data type

rowversion data type syntax.

Database management

sp_attach_db

sp_attach_single_file_db

CREATE DATABASE statement with the FOR ATTACH option; to rebuild multiple log files, when one or more have a new location, use the FOR ATTACH_REBUILD_LOG option.

Database objects

CREATE DEFAULT

DROP DEFAULT

sp_bindefault

sp_unbindefault

DEFAULT keyword in CREATE/ALTER TABLE.

Database objects

CREATE RULE

DROP RULE

sp_bindrule

sp_unbindrule

CHECK keyword in CREATE/ALTER TABLE.

Database objects

sp_renamedb

MODIFY NAME in ALTER DATABASE.

Database objects

Ability to return result sets from triggers

None.

Database options

'concat null yields null' of sp_dboption

None.

Database options

sp_dboption

sp_resetstatus

ALTER DATABASE SET { ONLINE | EMERGENCY }

Database options

TORN_PAGE_DETECTION option of ALTER DATABASE

PAGE_VERIFY TORN_PAGE DETECTION option of ALTER DATABASE

DBCC

DBCC DBREINDEX

REBUILD option of ALTER INDEX.

DBCC

DBCC INDEXDEFRAG

REORGANIZE option of ALTER INDEX

DBCC

DBCC SHOWCONTIG

sys.dm_db_index_physical_stats

Extended properties

Level0type = 'type' and Level0type = 'USER' to add extended properties to level-1 or level-2 type objects.

Use Level0type = 'USER' only to add an extended property directly to a user or role.

Use Level0type = 'SCHEMA' to add an extended property to level-1 types like TABLE or VIEW, or level-2 types like COLUMN or TRIGGER. For more information, see sp_addextendedproperty.

Extended stored procedure programming

srv_alloc

srv_convert

srv_describe

srv_getbindtoken

srv_got_attention

srv_message_handler

srv_paramdata

srv_paraminfo

srv_paramlen

srv_parammaxlen

srv_paramname

srv_paramnumber

srv_paramset

srv_paramsetoutput

srv_paramtype

srv_pfield

srv_pfieldex

srv_rpcdb

srv_rpcname

srv_rpcnumber

srv_rpcoptions

srv_rpcowner

srv_rpcparams

srv_senddone

srv_sendmsg

srv_sendrow

srv_setcoldata

srv_setcollen

srv_setutype

srv_willconvert

srv_wsendmsg

Use CLR Integration instead.

Extended stored procedure programming

sp_addextendedproc

sp_dropextendedproc

sp_helpextendedproc

Use CLR Integration instead.

Extended stored procedures

xp_LoginConfig

IsIntegratedSecurityOnly argument of SERVERPROPERTY

Full-text search

sp_fulltext_catalog

CREATE/ALTER/DROP FULLTEXT CATALOG

Full-text search

sp_fulltext_table

sp_fulltext_column

sp_fulltext_database

CREATE/ALTER/DROP FULLTEXT INDEX

ALTER FULLTEXT INDEX

Full-text search

sp_help_fulltext_tables[_cursor]

sp_help_fulltext_columns[_cursor]

sp_help_fulltext_catalogs[_cursor]

sys.fulltext_indexes

sys.fulltext_index_columns

sys.fulltext_catalogs

Functions

fn_get_sql

sys.dm_exec_sql_text

Index options

sp_indexoption

fillfactor = 0

ALTER INDEX

fillfactor = 100

Index options

CREATE INDEX <index_option>::= syntax

CREATE INDEX <relational_index_option>::= syntax

Instance options

Default setting of disallow results from triggers option = 0

Default setting of disallow results from triggers option = 1

Locking

sp_lock

sys.syslock_information

Locking

syslockinfo

sys.syslock_information

Metadata

DATABASEPROPERTY

FILE_ID

INDEXKEY_PROPERTY

DATABASEPROPERTYEX

FILE_IDEX

sys.index_columns

Other

DB-Library

Embedded SQL for C

Although the SQL Server 2005 Database Engine still supports connections from existing applications using the DB-Library and Embedded SQL APIs, it does not include the files or documentation needed to do programming work on applications that use these APIs. A future version of the SQL Server Database Engine will drop support for connections from DB-Library or Embedded SQL applications. Do not use DB-Library or Embedded SQL to develop new applications. Remove any dependencies on either DB-Library or Embedded SQL when modifying existing applications. Instead of these APIs, use the SQLClient namespace or an API such as OLE DB or ODBC. SQL Server 2005 does not include the DB-Library DLL required to run these applications. To run DB-Library or Embedded SQL applications you must have available the DB-Library DLL from SQL Server version 6.5, SQL Server 7.0, or SQL Server 2000.

Query hints

FASTFIRSTROW hint

OPTION (FAST n).

Removable databases

sp_certify_removable

sp_create_removable

sp_detach_db

Removable databases

sp_dbremove

DROP DATABASE

Security

sp_addapprole

sp_dropapprole

CREATE APPLICATION ROLE

DROP APPLICATION ROLE

Security

sp_addlogin

sp_droplogin

CREATE LOGIN

DROP LOGIN

Security

sp_adduser

sp_dropuser

CREATE USER

DROP USER

Security

sp_grantdbaccess

sp_revokedbaccess

CREATE USER

DROP USER

Security

sp_addrole

sp_droprole

CREATE ROLE

DROP ROLE

Security

sp_approlepassword

sp_password

ALTER APPLICATION ROLE

ALTER LOGIN

Security

sp_changeobjectowner

ALTER SCHEMA or ALTER AUTHORIZATION

Security

sp_defaultdb

sp_defaultlanguage

ALTER LOGIN

Security

sp_denylogin

sp_grantlogin

sp_revokelogin

ALTER LOGIN DISABLE

CREATE LOGIN

DROP LOGIN

Security

USER_ID

FILE_ID

DATABASE_PRINCIPAL_ID

FILE_IDEX

Security

sp_srvrolepermission

sp_dbfixedrolepermission

These stored procedures return information that was correct in SQL Server 2000. The output does not reflect changes to the permissions hierarchy implemented in SQL Server 2005. For more information, see "Permissions of Fixed Roles" in SQL Server 2005 Books Online.

SET options

SET ANSI_NULLS

SET ANSI_PADDING

SET CONCAT_NULL_YIELDS_NULL

None.

SET options

SET OFFSETS

None.

SET options

SET ROWCOUNT for INSERT, UPDATE, and DELETE statements

TOP keyword.

System tables

sysaltfiles

syscacheobjects

syscolumns

syscomments

sysconfigures

sysconstraints

syscurconfigs

sysdatabases

sysdepends

sysdevices

sysfilegroups

sysfiles

sysforeignkeys

sysfulltextcatalogs

sysindexes

sysindexkeys

syslockinfo

syslogins

sysmembers

sysmessages

sysobjects

sysoledbusers

sysopentapes

sysperfinfo

syspermissions

sysprocesses

sysprotects

sysreferences

sysremotelogins

sysservers

systypes

sysusers

Compatibility views. For more information, see Compatibility Views (Transact-SQL).

Important:
The compatibility views do not expose metadata for features introduced in SQL Server 2005. It is recommended that you upgrade your applications to use catalog views. For more information, see Catalog Views (Transact-SQL).

Table hints

Specifying NOLOCK or READUNCOMMITTED in the FROM clause of an UPDATE or DELETE statement when applied to the target table of the statement.

Remove the NOLOCK or READUNCOMMITTED table hints from the FROM clause.

Textpointers

READTEXT, WRITETEXT, UPDATETEXT

None.

Textpointers

'text in row' table option

Use varchar(max), nvarchar(max), and varbinary(max) data types. For more information, see sp_tableoption (Transact-SQL).

Textpointers

TEXT, NTEXT and IMAGE data types

Use varchar(max), nvarchar(max), and varbinary(max) data types.

Textpointers

TEXTPTR(), TEXTVALID()

None.

Transact-SQL

:: function-calling sequence

Replaced by SELECT column_list FROM sys.fn_function_name()

Transact-SQL

3-part and 4-part column references in SELECT list

2-part names is the standard-compliant behavior.

Transact-SQL

A string enclosed in quotes used as a column alias for an expression in a SELECT list:

'string_alias' = expression

expression [AS] column_alias 

expression [AS] [column_alias]

expression [AS] "column_alias"

expression [AS] 'column_alias'

column_alias = expression

Transact-SQL

Stored procedure numbers

sys.numbered_procedures

sys.numbered_procedure_parameters

None.

Transact-SQL

table_name.index_name syntax in DROP INDEX

index_name ON table_name syntax in DROP INDEX.

Transact-SQL

UPDATE table1, table2, ... SET syntax

Specifying more than one table in the UPDATE target is nonstandard and ambiguous.