Gareth Lennox

Don’t use @@IDENTITY

If you’re using SQL Server, and you’re inserting records into a table with an auto increment primary key, I suggest you don’t use @@IDENTITY to retrieve the previously inserted Id. Rather use SCOPE_IDENTITY().

The reason for this is that @@IDENTITY gives you the last generated Id in the current session vs. SCOPE_IDENTITY() gives you the last generated Id in the current scope.

The current session includes any triggers that run on the table after the insert. The current scope is just the current stored procedure that you’re in.

We were bitten by this recently. We were using @@IDENTITY when inserted into a table that is replicated. Upon insert the replication trigger was firing, inserting a record somewhere else. @@IDENTITY was giving us the identity of the record the trigger inserted.

Changing the @@IDENTITY to SCOPE_IDENTITY() fixed the problem.

This was even nastier in that by total fluke, the identity was matching a record that already existed, so when we used the retrieved identity to insert into another table, we didn’t violate the foreign key constraint.

Lesson learnt!

Update: I’ve found a page with an in-depth look at the different techniques. Its for SQL Server 2000, but applies to 2005/2008 too.

Google and search ranking

I’ve recently finished a new site for a client, and in the process of developing, I uploaded a test site to a web server for the client to review. Since going live with the actual site, I left the test site up and running (in the assumption that its less effort to leave it up than to take it down).

Unfortunately, google managed to find the test site and ranked it higher than the proper site for a couple of the keywords. Not good! Its a simple fix to put in a mod_rewrite redirect rule, but its quite incredible how quickly google managed to find a site that has no incoming links.

I have a feeling that its related to the site map plugin that we’re using for wordpress, I think that it pinged google when we were developing the site, and google happily went along and indexed the site.

So, if you’re developing a site, and put up a test version, make sure that you either take it down when making the site live, or ensure that it redirects to the correct site.

SQL Compact 3.5 Performance and NHibernate

We’re using SQL Compact 3.5 in a smart client desktop application, talking to it with NHibernate and are experiencing performance problems in a couple of places.

In general, we’re very happy with the database, it is most of the time pretty fast, and easy to work with. The fact that it runs within our process also helps, as its easy to install and use. This is especially useful, as we are going to be running on relatively low-spec machines (laptops with 500MB ram).

We are however seeing a performance problem in SELECT statements with multiple joins (> 5).

Continue reading →

Empty unit tests

I was reviewing some code, and noticed a couple of unit tests that had “TODO” comments in them (and no code). The problem is that when running these tests, they were passing. Someone reading the list of tests would assume that the test was testing what it claimed to be testing, when in actual fact its doing nothing.

The first option to avoid this problem is to not actually create test methods until you’re ready to implement them.

I know I like to sometimes lay out a list of tests I want to implement before actually writing them. If you want to do this, then I suggest you create the methods, but ensure that the test is ignored (in nUnit, put an [Ignore] attribute onto the method), or that it fails (in nUnit, Assert.Fail). This is especially important if you’re going to commit to your source control system your partially complete tests.

Doing it this way will ensure that

  1. You don’t forget that you need to implement the test.
  2. That someone doesn’t think that something is being tested when in actual fact it isn’t.

Using Yahoo Pipes to aggregate feeds

pipe I’ve got a list of links in the sidebar of this blog. What is not obvious is that this list is an aggregated list of links that come from multiple sources. At the moment, I mark links as interesting from two sources:

Ideally, I don’t want to have 2 lists on the side, especially when most items I both bookmark in Delicious and share in Google Reader.

Enter Yahoo Pipes. Its an incredibly easy-to-use and powerful tool. You can see the pipe I’ve created here: If you click the “View” Source” link you can see how its put together.

The general idea is to:

  1. add multiple sources (fetch feed)
  2. union them together into one feed
  3. Ensure they are unique, based on the id
  4. Sort them by the publish date
  5. Output the result

The end result is one rss feed that contains the unique list of links and is sorted properly. I just then point the WordPress RSS widget at that feed, and it works.

You can do a lot more fancy things, but what I’ve described above is the simplest.