what is the use of indexes , when i creating table it already creating one index..........

[Shaik Haneef]

hi all, i created table like that below create table extable(empid int primary key identity(1,1),empname varchar(50) not null,salary money not null) when i execute sp_help it already created one index for that table. sp_help extable PK__extable__3D9E16F4 Is it clustered index or non-clustered index ? what is the use of that index, how can improve that performance of the retrieving data

This is haneef.............................................................

[J4amieC]

The primary key is, by default, created as a clustered index. It will be used for lookups using "empid" in your case. Improving performance for retrieving data depends on how you lookup the data. For example, if you tend to lookup using empname put an index on that. If you lookup using empname and salary then create an index which includes both those columns. This is obviously a simplistic answer - indexing is a fine art where you must balance performance.

[Vinay Dornala]

Hi, The clustered index is created on primary key. a table can have only one clustered index the reason the data is arranged in physical order, sql server will maintain index record this will contain all index values mean empid in particular order. When we fire an select query this will arrange the result set in following format and fetch the corresponding record. The proper implementation of clustered index will reduce the query response time. The disadvantage is All the index values should present in Index Record Table( here is empids) empid 101 ---------> 101 XXX India 102 ---------> 102 YYY USA 103 ---------> 103 ZZZ UK Non Clustered index on table will arrange result set in heap sorting order this mean not all index values should not present in Index Record. This will occupies less memory space. We will usually created on Non primary keys. -.Net Professional.

modified on Tuesday, May 20, 2008 3:10 AM