@@RowCount after Truncate

@@Rowcount with Truncate

Aug 10 2012 12:00AM by Jeetendra   

---

@@ROWCOUNT returns the number of affected rows by last statement. Below example illustrate the same

1.CREATE TABLE #t (id int)

2.INSERT INTO #t(id) values(1)

3.DELETE   #t

4.SELECT @@ROWCOUNT

Here we will get @@ROWCOUNT AS 1
But same did not work for truncate

1.CREATE TABLE #t (id int)

2.INSERT INTO #t(id) values(1)

3.TRUNCATE table   #t

4.SELECT @@ROWCOUNT

Here we will get @@ROWCOUNT AS 0
It is because truncate removes page allocation not as individual row, so it will not return row count value.
one important point is, if number of affected row is more then 2 billion, we need to use rowcount_big.