Команды создания индексов — студенческий портал

Оператор 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 также поддерживает индексы и для представлений.

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

Опция 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 должна быть применена как к индексным страницам, так и к страницам данных, имеющим отношение к этому индексу.

Опция 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) появляется лишь один раз.

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

Источник: http://serversql.ru/indeksy/sozdanie-indeksov.html

SQL – Индексы

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

Индекс в базе данных очень похож на индекс в конце книги.

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

Индекс помогает ускорить для запросов SELECT и предложения WHERE, но это замедляет ввод данных, с заявлениями UPDATE и INSERT. Индексы могут быть созданы или удалены без влияния на данные.

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

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

Команда CREATE INDEX

Основной синтаксис CREATE INDEX выглядит следующим образом:

CREATE INDEX index_name ON table_name;

Одноколоночные индексы

Индекс для одного столбца создается на основе только одного столбца таблицы. Базовый синтаксис выглядит следующим образом.

CREATE INDEX index_name
ON table_name (column_name);

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

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

CREATE UNIQUE INDEX index_name
on table_name (column_name);

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

Составной индекс является индексом для двух или более столбцов таблицы. Его основной синтаксис выглядит следующим образом.

CREATE INDEX index_name
on table_name (column1, column2);

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

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

Неявные индексы

Неявные индексы – это индексы, которые автоматически создаются на сервере базы данных при создании объекта. Индексы автоматически создаются для первичного ключа и ограничения уникальности.

Команда DROP INDEX

Индекс может быть удален с помощью SQL команды DROP. Следует соблюдать осторожность при удалении индекса, поскольку производительность может либо замедлиться или улучшиться.

Базовый синтаксис выглядит следующим образом:

DROP INDEX index_name;

Вы можете посмотреть пример ограничения INDEX, чтобы увидеть некоторые реальные примеры по индексам.

Когда следует избегать индексов?

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

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

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

Источник: https://andreyex.ru/bazy-dannyx/uchebnoe-posobie-po-sql/sql-indeksy/

Создание кластеризованных индексов — SQL Server

• 02/17/2017
• Время чтения: 4 мин

ОБЛАСТЬ ПРИМЕНЕНИЯ: SQL Server База данных SQL Azure Azure Synapse Analytics (хранилище данных SQL) Parallel Data WarehouseAPPLIES TO: SQL Server Azure SQL Database Azure Synapse Analytics (SQL DW) Parallel Data Warehouse

Кластеризованные индексы для таблиц можно создать с помощью SQL Server Management StudioSQL Server Management Studio или Transact-SQLTransact-SQL.You can create clustered indexes on tables by using SQL Server Management StudioSQL Server Management Studio or Transact-SQLTransact-SQL.

За некоторыми исключениями, каждая таблица должна иметь кластеризованный индекс.With few exceptions, every table should have a clustered index. Кроме того, что кластеризованный индекс повышает производительность запросов, его можно перестраивать или переорганизовывать по запросу, управляя фрагментацией таблицы.

Besides improving query performance, a clustered index can be rebuilt or reorganized on demand to control table fragmentation. Кластеризованный индекс может быть также создан для представления.A clustered index can also be created on a view. (Кластеризованные индексы определены в разделе Описания кластеризованных и некластеризованных индексов.

)(Clustered indexes are defined in the topic Clustered and Nonclustered Indexes Described.)

В этом разделеIn This Topic

Перед началомBefore You Begin

Стандартные реализацииTypical Implementations

Кластеризованные индексы реализуются следующими методами.Clustered indexes are implemented in the following ways:

• Ограничениями PRIMARY KEY и UNIQUEPRIMARY KEY and UNIQUE constraints
  Если кластеризованный индекс в таблице еще не создан, а уникальный некластеризованный индекс еще не указан, то при создании ограничения PRIMARY KEY в одном или нескольких столбцах автоматически создается уникальный кластеризованный индекс.When you create a PRIMARY KEY constraint, a unique clustered index on the column or columns is automatically created if a clustered index on the table does not already exist and you do not specify a unique nonclustered index. В первичном ключевом столбце недопустимы значения NULL.The primary key column cannot allow NULL values.
  При создании ограничения UNIQUE создается уникальный некластеризованный индекс. Он нужен, чтобы принудительно применять ограничение UNIQUE по умолчанию.When you create a UNIQUE constraint, a unique nonclustered index is created to enforce a UNIQUE constraint by default. Если кластеризованный индекс в таблице еще не создан, то можно указать уникальный кластеризованный индекс.You can specify a unique clustered index if a clustered index on the table does not already exist.
  Индексу, создаваемому в составе ограничения, автоматически присваивается то же имя, что и имя ограничения.An index created as part of the constraint is automatically given the same name as the constraint name. Дополнительные сведения см. в разделах Primary and Foreign Key Constraints и Unique Constraints and Check Constraints.For more information, see Primary and Foreign Key Constraints and Unique Constraints and Check Constraints.
