create_date and modify_date for sys.objects in MSSQL shows same date

Question

I am trying to figure out when is the create date of a user defined object in the database.

but when I ran a select query on sys.sysobjects, it seems create_date is the same as modify_date.

Is this by design in SQL Server? I am using SQL Server 2016.

Answer 1

I assume you are actually talking about sys.objects view, and not sys.sysobjects.

It seems like the modify_date is set to create_date until some other changes to table are made (which is pretty common pattern). According to documentation, the modify_date is:

Date the object was last modified by using an ALTER statement. If the object is a table or a view, modify_date also changes when an index on the table or view is created or altered.

This would also mean that (for example) inserting and updating data does not count - only changes to the structure of table (well, and indexing).

Demo (Fiddle):

DROP TABLE  IF EXISTS MyTable;

CREATE TABLE MyTable (Id INT);

SELECT 
  'after CREATE', 
  [name], 
  create_date, 
  modify_date, 
  [Same?] = IIF(create_date = modify_date, 'same', 'different')
FROM sys.objects 
WHERE [object_id] = OBJECT_ID('dbo.MyTable')

/*Let's wait a bit, to make potential differences more obvious.*/
WAITFOR DELAY '00:00:05';
  
INSERT INTO MyTable(Id)
VALUES (1), (2);

SELECT 'after INSERT - does not change the modify_Date', 
  [name], 
  create_date, 
  modify_date, 
  [Same?] = IIF(create_date = modify_date, 'same', 'different')
FROM sys.objects 
WHERE [object_id] = OBJECT_ID('dbo.MyTable')

ALTER TABLE MyTable 
ADD [Name] VARCHAR(50);

SELECT 'after ALTER', 
  [name], 
  create_date, 
  modify_date, 
  [Same?] = IIF(create_date = modify_date, 'same', 'different')
FROM sys.objects 
WHERE [object_id] = OBJECT_ID('dbo.MyTable')

Answer 2

In addition to what Yano_of_Queenscastle said, if the object you're trying to verify is a table, the reason create_date is the same as modify_date could be the edition of the specified table using SSMS Design mode. The edition of a table with that GUI can cause the table to be recreated. By default, SSMS even show an error to avoid that, as you can see in the picture:

But one can avoid that message by removing that security lock:

Tools > Options > Designers > Table and Database Designers > Prevent saving changes that require table re-creation

Now the message is not shown and the table is recreated without any warning, making create_date and modify_date the same.