Post reply

Count number of records in a table

  • December 4, 2002 at 5:33 pm

    #443223

    That is true, and again you are correct. I just felt that it was worth mentioning. Its an indicator of whether the rows is accurate or not. A proc which checks for a 0 value there before doing an actual count of the rows can save a major amount of processing time and in my experience has been trustworthy. With as impressed with your overall knowledge base that I've seen on the posts, I think I'd be more likely to believe you forgot it.

    Oh, and not updates. Only inserts or deletes affect it.

    Edited by - scorpion_66 on 12/04/2002 5:39:03 PM

  • December 4, 2002 at 6:29 pm

    #443225

    Cool something else I can add to my personal knowledge. Thanks. ANd I love the idea of check for value of 0 to verify count is accurate. I need to move that concept into some of the extra work I have going. Will also be help since I manually update my stats to get a better picture of when I need to run that.

  • December 5, 2002 at 6:23 am

    #443260

    Try using exec sp_spaceused 'tbl_TableName'

    This returns how many rows and how much space the table takes up and the indices too.

  • December 5, 2002 at 9:21 am

    #443293

    Do you realize that sp_spaceused gets the rows from sysindexes?

    If someone needed the sizes and such, I could see them using it, but it's a lot of unneccessary overhead if the size is not wanted.

  • December 6, 2002 at 3:58 am

    #443430

    In answer to the original question: -

    'Also, in EM, when I right-click properties a table, it tells me the number of records in the table. Does anyone know how this number is generated?'

    EM uses the stored procedure sp_MStablespace in the master db to get this value. This returns the three values Rows, DataSpaceUsed and IndexSpaceUsed

    and internally uses the command: -

    SELECT convert(int, rowcnt) FROM dbo.sysindexes WHERE indid < 2 and id = @id to retrive the number of rows.

    Interestingly, I tried looking in BOL for 'sp_MStablespace' and got 'No topics found'.

    Regards,

    Andy Jones

    .

  • December 6, 2002 at 5:50 am

    #443446

    Almost all of the sp_MS-procs are undocumented (all but one actually, of almost 500!). Many of them are not really a part of SQL Server per se, but rather apart of Enterprise Manager, just as you would create procedures for your apps. In order to have a look at them, just run sp_helptext sp_MStablespace in master, they're usually pretty well commented.

    --

    Chris Hedgate @ Apptus Technologies (http://www.apptus.se)

    http://www.sql.nu

    --
    Chris Hedgate http://www.hedgate.net/
    Contributor to the Best of SQL Server Central volumes
    Articles: http://www.sqlservercentral.com/columnists/chedgate/

  • December 11, 2002 at 10:21 pm

    #444206

    Try this undocumented sp.

    set nocount on

    create table #sp_mstablespace (Rows INT, DataSpaceUsed INT, IndexSpaceUsed INT )

    Insert into #sp_mstablespace

    Exec sp_mstablespace <YourTableName>

    select * from #sp_mstablespace

    paul


    paul

  • December 12, 2002 at 2:52 pm

    #444293

    Just another comment about sp_spaceused...Even after an "update statistics" the rowcount can still be reported incorrectly in sp_spaceused. You must run a "DBCC UPDATEUSAGE ... WITH COUNT_ROWS" command to correct the number in sysindexes.

    I'm not sure if using the "WITH FULLSCAN" option on "update statistics" will ensure the rowcount is reported correctly?

    -Dan


    -Dan

  • December 12, 2002 at 3:19 pm

    #444297

    With Fullscan does not affect rowcounts.

Viewing 9 posts - 16 through 23 (of 23 total)

You must be logged in to reply to this topic. Login to reply