Count number or rows in a table

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Count number or rows in a table

Author

Topic

Bex
Aged Yak Warrior

580 Posts

Posted - 2006-03-28 : 02:56:03

I have an interview in a couple of days so am swatting up on interview questions. So far, I am fairly confident in answering most of them, but there was one that has me a bit stumped:

Name three ways you can get an accurate count of teh number of records in a table using query analyzer??

I know the function count, but thats abotu it. Any other ideas?

Also, if anyone can give me some interview hints, or things to revise, I would be grateful. I have printed off a load of interview questions that I have located on the net, but any others would help.

Thanks in advance

Hearty head pats

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2006-03-28 : 03:02:54

All the best with your interview..

Hope this helps

http://www.techinterviews.com/index.php?cat=12

If Debugging is the process of removing Bugs then i Guess programming should be process of Adding them.

mr_mist
Grunnio

1870 Posts

Posted - 2006-03-28 : 03:11:42

If you want an accurate count you might be rather limited. You could select all the rows, that would give you a count. Or you could use sp_spaceused with the updateusage option, or run a DBCC UPDATEUSAGE and query sysindexes directly.

-------
Moo. :)

Bex
Aged Yak Warrior

580 Posts

Posted - 2006-03-28 : 03:58:57

Thanks for the adivce and thanks for the encouragement. I have my fingers, legs, arms, and eyes all crossed!

Hearty head pats

nr
SQLTeam MVY

12543 Posts

Posted - 2006-03-28 : 05:58:52

pick a nullable column.
Remove any triggers from the table.

update tbl set col = null
select @@rowcount

Change null to other values for the other two options

That's an answer that probably suits the question.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Bex
Aged Yak Warrior

580 Posts

Posted - 2006-03-28 : 10:45:25

Thanks nr

Don't suppose you can come up with a third option? As so far, thats two (count and @@rowcount). i truely cannot think of another way to do it?

Hearty head pats

nr
SQLTeam MVY

12543 Posts

Posted - 2006-03-28 : 10:51:54

bcp the table to a text file and see what the rowcount is?

(I wasn't serious about that or this method by the way).

Suggestion from a colleage - add an identity column and get the max of it.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

nr
SQLTeam MVY

12543 Posts

Posted - 2006-03-28 : 11:01:18

alter table add a char(1) not null default ' '
will give you the number of rows too.

declare @i int
select @i = 0
select @i = @i + 1 frmo tbl
select @i

Best suggestion.
A cursor to loop through each row incrementing a count.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Bex
Aged Yak Warrior

580 Posts

Posted - 2006-03-28 : 11:02:37

Cheers muchly! I did think of the identity column, but you can only have one per table, therefore, will not work if one already exists. But then again, the question didn't specify whether the method should be applicable to all tables!

Anyway, home time soon! I have lots of exciting revision to do! Oh the thrill........ Have a good evening! :)

Hearty head pats

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2006-03-28 : 11:49:03

quote:
Originally posted by nr

Best suggestion.
A cursor to loop through each row incrementing a count.
==========================================
Cursors are useful if you don't know sql.

Srinika

bartender
Starting Member

1 Post

Posted - 2006-03-28 : 12:37:06

Actually, you can just write

select * from YourTable

and having "Results in Grid" option you'll get the list of all records in grid with conter on the right

mr_mist
Grunnio

1870 Posts

Posted - 2006-03-29 : 02:37:17

I think the point here is that the question is a bit stupid, hence there's more than one stupid answer for it.

-------
Moo. :)

ranganath
Posting Yak Master

209 Posts

Posted - 2007-07-03 : 02:21:31

Select Sum(1) From Table

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources