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

6. VIEW

By
§ A view is a virtual table. § Database views are created using the CREATE VIEW statement. § Views can be created from a single table, multiple tables, or another view. § When we update a view it will update underline base table. CREATEVIEW view_name AS SELECT column1 , column2 ..... FROM table_name WHERE [condition] Example : CREATEVIEW CUSTOMERS_VIEW AS SELECT name , age FROM   CUSTOMERS SELECT * FROM CUSTOMERS_VIEW                        Advantages § Reduce complexity of database schema. § Mechanism to implement row and column level security. § Represent aggregate data and hide detailed data. Limitations § Cannot pass parameter to view. § Rules and default cannot be associated with the view. § Order by clause is invalid in a view unless TOP or XML is also specified. § Cannot be created on the bases of temporary tables. Indexed View § Create u...
Post a Comment
Read more

13. Filtering Results

By
WHERE and HAVING are filters. They specify a series of search conditions, and only those rows that meet the terms of the search conditions are used to build the result set. Where SELECT * FROM Customers WHERE CustomerID = 1 ; GROUP BY The GROUP BY statement is used in conjunction with the aggregate functions to group the result-set by one or more columns. SELECT column_name , aggregate_function ( column_name ) FROM table_name WHERE column_name operator value GROUPBY column_name ; § Example SELECT Shippers . ShipperName , COUNT ( Orders . OrderID ) AS NumberOfOrders FROM Orders LEFTJOIN Shippers ON Orders . ShipperID = Shippers . ShipperID GROUPBY ShipperName ; Having § The HAVING clause is typically used together with the GROUP BY clause to filter the results of aggregate values. § The HAVING clause was added to SQL because the WHERE keyword could not be used with aggregate functions. SELECT column_name , aggregate_function ( colu...
Post a Comment
Read more