Okay, so I have a query that looks like this:
Declare @Table1 Table (some columns)
Insert into @Table1 [QueryA]
set Field1 = A.Value1
from ([QueryB]) A
where Field2 = A.Value2
Select * from @Table1
QueryA is a simple query that returns ~150 rows. QueryB is more complex and ...
We need to get better at closing/deleting hopeless questions quickly. And placing otherwise duff ones on hold quickly. And reopening improved, previously-duff questions. The system does work, mostly, eventually.
I wouldn't bother but there are 6 upvotes on the accepted answer, which was posted long enough after the last edit or comment from the OP too, so...
What do you mean by row 0 and row COUNT(*)? First and last row when sorted by val? In your example, two rows have the min val (which is 1), it's the rows id=1 and id=4. Your expected output shows that of the two only id=1 was removed. Why not id=4? And must it really be one row and not both? I'm asking this because the answer you've accepted would remove both val=1 rows from your example data. What was the actual requirement? — Andriy M3 mins ago
@swasheck I don't think I'm dragging and dropping, but agree that there are several tools like that. But the flavor of SQL is slightly different and I wasn't really that good at writing efficient SQL in the first place compared to you guys. So it will be a fun learning adventure.
You can not use dynamic sql in views or function. One possible solution could be to alter your query to drop/create a view and use it as a stored procedure that gets called by a trigger on insert/delete in the source table (so that the view is updated whenever rows are inserted or deleted).
You also say "SELECT TOP 1000 * FROM table_with_1m_rows ORDER BY col1; ORDER BY will perform even worse, because it will have to first arrange all 1m rows in the table before it then returns 1000 rows. All of the above holds true whether the DB needs to use a sequential scan or an index scan.." Really? Will such a query always have to sort all the 1m rows of the table? — ypercube2 mins ago
Please, someone tell me I'm crazy. I can't understand this guy's assertions in the question.