included columns for a non-clustered index

Question

Hello
i have a table with lots of records(11 million). The records has a foreign ID, Date and some other fields.
I have a query which i run in my application so often. The query is something like:


if( SELECT * FROM myTable WHERE Date=@Date AND ForeignID=@ForeignID != 0 )    
     UPDATE myTable SET ....... WHERE Date=@Date AND ForeignID=@ForeignID
else
     INSERT INTO myTable(......) VALUES(.....)

I want to add "Date" as a nonclustered index. Then if i add "ForeignID" column as included column for that index, will it help the query executes faster?
Thanks.

score 3 · Accepted Answer · answered Jun 01 '11 at 08:40

I agree with @gbn that you need an index on both Date and ForeignID rather than "Include Column".

You could create it as follows:

CREATE NONCLUSTERED INDEX [IDX1] ON [myTable] ([Date], [ForeignID])

However "Select * " is not a good way to check the existence of a record. You could use the "EXISTS" clause

Alex Aza · Answer 2 · 2011-06-01T08:52:09.000

1

Adding ForeignID will help as index will cover the subquery and it will not need to grab ForeignID from the table.

However, it is better to add unique constraint or index for both Date and ForeignID.

Like this:

create unique index IX_MyTable_Date_ForeignID on MyTable(Date, ForeignID)

Also, you might want to use MERGE statement for this kind of query. Depends on what SQL version you use.

edited Jun 01 '11 at 08:52

answered Jun 01 '11 at 08:04

Alex Aza

76,499
26
155
134

I use both Sql Server 2005/2008. I will search for MERGE thanks. You think adding nonclustered indexes for both Date and ForeignID seperatly is better? – Fer Jun 01 '11 at 08:43
@Fer - Not separately, one index for both fields. Updated the answer. – Alex Aza Jun 01 '11 at 08:50

score 1 · Answer 3 · edited May 23 '17 at 12:26

1

You need an index on both Date and ForeignID, with Date first. It is a filter (in the WHERE clause) so it should be in the key columns not the INCLUDE columns

Also, your pattern of test..update..else..insert isn't scalable. See this for other ways of doing it: Select / Insert version of an Upsert: is there a design pattern for high concurrency?

edited May 23 '17 at 12:26

Community

1
1

answered Jun 01 '11 at 08:11

gbn

422,506
82
585
676

should i add Date and ForeignID seperatly as indexes? I will search for a better pattern about checking updating-inserting. Thnks. – Fer Jun 01 '11 at 08:46
@Fer: no, a composite index on both columns. (Update: as per Hasan's answer) – gbn Jun 01 '11 at 08:59

score 0 · Answer 4 · answered Jun 01 '11 at 08:11

0

Yes I would expect it to speed up this query significantly (do you need to do a select * ?)

but it would slow down inserts into the table as it has another index to write - it will obviously increase the size of the db as well.

It's probably worth adding if this often run proc is really slow.

answered Jun 01 '11 at 08:11

iandayman

4,357
31
38

I think i must be added too. But i am searching for the best way to add an index. By the way i need select for testing of exsiting record but i may use any other method to do that. – Fer Jun 01 '11 at 08:47

included columns for a non-clustered index

4 Answers4