April 1, 2009 at 2:41 am
Hi,
If you need to return the total table's row count, you can use alternative way instead of SELECT COUNT(*) statement.i.e.
SELECT rows FROM sysindexes WHERE id = OBJECT_ID('table_name') AND indid < 2
What If we are to get total record count by joining more than two table?
Shall we use count ?
Is there any other method,which improve the method of query ?
[font="Verdana"]Regards
Kumar Harsh[/font]
April 1, 2009 at 5:52 am
Generally, I use the COUNT(*). It's pretty much guaranteed to be accurate. Gail Shaw showed how it will pick an index with the least number of pages to satisfy the count in the quickest way. It works well. Same thing if I were joining multiple tables except that, in practice, I'm less likely to use COUNT(*) in that sitatuation because I'm probably going for a count of some particular value, but it depends.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
April 1, 2009 at 7:48 am
I'm using the sys.indexes as overview for all tables. In operative processes it seems to be a quiet rare requirement to get a complete count without any criteria.
Greets
Flo
April 1, 2009 at 7:56 am
Grant Fritchey (4/1/2009)
Gail Shaw showed how it will pick an index with the least number of pages to satisfy the count in the quickest way.
Can you provide a link? Could be a good read.
Thanks.
---------------------------------------------
[font="Verdana"]Nothing is impossible.
It is just a matter of time and money.[/font]
April 1, 2009 at 8:00 am
Hi
When you say "What If we are to get total record count by joining more than two table?"
Based on the table join criteria you want to count the No.Of Records? In that case then i would see Count(*) is the right option. You can even use Column Name in palce of * but it depends on the requirement.
Thanks -- Vijaya Kadiyala
April 1, 2009 at 8:03 am
I don't see it on her blog[/url]. It was in a presentation she gave at the PASS Summit last November.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
April 1, 2009 at 9:08 am
pandeharsh (4/1/2009)
SELECT rows FROM sysindexes WHERE id = OBJECT_ID('table_name') AND indid < 2
That's not guaranteed accurate. Also, sysindexes is a deprecated system view and will be removed in future versions. In 2005 and 2008 you can query sys.partitions for the row info, but again it's not guaranteed to be 100% accurate.
If you need just a ballpark figure, sure, use sys.partitions. If you need to know the number of rows in the table, use COUNT(*)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 1, 2009 at 9:10 am
Vijaya Kadiyala (4/1/2009)
You can even use Column Name in palce of * but it depends on the requirement.
You can, but it'll very likely be slower than count(*). Also COUNT(Column) and COUNT(*) do not mean the same thing and hence COUNT(column) cannot be said to be a general replacement for COUNT(*)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 1, 2009 at 11:32 am
HI Gail
I agree with your statement on this. Count(*) and Count(ColumnName) are not the same :):-)
Thanks -- Vijaya Kadiyala
April 2, 2009 at 12:27 pm
select count(*)
from table_name
Thats a simple way of getting an total amount of rows in your table. If you select count(columnname)
you might not get back accurate results since their might be some null values in the table.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply