Skip to main content

5. INDEX

Image
By


§
Indexes are special lookup tables that the database search engine can use to speed up data retrieval.
§ An index can be created in a table to find data more quickly and efficiently.
§ An index helps speed up SELECT queries and WHERE clauses, but it slows down data input, with UPDATE and INSERT statements.
§ Indexes can be created or dropped with no effect on the data.
§ Updating a table with indexes takes more time than updating a table without (because the indexes also need an update).

Clustered indexes

§ Uses physical memory.
§ Easy to retrieve data.
§ Data is in stored form.

Non-clustered indexes

§ No use of physical memory is needed.
§ More complicated, data retrieval is not that easy.
§ We can toocreateuniqueindex.Duplicatevalues are not allowed.

CREATEINDEXindex_name
ONtable_name(column_name)

CREATEINDEXindex_name
ONtable_name(column_name)
CREATEINDEXPindex

Difference between clustered index and non-clustered index

         Clustered index
        Non-clustered index
One table can only have one clustered index
It can have more than one non clustered index

























<<Prev                                                                                   Next>>






Labels:

Comments

Post a Comment

Popular posts from this blog

10. SQL Aliases

By
§ SQL aliases are used to temporarily rename a table or a column heading. § Basically aliases are created to make column names more readable. Syntax for Columns SELECT column_name AS alias_name FROM table_name Syntax for Tables SELECT column_name ( s ) FROM table_name AS alias_name ; Here is a SQL statement with alias § Example SELECT o . OrderID , o . OrderDate , c . CustomerName FROM Customers AS c , Orders AS o WHERE c . CustomerName = ’Around the Horn’ AND c . CustomerID = o . CustomerID ; The same SQL statement without aliases: § Example SELECT Orders . OrderID , Orders . OrderDate , Customers . CustomerName FROM Customers , Orders WHERE Customers . CustomerName = "Around the Horn" AND Customers . CustomerID = Orders . CustomerID ; <<Prev                                                                           Next>>
Post a Comment
Read more