How to Add a Custom Index

To ensure that custom indexes are easy to find and are not overwritten by Blackbaud CRM, it is important to follow a few conventions.

• Naming – Custom indexes should use the following naming convention:

  <Index Prefix>_USR_<Table Name>_<Index Key Column 1>_<Index Key Column 2>_<…>

  The "USR_" prefix ensures that the system does not overwrite your custom indexes when you apply service packs and upgrades. The rest of the naming scheme gives a clear indication the columns that the index uses and their order.

  Note: For more information about naming conventions for database objects, see Commandment 5: Prefix Custom SQL Objects.

  Example – Let's say you have a custom report that pulls back gifts of more than $10,000 and that you want to search these gifts by quarter. (Typically, this type of report would be better run from the data warehouse, but for the purposes of this example, let's assume that only the OLTP database is available.) Your database administrator has noticed that every time this report is run, it performs a table scan on the Financial Transaction table. You decide to add an index on the BASEAMOUNT and DATE columns. To prevent key lookups, you also include CONSTITUENTID and ID. Your index would be named: IX_USR_FINANCIALTRANSACTION_BASEAMOUNT_DATE. The included columns don’t need to be part of the name as they are not part of the index key.

• Extended Properties – In addition to the naming convention, we recommend that you include extended properties with the following information:

  • Author – This can be a user or organization name.

  • Date – The date the index was added.

  • Reason – The purpose the custom index serves.

  This additional information will help developers quickly determine the source and purpose of the index, which aids in troubleshooting performance issues.

• Sample Script – Here is a sample of the script that would add the index from our example and the corresponding extended properties:

  CREATE NONCLUSTERED INDEX [IX_USR_FINANCIALTRANSACTION_BASEAMOUNT_DATE] ON [dbo].[FINANCIALTRANSACTION]
  (
      [DATE] ASC,
      [BASEAMOUNT] ASC
  )
  INCLUDE ( [ID],
  [CONSTITUENTID]) WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
  GO
  
  EXEC sys.sp_addextendedproperty @name=N'Author', @value=N'Blackbaud Product Development' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'FINANCIALTRANSACTION', @level2type=N'INDEX',@level2name=N'IX_USR_FINANCIALTRANSACTION_BASEAMOUNT_DATE'
  GO
  
  EXEC sys.sp_addextendedproperty @name=N'Date_Added', @value=N'1/1/2014' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'FINANCIALTRANSACTION', @level2type=N'INDEX',@level2name=N'IX_USR_FINANCIALTRANSACTION_BASEAMOUNT_DATE'
  
  GO
  
  EXEC sys.sp_addextendedproperty @name=N'Purpose', @value=N'Optimize read performance of the Large Gifts Report' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'FINANCIALTRANSACTION', @level2type=N'INDEX',@level2name=N'IX_USR_FINANCIALTRANSACTION_BASEAMOUNT_DATE'
  
  GO