Split date range without Calendar table

Hi guys!

Is there any way to split date ranges (to days / hours / minutes) in one table without using Calendar table (without joining 2 tables)?

Reason I'm asking this is because we have a huge Fact table (500+ million records) and joining small Calendar table and this huge Fact table takes considerable amount of time.

So far we tried:

• Join 2 tables (Fact and Calendar)


• Cross Applying Calendar and table-valued function (that returns necessary measures from Fact table)

and both approaches took few minutes for 1 day to complete.

Most successful test we had so far was using CLR and executing table-valued functions in parallel for different 15 minute periods. This way we were able to get the results in 3-4 seconds.

Anyway, is there any effective pure T-SQL way to accomplish this?

Thanks in advance,

Miljan