Extent Allocation Mechanism in SQL Server 2017
In SQL server 2016 and later, if we create a new table or index, will it be allocated a uniform extent or a mixed extent initially? 


If the database was created in SQL Server 2016+ then by default the uniform extent allocation is on by default and thus a uniform extent will be allocated even if just a single page is needed (extents are 8 contiguous pages starting from database page 0).

If, however, the database was created in an older version &lt;= SQL Server 2014 then by default the uniform extent allocation database setting did not exist and thus a single page from a shared extent was used. In &lt;= 2014 there was a trace flag that would override this TF1118 setting but it would be for the entire instance and not just a single database. SQL Server 2016 took away the trace flag (which is no a NOP) and made the setting per database with a default of ON.