Неожиданный синтаксис псевдонима SQL Server 'AS'

select c from (select 1 union all select 1) as d(c)

такой же как

select c from (select 1 as c union all select 1) as d

В первом запросе вы не назвали столбцы в своем подзапросе, но назвали их вне подзапроса,

Во втором запросе вы называете столбцы внутри подзапроса.

Если вы попробуете это так (без указания столбцов в подзапросе)

select c from (select 1 union all select 1) as d

Вы получите следующую ошибку

No column name was specified for column 1 of 'd'

Это тоже есть в Документация

Что касается использования, то кому-то нравится писать первым способом, кому-то вторым, кому как больше нравится. Все то же самое

У вас уже были комментарии, которые указывают на документацию о том, как работают производные таблицы, но не отвечают на ваш вопрос о полезных вариантах использования этой функции.

Лично я нахожу эту функциональность полезной всякий раз, когда я хочу создать набор адресуемых значений, которые будут широко использоваться в вашем выражении, или когда я хочу дублировать строки по какой-либо причине.

Примером адресных значений может быть гораздо более сложная версия следующего, в которой вычисленные значения в производной таблице v могут использоваться много раз с помощью более разумных имен, а не повторных вычислений, за которыми будет трудно следовать:

select p.ProductName
      ,p.PackPricePlusVAT - v.PackCost as GrossRevenue
      ,etc
from dbo.Products as p
    cross apply(values(p.UnitsPerPack * p.UnitCost
                      ,p.UnitPrice * p.UnitsPerPack * 1.2
                      ,etc
                      )
               ) as v(PackCost
                     ,PackPricePlusVAT
                     ,etc
                     )

и примером возможности дублирования строк может быть создание отчета об исключениях для использования при проверке данных, который будет выводить одну строку для каждого условия DataError, которому удовлетворяет строка dbo.Product:

select p.ProductName
      ,e.DataError
from dbo.Products as p
    cross apply(values('Missing Units Per Pack'
                      ,case when p.SoldInPacks = 1 and isnull(p.UnitsPerPack,0) < 1 then 1 end
                      )
                     ,('Unusual Price'
                      ,case when p.Price > (p.UnitsPerPack * p.UnitCost) * 2 then 1 end
                      )
                     ,(etc)
               ) as e(DataError
                     ,ErrorFlag
                     )
where e.ErrorFlag = 1

Если вы понимаете, что делают эти два скрипта, вы должны найти множество примеров того, как возможность генерировать дополнительные значения или дополнительные строки данных была бы очень полезной.

Наблюдение: использование конструктора таблиц values не дает вам возможности называть столбцы, что делает необходимым использовать имена столбцов после псевдонима таблицы:

select * from
(values
     (1,2) -- can't give a column name here
    ,(3,4)
) as tableName(column1,column2) -- gotta do it here