Hi Friends,

In this post I want to show you how you can find out SQL Server 2014 New System Objects, yourself!

From SQL Server 2014 VM (or a physical box), add a Linked Server connection to a SQL Server 2012 VM (or a physical box)

And execute the following script:

SELECT a.NAME sql14_objname
    ,a.xtype sql14_objtype
FROM sys.sysobjects a
LEFT JOIN [WIN2K8R2\SQL2012SP1].master.sys.sysobjects b ON a.NAME = b.NAME
ORDER BY sql14_objtype

And you get the output as follows:

sql14_objname sql14_objtype
fn_hadr_is_primary_replica FN NULL
fn_column_store_row_groups IF NULL
fn_dblog_xtp IF NULL
fn_dump_dblog_xtp IF NULL
dm_db_stats_properties_internal IF NULL
sqlagent_job_history IT NULL
sqlagent_jobs IT NULL
sqlagent_jobsteps IT NULL
sqlagent_jobsteps_logs IT NULL
sp_cci_tuple_mover P NULL
sp_sqlagent_add_job P NULL
sp_sqlagent_add_jobstep P NULL
sp_sqlagent_delete_job P NULL
sp_sqlagent_help_jobstep P NULL
sp_sqlagent_log_job_history P NULL
sp_sqlagent_start_job P NULL
sp_sqlagent_stop_job P NULL
sp_sqlagent_verify_database_context P NULL
sp_sqlagent_write_jobstep_log P NULL
sp_xtp_bind_db_resource_pool P NULL
sp_xtp_checkpoint_force_garbage_collection P NULL
sp_xtp_control_proc_exec_stats P NULL
sp_xtp_control_query_exec_stats P NULL
sp_xtp_merge_checkpoint_files P NULL
sp_xtp_unbind_db_resource_pool P NULL
syscsrowgroups S NULL
sysextfileformats S NULL
sysextsources S NULL
sysexttables S NULL
fn_sqlagent_job_history TF NULL
fn_sqlagent_jobs TF NULL
fn_sqlagent_jobsteps TF NULL
fn_sqlagent_jobsteps_logs TF NULL
fn_sqlagent_subsystems TF NULL
hash_indexes V NULL
dm_db_xtp_checkpoint_files V NULL
dm_db_xtp_checkpoint_stats V NULL
dm_db_xtp_gc_cycle_stats V NULL
dm_db_xtp_hash_index_stats V NULL
dm_db_xtp_index_stats V NULL
dm_db_xtp_memory_consumers V NULL
dm_db_xtp_merge_requests V NULL
dm_db_xtp_nonclustered_index_stats V NULL
dm_db_xtp_object_stats V NULL
dm_db_xtp_table_memory_stats V NULL
dm_db_xtp_transactions V NULL
dm_exec_query_profiles V NULL
dm_io_cluster_valid_path_names V NULL
dm_os_buffer_pool_extension_configuration V NULL
dm_resource_governor_resource_pool_volumes V NULL
dm_xtp_gc_queue_stats V NULL
dm_xtp_gc_stats V NULL
dm_xtp_system_memory_consumers V NULL
dm_xtp_threads V NULL
dm_xtp_transaction_recent_rows V NULL
dm_xtp_transaction_stats V NULL
column_store_row_groups V NULL
sp_flush_log X NULL


The object types are as follows:

FN = Scalar Function

IF = In-line Table Function

IT = Internal Table

P = Stored Procedure

S = System Table

TF = Table Function

V = View

X = Extended Stored Procedure

Want to watch this in action? Watch here!