A Nonclustered Index Update Disaster

Update execution plans are not something the T-SQL statement writer has much control over. You can affect the data reading side of the plan with query rewrites and hints, but there's not nearly as much tooling available to affect the writing side of the plan.

Update processing can be extremely complex and reading data-changing execution plans correctly can also be difficult. Many important details are hidden away in obscure and poorly documented properties, or simply not present at all.

In this article, I want to you show a particularly bad update plan example. It has value in and of itself, but it will also give me a chance to describe some less well-known SQL Server details and behaviours.

Read the full article on 𝕏.

Don't Mix with Datetime

Microsoft encourages us not to use the datetime data type: 

Avoid using datetime for new work. Instead, use the time, date, datetime2, and datetimeoffset data types. These types align with the SQL Standard, and are more portable. time, datetime2 and datetimeoffset provide more seconds precision. datetimeoffset provides time zone support for globally deployed applications.

Well, ok. Sensible and well-informed people might still choose to use datetime for performance reasons. Common date and time functions have optimised implementations in the SQL Server expression service for the datetime and smalldatetime data types.

The newer data types don't have this optimized support. They use more general algorithms, often involving a conversion to and from datetimeoffset during intermediate calculations. You can't usually see these extra steps in execution plans but trust me they are there. If you do heaps of DATEADD or DATEDIFF calculations, you'll likely be running your own data type performance tests to verify that.

On the other hand, you might choose from among the newer data types for equally good reasons. Perhaps you value smaller storage size, or greater range and precision over raw speed.

Regardless, the point of this article is you should not mix datetime with the newer data and time types. You'll avoid some quite counterintuitive behaviour and even a bug or two. Let me explain.

Read the full article on 𝕏