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. This blog post is about finding a table row count without using T-SQL.
I had asked following question to one of the candidates and he was not able to answer this question. I promised him that I will put answers to this question on the blog.
The question was: How to find table row count without using T-SQL and without opening tables?
Answer: Well, it is quite simple by using SQL Server Management Studio.
Right click on table and click on Properties. Now on the left nav of opened window, click on storage and under general section there is a display of Row Count.
Here is one question for you – how will you get the total number of the rows with T-SQL. If you know the answer, just post it over here. I have already provided hint in this blog post so it should be easy for you. Let me know what you think of this blog post.
If you have similar interesting questions, please share with me. I will post on the blog.
Reference: Pinal Dave (https://blog.sqlauthority.com)
19 Comments. Leave new
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…
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.
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?
I believe this option is only available in SQL 2008 and only in 2008 compatibility mode.
Thanks for the Info…:D
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)
You can use the powershell version against sql 2005 or sql 2008 servers (I havent tried it with sql 2000 but it should work)
You can even use it without a full sql 2008 install by using the powershell components from
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
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.
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.
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
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)
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
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.
Which version of SQL Server are you using? If you use versions prior to 2005, you need to update the statistics using updateusage command
how to get row number from a table please reply
select row_number() over (order by col) as rownum , * from talbe
Get the rows by executing sp_spaceused