Aaron Bertrand

First public preview of SQL Server 2019 : CTP 2.0

September 24, 2018 by in SQL Server 2019 | 3 Comments
Free eBook : Query Optimization
SentryOne Newsletters

The SQLPerformance.com bi-weekly newsletter keeps you up to speed on the most recent blog posts and forum discussions in the SQL Server community.

eNews is a bi-monthly newsletter with fun information about SentryOne, tips to help improve your productivity, and much more.

Subscribe

Featured Author

Itzik is a T-SQL trainer, a co-founder of SolidQ, and blogs about T-SQL fundamentails and query tuning.

Itzik’s Posts

Today Microsoft has announced the first public preview of SQL Server 2019.

I've posted a recap detailing many of the features over at MSSQLTips.com, but here are some highlights:

  • Table variable deferred compilation
    SQL Server won't make hard-coded estimates of 1 row, but will compile with the number of rows in the table variable on first execution. I previously wrote about the pros and cons.
  • Row mode memory feedback
    As with columnstore in previous versions, the memory grants for row mode operations can be adjusted over time as they are found to be too small (spills) or too large (wasted memory).
  • Batch mode over rowstore
    Similar to the above, we can now benefit from batch mode operations even when no columnstore index is present (see Kevin Farlee's post).
  • APPROX_COUNT_DISTINCT
    A new aggregate function that uses statistics instead of reading all of the data to get a count of distinct values in a column. The outcome should be within 2% of the actual value 97% of the time, so great for ballparks or dashboards.
  • Compatibility Level Hints
    You can now dictate a query's exact compatibility level (and, by extension, the cardinality estimation model) with a simple OPTION (USE) hint (back-ported to 2016/2017 – see Pedro Lopes' post).
  • Lightweight profiling on by default
    Behavior similar to trace flag 7412, where you can access plan, runtime statistics, and extended event data for all currently executing queries. You can add a query hint for individual queries, too (also recently added to SQL Server 2016 and 2017).
  • CCI stats in Clone Databases
    Accurate stats info for clustered columnstore indexes will be made available in clones. Previously you needed to manually force post-creation updates, just before the clone operation, in order for them to come across. Mr. Columnstore himself, Niko Neugebauer, blogged about this in more detail here.
  • Compression Estimates for Columnstore
    The system procedure for estimating gains from compression, sys.sp_estimate_data_compression_savings, now accepts COLUMNSTORE and COLUMNSTORE_ARCHIVE compression types.
  • sys.dm_db_page_info
    New function for determining the object and other info for a page, without the overhead of sys.dm_db_database_page_allocations (as long as you already know the page number).
  • Secure Enclaves
    Perform encryption and decryption actions, encryption key rotation, and computations used for seeks and predicates, without shifting Always Encrypted data out of the server. (See Jakub Szymaszek's post.)
  • Certificate Management in Config Manager
    View and validate all of your certificates from a single interface, and manage and deploy certificate changes across all of the replicas in an Availability Group or all of the nodes in a Failover Cluster Instance.
  • Built-in data classification
    A new ADD SENSITIVITY CLASSIFICATION statement helps you identify and automatically audit sensitive data, a huge step up from the previous SSMS wizard (which just used extended properties).
  • Resumable online index rebuild/creation
    In SQL Server 2017 they added resumable online rebuilds for rowstore tables, now you can perform resumable online creation (including WITH (DROP_EXISTING = ON)) for rowstore, and online rebuild / creation for columnstore.
  • Secondary to Primary Connection Redirection
    Cluster doesn't support a listener? No problem! Now you can automatically redirect to the primary when there is some issue with the secondary.

A few other things I didn't really get into:

  • Persistent memory support
  • UTF-8 collations
  • Constraints for node/edge connections
  • MERGE gains MATCH support
  • Clustering support for Machine Learning Services
  • Replication and distributed transactions on Linux
  • Availability groups now support five synchronous replicas
  • Java support (implementation similar to Python/R)
  • A new version of Management Studio (18.0 Preview 4)
  • The evolution of SQL Operations Studio Azure Data Studio
  • A new endeavor you might have heard about by now called “Big Data Clusters

And still more stuff that hasn't been announced yet, but we'll see in future CTPs. Fasten your seatbelts, this is going to be an exciting release! Go get it!