Create a reference table of numbers

create_dates.sql

-- Drop the ref.dates table if it already exists.
IF OBJECT_ID('ref.dates') IS NOT NULL DROP TABLE ref.dates;
GO


-- Create a temp table with computed values. We will delete this
-- temp table at the end of this script.
CREATE TABLE #dates (
	date DATE NOT NULL,
	year AS DATEPART(YEAR, date),
	month AS DATEPART(MONTH, date),
	day AS DATEPART(DAY, date),
	day_of_week AS DATEPART(WEEKDAY, date),
	day_of_year AS DATEPART(DAYOFYEAR, date),
	week_of_year AS DATEPART(WEEK, date),
	quarter AS DATEPART(QUARTER, date)
);
GO


CREATE TABLE ref.dates (
	[date] DATE NOT NULL,
	[year] INT NOT NULL,
	[month] INT NOT NULL,
	[day] INT NOT NULL,
	[day_suffix] CHAR(2) NOT NULL,
	[day_of_week] INT NOT NULL,
	[day_of_year] INT NOT NULL,
	[week_of_year] INT NOT NULL,
	[first_of_month] DATE NOT NULL,
	[last_of_month] DATE NOT NULL,
	[first_of_year] DATE NOT NULL,
	[last_of_year] DATE NOT NULL,
	[quarter] INT NOT NULL,
	[quarter_suffix] CHAR(2) NOT NULL,
	[month_name] VARCHAR(20) NOT NULL,
	[month_name_abbr] VARCHAR(3) NOT NULL,
	[day_name] VARCHAR(20) NOT NULL,
	[day_name_abbr] VARCHAR(3) NOT NULL,
	CONSTRAINT [PK_ref.dates] PRIMARY KEY CLUSTERED ([date])
);
GO


-- year + month + day must be unique.
CREATE UNIQUE NONCLUSTERED INDEX [UX_ref.dates_year_month_day] ON ref.dates (year, month, day);
GO


-- year + day_of_year must be unique.
CREATE UNIQUE NONCLUSTERED INDEX [UX_ref.dates_year_day_of_year] ON ref.dates (year, day_of_year);
GO


-- Set the start date and the number of years. Going as far back as 1900, and running for
-- 300 years ought to cover every possible scenario.
DECLARE @start_date DATE = CONVERT(DATE, '1900-01-01');
DECLARE @number_of_years INT = 300;

-- Compute the end date. Add the number of years, then back up one day.
DECLARE @end_date DATE = DATEADD(YEAR, @number_of_years, @start_date);
SET @end_date = DATEADD(DAY, -1, @end_date);

-- Insert rows into the temp table.
WITH cte_date (date) AS
(
    SELECT  DATEADD(DAY, rownum - 1, @start_date)
    FROM
    (
        SELECT TOP (DATEDIFF(DAY, @start_date, @end_date)) rownum = ROW_NUMBER() OVER (ORDER BY number)
        FROM ref.numbers
    ) AS dates
)
INSERT INTO #dates (date)
SELECT  date
FROM cte_date;
GO


INSERT INTO ref.dates (
    [date], 
    year, 
    month, 
    day,
    day_suffix,
    day_of_week,
    day_of_year,
    week_of_year,
    first_of_month,
    last_of_month,
    first_of_year,
    last_of_year,
    quarter,
    quarter_suffix,
    month_name,
    month_name_abbr,
    day_name,
    day_name_abbr
)
SELECT  date, 
        year, 
        month, 
        day,
        ref.fn_numeric_suffix(day),
        day_of_week,
        day_of_year,
        week_of_year,
        MIN(date) OVER (PARTITION BY year, month),
        MAX(date) OVER (PARTITION BY year, month),
        MIN(date) OVER (PARTITION BY year),
        MAX(date) OVER (PARTITION BY year),
        quarter,
        ref.fn_numeric_suffix(quarter),
        DATENAME(MONTH, date),
	LEFT(DATENAME(MONTH, date), 3),
        DATENAME(WEEKDAY, date),
	LEFT(DATENAME(WEEKDAY, date), 3)
FROM #dates
ORDER BY date ASC;
GO


-- Drop the temporary table. We no longer need it.
DROP TABLE #dates;
GO


SELECT * FROM ref.dates;
GO

create_numbers.sql

USE [master];
GO

-- Ensure that the [ref] schema exists.
IF SCHEMA_ID('ref') IS NULL EXEC('CREATE SCHEMA [ref];');
GO

-- Drop the table if it already exists.
IF OBJECT_ID('ref.numbers') IS NOT NULL DROP TABLE ref.numbers;
GO

CREATE TABLE ref.numbers (
  number INT NOT NULL,
  formatted_number VARCHAR(20) NOT NULL,
  number_suffix CHAR(2) NOT NULL,
  CONSTRAINT [PK_ref.numbers] PRIMARY KEY CLUSTERED (number)
);
GO

CREATE UNIQUE NONCLUSTERED INDEX [UX_ref.numbers_formatted_number] ON ref.numbers (formatted_number);
GO

-- Set the lower bound. This will be either 0 or 1.
DECLARE @lower_bound INT = 0;
DECLARE @upper_bound INT = 2e6;

-- Insert records into the numbers table.
WITH cte_number (number) AS
(
  SELECT ROW_NUMBER() OVER (ORDER BY source1.object_id) + (@lower_bound - 1)
  FROM sys.all_objects AS source1
  CROSS JOIN sys.all_objects AS source2
)
INSERT INTO ref.numbers (number, formatted_number, number_suffix)
SELECT number,
       FORMAT(number, 'N0'),
	     ref.fn_numeric_suffix(number)
FROM cte_number
WHERE number <= @upper_bound;
GO

SELECT COUNT(*) AS c FROM ref.numbers;
GO

SELECT TOP (10) * FROM ref.numbers;
GO

SELECT * FROM (SELECT TOP (10) * FROM ref.numbers ORDER BY number DESC) AS n ORDER BY number ASC;
GO