I don't have my SQL Server books with me, but you probably want to look at the @@IDENTITY value. You can look on Google for more info about this value, but it would go something like this:

my $insert = $dbi->prepare('insert into table values( ?, ?, ?)');
my $ident_sth = $dbi->prepare('select @@identity');

$insert->execute($val1, $val2, $val3);
$ident_sth->execute();
my $identity = $ident_sth->fetchrow_array();
[download]

Update:This is faster than querying the table itself to see what the id was. Instead of an index scan, this is a variable lookup.