Preferrably when you tend to ask someone - "How to find total number of records in a table?"; then you will receive a very generic answer -
"Simply try select count(*) from TableName and you will get the total number of records.. Simple isn't it?".
However, sometimes when the table sizes we are dealing with at the moment are very large - like in the databases of large financial firms who tend to have large transactional tables to store the data related to each transaction then in such cases try not to use the count(*) method as you might end up getting your session in hung state with no output for long.
The reason why you might end up in such a state is that count(*) tries to check the whole table to find the exact number of records in the table which makes it undergo a table scan and hence takes lot of time, while if what you really care about is the approx number of records in the table then try the following system stored procedure -
sp_spaceused <<table name>>
The system stored procedure sp_spaceused checks the OAM (Object Allocation Map) entry in the tables data page and computes the total number of records stored in the data table.
Simple isn't it?
"Simply try select count(*) from TableName and you will get the total number of records.. Simple isn't it?".
However, sometimes when the table sizes we are dealing with at the moment are very large - like in the databases of large financial firms who tend to have large transactional tables to store the data related to each transaction then in such cases try not to use the count(*) method as you might end up getting your session in hung state with no output for long.
The reason why you might end up in such a state is that count(*) tries to check the whole table to find the exact number of records in the table which makes it undergo a table scan and hence takes lot of time, while if what you really care about is the approx number of records in the table then try the following system stored procedure -
sp_spaceused <<table name>>
The system stored procedure sp_spaceused checks the OAM (Object Allocation Map) entry in the tables data page and computes the total number of records stored in the data table.
Simple isn't it?
No comments:
Post a Comment