In my opinion, there is no need to create non clustered index on column with frequent read/write/update. The large numbers of indexes on a table will affect the performance of write/update. The reason is that all indexes must be adjusted when the data in
the table changes. Thus, there is no need to use it.
Best wishes,
Please mark the replies as answers if they help or unmark if not.
Feedback to us
luckyforu200...
Participant
1167 Points
434 Posts
creating non clustered index on column with frequent read/write/update
Dec 18, 2012 03:32 PM|LINK
hi experts,
Just need to know, will it be feasible to create non clustered index on column with frequent read/write/update.
please help.
mth13
Member
331 Points
96 Posts
Re: creating non clustered index on column with frequent read/write/update
Dec 18, 2012 05:06 PM|LINK
Yes, it is possible, but not recommended. Indexes should be used on static data, such as a RowID or other Unique Identifiers.
Catherine Sh...
All-Star
23373 Points
2490 Posts
Microsoft
Re: creating non clustered index on column with frequent read/write/update
Dec 20, 2012 09:16 AM|LINK
Hi,
In my opinion, there is no need to create non clustered index on column with frequent read/write/update. The large numbers of indexes on a table will affect the performance of write/update. The reason is that all indexes must be adjusted when the data in the table changes. Thus, there is no need to use it.
Best wishes,
Feedback to us
Develop and promote your apps in Windows Store
Sirama
Member
283 Points
67 Posts
Re: creating non clustered index on column with frequent read/write/update
Dec 20, 2012 11:59 AM|LINK
Please refer this link. It will Nice idea .
luckyforu200...
Participant
1167 Points
434 Posts
Re: creating non clustered index on column with frequent read/write/update
Dec 20, 2012 02:01 PM|LINK
hi catherina,
thanks for replying.
My problem is to select records as fast as possible because to avoid deadlock.
after validation is successful then only update else no update is required.
validation can be a simple less than or greater than check .
suppose, if user A with value 10(greater than check) want to validate with the value specified in db column.
if column value is 11 then its valid and updating will happen
else invalid value error.
I feel if millions of user try to validate than a deadlock might happen. so i am trying to create non clustered index on column specified.