Get number of rows without using count() function

Efficient way to count the number of rows in a table.
How to get table row count without using count function?
How to get index and data size of a table?

You can use sp_spaceused to display total number of rows in a table, index size and data size.

sp_spaceused tableName
go

If you want, to display information for each of the indexes available on this table

sp_spaceused tableName , 1
go



If there is any text/image field in this table, then above will also display size reserved and unused by this column.

You can use this,
when you think table is too big to even run count(*)
when you want to check index size
when you want see space utilized by a text/image column
or just take it as an database interview question :)

Related Posts:
select count(*) vs. select count(1)