The other answers offer the preferred / logical / canonical solution.
There is a downside to those. Obtaining the MAX(creationdate)
(or ORDER BY creationdate
) for the tables with many rows now (think Stack Overflow) can take a while. None of the date fields in the SEDE database schema have an index.
Because SEDE is a bit special and we have access to implementation details we can (ab)use that knowledge to get the highest possible date without having the Query Engine perform a full Table Scan.
SQL Server keeps Meta data in its sys
schema for the different database objects. One of these meta data tables is sys.tables
which inherits the field create_date
from its parent, sys.objects.
Each Sunday the sp_Refresh_Database.sql
script is run for each database and its main function is to create a new database and recreate and populate all the tables. Once a new database is created, the tables are processeds by the script for example PostsWithDeleted, is created and populated with this statement:
Select * Into PostsWithDeleted
From [SomeExportServer].[StackOverflow].[dbo].[vExportPostsWithDeleted]
with (nolock)
This means that the new table PostsWithDeleted gets created right before the last data could enter the database on SomeExportServer. Therefor there will not be any records in PostsWithDeleted after the create_date
of the PostWithDeleted table.
With this knowledge you can replace your GetDate()
with'
(select create_date from sys.tables where object_id =object_id('PostsWithDeleted'))
Do notice that you get the best results if you use the specific table you're using the dates from as that is the most accurate. On Stack Overflow there is a five hour gap between the first table and last table created. There are some data anomalies due to this effect. For example you can have suggestededits with a postid that is not present yet in the PostsWithDeleted table. The PostId does exist on the site.
If you want to have the latest date where all tables are most likely to be all in sync take the create_date
of the Users
table because that is always the first table that gets created anew each Sunday.
select create_date from sys.tables where object_id = object_id('Users')