• Индекс, не зависящий от ограниченияIndex independent of a constraint
  Можно создать кластеризованный индекс в столбце, отличном от первичного ключевого столбца, если задано ограничение некластеризованного первичного ключа.You can create a clustered index on a column other than primary key column if a nonclustered primary key constraint was specified.

ОграниченияLimitations and Restrictions

• При создании кластеризованного индекса в соответствующих файлах и файловых группах требуется столько места на диске, сколько суммарно занимают старые (исходные) и новые (целевые) структуры.When a clustered index structure is created, disk space for both the old (source) and new (target) structures is required in their respective files and filegroups. Старая структура не освобождается до тех пор, пока не зафиксирована вся транзакция создания индекса.The old structure is not deallocated until the complete transaction commits. Кроме того, для сортировки может временно потребоваться дополнительное место на диске.Additional temporary disk space for sorting may also be required. Дополнительные сведения см. в статье Disk Space Requirements for Index DDL Operations.For more information, see Disk Space Requirements for Index DDL Operations.
• Если кластеризованный индекс создается в куче с несколькими существующими некластеризованными индексами, все некластеризованные индексы необходимо перестроить, чтобы они содержали значение ключа кластеризации, а не идентификатор строки (RID).If a clustered index is created on a heap with several existing nonclustered indexes, all the nonclustered indexes must be rebuilt so that they contain the clustering key value instead of the row identifier (RID). Точно так же, если кластеризованный индекс удаляется из таблицы с несколькими некластеризованными индексами, некластеризованные индексы перестраиваются в ходе операции DROP.Similarly, if a clustered index is dropped on a table that has several nonclustered indexes, the nonclustered indexes are all rebuilt as part of the DROP operation. Для больших таблиц это может занять значительное время.This may take significant time on large tables.
  Предпочтительный способ построения индекса в больших таблицах — это начать с кластеризованного индекса, а затем построить некластеризованные.The preferred way to build indexes on large tables is to start with the clustered index and then build any nonclustered indexes. При создании индексов для существующих таблиц рассмотрите целесообразность присвоения параметру ONLINE значения ON.Consider setting the ONLINE option to ON when you create indexes on existing tables. В этом случае длительные блокировки таблиц удерживаться не будут.When set to ON, long-term table locks are not held. Это позволит продолжить выполнение запросов или обновление базовых таблиц.This enables queries or updates to the underlying table to continue. Дополнительные сведения см. в статье Выполнение операции с индексами в сети.For more information, see Perform Index Operations Online.
• Ключ кластеризованного индекса не может включать в себя столбцы varchar , для которых существуют данные в единице размещения ROW_OVERFLOW_DATA.The index key of a clustered index cannot contain varchar columns that have existing data in the ROW_OVERFLOW_DATA allocation unit. Если кластеризованный индекс создается для столбца типа varchar и существующие данные располагаются в единице размещения IN_ROW_DATA, то все последующие операции вставки или обновления для данного столбца, выталкивающие данные за пределы строки, будут завершаться ошибкой.If a clustered index is created on a varchar column and the existing data is in the IN_ROW_DATA allocation unit, subsequent insert or update actions on the column that would push the data off-row will fail. Для получения сведений о таблицах, которые могут содержать превышающие размер страницы данные строки, используется функция динамического управления sys.dm_db_index_physical_stats (Transact-SQL).To obtain information about tables that might contain row-overflow data, use the sys.dm_db_index_physical_stats (Transact-SQL) dynamic management function.

безопасностьSecurity

PermissionsPermissions

Необходимо разрешение ALTER для таблицы или представления.Requires ALTER permission on the table or view.

Пользователь должен быть членом предопределенной роли сервера sysadmin или предопределенных ролей базы данных db_ddladmin и db_owner.

