SQL Data Operations Basics (intermediate) 7

Data is indexed in SQL

To index a table, start the ISQL/W program in the taskbar SQL Sever program group. After entering the query window, enter the following statement:

CREATE INDEX Mycolumn_index on MyTable (myclumn)

This statement establishes an index named Mycolumn_index. You can give a rope any name, but you should include the indexed field name in the index name, which will help you to figure out the intention to establish the index in the future.

Attention:

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

This command did don't return Data,and it did not return any rows

This indicates that the statement was executed successfully.

The index Mycolumn_index the MyColumn field of the table MyTable. This is a nonclustered index and is not a unique index. (This is the default property for an index)

If you need to change the type of an index, you must delete the original index and build one. Once you have established an index, you can delete it with the following SQL statement:

DROP INDEX Mytable.mycolumn_index

Notice that you want to include the name of the table in the drop INDEX statement. In this example, the index you delete is Mycolumn_index, which is the index of the table mytable.

To establish a clustered index, you can use the keyword clustered. Remember that a table can have only one clustered index. (Here's an example of how to set up a clustered index on a table:

CREATE CLUSTERED INDEX mycolumn_clust_index on MyTable (MyColumn)

If there are duplicate records in the table, an error occurs when you attempt to use this statement to create an index. But tables with duplicate records can also be indexed; you just use the keyword allow_dup_row to tell the SQL sever:

CREATE CLUSTERED INDEX mycolumn_cindex on MyTable (MyColumn)

With Allow_dup_row

This statement establishes a clustered index that allows duplicate records. You should try to avoid duplicate records in one table, but if you have, you can use this method.

To create a unique index on a table, you can use the keyword unique. This keyword is available for clustered and nonclustered indexes. Here's an example:

CREATE UNIQUE coustered INDEX myclumn_cindex on MyTable (MyColumn)

This is the index you will often use to establish the statement. Whenever you can, you should try to create a unique clustered index on a table to enhance the query operation.

Finally, to establish an index to multiple fields--composite Index--Contains multiple field names in the index establishment statement. The following example indexes the FirstName and LastName two fields:

CREATE INDEX Name_index on username (firstname,lastname)

This example establishes a single index for two fields. In a composite index, you can index up to 16 fields.

Using the transaction manager to establish an index

It is much easier to build an index with the transaction manager than to use SQL statements. Using the transaction manager, you can see a list of the indexes that have been established, and you can select the indexing options through the graphical interface.

You can use the transaction manager to index two ways: Use the Manage Tables window or use the Manage Indexes window.

To create a new index with the Manage Tables window, click the button Advanced Options (it looks like a table with a plus sign in front). This opens the Advanced Options dialog box. This dialog box has a portion labeled Primary Key (see Figure 11.1).

Figure 11. 1

To create a new index, select the name of the field you want to index from the Drop-down list. If you want to set up a multiple-field index, you can select more than one field name. You can also choose whether the index is clustered or not clustered. After you save the table information, the index is automatically created. A key appears next to the field name in the Manage Tables window.

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

To establish an index without these restrictions, you need to use the Manage Indexes window. Choose manage| from the menu Indexes, open the Manage Indexes window. In the Manage Indexes window, you can select a table and a specific index by using the dropdown box. (see Figure 11.2). To make a new index, select New from the Index Drop-down box, and then select the field to which you want to index. Click the button add to add the field to the index.

Figure 11. 2

You can choose a number of different options for your index. For example, you can choose whether the index is clustered or nonclustered. You can also specify that the index is a unique index. After you have designed the index, click the button build to establish the index.

Attention:

A unique index is one in which the field cannot have duplicate values, rather than being able to establish this index.

SQL Core Statement

In the tenth chapter, you learned how to use an SQL SELECT statement to fetch data from a table. However, until now, there has been no discussion of how to add, modify, or delete data in a table. In this section, you will learn these things.

Inserting data

To add a new record to the table, you want to 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 string ' Some data ' into the MyColumn field of the table MyTable. The name of the field that will be inserted into the data is specified in the first parenthesis, and the actual data is given in the second parenthesis.

The complete syntax for the INSERT statement is as follows:

INSERT [into] {table_name|view_name} [(column_list)] {DEFAULT VALUES |

Values_list | Select_statement}

If a table has more than one field, you can insert data into all fields by separating the field name and field values by commas. Suppose the table mytable has three fields First_column,second_column, and Third_column. The following INSERT statement adds a full record of three fields that have values:

INSERT MyTable (First_column,second_column,third_column)

VALUES (' Some data ', ' Some more data ', ' yet more data ')

Attention:

You can use the INSERT statement to insert data into a text-type field. However, if you need to enter a long string, you should use the WRITETEXT statement. This part is too advanced for this book, so it is not discussed. To learn more, refer to the Microsoft SQL Sever documentation.

What happens if you specify only two fields and data in an INSERT statement? In other words, you insert a new record into a table, but one field doesn't provide the data. In this case, there are the following four possible kinds of possibilities:

If the field has a default value, the value is used. For example, suppose you insert a new record without providing data to the field Third_column, and the field has a default value of ' some value '. In this case, the value ' some value ' is inserted when the new record is established.
If the field can accept null values and has no default value, it is inserted with a null value.
If the field cannot accept null values, and there is no default value, an error occurs. You will receive the error message:
The column in table mytable May is null.

Finally, if the field is an identity field, it automatically produces a new value. When you insert a new record into a table with a marked literacy segment, the Identification field assigns a new value to the field as long as it is ignored.
Attention:

After inserting a new record into a table with a marked literacy segment, you can use the SQL variable @ @identity to access the new record

The value of the identity field. 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 identity field, the value of the field is inserted into the Another_first field of the table anothertable. This is because the variable @ @identity always holds the value of the last time you inserted the identity field.

Field Another_first should have the same data type as the field First_column. However, field Another_first cannot be a field that should be identified. The Another_first field is used to hold the value of the field First_column.

Delete a record

To delete one or more records from a table, you need to use the SQL DELETE statement. You can provide a WHERE clause to the DELETE statement. The WHERE clause is used to select the record to delete. For example, the following DELETE statement deletes only the record for the First_column value of the field equals ' Delete Me ':

DELETE mytable WHERE first_column= ' Deltet Me '

The complete syntax for the DELETE statement is as follows:

DELETE [from] {table_name|view_name} [WHERE clause]

Any condition that can be used in an SQL SELECT statement can be used in the WHERE clause of the Delect statement. For example, the following DELETE statement deletes only those records in which the value of the First_column field is ' So long ' in the ' Goodbye ' or second_column field:

DELETE mytable WHERE first_column= ' goodby ' OR second_column= ' so long '

If you do not provide a WHERE clause to the DELETE statement, all records in the table will be deleted. You should not have such an idea. If you want to delete all records in the table, you should use the TRUNCATE TABLE statement in chapter tenth.

Attention:

Why use the TRUNCATE TABLE statement instead of the DELETE statement? When you use the TRUNCATE TABLE statement, the deletion of the record is not recorded. That means that truncate TABLE is much faster than delete.