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.
| Author |
Topic |
|
Sun Foster
Aged Yak Warrior
515 Posts |
 Posted - 2010-08-11 : 10:27:05
|
| Which one is fast to get count of records?1)select count(*) from order2)select count(OrderID) from order (OrderID is primary key)I tested in 3894000 records table but both them are done in 0 second |
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2010-08-11 : 11:18:20
|
The difference isn't the performance.count(OrderId) isn't counting NULL values and count(*) is counting all records.Exceute this and see the difference:declare @order table (OrderId int)insert @orderselectnullunionselect 1union select 2selectcount(*) as count_asterix,count(OrderId) as count_OrderIdfrom @order
No, you're never too old to Yak'n'Roll if you're too young to die. |
 |
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2010-08-11 : 11:49:12
|
| SELECT Rows FROM sys.partitions WHERE object_id = object_id('YourTable') |
|
|
|
Bustaz Kool
Master Smack Fu Yak Hacker
1834 Posts |
Posted - 2010-08-11 : 13:42:00
|
| I didn't think that this was guaranteed to be accurate:SELECT Rows FROM sys.partitions WHERE object_id = object_id('YourTable')=======================================A couple of months in the laboratory can save a couple of hours in the library. -Frank H. Westheimer, chemistry professor (1912-2007) |
|
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2010-08-11 : 15:08:51
|
| I wouldn't use it in a decision making routine, but if I need to know how many records there are, that's what I use. There is *some* latency, but in my experience it has always been milliseconds.It will also return more than one record if there are indexes -- or if the table is partitioned. |
|
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2010-08-12 : 02:11:44
|
quote:
Originally posted by russell
I wouldn't use it in a decision making routine, but if I need to know how many records there are, that's what I use. There is *some* latency, but in my experience it has always been milliseconds.It will also return more than one record if there are indexes -- or if the table is partitioned.
I don't see what these answers have to do with a question about count(*) or count(ColumnName)?
No, you're never too old to Yak'n'Roll if you're too young to die. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-08-12 : 02:44:14
|
| "I don't see what these answers have to do with a question about count(*) or count(ColumnName)?"I think "Which one is fast to get count of records?" might mean "what's the fastest way" rather than "which of these two is fastest" ... |
|
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2010-08-12 : 09:13:33
|
| SELECT Rows FROM sys.partitions WHERE object_id = object_id('YourTable') is fastest. Doesn't have to scan the table.SET STATISTICS IO ON and try it |
|
|
|
|
|
|
select count(*) or select count(OrderID)