Да - модульные тесты должны быть отдельными и иметь целью проверить только одну вещь (или, по крайней мере, небольшое количество тесно связанных вещей). Кроме того, вызовы data.Expect и phone.Expect в вашем методе тестирования создают ожидания, а не вызовы-заглушки, которые могут сделать ваши тесты хрупкими, если вы выполните рефакторинг ...
Он использовался для « промежуточной материализации (поиск Google) »
Хорошая статья: Адам Маханик: Изучение секретов промежуточной материализации
Он даже поднял MS Connect , чтобы это можно было сделать более чистым способом
Я считаю, что «не по своей сути плохой», но не используйте его, если не уверен на 100%. Проблема в том, что он работает только в то время, когда вы это делаете, и, вероятно, не позже (уровень исправления, схема, индекс, количество строк и т. Д.) ...
Это может не работать, потому что вы не знаете в в каком порядке выполняются вычисления
SELECT foo From MyTable WHERE ISNUMERIC (foo) = 1 AND CAST(foo AS int) > 100
И это также может быть неудачным, поскольку
SELECT foo
FROM
(SELECT foo From MyTable WHERE ISNUMERIC (foo) = 1) bar
WHERE
CAST(foo AS int) > 100
Однако этого не было в SQL Server 2000. Внутренний запрос оценивается и помещается в буфер:
SELECT foo
FROM
(SELECT TOP 100 PERCENT foo From MyTable WHERE ISNUMERIC (foo) = 1 ORDER BY foo) bar
WHERE
CAST(foo AS int) > 100
Примечание. это все еще работает в SQL Server 2005
SELECT TOP 2000000000 ... ORDER BY...
I have seen other code which I have inherited which uses SELECT TOP 100 PERCENT
The reason for this is simple: Enterprise Manager used to try to be helpful and format your code to include this for you. There was no point ever trying to remove it as it didn't really hurt anything and the next time you went to change it EM would insert it again.
No reason but indifference, I'd guess.
Such query strings are usually generated by a graphical query tool. The user joins a few tables, adds a filter, a sort order, and tests the results. Since the user may want to save the query as a view, the tool adds a TOP 100 PERCENT. In this case, though, the user copies the SQL into his code, parameterized the WHERE clause, and hides everything in a data access layer. Out of mind, out of sight.
If there is no ORDER BY
clause, then TOP 100 PERCENT
is redundant. (As you mention, this was the 'trick' with views)
[Hopefully the optimizer will optimize this away.]
...allow use of an ORDER BY in a view definition.
That's not a good idea. A view should never have an ORDER BY defined.
An ORDER BY has an impact on performance - using it a view means that the ORDER BY will turn up in the explain plan. If you have a query where the view is joined to anything in the immediate query, or referenced in an inline view (CTE/subquery factoring) - the ORDER BY is always run prior to the final ORDER BY (assuming it was defined). There's no benefit to ordering rows that aren't the final result set when the query isn't using TOP (or LIMIT for MySQL/Postgres).
Consider:
CREATE VIEW my_view AS
SELECT i.item_id,
i.item_description,
it.item_type_description
FROM ITEMS i
JOIN ITEM_TYPES it ON it.item_type_id = i.item_type_id
ORDER BY i.item_description
...
SELECT t.item_id,
t.item_description,
t.item_type_description
FROM my_view t
ORDER BY t.item_type_description
...is the equivalent to using:
SELECT t.item_id,
t.item_description,
t.item_type_description
FROM (SELECT i.item_id,
i.item_description,
it.item_type_description
FROM ITEMS i
JOIN ITEM_TYPES it ON it.item_type_id = i.item_type_id
ORDER BY i.item_description) t
ORDER BY t.item_type_description
This is bad because:
ORDER BY item_type_description, item_description
TOP (100) PERCENT is completely meaningless in recent versions of SQL Server, and it (along with the corresponding ORDER BY, in the case of a view definition or derived table) is ignored by the query processor.
You're correct that once upon a time, it could be used as a trick, but even then it wasn't reliable. Sadly, some of Microsoft's graphical tools put this meaningless clause in.
As for why this might appear in dynamic SQL, I have no idea. You're correct that there's no reason for it, and the result is the same without it (and again, in the case of a view definition or derived table, without both the TOP and ORDER BY clauses).