Создание индекса инструкцией Create Table. Создание индекса

Создание индексов

Оператор create index создает индекс для конкретной таблицы. Общая форма этого оператора:

create [unique] [clustered |nonclustered] index index_name on table_name {columnl [asc i desc] , ...) [ include ( column_name [,...])] [with

[fillfactor=n]

[[, ] pad_index = [on i off}]

[[, ] drop_existing = [on | off}]

[[, ] sort_in_tempdb = [on | off}]

[[, ] ignore_dup_key = (on | off}]

[[, ] allow_row_locks = {on i off}]

[[, ] allow_page_locks = [on | off}]

[[, ] statistics_norecompute = [on i off}]

[[, ] online = {on I off}]] [on file_group | «default»]

Здесь index_name задает имя создаваемого индекса. Индекс может быть создан для одного или более столбцов одной таблицы (tabie_name). coiumni - имя столбца, для которого создается индекс. Как вы можете видеть в форме оператора create index, вы можете создавать индекс для нескольких столбцов таблицы. Database Engine также поддерживает индексы и для представлений. Подобные представления, называемые индексированными представлениями, обсуждаются в следующей главе.

Индекс может быть простым или составным. Простой индекс имеет один столбец, в то время как составной индекс создан более чем для одного столбца. Каждый составной индекс имеет некоторые ограничения, связанные с его длиной и количеством столбцов. Максимальный размер индекса- 900 байтов, при этом индекс может содержать не более 16 столбцов.

Опция unique указывает, что каждое значение данных может появляться только один раз в индексированном столбце. Для уникального составного индекса комбинация значений данных во всех столбцах каждой строки должна быть уникальной. Если unique не задано, допустимы дубликаты в индексируемом столбце (столбцах).

Опция clustered задает кластеризованный индекс. Опция nonclustered (значение по умолчанию) указывает, что индекс не изменяет порядок строк в таблице. Database Engine допускает максимум 249 некластеризованных индексов для одной таблицы.

Database Engine имеет улучшенную поддержку индексов, которые упорядочены по убыванию значений столбца. Опция asc после имени столбца указывает, что индекс создается с возрастающим порядком значений столбца, тогда как desc задает убывающий порядок. Это дает вам больше гибкости в плане использования индексов. Убывающие индексы должны быть созданы, когда вы создаете составной индекс для столбцов, которые имеют противоположные направления сортировки.

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

Опция FILLFACTOR=n определяет процент заполнения каждой индексной страницы во время создания индекса. Вы можете задать значение fillfactor в диапазоне от 1 до 100. Если значение л установлено в 100, то каждая индексная страница будет заполнена на 100%, т. е. существующая страница листа индекса так же, как и страница, не относящаяся к листу, не будет иметь места для добавления новых строк. По этой причине такое значение рекомендуется использовать только для статических таблиц. Значение по умолчанию 0 также указывает на то, что страница листа индекса будет заполнена полностью и каждая промежуточная страница содержит место для одной записи.

Если вы установите значение опции fillfactor в значение между 1 и 99, то новая индексная структура будет создана со страницами листьев, которые не будут заполнены полностью. Чем больше значение fillfactor, тем меньше объем памяти, который остается свободным на индексной странице. Например, установка опции fillfactor в значение 60 означает, что 40% на каждой странице листа индекса остается свободным для дальнейшего добавления строк индекса. (Индексная строка будет добавлена, когда вы выполняете либо оператор insert, либо оператор update.) По этой причине значение 60 будет разумным решением для таблиц с довольно частым изменением данных. Для всех значений опции fillfactor между 1 и 99 все промежуточные страницы, не относящиеся к листьям, будут содержать свободное место для одной записи.

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

Опция drop_existing позволяет увеличить производительность при пересоздании кластеризованного индекса для таблицы, которая также имеет некластеризованный индекс. Подробности «Пересоздание индекса» далее В этом разделе.

Опция sort_in_tempdb служит для помещения в системную базу данных tempdb промежуточных данных сортировки, используемой при создании индекса. Это может дать преимущество, если база данных tempdb располагается на дисковом носителе, отличном от того, где находятся сами данные. (Опция dropexisting обсуждается в разд. «Пересоздание индекса» далее В этом разделе.)

