Database Calendar Table Storage-with Usage

--1. Continuous Digital table
drop table Nums
CREATE TABLE Nums (n int not NULL PRIMARY KEY CLUSTERED)

With B1 as (select N=1 UNION all select N=1),--2
B2 as (SELECT n=1 from B1 a cross JOIN B1 b),--4
B3 as (SELECT n=1 from B2 a cross JOIN B2 b),--16
B4 as (SELECT n=1 from B3 a cross JOIN B3 b),--256
B5 as (SELECT n=1 from B4 a cross JOIN B4 b),--65536
--.. You can continue, but it's enough.
CTE as (select Autonum=row_number () over (order by (select 1)) from B5 a cross JOIN B3 b)--65536 * 16
--select * from CTE
Insert INTO Nums SELECT TOP (1000000) autonum from CTE--insert 1 million data

--2. Calendar table
drop TABLE Calendar
CREATE TABLE Calendar (
Date date not NULL PRIMARY KEY CLUSTERED,
Weeknum int not NULL,
Weekday int not NULL,
Weekname nchar (3) not NULL,
Workday bit not NULL
)

With CTE1 as (
SELECT
Date = DATEADD (Day,n, ' 19991231 ')
From Nums
WHERE n <= DATEDIFF (day, ' 19991231 ', ' 20501231 ')
),
CTE2 as (
SELECT
Date
Weeknum = DATEPART (week,date),
Weekday = DATEPART (weekday,date)-1,
Weekname = Datename (weekday,date)
From CTE1
)
--select * from CTE2
INSERT into Calendar
SELECT
Date
Weeknum
Weekday
Weekname,
Workday = case while weekday in (0,6) then 0 ELSE 1 END
From CTE2

--
SELECT * FROM Calendar
SELECT @ @DATEFIRST

Database Calendar Table Storage-with Usage