Teaching Old Dogs New Tricks

Revitalizing a SQL Server Code Base One Version at a Time

Kevin Feasel (@feaselkl)
https://csmore.info/on/olddogs

Who Am I? What Am I Doing Here?

Catallaxy Services
Curated SQL
Finding Ghosts in Your Data

@feaselkl

Motivation

Databases tend to last a lot longer than applications.

The primary database(s) you work with have likely been around for decades, in some form or another.

SQL Server has changed a lot since those databases were new, and there may be better ways to solve problems than existed before.

Let's bring some vitality back to those old databases and make our lives easier in the process!

Agenda

  1. Ground Rules
  2. SQL Server 2012
  3. SQL Server 2014
  4. SQL Server 2016
  5. SQL Server 2017
  6. SQL Server 2019
  7. SQL Server 2022

Ground Rules

  1. We will focus only on T-SQL improvements, not external products (e.g., SSIS, SSAS, SSRS, ML Services)
  2. We will focus on enhancements in functionality, not necessarily performance--we will ignore most adaptive query processing changes
  3. We will focus on "like to like" replacements, not "net new" things--we will ignore PolyBase, In-Memory OLTP, cardinality estimators, Query Store, etc.
  4. We will start with SQL Server 2012 and move forward version by version.

Agenda

  1. Ground Rules
  2. SQL Server 2012
  3. SQL Server 2014
  4. SQL Server 2016
  5. SQL Server 2017
  6. SQL Server 2019
  7. SQL Server 2022

Functionality We Won't Discuss

  • Columnstore indexes
  • Availability groups

Demo Time

Agenda

  1. Ground Rules
  2. SQL Server 2012
  3. SQL Server 2014
  4. SQL Server 2016
  5. SQL Server 2017
  6. SQL Server 2019
  7. SQL Server 2022

Functionality We Won't Discuss

  • New cardinality estimator
  • In-Memory OLTP
  • Delayed Durability
  • Updatable columnstore indexes
  • SELECT INTO now parallelizable

Demo Time

Agenda

  1. Ground Rules
  2. SQL Server 2012
  3. SQL Server 2014
  4. SQL Server 2016
  5. SQL Server 2017
  6. SQL Server 2019
  7. SQL Server 2022

Functionality We Won't Discuss

  • Query Store
  • Adaptive Query Processing
  • Temporal tables
  • PolyBase to Hadoop / Azure Blob Storage
  • Always Encrypted
  • Row-Level Security
  • Dynamic Data Masking
  • R Services

Demo Time

Agenda

  1. Ground Rules
  2. SQL Server 2012
  3. SQL Server 2014
  4. SQL Server 2016
  5. SQL Server 2017
  6. SQL Server 2019
  7. SQL Server 2022

Functionality We Won't Discuss

  • SQL Server on Linux / Containers
  • ML Services and Python support
  • Adaptive Query Processing updates: batch mode and interleaved execution
  • Graph database
  • Distributed availability groups

Demo Time

Agenda

  1. Ground Rules
  2. SQL Server 2012
  3. SQL Server 2014
  4. SQL Server 2016
  5. SQL Server 2017
  6. SQL Server 2019
  7. SQL Server 2022

Functionality We Won't Discuss

  • Intelligent Query Processing
  • Memory-optimized TempDB metadata
  • Accelerated Database Recovery
  • Batch mode on rowstore operations
  • PolyBase to ODBC
  • UTF-8 support
  • ML Services and Java support

Demo Time

Agenda

  1. Ground Rules
  2. SQL Server 2012
  3. SQL Server 2014
  4. SQL Server 2016
  5. SQL Server 2017
  6. SQL Server 2019
  7. SQL Server 2022

Functionality We Won't Discuss

  • Intelligent Query Processing updates
    • Parameter sensitive plan optimization
    • Degree of parallelism feedback
    • Cardinality estimation feedback
    • Query Store hints
  • Ledger tables
  • Purview and Synapse integration
  • Managed Instance link
  • PolyBase to APIs (S3, Azure Blob Storage, Azure Data Lake Storage Gen2)

Demo Time

Wrapping Up

The T-SQL language has changed a lot in the past decade. Between new functionality and enhancements to the existing core language, there are plenty of opportunities to work with existing code bases and make them better.

Wrapping Up

To learn more, go here:
https://csmore.info/on/olddogs


And for help, contact me:
feasel@catallaxyservices.com | @feaselkl


Catallaxy Services consulting:
https://CSmore.info/on/contact