largely copied from

  • Cluster Index
  • Non-clustered index
  • Covering Index

    create an index on a column, but include additional, none-indexed information.  run a query that uses the covering index.  When the query finds the index entry, it also finds the additional information.  If this info is enough to satisfy the query, the engine does not have to do to the record.  Create covering indexes to match frequently run queries

    CREATE INDEX IX_LastName_Includes ON dbo.People (LastName) INCLUDE (FirstName, PhoneNumber)
  • Filtered index

    If we have a table with lots of data in a column, mostly type a but some type b (and we care about type b), an index on that table that includes the types will be large.  If we want to query the table for data that selected type b, we can create a relatively small index (less than 100% of the records) just for type b

    CREATE INDEX IX_OrderStatus ON dbo.Orders (OrderNumber) WHERE OrderProcessed = 0
  • full-test indexes
  • XML indexes