Optimal solution for getting timestamp of last entry

Hi

I'm trying to find the optimal way of getting the timestamp of the last updated entry in an mssql database. A database is updated only about 5 times a minute, how ever a request for the time of the last entry could be around 1 per second. For this reason i was thinking of having a separate table which has a single row which is updated everytime a new entry is updated in the main table. I would then only need a simple SELECT statement and need very little processing power.

Is this the best method, or can you think of any others i could use?

many thanksWhat's the scope? Database wide or table specific?|||single table, single row? a potential bottleneck, but only 5/minute should be no problem, so yeah, that would be the simplest solution

but if this is just for your main table, consider an index on the datetime column descending, so that TOP 1 gets your last update easily|||but if this is just for your main table, consider an index on the datetime column descending, so that TOP 1 gets your last update easilyHow does SQL Server handle a monotonically decreasing index? Presumably with regular page splits? Like you say low activity will mitigate this somewhat.|||oh dear, i'm in over my head here

what is monotonically? no music? and how is monotonically different from sequentially? from consecutively?

and what's a page split?

sheesh, i should stick to stuff i know|||SELECT MAX(upd_dt) FROM (
SELECT MAX(upd_dt) AS upd_dt FROM tbl1 UNION ALL
SELECT MAX(upd_dt) AS upd_dt FROM tbl2 UNION ALL
SELECT MAX(upd_dt) AS upd_dt FROM tbl3 UNION ALL
SELECT MAX(upd_dt) AS upd_dt FROM tbl4 UNION ALL
SELECT MAX(upd_dt) AS upd_dt FROM tbl5 UNION ALL
SELECT MAX(upd_dt) AS upd_dt FROM tbl6) AS XXX

???????????????

You gotta give us more to go on|||Hi, its a pretty simple database, there are only two tables, one with all the data, and the other which will hold the single row with the latest time stamp.

I realise i can SELECT the top1 descending record from the main table, but won't that be more work than simply selecting a single value?

...or is selecting top1 more viable than loosing out on 'bottle necking' when selecting a single row..!|||more work? for you or for the server?

i'll bet if you timed the cpu cycles, it'd be pretty close|||more work? for you or for the server?

i'll bet if you timed the cpu cycles, it'd be pretty close

lol - i don't mind doing the work! - i just want this to be as optimal as possible...|||How many rows in the table?|||oh dear, i'm in over my head here
...
sheesh, i should stick to stuff i knowI don't think so - I think you just sometimes pretend you know less than you do so no one asks you anything remotely resembling a dba question :)|||In the main table, there will be approximately 1000 new rows per day.

I suppose this leads onto another question, are there any limits other than disk space, eg on the cpu that i should be aware of?|||Sorry I meant in total|||well theres zero rows at the moment because im in the middle of building the application and setting up the database. But i am expecting approx 1000 new rows a day - not sure what limits are advisable as to how many rows before i need to do something, eg create a new table or whatever?|||well theres zero rows at the moment because im in the middle of building the application and setting up the database. But i am expecting approx 1000 new rows a day - not sure what limits are advisable as to how many rows before i need to do something, eg create a new table or whatever?You won't get close to the sort of volume where you need to start getting canny at 1000 inserts a day.

Agreed with Rudy - just put the column in your table and then select MAX() of that column to get the last update. Index the column. I think I would index ASC but there you go. Triger to update it.

BTW - one table - is it normalised?|||ok - sounds good to me - can i just write this out how i see it working from what you've said above:

1) Index time/date column Ascending
2) When getting the last update, use something similiar to:

SELECT MAX(timeDateStampColumn) as "LastEntry"
FROM table;

BTW - one table - is it normalised?
My application is based around sms text messages, each new row in my table is basically a new text message, there are only a few columns (id, message, mobile number, dateTimeStamp) so no need to normalise. I'm at the designing stages of the project so will certainly be more tables added later on.|||My application is based around sms text messages, each new row in my table is basically a new text message, there are only a few columns (id, message, mobile number, dateTimeStamp) so no need to normalise. That table structure sounds fine although I would disagree and say that the very early stages are absolutely the right time to normalise. I would also strongly recommend you design your complete database before worrying about requirements like "how do I find the time of the last SMS?".|||point taken ;)

thanks everyone for your excellent help (as usual) :D