Systems Engineering and RDBMS

Archive for May 28th, 2008

Windows 7 and Multi-Touch

Posted by decipherinfosys on May 28, 2008

By now, you must have heard about Microsoft Surface – we had also covered it before in one of our posts. If you haven’t looked at it, here is the link. Some of those multi-touch innovations are making it’s way to the next version of Windows – Windows 7. All things digital had posted an interview of Bill Gates and Steve Ballmer and it has a video that demonstrates the multi-touch capabilities of Windows 7.  It’s a very interesting demo and a very good chat with Mr. Gates and Mr. Ballmer.  Interesting times are ahead…

Posted in Technology, Windows | Leave a Comment »

Enhancements to the OUTPUT clause in SQL Server 2008

Posted by decipherinfosys on May 28, 2008

We had covered the OUTPUT and the OUTPUT INTO clauses in SQL Server 2005 before in this post. Prior to SQL Server 2008, as you can see from the examples from that post, one could not filter out the records that are returned using the OUTPUT/OUTPUT INTO clauses i.e. one can choose only the different columns that one wants in those clauses but cannot choose which rows one wants to return. As a result, one has to dump all of that data into a table and then apply a filter criteria (WHERE clause) at that table in order to sub-select the records that one wants to get.

In SQL Server 2008, one of the new T-SQL enhancements makes this pretty simple. One can now define a table expression based off a modification statement with an OUTPUT clause and then apply the filters on it. For Example:

INSERT INTO dbo.PO_DTL (….)
SELECT ….
FROM (UPDATE dbo.INPT_PO_DTL
SET Quantity *= 1.5
OUTPUT
inserted.PO_DTL_ID,
deleted.Quantity AS Old_Qty,
inserted.Quantity AS New_Qty
WHERE RECVD_DATE > getdate() – 10) AS IV
WHERE IV.Old_Qty < 100.0 AND New_Qty >= 100.0
GO

One can also join that data set with another table and do processing.  The biggest advantage (besides not having another table and another set of steps to achieve the same thing is the fact that we are reducing the number of records that we have to retrieve – previously, if the data set returned would have been large, we would have first stored it in a temp table and then applied a filter on it.  If that data set was huge, it would have had performance implications.  With this new feature, we can easily reduce that overhead and get only the records that we want/need.

You can also see the new syntax of ” *= ” for the multiplication of the data – that is the compound assignment operator in SQL Server 2008.

Posted in SQL Server | 1 Comment »

 
Follow

Get every new post delivered to your Inbox.

Join 77 other followers