You could also consider using an A/B table approach. That is, the nightly load goes into a different table name. When the load process is complete, either the...

Most likely a LOOP join will be used. That is not guaranteed, of course, but it is likely.

If you want to be sure what join algorithm will be used,...

Look at the tempdb log file size and growth amount, particularly if it is a % (which you should change to a fixed amount: never use %, no matter what...

Alan.B (3/31/2016)

1. Nothing I posted uses recursion. Look again.

2. People should put unique nonclustered indexes on their dim_date or calendar but dont. It's not like it gets modified all...

We don't really know why MS chose to make the PK by default also the clustering index.

Personally I think it was more about making it "easy to use" so it...

You should definitely cluster the temp table on the key column as well. It can't hurt, but it can help under certain conditions.

SELECT TOP (0) D.X INTO #tmp FROM...

Hmm, not sure what you mean there. There's no problem at all explicitly creating a PK as nonclustered, even...

souLTower (3/30/2016)

---

If I have a data table containing data for 3 items (say people), and I know that no one is modifying data for person 1, and I can reasonably...

Simply because Microsoft chose to assume that a PK would also automatically be the clustering key (if one didn't already exist, of course). Other dbms's don't make that assumption,...

Alan.B (3/30/2016)

---

Alan.B (3/30/2016)

---

ScottPletcher (3/30/2016)

---

Alan.B (3/30/2016)

---

ScottPletcher (3/30/2016)

---

Alan.B (3/29/2016)

Lastly, ditto the last Thursday of the month:

SET STATISTICS IO ON;

PRINT 'Old way:';

SELECT DISTINCT MAX([Date]) OVER (PARTITION BY [Year],[MonthName],[WeekdayName]) AS [Date]

FROM ...

Alan.B (3/30/2016)

---

ScottPletcher (3/30/2016)

---

Alan.B (3/29/2016)

Lastly, ditto the last Thursday of the month:

SET STATISTICS IO ON;

PRINT 'Old way:';

SELECT DISTINCT MAX([Date]) OVER (PARTITION BY [Year],[MonthName],[WeekdayName]) AS [Date]

FROM dbo.itvf_CALENDAR('01/01/2014','12/31/2014')

WHERE ...

Alan.B (3/29/2016)

Lastly, ditto the last Thursday of the month:

SET STATISTICS IO ON;

PRINT 'Old way:';

SELECT DISTINCT MAX([Date]) OVER (PARTITION BY [Year],[MonthName],[WeekdayName]) AS [Date]

FROM dbo.itvf_CALENDAR('01/01/2014','12/31/2014')

WHERE [WeekdayName]...

SELECT DATENAME(MONTH, CAST('01-' + string AS date)) + SPACE(1) +

CAST(YEAR(CAST('01-' + string AS date)) AS varchar(4))

FROM (VALUES('Dec-15'),('Jan-16'),('Feb-16')) test_data(string)

A couple of things to consider.

1) Change the PRINT @<sql_variable> to SELECT @<sql_variable>, since SELECT can display many more chars than SELECT.

2) Explicitly cast the unicode literal in the SET...

You could create a logon trigger to capture which logins are being used, and how often.