{"id":23331,"date":"2018-08-12T06:28:44","date_gmt":"2018-08-12T06:28:44","guid":{"rendered":"https:\/\/data-flair.training\/blogs\/?p=23331"},"modified":"2021-03-10T21:38:20","modified_gmt":"2021-03-10T16:08:20","slug":"sql-index","status":"publish","type":"post","link":"https:\/\/data-flair.training\/blogs\/sql-index\/","title":{"rendered":"SQL Index – Example | Create, Unique, Composite Index"},"content":{"rendered":"

Data stored in the tables within the database is quite large in quantity. Practically, searching and retrieving such vast data records creates speed constraints.<\/p>\n

To simplify the searching and retrieving of data, we use various techniques, and Indexes provide one such method. In SQL, the searching algorithm is set by the system, and thus, indexing proves to be very beneficial.<\/p>\n

Indexes are put internally and are not visible to the user.<\/p>\n

Indexes are just like unique labels provided to the data so that the identification of data becomes easy when a search query is made. Indexing is a very popular method used with large datasets in the industry.<\/p>\n

What are Indexes in SQL and their Usage?<\/h3>\n

Indexes are set to fasten the query and are internal to the system, i.e., Indexes are not visible to the user. Indexes can be put in a single column or by using multiple columns.<\/p>\n

While creating indexes, we should be very cautious as once we create indexes updating the table becomes very difficult. This happens because, along with the data, we need to update the indexes as well.<\/p>\n

Imagine in a bank server we have lakhs of users and even some have the same names. If we depend only on the names for data retrieval, the system would lag a lot.<\/p>\n

To resolve the speed and efficiency issues, Indexes are put instead to make the process of retrieving data efficient. And indexes are kept hidden and not available to the user to ensure the security is not at stake.<\/p>\n

The statement CREATE INDEX operates to create an index on a table. The syntax is as follows:
\nSyntax:<\/strong><\/p>\n

CREATE INDEX index_name\r\nON tableName (columnName1, columnName2, ...);\r\n<\/pre>\n
Demo Database<\/h3>\n
Let us view our database, which we will use in our demo.
\nQuery:\u00a0<\/strong><\/p>\n
SELECT * FROM DataFlair_Employee ;\r\n<\/pre>\n
\"SQL<\/a><\/p>\n
Indexes in SQL<\/h3>\n
We use the INDEX clause to create an index in our table. The index clause helps us to query the data fast. We can decide for ourselves if we need unique Indexes or whatsoever the condition is put up.<\/p>\n