SQL index Vs Database index Real Differences

Index in a database is very important object to access row as you desired from millions of rows in a Table or group of Tables. The question is how you can differentiate SQL index from Database index. The below selected points help you understand the subject quickly and very efficiently.

What is the SQL index?

An index is used to speed up the performance of queries. It does this by reducing the number of database data pages that have to be visited/scanned.

A clustered index determines the physical order of data in a table. There can be only one clustered index per table.

With SQL, you can create any kind of index as supported by the database. For example, DB2, Oracle, SQL server -each has its own index types. (Read DB2 indexes here).

What is a Database index?

Indexes are used by the database to quickly locate data without having to search every row in a database table every time a database table is accessed. Indexes can be created using one or more columns of a database table, providing the basis for both rapid random lookups and efficient access of ordered records. You can also read online How do hash access works in DB2, without scanning Table or Index.

Once index is created on a particular column of a table, then database has its internal mechanism to locate your desired row quickly.

In general, once an index is created on a table, the database treats it as a B-tree index. Typically speaking, an index created by SQL, will be used input and the database arranges rows accordingly and make available for fast access. So this is called the Database index.

Related Posts

Author: Srini

Experienced software developer. Skills in Development, Coding, Testing and Debugging. Good Data analytic skills (Data Warehousing and BI). Also skills in Mainframe.