SQL Server Performance Forum – Threads Archive

Index creation for UPDATE,INSERT,DELETE

i have a table having 50,000 Records. I can update any of the field . should i create non-cluster index on every field ? If i dont create the index , SQL engine will lock the entire table to search the record first and then it will update. while the table is locked , if the same table is accessed by some other stored procedure, procedure may become the victim of deadlock. Please suggest me the solution ? 1. Should i create the index on every field ?
2. if Yes/ No . Why ?

Its better to go for non-clustered index and do this table has any primary key then default it will create clustered index .. Its strange that you still dint had any index created. Depending on the situation you can create other indexes on diferent fields too. So that query will itself make use of composite indexes. Also depending on the read/write operations select appropriate fill factor default is 100%.[<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />]

Create clustered index for the key column. That will be helpful in searching records
Hereafter post your question at the relevent forum
Madhivanan Failing to plan is Planning to fail

Good question! If I understand correctly, SQL Server will use the Primary Key constraint of a given table to identify the row(s) being touched and handle the locking based on that – so you can see it doesn’t lock at the column level. And if there’s no primary key defined on the table, I’m sure there is a fall-back routine to identify the row(s). Also keep in mind that the PK is not necessarily a clustered index … Although there are tables where you will see indexes on all columns, like tables that resolve many-to-many relationships, you should be careful not add too many indexes to a data entry table, as each additional index will add to the response time on inserts, updates and deletes.

http://support.microsoft.com/default.aspx?scid=kb;EN-US;814324
http://support.microsoft.com/default.aspx?scid=kb;EN-US;325024
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx
http://support.microsoft.com/default.aspx?scid=kb;EN-US;820209
http://support.microsoft.com/default.aspx?scid=kb;en-us;304519 might be a start. Generally you will not want to create an index on each and every column. While indexes speed up data retrieval operations, they slow down data modification operations. Work your way through the links I’ve posted. They should get you going in the right direction. Btw, regarding deadlocks thishttp://www.sql-server-performance.com/forum/topic.asp?TOPIC_ID=9102 might be interesting. —
Frank Kalis
Microsoft SQL Server MVP
http://www.insidesql.de
Ich unterstÃ¼tze PASS Deutschland e.V. http://www.sqlpass.de)

Hi, in my opinion u should not have index each and every field / column, create index only on those columns where required (especially the columns u use for creteria / search / condition) because more index / unused can overhead your table. and Adriaan , <blockquote id="quote">quote:<hr height="1" noshade id="quote">Also keep in mind that the PK is not necessarily a clustered index …<hr height="1" noshade id="quote"></blockquote id="quote"> [?] how ? i have read that if we create PK then by default it creates a Clustered Index … unless we mention nonclusterd isn’t it ? [<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />] Regards. hsGoswami [email protected] "Humans don’t have Caliber to PASS TIME , Time it self Pass or Fail Humans" – by Hemant Goswami

Yes, SQL Server has this behaviour to make the PRIMARY KEY CLUSTERED unless you state otherwise. But that’s exactly what Adriaan said. So I don’t understand your question here.
??? —
Frank Kalis
Microsoft SQL Server MVP
http://www.insidesql.de
Ich unterstÃ¼tze PASS Deutschland e.V. http://www.sqlpass.de)

If there is already a clustered index before you add a primary key, the new pk is created as not clustered — CREATE TABLE dbo.Test (IDcol INT IDENTITY(1, 1), VARCHARcol VARCHAR(100))
CREATE CLUSTERED INDEX idxTest ON dbo.Test (VARCHARcol)
ALTER TABLE dbo.Test
ADD CONSTRAINT a_Test PRIMARY KEY (IDCol) — Now show the PK details:
EXEC sp_helpconstraint ‘Test’
DROP TABLE Test

]]>