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.

23:59:59.000 does not really make sense in SQL Server, since it doesn't match any system data type's time sensitivity.

Smalldatetime is 23:59 and datetime is 23:59:59.997.

Use a tally table -- a table of just sequential numbers -- to generate the months. You can use an in-line tally table or a stored tally table. ...

Are you certain that query is the one that is deadlocking? So that query wasn't really commented out in the proc?!

Do you have on trace flags, at least 1222,...