1. SQL Server 2005 - Features Not Supported in the Next Version of SQL Server
1.1 Backup and restore
1.1.1 DUMP – replaced with Backup
1.1.2 LOAD - replaced with Restore
1.1.3 BACKUP LOG WITH NO_LOG – None, switch to the simple recovery mode
1.1.4 BACKUP LOG WITH TRUNCATE_ONLY- - None, switch to the simple recovery mode
1.1.5 BACKUP TRANSACTION - replaced with Backup LOG
1.1.6 BACKUP { DATABASE | LOG } WITH PASSWORD - None
1.1.7 BACKUP { DATABASE | LOG } WITH MEDIAPASSWORD- None
1.1.8 RESTORE { DATABASE | LOG } … WITH DBO_ONLY – replaced with RESTORE { DATABASE | LOG } … … WITH RESTRICTED_USER
1.1.9 RESTORE { DATABASE | LOG } WITH PASSWORD – None
1.1.10 RESTORE { DATABASE | LOG } WITH MEDIAPASSWORD – None
1.2 Compatibility levels
1.2.1 60, 65, 70 compatibility levels – None
1.3 DBCC
1.3.1 DBCC CONCURRENCYVIOLATION – None
1.4 Extended stored procedure programming
1.4.1 srv_getuserdata and srv_setuserdata (The Data Retrieval gateway functions for Open Data Services application prior 7.0) – Replaced with CLR
1.5 Full-text search
1.5.1 sp_fulltext_service action values clean_up,connect_timeout, and data_timeout return 0- None
1.6 Instance options
1.6.1 sp_configure 'remote proc trans' (SET REMOTE_PROC_TRANSACTIONS )- Use sp_addlinkedserver (for linked servers and distributed queries)
1.7 Remote servers
1.7.1 sp_addserver to create remote servers- Use sp_addlinkedserver to create linked servers
1.8 Security
1.8.1 sp_addalias (Maps a login to a user in a database.) – use Role
1.8.2 sp_dropalias– use Role
1.8.3 sp_addgroup– use Role
1.8.4 sp_changegroup– use Role
1.8.5 sp_dropgroup– use Role
1.8.6 sp_helpgroup– use Role
1.8.7 SETUSER - EXECUTE AS
1.9 System tables
1.9.1 Syssegments (from Sybase) – None
2. SQL Server 2005 - Features Not Supported in a Future Version of SQL Server
2.1 Backup and restore
2.1.1 sp_helpdevice (Reports information about Microsoft® SQL Server™ backup devices) - Use sys.backup_devices (Contains a row for each backup-device registered by using sp_addumpdevice or created in SQL Server Management Studio.)
2.2 Collations
2.2.1 Hindi - None
2.2.2 Lithuanian_Classic - None
2.2.3 SQL_AltDiction_CP1253_CS_AS – None
2.3 Compatibility level
2.3.1 80 compatibility level – None
2.4 Data types
2.4.1 sp_addtype - CREATE TYPE
2.4.2 timestamp syntax for rowversion data type - rowversion data type syntax.
2.5 Database management
2.5.1 sp_attach_db and 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.
2.6 Database objects
2.6.1 CREATE DEFAULT , DROP DEFAULT, sp_bindefault (Binds a default to a column or to an alias data type) and sp_unbindefault (Unbinds, or removes, a default from a column or from an alias data type in the current database.)– Use DEFAULT keyword in CREATE/ALTER TABLE
2.6.2 CREATE RULE, DROP RULE, sp_bindrule, and sp_unbindrule – Use the CHECK keyword in CREATE/ALTER TABLE
2.6.3 sp_renamedb – replaced with MODIFY NAME in ALTER DATABASE.
2.6.4 Ability to return result sets from triggers – Disable it by setting the disallow results from triggers option to 1.
2.7 Database options
2.7.1 'concat null yields null' of sp_dboption (When true, if either operand in a concatenation operation is NULL, the result is NULL.) – None
2.7.2 sp_dboption(Displays or changes database options. is supported for backward compatibility. Use ALTER DATABASE to set database options), sp_resetstatus (Resets the status of a suspect database) – Use ALTER DATABASE SET { ONLINE | EMERGENCY }
2.7.3 TORN_PAGE_DETECTION option of ALTER DATABASE – replaced by PAGE_VERIFY { CHECKSUM | TORN_PAGE_DETECTION | NONE } option of ALTER DATABASE
2.8 DBCC
2.8.1 DBCC DBREINDEX (Rebuilds one or more indexes for a table in the specified database) – Use REBUILD option of ALTER INDEX.
2.8.2 DBCC INDEXDEFRAG (Defragments indexes of the specified table or view) – Use REORGANIZE option of ALTER INDEX
2.8.3 DBCC SHOWCONTIG (Displays fragmentation information for the data and indexes of the specified table or view, CONTIG=CONTIGUity) - sys.dm_db_index_physical_stats
2.9 Extended properties
2.9.1 Level0type = 'type' and Level0type = 'USER' to add extended properties to level-1 or level-2 type objects in sp_addextendedproperty - 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. (USER and TYPE as level-0 types will be removed in a future version of SQL Server. Avoid using these features in new development work, and plan to modify applications that currently use these features. Use SCHEMA as the level 0 type instead of USER. For TYPE, use SCHEMA as the level 0 type and TYPE as the level 1 type.)
2.10 Extended stored procedure programming - Use CLR Integration instead.
2.10.1 srv_alloc
2.10.2 srv_convert
2.10.3 srv_describe
2.10.4 srv_getbindtoken
2.10.5 srv_got_attention
2.10.6 srv_message_handler
2.10.7 srv_paramdata
2.10.8 srv_paraminfo
2.10.9 srv_paramlen
2.10.10 srv_parammaxlen
2.10.11 srv_paramname
2.10.12 srv_paramnumber
2.10.13 srv_paramset
2.10.14 srv_paramsetoutput
2.10.15 srv_paramtype
2.10.16 srv_pfield
2.10.17 srv_pfieldex
2.10.18 srv_rpcdb
2.10.19 srv_rpcname
2.10.20 srv_rpcnumber
2.10.21 srv_rpcoptions
2.10.22 srv_rpcowner
2.10.23 srv_rpcparams
2.10.24 srv_senddone
2.10.25 srv_sendmsg
2.10.26 srv_sendrow
2.10.27 srv_setcoldata
2.10.28 srv_setcollen
2.10.29 srv_setutype
2.10.30 srv_willconvert
2.10.31 srv_wsendmsg
2.10.32 sp_addextendedproc
2.10.33 sp_dropextendedproc
2.10.34 sp_helpextendedproc
2.11 Extended stored procedures
2.11.1 xp_LoginConfig (Reports the login security configuration of Microsoft® SQL Server™ when running on Microsoft Windows 2000 or Microsoft Windows NT® 4.0.) – use SERVERPROPERTY(‘IsIntegratedSecurityOnly’)
2.12 Full-text search
2.12.1 sp_fulltext_catalog (Creates and drops a full-text catalog, and starts and stops the indexing action for a catalog. Multiple full-text catalogs can be created for each database.) – Use CREATE/ALTER/DROP FULLTEXT CATALOG
2.12.2 sp_fulltext_database (Initializes full-text indexing or removes all full-text catalogs from the current database) , sp_fulltext_table Marks or unmarks a table for full-text indexing), sp_fulltext_column (Specifies whether or not a particular column of a table participates in full-text indexing) – Use CREATE/ALTER/DROP FULLTEXT INDEX
2.12.2.1 sp_fulltext_database (Transact-SQL) in SQL Server 2005
2.12.2.2 sp_fulltext_table (Transact-SQL) in SQL Server 2005
2.12.2.3 sp_fulltext_column (Transact-SQL) in SQL Server 2005
2.12.3 sp_help_fulltext_tables[_cursor](Return a list of tables that are registered for full-text indexing without or with using a cursor) – replaced by sys.fulltext_indexes (the new catalog view introduced in 2005)
2.12.4 sp_help_fulltext_columns[_cursor] ](Return a list of columns that are registered for full-text indexing without or with using a cursor – replaced by sys.fulltext_index_columns
2.12.5 sp_help_fulltext_catalogs[_cursor] (Uses a [cursor] to return the ID, name, root directory, status, and number of full-text indexed tables for the specified full-text catalog) - replaced by sys.fulltext_catalogs
2.13 Functions
2.13.1 ::fn_get_sql (@sqlHandle) (Returns the text of the SQL statement for the specified SQL handle) – replaced by sys.dm_exec_sql_text(@sqlHandle)
2.14 Index options
2.14.1 sp_indexoption fillfactor = 0 – replaced by ALTER INDEX fillfactor = 100
2.14.2 CREATE INDEX <index_option>::= syntax – replaced by CREATE INDEX <relational_index_option>::= syntax
2.14.2.1 Create Index Syntax in SQL Server 2000
2.14.2.2 Create Index Syntax in SQL Server 2005
2.15 Instance options
2.15.1 Default setting of disallow results from triggers option = 0 – replaced by Default setting of disallow results from triggers option = 1
2.16 Locking
2.16.1 Sp_lock (This system sp reports information about locks) – replaced by sys.syslock_information sys.dm_tran_locks (Returns information about currently active lock manager resources. Each row represents a currently active request to the lock manager for a lock that has been granted or is waiting to be granted)
2.16.2 Syslockinfo (This system table return information about all granted, converting, and waiting lock requests)– replaced by sys.syslock_information sys.dm_tran_locks
2.17 Metadata
2.17.1 DATABASEPROPERTY - replaced by DATABASEPROPERTYEX
2.17.2 FILE_ID (Returns the file identification (ID) number for the given logical file name in the current database)– replaced by FILE_IDEX (Returns the file identification (ID) number for the specified logical file name of the data, log, or full-text file in the current database)
2.17.3 INDEXKEY_PROPERTY (Returns information about the index key. Returns NULL for XML indexes) - replaced by sys.index_columns (Contains one row per column that is part of a sys.indexes index or unordered table (heap))
2.18 Other
2.18.1 DB-Library and/or Embedded SQL for C – Use use the SQLClient namespace or an API such as OLE DB or ODBC instead
2.19 Query hints
2.19.1 FASTFIRSTROW hint – Replace with OPTION (FAST n).
2.20 Removable databases
2.20.1 sp_certify_removable (Verifies that a database is correctly configured for distribution on removable media and reports any problems to the user) – replaced with sp_detach_db
2.20.2 sp_create_removable (Creates a removable media database. Creates three or more files (one for the system catalog tables, one for the transaction log, and one or more for the data tables) and places the database on those files) – replaced with sp_detach_db
2.20.3 sp_dbremove (Removes a database and all files associated with that database) – Use DROP DATABASE
2.21 Security
2.21.1 sp_addapprole - CREATE APPLICATION ROLE
2.21.2 sp_dropapprole - DROP APPLICATION ROLE
2.21.3 sp_addlogin - CREATE LOGIN
2.21.4 sp_droplogin- DROP LOGIN
2.21.5 sp_adduser - CREATE USER
2.21.6 sp_dropuser - DROP USER
2.21.7 sp_grantdbaccess (Adds a database user to the current database) – use CREATE USER instead
2.21.8 sp_revokedbaccess (Removes a database user from the current database) - use DROP USER instead
2.21.9 sp_addrole - CREATE ROLE
2.21.10 sp_droprole- DROP ROLE
2.21.11 sp_approlepassword (Changes the password of an application role in the current database) - ALTER APPLICATION ROLE
2.21.12 sp_password (Adds or changes a password for a Microsoft SQL Server login) - ALTER LOGIN
2.21.13 sp_changeobjectowner (Changes the owner of an object in the current database. It changes both the schema and the owner) - ALTER SCHEMA or ALTER AUTHORIZATION
2.21.14 sp_defaultdb - ALTER LOGIN
2.21.15 sp_defaultlanguage- ALTER LOGIN
2.21.16 sp_denylogin (Prevents a Windows user or Windows group from connecting to an instance of SQL Server) - ALTER LOGIN DISABLE
2.21.17 sp_grantlogin (Creates a SQL Server login) - CREATE LOGIN
2.21.18 sp_revokelogin (Removes the login entries from SQL Server for a Windows user or group created by using CREATE LOGIN, sp_grantlogin, or sp_denylogin) - DROP LOGIN
2.21.19 USER_ID (Returns the identification number for a database user) - DATABASE_PRINCIPAL_ID
2.21.20 FILE_ID - FILE_IDEX
2.21.21 sp_srvrolepermission (Displays the permissions of a fixed server role.) and sp_dbfixedrolepermission (Displays the permissions of a fixed database role) – None. Just do not use them
2.22 SET Options
2.22.1 SET ANSI_NULLS (Specifies ISO compliant behavior of the Equals (=) and Not Equal To (<>) comparison operators when they are used with null values.) – Will be always ON
2.22.2 SET ANSI_PADDING (Controls the way the column stores values shorter than the defined size of the column, and the way the column stores values that have trailing blanks in char, varchar, binary, and varbinary data) – Will be always ON
2.22.3 SET CONCAT_NULL_YIELDS_NULL (Controls whether concatenation results are treated as null or empty string values.) – Will be always ON
2.22.4 SET OFFSETS (Returns the offset (position relative to the start of a statement) of specified keywords in Transact-SQL statements to DB-Library applications) – will be available, no replacement
2.22.5 SET ROWCOUNT for INSERT, UPDATE, and DELETE statements (Causes SQL Server to stop processing the query after the specified number of rows are returned, but is ignored for INSERT, UPDATE, and DELETE statements) – Use the TOP keyword.
2.23 System tables – replaced by system views or functions
2.23.1 Sysaltfiles (Under special circumstances, contains rows corresponding to the files in a database) – replaced by the catalog view sys.master_files
2.23.2 Syscacheobjects (Contains information about how the cache is used) – replaced by the DMVs (sys.dm_exec_cached_plans, sys.dm_exec_plan_attributes, sys.dm_exec_sql_text, sys.dm_exec_cached_plan_dependent_objects)
2.23.3 syscolumns (Returns one row for every column in every table and view, and a row for each parameter in a stored procedure in the database) - replaced by the catalog view sys.columns
2.23.4 syscomments (Contains entries for each view, rule, default, trigger, CHECK constraint, DEFAULT constraint, and stored procedure within the database. The text column contains the original SQL definition statements) - replaced by the catalog view sys.sql_modules
2.23.5 sysconfigures (Contains one row for each configuration option. sysconfigures contains the configuration options that are defined before the most recent startup of Microsoft SQL Server 2005, plus any dynamic configuration options set since then.)– replaced by the catalog view sys.configurations
2.23.6 sysconstraints (Contains mappings of constraints to the objects that own the constraints within the database) - - replaced by DMVs (sys.check_constraints, sys.default_constraints, sys.key_constraints, sys.foreign_keys)
2.23.7 syscurconfigs (Contains an entry for each current configuration option.) – replaced by the catalog view sys.configurations
2.23.8 sysdatabases (Contains one row for each database in an instance of Microsoft SQL Server 2005. When SQL Server is first installed, sysdatabases contains entries for the master, model, msdb, and tempdb databases) – replaced by the catalog view sys.databases
2.23.9 sysdepends (Contains dependency information between objects (views, procedures, and triggers) in the database, and the objects (tables, views, and procedures) that are contained in their definition) – replaced by the catalog view sys.sql_dependencies
2.23.10 sysdevices (Contains one row for each disk backup file, tape backup file, and database file) – replaced by the catalog view sys.backup_devices
2.23.11 sysfilegroups (Contains one row for each file group in a database. There is at least one entry in this table that is for the primary file group) – replaced by the catalog view sys.filegroups
2.23.12 sysfiles (Contains one row for each file in a database) – replaced by the catalog view sys.database_files
2.23.13 sysforeignkeys (Contains information about the FOREIGN KEY constraints that are in the definitions of tables in the database.) – replaced by the catalog view sys.foreign_key_columns
2.23.14 sysfulltextcatalogs (Contains information about the full-text catalogs.) – replaced by the catalog view sys.fulltext_catalogs
2.23.15 sysindexes (Contains one row for each index and table in the current database. XML indexes are not supported in this view. Partitioned tables and indexes are not fully supported in this view) - use the sys.indexes catalog view instead.
2.23.16 Sysindexkeys (Contains information about the keys or columns in an index of the database.) – use sys.index_columns
2.23.17 Syslockinfo (Contains information about all granted, converting, and waiting lock requests) – the DMV sys.dm_tran_locks
2.23.18 Syslogins (Contains one row for each login account.) – use the catalog view sys.server_principals or sys.sql_logins
2.23.19 Sysmembers (Contains a row for each member of a database role) – use sys.database_role_members
2.23.20 sysmessages (Contains one row for each system error or warning that can be returned by the Microsoft SQL Server Database Engine. The Database Engine displays the error description on the user's screen)– use sys.messages
2.23.21 sysobjects(Contains one row for each object that is created within a database, such as a constraint, default, log, rule, and stored procedure) - Use sys.objects
2.23.22 sysoledbusers (Contains one row for each user and password mapping for the specified linked server. sysoledbusers is stored in the master database) – Use the catalog view sys.linked_logins
2.23.23 sysopentapes (Contains one row for each currently open tape device. This view is stored in the master database) – use the DMV sys.dm_io_backup_tapes
2.23.24 sysperfinfo (Contains a Microsoft SQL Server Database Engine representation of the internal performance counters that can be displayed through the Windows System Monitor.) - sys.dm_os_performance_counters
2.23.25 syspermissions (Contains information about permissions granted and denied to users, groups, and roles in the database.) – Use sys.database_permissions or sys.server_permissions
2.23.26 sysprocesses (Contains information about processes that are running on an instance of Microsoft SQL Server. These processes can be client processes or system processes.) – Use sys.dm_exec_connections, sys.dm_exec_sessions, or sys.dm_exec_requests
2.23.27 sysprotects (Contains information about permissions that have been applied to security accounts in the database by using the GRANT and DENY statements.) – replaced by sys.database_permissions or sys.server_permissions
2.23.28 sysreferences (Contains mappings of the FOREIGN KEY constraint definitions to the referenced columns within the database.)- replaced by the catalog view sys.foreign_keys
2.23.29 sysremotelogins(Contains one row for each remote user that is permitted to call remote stored procedures on an instance of Microsoft SQL Server 2005.) – use sys.remote_logins
2.23.30 sysservers (Contains one row for each server that an instance of Microsoft SQL Server 2005 can access as an OLE DB data source.) - Use sys.servers
2.23.31 systypes (Returns one row for each system-supplied and each user-defined data type defined in the database.)- use sys.types
2.23.32 sysusers Contains one row for each Microsoft Windows user, Windows group, Microsoft SQL Server user, or SQL Server role in the database.) - sys.database_principals
2.24 Table hints
2.24.1 Specifying NOLOCK or READUNCOMMITTED in the FROM clause of an UPDATE or DELETE statement when applied to the target table of the statement (just ignored for UPDATE or DELETE) - Remove the NOLOCK or READUNCOMMITTED table hints from the FROM clause.
2.25 Textpointers
2.25.1 'text in row' table option - Use varchar(max), nvarchar(max), or varbinary(max) data types. For more information, see sp_tableoption (Transact-SQL).
2.25.2 TEXT, NTEXT and IMAGE data types - Use varchar(max), nvarchar(max), and varbinary(max)
2.25.3 TEXTPTR(TextNtextOrIimageColumn)(Returns the text-pointer value that corresponds to a text, ntext, or image column in varbinaryformat. The retrieved text pointer value can be used in READTEXT, WRITETEXT, and UPDATETEXT statements.), TEXTVALID('table.column' ,text_ ptr ) (A text, ntext, or image function that checks whether a specific text pointer is valid.) – None, Alternative functionality is not available.
2.26 Transact-SQL
2.26.1 :: function-calling sequence - Replaced by SELECT column_list FROM sys.fn_function_name()
2.26.2 3-part and 4-part column references in SELECT list - 2-part names is the standard-compliant behavior.
2.26.3 A string enclosed in quotation marks used as a column alias for an expression in a SELECT list: 'string_alias' = expression – replaced by expression [AS] column_alias, expression [AS] [column_alias], expression [AS] "column_alias", expression [AS] 'column_alias', or column_alias = expression
2.26.4 Stored procedure numbers: sys.numbered_procedures (Contains a row for each SQL Server stored procedure that was created as a numbered procedure. This does not show a row for the base (number = 1) stored procedure. Entries for the base stored procedures can be found in views such as sys.objects and sys.procedures),sys.numbered_procedure_parameters (Contains one row for each parameter of a numbered procedure. When you create a numbered stored procedure, the base procedure is number 1. All subsequent procedures have numbers 2, 3, and so forth. sys.numbered_procedure_parameters contains the parameter definitions for all subsequent procedures, numbered 2 and greater.) – None, simply deprecated, no replacement is required.
2.26.5 table_name.index_name syntax in DROP INDEX (DROP INDEX tablename.indexname) – replaced by index_name ON table_name syntax in DROP INDEX (DROP INDEX indexname on tableName).
2.26.6 UPDATE table1, table2, ... SET syntax - Specifying more than one table in the UPDATE target is nonstandard and ambiguous.
3. SQL Server 2008 - Features Not Supported in the Next Version of SQL Server
3.1 Backup and Restore
3.1.1 BACKUP { DATABASE | LOG } WITH PASSWORD - None
3.1.2 BACKUP { DATABASE | LOG } WITH MEDIAPASSWORD - None
3.1.3 RESTORE { DATABASE | LOG } … WITH DBO_ONLY - RESTORE { DATABASE | LOG } … … WITH RESTRICTED_USER
3.1.4 RESTORE { DATABASE | LOG } WITH PASSWORD - None
3.1.5 RESTORE { DATABASE | LOG } WITH MEDIAPASSWORD - None
3.2 Compatibility levels
3.2.1 80 compatibility level (SQL Server 2000) and upgrade from version 80 – Use ALTER DATABASE database_name SET COMPATIBILITY_LEVEL = { 80 | 90 | 100 }. Compatibility levels are only available for the last two versions.
3.3 Metadata
3.3.1 DATABASEPROPERTY – use DATABASEPROPERTYEX
3.4 Database objects
3.4.1 WITH APPEND clause on triggers (CREATE TRIGGER WITH APPEND) - Re-create the whole trigger.
3.5 Instance options
3.5.1 Default setting of disallow results from triggers option = 0 – replaced with Default setting of disallow results from triggers option = 1
3.6 Database options
3.6.1 sp_dboption - ALTER DATABASE
3.7 Query hints
3.7.1 FASTFIRSTROW hint - OPTION (FAST n).
3.8 Remote servers
3.8.1 sp_addremotelogin, sp_addserver, sp_dropremotelogin, sp_helpremotelogin, sp_remoteoption - Replace remote servers by using linked servers
3.8.2 @@remserver (Returns the name of the remote SQL Server database server as it appears in the login record) - Use linked servers and linked server stored procedures instead.
3.8.3 SET REMOTE_PROC_TRANSACTIONS (Specifies that when a local transaction is active, executing a remote stored procedure starts a Transact-SQL distributed transaction managed by Microsoft Distributed Transaction Coordinator (MS DTC)) - Replace remote servers by using sp_addlinkedserver.
3.9 Security
3.9.1 sp_dropalias - Replace aliases with a combination of user accounts and database roles. Use sp_dropalias to remove aliases in upgraded databases.
3.10 SET options
3.10.1 SET DISABLE_DEF_CNST_CHK - None. Option has no effect.
3.10.2 SET ROWCOUNT for INSERT, UPDATE, and DELETE statements - Use TOP keyword
3.11 Transact-SQL syntax
3.11.1 Use of *= and =* - Use ANSI join syntax. For more information, see FROM (Transact-SQL).
3.11.2 COMPUTE / COMPUTE BY - Use ROLLUP
3.12 System tables
3.12.1 sys.database_principal_aliases - Use roles instead of aliases.
3.13 Tools
3.13.1 sqlmaint Utility - Use the SQL Server maintenance plan feature.
3.14 Transact-SQL
3.14.1 The RAISERROR (Format: RAISERROR integer string) syntax is deprecated - Rewrite the statement using the current RAISERROR syntax.
4. SQL Server 2008 - Features Not Supported in a Future Version of SQL Server
4.1 Compatibility levels
4.1.1 sp_dbcmptlevel - Use ALTER DATABASE … SET COMPATIBILITY_LEVEL
4.1.2 Database compatibility level 90 – Solution: Plan to upgrade the database and application for a future release.
4.2 XML
4.2.1 Inline XDR Schema Generation - The XMLDATA directive to the FOR XML option is deprecated. Use XSD generation in the case of RAW and AUTO modes. There is no replacement for the XMLDATA directive in EXPLICT mode.
4.3 Backup and restore
4.3.1 BACKUP { DATABASE | LOG } TO TAPE, BACKUP { DATABASE | LOG } TO device_that_is_a_tape – Use BACKUP { DATABASE | LOG } TO DISK, BACKUP { DATABASE | LOG } TOdevice_that_is_a_disk
4.3.2 sp_addumpdevice 'tape'- sp_addumpdevice 'disk'
4.3.3 sp_helpdevice - sys.backup_devices
4.4 Collations
4.4.1 Korean_Wansung_Unicode, Lithuanian_Classic, SQL_AltDiction_CP1253_CS_AS - None. These collations exist in SQL Server 2005, but are not visible through fn_helpcollations
4.4.2 Hindi, Macedonian - These collations exist in SQL Server 2005 and higher, but are not visible through fn_helpcollations. Use Macedonian_FYROM_90 and Indic_General_90 instead.
4.4.3 Azeri_Latin_90, Azeri_Cyrilllic_90 – Use Azeri_Latin_100, Azeri_Cyrilllic_100
4.5 Configuration
4.5.1 SET ANSI_NULLS OFF and ANSI_NULLS OFF database option – None, will be ON always
4.5.2 SET ANSI_PADDING OFF and ANSI_PADDING OFF database option– None, will be ON always
4.5.3 SET CONCAT_NULL_YIELDS_NULL OFF and CONCAT_NULL_YIELDS_NULL OFF database option – None, will be ON always
4.5.4 SET OFFSETS (Returns the offset (position relative to the start of a statement) of specified keywords in Transact-SQL statements to DB-Library applications) – None, will be unavailable
4.6 Data Types
4.6.1 sp_addtype - CREATE TYPE
4.6.2 sp_droptype- DROP TYPE
4.6.3 timestamp syntax for rowversion data type - rowversion data type syntax
4.6.4 Ability to insert null values into timestamp columns - Use a DEFAULT instead (e.g., default to GetDate(), timestamp is automatically generated).
4.6.5 'text in row' table option in sp_tableoption - Use varchar(max), nvarchar(max), and varbinary(max) data types. For more information, see sp_tableoption (Transact-SQL).
4.6.6 Text, ntext, image –Use varchar(max), nvarchar(max), and varbinary(max) data types.
4.7 Database management
4.7.1 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.
4.8 Database objects
4.8.1 CREATE DEFAULT, DROP DEFAULT, sp_bindefault, sp_unbindefault - DEFAULT keyword in CREATE TABLE and ALTER TABLE
4.8.2 CREATE RULE, DROP RULE, sp_bindrule, sp_unbindrule - CHECK keyword in CREATE TABLE and ALTER TABLE
4.8.3 sp_change_users_login - Use ALTER USER.
4.8.4 sp_depends – Use sys.dm_sql_referencing_entities and sys.dm_sql_referenced_entities
4.8.5 sp_renamedb - MODIFY NAME in ALTER DATABASE
4.8.6 sp_getbindtoken (Returns a unique identifier for the transaction. This unique identifier is a string used to bind sessions using sp_bindsession) - Use MARS or distributed transactions.
4.8.7 sp_bindsession (Binds or unbinds a session to other sessions in the same instance of the SQL Server Database Engine. Binding sessions allows two or more sessions to participate in the same transaction and share locks until a ROLLBACK TRANSACTION or COMMIT TRANSACTION is issued.) – Use MARS or distributed transactions
4.8.8 sp_resetstatus- ALTER DATABASE SET { ONLINE | EMERGENCY }
4.8.9 TORN_PAGE_DETECTION option of ALTER DATABASE - PAGE_VERIFY TORN_PAGE_DETECTION option of ALTER DATABASE
4.9 DBCC
4.9.1 DBCC DBREINDEX - REBUILD option of ALTER INDEX.
4.9.2 DBCC INDEXDEFRAG - REORGANIZE option of ALTER INDEX
4.9.3 DBCC SHOWCONTIG - sys.dm_db_index_physical_stats
4.9.4 DBCC [UN]PINTABLE (Marks a table to be pinned. This means the SQL Server Database Engine does not flush the pages for the table from memory.) – Still available, but has no effect on the server.
4.10 Extended properties
4.10.1 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 such as TABLE or VIEW, or level-2 types such as COLUMN or TRIGGER. For more information, see sp_addextendedproperty (Transact-SQL).
4.11 Extended stored procedure programming (XP_API) - Use CLR Integration instead.
4.11.1 srv_alloc
4.11.2 srv_convert
4.11.3 srv_describe
4.11.4 srv_getbindtoken
4.11.5 srv_got_attention
4.11.6 srv_message_handler
4.11.7 srv_paramdata
4.11.8 srv_paraminfo
4.11.9 srv_paramlen
4.11.10 srv_parammaxlen
4.11.11 srv_paramname
4.11.12 srv_paramnumber
4.11.13 srv_paramset
4.11.14 srv_paramsetoutput
4.11.15 srv_paramstatus
4.11.16 srv_paramtype
4.11.17 srv_pfield
4.11.18 srv_pfieldex
4.11.19 srv_rpcdb
4.11.20 srv_rpcname
4.11.21 srv_rpcnumber
4.11.22 srv_rpcoptions
4.11.23 srv_rpcowner
4.11.24 srv_rpcparams
4.11.25 srv_senddone
4.11.26 srv_sendmsg
4.11.27 srv_sendrow
4.11.28 srv_setcoldata
4.11.29 srv_setcollen
4.11.30 srv_setutype
4.11.31 srv_willconvert
4.11.32 srv_wsendmsg
4.11.33 sp_addextendedproc
4.11.34 sp_dropextendedproc
4.11.35 sp_helpextendedproc
4.12 Extended stored procedures
4.12.1 xp_grantlogin - Use CREATE LOGIN
4.12.2 xp_revokelogin –Use DROP LOGIN
4.12.3 xp_loginConfig (Reports the login security configuration of an instance of SQL Server when it running on Windows XP, Windows Server 2003, or Windows 2000) - IsIntegratedSecurityOnly argument of SERVERPROPERTY
4.13 Functions
4.13.1 fn_get_sql - sys.dm_exec_sql_text
4.14 Index options
4.14.1 sp_indexoption- ALTER INDEX
4.14.2 CREATE TABLE, ALTER TABLE, or CREATE INDEX syntax without parentheses around the options - Rewrite the statement to use the current syntax.
4.15 Database objects
4.15.1 Ability to return result sets from triggers - None
4.16 Instance options
4.16.1 sp_configure 'allow updates' - System tables are no longer updatable. Setting has no effect.
4.16.2 sp_configure 'locks' (The locks option sets the maximum number of available locks, thereby limiting the amount of memory the SQL Server Database Engine uses for them.) - Now automatically configured. Setting has no effect.
4.16.3 sp_configure 'open objects' (This option is still present in sp_configure, although its functionality has been disabled in Microsoft SQL Server. (The setting has no effect.) In SQL Server, the number of open database objects is managed dynamically and is limited only by the available memory. The open objectsoption available in sp_configure for backward compatibility with existing scripts.) - Now automatically configured. Setting has no effect.
4.16.4 sp_configure 'set working set size' (This option is still present in the sp_configure stored procedure, but its functionality is unavailable in Microsoft SQL Server. (The setting has no effect.) - Now automatically configured. Setting has no effect.
4.16.5 sp_configure options 'user instances enabled' and 'user instance timeout' (These option that you can access through sp_configure are not supported in Microsoft SQL Server 2008. These options work only with SQL Server 2008 Express - No longer needed as SQL Server Compact 3.5 SP1 provides the necessary functionality. Setting has no effect.
4.16.6 sp_configure option 'priority boost' (specify whether Microsoft SQL Server should run at a higher Microsoft Windows 2000, Windows Server 2003, Windows 2008, or Windows 2008 R2 scheduling priority than other processes on the same computer.) – Removed as raising the priority too high may drain resources from essential operating system and network functions, resulting in problems shutting down SQL Server or using other operating system tasks on the server.
4.16.7 sp_configure option 'remote proc trans' - use distributed queries that reference linked servers.
4.17 Linked servers
4.17.1 Specifying the SQLOLEDB provider for linked servers - SQL Server Native Client (SQLNCLI)
4.18 Locking
4.18.1 sp_lock - sys.dm_tran_locks
4.19 Mail
4.19.1 SQL Mail - Database Mail
4.20 Metadata
4.20.1 FILE_ID - FILE_IDEX
4.20.2 INDEXKEY_PROPERTY - sys.index_columns
4.21 Native XML Web Services
4.21.1 The CREATE ENDPOINT or ALTER ENDPOINT statement with the FOR SOAP option - Use Windows Communications Foundation (WCF) or ASP.NET instead.
4.21.2 sys.endpoint_webmethods - Use Windows Communications Foundation (WCF) or ASP.NET instead.
4.21.3 sys.soap_endpoints - Use Windows Communications Foundation (WCF) or ASP.NET instead.
4.22 Programmability
4.22.1 SQL Server Database Management Objects (SQL-DMO) - SQL Server Management Objects (SMO)
4.23 Removable databases
4.23.1 sp_certify_removable (Verifies that a database is correctly configured for distribution on removable media and reports any problems to the user) - sp_detach_db (However, sp_attach_db is deprecated)
4.23.2 sp_create_removable (Creates a removable media database. Creates three or more files (one for the system catalog tables, one for the transaction log, and one or more for the data tables) and places the database on those files) - sp_detach_db
4.23.3 sp_dbremove - DROP DATABASE
4.24 Security
4.24.1 The ALTER LOGIN WITH SET CREDENTIAL syntax – Replaced by the new ALTER LOGIN ADD and DROP CREDENTIAL syntax
4.24.2 sp_addapprole - CREATE APPLICATION ROLE
4.24.3 sp_dropapprole - DROP APPLICATION ROLE
4.24.4 sp_addlogin- CREATE LOGIN
4.24.5 sp_droplogin - DROP LOGIN
4.24.6 sp_adduser - CREATE USER
4.24.7 sp_dropuser - DROP USER
4.24.8 sp_grantdbaccess - CREATE USER
4.24.9 sp_revokedbaccess - DROP USER
4.24.10 sp_addrole - CREATE ROLE
4.24.11 sp_droprole- CREATE ROLE
4.24.12 sp_approlepassword - ALTER APPLICATION ROLE
4.24.13 sp_password - ALTER LOGIN
4.24.14 sp_changeobjectowner- ALTER SCHEMA or ALTER AUTHORIZATION
4.24.15 sp_defaultdb - ALTER LOGIN
4.24.16 sp_defaultlanguage - ALTER LOGIN
4.24.17 sp_denylogin - ALTER LOGIN DISABLE
4.24.18 sp_grantlogin - CREATE LOGIN
4.24.19 sp_revokelogin- DROP LOGIN
4.24.20 USER_ID - DATABASE_PRINCIPAL_ID
4.24.21 sp_srvrolepermission and 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 2008. For more information, see Permissions of Fixed Server Roles (Database Engine)
4.24.22 GRANT ALL - GRANTspecific permissions.
4.24.23 DENY ALL – DENY specific permissions.
4.24.24 REVOKE ALL - REVOKE specific permissions.
4.24.25 PERMISSIONS intrinsic function - Query sys.fn_my_permissions instead.
4.24.26 SETUSER - EXECUTE AS
4.24.27 RC4 and DESX encryption algorithms - Use another algorithm such as AES.
4.25 SMO classes
4.25.1 Microsoft.SQLServer.Management.Smo.Information class – Use Microsoft.SqlServer.Management.Smo.Server class
4.25.2 Microsoft.SQLServer.Management.Smo.Settings class – Use Microsoft.SqlServer.Management.Smo.Serverclass
4.25.3 Microsoft.SQLServer.Management.Smo.DatabaseOptions class – Use Microsoft.SqlServer.Management.Smo.Databaseclass
4.25.4 Microsoft.SqlServer.Management.Smo.DatabaseDdlTrigger.NotForReplicationproperty - None
4.26 SQL Server Agent
4.26.1 net send notification - E-mail notification
4.26.2 Pager notification – E-mail notification
4.26.3 ActiveX subsystem - Command or PowerShell scripts
4.27 SQL Server Management Studio
4.27.1 Solution Explorer integration in SQL Server Management Studio - ?
4.27.2 Source Control integration in SQL Server Management Studio - ?
4.28 System tables – Use catalog views.
4.28.1 sysaltfiles
4.28.2 syscacheobjects
4.28.3 syscolumns
4.28.4 syscomments
4.28.5 sysconfigures
4.28.6 sysconstraints
4.28.7 syscurconfigs
4.28.8 sysdatabases
4.28.9 sysdepends
4.28.10 sysdevices
4.28.11 sysfilegroups
4.28.12 sysfiles
4.28.13 sysforeignkeys
4.28.14 sysfulltextcatalogs
4.28.15 sysindexes
4.28.16 sysindexkeys
4.28.17 syslockinfo
4.28.18 syslogins
4.28.19 sysmembers
4.28.20 sysmessages
4.28.21 sysobjects
4.28.22 sysoledbusers
4.28.23 sysopentapes
4.28.24 sysperfinfo
4.28.25 syspermissions
4.28.26 sysprocesses
4.28.27 sysprotects
4.28.28 sysreferences
4.28.29 sysremotelogins
4.28.30 sysservers
4.28.31 systypes
4.28.32 sysusers
4.28.33 sys.numbered_procedures – None
4.28.34 sys.numbered_procedure_parameters – None
4.29 System functions
4.29.1 fn_virtualservernodes - sys.dm_os_cluster_nodes
4.29.2 fn_servershareddrives - sys.dm_io_cluster_shared_drives
4.30 System views
4.30.1 sys.sql_dependencies - sys.sql_expression_dependencies
4.31 Table compression
4.31.1 The use of the vardecimal storage format - Vardecimal storage format is deprecated. SQL Server 2008 data compression compresses decimal values as well as other data types. We recommend that you use data compression instead of the vardecimal storage format.
4.31.2 Use of the sp_db_vardecimal_storage_format procedure - Vardecimal storage format is deprecated. SQL Server 2008 data compression compresses decimal values as well as other data types. We recommend that you use data compression instead of the vardecimal storage format.
4.31.3 Use of the sp_estimated_rowsize_reduction_for_vardecimal procedure - Use data compression and the sp_estimate_data_compression_savings procedure instead
4.32 Table hints
4.32.1 Specifying NOLOCK or READUNCOMMITTED in the FROM clause of an UPDATE or DELETE statement - Remove the NOLOCK or READUNCOMMITTED table hints from the FROM clause.
4.32.2 Specifying table hints without using the WITH keyword - Use WITH.
4.32.3 HOLDLOCK table hint without parenthesis – use ( )
4.32.4 INSERT_HINTS - ???
4.33 Textpointers
4.33.1 WRITETEXT(Permits minimally logged, interactive updating of an existing text, ntext, or image column. WRITETEXT overwrites any existing data in the column it affects. WRITETEXT cannot be used ontext, ntext, and image columns in views.) - Use the large-value data types and the .WRITE clause of the UPDATE statement instead.
4.33.2 UPDATETEXT (Updates an existing text, ntext, or image field. Use UPDATETEXT to change only a part of a text,ntext, or image column in place. Use WRITETEXT to update and replace a whole text, ntext, orimage field) -- Use the large-value data types and the .WRITE clause of the UPDATE statement instead.
4.33.3 , READTEXT (Reads text, ntext, or image values from a text, ntext, or image column, starting from a specified offset and reading the specified number of bytes.) - Use the SUBSTRING function instead.
4.33.4 TEXTPTR() (Returns the text-pointer value that corresponds to a text, ntext, or image column in varbinary format. ) – None
4.33.5 TEXTVALID() (A text, ntext, or image function that checks whether a specific text pointer is valid.) – None
4.34 Transact-SQL
4.34.1 :: function-calling sequence - Replaced by SELECT column_list FROM sys.<function_name>().
4.34.2 Three-part and four-part column references - Two-part names is the standard-compliant behavior.
4.34.3 A string enclosed in quotation marks 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
4.34.4 Numbered procedures - None. Do not use.
4.34.5 table_name.index_name syntax in DROP INDEX - index_name ON table_name syntax in DROP INDEX.
4.34.6 Not using a statement terminator for Transact-SQL statement.- End Transact-SQL statements with a statement terminator, which is a semicolon ( ; ).
4.34.7 GROUP BY ALL - Use custom case-by-case solution with UNION or derived table.
4.34.8 ROWGUIDCOL as a column name in DML statements - Use $rowguid.
4.34.9 IDENTITYCOL as a column name in DML statements - Use $identity.
4.34.10 Use of #, ## as temporary table and temporary stored procedure names - Use at least one additional character.
4.34.11 Use of @, @@, or @@ as Transact-SQL identifiers - Do not use @ or @@ or names that begin with @@ as identifiers.
4.34.12 Use of DEFAULT keyword as default value - Do not use the word DEFAULT as a default value.
4.34.13 Use of a space as a separator between table hints or Multiple table hints without comma - Use a comma to separate table hints.
4.34.14 The select list of an aggregate indexed view must contain COUNT_BIG (*) (rather than COUNT (*) in 90 compatibility mode - Use COUNT_BIG (*), not COUNT(*)
4.34.15 The indirect application of table hints to an invocation of a multi-statement table-valued function (TVF) through a view - None
4.34.16 ALTER DATABASE syntax: MODIFY FILEGROUP READONLY and MODIFY FILEGROUP READWRITE - MODIFY FILEGROUP READ_ONLY and MODIFY FILEGROUP READ_WRITE
4.35 Other
4.35.1 DB-Library Embedded SQL for C – None
5. SQL Server 2008 R2 - Features Not Supported in the Next Version of SQL Server
5.1 Backup and restore
5.1.1 BACKUP { DATABASE | LOG } WITH PASSWORD – None
5.1.2 BACKUP { DATABASE | LOG } WITH MEDIAPASSWORD – None
5.1.3 RESTORE { DATABASE | LOG } … WITH DBO_ONLY- RESTORE { DATABASE | LOG } … … WITH RESTRICTED_USER
5.1.4 RESTORE { DATABASE | LOG } WITH PASSWORD – None
5.1.5 RESTORE { DATABASE | LOG } WITH MEDIAPASSWORD – None
5.2 Compatibility levels
5.2.1 80 compatibility level and upgrade from version 80 - Compatibility levels are only available for the last two versions. For more information about compatibility levels.
5.3 Metadata
5.3.1 DATABASEPROPERTY- DATABASEPROPERTYEX
5.4 Memory Management
5.4.1 Address Windowing Extensions (AWE) support that allows 32-bit operating systems to use over 4 GB of physical memory - Use a 64-bit operating system to use over 4 GB of physical memory.
5.5 Database objects
5.5.1 WITH APPEND clause on triggers- Re-create the whole trigger.
5.6 Instance options
5.6.1 Default setting of disallow results from triggers option = 0 - Default setting of disallow results from triggers option = 1
5.7 Database options
5.7.1 sp_dboption- ALTER DATABASE
5.8 Query hints
5.8.1 FASTFIRSTROW hint - OPTION (FAST n).
5.9 Remote servers
5.9.1 sp_addremotelogin - Replace remote servers by using linked servers
5.9.2 sp_addserver - Replace remote servers by using linked servers
5.9.3 sp_dropremotelogin - Replace remote servers by using linked servers
5.9.4 sp_helpremotelogin - Replace remote servers by using linked servers
5.9.5 sp_remoteoption - Replace remote servers by using linked servers
5.9.6 @@remserver - Replace remote servers by using linked servers.
5.9.7 SET REMOTE_PROC_TRANSACTIONS - Replace remote servers by using linked servers.
5.10 Security
5.10.1 sp_dropalias - Replace aliases with a combination of user accounts and database roles. Use sp_dropalias to remove aliases in upgraded databases.
5.11 SET Options
5.11.1 SET DISABLE_DEF_CNST_CHK- None. Option has no effect.
5.11.2 SET ROWCOUNT for INSERT, UPDATE, and DELETE statements – use TOP
5.12 Transact-SQL syntax
5.12.1 Use of *= and =* - Use ANSI join syntax.
5.12.2 COMPUTE [ BY]- Use Rollup
5.13 System tables
5.13.1 sys.database_principal_aliases- Use roles instead of aliases.
5.14 Tools
5.14.1 sqlmaint Utility - Use the SQL Server maintenance plan feature
5.15 Transact-SQL
5.15.1 The old style syntax for RAISERROR (Format: RAISERROR integer string) syntax is deprecated - Rewrite the statement using the current RAISERROR syntax.
5.16 Programmability
5.16.1 SQL Server Database Management Objects (SQL-DMO) - SQL Server Management Objects (SMO)
6. SQL Server 2008 R2 - Features Not Supported in a Future Version of SQL Server
6.1 Compatibility levels
6.1.1 sp_dbcmptlevel - ALTER DATABASE … SET COMPATIBILITY_LEVEL
6.1.2 Database compatibility level 90 - Plan to upgrade the database and application for a future release.
6.2 XML
6.2.1 Inline XDR Schema Generation - The XMLDATA directive to the FOR XML option is deprecated. Use XSD generation in the case of RAW and AUTO modes. There is no replacement for the XMLDATA directive in EXPLICT mode.
6.3 Backup and Restore
6.3.1 BACKUP { DATABASE | LOG } TO TAPE or BACKUP { DATABASE | LOG } TO device_that_is_a_tape - BACKUP { DATABASE | LOG } TO DISK or BACKUP { DATABASE | LOG } TOdevice_that_is_a_disk
6.3.2 sp_addumpdevice 'tape' - sp_addumpdevice 'disk'
6.3.3 sp_helpdevice- sys.backup_devices
6.4 Collations
6.4.1 Korean_Wansung_Unicode - None. The collation exist in SQL Server 2005, but are not visible through fn_helpcollations.
6.4.2 Lithuanian_Classic- None. The collation exist in SQL Server 2005, but are not visible through fn_helpcollations.
6.4.3 SQL_AltDiction_CP1253_CS_AS - None. The collation exist in SQL Server 2005, but are not visible through fn_helpcollations.
6.4.4 Hindi - Exists in SQL Server 2005 and higher, but is not visible through fn_helpcollations. Use Indic_General_90 instead
6.4.5 Macedonian - Exist in SQL Server 2005 and higher, but is not visible through fn_helpcollations. Use Macedonian_FYROM_90.
6.4.6 Azeri_Latin_90- Azeri_Latin_100
6.4.7 Azeri_Cyrilllic_90 - Azeri_Cyrilllic_100
6.5 Configurations (see 4.5)
6.5.1 SET ANSI_NULLS OFF and ANSI_NULLS OFF database option, SET ANSI_PADDING OFF and ANSI_PADDING OFF database option; SET CONCAT_NULL_YIELDS_NULL OFF and CONCAT_NULL_YIELDS_NULL OFF database option – None, will always be set to ON.
6.5.2 SET OFFSETS - will be unavailable.
6.6 Data types
6.6.1 sp_addtype - CREATE TYPE
6.6.2 sp_droptype - DROP TYPE
6.6.3 timestamp syntax for rowversion data type - rowversion data type syntax
6.6.4 Ability to insert null values into timestamp columns- Use a DEFAULT instead.
6.6.5 'text in row' table option - Use varchar(max), nvarchar(max), andvarbinary(max) data types.
6.6.6 Data types: text, ntext,image - Use varchar(max), nvarchar(max), andvarbinary(max) data types.
6.7 Database management
6.7.1 sp_attach_db and 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.
6.8 Database objects
6.8.1 CREATE DEFAULT, DROP DEFAULT, sp_bindefault, sp_unbindefault - DEFAULT keyword in CREATE TABLE and ALTER TABLE
6.9 Database objects
6.9.1 CREATE RULE, DROP RULE, sp_bindrule, sp_unbindrule - CHECK keyword in CREATE TABLE and ALTER TABLE
6.9.2 sp_change_users_login - Use ALTER USER.
6.9.3 sp_depends - Use sys.dm_sql_referencing_entities and sys.dm_sql_referenced_entities
6.9.4 sp_renamedb- Use MODIFY NAME in ALTER DATABASE
6.9.5 sp_getbindtoken - Use MARS or distributed transactions.
6.9.6 sp_bindsession- Use MARS or distributed transactions.
6.9.7 sp_resetstatus - ALTER DATABASE SET { ONLINE | EMERGENCY }
6.10 Database options
6.10.1 TORN_PAGE_DETECTION option of ALTER DATABASE- PAGE_VERIFY TORN_PAGE_DETECTION option of ALTER DATABASE
6.11 DBCC
6.11.1 DBCC DBREINDEX - REBUILD option of ALTER INDEX.
6.11.2 DBCC INDEXDEFRAG - REORGANIZE option of ALTER INDEX
6.11.3 DBCC SHOWCONTIG - sys.dm_db_index_physical_stats
6.11.4 DBCC [UN]PINTABLE - Has no effect.
6.12 Extended properties
6.12.1 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 such as TABLE or VIEW, or level-2 types such as COLUMN or TRIGGER.
6.13 Extended stored procedure programming (Use CLR Integration instead.)
6.13.1 srv_alloc
6.13.2 srv_convert
6.13.3 srv_describe
6.13.4 srv_getbindtoken
6.13.5 srv_got_attention
6.13.6 srv_message_handler
6.13.7 srv_paramdata
6.13.8 srv_paraminfo
6.13.9 srv_paramlen
6.13.10 srv_parammaxlen
6.13.11 srv_paramname
6.13.12 srv_paramnumber
6.13.13 srv_paramset
6.13.14 srv_paramsetoutput
6.13.15 srv_paramstatus
6.13.16 srv_paramtype
6.13.17 srv_pfield
6.13.18 srv_pfieldex
6.13.19 srv_rpcdb
6.13.20 srv_rpcname
6.13.21 srv_rpcnumber
6.13.22 srv_rpcoptions
6.13.23 srv_rpcowner
6.13.24 srv_rpcparams
6.13.25 srv_senddone
6.13.26 srv_sendmsg
6.13.27 srv_sendrow
6.13.28 srv_setcoldata
6.13.29 srv_setcollen
6.13.30 srv_setutype
6.13.31 srv_willconvert
6.13.32 srv_wsendmsg
6.13.33 sp_addextendedproc
6.13.34 sp_dropextendedproc
6.13.35 sp_helpextendedproc
6.14 Extended stored procedures
6.14.1 xp_grantlogin - Use CREATE LOGIN
6.14.2 xp_revokelogin - Use DROP LOGIN
6.14.3 xp_loginConfig – Use IsIntegratedSecurityOnly argument of SERVERPROPERTY
6.15 Functions
6.15.1 fn_get_sql - sys.dm_exec_sql_text
6.16 Index options
6.16.1 sp_indexoption - ALTER INDEX
6.16.2 CREATE TABLE, ALTER TABLE, or CREATE INDEX syntax without parentheses around the options - Rewrite the statement to use the current syntax.
6.17 Database objects
6.17.1 Ability to return result sets from triggers – None
6.18 Instance options
6.18.1 sp_configure option 'allow updates' - System tables are no longer updatable. Setting has no effect.
6.18.2 sp_configure 'locks', sp_configure 'open objects', sp_configure 'set working set size' - Now automatically configured. Setting has no effect.
6.18.3 sp_configure 'user instances enabled' and sp_configure 'user instance timeout' - No longer needed as SQL Server Compact 3.5 SP2 provides the necessary functionality. Setting has no effect.
6.18.4 sp_configure 'priority boost' - System tables are no longer updatable. Setting has no effect. Use the Windows start /high … program.exe option instead (see 4.16.6 as well)
6.18.5 sp_configure option 'remote proc trans' - Use sp_addlinkedserver (for linked servers and distributed queries)
6.19 Linked servers
6.19.1 Specifying the SQLOLEDB provider for linked servers - SQL Server Native Client (SQLNCLI)
6.20 Locking
6.20.1 sp_lock - sys.dm_tran_locks
6.21 Mail
6.21.1 SQL Mail - Database Mail
6.22 Metadata
6.22.1 FILE_ID - FILE_IDEX
6.22.2 INDEXKEY_PROPERTY - sys.index_columns
6.23 Native XML Web Services
6.23.1 The CREATE ENDPOINT or ALTER ENDPOINT statement with the FOR SOAP option. sys.endpoint_webmethods, sys.soap_endpoints - Use Windows Communications Foundation (WCF) or ASP.NET instead.
6.24 Programmability
6.24.1 The Microsoft.NetEnterpriseServers namespace – replaced by The Microsoft.SqlServer.MessageBox namespace.
6.25 Removable databases
6.25.1 sp_certify_removable and sp_create_removable – use sp_detach_db
6.25.2 sp_dbremove - DROP DATABASE
6.26 Security
6.26.1 ALTER LOGIN WITH SET CREDENTIAL - Replaced by the new ALTER LOGIN ADD and DROP CREDENTIAL syntax
6.26.2 sp_addapprole and sp_dropapprole - CREATE APPLICATION ROLE and DROP APPLICATION ROLE
6.26.3 sp_addlogin and sp_droplogin - CREATE LOGIN and DROP LOGIN
6.26.4 sp_adduser and sp_dropuser - CREATE USER and DROP USER
6.26.5 sp_grantdbaccess and sp_revokedbaccess - CREATE USER and DROP USER
6.26.6 sp_addrole and sp_droprole - CREATE ROLE and DROP ROLE
6.26.7 sp_approlepassword - ALTER APPLICATION ROLE
6.26.8 sp_password - ALTER LOGIN
6.26.9 sp_changeobjectowner - ALTER SCHEMA or ALTER AUTHORIZATION
6.26.10 sp_defaultdb and sp_defaultlanguage - ALTER LOGIN
6.26.11 sp_denylogin - ALTER LOGIN DISABLE
6.26.12 sp_grantlogin - CREATE LOGIN
6.26.13 sp_revokelogin - DROP LOGIN
6.26.14 USER_ID - DATABASE_PRINCIPAL_ID
6.26.15 sp_srvrolepermission AND 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 2008. For more information, see Permissions of Fixed Server Roles (Database Engine).
6.26.16 GRANT ALL, DENY ALL, REVOKE ALL - GRANT, DENY, and REVOKE specific permissions.
6.26.17 PERMISSIONS intrinsic function - Query sys.fn_my_permissions instead.
6.26.18 SETUSER - EXECUTE AS
6.26.19 RC4 and DESX encryption algorithms - Use another algorithm such as AES.
6.27 SMO classes
6.27.1 Microsoft.SQLServer.Management.Smo.Information class - Microsoft.SqlServer.Management.Smo.Serverclass
6.27.2 Microsoft.SQLServer.Management.Smo.Settings class - Microsoft.SqlServer.Management.Smo.Serverclass
6.27.3 Microsoft.SQLServer.Management.Smo.DatabaseOptions class - Microsoft.SqlServer.Management.Smo.Database class
6.27.4 Microsoft.SqlServer.Management.Smo.DatabaseDdlTrigger.NotForReplication property - None
6.28 SQL Server Agent
6.28.1 net send notification - E-mail notification
6.28.2 Pager notification - E-mail notification
6.28.3 ActiveX subsystem - Command or PowerShell scripts
6.29 SQL Server Management Studio
6.29.1 Solution Explorer integration in SQL Server Management Studio Source Control integration in SQL Server Management Studio – None
6.30 System tables – Use Compatibility views
6.30.1 sysaltfiles
6.30.2 syscacheobjects
6.30.3 syscolumns
6.30.4 syscomments
6.30.5 sysconfigures
6.30.6 sysconstraints
6.30.7 syscurconfigs
6.30.8 sysdatabases
6.30.9 sysdepends
6.30.10 sysdevices
6.30.11 sysfilegroups
6.30.12 sysfiles
6.30.13 sysforeignkeys
6.30.14 sysfulltextcatalogs
6.30.15 sysindexes
6.30.16 sysindexkeys
6.30.17 syslockinfo
6.30.18 syslogins
6.30.19 sysmembers
6.30.20 sysmessages
6.30.21 sysobjects
6.30.22 sysoledbusers
6.30.23 sysopentapes
6.30.24 sysperfinfo
6.30.25 syspermissions
6.30.26 sysprocesses
6.30.27 sysprotects
6.30.28 sysreferences
6.30.29 sysremotelogins
6.30.30 sysservers
6.30.31 systypes
6.30.32 sysusers
6.30.33 numbered_procedures – None
6.30.34 numbered_procedure_parameters – None
6.31 System functions
6.31.1 fn_virtualservernodes - sys.dm_os_cluster_nodes
6.31.2 fn_servershareddrives - sys.dm_io_cluster_shared_drives
6.32 System views
6.32.1 sys.sql_dependencies - sys.sql_expression_dependencies
6.33 Table compression
6.33.1 The use of the vardecimal storage format - Vardecimal storage format is deprecated. SQL Server 2008 data compression compresses decimal values as well as other data types. We recommend that you use data compression instead of the vardecimal storage forma.
6.33.2 Use of the sp_db_vardecimal_storage_format procedure - Vardecimal storage format is deprecated. SQL Server 2008 data compression compresses decimal values as well as other data types. We recommend that you use data compression instead of the vardecimal sorage format.
6.33.3 Use of the sp_estimated_rowsize_reduction_for_vardecimal procedure- Use data compression and the sp_estimate_data_compression_savings procedure instead.
6.34 Table hints
6.34.1 Specifying NOLOCK or READUNCOMMITTED in the FROM clause of an UPDATE or DELETE statement - Remove the NOLOCK or READUNCOMMITTED table hints from the FROM clause.
6.34.2 Specifying table hints without using the WITH keyword – Use WITH
6.34.3 HOLDLOCK table hint without parenthesis – Use ( )
6.34.4 INSERT_HINTS – None
6.35 Textpointers
6.35.1 UPDATETEXT/WRITETEXT and READTEXT – None
6.35.2 TEXTPTR() and TEXTVALID() – None
6.36 Transact-SQL
6.36.1 :: function-calling sequence - Replaced by SELECT column_list FROM sys.<function_name>().
6.36.2 Three-part and four-part column references - Two-part names is the standard-compliant behavior.
6.36.3 A string enclosed in quotation marks used as a column alias for an expression in a SELECT list: 'string_alias' = expression – replaced by expression [AS] column_alias / expression [AS] [column_alias] / expression [AS] "column_alias" /expression [AS] 'column_alias' / column_alias = expression
6.36.4 Numbered procedures – None, do not use
6.36.5 table_name.index_name syntax in DROP INDEX - index_name ON table_name syntax in DROP INDEX.
6.36.6 Not ending Transact-SQL statements with a semicolon - End Transact-SQL statements with a semicolon ( ; ).
6.36.7 GROUP BY ALL - Use custom case-by-case solution with UNION or derived table.
6.36.8 ROWGUIDCOL as a column name in DML statements - Use $rowguid.
6.36.9 IDENTITYCOL as a column name in DML statements - Use $identity.
6.36.10 Use of #, ## as temporary table and temporary stored procedure names - Use at least one additional character.
6.36.11 Use of @, @@, or @@ as Transact-SQL identifiers - Do not use @ or @@ or names that begin with @@ as identifiers.
6.36.12 Use of DEFAULT keyword as default value - Do not use the word DEFAULT as a default value.
6.36.13 Use of a space as a separator between table hints - Use a comma to separate table hints.
6.36.14 The select list of an aggregate indexed view must contain COUNT_BIG (*) in 90 compatibility mode - Use COUNT_BIG (*).
6.36.15 The indirect application of table hints to an invocation of a multi-statement table-valued function (TVF) through a view - None.
6.36.16 MODIFY FILEGROUP READONLY and MODIFY FILEGROUP READWRITE – replaced by MODIFY FILEGROUP READ_ONLY and MODIFY FILEGROUP READ_WRITE
6.37 Other
6.37.1 DB-Library Embedded SQL for C – Use the SQLClient namespace or an API such as OLE DB or ODBC.
6.37.2 VIA protocol – Use TCP
7. SQL Server 2008 R2 - Features Not Supported in the Next Version of SQL Server
7.1 Backup and Restore
7.1.1 RESTORE { DATABASE | LOG } WITH [MEDIA]PASSWORD continues to be deprecated.
7.1.2 BACKUP { DATABASE | LOG } WITH PASSWORD and BACKUP { DATABASE | LOG } WITH MEDIAPASSWORD are discontinued.
7.2 Compatibility levels
7.2.1 90 compatibility level and upgrade from version 90 (SQL Server 2005 Database Engine) - Compatibility levels are only available for the last two versions.
7.3 Database objects
7.3.1 Ability to return result sets from triggers – None
7.4 Encryption
7.4.1 Encryption using RC4 or RC4_128 is deprecated and is scheduled to be removed in the next version. Decrypting RC4 and RC4_128 is not deprecated - Use another encryption algorithm such as AES.
7.5 Remote servers - Replace remote servers by using linked servers.
7.5.1 sp_addremotelogin
7.5.2 sp_addserver - can only be used with the local option.
7.5.3 sp_dropremotelogin
7.5.4 sp_helpremotelogin
7.5.5 sp_remoteoption
7.5.6 @@remserver - Replace remote servers by using linked servers.
7.5.7 SET REMOTE_PROC_TRANSACTIONS - Replace remote servers by using linked servers.
7.6 Set options
7.6.1 SET ROWCOUNT for INSERT, UPDATE, and DELETEstatements - Use TOP keyword
7.7 Table hints
7.7.1 HOLDLOCK table hint without parenthesis - Use HOLDLOCK with parenthesis.
7.8 Tools
7.8.1 sqlmaint Utility - Use the SQL Server maintenance plan feature
8. SQL Server 2008 R2 - Features Not Supported in a Future Version of SQL Server
8.1 Compatibility levels
8.1.1 sp_dbcmptlevel - ALTER DATABASE … SET COMPATIBILITY_LEVEL.
8.1.2 Database compatibility level 100 - Plan to upgrade the database and application for a future release.
8.2 XML
8.2.1 Inline XDR Schema Generation - The XMLDATA directive to the FOR XML option is deprecated. Use XSD generation in the case of RAW and AUTO modes. There is no replacement for the XMLDATA directive in EXPLICT mode.
8.3 Backup and restore
8.3.1 BACKUP { DATABASE | LOG } TO TAPE, BACKUP { DATABASE | LOG } TO device_that_is_a_tape – Use BACKUP { DATABASE | LOG } TO DISK, BACKUP { DATABASE | LOG } TOdevice_that_is_a_disk
8.3.2 sp_addumpdevice 'tape' - sp_addumpdevice 'disk'
8.3.3 sp_helpdevice - sys.backup_devices
8.4 Collations
8.4.1 Korean_Wansung_Unicode, Lithuanian_Classic, SQL_AltDiction_CP1253_CS_AS - None. These collations exist in SQL Server 2005, but are not visible through fn_helpcollations.
8.4.2 Hindi and Macedonian - These collations exist in SQL Server 2005 and higher, but are not visible through fn_helpcollations. Use Indic_General_90 and Macedonian_FYROM_90 instead.
8.4.3 Azeri_Latin_90 and Azeri_Cyrilllic_90 – replaced by Azeri_Latin_100 and Azeri_Cyrilllic_100
8.5 Configuration
8.5.1 SET ANSI_NULLS OFF and ANSI_NULLS OFF database option – None, will always be set to ON
8.5.2 SET ANSI_PADDING OFF and ANSI_PADDING OFF database option – None, will always be set to ON
8.5.3 SET CONCAT_NULL_YIELDS_NULL OFF and CONCAT_NULL_YIELDS_NULL OFF database option– None, will always be set to ON
8.5.4 SET OFFSETS - will be unavailable.
8.6 Data Types
8.6.1 sp_addtype and sp_droptype - CREATE TYPE and DROP TYPE
8.6.2 timestamp syntax for rowversion data type - rowversion data type syntax
8.6.3 Ability to insert null values into timestamp columns - Use a DEFAULT instead.
8.6.4 'text in row' table option - Use varchar(max), nvarchar(max), andvarbinary(max) data types.
8.6.5 Data types: text, ntext, image - Use varchar(max), nvarchar(max), andvarbinary(max) data types
8.7 Database management
8.7.1 sp_attach_db and 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.
8.8 Database objects
8.8.1 CREATE DEFAULT, DROP DEFAULT, sp_bindefault, sp_unbindefault - DEFAULT keyword in CREATE TABLE and ALTER TABLE.
8.8.2 CREATE RULE, DROP RULE, sp_bindrule, sp_unbindrule - CHECK keyword in CREATE TABLE and ALTER TABLE
8.8.3 sp_change_users_login- Use ALTER USER.
8.8.4 sp_depends - sys.dm_sql_referencing_entities and sys.dm_sql_referenced_entities
8.8.5 sp_renamedb - MODIFY NAME in ALTER DATABASE
8.8.6 sp_getbindtoken - Use MARS or distributed transactions.
8.8.7 sp_bindsession - Use MARS or distributed transactions
8.8.8 sp_resetstatus - ALTER DATABASE SET { ONLINE | EMERGENCY }
8.8.9 TORN_PAGE_DETECTION option of ALTER DATABASE - PAGE_VERIFY TORN_PAGE_DETECTION option of ALTER DATABASE
8.9 DBCC
8.9.1 DBCC DBREINDEX - REBUILD option of ALTER INDEX.
8.9.2 DBCC INDEXDEFRAG - REORGANIZE option of ALTER INDEX
8.9.3 DBCC SHOWCONTIG - sys.dm_db_index_physical_stats
8.9.4 DBCC PINTABLE and DBCC UNPINTABLE - Has no effect.
8.10 Extended properties
8.10.1 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 such as TABLE or VIEW, or level-2 types such as COLUMN or TRIGGER.
8.11 Extended stored procedure programming - Use CLR Integration instead.
8.11.1 srv_alloc
8.11.2 srv_convert
8.11.3 srv_describe
8.11.4 srv_getbindtoken
8.11.5 srv_got_attention
8.11.6 srv_message_handler
8.11.7 srv_paramdata
8.11.8 srv_paraminfo
8.11.9 srv_paramlen
8.11.10 srv_parammaxlen
8.11.11 srv_paramname
8.11.12 srv_paramnumber
8.11.13 srv_paramset
8.11.14 srv_paramsetoutput
8.11.15 srv_paramstatus
8.11.16 srv_paramtype
8.11.17 srv_pfield
8.11.18 srv_pfieldex
8.11.19 srv_rpcdb
8.11.20 srv_rpcname
8.11.21 srv_rpcnumber
8.11.22 srv_rpcoptions
8.11.23 srv_rpcowner
8.11.24 srv_rpcparams
8.11.25 srv_senddone
8.11.26 srv_sendmsg
8.11.27 srv_sendrow
8.11.28 srv_setcoldata
8.11.29 srv_setcollen
8.11.30 srv_setutype
8.11.31 srv_willconvert
8.11.32 srv_wsendmsg
8.11.33 sp_addextendedproc
8.11.34 sp_dropextendedproc
8.11.35 sp_helpextendedproc
8.12 Extended stored procedures
8.12.1 xp_grantlogin - Use CREATE LOGIN
8.12.2 xp_revokelogin - Use DROP LOGIN
8.12.3 xp_loginConfig - IsIntegratedSecurityOnly argument of SERVERPROPERTY
8.13 Functions
8.13.1 fn_get_sql - sys.dm_exec_sql_text
8.14 High availability
8.14.1 Database mirroring - AlwaysOn Availability Groups. If your edition of SQL Server does not support AlwaysOn Availability Groups, use log shipping.
8.15 Index options
8.15.1 sp_indexoption - ALTER INDEX
8.15.2 CREATE TABLE, ALTER TABLE, or CREATE INDEX syntax without parentheses around the options - Rewrite the statement to use the current syntax.
8.16 Instance options
8.16.1 sp_configure option 'allow updates'- System tables are no longer updatable. Setting has no effect.
8.16.2 sp_configure 'locks', sp_configure 'open objects', and sp_configure 'set working set size' - Now automatically configured. Setting has no effect.
8.16.3 sp_configure 'priority boost' - System tables are no longer updatable. Setting has no effect. Use the Windows start /high … program.exe option instead.
8.16.4 sp_configure option 'remote proc trans' - Use sp_addlinkedserver (for linked servers and distributed queries)
8.17 Linked servers
8.17.1 Specifying the SQLOLEDB provider for linked servers – Use SQL Server Native Client (SQLNCLI)
8.18 Locking
8.18.1 sp_lock - sys.dm_tran_locks
8.19 Metadata
8.19.1 FILE_ID - FILE_IDEX
8.19.2 INDEXKEY_PROPERTY - sys.index_columns
8.20 Native XML Web Services
8.20.1 The CREATE ENDPOINT or ALTER ENDPOINT statement with the FOR SOAP option, sys.endpoint_webmethods, sys.soap_endpoints - Use Windows Communications Foundation (WCF) or ASP.NET instead.
8.21 Removable databases
8.21.1 sp_certify_removable and sp_create_removable - sp_detach_db
8.21.2 sp_dbremove - DROP DATABASE
8.22 Security
8.22.1 ALTER LOGIN WITH SET CREDENTIAL - Replaced by the new ALTER LOGIN ADD and DROP CREDENTIAL syntax
8.22.2 sp_addapprole and sp_dropapprole - CREATE APPLICATION ROLE and DROP APPLICATION ROLE
8.22.3 sp_addlogin and sp_droplogin - CREATE LOGIN and DROP LOGIN
8.22.4 sp_adduser and sp_dropuser - CREATE USERand DROP USER
8.22.5 sp_grantdbaccess and sp_revokedbaccess - CREATE USER and DROP USER
8.22.6 sp_addrole and sp_droprole - CREATE ROLE and DROP ROLE
8.22.7 sp_approlepassword and sp_password- ALTER APPLICATION ROLE and ALTER LOGIN
8.22.8 sp_changeobjectowner - ALTER SCHEMA or ALTER AUTHORIZATION
8.22.9 sp_defaultdb and sp_defaultlanguage - ALTER LOGIN
8.22.10 sp_denylogin - ALTER LOGIN DISABLE
8.22.11 sp_grantlogin - CREATE LOGIN
8.22.12 sp_revokelogin - DROP LOGIN
8.22.13 USER_ID - DATABASE_PRINCIPAL_ID
8.22.14 sp_srvrolepermission and 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 2008.
8.22.15 GRANT ALL, DENY ALL, REVOKE ALL - GRANT, DENY, and REVOKE specific permissions.
8.22.16 PERMISSIONS intrinsic function - Query sys.fn_my_permissions instead.
8.22.17 SETUSER - EXECUTE AS
8.22.18 RC4 and DESX encryption algorithms - Use another algorithm such as AES
8.23 SET Options
8.23.1 SET FMTONLY – replaced by sys.dm_exec_describe_first_result_set (Transact-SQL), sys.dm_exec_describe_first_result_set_for_object (Transact-SQL), sp_describe_first_result_set (Transact-SQL), and sp_describe_undeclared_parameters (Transact-SQL).
8.24 Server Configuration Options
8.24.1 sp_configure 'c2 audit mode' (C2 audit mode can be configured through SQL Server Management Studio or with the c2 audit mode option in sp_configure. Selecting this option will configure the server to record both failed and successful attempts to access statements and objects. This information can help you profile system activity and track possible security policy violations) - common criteria compliance enabled Server Configuration Option
8.24.2 sp_configure 'default trace enabled' (Use the default trace enabled option to enable or disable the default trace log files. The default trace functionality provides a rich, persistent log of activity and changes primarily related to the configuration options) – use Extended Events
8.25 SMO classes
8.25.1 Microsoft.SQLServer.Management.Smo.Information class - Microsoft.SqlServer.Management.Smo.Serverclass
8.25.2 Microsoft.SQLServer.Management.Smo.Settings class - Microsoft.SqlServer.Management.Smo.Serverclass
8.25.3 Microsoft.SQLServer.Management.Smo.DatabaseOptions class - Microsoft.SqlServer.Management.Smo.Databaseclass
8.25.4 Microsoft.SqlServer.Management.Smo.DatabaseDdlTrigger.NotForReplicationproperty – None
8.26 SQL Server Agent
8.26.1 net send notification - E-mail notification
8.26.2 Pager notification - E-mail notification
8.26.3 ActiveX subsystem - Command or PowerShell scripts
8.27 SQL Server Management Studio
8.27.1 Solution Explorer integration in SQL Server Management Studio, Source Control integration in SQL Server Management Studio – None
8.28 System Stored Procedures
8.28.1 sp_db_increased_partitions - None. Support for increased partitions is available by default in SQL Server 2012
8.29 System tables – Use Compatibility views.
8.29.1 sysaltfiles
8.29.2 syscacheobjects
8.29.3 syscolumns
8.29.4 syscomments
8.29.5 sysconfigures
8.29.6 sysconstraints
8.29.7 syscurconfigs
8.29.8 sysdatabases
8.29.9 sysdepends
8.29.10 sysdevices
8.29.11 sysfilegroups
8.29.12 sysfiles
8.29.13 sysforeignkeys
8.29.14 sysfulltextcatalogs
8.29.15 sysindexes
8.29.16 sysindexkeys
8.29.17 syslockinfo
8.29.18 syslogins
8.29.19 sysmembers
8.29.20 sysmessages
8.29.21 sysobjects
8.29.22 sysoledbusers
8.29.23 sysopentapes
8.29.24 sysperfinfo
8.29.25 syspermissions
8.29.26 sysprocesses
8.29.27 sysprotects
8.29.28 sysreferences
8.29.29 sysremotelogins
8.29.30 sysservers
8.29.31 systypes
8.29.32 sysusers
8.29.33 numbered_procedures – None
8.29.34 numbered_procedure_parameters – None
8.30 System functions
8.30.1 fn_virtualservernodes - sys.dm_os_cluster_nodes
8.30.2 fn_servershareddrives - sys.dm_io_cluster_shared_drives
8.31 System views
8.31.1 sys.sql_dependencies - sys.sql_expression_dependencies
8.32 Table compression
8.32.1 The use of the vardecimal storage format - Vardecimal storage format is deprecated. SQL Server 2012 data compression, compresses decimal values as well as other data types. We recommend that you use data compression instead of the vardecimal storage format.
8.32.2 Use of the sp_db_vardecimal_storage_format procedure - Vardecimal storage format is deprecated. SQL Server 2012 data compression, compresses decimal values as well as other data types. We recommend that you use data compression instead of the vardecimal storage format.
8.32.3 Use of the sp_estimated_rowsize_reduction_for_vardecimal procedure- Use data compression and the sp_estimate_data_compression_savings procedure instead.
8.33 Table hints
8.33.1 Specifying NOLOCK or READUNCOMMITTED in the FROM clause of an UPDATE or DELETE statement - Remove the NOLOCK or READUNCOMMITTED table hints from the FROM clause.
8.33.2 Specifying table hints without using the WITH keyword - Use WITH.
8.33.3 INSERT_HINTS – None???
8.34 Textpointers
8.34.1 WRITETEXT /UPDATETEXT /READTEXT – None
8.34.2 TEXTPTR() and TEXTVALID() – None
8.35 Transact-SQL
8.35.1 :: function-calling sequence - Replaced by SELECT column_list FROM sys.<function_name>().
8.35.2 Three-part and four-part column references - Two-part names is the standard-compliant behavior.
8.35.3 A string enclosed in quotation marks used as a column alias for an expression in a SELECT list: 'string_alias' = expression – Use expression [AS] column_alias, expression [AS] [column_alias], expression [AS] "column_alias", expression [AS] 'column_alias', and column_alias = expression
8.35.4 Numbered procedures – None
8.35.5 table_name.index_name syntax in DROP INDEX - index_name ON table_name syntax in DROP INDEX.
8.35.6 Not ending Transact-SQL statements with a semicolon - End Transact-SQL statements with a semicolon ( ; ).
8.35.7 GROUP BY ALL - Use custom case-by-case solution with UNION or derived table.
8.35.8 ROWGUIDCOL as a column name in DML statements - Use $rowguid.
8.35.9 IDENTITYCOL as a column name in DML statements- Use $identity.
8.35.10 Use of #, ## as temporary table and temporary stored procedure names - Use at least one additional character.
8.35.11 Use of @, @@, or @@ as Transact-SQL identifiers - Do not use @ or @@ or names that begin with @@ as identifiers.
8.35.12 Use of DEFAULT keyword as default value- Do not use the word DEFAULT as a default value.
8.35.13 Use of a space as a separator between table hints - Use a comma to separate table hints.
8.35.14 The select list of an aggregate indexed view must contain COUNT_BIG (*) in 90 compatibility mode - Use COUNT_BIG (*).
8.35.15 The indirect application of table hints to an invocation of a multi-statement table-valued function (TVF) through a view – None.
8.35.16 MODIFY FILEGROUP READONLY and MODIFY FILEGROUP READWRITE - MODIFY FILEGROUP READ_ONLY and MODIFY FILEGROUP READ_WRITE
8.36 Other
8.36.1 DB-Library Embedded SQL for C – Use the SQLClient namespace or an API such as ODBC.
8.37 Tools
8.37.1 SQL Server Profiler for Trace Capture - Use Extended Events Profiler embedded in SQL Server Management Studio.
8.37.2 SQL Server Profiler for Trace Replay - SQL Server Distributed Replay (The Microsoft SQL Server Distributed Replay feature helps you assess the impact of future SQL Server upgrades. You can also use it to help assess the impact of hardware and operating system upgrades, and SQL Server tuning.)
8.38 Trace Management Objects
8.39 SQL Trace stored procedures, functions, and catalog views – replaced with Extended Events
8.39.1 sp_trace_create
8.39.2 sp_trace_setevent
8.39.3 sp_trace_setfilter
8.39.4 sp_trace_setstatus
8.39.5 fn_trace_geteventinfo
8.39.6 fn_trace_getfilterinfo
8.39.7 fn_trace_getinfo
8.39.8 fn_trace_gettable
8.39.9 sys.traces
8.39.10 sys.trace_events
8.39.11 sys.trace_event_bindings
8.39.12 sys.trace_categories
8.39.13 sys.trace_columns
8.39.14 sys.trace_subclass_values