Performing a transaction log backup every 15 minutes has its advantages. But it also has disadvantages. Ever tried to restore hundreds of transaction log backups one by one in order to bring your database back from disaster? Not a bit of fun that is. Check out this useful script that I prepared to help sort this problem out.
Read More »T-SQL Script to Roll Forward Transaction Log Backups
Today I’ll talk about the main physical JOIN operator types in SQL Server (Nested Loops, Hash and Merge Joins), their differences, best practices, and complexity (which determines when the SQL Server optimizer would use them in execution plans).
Read More »The LOOP, HASH and MERGE Join Types
Dynamic Search Queries SQL Injection is a widely known issue with databases that work as back-ends for websites and applications. In… Read More »Dynamic Search Queries versus SQL Injection
This is a special script I wrote which will let you “take a snapshot” of your table’s data, and apply… Read More »Generate MERGE Statement as a Snapshot of Your Tables Content
Unlike foreign keys, primary keys and unique indexes cannot be “untrusted”. There’s no “NOCHECK” option for unique constraints. The closest… Read More »The "IGNORE_DUP_KEY" option in Primary Keys and Unique Indexes
A lot of DBAs would spend hours (and even days) sitting in front of their computer to write a stupendous amount of lines of code… Then take care of a lot of copy-paste and typing errors… Test the code and find errors… Fix the code… Run it again… And so on. All the while having to navigate within a huge forest of code.
I would presume that it doesn’t take a special kind of mind to stand up and say “just hold on a minute! Why am I working so hard with this mundane task?? Why can’t SQL Server do all of this for me??”
Because in fact… It can!
Read More »Let SQL Server Write Code for You
Since SQL Server 2005, Microsoft had a nice addition to the common DML statements, UPDATE and DELETE, by allowing the use of the TOP keyword. Well, SQL Server 2019 has just been released, and yet unfortunately for many of us, SQL Server still doesn’t support the use of the TOP keyword in direct combination with the ORDER BY clause. So when we do need to update or delete the top x rows in a table based on a specific order, we’re bound to use all kinds of workarounds.
However, many of the commonly used workarounds are not as efficient as they can be.
Read More »Efficiently UPDATE and DELETE using TOP and ORDER BY
One fateful night, cold and dreary, I’ve stumbled upon how collations can significantly affect performance in SQL Server… Yes, really!
While normalizing a table in their database, one of my clients had weird and inconsistent performance problems and couldn’t find a solution for it for quite a while.
In this post, I will discuss how to remove the IDENTITY property from an existing column using T-SQL (without changing the order of the columns or moving the data into a new table).
Read More »Remove the IDENTITY property from an existing column
Microsoft SQL Server Reporting Services is an excellent and easy-to-use platform for creating, viewing, managing, and subscribing to reports of… Read More »Using Reporting Services as your Software Frontend