October 2018

Can't have a post about triggers without a gun image

Advanced Service Broker Sample: Asynchronous Triggers

In my previous post, Advanced Service Broker Sample: Multi-Threading, I showed a rather advanced scenario where we can implement a multi-threading solution inside the SQL Server database engine.

In this post, I hope to show a simpler scenario of using Service Broker in SQL Server.
This time, I’ll start by handing out the API script itself and give brief explanation on how to use it.

Then, if you’re interested, you may continue reading for further explanations.

Advanced Service Broker Sample: Multi-Threading

2018-10-31
Tools and Scripts

I managed to encounter several times a scenario in which an application needed to execute several queries independent of each other, and then group them together somehow. However, each of these queries takes a while to execute.

One might wonder: If only we could use some kind of multi-threading mechanism to execute them in parallel, and then the total running time will be that of the slowest query only and not the total of them.

Well, it just so happens that our first sample will show how you can implement “multi-threading” in SQL Server using Service Broker queues!

T-SQL Script to Fix Orphaned DB Users Easily

What are Orphaned Users

“Orphaned Users” is a common issue in SQL Server where a Database User is no longer associated with its relevant Server Login.

This often happens when the Server Login is deleted (even if it’s recreated later), or when the database is moved or restored to a different SQL Server.

T-SQL Script to Roll Forward Transaction Log Backups

2018-10-28
Tools and Scripts

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.

The LOOP, HASH and MERGE Join Types

2018-10-28
SQL Server Database Engine, SQL Server Performance

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).

Dynamic Search Queries versus SQL Injection

Dynamic Search Queries SQL Injection is a widely known issue with databases that work as back-ends for websites and applications. In the most part, protecting your… Read More »Dynamic Search Queries versus SQL Injection

It's a snapshot camera on a table... get it?

Generate MERGE Statement as a Snapshot of Your Tables Content

This is a special script I wrote which will let you “take a snapshot” of your table’s data, and apply it on an identically structured… Read More »Generate MERGE Statement as a Snapshot of Your Tables Content

These are not the duplicates you're looking for

The "IGNORE_DUP_KEY" option in Primary Keys and Unique Indexes

Unlike foreign keys, primary keys and unique indexes cannot be “untrusted”. There’s no “NOCHECK” option for unique constraints. The closest there is, is the “ignore_dup_key”… Read More »The "IGNORE_DUP_KEY" option in Primary Keys and Unique Indexes

Let SQL Server Write Code for You

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!

Efficiently UPDATE and DELETE using TOP and ORDER BY

2018-10-28
SQL Server Performance, T-SQL

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.