SQL Server Blog

This is a very common thing to need to do and will put together the pieces of my last two posts into one large post in order to achieve the desired outcome.

I’ve seen a good few questions about this on the internet as, although simple when you know how, there are a lot of people who struggle with the best way to achieve it.

Cleary this is a direct follow-up to my last post in which I showed how to merge a partition… this is the exact opposite in which we’ll split a partition into 2.

This is just as simplistic as the Merge and with pretty much the same syntax… however with SPLIT there’s a slight caveat that’s worth knowing and therefore I kept it in a separate post.

I’m going to briefly dip back into Partitioning for a few posts because this is something I was working on recently and I realized I didn’t have the code to hand on my blog… not that I rely on all code being present, but it’s definitely easier having something to hand than not.

This is actually a small piece of a larger partitioning function, but I decided that it warranted a separate post so here it is…

In my previous post I was stating that we need to be more intelligent with our stats updates rather than hitting a whole table with a sledgehammer and potentially suffering poor performance as a result.



However, wouldn’t it be great to be more proactive about the whole thing…

I’ve had a LOT of issues with stats over the years, mostly due to ascending keys, and I’m not the only one. However, the way in which I see people try to deal with these issues is quite interesting.

This is something that comes up rarely, but when it does it can be a real pain.



I’ve seen situations in which you have multiple triggers on the same table and these Triggers could also be set to fire from the same action.



This can cause a lot of issues.

This is another post about Triggers, but in this one we’re going to focus more on the Inserted and Deleted tables that a Trigger has access to.



This will explain how they’re created and therefore how Triggers can cause unexpected issues when reading Execution Plans.

As mentioned in my previous post, we have 2 types of Trigger available in SQL Server… AFTER and INSTEAD OF.



In this post I’m going to go through a few examples to show syntax and a couple of uses.



These examples will cover both types and also reference both the Inserted and Deleted tables that Triggers offer.

Triggers are kind of like stored procedures… they’re simply pieces of code that can execute based on another event… ie. They’re “triggered” by another event.



These triggers can perform follow-up events or they can actually replace the action that the user thinks is happening. The former is called an “AFTER” trigger and the latter an “INSTEAD OF” trigger.