SQL SERVER – Find Table Rowcount Without Using T-SQL and Without Opening Table

Recently I have been busy with interviewing many candidates for my organization. We are looking for some smart and experienced developers for some senior positions. I have wrote this previously SQL SERVER – Interesting Interview Questions.

I had asked following question to one of the candidate and he was not able to answer this question. I promised him that I will put answer to this question on blog.

The question was : How to find table rowcount without using T-SQL and without opening table?

Answer : Well it is quite simple by using SQL Server Management Studio.

Right click on table and click on Properties. Now on left nav of opened window click on storage and under general section there is display of Row Count.

If you have similar interesting questions, please share with me. I will post on blog.

Reference : Pinal Dave (http://blog.SQLAuthority.com)

19 thoughts on “SQL SERVER – Find Table Rowcount Without Using T-SQL and Without Opening Table

  1. Hmmm … I’d argue that “SELECT COUNT(*) FROM TABLE” isn’t actually T-SQL, and therefore is a valid answer … I’d consider that ANSI SQL. Nothing in that statement ties it to Transaction-SQL specifically.

    That aside, as a developer I would have felt compelled to point out a couple of lines of .NET code that could have accessed the DataTable.Rows.Count object as well… either that, or go through SMO. Whether you’d class that as “opening” the table, compared to whatever Management Studio is doing in the background in order to get that RowCount …

    Easy to get caught up in the semantics, methinks :-D

    Pinal, question for you: *is* Management Studio “opening” the table in the background to get this figure on-demand? Or is the figure a running count that’s stored separately, and updated live whenever the table is updated?

    If it’s on-demand, I’d argue that Management Studio is still “opening” the table, in order to get that figure…

    Like

  2. Hello,

    Few cents from my side…

    –This is fastest amoung all, output includes some good information including row count information.
    select sp_spaceused [table_name]

    — If you just want rowcount for all tables/single table in a database,
    — for all tables
    Select A.name ‘TableName’ , B.rowcnt ‘RowCount’ from sysobjects A, sysindexes B where A.id =b.id and A.type = ‘u’ and indid <2

    — for single table
    Select A.name ‘TableName’ , B.rowcnt ‘RowCount’ from sysobjects A, sysindexes B where A.id =b.id and indid <2 and A.name = table_name

    — The poorest among all. If we have a exclusive lock on that table, this query will be running for ever.
    select count(*) from table_name

    Thanks,
    IM.

    Like

  3. There is another way that doesnt involve management studio or t-sql.
    You can use the powershell provider for sql

    (get-item SQLSERVER:\sql\dbserver\default\Databases\user_database\Tables\dbo.user_table).rowcount

    gets the rowcount for table called user_table in database user_database on server dbserver

    I know this was an interview question to find out what people know, but when would you ever need to find out the rowcount of a table without using t-sql?

    Like

  4. One day we’ll realize we cannot survive without SSMS :)

    2 ways I think of

    1) sysindexes (may not be accurate)
    SELECT
    OBJECT_NAME(sysindexes.id) TableName
    ,ROWS
    FROM sysindexes

    2) In 2008 SSMS, add in the column “Row Count” for the Tables

    but to get most accurate count, I’d still use
    SELECT COUNT(1) FROM Table (nolock)

    Like

  5. Pinal–

    If it were me, and I were interviewing prospective DBA’s, I would prefer they know more about querying the data than using the GUI. A better question would have been can you tell me which table (if SQL 2000) or which DMV (SQL 2005 or 2008) contains the row count displayed in the GUI.

    Anybody can learn the GUI, but a real DBA knows how to get along without it.

    Just my thoughts.

    HGH

    Like

  6. You can also use the standard reports (In 2008 SSMS, right click database – Reports – Standard Reports – Disk Usage by Table). The second column give the # Records for each table in the database.

    Like

  7. Pinal, the answer you suggest for your interview question is not correct just because any other “DB Administration” frontend could do the same (correct answer doesn’t restrict to MS SQL…..Studio) and because you didn’t specified a specific platform or software vendor. The question is wornly constructed, and multiple correct answers can fit…..including some answers that you’d mark as wrong.

    To get the correct answer you have to make the correct question.

    Like

  8. Pingback: SQL SERVER - Find Number of Rows and Disk Space Reserved - Using sp_spaceused Interesting Observation Journey to SQL Authority with Pinal Dave

  9. Hi,

    Pinal, how to know the date time, the record inserted into table or record modified.
    Table doesn’t have column – datetime.

    please any one can help me

    Thanks
    Kiran

    Like

  10. Hello,

    Is it possible to get the row count from many (1 to 4) tables
    including a condition.
    Like For Example I want the number of active employees , active departments (employee count from employeemater table and department count from departmentmaster table)

    This has to be done using simple query. (not more coding required)

    Like

  11. Hi all,

    Please do not ask questions like this or post replies like these. If you know, then you reply. It should be authoritive, correct, reliable and functioning.

    Otherwise, what is the meaning of posting each persons views / irritation etc.

    This is not a place for fights. So, please reply only if you understand and know the things.

    Ok, now, can anyone tell me how to extract multiple column values from a single table satisfying a particular condition ? Please help me earliest. My customer is having problem (errors) in the software I gave him.

    K Singh

    Like

  12. Hmm. I just came across an instance where the storage properties rowcount shows 5,991 rows and a query returned a result set that suggested there were actually 5,993 rows.

    So, I ran a query returning just the primary keyset (2 fields make up the primary key) for each row, and it also returns 5,993 rows.

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s