Index in SQL Server

Arun Choudhary

14y1.9k

Hi,
Ques What is an Index. Can I create a separate Index on each column of a table ?

3 Replies

Know the answer? Post it — somebody with the same question will find it here.

MuthuMari M14y ago

Hi,

Index in sql is created on existing tables to retrieve the rows quickly.

When there are thousands of records in a table, retrieving information will take a long time. Therefore indexes are created on columns which are accessed frequently, so that the information can be retrieved quickly. Indexes can be created on a single column or a group of columns. When a index is created, it first sorts the data and then it assigns a ROWID for each row.

pls refer this url

http://beginner-sql-tutorial.com/sql-index.htm

Thanks.

If this post is useful then mark it as "Accepted Answer"

Satyapriya Nayak14y ago
Hi Arun,

Please refer Our recommended articles

http://www.c-sharpcorner.com/uploadfile/b19d5a/index-in-sql-server-2008/default.aspx

http://www.c-sharpcorner.com/uploadfile/nipuntomar/clustered-index-and-non-clustered-index-in-sql-server/default.aspx

Thanks
0
Senthilkumar14y ago
Hi Arun,

Indexes are created on columns in tables or views. The index provides a fast way to look up data based on the values within those columns. For example, if you create an index on the primary key and then search for a row of data based on one of the primary key values, SQL Server first finds that value in the index, and then uses the index to quickly locate the entire row of data. Without the index, a table scan would have to be performed in order to locate the row, which can have a significant effect on performance.

You can create indexes on most columns in a table or a view. The exceptions are primarily those columns configured with large object (LOB) data types, such as image, text, and varchar(max). You can also create indexes on XML columns, but those indexes are slightly different from the basic index and are beyond the scope of this article. Instead, I'll focus on those indexes that are implemented most commonly in a SQL Server database.

There are several types of indexes in sql server.

1) Clustered index
2) Non clustered index
3) Covering index

Clustered index will create automatically when you create the primary key on the table. A table can have only one clustered index.

Non clustered index is very useful for frequently searching columns in the table. As per the sql server 2008 you can have 999 non clustered index. All the rows it will have the lookup index table.

For more info:
http://msdn.microsoft.com/en-us/library/ms188783.aspx
http://www.simple-talk.com/sql/learn-sql-server/sql-server-index-basics/

0