My suspicion would be IO contention, particularly if the logic in the view is causing heavy spilling into tempdb and if the data and log files are on the same...

I still find myself occasionally having to strip:
SELECT TOP 100 PERCENT
...
ORDER BY xxx

from views because somebody decided it was a way to "get around" not...

In both cases you're providing the foreign key constraint as a table constraint (hence having to specify the column "a") as opposed to specifying it as a column constraint (for...

Yes, this is why I've often had to explain to my boss (who is most definitely not a SQL guy) that getting hung up on what the language standard says...

The Industry_Sectors subquery is just missing the FOR JSON PATH. I'm not sure what you're trying to accomplish with the second but it isn't framed as a subquery.

Well the SQL server equivalent is probably to use a Custom Table Type and select a single row into it. It's not exactly equivalent but it's a close approximation.

Can this code be called by multiple overlapping callers? If so, you almost certainly have a race condition if multiple calls get the same result from SPRC_LASTID_GET before then attempting...