Этот запрос сначала создает таблицу с нулевыми значениями для. магазинов и типов книг путем умножения таблиц stores и titles с помощью CROSS JOIN. Зате.м это множество объединяется с помощью UNION с таблице?! sales для создания композиции, которая включает записи о продажах для каждого магазина, а также нулевые значения для кажд01г комбинации магазии-тип книги . Далее

UNION

Оператор UNION позволяет объединять результаты двух запросов в одно результирующее множество. Мы при.менялн UNION всюду в этой главе для объединения результатов различных запросов. Оператор UNION не сложен, но существует несколько простых правил, о которых необходимо помнить при его применении:

О каждый запрос, используемый в UNION, должен иметь такое же количество и такой же список столбцов, как и другие запросы;

О столбцы, возвращаемые кажды.м операторо.м SELECT, либо должны иметь типы, совместимые с тинами соответствующих столбцов других SELECT, либо должны быть явно преобразованы;

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

Есть несколько предостережений и ограничений, о которых необходимо знать при использовании CUBE и ROLLUP:

О максимальное количество измерений для каждого оператора - десять;

О с обоими операторами нельзя использовать DISTINCT агрегаты;

О CUBE может создавать очень большие результирующие множества; это может занимать .много времени, возможны проблемы с программами, не спроектированными для их обработки.

HAVING

Как я сказал ранее, HAVING ограничивает записи, возвращаемые GROUP BY, так же как WHERE ограничивает записи, возвращаемые операторо.м SELECT. HAVING выполняется, после того как записи будут собраны из таблиц(ы), и поэтому его применение менее эффективно для большого количества записей, по сравнению с использованием WHERE. Фактически SQL автоматически неявно преобразует в WHERE предложения HAVING, если сочтет, что это будет эффективнее. Это означает, что планы выполнения для следующих запросов одинаковы:

SELECT titlejd FROM titles WHERE type business GROUP BY title id. type

SELECT tme 1d FROM titles

GROUP BY titlejd. type HAVING type=business.

Bo втором запросе HAVING не делает ничего, что не .могло бы сделать WHERE, так что SQL Server преобразует HAVING в WHERE во время выполнения, чтобы количество записей, обрабатываемых GROUP BY, было как можно меньше.

О в результате объединения столбцов, типы которых совместимы, но различны, получится столбец с более старшим типом из двух (например, в результате объединения smallint и float результирующий столбец будет типа float);

О названия столбцов, возвращаемых UNION, берутся из списка столбцов первого оператора SELECT;

О UNION ALL быстрее, чем UNION, так как не удаляет дублирующиеся значения. Чтобы удалить дублирующиеся значения, серверу, возможно, придется отсортировать данные - дорогое удовольствие, особенно в случае больших таблиц. Если вы не беспокоитесь о дубликатах, применяйте UNION ALL вместо UNION.

Вот пример простого UNION;

SELECT title id. type FROM titles WHERE type=business UNION ALL

SELECT titlejd. type

FROM titles

WHERE type=mod cook

title id type

BU1032 business

BUllll business

BU2075 business

BU7832 business

