The first required step in getting reasonably decent table designs is to get rid of the myth that every table has to have an identity column and, far worse, should...

Table variables are almost always much slower than temp tables.  I wouldn't use table variables unless I knew it was only 1 or 2 rows, ever, period, or I had...

If he is actually doing a SELECT ... INTO #temp, that could cause blocking, perhaps long blocking, but it normally wouldn't cause true "deadlocks".  Is it an actual deadlock or...

That's a system-generated constraint name for an implicit DEFAULT constraint, such as:

USE tempdb;
CREATE TABLE table1 ( column1 int DEFAULT 0 );
EXEC sp_help 'table1';
DROP TABLE table1;

If you want to apply a table-valued function to a string from a table, you need to use APPLY, typically CROSS APPLY.  Something like this:

SELECT *...

For that one query, try putting the variables into a (keyed) temp table (not a table variable), then joining to that table.:

CREATE TABLE #ids ( id...

There's an off chance the slowness has to do with log space, particularly if not in tempdb.

How much total space is the new table?  If it's large, make...

In the real world, I've seen the method (1) style used quite a bit.  Yes, it does violate 1NF, but it's pragmatic for the task at hand.  Bottle size would...

Most typically in SQL Server I see this technique used:

SELECT TOP ...
FROM dbo.table_name
ORDER BY NEWID()

I presume that gives a roughly random sample.  Of course...

I've never seen that, and I've been in...

I was in hurry so my code isn't what I'd normally do.  Normally I'd calc only the first/last Monday, then simply subtract/add 7 days to that for other dates.  There's...

The method below is math only, and thus very efficient; works under all date settings; and is flexible/reusable for other days / months / etc..

DECLARE...

You can use APPLY to effectively assign alias names to expressions / results.  You can even nest APPLYs so that the alias from one is used in the next one.

If you want a rolling 60 months:

WHERE OpenDate >= DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) - 60, 0) /* or -59 if you count the current month...