Sql-server – SQL Server: Why would an ALTER TABLE to add nullable column run forever

You've already been told about why TRUNCATE would be so much faster/better/sexier than DELETE, but there's still a question left to address:

Why is SELECT slower after DELETE completed?

That is because DELETE has only ghosted the rows. The table is just as big as when it had 12M rows, even though it has none. To count the rows (0), it takes just as much time it took to count 12M rows. In time the ghost cleanup process will garbage collect these ghosted records and deallocate pages that contained only ghosts, and your SELECTs will speed up. But right now if you check Skipped Ghosted Records/sec in perfmon is probably skyrocketing during SELECT COUNT(*). You could also speed up things by rebuilding the table: ALTER TABLE ... REBUILD.

TRUNCATE would had also taken care of this problem, as it leaves no ghosts behind.

See also Inside the Storage Engine: Ghost cleanup in depth.

You need a split TVF on both sides to make it a JOIN

SELECT DISTINCT
   M.ID
FROM
   MyTable M
   CROSS APPLY
   dbo.SomeSplitTVF(M.Blahcolumn) M2
   JOIN
   dbo.SomeSplitTVF(@parameter) P ON M2.value = P.value

There are several solutions for "dbo.SomeSplitTVF" on SO, but you could start with this article by our own Aaron Bertrand