Опция ignoredupkey указывает системе, что она должна игнорировать попытки добавления дубликатов значений в индексный столбец (столбцы). Эта опция должна быть использована только для того, чтобы исключить завершение длинной транзакции в случае, когда оператор insert добавляет дубликаты данных в индексируемый столбец (столбцы). Если активирована эта опция, и оператор insert пытается добавить строки, которые нарушат уникальность индекса, то система базы данных вернет предупреждение вместо того, чтобы выдавать ошибку для всего оператора. Database Engine не добавит строки, которые пытаются создать дубликаты ключевых значений, он просто проигнорирует такие строки и добавит все оставшиеся. (Если такая опция не установлена, то все операторы будут отменены.)

Опция allowrowlocks задает, что система использует блокировку строк, когда активирована эта опция (установлена в on). Аналогично, опция allowpagelocks задает, что система использует блокировку страниц, когда эта опция установлена в on. (Описание блокировок страниц и строк.)

Опция statistics_norecompute указывает, что статистика по указанному индексу не должна вычисляться заново автоматически. Опция on создает указанный индекс либо для файловой группы по умолчанию («default»), либо для указанной файловой группы (filegroup).

Если вы активируете опцию online, то можете создавать, пересоздавать или удалять индекс в диалоговом режиме. Эта опция допускает параллельные модификации индексируемой таблицы или данных кластеризованного индекса, а также ассоциированных индексов в процессе выполнения изменений индекса. Например, в то время как пересоздается кластеризованный индекс, вы можете продолжать выполнение изменений данных в таблице и выполнять запросы к этим данным. (Это означает, что исключительные блокировки данных соответствующей таблицы не используются в процессе пересоздания индекса).

В примере 10.1 показано создание некластеризованного индекса.

Создание индексов

В примере 10.2 показано создание уникального составного индекса.

Создание индексов

Создание уникального индекса для столбца, который уже содержит дубликаты значений, невозможно. Создание уникального индекса возможно, если каждое существующее значение данных (в том числе значение null) появляется лишь один раз. Точно так же любая попытка добавить или изменить существующее значение данных в столбец, включенный в существующий уникальный индекс, будет отвергнуто системой в случае дублирования значения.

serversql.ru

Создание индекса для одного поля — Мегаобучалка

Создание и использование индекса

Для увеличения производительности

Если необходимо постоянно выполнять поиск в таблице или сортировать записи по определенному полю, можно ускорить эти операции, создав индекс для этого поля. В таблицах Microsoft Office Access 2007 индексы используются точно так же, как предметные или именные указатели в книгах. Чтобы найти данные, Office Access 2007 проверяет местоположение этих данных по индексу. В некоторых случаях, например при определении первичного ключа, Access формирует индекс автоматически. В других случаях самому пользователю может потребоваться создание индекса.

Общие сведения об индексах.Индексы способствуют более быстрым поиску и сортировке записей в Microsoft Office Access 2007. В индексе хранится местоположение записей на основе одного или нескольких полей, которые были выбраны для индексирования. После того как Access получает сведения о местоположении данных, эти данные могут загружаться путем перемещения в нужное местоположение. Благодаря этому использование индекса гораздо эффективнее просмотра всех записей для поиска необходимых данных.

Выбор полей для индексирования.Можно создавать индексы, основанные на одном или нескольких полях. В основном требуется индексировать поля, в которых часто осуществляется поиск, сортируемые поля и поля, объединенные с полями в других таблицах, что часто используется в запросах по нескольким таблицам. Индексы ускоряют поиск и построение запросов, однако они могут привести и к снижению производительности при добавлении или обновлении данных. При вводе данных в таблицу, содержащую один или несколько индексов, приложение Access должно обновлять индексы при каждом добавлении или изменении записи. Добавление записей с помощью запроса на добавление или с помощью импортирования записей также будет происходить более медленно, если таблица-получатель содержит индексы.

Примечание. Первичный ключ таблицы индексируется автоматически. Индексирование полей с типом данных «Объект OLE» и «Вложение» невозможно. Индексировать другие поля следует в тех случаях, когда одновременно выполняются следующие условия:

- типом данных поля является «Текстовый», «Поле Memo», «Числовой», «Дата/время», «Счетчик», «Денежный», «Логический» или «Гиперссылка»;

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

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

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

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

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

В составной индекс можно включить до 10 полей.

Создание индекса

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