MC2222 [iiod cook

MC3021 [iiod cook

Этот запрос объединяет два отдельных сег.мента таблицы titles, разделенных по полю type. Так как запрос использует UNION ALL, элементы не сортируются. Как было показано ранее в этой главе, одна из особенностей UNION - возможность применять производные таблицы для создания виртуальных таблиц в запросе налету. Это удобно для создания справочных таблиц и других типов табличных конструкций, которые пет необходимости хранить постояшю. Вот пример;

SELECT title id AS Title ID. t.type AS Type, b.typecode AS TypeCode FROM titles t JOIN

(SELECT business AS type. 0 AS typecode UNION ALL

SELECT mod cook AS type. 1 AS typecode UNION ALL

SELECT popular comp AS type. 2 AS typecode UNION ALL

SELECT psychology AS type. 3 AS typecode UNION ALL

SELECT tradcook AS type. 4 AS typecode UNION ALL

SELECT UNDECIDED AS type, 5 AS typecode) b ON (t.type = b.type) ORDER BY TypeCode. Title ID

Title ID Type TypeCode

BU1032 business 0

Этот запрос задействует возможность Transact-SQL создавать результирующие множества без применения объектов базы данных для создания виртуальной таблицы из нескольких операторов SELECT, объединенных с помощью UNION. В данном случае мы используем UNION для преобразования в код поля type в таблице titles. Конечно, здесь лучше было бы выбрать оператор CASE - мы применили способ с виртуальными таблицами только для иллюстрации.

ORDER BY

Предложение ORDER BY служит для сортировки данных в результирующем множестве. Когда это возможно, оптимизатор будет использовать для этого индекс. Когда это невозможно или оптимизатор сочтет это не совсем оптимальным, для сортировки создается рабочая таблица (work table). В больших таблицах для этого может потребоваться некоторое время, а также в результате может закончиться место в базе данных tenjdb, если она недостаточно большая. Вот почему вы не должны упорядочивать результирующие множества без необходимости - это будет просто пустая трата ресурсов сервера. Вместе с тем, если вам необходим фиксированный порядок сортировки, обязательно включите предложение ORDER BY. Больше нельзя полагаться на то, что такие предложения, как GROUP BY и UNION, возвратят записи в необходимом порядке. Это поведение отличается от предыдущих версий SQL Server (6.5 и более ранних), так что будьте осторожны. Запросы, полагающиеся на определенный порядок сортировки записей, без применения ORDER BY могут работать не так, как ожидается.

На столбцы в предложении ORDER BY можно ссылаться тремя способами: по имени, по псевдониму столбца или по его номеру в результирующем множестве. Вот пример:

SELECT storjd AS Store, titlejd AS title, qty AS sales FROM sales s ORDER BY storjd. 2. sales

store title sales

6380 BU1032 5 6380 PS2091 3

Этот запрос упорядочивает результирующее .чиюжество с применепием всех трех методов, что обычно не очень хорошо в одном запросе. Синтаксически здесь все правильно, но делать что-то тремя различными способа.ми, когда это можно сделать одним, означает запутывать код без необходимости. Помните закон простоты (также известный как Бритва Окка.ма ) - не умножайте сущности без необходи.мости.

Это не значит, что вы не должны использовать каждую из этих техник в разное время. Возможность ссылаться на столбцы в результирующем множестве по номеру довольно удобна. (Однако упорядочивание по номерам столбцов в последнее время осуждается, так что желательно давать названия столбца.м и производить сортировку, ири.меняя псевдонимы столбцов.) Возможность использовать исевдони.мы столбцов избавляет от необходимости повторять сложные выражения в предложении ORDER BY, а возможность ссылаться иа столбцы напрямую позволяет проводить сортировку по столбцам, отсутствующи.м в списке SELECT.

Вы также можете задействовать подзапросы и константы в предложении ORDER BY, хотя необходимость в этом возникает довольно редко. Подзапросы, содержащиеся в ORDER BY, могут быть как связанными, так и автономиыхп!. Каждый столбец в списке ORDER BY может опционально сопровождаться ключевым словом DESC или ASC, чтобы сортировка проводилась по убыванию или по возрастанию (используется ио умолчанию):

SELECT st.stor name AS Store, t.type AS Type, SUM(qty) AS Sales FROM stores st JOIN sales s ON (st,storJd=s,storjd) JOIN titles t ON (s,tTtleJd=t,titlejd) GROUP BY st.stor name. t.type ORDER BY Store DESC. Type ASC

Store Type Sales

Вот несколько тонкостей, связанных с ORDER BY, о которых необходимо помнить.

О Нельзя применять ORDER BY в представлениях, производных таблицах и подзапросах без использования расширения ТОР п (за более подробной информацией обратитесь к разделу, касающемуся ТОР п, ранее в этой главе). Способ обойти это ограничение - включить предложение ТОР п с указанием большего количества записей, чем есть в таблице(ах).

О Нельзя сортировать да1пш1е по столбцам типа text, ntext или image.

О Если вы используете запрос с DISTINCT или объединяете два множества с помощью UNION, столбцы, указа1пн>1е в ORDER BY, должны также быть указаны в списке SELECT.

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

Заключение

в этой главе мы исследовали вездесущий и всемогущий оператор SELECT Transact-SQL. Чтобы стать искусным программистом на Transact-SQL, необходимо владеть им в совершенстве. Оператор SELECT мощный, но эта мощь достигается за счет сложности. Хотя операторы SELECT могут быть довольно короткими, в реальных приложениях они очень часто становятся чрезвычайно запутанными.

Представления

Где информация? Потеряна в данных. Где данные?

1отеря11Ы в этой идиотской баае данных!

Джо Селко

Представления (VIEW) -- это статические запросы, кото1)ые .можно исполызовать так, как если бы oin-i были таблица.чиь VIEW состоит из оператора SELECT, заранее скомпилированного с помощью ко.манды SQL CREATE VIEW, и работать с иред-ставления.чи! можно так же, как с таблица.ми. Столбцами VIEW могут быть столбцы таблиц, агрегатные значения, константы и выражения (вьщисляе.мые столбцы). Некоторые VIEW обновляемые, некоторые нет. Обновляемое представление или нет, зависит в основном от того, сможет ли SQL Server определить единственную запись, которую нужно т^лтшъ в таблице, являющейся основой представления. Все представления должны в коиечно.м счете ссылаться иа базовые таблицы или нетабличные выражения (выражения, для которых не требуются таблицы, - GETDATEO, например), хотя представления могут быть вложенными - это означает, что представление .\южет ссылаться на другие представления до тех пор, пока дерево зависимостей разрешается до базовых таблиц или нетабличных выражений.

Ограничения

Transact-SQL не поддерживает временные представления, хотя вы .можете создавать статические представления в tempdb и те.м са.мым добиться похожего эффекта. Также в представлениях нельзя использовать временные таблицы - разрешены ссылки только на другие представления или постоянные базовые таблицы. Как правило, в представления нельзя включать ORDER BY, так что следующий синтаксис - неправильный:

-- Неправильный синтаксис Transact-SQL CREATE VIEW myauthors AS SELECT * FROM authors ORDER BY au Iname

Это ограничение можно обойти. Если вам заранее известно количество записей, которое должен вернуть запрос, вы можете использовать расширение Transact-SQL ТОР п', чтобы в представление можно было включить ORDER BY, как здесь:

CREATE VIEW myauthors AS SELECT TOP 50 * FROM authors ORDER BY au lname

Указывайте достаточно большое количество записей, если вы не знаете их точное количество. ТОР п позволяет применить ORDER BY в представлениях, разрешая вам запросить больше записей, чем на самом деле имеется в таблице, при этом будут возвращены все записи. Запрос ниже показывает, что ORDER BY работает, когда мы делаем запрос к представлению:

SELECT au id. au lnaine. au fnanie FROM myauthors

Как и в случае храни.мых процедур, статус SET QUOTED IDENTIFIER и SET ANSI NULLS сохраняется с каждым представлением. Это означает, что установки сессии для этих опций игнорируются при выполнении запросов к представлению. Также это означает, что вы можете локализовать действие этих опций для конкретного представления, не затрагивая ничего больше.

Ограничения DML

Нельзя применять оператор UPDATE к представлению, если он воздействует более чем на одну таблицу за один раз. Если представление соединяет две или бо-

В позднем издании ТОР п заменено на ТОР 100 PERCENT. - Примеч. перев.

Представления схемы ANSI SQL

Замечательно, что SQL Server содержит множество представлений для доступа к системной информации. Эти объекты предоставляют совместимый с ANSI SQL-92 способ получения метаданных, то есть запросы к серверу для получения инфор.\1ации системного уровня. Вы должны использовать эти объекты вместо прямых запросов к системны.м таблицам по двум причинам:

1. Эти представления определены в спецификации ANSI SQL-92, поэтому они будут функционировать одинаково в разных СУБД.

2. Вы можете работать с ними одинаково в разных версиях SQL Server, даже если изменятся системные таблицы, на которых они основаны.

В табл. 7.1 приведены совместимые с SQL-92 представления, которые предлагает SQL Server.

Таблица 7.1. Представления схемы ANSI SQL-92 в SQL Server Название представления

CHECK CONSTRAINTS COLUMN DOMAlN USAGE COLUMN PRIVILEGES COLUMNS

CONSTRAINT COLUMN USAGE CONSTRAINT TABLE USAGE DOMAIN CONSTRAINTS DOMAINS

KEY COLUMN USAGE

REFERENTIAL CONSTRAINTS

SCHEMATA

TABLE CONSTRAINTS

TABLES

VIEW COLUMN USAGE

VIEW TABLE USAGE

VIEWS

лее таблицы, UPDATE должен воздеГютвовать только на одну из них. Аиа;10гично, оператор INSERT должен модифицировать только одну таблицу за один раз, если представление использует несколько таблиц. Это означает, что значения .могут быть указаны только для одной таблицы - столбцы в другой таблице(ах) должны либо иметь DEFAULT-ограннчеиия, либо допускать хранение неопределенных значений, либо еще каки.м-то образо.м быть опцнональныхщ. Оператор DELETE можно ири.менять только с представленияхи!, основанными на одной таблице, ~ его нельзя задействовать с хиюготабличиыми представлениями.

Получение исходного кода представления

Если представление было создано без указания опции WITH ENCRYPTION, вы можете задействовать sp helptext для получения его исходного кода. Вы также можете просматривать и модифицировать исходный код представления в Enterprise Manager и во множестве инструментов администрирования, использующих SQL-DMO. Вот код, возвращающий исходный код системного представления sysloglns: USE master

exec sp helptext syslogins Text

CREATE VIEW syslogins AS SELECT suid = convert(smallint. suser id(name)). Sid = convert(varbinary(85). sid). status = convert(smallint. 8 +

CASE WHEN (xstatus & 2)=0 THEN 1 ELSE 2 END).

createdate = convert(datetime. xdatel).

updatedate = convert(datetime. xdate2). accdate = convert(datetime. xdatel). totcpu = convert(int. 0). totio = convert(int. 0). spacelimit = convertOnt. 0). , timelimit - convertdnt. 0). resultlimit = convertdnt. 0).

