Microsoft SQL Server: Any Way to Tell When a Record Was Created

Microsoft SQL Server: Any way to tell when a record was created?

Nope.

You need to have a column for this.

Imagine how big the meta-data would be if you needed to keep a record for each record for creation! Would you also want to keep meta-data on your meta-data so you know when the meta-data was updated? The space use can quickly escalate.

SQL Server keeps some stats but something this specific will need to come from a user-defined field.

As a side note, you can make it more difficult to tamper with the date on your created field if you use a lookup table. Create a table "TableName_CreateDate" and use the PK from your actual table and a date value. Your date is in a separate location and less likely to be modified but you can still JOIN on it to get your order. You would need to create a trigger to update this with new values.

If you only want the DATE and don't need a datetime value, you can go one step further and just have a table of dates and a lookup table that joins to that. I.e.:

Table->Table.PK + Date.Pk -> DateTable

This would save a lot of drive space if you have a lot of rows (4 bytes per row I think).

Can you tell me when data was inserted into a table

I think the short answer is NO, there's no magic, ad hoc SQL query that will let you go back after the fact and find out when a row was inserted.

If you want to know when a row is inserted, the easiest thing would be to simply add a date or timestamp field with a default value (like getDate()) that automatically fills in the date/time when the row is inserted.

There are, of course, SQL logs available that will let you track when rows are inserted, updated, deleted, etc., but those require set up and maintenance.

Third option would be to have the program that's inserting the data perform some logging.

SQL Server row date last modified

As the others are hinting at, your colleague must be talking gibberish, or referring to something else. The on-disk structure for a record, or page for that sake, does not contain any references to the time of the last update. While you can find info regarding the last update at the object level, no such info is available at the record/row level.

Need a datetime column in SQL Server that automatically updates when the record is modified

SQL Server doesn't have a way to define a default value for UPDATE.

So you need to add a column with default value for inserting:

ADD modstamp DATETIME2 NULL DEFAULT GETDATE()

And add a trigger on that table:

CREATE TRIGGER tgr_modstamp
ON **TABLENAME**
AFTER UPDATE AS
  UPDATE **TABLENAME**
  SET ModStamp = GETDATE()
  WHERE **ID** IN (SELECT DISTINCT **ID** FROM Inserted)

And yes, you need to specify a identity column for each trigger.

CAUTION: take care when inserting columns on tables where you don't know the code of the application. If your app have INSERT VALUES command without column definition, it will raise errors even with default value on new columns.

Fastest way to determine if record exists

SELECT TOP 1 products.id FROM products WHERE products.id = ?; will outperform all of your suggestions as it will terminate execution after it finds the first record.

Determine what user created objects in SQL Server

The answer is "no, you probably can't".

While there is stuff in there that might say who created a given object, there are a lot of "ifs" behind them. A quick (and not necessarily complete) review:

sys.objects (and thus sys.tables, sys.procedures, sys.views, etc.) has column principal_id. This value is a foreign key that relates to the list of database users, which in turn can be joined with the list of SQL (instance) logins. (All of this info can be found in further system views.)

But.

A quick check on our setup here and a cursory review of BOL indicates that this value is only set (i.e. not null) if it is "different from the schema owner". In our development system, and we've got dbo + two other schemas, everything comes up as NULL. This is probably because everyone has dbo rights within these databases.

This is using NT authentication. SQL authentication probably works much the same. Also, does everyone have and use a unique login, or are they shared? If you have employee turnover and domain (or SQL) logins get dropped, once again the data may not be there or may be incomplete.

You can look this data over (select * from sys.objects), but if principal_id is null, you are probably out of luck.

Create a SQL query to retrieve most recent records

Aggregate in a subquery derived table and then join to it.

 Select Date, User, Status, Notes 
    from [SOMETABLE]
    inner join 
    (
        Select max(Date) as LatestDate, [User]
        from [SOMETABLE]
        Group by User
    ) SubMax 
    on [SOMETABLE].Date = SubMax.LatestDate
    and [SOMETABLE].User = SubMax.User 

SQL get the last date time record

If you want one row for each filename, reflecting a specific states and listing the most recent date then this is your friend:

select filename ,
       status   ,
       max_date = max( dates )
from some_table t
group by filename , status
having status = '<your-desired-status-here>'

Easy!

---

Related Topics