0
create table #testing(id int, name varchar(50))
go

Insert Into #testing
Values(231, 'fasd')

Insert Into #testing
Values(232, 'dsffd')

Insert Into #testing
Values(233, 'xas')

Insert Into #testing
Values(234, 'asdasd')

create table #testing2(id int, name varchar(50))
go
Insert Into #testing(id)
Values(231)

Insert Into #testing(id)
Values(232)

Insert Into #testing(id)
Values(233)

Insert Into #testing(id)
Values(234)
go

update m
set name = x.name
from #testing2 m
join #testing x  
on m.id = x.id
Where m.name is null

Without an index on #testing and #testing2

enter image description here

index on id column on both #testing and #testing2

enter image description here

index is

create nonclustered index ix_test1 on #testing1(id) include (name)
create nonclustered index ix_test1 on #testing2(id)

enter image description here

What would be the best possible index on this situation? Is it right to not have an index on #testing(name) because the write/update would be slower?

Emman Bangis
  • 85
  • 2
  • 6

1 Answers1

0

Including a column in an index is ONLY useful for reads. There is always another copy of the column in the database. So for an update, second index is better since the database only needs to update the column in one place. If you have any selects that select name but only filter on id, then the first index could be useful.

To determine if the first index is worth it, you have to examine your environment. Is the first index used at all? Does the ratio of reads to writes on the index favor reads? Does it significantly improve the speed of an important or frequent query? This are the questions you need to answer.

Also, this question might work better on DatabaseAdministrators StackExchange since you will find even more database experts there.

Vulcronos
  • 3,428
  • 3
  • 16
  • 24
  • I'm confused by your wording: "Including a column in an index is ONLY useful for reads.". How are indexes (in general) useful for anything /but/ reads? AFAIK, include columns are to avoid a key (or rid) lookup once the data is found. – Ben Thul Jul 08 '14 at 19:38
  • @BenThul Index can be used to find rows to update or delete. The included column is also only useful in the select clause, it doesn't do any good in a where, join, or group by to filter it. – Vulcronos Jul 08 '14 at 19:42
  • Right, but when you're doing that update our delete, you're doing reads to find the affected rows. So your being a little imprecise.:-) – Ben Thul Jul 09 '14 at 00:27