Procedure for creating an index using SQL

Enter the query window and enter the following statement:

Create index mycolumn_index on mytable (myclumn)

This statement creates an index named mycolumn_index. You can give an index any name, but you should include the field name of the index in the index name, which is helpful for you to figure out the intention of creating the index in the future.

Note:

When you execute any SQL statement in this book, you will receive the following information:

This command did not return data, and it did not return any rows

This indicates that the statement is successfully executed.

Index mycolumn_index to the mycolumn field of the table mytable. This is a non-clustered index and a non-unique index. (This is the default attribute of an index)

If you want to change the index type, you must delete the original index and recreate it. After an index is created, you can use the following SQL statement to delete it:

Drop index mytable. mycolumn_index

Note that you must include the table name in the drop index statement. In this example, the index you deleted is mycolumn_index, which is the index of mytable.

To create a clustered index, you can use the keyword clustered .) Remember that a table can only have one clustered index. (Here is an example of how to create a clustered index for a table:

Create clustered index mycolumn_clust_index on mytable (mycolumn)

If the table contains duplicate records, an error occurs when you try to use this statement to create an index. However, you can create indexes for tables with duplicate records. You only need to use the keyword allow_dup_row to tell SQL Sever:

Create clustered index mycolumn_cindex on mytable (mycolumn)

With allow_dup_row

This statement creates a clustered index that allows Repeated Records. You should try to avoid repeated records in a table. However, if the record already appears, you can use this method.

To create a unique index for a table, you can use the keyword unique. This keyword can be used for clustered indexes and non-clustered indexes. Here is an example:

Create unique coustered index myclumn_cindex on mytable (mycolumn)

This is the index creation statement that you will frequently use. Whenever possible, you should try to create a unique clustered index for a table to enhance query operations.

Finally, you need to create an index for multiple fields -- composite index -- the index creation statement contains multiple field names at the same time. The following example creates an index for the firstname and lastname fields:

Create index name_index on username (firstname, lastname)

In this example, a single index is created for two fields. In a composite index, You Can index up to 16 fields.

Create an index using the Transaction Manager

Creating an index using the Transaction Manager is much easier than using SQL statements. Using the Transaction Manager, you can view the list of created indexes and select the index option on the GUI.

You can use the Transaction Manager to create an index in two ways: Use the manage tables window or use the manage indexes window.

Create a new index in the manage tables window and click the advanced Options button (it looks like a table with a plus sign in front ). The advanced Options dialog box is displayed. In this dialog box, some labels are named primary key (see figure 11.1 ).

Figure 11. 1

To create a new index, select the field name you want to create an index from the drop-down list. If you want to create an index for multiple fields, you can select multiple field names. You can also choose whether the index is clustered or non-clustered. After the table information is saved, the index is automatically created. A key appears next to the field name in the manage tables window.

You have created a "primary index" for your table ". The primary index must be created for fields that do not contain null values. In addition, the primary index forces a field to become a unique value field.

To create indexes without these restrictions, you need to use the manage indexes window. Select Manage | indexes from the menu to open the manage indexes window. In the manage indexes window, you can select a table and a specific index from the drop-down list. (See Figure 11.2 ). To create a new index, select new index. From the index drop-down box, and then select the field to be indexed. Click Add to add the field to the index.

Figure 11. 2

You can select many different options for your index. For example, you can choose whether the index is clustered or non-clustered. You can also specify this index as a unique index. After the index is designed, click build to create the index.

Note:

A unique index means that this field cannot have duplicate values, rather than creating this index.

Core SQL statements

In Chapter 10, you learned how to use SQL select statements to retrieve data from a table. However, till now, we have not discussed how to add, modify, or delete data in a table. In this section, you will learn this content.

Insert data

Add a new record to the table. You must use the SQL insert statement. Here is an example of how to use this statement:

Insert mytable (mycolumn) values ('some data ')

This statement inserts the 'some data' string into the mycolumn field of mytable. The name of the field to be inserted is specified in the first bracket, and the actual data is given in the second bracket.

The complete syntax of the insert statement is as follows:

Insert [into] {table_name | view_name} [(column_list)] {default values |

Values_list | select_statement}

If a table has multiple fields, you can insert data into all fields by separating the field names and field values with commas. Assume that mytable has three fields first_column, second_column, and third_column. The following insert statement adds a complete record with values for all three fields:

Insert mytable (first_column, second_column, third_column)

Values ('some data', 'Some more data', 'Et more data ')

Note:

You can use the insert statement to insert data to text fields. However, if you need to enter a long string, you should use the writetext statement. This part of content is too advanced for this book, so it is not discussed. For more information, see the Microsoft SQL server documentation.

What if you specify only two fields and data in the insert statement? In other words, you insert a new record to a table, but one field does not provide data. In this case, there are four possibilities:

If this field has a default value, this value is used. For example, assume that no data is provided to the field third_column when you insert a new record, and this field has a default value of 'some value '. In this case, when a new record is created, the value 'some value' is inserted '.
If this field can accept null values without default values, a null value is inserted.
If this field cannot accept null values and there is no default value, an error will occur. You will receive the error message:
The column in table mytable may not be null.

Finally, if this field is an Identifier Field, it will automatically generate a new value. When you insert a new record to a table with an ID field, you only need to ignore this field and the ID field will assign a new value to you.
Note:

After inserting a new record into a table with an identified field, you can use the SQL variable @ identity to access the new record.

. Consider the following SQL statement:

Insert mytable (first_column) values ('some value ')

Insert anothertable (another_first, another_second)

Values (@ identity, 'some value ')

If the table mytable has an identification field, the value of this field will be inserted into the another_first field of the table anothertable. This is because the variable @ identity always saves the value of the last inserted Id field.

The field another_first should have the same data type as the field first_column. However, the field another_first cannot be identified. The another_first field is used to save the value of the field first_column.

Delete record

To delete one or more records from a table, use the SQL Delete statement. You can provide the WHERE clause for the delete statement. The where clause is used to select the record to be deleted. For example, the following Delete statement only deletes records whose value of first_column is 'deleteme:

Delete mytable where first_column = 'deltet me'

The complete syntax of the delete statement is as follows:

Delete [from] {table_name | view_name} [where clause]

Any conditions that can be used in the SQL SELECT statement can be used in the WHERE clause of the delect statement. For example, the following Delete statement only deletes records whose first_column values are 'Goodbye 'or whose second_column values are 'so long:

Delete mytable where first_column = 'goodby' or second_column = 'so long'

If you do not provide a where clause for the delete statement, all records in the table will be deleted. You shouldn't have this idea. If you want to delete all records in the expected table, use the truncate TABLE statement in chapter 10.

Note:

Why should we use the truncate TABLE statement instead of the delete statement? When you use the truncate TABLE statement, the deletion of records is not recorded. That is to say, this means that the truncate table is much faster than the delete table.