name = convert(varchar(30). CASE WHEN (xstatus&4)=0 THEN name

ELSE suser name(suser id(name)) END), dbname = convert(sysname. db name(dbid)). password = convert(sysname. password), language - convert(sysname. language).

denylogin = convert(int. CASE WHEN (xstatus&l)=l THEN 1 ELSE 0 END), hasaccess = convertdnt. CASE WHEN (xstatus&2)=2 THEN 1 ELSE 0 END), isntname = convertdnt. CASE WHEN (xstatus&4)=4 THEN 1 ELSE 0 END), isntgroup = convert(int. CASE WHEN (xstatus&12)=4 THEN 1 ELSE 0 END). isntuser-= convertdnt. CASE WHEN (xstatus&12)=12 THEN 1 ELSE 0 END), sysadmin = convertdnt. CASE WHEN (xstatus&16)=16 THEN 1 ELSE 0 END), securityadmin = convertdnt. CASE WHEN (xstatus&32)=32 THEN 1 ELSE 0 END), serveradmin = convertdnt. CASE WHEN (xstatus&64)-64 THEN 1 ELSE 0 END), setupadmm = convertdnt. CASE WHEN (xstatus&128)=128 THEN 1 ELSE 0 END), processadmin = convertdnt. CASE WHEN (xstatus&256)=256 THEN 1 ELSE 0 END), diskadmin = convertdnt. CASE WHEN (xstatus&512)=512 THEN 1 ELSE 0 END), dbcreator = convertdnt. CASE WHEN (xstatus&1024)=1024 THEN 1 ELSE 0 END), loginname = convert(sysname. name) FROM sysxlogins WHERE srvid IS NULL

Заметьте, что вы должны ссылаться на эти объекты, используя схему базы данных INFORMATION SCHEMA. На языке SQL Server схема (schema) и владелец (owner) - синонимы. Это означает, что вы должны применить

SELECT * FROM INFORMATIONJCHEMA.TABLES

SELECT * FROM TABLES