Count function

[Stephen]

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

[AMDRIT]

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" <St*****@discussions.microsoft.com> wrote in message
news:78**********************************@microsof t.com...

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

[Stephen]

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

"AMDRIT" wrote:

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" <St*****@discussions.microsoft.com> wrote in message
news:78**********************************@microsof t.com...

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

[Chris Dunaway]

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!

[AMDRIT]

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" <St*****@discussions.microsoft.com> wrote in message
news:2F**********************************@microsof t.com...

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

"AMDRIT" wrote:

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" <St*****@discussions.microsoft.com> wrote in message
news:78**********************************@microsof t.com...

>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

[Stephany Young]

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" <am****@hotmail.com> wrote in message
news:Ok**************@TK2MSFTNGP03.phx.gbl...

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" <St*****@discussions.microsoft.com> wrote in message
news:2F**********************************@microsof t.com...

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

"AMDRIT" wrote:

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" <St*****@discussions.microsoft.com> wrote in message
news:78**********************************@microsof t.com...
>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