Table field that holds row count from another tabl

Question

I have a field where I want to store the number of certain rows from another table. I have been incrementing this value when the rows are created, but feel this is probably not the best way. THen again I dont think that doing "count" on the table in question every time makes sense either. what is best approach?

EDIT: Count is filtered

Answer 1

If the value you're looking for is the count of rows from the first table, that would be a far less expensive operation than the overhead of insterting/updating a second table and then querying the value out of it.

Answer 2

While you never would want to store a calculated value in a database in an ideal world, it's often necessary down here in the mud and muck of the real world.

So Chris's suggestion of a trigger is your best bet. You may have to requery the count after each trigger invocation, since it may not always be possible to know how many rows were inserted or deleted (depending on RDBMS), and you may need multiple queries if there's a chance that more than one of your counts could be affected by a single INSERT or DELETE statement.

And, since you're also counting specific rows, you'll also need an UPDATE trigger, in case one of the values your count filters on is changed.

So, the trigger or triggers (you may be able to package it all into a single INSERT/UPDATE/DELETE trigger) could get pretty complicated.

Answer 3

Create an INSERT trigger to increment a product count.

Here is the code

create table Products ( ID int identity primary key )
GO
create table ProductCounter ( ProductCount int not null default 0 )
GO
insert ProductCounter default values
GO
create trigger trgIncrementProductCount
on Products
after insert
as
begin
    update  ProductCounter
    set ProductCount = ProductCount + 1
end
GO

insert Products default values
insert Products default values
select * from ProductCounter

insert Products default values
insert Products default values
select * from ProductCounter

Answer 4

If its sql server, you could make triggers for insert and delete to increment/decrement the value.

Or you could make a view for it.

Or, as i would do, simply do the count(*) every time, unless its a huge table.

Answer 5

How often is this row count needed? Is it possible to run something like a cron to update every few hours? Simple SELECT COUNT(ID) FROM Table WHERE...yada yada. It would not rely on the additional update after each query.

Just a ponderance...

Answer 6

Note this is a specific Sql Server answer, first off I'm not sure if

select count(*) from table 

really does a full table scan, the execution plan says that it does. Iff, that is true then you can lookup the number of rows in a table using the system tables and that will be faster especially on a large table.

select rowcnt from sysindexes
inner join sysobjects on sysobjects.id = sysindexes.id
where xtype='U'
and sysobjects.name = 'YourTableName'
and indid = 0

I know this relies on system tables that may change, but these haven't for the last decade, it also relies on your table having a primary key. So rather than storing the number of rows you have a fast but nasty lookup.