I’m going to start a new series on backing up SQL Server. This was inspired by @Kendra_Little in one of her Dear SQL DBA podcasts In this series I want to work through why we need backups, how to do backups, and the different types of backups that you could do and why you would […]
If you have multiple temporal tables that join together, can you retrieve the data from the temporal table at a specific point in time? We’ll find out by running a test. First up, let’s create 2 system versioned tables and put some data in them: Next, we’ll update both records which will add data to […]
While trying to debug performance for an application the other day, I noticed that the expected behavior for the application was to use Read Committed Snapshot Isolation level, however when a query was run it seems that it is running in isolation level Read Committed. Why the change? Testing the problem I’m using the Stack Overflow database […]
I often need to update a record with an aggregate of another table. It’s often a parent record that has a record of the sum of a column in all of it’s child records. It seems like a really simple case of having an update statement and setting the value of the column to the […]
Knowing the difference between join types is a very important part of querying a database. The two main joins that I use each day are INNER and LEFT. INNER join means get me everything from table A that has a matching row in table B. LEFT means get me everything from table A and if […]
This week, I was trying to improve the performance of a bit of SQL and noticed that it generated significantly different query plans depending on the parameters passed in. So I tried to work out how to make it run well for all scenarios.
I recently patched some servers from SQL 2014 SP2 CU3 all the way up to CU10. The patching went fine, everything came back online and looked to be working perfectly. However, the next day I realised that the CDC Collection Job was not running. After much googling and trying to rebuild the collection job, I […]
Kendra Little over at SQL Workbooks recently answered the question “When should I use explicit transactions for single statements?” I’m not going to re-hash what Kendra wrote, go and read it on her site. What I want to do is add another case for when I use explicit transactions for a single statement. Firstly, what is an […]
A current project I’m working on has it’s database stored in Source Control (you should be using source control) using a Visual Studio .sqlproj file. Visual Studio is able to generate a SQL script to update the database to make the objects the same as what is in Source Control. One of the things it […]
A few years ago I needed to join a table to the result of a table-value function and came across Cross Apply. Cross Apply and Outer Apply were introduced back in SQL 2005.
