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 simpleOPTION (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 acceptsCOLUMNSTOREandCOLUMNSTORE_ARCHIVEcompression types. - sys.dm_db_page_info
New function for determining the object and other info for a page, without the overhead ofsys.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 newADD SENSITIVITY CLASSIFICATIONstatement 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 (includingWITH (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
MERGEgainsMATCHsupport- 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 StudioAzure 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!
Microsoft Docs state that SSMS 18.0 doesn't have a T-SQL Debugger because it's deprecated. A pretty strange decision. Yes, T-SQL Debugger is very limited, but it's still better than nothing if you want to understand the control flow of a complex stored procedure.
I know people who still use the Query Analyzer that shipped with SQL Server 2000. If you really like the debugger, you'll be able to keep a copy of SSMS 17.x for as long as you like.
Personally I find it both useless and too restrictive. And since stored procedure code is, well, procedural and not OO, I don't find it adds any value over PRINT and RAISERROR and other debug mechanisms I can implement myself. YMMV.
Thanks for the good write up and links bud.
Thanks for the good new enthusiastic information about SQL Server 2019.