Some New Stuff In SQL Server 2022 CTP 2.1

Posted on by Erik Darling

New Baby

SQL Server 2022 CTP 2.1 dropped recently, and there’s some new stuff in it! What kind of new stuff?

Well, there’s some linguistic stuff:

  • APPROX_PERCENTILE_DISC
  • APPROX_PERCENTILE_CONT
  • DATETRUNC
  • IS [NOT] DISTINCT FROM
  • Bit manipulation:
    • LEFT_SHIFT
    • RIGHT_SHIFT
    • BIT_COUNT
    • GET_BIT
    • SET_BIT

How useful any of that will be depends on how you use SQL Server. I’ve never heard anyone yearning for bit manipulation functions, but perhaps I travel in the wrong circles.

Inside You

As far as “unannounced” stuff, here’s some of what I noticed:

There’s a new database scoped configuration called LEDGER_DIGEST_STORAGE_ENDPOINT. The name seems fairly self-documenting, but I can’t picture myself using ledger enough to explore it further.

There are a bunch of new objects, too:

+------------------------------------------------------------+----------------------------------+
|                            name                            |            type_desc             |
+------------------------------------------------------------+----------------------------------+
| dm_db_xtp_hash_index_approx_stats                          | SQL_INLINE_TABLE_VALUED_FUNCTION |
| dm_tran_distributed_transaction_stats                      | VIEW                             |
| extgov_attribute_sync_objects_synchronizing                | VIEW                             |
| sp_change_feed_configure_parameters                        | SQL_STORED_PROCEDURE             |
| sp_dw_physical_manifest_file_table_insert                  | EXTENDED_STORED_PROCEDURE        |
| sp_dw_physical_upsert                                      | EXTENDED_STORED_PROCEDURE        |
| sp_help_change_feed_table                                  | SQL_STORED_PROCEDURE             |
| sp_manage_distributed_transaction                          | EXTENDED_STORED_PROCEDURE        |
| sp_md_discovery_stats_staleness_detection                  | EXTENDED_STORED_PROCEDURE        |
| sp_reset_dtc_log                                           | EXTENDED_STORED_PROCEDURE        |
| sp_trident_create_credential_to_access_internal_md_storage | EXTENDED_STORED_PROCEDURE        |
+------------------------------------------------------------+----------------------------------+

Again, their usefulness will depend on how you use the product.

Here are some new extended events:

  • query_ce_feedback_telemetry
  • query_feedback_analysis
  • query_feedback_validation
  • resumable_add_constraint_executed

I don’t think I’ll need to add any of these to sp_HumanEvents, but I’m sure you’ll see them in lots of Microsoft demos.

There are also some new wait stats, but I think I’ll be ignoring them.

  • CDC_SCAN_FINISHED
  • CORRUPTED_PAGE_PROCESS
  • DW_DB
  • DW_WS_DB_LIST
  • NATIVE_SHUFFLE_WRITE_BUFFER_DEQUEUE
  • PARQUET_INDEX_BUILD_MANIFEST_SYNC
  • PURVIEW_POLICY_SDK_PREEMPTIVE_SCHEDULING
  • PWAIT_S3_TEMP_CREDENTIAL
  • RBAC_AAD_GROUP_INFO
  • SPINLOCK_EXT
  • WAIT_EXTGOV_PERMCACHE_DECISIONLOCK
  • WAIT_EXTGOV_PERMCACHE_RESOURCELOCK

Nothing all that juicy this time around. Maybe next time!

Thanks for reading!

Going Further

If this is the kind of SQL Server stuff you love learning about, you’ll love my training. I’m offering a 75% discount to my blog readers if you click from here. I’m also available for consulting if you just don’t have time for that, and need to solve database performance problems quickly. You can also get a quick, low cost health check with no phone time required.

Related Posts

2 thoughts on “Some New Stuff In SQL Server 2022 CTP 2.1”

  1. Bit manipulation comes in handy if you use Change Data Capture, have wide tables, have a high volume of changes, and only need to pay attention to changes in certain columns. The function that SQL Server makes available for that (sys.fn_cdc_is_bit_set) is much slower than doing a bitwise AND on the contents of the CDC table __$update_mask in order to find out if a column of interest has changed. Have been adding tracked column ordinal IDs raised to a power of 2 in order to create the mask to be ANDed with __$update_mask. That code works well enough, but isn’t very readable. The code would be cleaner with a SET_BIT function. Maybe that’s kind of a niche use of bit manipulations, but it’s real.

    1. Well, yeah, that’s why I said their usefulness will depend on how you use SQL Server. I’m just not looking forward to seeing them applied to wheres and joins.

Comments are closed.