SELECT
    convert(date , d ) as CalDate,
    DATEPART(YEAR, d) as CalYear,
    DATEPART(QUARTER,  d) as CalQuarter,
    DATEPART(MONTH,  d) as CalMonth,
    DATENAME(MONTH,    d) as CalMonthName,
    DATEPART(WEEK,     d) as CalWeekOfYear,
    CONVERT(CHAR(10),  d, 101) as CalDateStr,
    DATEPART(WEEKDAY,  d) as CalDayOfWeek,
    DATEPART(DAY,  d) as CalDayOfMonth,
    DATEPART(dy, d) as CalDayOfYear,
    CONVERT(VARCHAR(10), DATENAME(WEEKDAY, d)) as CalDayName,
    DATEPART(YEAR, d)*100+REPLACE(STR(DATEPART(MONTH,  d), 4), SPACE(1), '0') as CalYearMonth,
    DATEPART(YEAR, d)*10000+REPLACE(STR(DATEPART(MONTH,  d), 4), SPACE(1), '0')*100+DATEPART(DAY,  d) as CalYearDay,
    CASE WHEN REPLACE(STR(DATEPART(MONTH,  d), 4), SPACE(1), '0')*100+DATEPART(DAY,  d) IN (101, 501, 508, 714,815, 1101, 1225) THEN 1 ELSE 0  END as CalIsPublicHoliday,
    case when DATEPART(WEEKDAY,  d) in (6, 7) THEN 1 else 0 end as CalIsWeekEnd
    into dbo.Calendar
from
(
SELECT d = DATEADD(DAY, rn - 1, '19000101')
  FROM
  (
    SELECT TOP (DATEDIFF(DAY, '19000101', '29991231'))
      rn = ROW_NUMBER() OVER (ORDER BY s1.[object_id])
    FROM sys.all_objects AS s1
    CROSS JOIN sys.all_objects AS s2
    ORDER BY s1.[object_id]
  ) A
) b;

create index IdxRefCalendar on dbo.[REF-Calendar](CalDate);