Хорошо, что если у нас возникнет желание прямо противоположное желанию в предыдущем опыте. Там, как вы помните, мы «страстно» хотели применения индекса представления, а оптимизатор всячески сопротивлялся этому нашему желанию. Что если теперь, по причинам которые не суть важны, мы не хотим использования в плане запроса индекса представления IX_vT1T2. Ну вот уверены мы, что это будет лишь во вред нашему решению. Такое наше желание реализуемо? Вполне! Но раз желание у нас изменилось на 180 градусов, то и хинт следует применить прямо противоположный предыдущему, а именно — EXPAND VIEWS. И вот тогда, и запрос
1 | SELECT [City],[CountryRegionCode] FROM vT1T2 OPTION (EXPAND VIEWS) |
и запрос
1 2 3 | SELECT [City],[CountryRegionCode] FROM T1 INNER JOIN T2 ON T1.[StateProvinceID]=T2.[StateProvinceID] OPTION (EXPAND VIEWS) |
оба выполнятся согласно «плану номер 2», а именно:
Думаю, читатели даже не встречавшиеся до этого момента с подсказкой EXPAND VIEWS уже сделали правильный вывод о ее предназначении. Раз она дает эффект прямо противоположный уже знакомому нам хинту NOEXPAND, то, наверно, она и намекает оптимизатору на прямо противоположное поведение. Раз NOEXPAND говорил «если есть хоть малейшая возможность, вот это представление — не разворачивать!», то EXPAND VIEWS, надо думать, сообщает оптимизатору «приступать к развороту представлений не анализируя возможное применение их индексов». Так же будет не лишним подметить разный «статус» этих двух подсказок: NOEXPAND является табличным хинтом (table hint), хотя именно для него лучше бы если б эта группа называлась «хинты представлений». То есть NOEXPAND указывается в запросе после конкретного представления и оказывает влияние только на это самое представление. В противоположность тому, EXPAND VIEWS является «хинтом запроса» (query hint), то есть указывается в конце запроса и оказывает влияние на все представления. Обратите внимание — не на все представления упомянутые в тексте запроса, а вообще на все. То есть на все существующие. Скажем последний из приведенных запросов вообще не упоминает в коде ни единого представления, а влияние обсуждаемого хинта несомненно. Такое «разнесение по статусам» сделано совершенно умышленно. Потому что, вы можете написать некоторый запрос «джойнящий» три представления — V1, V2, V3. И в конце этого запроса поставить ...OPTION (EXPAND VIEWS). Тогда все три сразу же будут развернуты, а их индексы даже не будут рассматриваться как кандидаты на включение в план. Но вы можете после, к примеру, V2 указать ...WITH (NOEXPAND), и тогда безусловной развертке подвергнутся лишь индексы V1 и V3 (впрочем, если эти два ссылаются в своем коде на представления V4, V5, V6,... то этих последних постигнет та же участь), а представление V2 будет рассмотрено оптимизатором как таблица, и все индексы этой «таблицы» будут учтены при переборе альтернатив плана исполнения. То есть — что нам дает «разностатусность» двух обсуждаемых хинтов? Правильно, гораздо более гранулированное управление оптимизатором (через подсказки) нам сей факт дает. Что есть хорошо и правильно. Итого:
Иными словами, назначение этого последнего хинта придать вам уверенности, что данный запрос будет черпать данные исключительно из базовых таблиц, и ни в коем случае из индексов представлений. А с чего вы можете вдруг захотеть быть уверенны в такой, на первый взгляд, второстепенной детали? Разве данные там и там не идентичны и не синхронизируются автоматически при любом их редактировании? Идентичны и синхронизируются, и все автоматически. А захотеть все это вы можете с того, что обращение к индексам представлений может, при определенных обстоятельствах, приводить к конфликтам блокировок (lock contention), кои элементарно избегаются обращением к исходным данным, то есть к базовым таблицам. Описание подобных обстоятельств, и вообще рассказ о влиянии индексированных представлений на блокировки, выходит за рамки текущего материала, однако и описание, и рассказ значатся в «шорт-листе» тем будущих статей автора. Как говорится — be tuned и оставайтесь с нами. 
Все факты изученные нами к текущей точке изложения материала, позволяют ответить на один из самых популярных вопросов на SQL-форумах связанных с индексированными представлениями, и звучит он примерно так: «я создал представление V1 и на нем кластерный индекс iV1. У меня есть запрос полностью/почти повторяющий код определения V1 (или, как вариант, запрос напрямую обращающийся к V1). Мне кажется, что применение в плане такого запроса индекса iV1 является вещью самоочевидной, однако оптимизатор его игнорирует — почему?» Ответ на этот вопрос состоит из пяти пунктов, причем первые три мы уже отметили выше:
- iV1 был учтен оптимизатором, но отброшен, так как выборка из базовой таблицы оценена как более «дешевая»;
- базовая таблица оценена как small — iV1 даже не рассматривается как кандидат;
- сам запрос оценен как trivial — iV1 даже не рассматривается как кандидат.
Пункт четвертый того же списка говорит нам о, еще пока, не полной идеальности оптимизатора когда последний пытается соотнести запрос и код представления, по которому был построен индекс. Если запрос подлежащий оптимизации отличается от тела представления хотя бы и в самых вторичных вещах, оптимизатор, с большой степенью вероятности, «промахивается». Пример: перепишем запрос для которого оптимизатор выбирал ранее индекс IX_vT1T2 вот таким образом:
1 2 | SELECT [City],[CountryRegionCode] FROM T1 INNER JOIN T2 ON CONVERT(int, T1.[StateProvinceID])=CONVERT(int, T2.[StateProvinceID]) |
Казалось бы — ну два полностью бессмысленных приведения типа (колонка StateProvinceID и без того имеет тип int), если отбросить дополнительное (и весьма не значительное) время на выполнение тех самых приведений, иных изменений быть не должно. Так вот — отнюдь! Оптимизатор не способен «сообразить», что индекс IX_vT1T2 вполне пригоден и для такого запроса и отказывается от услуг последнего:
Сравните этот план, и план «нормального» запроса, без всяких приведений типов. Так что да, «шаг влево, шаг вправо» и последствия налицо.
Однако самым популярным, и самым частым ответом на разбираемый нами вопрос является его пятый вариант — у вас просто «неправильная» редакция SQL Server! И хотя суть этого последнего ответа понятна в одну секунду, причины существования редакций «правильных» и «неправильных», а так же последствий деления оных на первые и вторые, заслуживают отдельного раздела статьи, к коему мы незамедлительно и переходим.
Индексы на представлениях в редакции не-Enterprise.
Напомню, что все изложенное в текущей статье к данной точке применимо к редакциям Enterprise/Datacenter/Developer. В рамках этой заключительной части будем ссылаться на все редакции не входящие в последний перечень как на редакцию Standard. То есть в эту вторую группу войдут сам Standard, Web, Workgroup и конечно же столь любимый простым IT-народом Express. Вот всю эту группу мы для краткости обозначим Standard-группой.
Стало быть — что меняется в отношении индексированных представлений для этой второй группы редакций Standard? Если говорить о построении таковых индексов — ничего не меняется. То есть строятся абсолютно такие же по структуре индексы, хотим кластерные, а хотим нет. Иными словами под-раздел статьи Структура и внутренне содержимое индексированных представлений применим к этой группе на 100%. Чего никак не скажешь о подразделе Применение оптимизатором запросов индексов на представлениях в планах исполнения. Вот этот второй к указанной группе редакций практически не применим, за исключением, быть может, пары абзацев. А дело тут в следующем.
В конце предыдущего раздела мы отметили, что оптимизатор определенно имеет проблемы в сопоставлении кода данного запроса и того кода, по которому был построен индекс на представлении. Однако если оба указанных кода [почти] совпадают, то самое сопоставление имеет место быть вполне успешным и индекс IX_vT1T2 применяется даже в запросе вообще не ссылающимся на объект по которому данный индекс был построен, то есть в запросе, где нет упоминания представления vT1T2. Так вот в группе редакций Standard даже не предпринимается попытка соотнести один код с другим. Да, увы, но в этих редакциях запрос
1 2 | SELECT [City],[CountryRegionCode] FROM T1 INNER JOIN T2 ON T1.[StateProvinceID]=T2.[StateProvinceID] |
не будет использовать индекс IX_vT1T2. То есть ни в коем случае. Но и это еще не все плохие новости, запрос куда как более очевидный
1 | SELECT [City],[CountryRegionCode] FROM vT1T2 |
тоже не будет использовать тот же индекс! 
Да, вот так. Индекс представления игнорируется даже там, где не применить его кажется просто невозможно. Вместо него будет использован вариант сканирования обеих таблиц. По счастью, пользователям группы редакций Standard хотя бы оставлен хинт NOEXPAND, и в запросе вот такого вида
1 | SELECT [City],[CountryRegionCode] FROM vT1T2 WITH (NOEXPAND) |
оптимизатор, наконец-то, обращает свой милостивый взор на многострадальный индекс IX_vT1T2. Однако к этому хинту, применение которого позволяет пользователям группы редакций Standard сказать «ну, хоть что-то/как-то!» заботливо приложена парочка «граблей». Во-первых, хинт применим только к представлению. Поэтому если такие пользователи желают обращаться именно к базовым таблицам T1/T2 индексу IX_vT1T2 они могут сказать «до свидания». Ведь NOEXPAND обязан применятся только к представлениям, не так ли? Но есть и во-вторых. Написали вы, допустим, хранимую процедуру с таким хинтом в одном из операторов SELECT кода процедуры, успешно пользуетесь индексом IX_vT1T2, и все отлично. Через некоторое время решаете что данный индекс используется весьма редко, места занимает много, так что разумно его удалить. И вы его «дропаете». Вам, разумеется, и в голову не придет одновременно поправить код той самой хранимой процедуры, и напрасно! Потому, как при ближайшем исполнении последней пользователи системы получат не резалт-сет, а вовсе даже ошибку 8171. А все из-за того, что как мы выяснили ранее, хинт NOEXPAND обязан применятся не только к представлениям (и ни в коем случае не к таблицам), но и непременно к их индексированному варианту. Справедливости ради заметим, что ошибка при попытке применить разбираемый хинт к таблице еще туда-сюда и как-то объяснима, хотя вполне можно было возвращать warning, игнорировать хинт, и «пилить дальше», то желание непременно прервать исполнение кода при использовании того же хинта с обычным (без индекса) представлением выглядит как едва ли не вредительство. Ну верни ты warning, зачем error-то лепить и все рушить?! 
Да и более того, такая жалоба давно была направлена в Microsoft Connect и признана сотрудниками MS вполне обоснованной. Только вот итоговый ответ был «ну... как руки дойдут — сделаем». То есть в неопределенном будущем у нас будет возможность сказать серверу возвращать не error 8171, а warning. Ну а до той поры все строчки кода с этой подсказкой будут ничем иным как «бомбами замедленного действия».
Есть и более тонкое различие в применении индексов на представлениях в Enterprise и Standard редакциях. Допустим такой код в Enterprise
1 | SELECT * FROM vT1T2 |
и такой в Standard
1 | SELECT * FROM vT1T2 with (NOEXPAND, index=[IX_vT1T2]) |
кажутся совершенно идентичными, как по финальному резалт-сету, так и по плану исполнения коим этот резалт-сет формируется. И действительно, они и будут идентичны в большинстве случаев. Но — не всегда. Дело в том, что Enterprise-вариант может пользоваться индексом представления, и будет это делать при каждом удобном случае. Но он не обязан это делать. И если обстоятельства существенно изменятся (существенно поменяется распределение данных, появятся более удобные индексы и т.д.) Enterprise без проблем откажется от индексированного представления и построит лучший план. А вот Standard вариант обязан соблюдать хинт указанный администратором/разработчиком и выбирает тот же индекс безусловно, а новые, потенциально лучшие планы, останутся им незамеченными. Ну то есть вы понимаете: динамическая подстройка «по месту» обыкновенно много, много лучше правил «выбитых в камне». Хотя, повторюсь, некоторый отрезок времени оба показанных фрагмента будут действительно абсолютно идентичны, вопрос лишь как долго тот отрезок продолжаться будет.
Итого, мы имеем два печальных факта связанных с применением (но не созданием) индексов на представлениях в Standard-редакциях:
и
Можно ли из этих фактов сделать вывод, что индексированные представления как таковые полностью бессмысленны в группе редакций Standard? Немалая часть MS SQL-специалистов приняла именно такую точку зрения, и свои аргументы у них есть. Один только NOEXPAND со своей всегда готовой сработать ошибкой 8171 придает этим аргументам ощутимую весомость. Автор же, тем не менее, принадлежит тому лагерю, который считает что применение индексированных представлений оправдано и в Standard-редакциях, но только в гораздо, гораздо более редких ситуациях, чем в случае Enterprise-редакций. Читатели статьи вольны принять и применять на практике свою собственную точку зрения по этому острому вопросу, главное что бы точка эта была взвешенной и осмысленной. Автор скромно надеется, что предоставленный в данной статье фактологический материал даст каждому читателю достаточно пищи для размышлений и выработки той самой единственно правильной (правильной она в любом случае будет индивидуально для вас и вашего окружения, разумеется) линии поведения при (не?)использовании индексированных представлений. Автор так же традиционно благодарит всех своих читателей за их внимание к блогу, письма и комментарии к статьям. Всем успешного применения индексов, в том числе и на представлениях, и оптимальных планов исполнения. Увидимся, пока!