View Single Post
Old February 22nd, 2005, 09:41 AM
BinFrog BinFrog is offline
Registered User
Join Date: Feb 2005
Location: , , USA.
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via AIM to BinFrog
Default indexing performance question

I was wondering if anyone knew the answer this simple, yet search engine deficient, question.

Suppose I create a table called sw_Person_Update. The table has maybe 250,000 rows including swPersonID, swLastName, swEmailAddress. The table was created with some info I got from a select statement on the contact table which we'll call sw_Contact. That table has well over 2 million rows. Also, sw_Contact has a clustered index on it's primary key, swContactID. sw_Person_Update has no indexes.

My question is: how much will a clustered index be helpful on sw_Person_Update's swPersonID field if I write an update as follows:

Update sw_Contact
set sw_contact.swEmailAddress = sw_Person_Update.swEmailAddress
from sw_Contact
join sw_Person_Update on swPersonID = swContactID

Will the lack of an index on our ad-hoc table create performance issues? I know that if we didn't have an index on the sw_Contact table we would have major time issues. But does the joined table matter? Doesn't it just have to go row by row in sw_Person_Update anyway? I know it's good practice to always have a clustered index on tables, but suppose I were to forget to add one (which recently happened), does anyone know what kind of a performance hit the database would take?

Because in my mind I see the update taking about the same amount of time either way. Because the table being updated has an index, SQL Server can jump right to the record it needs to update while going through the Update statement. But our ad-hoc table still needs to go row by row, regardless of whether or not it is indexed. Do you see what I mean? What does it matter if my ad-hoc table is stored in SQL as "1, 2, 3, 4, 5" or "202, 9991, 107453, 70712"?

Am I way off on this?