Параметр свойства «Индексированное поле»	Значение
Нет	Не создавать индекс для этого поля (или удалить существующий индекс)
Да (Допускаются совпадения)	Создать индекс для этого поля
Да (Совпадения не допускаются)	Создать уникальный индекс для этого поля

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

Создание индекса для одного поля

1. В области переходов щелкните правой кнопкой мыши название таблицы, в которой необходимо создать индекс, затем в контекстном меню выберите Конструктор.

2. Щелкните Имя поля для поля, которое следует индексировать.

3. В разделе Свойства поля откройте вкладку Общие.

4. В свойстве Индексированное поле щелкните значение Да (Допускаются совпадения), если следует разрешить повторяющиеся значения, или значение Да (Совпадения не допускаются), чтобы создать уникальный индекс.

5. Чтобы сохранить изменения, щелкните Сохранить на панели быстрого доступа или нажмите сочетание клавиш CTRL+S.

megaobuchalka.ru

Создание и использование индекса для увеличения производительности

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

В этой статье дается описание индексов; рассматривается, какие поля следует индексировать; описывается создание, удаление и изменение индексов. Кроме того, в этой статье объясняется, в каких случаях приложение Access создает индексы автоматически.

В этой статье

Что такое индекс?

Выбор полей для индексирования

Создание индекса

Удаление индекса

Просмотр и редактирование индексов

Автоматическое создание индексов

Примечание: Методы, описанные в данной статье, нельзя использовать для создания индекса для таблицы веб-базы данных. Производительность веб-базы данных зависит от нескольких факторов, например производительности сервера SharePoint, на котором она размещена.

Что такое индекс?

Индексы обеспечивают поиск и сортировку записей в Access. В индексе хранится местоположение записей на основе одного или нескольких полей, которые были выбраны для индексирования. После того как Access получает сведения о позиции в индексе, он может извлечь данные путем перемещения непосредственно к нужной позиции. Благодаря этому использование индекса гораздо эффективнее просмотра всех записей для поиска необходимых данных.

Выбор полей для индексирования

Вы можете создавать индексы, основанные на одном или нескольких полях. В основном требуется индексировать поля, в которых часто осуществляется поиск, сортируемые поля и поля, объединенные с полями в других таблицах, что часто используется в запросах по нескольким таблицам. Индексы ускоряют поиск и выполнение запросов, однако они могут привести к снижению производительности при добавлении или обновлении данных. Каждый раз, когда вы добавляете или изменяете запись в таблице, содержащей один или несколько индексов, Access приходится обновлять индексы. Добавление записей с помощью запроса на добавление или с помощью импортирования записей также, скорее всего, будет происходить медленнее, если таблица-получатель содержит индексы.

Примечание: Первичный ключ таблицы индексируется автоматически.

Индексировать поля с типом данных "Объект OLE", "Вычисляемый" или "Вложение" невозможно. Индексировать другие поля следует в тех случаях, когда выполняются все указанные ниже условия.

Типом данных поля является "Короткий текст" ("Текст" в Access 2007 и 2010), "Длинный текст" ("Поле MEMO" в Access 2007 и 2010), "Число", "Дата/время", "Счетчик", "Денежный", "Логический" или "Гиперссылка".
Предполагается поиск значений в поле.
Предполагается сортировка значений в поле.
Предполагается сохранение большого числа различных значений в поле. Если поле содержит много одинаковых значений, то применение индекса может не дать значительного ускорения выполнения запросов.

Составные индексы

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

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

В составной индекс можно включить до 10 полей.

Создание индекса

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

Параметр свойства "Индексированное поле"	Значение
Нет	Не создавать индекс для этого поля (или удалить существующий индекс)
Да (допускаются совпадения)	Создать индекс для этого поля
Да (совпадения не допускаются)	Создать уникальный индекс для этого поля

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

Создание индекса для одного поля

В области навигации щелкните правой кнопкой мыши имя таблицы, в которой необходимо создать индекс, и выберите в контекстном меню пункт Конструктор.
Щелкните пункт Имя поля для поля, которое следует индексировать.
В разделе Свойства поля откройте вкладку Общие.
В свойстве Индексированное выберите значение Да (допускаются совпадения), если следует разрешить повторяющиеся значения, или значение Да (совпадения не допускаются), чтобы создать уникальный индекс.
Чтобы сохранить изменения, щелкните элемент Сохранить на панели быстрого доступа или нажмите клавиши CTRL+S.

