Month: October 2018

Can't have a post about triggers without a gun image
Tools and Scripts

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. Continue reading Advanced Service Broker Sample: Asynchronous Triggers

Eitan BluminLeave a comment
Multi-Threading
Tools and Scripts

Advanced Service Broker Sample: Multi-Threading

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!

Continue reading Advanced Service Broker Sample: Multi-Threading

Eitan BluminLeave a comment
Obligatory Orphan-Related Imagery
Tools and Scripts

T-SQL Script to Fix Orphaned DB Users Easily

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

The symptoms that you can expect from such a scenario is inability to use such database users. Specifically, using the login to connect to the database that it’s supposed to be mapped to. It just doesn’t work. Continue reading T-SQL Script to Fix Orphaned DB Users Easily

Eitan BluminLeave a comment
Tools and Scripts

T-SQL Script to Roll Forward Transaction Log Backups

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. This script is especially useful for those … Continue reading T-SQL Script to Roll Forward Transaction Log Backups

Eitan BluminLeave a comment
Creativity has its price
Tools and Scripts

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 website from SQL Injection is fairly easy and you can find a lot of resources on that around the internet. Usually, this issue is solved by using stored procedures with strongly-typed parameters, thus ensuring that non-numerical values won’t be entered … Continue reading Dynamic Search Queries versus SQL Injection

Eitan Blumin1 Comment
It's a snapshot camera on a table... get it?
Tools and Scripts

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 table somewhere else, or on the same table in a later point in time. This script uses two useful concepts which, when combined, can give you a really nice solution for generating a “snapshot” for your table data. These two … Continue reading Generate MERGE Statement as a Snapshot of Your Tables Content

Eitan BluminLeave a comment
Joins
SQL Server Database Engine, SQL Server Performance

The LOOP, HASH and MERGE Join Types

Today I’ll talk about the available JOIN operator types in SQL Server (Nested Loops, Hash and Merge Joins), their differences, best practices and complexity. For the samples in this post, we’ll use the free AdventureWorks database sample available here: http://msftdbprodsamples.codeplex.com/releases/view/4004 Introduction: What are Join Operators? A join operator is a type of an algorithm which the SQL Server Optimizer chooses in order to implement logical … Continue reading The LOOP, HASH and MERGE Join Types

Eitan BluminLeave a comment