Deadlocks are typically encountered in high-load scenarios or in complex concurrent transactions. And the worst part is, you probably won’t see them until you’re live in production. Luckily, finding and solving deadlock issues isn’t as complicated as it may sound. All it takes is a bit of logging and patience, along with some basic know-how of how locking mechanisms work in SQL Server.
Category: Intermediate
Introduction to DML triggers
A trigger is like a stored procedure that automatically (and atomically) executes on a given condition, for instance when you insert a record into a table. Used properly, this is a powerful tool to enforce business rules, perform automated calculations, log changes, and more. But triggers also come with some potential pitfalls.
Indexing basics
Understanding indexes in SQL Server can help you build much more efficient database solutions. Often, performance problems can be adressed with indexes, so knowing how indexes work and how to set them up for the best performance are a great asset in your optimization work.
Basic string parsing
A lot of times, T-SQL queries aren’t just for compiling huge sets of aggregate calculations or advanced joins and indexing strategies. Sometimes, you need the skills and tools to do some basic string parsing. Here, you’ll learn the most common basic string parsing and functions found in T-SQL.
Recursive common table expressions
Remember all the times you’ve written long loop statements? Often, a recursive common table expression can be a much better solution, not to mention a lot more pleasing to the eye. The recursive CTE builds on the same basic framework as a regular CTE, except it consists of two parts: the anchor and the recursion.
APPLY tutorial
The APPLY operator can be a bit confusing, but if you master how it works, you can do great things with it. It is similar to a JOIN (hence the confusion), but differs in one key area: The JOIN defines a relation between two datasets using the ON keyword, whereas APPLY does not.
Indexed views
Indexed views are views where the computed view data is already calculated and stored in the database, which make them super-fast for querying. And if you’re using the expensive Enterprise Edition of SQL Server, you’ll enjoy performance increases in other queries that can make use of the indexed view as well.