Создание составного индекса

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

В области навигации щелкните правой кнопкой мыши имя таблицы, в которой необходимо создать индекс, и выберите в контекстном меню пункт Конструктор.
На вкладке Конструктор в группе Показать или скрыть щелкните пункт Индексы.

Появится окно "Индексы". Измените размеры этого окна, чтобы отображались пустые строки и свойства индекса.
В первой пустой строке столбца Индекс введите имя индекса. Для индекса можно использовать либо имя одного из индексируемых полей, либо другое подходящее имя.
В столбце Имя поля щелкните стрелку, затем щелкните первое поле, которое следует использовать в индексе.
Следующую строку столбца Индекс оставьте пустой, затем в столбце Имя поля укажите второе индексируемое поле. Повторите этот шаг для всех полей, которые необходимо включить в индекс.
Чтобы изменить порядок сортировки значений полей, в столбце Порядок сортировки окна "Индексы" щелкните пункт По возрастанию или По убыванию. По умолчанию выполняется сортировка по возрастанию.

В разделе Свойства индекса окна Индексы укажите свойства индекса для строки в столбце Имя индекса, содержащем имя индекса. Задайте свойства в соответствии с таблицей ниже.

Подпись	Значение
Первичный	Если Да, то индекс является первичным ключом.
Уникальный	Если Да, то каждое индексируемое значение должно быть уникальным.
Пропуск пустых полей	Если Да, то записи с пустыми значениями в индексируемых полях будут исключены из индекса.

Чтобы сохранить изменения, нажмите кнопку Сохранить на панели быстрого доступа или нажмите клавиши CTRL+S.
Закройте окно "Индексы".

Удаление индекса

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

В области навигации щелкните правой кнопкой мыши имя таблицы, для которой необходимо удалить индекс, и выберите в контекстном меню пункт Конструктор.
На вкладке Конструктор в группе Показать или скрыть щелкните пункт Индексы.

Появится окно "Индексы". Измените размеры этого окна, чтобы отображались пустые строки и свойства индекса.
В окне "Индексы" выделите строки, содержащие индекс, который следует удалить, и нажмите клавишу DELETE.
Чтобы сохранить изменения, нажмите кнопку Сохранить на панели быстрого доступа или нажмите клавиши CTRL+S.
Закройте окно Индексы

Просмотр или редактирование индексов

Чтобы оценить влияние индексов на производительность или убедиться, что необходимые поля проиндексированы, просмотрите индексы в таблице.

В области навигации щелкните правой кнопкой мыши имя таблицы, индекс которой вы хотите изменить, и выберите в контекстном меню пункт Конструктор.
На вкладке Конструктор в группе Показать или скрыть щелкните пункт Индексы.

Появится окно "Индексы". Измените размеры этого окна, чтобы отображались пустые строки и свойства индекса.
Просмотрите или измените индексы и свойства индексов в соответствии со своими задачами.
Чтобы сохранить изменения, нажмите кнопку Сохранить на панели быстрого доступа или нажмите клавиши CTRL+S.
Закройте окно Индексы

Автоматическое создание индексов

В некоторых случаях индексы создаются автоматически. Например, индексы создаются для любых полей, которые определяются пользователем в качестве первичного ключа таблицы.

Для автоматического создания индекса также можно использовать параметр Автоиндекс при импорте и создании в диалоговом окне Параметры Access. Access автоматически проиндексирует все поля, имена которых начинаются с указанных в поле Автоиндекс при импорте и создании знаков или заканчиваются ими, например ID, ключ, код или число. Чтобы просмотреть или изменить текущие параметры, сделайте следующее:

Выберите Файл > Параметры. Обратите внимание: если вы используете Access 2007, нажмите кнопку Microsoft Office и выберите Параметры Access.
Щелкните Конструкторы объектов, а затем в разделе Конструктор таблиц добавьте, измените или удалите значения в поле Автоиндекс при импорте и создании. Для разделения значений используйте точку с запятой (;).

Примечание: Если имя поля начинается со значения, указанного в списке, или заканчивается им, поле будет автоматически проиндексировано.
Нажмите кнопку ОК.

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

К началу страницы

support.office.com

