included columns for a non-clustered index

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.

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

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?

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.

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.