dans.blog

One of the features introduced in Microsoft SQL 2005 that I think really goes largely unused in the "OUTPUT" clause. It certainly was a feature that went unnoticed by me for a long time and even once I became aware of the feature, didn't really put it to much use. However, lately I've been refactored a ton of old SQL into objects and with this refactoring I've been making high use of the OUTPUT clause in order to help create audit trails. It's been a great way to know which rows were affected in your CRUD operations.

There's really a ton of useful things you can do with the OUTPUT clause, but one thing it allows you to do is to migrate data from one table to another in a single atomic operation.

Let's look at the syntax:

-- the table to move data from
delete
    dbo.SourceTable

-- get the data removed from the "source" table
output
    deleted.column1, deleted.column2, deleted.column3

-- insert the deleted row into the "destination" table
into dbo.DestinationTable

-- your where clause for the "delete" operation
where
    Column1 > 2000
and
    Column2 = 1234

Here's a fully working example that you can run in SSMS:

-- declare temp tables
declare @original table (id int identity(1,1), name varchar(20), company varchar(20), dob datetime)
declare @new table (id int, name varchar(20), company varchar(20), dob datetime)

-- insert some fake data into our "original" table
insert into @original (name, company, dob) values ('Bill Gates', 'Microsoft', '1955-10-28')
insert into @original (name, company, dob) values ('Paul Allen', 'Microsoft', '1953-01-21')
insert into @original (name, company, dob) values ('Steve Jobs', 'Apple', '1955-02-24')
insert into @original (name, company, dob) values ('Steve Wozniak', 'Apple', '1950-08-11')

-- show the results
select 'original' as tableName, * from @original
select 'new' as tableName, * from @new

/*
  here is the core SQL used to do the move
*/
delete
  @original

-- get the data removed from the @original table
output
  deleted.id, deleted.name, deleted.company, deleted.dob
-- insert the deleted row into the @new table
into @new

-- your where clause for the "delete" operation
where
  company = 'Microsoft'


-- show the new results 
select 'original' as tableName, * from @original
select 'new' as tableName, * from @new

If you run this example in SSMS, you'll see the two "Microsoft" records move from the @original table into the @new table.

NOTE:

The @original and @new tables are simple table variables (which are like temp tables.)

There really is a ton of useful things you can do with the OUTPUT clause, so I highly recommend playing around with the OUTPUT clause if you're using SQL Server 2005 or later!

Posted by Dan on Aug 19, 2011 @ 8:00 AM

[UPDATED: Wednesday, August 24, 2011 at 3:19:27 PM]

I was looking through the source code of Underscore.js this morning and notice it's implementation of _.throttle() contains two issues that bother me with most JavaScript-based throttle implementations I've seen:

1. It delays the execution of the first hit until the "delay" has been reached. While this is ideal for debounce type operations, I believe throttle operations should execute immediately and then only execute after the delay for each additional call. Throttling works really well for mouse movement and scrolling operations, but you usually want the behavior to initiate when the operations begins—delaying the first execution until the delay is reach usually ends up with an odd behavior.
2. The arguments passed to the throttled event are based upon the invoking function that triggered the setTimeout() event. This means you are not dealing with the latest data passed to your throttled event, but dealing with expired data. A perfect example of this is using throttling to monitor mouse movements. The way Underscore.js has implemented the throttle() function, you could end up with coordinates based on where the pointer started—not where it ended.

Let's take a look at an example I posted on JSFiddle: http://jsfiddle.net/MNGpr/

Mouse your mouse from the top left of the "Results" frame to the bottom right. If you do this quickly (under the 1 second delay,) you'll notice that when the function executes it's based upon coordinates in the upper left of the screen—not where your cursor left off.

What I've been doing is using the following throttle() function in my code:

// limit a function to only firing once every XX ms
var throttle = function (fn, delay, trail){
  delay || (delay = 100);
  var last = 0, timeout, args, context, offset = (trail === false) ? 0 : delay;
  return function (){
    // we subtract the delay to prevent double executions
    var now = +new Date, elapsed = (now - last - offset);
    args=arguments, context=this;

    function exec(){
      // remove any existing delayed execution
      timeout && (timeout = clearTimeout(timeout));
      fn.apply(context, args);
      last = now;
    }

    // execute the function now
    if( elapsed > delay ) exec();
    // add delayed execution (this could execute a few ms later than the delay)
    else if( !timeout && trail !== false ) timeout = setTimeout(exec, delay);
  };
};

What this version does that differentiates it from the Underscore.js version is it:

1. Executes immediately upon first hit and then only after the specified delay. IMO, this provides a more desired effect for most throttled events. If you're really looking to do something when a user stops interacting with the screen, then use a debounce technique.
2. The last arguments are applied when executing the throttled function. This means that the latest version of the values are used, instead of the values supplied to the initial timed event.

To see the difference in behavior, check out the JSFiddle example using my version of throttle().

Posted by Dan on Aug 18, 2011 @ 12:40 PM