Создание индекса инструкцией Create Table

При создании таблицы инструкцией Create Table можно создать индекс по отдельным столбцам или составной индекс по двум или больше столбцам. Для этого используется ключевое слово – Constraint (с него начинается определение индекса).

В следующем примере показано, как создать таблицу с индексом по трем столбцам.

Create Table Служащие ([Имя служащего] Text (25),[Фамилия служащего] Text (25), [Дата рождения] DateTime, Constraint СлужащиеИндекс Unique ([Имя служащего],[Фамилия 30а30еляя30го],[Дата рождения] )).

Чтобы проиндексировать только один столбец, ключевое слово Constraint нужно поместить в определение этого столбца. Например, для создания индекса только по столбцу Дата рождения можно выполнить следующую инструкцию:

Create Table Служащие ([Имя служащего] Text (25), [Фамилия служащего] Text (25), [Дата рождения] DateTime Constraint СлужащиеИндекс Primary).

Различие между синтаксисами создания составного и простого индекс в том, что для простого индекса ключевое слово Constraint, которым начинается определение индекса, не отделяется от последнего столбца запятой, а вводится сразу после объявления типа данных индексируемого столбца.

Создание индекса инструкцией Create Index

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

Create Unique Index Index1 On Служащие ([Дата рождения])

Создание индекса инструкцией Alter Table

Добавить индекс к существующей таблице можно, используя инструкцию Alter Table с предложением Add Constraint.

Например, следующая инструкция добавляет индекс типа первичный ключ по столбцу Ном_служащего таблицы Служащие:

Alter Table Служащие Add Constraint Index1 Primary (Ном_Служащего)

Инструкцией Alter Table в таблице можно создать и составной индекс:

Alter Table Служащие Add Constraint ИмяИндекса Unique ([Имя служащего], [Фамилия служащего], [Дата рождения])

Для удаления индекса используется ключевое слово Drop с добавлением опции Constraint в инструкции Alter Table.

Например, следующая инструкция удаляет первичный ключ по столбцу Ном_служащего таблицы Служащие, созданный ранее.

Alter Table Служащие Drop Constraint Index1.

Это же можно выполнить инструкцией

Drop Index Index1 On Служащие.

Инструкции манипулирования данными (dml)

В инструкции манипулирования данными входят три операции: операция удаления записей — ей соответствует инструкция DELETE, операция добавления или ввода новых записей — ей соответствует инструкция INSERT и операция изменения (обновления записей) — ей соответствует инструкция UPDATE.

Все инструкции манипулирования данными позволяют изменить данные только в одной таблице. Рассмотрим каждую инструкцию подробнее.

Инструкция ввода данных INSERT имеет следующий синтаксис:

INSERT INTO имя_таблицы [(<список столбцов>)]

VALUES (<список значений>)

Подобный синтаксис позволяет ввести только одну строку в таблицу.

Insert Into Студент ([Фамилия],[Имя],[Дата рождения])

Values (“Петров”,”Иван”, 23/3/80)

Задание списка столбцов необязательно тогда, когда мы вводим строку с заданием значений всех столбцов.

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

Insert Into таблица_куда [In внешняя_база_данных]

Select [Источник] поле1, поле2,…

From выражение

Where условие

Перед загрузкой выполняется подзапрос Select, который и формирует выборку для добавления. Фраза Select определяет структуру данных источника передаваемых записей – имена таблицы и полей, содержащих исходные данные для загрузки в таблицу_куда. From позволяет указать имена исходных таблиц, участвующих в формировании выборки, а фраза Where – задает условия выполнения подзапроса. Структура данных выборки должна соответствовать структуре данных таблице, в которую производится добавление.

Добавление записей возможно и во внешнюю базу данных, для которой указывается полностью ее имя (диск, каталог, имя, расширение).

Insert Into Студент Select [Студент_заочник]*

From [Студент_заочник]

Все записи таблицы Студент_заочник в полном составе будут добавлены в таблицу Студент, если структуры таблиц совпадают.

Insert Into Студент Select [Студент_заочник].*

From [Студент_заочник]

Where [Дата рождения] >= #01/01/80#

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

Когда предложение INSERT INTO включается в инструкцию, оно предшествует слову Select.

Чтобы ввести записи в таблицу другой базы данных, нужно включить в инструкцию оператор IN.

