0

I need to determine the number of rows of a temp table in SQL Server. The obvious way to do it is

select count(*) from #table

The problem is that if the #table is huge, count(*) takes too long.

I am aware that one can find the number of rows of a table this way:

select so.name,
case when si.indid between 1 and 254
    then si.[name] else NULL end AS [Index Name],
si.indid,
rows as number_of_rows
from sys.sysindexes si
inner join sys.sysobjects so on si.id = so.id
and si.indid < 2
and so.type = 'U'
and so.name <> 'dtproperties'
where so.name = 'table_name'

Is there any similar query that works for a temp table?

Another way I was thinking of:

Select Top 1 rn From

(Select *, ROW_NUMBER() Over (Order by (Select NULL) ) As rn From geschaeft.SPOTGeschaeftstammMonat ) As a

Order by rn Desc

Any ideas?

Boris
  • 79
  • 5
  • 1
    SQL Server v18.9.1 doesn't exist. The latest version release is 15.0.4261.1, which was released a few days ago. What does `PR IN NT @@VERSION` return? – Thom A Sep 30 '22 at 18:34
  • 1
    _"The problem is that if the #table is huge, count(*) takes too long."_ - that just means your `#temporary` table doesn't have a `PRIMARY KEY`. (Also, is there a (good) reason you're using 1990s-style temporary-tables instead of sleek, modern (and I dare say _sexy_) table-variables? – Dai Sep 30 '22 at 18:36
  • 2
    Table variables aren't a replacement for temporary tables, @Dai ; there's a lot of reasons to continue to use temporary tables. On older (but not that old) version of SQL Server the optimiser assumed that a table variable only contained 1 row; which could cause some terrible plans. – Thom A Sep 30 '22 at 18:40
  • @Larnu They aren't a complete replacement, no - but TVPs/TVVs have supplanted temporary-tables for passing tabular data into procedures and functions (but not _out_ of a procedure...) as well as being far tidier than temporary-tables in ad-hoc statement batches. (Personally I avoid temporary-tables as much as possible because of their awkward lifetime and sharing semantics). – Dai Sep 30 '22 at 18:44
  • 2
    Just my 2 cents, but I've found that when dealing with very large data sets, temp tables perform better. I rewrote a procedure recently that was using table variables to use temp tables and it shaved a couple mins off the run time. But for small data? Absolutely, table variables are the way to go. – jw11432 Sep 30 '22 at 19:49

1 Answers1

2

Get the row count from sys.partitions rather than the deprecated sys.sysindexes and sys.sysobjects views. Use the OBJECT_ID in the query rather than the object name;

SELECT SUM(rows) AS rows
FROM tempdb.sys.partitions 
WHERE
    object_id = OBJECT_ID(N'tempdb..#temp') 
    AND index_id IN(0,1);
Dan Guzman
  • 43,250
  • 3
  • 46
  • 71
  • I get NULL after executing the code with my temptable. – Boris Sep 30 '22 at 19:14
  • Probably you would need to use `tempdb.sys.partitions` and `OBJECT_ID(N'tempdb..#temp')` as unlikely you are in the database context of `tempdb` - example https://dbfiddle.uk/KSXJyytg – Martin Smith Sep 30 '22 at 19:15
  • @Boris, sorry, I assumed you were running this in the context of tempdb. I added the qualified names to my answer. – Dan Guzman Sep 30 '22 at 20:32