Prev: LEFT JOIN and unnecessary reads
Next: Audit trigger
From: --CELKO-- on 8 Apr 2010 13:08
In one case, you return a scalar flag and in the second case you
return a table with an unknown number of rows and columns. A SELECT
statement cannot change the structure of a table on the fly.

You can get @@ROWCOUNT and use that in some procedural code.
Hopefully, you were not tying to use SQL to get a Boolean flag for
some programming langue that uses -1 for a flag.

From: simon on 16 Apr 2010 04:39
On 8 apr., 19:08, --CELKO-- <jcelko...(a)earthlink.net> wrote:
> In one case, you return a scalar flag and in the second case you
> return a table with an unknown number of rows and columns. A SELECT
> statement cannot change the structure of a table on the fly.
>
> You can get @@ROWCOUNT and use that in some procedural code.
> Hopefully, you were not tying to use SQL to get a Boolean flag for
> some programming langue that uses -1 for a flag.

Hi,

all I'm trying to do is that if there is more than 100(or some other
number) records,
I return user message that it must use filter. If there is less
records, I return those records.

So, I need to execute the same select twice, once for count and than
once more to return records if count is lower than some value.

This idea would be great, but unfurtunatelly COUNT(*) OVER() works
very slow, even 10 times slower than if I put top 5000 records into
temp table and execute count there.

;WITH CTE AS (SELECT <columns>, COUNT(*) OVER() AS cnt FROM table)
SELECT <columns> FROM CTE WHERE cnt <= 100;


IF @@ROWCOUNT = 0 RETURN -1;

For now the fastest way is to select top 5001 record into temp
table(or table variable) and than check:

SELECT * INTO #tmpTbl FROM ......WHERE ....

IF (SELECT count(*) FROM #tmpTbl)=5001--(OR if @@rowcount=5001)
RETURN -1 (user must use filter to shorten recordset)
ELSE
SELECT * from #tmpTbl (user get recordset)

What I was trying to do is to avoid temp table (use CTE instead) but
every other way is much slower.
(otherwise I like Plamen idea)
Any comment?

Thank you,
Simon




From: Plamen Ratchev on 16 Apr 2010 09:38
There is no need to use temp table. You can simply use SELECT TOP 101 without ORDER BY and then check @@ROWCOUNT if 101
then you can return the user message, otherwise run the query.

--
Plamen Ratchev
http://www.SQLStudio.com
From: simon on 16 Apr 2010 10:49
On 16 apr., 15:38, Plamen Ratchev <Pla...(a)SQLStudio.com> wrote:
> There is no need to use temp table. You can simply use SELECT TOP 101 without ORDER BY and then check @@ROWCOUNT if 101
> then you can return the user message, otherwise run the query.
>
> --
> Plamen Ratchevhttp://www.SQLStudio.com

Plamen,

I don't use ORDER BY at all. All I want is to execute complicated
query on table with milion records only once. That is possible only if
I use temp table or your idea with COUNT(*) OVER() .
COUNT over() on large table is very slow so temp table is the only
solution.

SELECT top 5001 (or 101, doesn't matter) * INTO #tmp from 1MilionTable
WHERE (user filters)

IF @@ROWCOUNT<101 --user filters are ok and I return recordset (but
not all records, just the current page on grid)
SELECT * from #tmp WHERE rowID BETWEEN 10 AND 20 --I return records
for just one page
ELSE
SELECT -1 --user must use more filters or there is no record

I think this is very common example, grid with paging.
I don't know what did you mean?

Thanks,
Simon
From: Plamen Ratchev on 16 Apr 2010 12:35
If you execute your query with your limit (page size) + 1, you need to execute the CTE query only once, no need for temp
table. On the next line after the query you can have the @@ROWCOUNT check and if limit + 1 rows are returned then you
send message to the user, if less then you show the result set. You can return the @@ROWCOUNT to the client as output
parameter and based on limit + 1 check take the appropriate action.

--
Plamen Ratchev
http://www.SQLStudio.com
First  |  Prev  |  Next  |  Last
Pages: 1 2 3
Prev: LEFT JOIN and unnecessary reads
Next: Audit trigger