Прежде чем выполнить запрос на добавление, полезно выяснять, какие записи будут добавлены к адресату. Для этого можно запустить запрос типа SELECT с теми же критериями выбора и просмотреть его результаты.

Запрос на добавление копирует в таблицу – адресат записи из одной или нескольких таблиц. На таблицы, которые содержат требуемые записи, при выполнении запроса этого типа никакого воздействия не оказывается.

Удаление записей в запросе. Записи из одной или больше таблиц, перечисленных в предложении FROM, которые удовлетворяют условиям предложения WHERE, можно удалить с помощью запроса на удаление записей. Синтаксис его следующий:

DELETE [таблица.*]

FROM таблицы

WHERE критерии

Если условия отбора не задаются, то из таблицы удаляются все строки, однако это не значит, что удаляется вся таблица. Исходная таблица остается, но она остается пустой, незаполненной.

Инструкция DELETE особенно полезна, когда требуется удалить много записей.

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

Если требуется удалить все записи таблицы, удаление всей таблицы может быть более эффективным, чем выполнение запроса на удаление. Чтобы удалить таблицу из базы данных, можно использовать метод Execute с инструкцией DROP TABLE. Однако при удалении таблицы структура ее будет потеряна. С другой стороны, когда используется инструкция DELETE, удаляются только данные; структура таблицы и все свойства таблицы, такие как атрибуты полей и индексы, остаются без изменений.

Инструкцией DELETE можно удалить записи из отдельной таблицы или из таблиц стороны «многие» отношения один-к-многим. Например, в отношении между таблицами Заказчики и Заказы таблица Заказы — сторона «многие», поэтому операции удаления в запросе будут воздействовать только на таблицу Заказы.

Запрос DELETE удаляет записи полностью, а не только данные в определенных полях. Если требуется удалить значения в определенном поле, следует создать запрос UPDATE (см. ниже), который изменяет значения в поле на Null.

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

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

Изменение записей в запросе. Инструкция UPDATE создает запрос, который изменяет значения в полях указанной таблицы, основываясь на заданных критериях. Синтаксис запроса на обновление следующий:

UPDATE имя_таблицы

SET имя_столбца = новое_значение

WHERE критерии_отбора ;

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

Часть WHERE является необязательной, так же как и в операторе DELETE. Она играет здесь ту же роль, что и в операторе DELETE, — позволяет отобрать строки, к которым будет применена операция модификации. Если условие отбора не задается, то операция модификации будет применена ко всем строкам таблицы.

Запрос UPDATE особенно полезен, когда требуется изменить много записей или когда записи, которые требуется изменить, находятся в нескольких таблицах. Обычно такой запрос используется с методом Execute.

В следующем примере для грузоотправителей из России значения в поле Объем заказа увеличиваются на 10 процентов, а значения в поле Стоимость перевозки уменьшаются на 3 процента:

UPDATE Заказы _

SET [Объем заказа] = [Объем заказа] * 1.1, _

[Стоимость перевозки] = [Стоимость перевозки] * .97 _

WHERE [Страна] = 'RU';

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

Операция модификации, так же как и операция удаления, может использовать сложные подзапросы.

studfiles.net

Создание индексов

Формат команды CREATE INDEX на Transact SQL имеет вид:

CREATE [UNIQUE] [CLUSTERED | NONCLUSTERED]

INDEX index_name ON table (column [...n])

UNIQUE – при указании этого ключевого слова будет создан уникальный индекс. При создании такого индекса сервер выполняет предварительную проверку колонки на уникальность значений. Если в колонке есть хотя бы два одинаковых значения, индекс не создается и сервер выдает сообщение об ошибке. В индексируемой колонке также желательно запретить хранение значений NULL, чтобы избежать проблем, связанных с уникальностью значений. После того как для колонки создан уникальный индекс, сервер не разрешает выполнение команд INSERT и UPDATE, которые приведут к появлению дублирующихся значений.

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

NONCLUSTERED – создаваемый индекс будет некластерным. В таблице можно определить до 249 некластерных индексов.

Index_name – имя индекса, по которому он будет распознаваться командами Transact-SQL. Имя индекса должно быть уникальным в пределах таблицы.

