rja.carnegie (9/3/2009)

---

You've got a point there. I think it's most likely that you'd want total row count only from a work-in-progress or temporary table that your own process just...

Jeff - great stuff, as usual. (Kevin, eh? Hmmm...)

Let me add my congratulations to the author: this tends to be an emotional topic, so well done on presenting...

You mean...

Jeff Moden (9/2/2009)

---

In order for the rowcounts found in sysIndexes in SQL Server 2000 to be anywhere close to accurate, you need to use DBCC UPDATEUSAGE on the table you're...

rja.carnegie (9/3/2009)

---

I think I heard it as an old Oracle trick - that the equivalent of query optimiser was dumb enough to interpret EXISTS (SELECT *) and/or COUNT(*) as demanding...

What sort of clustering is it you use?

I use PolyServe Matrix these days (shared-everything) so we use a combination of mirroring, log shipping, and replication - for various reasons, in...

I agree it seems like an odd requirement, but that said:

One way to do this would be to dump the IDENTITY column and use a Sequence Table instead. This...

Hey tvanharp,

Like many of the good folks on here, I'm willing to put some effort into this to help you toward a definitive answer.

It would be most helpful if you...

Mathew Michuta (9/2/2009)

---

Note the (nolock) on the tables. If you are importing large amounts of data, this query will hang if the table is locked for loading. I've seen this...

A compact way to get a usually-accurate row count for a table is:

SELECT row_count = SUM(row_count)

FROM sys.dm_db_partition_stats DPS

WHERE DPS.[object_id] = OBJECT_ID(N'dbo.TableName', N'U')

AND ...

wbrianwhite (9/2/2009)

---

I had always heard that you should do a "select count(1) from table".

http://www.techonthenet.com/sql/count.php

"TIP: Performance Tuning

Since the COUNT function will return the same results regardless of what NOT NULL field(s)...

sqlservercentral (9/2/2009)

---

I find it baffling that the query planner can't provide a way to do count(*) quickly in the common case. It does so many other clever things; this...

Thanks for that. Other things of interest include:

* Are you able to schedule some database off-line time for this work?

* If so, how long?

* What would...

As am I. I think we've covered this ground already, and determined that the counter-intuitive behaviour is limited to NOT IN...

TheSQLGuru (8/31/2009)

---

NOTE: if your IO subsystem sucks (as most I have encountered do), using 8 files for tempdb can actually result in LESS IO throughput than 1 or 2 or...