Brian has a interesting post about his discovery of the new EXCEPT set operator in SQL Server 2005.  When I read his post I was at first excited, but then disappointed.  You see, the EXCEPT set operator could potentially save us from updating or inserting a large number of records in the data imports that our system runs.  Unfortunatly, we currently support BOTH Oracle and SQL Server so this new and exciting SQL Server 2005 feature wouldn't help us with many of our clients (since they're primarily on Oracle).  However, after a little bit of Googling I came across the MINUS operator in Oracle, which appears to be the equivalent of the SQL Server 2005 EXCEPT operator.  I'll have to dig into things a little more to know if the EXCEPT and MINUS set operators are equivalent, as well as run some tests to see what kind of impact it could have on our data imports, but it looks promising.

Tonight I found out the hard way that prior to version 5, MySQL don't support using an aggregate in an ORDER BY clause.  Unfortunetly my web host is currently on 4.1.21-standard of MySQL while my development machine is running 5.0.27-standard.  The end result is that some pages within the Rails app that I'm working on are not working so well on my staging box at Site5 :(

I tried to find details online about when ordering by aggregates was added, however, I didn't have any luck so all I know is that it works in the version on my dev machine, but not on the version that my web host is running.

Update: For those interested the work around is to include the aggregate in the select list with an alias, and then order by the alias.  So instead of including the aggregate in the order by like so:

SELECT Column1, Column2, etc FROM MyTable LEFT OUTER JOIN MyJoinedTable on MyTable.Id = MyJoinedTable.OtherId ORDER BY COUNT(*)

You instead need to do something along the lines of:

SELECT Column1, Column2, etc, COUNT(*) Cnt FROM MyTable LEFT OUTER JOIN MyJoinedTable on MyTable.Id = MyJoinedTable.OtherId ORDER BY Cnt

And yes, I know the SQL here is completely contrived and might not be showing why the order by count is needed but that's not the point.  And for those wondering, as far as I can tell Rails doesn't exactly make it easy for  me to do what I need to do.  I can write all the SQL myself, but I was using find along with an :include to pull in an association, which means the necessary SQL isn't something I want to write if I don't have to.

One of the things that I've missed about using FeedDemon as my RSS Newsreader is the ability to sync my unread items locally and read them while offline.  Since my current RSS Reader is a web app, Google Reader, I had accepted the fact that syncing unread items to read on the train ride home or elsewhere was something I would have to live without.  Apparently, I'm not very forward thinking because with Google Gears I can now sync locally and use Google Reader while offline!  In fact, with Google Gears, any web app can now add offline support and thus open a whole new world of possibilities.  Gears provides a client side relational database that can be accessed via Javascript.  Under the covers Gears uses SQLite.

What's even more interesting is the thought of how Microsoft might respond, or perhaps how they've already responded.  Imagine being able to write Silverlight applications in managed code, ruby, or whatever language tickles your fancy and persist data to a local SQL Server Compact edition using the common ADO.NET programming paradigms that we're all used to.  Next imagine ADO.NET Synchronization Services thrown into the mix so that local data in the SQL Server compact edition database can be synced back to the server. 

While the advancements made by Google Gears excites me, I'm not real big on being limited to programming against it with javascript.  The idea of building web apps that can now work offline, and use local databases that can sync back with the data center using Sync Services, all within the nice managed environment provided by Silverlight would be a sweet deal.  Heck, even if that doesn't happen we should be able to write a managed wrapper on top of Google Gears so that our managed code within Silverlight can take advantage of it.

This week we have a story to add the ability to extract some of the data within our system into Excel format.  Upon initially hearing the requirement I thought of Visual Studio Tools for Office (VSTO), but after digging into the documentation for a bit, it doesn't seem that it was meant for our scenario.  VSTO seems to be ideal for Office Add-ins but quite lacking in its support for server based scenarios.

We did a bit of research and found a couple commercial options such as Software Artisans OfficeWriter and Aspose.Cells. Additionally, this evening Aaron emailed me about Carlos Aguiar's ExcelXmlWriter project that provides a free utility for generating Excel files as well as a nice generator. 

I'd certainly be interested in hearing about any other components that anyone has used for generating Excel in a server environment and/or any experiences with the products mentioned above.

Today at work we had a brief discussion about whether using an IN or an EXISTS is more performant.  There were a couple different opinions, although most thought that EXISTS was faster.  It turns out that like most things in our wonderful software world there is not clear cut answer.  In general the below holds true:

• If the majority of the filtering criteria are in the subquery then the IN variation may be more performant.
• If the majority of the filtering criteria are in the top query then the EXISTS variation may be more performant.

Source: http://www.oracle-base.com/articles/misc/EfficientSQLStatements.php#Exists and this.

I assume the same holds true for SQL Server, although we're less interested in that at the moment.

Recently we've been discussing ways to increase the performance of some performance critical areas of our application.  As such, a number of individuals have been doing research into various options.  Through our investigation we came upon Oracle's Timesten In-Memory database.  I was familiar with many other In-Memory databases, such as Firebird (SQLite?, VistaDB?), but up until yesterday I wasn't aware that Oracle had a product in the in-memory space.  While Oracle doesn't currently offer a .NET native provider for Timesten, TankardSoft does offer a commercial native .NET data provider for Timesten that supports the recently released version 7 of Timesten. 

We've had several internal discussions about how we should allow our customers to extend and customize their data.  We considered a lot of different options and thought about a lot of the tradeoffs that need to be made for each approach.  It's because of this that I'm very interested in people's opinions, and as such would like to direct you to Jeremy Miller's "How do you extend and customize a database" post.  Please go and drop him a comment describing what approach has worked best for you so that we can all learn from your experiences! :)