November 28, 2007 at 1:23 am
How does SQL manage and use Primary keys & indeces in INSERT, DELETE
and UPDATE operations?
November 28, 2007 at 2:50 am
As the name implies, a primary key has to be unique for each record in the table, so SQL will definitely test if it exists before committing an INSERT or UPDATE.
SQL will commit a DELETE on a table with a primary key defined ONLY when there are no related records in its foreign key tables.
An index is a sorted list of the values in the indexed columns. It may be single-column or composite, clustered or unclustered, and unique/non-unique.
A clustered index sorts the table physically while non-clustered indexes maintain separate index files. Each index is updated after each INSERT, UPDATE and DELETE.
A unique index will not allow duplicate values in its base columns during INSERT and UPDATE.
Whether an index is composite or single-column does not affect SQL.
December 2, 2007 at 5:58 pm
Thanks a l ot.
December 4, 2007 at 1:15 am
Just one additional thing: in case of some large scale operations (e.g. when inserting large amount of data), it may help a lot to drop indexes, perform the task and then create indexes again.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply