Prizes & Awards
My Profile
Active Members
TodayLast 7 Days
more...
|
Forums » .NET » SQL Server »
index in sql
Posted Date: 24 Dec 2008 Posted By: Karthik Member Level: Silver Points: 1
Responses:
5
|
hi,
what is index in sql? why and where we use this index?
Thanks& regards
karthik.
|
Responses
|
| Author: Durga Prasad 24 Dec 2008 | Member Level: Gold | Rating:  Points: 6 |
Relational databases like SQL Server use indexes to find data quickly when a query is processed. Creating and removing indexes from a database schema will rarely result in changes to an application's code; indexes operate 'behind the scenes' in support of the database engine. However, creating the proper index can drastically increase the performance of an application.
The SQL Server engine uses an index in much the same way a reader uses a book index. For example, one way to find all references to INSERT statements in a SQL book would be to begin on page one and scan each page of the book. We could mark each time we find the word INSERT until we reach the end of the book. This approach is pretty time consuming and laborious. Alternately, we can also use the index in the back of the book to find a page number for each occurrence of the INSERT statements. This approach produces the same results as above, but with tremendous savings in time.
When a SQL Server has no index to use for searching, the result is similar to the reader who looks at every page in a book to find a word: the SQL engine needs to visit every row in a table. In database terminology we call this behavior a table scan, or just scan.
A table scan is not always a problem, and is sometimes unavoidable. However, as a table grows to thousands of rows and then millions of rows and beyond, scans become correspondingly slower and more expensive.
Consider the following query on the Products table of the Northwind database. This query retrieves products in a specific price range.
SELECT ProductID, ProductName, UnitPrice
FROM Products WHERE (UnitPrice > 12.5) AND (UnitPrice < 14)
There is currently no index on the Product table to help this query, so the database engine performs a scan and examines each record to see if UnitPrice falls between 12.5 and 14.
Taking Advantage of Indexes
The database engine can use indexes to boost performance in a number of different queries. Sometimes these performance improvements are dramatic. An important feature of SQL Server 2000 is a component known as the query optimizer. The query optimizer's job is to find the fastest and least resource intensive means of executing incoming queries. An important part of this job is selecting the best index or indexes to perform the task. In the following sections we will examine the types of queries with the best chance of benefiting from an index.
Searching For Records
The most obvious use for an index is in finding a record or set of records matching a WHERE clause. Indexes can aid queries looking for values inside of a range (as we demonstrated earlier), as well as queries looking for a specific value. By way of example, the following queries can all benefit from an index on UnitPrice:
Sorting Records
When we ask for a sorted dataset, the database will try to find an index and avoid sorting the results during execution of the query. We control sorting of a dataset by specifying a field, or fields, in an ORDER BY clause, with the sort order as ASC (ascending) or DESC (descending). For example, the following query returns all products sorted by price:
SELECT * FROM Products ORDER BY UnitPrice ASC
With no index, the database will scan the Products table and sort the rows to process the query. However, the index we created on UnitPrice (IDX_UnitPrice) earlier provides the database with a presorted list of prices. The database can simply scan the index from the first entry to the last entry and retrieve the rows in sorted order.
The same index works equally well with the following query, simply by scanning the index in reverse.
SELECT * FROM Products ORDER BY UnitPrice DESC
Grouping Records
We can use a GROUP BY clause to group records and aggregate values, for example, counting the number of orders placed by a customer. To process a query with a GROUP BY clause, the database will often sort the results on the columns included in the GROUP BY. The following query counts the number of products at each price by grouping together records with the same UnitPrice value.
SELECT Count(*), UnitPrice FROM Products GROUP BY UnitPrice
The database can use the IDX_UnitPrice index to retrieve the prices in order. Since matching prices appear in consecutive index entries, the database is able count the number of products at each price quickly. Indexing a field used in a GROUP BY clause can often speed up a query.
Maintaining a Unique Column
Columns requiring unique values (such as primary key columns) must have a unique index applied. There are several methods available to create a unique index. Marking a column as a primary key will automatically create a unique index on the column. We can also create a unique index by checking the Create UNIQUE checkbox in the dialog shown earlier. The screen shot of the dialog displayed the index used to enforce the primary key of the Products table. In this case, the Create UNIQUE checkbox is disabled, since an index to enforce a primary key must be a unique index. However, creating new indexes not used to enforce primary keys will allow us to select the Create UNIQUE checkbox. We can also create a unique index using SQL with the following command:
CREATE UNIQUE INDEX IDX_ProductName On Products (ProductName)
The above SQL command will not allow any duplicate values in the ProductName column, and an index is the best tool for the database to use to enforce this rule. Each time an application adds or modifies a row in the table, the database needs to search all existing records to ensure none of values in the new data duplicate existing values. Indexes, as we should know by now, will improve this search time.
| | Author: Asal-2009 24 Dec 2008 | Member Level: Diamond | Rating: Points: 2 |
Hi,
take this links its may hlep u
http://infogoal.com/sql/sql-create-index.htm
http://www.sql-tutorial.com/sql-indexes-sql-tutorial/
http://www.1keydata.com/sql/sql-create-index.html
G.Rajan
Happy New Year
| | Author: Babu Akkandi 24 Dec 2008 | Member Level: Diamond | Rating: Points: 6 |
Hi,
Indexes in databases are very similar to indexes in libraries.Indexes allow locating information within a database fast, much like they do in libraries. If all books in a library are indexed alphabetically then you don’t need to browse the whole library to find particular book. Instead you’ll simply get the first letter from the book title and you’ll find this letter’s section in the library starting your search from there, which will narrow down your search significantly.
An Index can be created on a single column or a combination of columns in a database table. A table index is a database structure that arranges the values of one or more columns in a database table in specific order. The table index has pointers to the values stored in specified column or combination of columns of the table. These pointers are ordered depending on the sort order specified in the index.
Here is how to use CREATE INDEX SQL statement to create an index on column Model in the Product table, called idxModel:
CREATE INDEX idxModel
ON Product (Model)
The syntax for creating indexes varies greatly amongst different RDBMS, that’s why we will not discuss this matter further.
There are some general rules which describe when to use indexes. When dealing with relatively small tables, indexes do not improve performance. In general indexes improve performance when they are created on fields used in table joins. Use indexes when most of your database queries retrieve relatively small datasets, because if your queries retrieve most of the data most of the time, the indexes will actually slow the data retrieval. Use indexes for columns that have many different values (there are not many repeated values within the column). Although indexes improve search performance, they slow the updates, and this might be something worth considering.
Hope it Helps,
Thanks and Regards,
Babu Akkandi
Microsoft Technology
| | Author: Tejinder Singh Barnala 24 Dec 2008 | Member Level: Gold | Rating: Points: 6 |
When data volumes increase, organizations are faced with problems relating to data retrieval and posting. They feel the need for a mechanism that will increase the speed of data access. An index, like the index of a book, enables the database retrieve and present data to the end user with ease. An index can be defined as a mechanism for providing fast access to table rows and for enforcing constraints.
An index can be created by selecting one or more columns in a table that is being searched. It is a kind of ‘on disk’ structure associated with the table or view and contains keys that are built from one or more of the columns in the table or view. This structure known as B-Tree helps the SQL Server find the row or rows associated with the key values. Indexes can be created on computed columns or xml columns also.
Indexes can be clustered or non clustered. A clustered index stores data rows in the table based on their key values. Each table can have only one clustered index as the key values in the data rows are unique and the index is built on the unique key column. When a table has a clustered index, it is known as a clustered table. Non-Clustered indexes have structures that are different from the data rows. A non clustered index key value is used to point to data rows that contain the key value. This value is known as row locator. The structure of the row locator is determined on the basis of the type of storage of the data pages. If the data page is stored as a heap, a row locator becomes a pointer to a row. If the data page is stored in a clustered table the row locator is a clustered index key. Clustered and Non clustered indexes can be unique and indexes are automatically maintained for a table or view whenever the data table is modified.
SQL Server 2005 permits users add non-key columns to leaf level of the non clustered index for by passing existing index key limits and to execute fully covered index queries.
When the primary key and unique constraints of a table column are defined an automatic index is created.
The Query Optimizer uses indexes to reduce disk I/O operations and use of system resources while querying on data. Queries which contain SELECT, UPDATE or DELETE statements require indexes for optimal performance. When a query is executed, each available method is evaluated for retrieving data and the most efficient one is selected by the Query optimizer. The methodology used may be table scans or index scans. In table scans I/O operations are many and resource intensive as all rows in a table are scanned to find the relevant ones. Index scans are used to search the index key columns to find the storage location of rows needed by the query and as the Index contains very few columns, the query executes faster.
SQL Server 2005 provides the user with a new Transact-SQL DDL statement for modifying relational and XML indexes. The CREATE INDEX statement is enhanced to support XML index syntax, partitioning and the included columns. A number of new index options have been added including the ONLINE option that allows for concurrent user access to underlying data during index operations.
To create an index
1. In Object Explorer, right-click the table for which you want to create an index and click Modify.
2. The table opens in Table Designer.
3. From the Table Designer menu, click Indexes/Keys.
4. In the Indexes/Keys dialog box, click Add.
5. Select the new index in the Selected Primary/Unique Key or Index list and set properties for the index in the grid to the right.
6. Specify any other settings for the index and click Close.
7. The index is created in the database when you save the table.
SQL Server allows users create unique indexes on unique columns such as the identity number of the employee or student or whatever is the unique key by which the component data are identified. A set of columns also can be used to create a unique index. The DBA can set the option of ignoring duplicate keys in a unique index if required. The default is No.
To create a unique index
In Object Explorer, right-click the table and click Modify.
The table opens in Table Designer.
From the Table Designer menu, click Indexes/Keys.
Click Add. The Selected Primary/Unique Key or Index list displays the system-assigned name of the new index.
5. In the grid, click Type.
6. Choose Index from the drop-down list to the right of the property.
7. Under Column name, select the columns you want to index. You can select up to 16 columns. For optimal performance, select only one or two columns per index. For each column you select, indicate whether the index arranges values of this column in ascending or descending order.
8. In the grid, click Is Unique.
9. Choose Yes from the drop-down list to the right of the property.
10. Select the Ignore duplicate keys option if you want to ignore new or updated data that would create a duplicate key in the index (with the INSERT or UPDATE statement).
11. The index is created in the database when you save the table or diagram.
Please note that unique indexes cannot be created on a single column if the column contains NULL in more than one row. Similarly indexes cannot be created on multiple columns if the combination of the columns contains NULL in some rows. The NULL values are treated as duplicate values.
Clustered indexes can be created in SQL Server databases. In such cases the logical order of the index key values will be the same as the physical order of rows in the table. A table can have only one clustered index.
To create a clustered index
1. In Object Explorer, right-click the table for which you want to create a clustered index and click Modify.
2. The table opens in Table Designer.
3. From the Table Designer menu, click Indexes/Keys.
4. In the Indexes/Keys dialog box, click Add.
5. Select the new index in the Selected Primary/Unique Key or Index list.
6. In the grid, select Create as Clustered, and choose Yes from the drop-down list to the right of the property.
7. The index is created in the database when you save the table.
A full text index is used when a full text search is required to be performed on all the text based columns of the database. This index relies on a regular index which has to be created before a full text index is created. The regular index is created on a single, non null column. Usually a column with small values is selected for the indexation in a regular index. Often a Catalog is created using an external tool such as SQL Server Management Studio. Textual data from different text file formats are to be stored as image type files before Full text search can be done on the data.
To create full-text indexes
In Object Explorer, right-click the table for which you want to create a full-text index and click Modify.
The table opens in Table Designer.
From the Table Designer menu, click Fulltext Index.
4. The Full-text Index dialog box opens. If the database is not enabled for full text indexing the dialog box will have the add button disabled. To enable full text indexing for the database, right click the database>Click properties and check the Full text indexing check box.
5. Then create a catalog by right clicking on Storage>Full Text Catalog and creating a new Catalog and entering the required information in the dialog box that opens.
6. Now open the Full Text Index property dialog box by clicking on it in the Table Designer menu.
7. Click Add.
8. Select the new index in the Selected Full-text Index list and set properties for the index in the grid to the right.
9. Your index is automatically saved in the database when you save your table in Table Designer. The index is available for modification as soon as you create it.
To change index properties
1. In Object Explorer, right-click the table you want to open and click Modify.
2. From the Table Designer menu, click Indexes/Keys.
3. Select the index from the Selected Primary/Unique Key or Index list.
4. Change properties in the grid.
5. The changes are saved to the database when you save the table.
System defined names are assigned to indexes based on the database file name. If multiple indexes are created on a table the index names are incremented numerically with _1, _2 etc. An index can be renamed to be unique to a table. Since the automatically created index bears the same name as the primary key or unique constraint in a table, another index cannot be renamed later to match the primary key or unique constraint.
To rename an index
1. In Object Explorer, right-click the table with the index you want to rename and click Modify. 2. From the Table Designer menu, click Indexes/Keys.
3. Select the index from the Selected Primary/Unique Key or Index list.
4. In the grid, click Name and type a new name into the text box.
5. The changes are saved to the database when you save the table.
Indexes can be deleted. Usually an index is considered for deletion when the performance of the INSERT, UPDATE and DELETE operations are hindered by the Index.
To delete an index
1. In Object Explorer, right-click the table with indexes you want to delete and click Modify.
2. From the Table Designer menu, click Indexes/Keys.
3. In the Indexes/Keys dialog box, select the index you want to delete.
4. Click Delete.
5. The index is deleted from the database when the table is saved.
6. A similar procedure can be followed for deleting a full text index by selecting Full text index from the Table Designer and selecting the index name and clicking delete button.
Microsoft SQL Server database uses a fill factor to specify how full each index page can be. The percentage of free space allotted to an index is defined as the fill factor. This is an important aspect of indexing as the amount of space to be filled by an index has to be determined by the DBA so that performance is not retarded.
To specify a fill factor for an index
1. In Object Explorer, right-click the table with an index for which you want to specify a fill factor and click Modify.
2. The table opens in Table Designer.
3. From the Table Designer menu, click Indexes/Keys.
4. The Indexes/Keys dialog box opens.
5. Select the index in the Selected Primary/Unique Key or Index list.
6. In the Fill Factor box, type a number from 0 to 100. The value of 100 implies that the index will fill up completely and the storage space required will be minimal. This setting is recommended only for cases where data is unlikely to change. If data is likely to undergo addition and modification, it is better to set a lower value. Storage space required would be in proportion to the value set.
XML indexes cannot be created using the Index/Keys dialog box. One or more XML indexes can be created for xml data type columns on the basis of a primary xml index. Deleting the primary xml index will result in the deletion of all indexes created on the base of the primary index.
To create an XML index
1. In Object Explorer, right-click the table for which you want to create an XML index and click Modify.
2. The table opens in Table Designer.
3. Select the xml column for the index.
4. From the Table Designer menu, click XML Index.
5. In the XML Indexes dialog box, click Add.
6. Select the new index in the Selected XML Index list and set properties for the index in the grid to the right.
To delete XML Indexes
1. In Object Explorer, right-click the table with the XML index you want to delete and click Modify.
2. The table opens in Table Designer.
3. From the Table Designer menu, click XML Index.
4. The XML Index dialog box opens.
5. Click the index you want to delete in the Selected XML Index column.
6. Click Delete.
Many Thanks
Tejinder Singh Barnala
/*I have the simplest tastes. I am always satisfied with the best*/
|
| Post Reply |
|
|
|
 | | This thread is locked for new responses. Please post your comments and questions as a separate thread.
If required, refer to the URL of this page in your new post. |
|
|
|
|