Jeremy Smyth’s Blog

Some programmers are happy to do as asked, following the spec, and building useful code that does what it’s supposed to do, and no more.

Other programmers are happy to question the spec, to discover business functionality that may not have been known, and to engage more in design.

Sometimes, and ideally, these functions are separated; you have a programmer who solves problems made clear in the spec, and you have a software project manager/domain expert who designs the solution to be implemented by a programmer.

Having said that, it’s not unusual to find the two functions in a single person, particularly a developer with more experience within the problem domain (in fact, it’s hard to find a project manager who’s experienced enough to design a good solution, who isn’t an experienced programmer).

Standard project management technique would suggest you start with the scoping, and the environment (the “why” and the “what”), and only when you get to identifying the work, to get down to the “how”.

Frequently, the guys involved in organising the scope are not the guys doing the actual nitty-gritty work, so by the time they’re involved, the “why” and “what” should ideally have been specced out.

With software development, particularly with iterative methods, the “what” is usually figured out as part of each iteration, which lets the “why” leak in, and should ideally involve lots of two-way communication between the developers and the client.

This isn’t always how it works though. As I said above, it’s an ideal world that can separate the functions, and often a programmer with enough experience to connect business analysis with programming will already be filling that role in any given project.

Posted in Project Management | | No Comments »

Let’s say you want to insert (or, for our example, update) a whole set of random numbers into a table.

You may try this:

…only you find, to your amazement, that SQL Server has put the same number into each row.

Hmm.

In SQL Server, when rand() is called multiple times in the same query (e.g. for multiple rows in an update statement), it usually returns the same number.

Two problems:

* Firstly, the rand() function returns a number between 0 and 1.
* Secondly, when rand() is called multiple times in the same query (e.g. for multiple rows in an update statement), it usually returns the same number (which I suspect your algorithm above is trying to solve, by splitting it into multiple calls)

My favourite way around this problem is to use a function that’s guaranteed to return a unique value each time, like NEWID(), convert it to binary, and use it as the seed.

This works because NEWID() is guaranteed to return a new GUID (a globally unique 16-byte number) each time it’s invoked. We must convert this to binary before using it, as RAND() won’t accept GUIDs as its seed.

So, although RAND() ordinarily gives the same random value for each row in an update, we get over the problem with RAND by giving it a different seed for each row using a function that gives a different result for each row.

Tags: SQL Server
Posted in Databases, SQL Server 2005 | | No Comments »

It’s not unheard of for a company to change e-mail domain in mid-thrust; maybe it’s been bought out, or rebranded, or the parent company has spun it off to its own brand.

Only you’ve got hundreds of employees, each one with their own email address, and your MySQL database is in dire need of updating to reflect this.

To get around this, you’ll need to replace the relevant part of each email string within an update statement, grabbing the hostname substring (after the ‘@’) with a REPLACE, and replacing it.

Note: REPLACE() is case-sensitive, so if needs be, you can use LOWER(email) inside the REPLACE function if you need to catch all case possibilities, as below:

This will also convert all your email addresses to lowercase, so be aware of that.

Tags: MySQL
Posted in Databases, MySQL | | 3 Comments »