I'll give it a guess.  Typically now you use OUTER APPLY to do the type of lookup  you're trying to do.  You also definitely don't need to join the table...

Add a clustering index to pre-sort at least a few of the values.  Just based off the very limited data you gave, I'd suggest something like this:

CREATE CLUSTERED...

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...