> С рекурсией оно конечно выглядит красиво, но без неё всегда можно обойтись.

Прямо-таки всегда? Дано:
Create table T (ID int primary key, Name varchar(50), Parent_ID int)
Смысл, думаю, понятен. Таблица для хранения дерева.
Нужно обеспечить удаление всех дочерних, внучатых, правнучатых... и т.д. записей при удалении узлов дерева.

Вариант 1. Заранее известно, что глубина дерева не превышает ограничения по вложенности рекурсивных триггеров (16 для 7.0)
Тогда, IMHO, самое правильное решение написать рекурсивный триггер:
create trigger T_OnDel on T
as
if exists(select T.ID from T, deleted D where T.Parent_ID=D.ID)
Delete from T
from T, deleted D where T.Parent_ID=D.ID
Это и изящно, и вообще...

Вариант 2. Заранее известно, что глубина дерева не превышает ограничения по вложенности хранимых процедур (32 для 7.0).
Триггер делается нерекурсивным. Но он вызывает хранимую процедуру, которая рекурсивна (текст писать не буду - интуитивно ясно).

Вариант 3. Глубина дерева вообще не ограничена. Увы, алгоритм раскраски дерева по своей природе рекурсивен. Рекурсия организуется с помощью стека. Выкручиваться, естественно придется без рекурсивных возможностей инструментария, поскольку уперлись в ограничения MS SQL Server. Делается все на циклах, но геморройно, поскольку нужно что-то вроде стека организовать самому (временная таблица).
Должен оговориться, что для варианта 3 есть нерекурсивный по природе вариант на голом цикле. На первом шаге цикла во временную таблицу переписываются ID и Parent_ID удаляемых записей (которую подали с клиента). На каждом следующем шаге цикла в эту таблицу переписываются ID и Parent_ID записей, которых пока что нет в этой таблице, но Paraent_ID которых in (select ID from #TemTable). И так до тех пор, пока переписывать будет нечего. В #TempTable получаем набор швутеификаторов всех записей, которые необходимо удалить - выдаем одну-единственную команду Delete - и всё. Да, алгоритм не рекурсивный. Но он еще и не оптимальный. Поскольку на каждом шаге цикла в перечень просматриваемых записей попадают те, которые уже раньше просмотрены (ID записей из верхних уровней дерева), и повторно просматривать которые нет смысла - а это дополнительный тормоз.

Честно говоря, не знаю как именно в SQL Server рекурсивность сказывается на быстродействии. Во всех остальных инструментариях отрицательно. Это связано с необходимостью сохранения в стеке всех локальных переменных. Возможно, в SQL ситуация не просто аналогичная, но соображения alexeyvg являются дополнительным и более важным фактором, сказывающимся на быстродействии.
Тем не менее, не могу согласиться с тем, что рекусия - самый неизящный стиль. IMHO, как раз наоборот. Посмотри на текст рекурсивного триггера (в 2 строчки) и сравни с текстом аналогичного нерекурсивного скрипта. В этом плане я скорее соглашусь со мнением SergSuper.
По поводу того, что рекурсия - это наследие процедурного мышления (в смысле "пережиток") тоже не могу согласиться. Современный Transact-SQL содержит операторы Exec (аналог Call), If, While, механизмы пошаговых вычислений, локальные переменные. Это все атрибуты процедурного программирования. И появились они не в первых версиях T-SQL с последующим отмиранием, а наоборот. Даже термин "stored procedure" говорит сам за себя (а не "stored query"). И это не пережиток, а веление времени. Грани между процедурным языком и языком запросов постепенно стираются. В версиях 7.0 и 2000 SQL-сервера получило развитие обращение объектам БД в контексте объектной модели (родитель.объект-свойство). Я не удивлюсь, если в будущем увижу диалекты T-SQL, поддерживающие множественное наследие и перегрузку операций. IMHO, поезд катится не просто к процедурному, а еще и к объектно-ориентированному. Это неизбежный процесс, если речь идет о переносе бизнес-логики на сервер. Отказавшись от процедурного мышления, далеко не все можно реализовать операциями базовой реляционной алгебры.
Также не могу согласиться, что реляционное мышление - это пик человеческой мысли. Графы, деревья продолжают использоваться вместе с табличными представлениями и отмирать не собираются. Когда-то на MUMPS (иерархическая СУБД) программисты ломали голову над тем, как выполнять агрегатные функции и произвольно задаваемые объединения по таблично-подобным данным. Теперь более молодое поколение ломает голову над тем, как с помощью табличных структур представить граф или дерево так, чтобы с ним было удобно работать методами реляционной алгебры. Я уверен, что следующее поколение сможет работать и стем, и с другим с одинаковой легкостью, получив настоящий объектно-ориентированный инструментарий.