table (column [...n]) – имя таблицы, в которой содержатся одна или несколько индексируемых колонок. В скобках указываются имена колонок, на основе которых будет построен индекс. Не допускается построение индекса на основе колонок с типом данных text, ntext, image или bit. Если указывается несколько колонок, то создаваемый индекс будет смешанным (composite index). В один смешанный индекс можно включить до 16 колонок.

Для удаления индекса используется команда DROP INDEX, имеющая следующий синтаксис:

DROP INDEX 'table.index' [...n]

Аргумент 'table.index' определяет удаляемый индекс в таблице. За одну операцию удаления можно удалить несколько индексов в любой таблице базы данных для этого их достаточно перечислить их через запятую.

Использование представлений

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

Представление может выбирать данные из других представлений, которые, в свою очередь, могут также основываться на представлениях или таблицах. Вложенность представлений не должна превышать 32. Это максимальное количество уровней, поддерживаемое SQL Server 7.0. Представление можно создать только в текущей базе данных, но, используя распределенные запросы, можно ссылаться на таблицы и представления, созданные в других базах данных. Представление часто применяется для ограничения доступа пользователей к конфиденциальным данным в таблице. Представление может быть использовано для объединения данных из нескольких взаимосвязанных таблиц.

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

Так как в основе представления лежит SQL-запрос, то для представлений, как и для обычных запросов, генерируется план исполнения запроса. Каждый раз, когда пользователь обращается к представлению, план выполнения запроса компилируется заново.

Создание представлений с использованием Transact-SQL

CREATE VIEW view_name [(column [,...n])]

[WITH ENCRYPTION]

select_statement

[WITH CHECK OPTION]

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

column – имя колонки, которое будет использоваться в представлении. Максимальная длина имени составляет 128 символов. По умолчанию имена колонок в представлении соответствуют именам колонок в исходных таблицах. Явное указание имени колонки требуется при использовании вычисляемых колонок или при объединении нескольких таблиц, имеющих колонки с одинаковыми именами. Имена колонок перечисляются через запятую в соответствии с их порядком в представлении. Имена колонок можно указывать в команде SELECT, определяющей представление.

WITH ENCRYPTION – использование этого параметра предписывает серверу шифровать код SQL-запроса. Это гарантирует, что пользователи не смогут просмотреть код запроса и использовать его. Если при определении представления необходимо скрыть имена исходных таблиц и колонок, а также алгоритм объединения данных, то необходимо использовать эту опцию.

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

1) Нельзя создавать новую таблицу на основе результатов, полученных в ходе выполнения запроса, то есть запрещается использование параметра INTO.

2) Нельзя проводить выборку данных из временных таблиц, то есть нельзя использовать имена таблиц, начинающихся на # или ##.

В запрос нельзя включать операции вычисления и группировки, то есть запрещается указание параметров ORDER BY, COMPUTE и COMPUTE BY.

Для удаления представления используется команда Transact SQL DROP VIEW{view […n]}. За один раз можно удалить несколько представлений.

studfiles.net

Создание индекса

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

В среде SQL Server реализовано несколько типов индексов:

кластерные индексы;
некластерные индексы;
уникальные индексы.

Некластерный индекс

Некластерные индексы – наиболее типичные представители семейства индексов. В отличие от кластерных, они не перестраивают физическую структуру таблицы, а лишь организуют ссылки на соответствующие строки.

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

информацию об идентификационном номере файла, в котором хранится строка;
идентификационный номер страницы соответствующих данных;
номер искомой строки на соответствующей странице;
содержимое столбца.

В большинстве случаев следует ограничиваться 4-5 индексами.

Кластерный индекс

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

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

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

Кластерный индекс может включать несколько столбцов. Однако количество таких столбцов рекомендуется по возможности свести к минимуму.

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

При создании в таблице первичного ключа (PRIMARY KEY) сервер автоматически создает для него кластерный индекс, если его не существовало ранее или если при определении ключа не был явно указан другой тип индекса.

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

studfiles.net

Создание индекса

В среде SQL Server реализовано несколько типов индексов:

кластерные индексы;
некластерные индексы;
уникальные индексы.

Некластерный индекс

информацию об идентификационном номере файла, в котором хранится строка;
идентификационный номер страницы соответствующих данных;
номер искомой строки на соответствующей странице;
содержимое столбца.

В большинстве случаев следует ограничиваться 4-5 индексами.

Кластерный индекс

studfiles.net