Sql-server – Equivalent of include clause while creating non clustered index in sybase

1&2 - Every update query must update the data for the table, wherever it may appear - in the clustered index (always), in any index that has the column in its definition, and in any index which just includes this column.

3 - Removing records may or may not help, it depends on 4:

4 - Probably, because sometimes it will not use the index, but will instead choose to scan the entire table. If it's scanning the entire table, then reducing the size of the table (via (3) above) will help. It will scan the entire table if, so far as the optimizer is concerned, that will be more efficient than using the index, then accessing the clustered index to complete the operation. It may choose to do this because it's actually true that the index will not help, or if statistics are out of date, or just sometimes it gets this wrong. Examine execution plans to determine if this has happened.

4a - If there's a lot of activity on the table, then other operation may have locks that prevent your query from processing as fast as it could if it was the only activity occurring in the database. Waiting for locks can take seconds, minutes, or hours - it depends on how timely the other operations are

5 - Solution to what?

824392 isn't that many rows, but it may make for a big table - it depends on how wide those rows are, really.

That index suggestion by marc_s is wrong. I've added a comment. (And it was my answer accepted too!)

The index for this query would be

CREATE NONCLUSTERED INDEX NC_EmpDep 
  ON Employee(DepartmentID)
  INCLUDE (Lastname, EmployeeID)

An index is typically

CREATE INDEX <name> ON <table> (KeyColList) INCLUDE (NonKeyColList)

Where:

• KeyColList = Key columns = used for row restriction and processing
  WHERE, JOIN, ORDER BY, GROUP BY etc
• NonKeyColList = Non-key columns = used in SELECT and aggregation (e.g. SUM(col)) after selection/restriction