Re: Again about rows count

On Fri, 20 Sep 2013 14:59:15 +0300
Velichko Yuriy <[email protected]> wrote:

> I need to figure out the number of records returned from SELECT query,
> before start fetching data [MSSQL Server].

>From the perspective of SQL Server, that's a little like asking for
tomorrow's winning lottery ticket today.  

The server returns the rows as they are produced.  Often the client
gets the first row before the server has even *created* the last one.
The only time delivery of the first row is deferred until production of
the final one is in the presence of ORDER BY.  And sometimes not even
then, if the join algorithm happens to produce the rows in the order
required by the ORDER BY clause.  

The "need" to know how many rows will be returned can be illusory.  The
programmer naturally wants to know in advance how much to allocate in
the receiving receptical (GUI grid, C++ collection, whatever).  The
answer is usually to start by taking just some, and getting more as
needed, or to use a growable collection, or to write the results to a
file temporarily, then allocate and read them back in.  

But what if you want to display the "page count" or some such ("showing
rows 11-20 of 453")?  This is an ancient need, not only of database
programming, and *requires* two passes. Consider for instance the
problem of putting a "page n of N" footer on any document.  troff
cannot do that without arcane trickery even today, because it's a
single-pass system!  

One solution, which is fast and sometimes good enough, is to first
SELECT COUNT(*), and then select the rows.  That goes wrong though if a
referenced table is updated between the two selects.  

Another solution is SELECT ... INTO #T.  That immediately returns the
rowcount while isolating your results from subsequent updates.  But
some libraries introduce pooling and other medation that interfere with
the use of temporary tables.  

--jkl