Detecting changes in a SQL Server table

No, there isn't any. Any sort of 'last updated at' tracking would run into a severe performance problem as all updates, from all transactions, would attempt to update the one record tracking the 'last updated at'. This would effectively mean only one transaction can update the table at any moment, and all other transactions have to wait for the first one to commit. Complete Serialization. The number of admins/devs willing to put up with such performance penalty just for the benefit of knowing when the last update occurred is probably small.

So you are stranded to handle it via custom code. That means triggers since the alternative (detecting from log records) is a prerogative reserved only for transactional replication (or it's CDC alter-ego). Be aware that if you try to track it via a 'last updated at' column then you'll be facing exactly the serialization problem mentioned above. If update concurrency is important then you'd have to use a queue mechanism (trigger uses an INSERT and then a process aggregates the inserted values to formulate the 'last updated at'). Do not try to cheat with some 'clever' solution like sneaking at the current identity or looking up sys.dm_db_index_usage_stats. And also an 'updated_at' per-record column, like Rails timestamps have, is not working because it does not detect deletes...

Is there any 'lightweight' alternative? Actually there is one, but it is difficult to say whether it will work for you and is difficult to get it right: Query Notifications. Query Notification does exactly that, it will set up a notification if any data has changes and you need to refresh your query. Although most devs are familiar only with its .Net incarnation as SqlDependency, Query Notification can be used as a long lived, persisted mechanism to detect data change. Compared with true change tracking it is going to be really lightweight, and its semantics are closer to your needs (something, anything, changed, so you need to rerun the query).

But in the end, in your place, I would really reconsider my assumptions and go back to the drawing board. Perhaps you can use log shipping or replication to set up a reporting database, on a different server. What I read between the lines is that you're in need of a proper ETL pipe-line and an analytics data warehouse...

It looks like I'm two years late to the game, here, but there is indeed a pretty lightweight way of doing what you're asking for.

There are two SQL Server mechanisms that can help you. Your ultimate solution might be a hybrid of the two.

Change Tracking. SQL Server has the capability of placing specific tables under watch, recording only which rows have changed (by their primary key value), and what kind of change it was (Insert, Update, or Delete). Once you set up change detection on a set of tables, a lightweight query can tell you whether any changes have been made to the table since the last time you checked. The overhead is approximately the same as maintaining an additional simple index.

Rowversion / timestamp. This is a 8-byte varbinary column type (castable to a BigInt) that is incremented, database wide, whenever a row that contains one is inserted or updated (it doesn't help with deletes). If you indexed these columns, you could easily tell if row data has changed by comparing the MAX(timestamp) to its value since the last time it was evaluated. Since the value is monotonically increasing, this would give you a reliable indication that data has changed if the new value is larger than it was the last time you checked it.

If the source is insert-only give it an IDENTITY column. When you do your data transfer you log the highest value written across. During the next transfer you need only query for values greater than that logged during the previous transfer. We do this for transferring log records to a data warehouse.

For updatable rows add a "dirty" flag. It will have three values - clean, dirty and deleted. Day-to-day queries will have to omit rows with the flag set to "deleted". This will be expensive in maintenance, testing and run-time. After the big query you mention all rows marked for delete must be removed and the flag reset for all others. This will not scale well.

A lighter alternative to Change Data Capture is Change Tracking. It will not tell you what values changed, just that the row has changed since it was last queried. Built-in functions facilitate retrieval of changed values and management of tracking. We have had success using CT to process about 100,000 changes per day in a 100,000,000 row table.

Query Notifications act at a higher lever still - at the level of a result set. Conceptually, it's like defining a view. If SQL Server detects that any row returned through that view has changed, it fires a message to the application. There is no indication how many rows changed, or which columns. There is only a simple messages saying "something happended." It is up to the application to enquire and react. Practically it is a lot more complex than that, as you may imagine. There are restrictions on how the query can be defined and notification may fire for conditions other than changed data. When the notification fires it is removed. If further activity of interest happens subsequently no further message will be sent. It is up to the application designer to ensure activity between a notification and subsequent re-establishment of the query is properly handled.

In the context of the OP's question, QN will have the advantage of being low overhead to set up and little run time cost. It may be significant effort to establish and maintain a rigorous subscribe-message-react regime. Since the data table is large it is likely there will be frequent changes to it, meaning the notification is likely to fire in most processing cycles. As there is no indication of what changed incremental processing of the deltas will not be possible, as it would with CT or CDC. The overhead due to false triggering is a tiresome, but even in worst-case the expensive query need not be run any more frequently than it is currently.