Count function

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a table which has a million records. Each night it get trashed and
re-built.

I want to run a select statment and pass the results into a variable to let
me know if it contains more than 0 records.

I don't want to do this because it takes to long to perform the count.

declare @counttable int
select @counttable = count(*) from [tablename]

Is there a quicker way to do what I want to achieve
 
Try this

SELECT 'Primary Key' AS IndexName, [rows]
FROM sysindexes
WHERE (id = OBJECT_ID('<Table_name>')) AND (status & 2048 <> 0)

This will give the rowcount based on the primary key.
 
Is there no simple way to check for the exist of rows in a table?

AMDRIT said:
Try this

SELECT 'Primary Key' AS IndexName, [rows]
FROM sysindexes
WHERE (id = OBJECT_ID('<Table_name>')) AND (status & 2048 <> 0)

This will give the rowcount based on the primary key.

Stephen said:
I have a table which has a million records. Each night it get trashed and
re-built.

I want to run a select statment and pass the results into a variable to
let
me know if it contains more than 0 records.

I don't want to do this because it takes to long to perform the count.

declare @counttable int
select @counttable = count(*) from [tablename]

Is there a quicker way to do what I want to achieve
Click to expand...
Click to expand...
 
I think that is the simplest way. That query should be very fast. As you
insert into a table, indexes are recomputed and row count is cached for each
row. Since cluster indexes and pk indexes represent all the data, you will
know the number is good. The query I gave should be the fastest way to
determine if there are 1 or more rows.


Stephen said:
Is there no simple way to check for the exist of rows in a table?

AMDRIT said:
Try this

SELECT 'Primary Key' AS IndexName, [rows]
FROM sysindexes
WHERE (id = OBJECT_ID('<Table_name>')) AND (status & 2048 <> 0)

This will give the rowcount based on the primary key.

Stephen said:
I have a table which has a million records. Each night it get trashed
and
re-built.

I want to run a select statment and pass the results into a variable to
let
me know if it contains more than 0 records.

I don't want to do this because it takes to long to perform the count.

declare @counttable int
select @counttable = count(*) from [tablename]

Is there a quicker way to do what I want to achieve
Click to expand...
Click to expand...
Click to expand...
 
Since you only care if there are greater than 0 records, you can just
select the top record:

Select Top 1 * From [tablename]

That should be fairly quick. And if you get a record, you know there
is more than 0. As for getting the actual count, I think AMDRIT's
solution is the one. I don't know how much more simple you want it
than that!
 
This sort of approach assumes that the table in question actually has a
primary key and also that statistics are being updated automatically.

The OP say's that the table in question is 'trashed and re-built'. This
indicates (to me at least) that all the existing rows in the table are being
deleted in some way or other and that new rows are being inserted.

The inserts generate information as the number of rows affected. If any of
the inserts report that 1 or more rows were affected then the table in
question contains more than 0 rows.


AMDRIT said:
I think that is the simplest way. That query should be very fast. As you
insert into a table, indexes are recomputed and row count is cached for
each row. Since cluster indexes and pk indexes represent all the data, you
will know the number is good. The query I gave should be the fastest way
to determine if there are 1 or more rows.


Stephen said:
Is there no simple way to check for the exist of rows in a table?

AMDRIT said:
Try this

SELECT 'Primary Key' AS IndexName, [rows]
FROM sysindexes
WHERE (id = OBJECT_ID('<Table_name>')) AND (status & 2048 <> 0)

This will give the rowcount based on the primary key.

I have a table which has a million records. Each night it get trashed
and
re-built.

I want to run a select statment and pass the results into a variable
to
let
me know if it contains more than 0 records.

I don't want to do this because it takes to long to perform the count.

declare @counttable int
select @counttable = count(*) from [tablename]

Is there a quicker way to do what I want to achieve
Click to expand...
Click to expand...
Click to expand...
 
Back
Top