Welcome to blogs.conchango.com

I recently inherited some SQL that someone else had written and had the job of “tidying it up” before it gets pushed out to production. Here’s a slightly simplified (yes, simplified) version of that SQL: 1: --options 2: select asset_class 3: , case when volume > 0 then 'profit' 4: ...

UPDATE FROM is dead … or it should be. Hugo Kornelis has done a good job of explaining why. Apart from a badly thought out and non-portable syntax, UPDATE FROM suffers a notorious bug/feature. I say “notorious” but it’s probably not notorious enough because it is still common to see people get tripped up by it. If I write: UPDATE Alpha SET z ...

DB2 has some new features that are aimed at customers who want to transition from Oracle to DB2: http://www-05.ibm.com/ch/events/symposium/pdf/9_M_Obrecht_IBM_Symposium_2009.pdf IBM are emphasising the improved snapshot isolation, PL/SQL support and packages (“modules” in IBM’s terms). One “feature” that caught my eye is support for implicit ...

I have been dipping my toes into the cloud and found some pretty exciting stuff from Amazon. Simple DB is Amazon's web service database and it is now in unlimited beta which means that anyone can sign up and start using the service free (some restrictions), just signup here. There are loads of technical resources and also a pretty complete FAQ ...

Simon Evans some time ago questioned if given the parametric nature of queries generated by ORMs, stored procedures still have a place in developers toolbox? At the end of the day why bother if ORMs seem these days seem to do everything for free? It looks to me that statement that ''stored procedures are dead'' (although Simon has never said that) ...

Ok I admit - I use it. Pretty much everyone I know uses it and yet I feel dirty when I do.  Why? Well it's not portable SQL and most certainly not ANSI standard.  I need to get the datetime I hear you say.  How do I do this without getdate()?  There is a perfectly good substitute in the form of CURRENT_TIMESTAMP that is ...

A colleague of mine asked for a simple example to get him up and running with the basics of bitmasking.  Since I had gone to the effort to create the example I thought I might as well blog it :o). This example uses the logical AND operator &. First of all you need to define your table.. create table BitmaskDemo (ID int Primary ...

  This issue caught me out today whilst putting together some build scripts for reference data. So I thought I’d share it. USE [tempdb] GO CREATE TABLE [dbo].[Table_1](       [Col1] [int] IDENTITY(1,1) NOT NULL,       [Col2] [char](1) NOT NULL ) ON [PRIMARY] GO delete from ...

The MERGE syntax I used here has changed. In 2008 RC0 the ''WHEN SOURCE NOT MATCHED'' clause is replaced with ''WHEN NOT MATCHED BY SOURCE'' which is supposed to make the meaning clearer. Although MERGE is part of standard SQL 2003 it's worth noting that the SOURCE NOT MATCHED / NOT MATCHED BY SOURCE clause is not part of the standard. ...

Friday’s community day was all geared towards collaboration. What I found fascinating was the ''speed dating''. Not real dating, mind. The format was the same, but the outcome was to encourage all the different disciplines that make up Conchango to get to know each other better. So I had 3 minutes apiece to talk to five or six people from the ...