А вот гарантировать, что получишь результат именно последнего ЯВНОГО insert'а, не всегда возможно. Например, если у тебя сработают триггера, которые делают вставки в таблицы, содержащие identity поля, то в @@identity получишь результат последней операции, а не своей явной.

Это смотря в каком месте триггера @@identity запросить
Вот пример:

create table t1(
col1 int identity(1, 1),
col2 sysname
)

create table t2(
col1 int identity(1, 1),
col2 sysname
)

create trigger ins_t1 on t1
for insert
as
print @@identity
insert into t2 values (123)
print @@identity

insert into t2 values (456)
insert into t2 values (456)

insert into t1 values (123)

В распечатке мы увидим
1
3

Так что в начале триггера, наверное, можно запрашивать, а вот в конце не стОит. Ну и ясное дело, после отработки последнего инсерта @@identity уже покореженное, так что писать
insert into t1 values (123)
select @@identity
нельзя в принципе.

Ну и чем тебе поможет set nocount on ? В моем примере (см. выше) добавление set nocount on ничего не изменило в поведении @@identity.

insert into t1 values (111)
select @@identity

по-прежнему будет возвращать @@identity, не соответствующую id записи, вставленной в t1.