SQL Server Cable Introduction order: Level 12th, create, modify, delete

In level tenth we see the internal structure of the index, and at level 11th we see the potential negative impact of the balance tree structure: index fragmentation. With the knowledge of the internal structure of the index, we can examine what happens when the data definition statements and data manipulation statements are executed. In this level we introduce the three verbs in the data definition language: create,alter and drop. In the next level, we introduce the three verbs in the data manipulation language: Insert,update,delete.

Create, modify, and delete indexes are the scope of index maintenance. Create,alter,drop serves as a verb to maintain the index, just because the SQL Server team believes that the same DDL verbs should be used for the maintenance of teammate objects. In this article you will see that these verbs do more work than the surface, so that you can create, rebuild, reorganize, disable, delete the index, and modify the metadata.

When you create or modify an index, you can specify an option. These options are part of the index metadata, stored in system tables, and viewed through the sys.indexes view. SQL Server is used when querying and updating data, as well as maintaining indexes. We've used these options in many places in this article, but we haven't covered them in detail. However, we'll cover these options in more detail at the end of the article.

The larger your table, the greater the impact of executing the DDL on the index. While executing the DDL, it consumes server resources and slows down other queries. In understanding what happened to your index in the DDL and DML process, you can:

• Understand the need for regular index maintenance.
• Maximize the performance of maintenance operations.
• Minimize the impact of maintenance operations on other queries.
• Reduce the number of maintenance.

Create an index

Let's look at creating a clustered index and later on creating a nonclustered index.

If you create a clustered index, the following information may appear.

The table already is a clustered index:

An error has occurred. A table cannot create two clustered indexes, which will result in one table having two sorts at the same time, which is not possible.

The table is empty:

SQL Server updated the clustered index in the system tables, and there is no space allocated.

The table has rows. The table does not has nonclustered indexes:

SQL Server updated the clustered index in the system tables.

SQL Server sorts the tables in the order of the index keys.

The table has rows. The table has nonclustered indexes:

SQL Server frees up the space occupied by nonclustered indexes, but retains the metadata.

SQL Server updated the clustered index in the system tables.

SQL Server builds a clustered index.

Rebuilds a nonclustered index based on metadata. No other choice, nonclustered indexes must be rebuilt. The label for each entry, formerly the RID, is now the key value of the clustered index. Therefore, the size of each entry is different from the original size.

Therefore, if you are creating multiple indexes in a table, in order to save time and reduce imagery, you should create a clustered index and then create a nonclustered index.

To create a nonclustered index

The table is empty:

SQL Server updated the nonclustered indexes in the system tables. There is no space allocated at this time.

The table has rows:

SQL Server updated the nonclustered indexes in the system tables.

SQL Server scans the table, contains other nonclustered indexes that require columns, builds an index entry for each row, and sorts the entries in indexed order.

modifying indexes

Modifying the index allows you to do four things:

• Disables indexing.
• Rebuild the index.
• Reorganize the index.
• The option to change the index.

Remember: One thing to modify the index does not allow you to do, that is to change the columns that make up the index key. If necessary, you can only delete the index and then create a new index, or use the DROP_EXISTING option when creating index.

disabling indexes

Disables indexing, using the Disable keyword.

ALTER INDEX Pk_fragtest_pkcol             on fragtest           GO

Disabling the index does not remove the definition of the index from the system tables. All disabled indexes can be rebuilt or deleted at a later time.

Disabling nonclustered indexes frees the disk space that the index consumes. After you disable nonclustered indexes, all queries are the same as no indexes.

Disabling a clustered index frees the space occupied by non-leaf layers in the index. After you disable a clustered index, the table cannot be queried and updated.

Because disabling an index is accompanied by a space release, processing requires a small amount of processor time and log file IO.

The primary reason to disable indexing is to save some disk space when rebuilding the index. If the index is not disabled, the rebuild process maintains an old version of the index until the new version's index is created, resulting in two versions consuming disk space at the same time. The index is deleted first, and the entire index is saved during the rebuild process.

Rebuilding indexes

Rebuild the index using the following statement.

ALTER INDEX Pk_fragtest_pkcol             on fragtest          REBUILD        withFILLFACTOR=at               = 3             

SQL Server Cable Introduction order: Level 12th, create, modify, delete