User must be a member of the sysadmin fixed server role or the db_ddladmin and db_owner fixed database roles.

Использование среды SQL Server Management StudioUsing SQL Server Management Studio

Создание кластеризованного индекса в обозревателе объектовTo create a clustered index by using Object Explorer

1. В обозревателе объектов разверните таблицу, в которой необходимо создать кластеризованный индекс.In Object Explorer, expand the table on which you want to create a clustered index.

2. Щелкните правой кнопкой мыши папку Индексы, наведите указатель на пункт Создать индекс и выберите пункт Кластеризованный индекс.Right-click the Indexes folder, point to New Index, and select Clustered Index….

3. В диалоговом окне Создание индекса на странице Общие введите имя нового индекса в поле Имя индекса .In the New Index dialog box, on the General page, enter the name of the new index in the Index name box.

4. В разделе Ключевые столбцы индекса щелкните Добавить… .Under Index key columns, click Add….

5. В диалоговом окне Выбор столбцов изимя_таблицы установите флажок для столбца таблицы, который следует добавить в кластеризованный индекс.In the Select Columns fromtable_name dialog box, select the check box of the table column to be added to the clustered index.

6. Нажмите кнопку ОК.Click OK.

7. В диалоговом окне Создание индекса нажмите кнопку ОК.In the New Index dialog box, click OK.

Создание кластеризованного индекса с помощью конструктора таблицTo create a clustered index by using the Table Designer

1. В обозревателе объектов разверните базу данных, в которой необходимо создать таблицу с кластеризованным индексом.In Object Explorer, expand the database on which you want to create a table with a clustered index.

2. Щелкните правой кнопкой мыши папку Таблицы и выберите команду Создать таблицу… .Right-click the Tables folder and click New Table….

3. Создайте новую таблицу обычным способом.Create a new table as you normally would. Дополнительные сведения см. в статье Создание таблиц (компонент Database Engine).For more information, see Create Tables (Database Engine).

4. Щелкните правой кнопкой мыши созданную таблицу и выберите пункт Конструктор.Right-click the new table created above and click Design.

5. В меню Конструктор таблиц выберите пункт Индексы и ключи.On the Table Designer menu, click Indexes/Keys.

6. В диалоговом окне Индексы и ключи нажмите Добавить.In the Indexes/Keys dialog box, click Add.

7. Выберите новый индекс в текстовом поле Выбранный первичный/уникальный ключ или индекс .Select the new index in the Selected Primary/Unique Key or Index text box.

8. Выберите в сетке Создать как кластеризованныйи из раскрывающегося списка справа от свойства выберите Да .In the grid, select Create as Clustered, and choose Yes from the drop-down list to the right of the property.

9. Щелкните Закрыть.Click Close.

10. В меню Файл выберите Сохранитьимя_таблицы.On the File menu, click Savetable_name.

Использование Transact-SQLUsing Transact-SQL

Создание кластеризованного индексаTo create a clustered index

1. В обозревателе объектовподключитесь к экземпляру компонента Компонент Database EngineDatabase Engine.In Object Explorer, connect to an instance of Компонент Database EngineDatabase Engine.

2. На стандартной панели выберите пункт Создать запрос.On the Standard bar, click New Query.

3. Скопируйте следующий пример в окно запроса и нажмите кнопку Выполнить.Copy and paste the following example into the query window and click Execute.

   USE AdventureWorks2012;
   GO
   — Create a new table with three columns.
   CREATE TABLE dbo.TestTable
   (TestCol1 int NOT NULL,
   TestCol2 nchar(10) NULL,
   TestCol3 nvarchar(50) NULL);
   GO
   — Create a clustered index called IX_TestTable_TestCol1
   — on the dbo.TestTable table using the TestCol1 column.
   CREATE CLUSTERED INDEX IX_TestTable_TestCol1
   ON dbo.TestTable (TestCol1);
   GO

Дополнительные сведения см. в разделе CREATE INDEX (Transact-SQL).For more information, see CREATE INDEX (Transact-SQL).

См. также:See Also

Создание первичных ключей Create Primary Keys
Создание ограничений уникальностиCreate Unique Constraints

Отправить отзыв о следующем:

Этот продукт

Вы также можете оставить отзыв непосредственно на GitHub .

Источник: https://docs.microsoft.com/ru-ru/sql/relational-databases/indexes/create-clustered-indexes