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!
[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:
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:
To see the difference in behavior, check out the JSFiddle example using my version of throttle().