Shulou(Shulou.com)05/31 Report--

This article mainly explains "how to build an index with SQL". The explanation in this article is simple and clear, easy to learn and understand. Please follow the ideas of Xiaobian and go deep into it slowly to study and learn "how to build an index with SQL" together.

Creating indexes with SQL

To index a table, start the ISQL/w program in the SQL Server program group on the taskbar. After entering the query window, 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 name of the field being indexed in the index name. This will help you understand your intention for creating the index later.

Note:

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

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

This means that the statement was executed successfully.

The index mycolumn_index is performed on the mycolumn field of the table mytable. This is a non-clustered index and a non-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 rebuild one. Once an index has been created, you can delete it with the following SQL statement:

DROP INDEX mytable.mycolumn_index

Note 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 create a clustered index, use the keyword CLUSTERED.) Remember that a table can have only one clustered index. (Here's an example of how to cluster index a table:

CREATE CLUSTERED INDEX mycolumn_clust_index ON mytable(mycolumn)

If there are duplicate records in the table, an error occurs when you try to index with this statement. But tables with duplicate records can also be indexed; you just tell SQL Server this using the keyword ALLOW_DUP_ROW:

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 entries in a table, but if they already appear, you can use this method.

To create a unique index on a table, use the keyword UNIQUE. This keyword can be used for both clustered and non-clustered indexes. Here's an example:

CREATE UNIQUE COUSTERED INDEX myclumn_cindex ON mytable(mycolumn)

This is an index building statement that you will use frequently. Whenever possible, you should try to enhance query operations by creating unique clustered indexes on a pair of tables.

Finally, to create an index on multiple fields--a composite index--include multiple field names in the index creation statement. The following example indexes the firstname and lastname fields:

CREATE INDEX name_index ON username(firstname,lastname)

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

Building Index with Transaction Manager

Indexing is much easier with Transaction Manager than with SQL statements. Using the Transaction Manager, you can see a list of established indexes and select index options through the graphical interface.

Using Transaction Manager you can create indexes in two ways: using the Manage Tables window or using the Manage Indexes window.

To create a new index using the Manage Tables window, click the Advanced Options button (which looks like a table with a plus sign in front). This opens the Advanced Options dialog box. A section of this dialog box is labeled Primary Key (see Figure 11.1).

Figure 11. 1

To create a new index, select the field name you want to 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 unclustered. After saving the table information, the index is automatically created. A key appears next to the field name in the Manage Tables window.

You have created a "master 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 be a unique value field.

To create an index without these restrictions, you need to use the Manage Indexes window. Select Manage from the menu| Indexes, open the Manage Indexes window. In the Manage Indexes window, you can select tables and specific indexes from the drop-down boxes. (See Figure 11.2.) To create a new index, select New Index. from the Index drop-down box, You can then select fields to index. Click the Add button to add fields to the index.

Figure 11. 2

You can choose many different options for your index. For example, you can choose whether the index is clustered or unclustered. You can also specify that the index is unique. After designing the index, click the Build button to build the index.

Note:

Unique index means that the field cannot have duplicate values, rather than only one index.

SQL Core Statements

In Chapter 10, you learned how to fetch data from a table using SQL SELECT statements. However, until now, there has been no discussion of how to add, modify, or delete data from tables. In this section, you will learn about them.

insert data

To add a new record to a table, you use SQL INSERT statements. 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 mytable. The name of the field into which the data is 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 more than one field, you can insert data into all fields by separating the field names from the field values with commas. Suppose that the table 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','yet more data')

Note:

You can insert data into text fields using INSERT statements. However, if you need to enter a long string, you should use the WRITETEXT statement. This part of the book is too advanced for discussion. For more information, refer to Microsoft SQL Server documentation.

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

If the field has a default value, that value is used. For example, suppose you insert a new record without providing data for the field third_column, which has a default value of 'some value'. In this case, the value 'some value' is inserted when a new record is created.

If the field can accept nulls and there is no default, nulls are inserted.

If the field cannot accept a null value and there is no default value, an error occurs. You will receive an error message:

The column in table mytable may not be null.

Finally, if the field is an identification field, it automatically generates a new value. When you insert a new record into a table that has an identification field, simply ignore the field and the identification field assigns itself a new value.

Note:

After inserting a new record into a table with an identity field, you can access the new record using the SQL variable @@identity

The value of the identification field for. 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 that field is inserted into the other_first field of the table anothertable. This is because the variable @@identity always holds the value of the last inserted identity field.

Field another_first should have the same data type as field first_column. However, the field another_first cannot be the field that should identify. Another_first field is used to hold the value of field first_column.

delete records

To delete one or more records from a table, you need to use the SQL Delete statement. You can provide a WHERE clause to a Delete statement. The WHERE clause is used to select records to delete. For example, the following REMOTE statement deletes only records whose field first_column has a value equal to 'Delete Me':

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 condition that can be used in a SQL SELECT statement can be used in the WHERE clause of a DELECT statement. For example, the following REMOTE statement deletes only records whose first_column field has a value of 'goodbye' or whose second_column field has a value of 'so long':

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

Thank you for reading, the above is "how to use SQL index" content, after the study of this article, I believe we have a deeper understanding of how to use SQL index this problem, the specific use of the situation also needs to be verified by practice. Here is, Xiaobian will push more articles related to knowledge points for everyone, welcome to pay attention!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.