Defining Indexes for a Subscription Class
You can significantly enhance the performance of a Notification Services application by creating appropriate indexes that allow for faster location of records based on the data in a specified field. SQL Server automatically uses indexes when appropriate.
For subscription classes that have scheduled rules, Notification Services automatically creates an index on the ScheduleId column.
Custom Indexes
You can add custom indexes to a subscription class by writing indexing statements. The following example shows the Transact-SQL syntax for creating an index on the City column of the WeatherSubscriptions table:
CREATE INDEX CityIndex
ON dbo.WeatherSubscriptions (City);
Notice that the subscription table has the same name as the subscription class. You only need to know the subscription class name and field names to create indexes.
Also notice that each object name is preceded by the schema name (in this case, dbo). Because multiple applications can share a database, it is good practice to specify the schema name. If you specify a schema name when you define the application database, use the application schema name. The default application schema is dbo. For more information, see Defining the Application Database.
When Notification Services creates the application, it creates the subscription class tables and then adds the custom indexes. When you update the application, if there are any changes to the subscription class, Notification Services renames the subscription tables by appending "Old" to the table name. Indexes stay associated with the renamed tables. For more information, see Updating an Application.
For more information about SQL Server indexes, see Indexes.
To define custom indexes for a subscription class
If you are defining an application through XML, define custom indexes in the application definition file (ADF). If you are defining an application programmatically, use the Notification Services Management Objects (NMO) to define custom indexes.
See Also
Other Resources
CREATE INDEX (Transact-SQL)
Indexes
Defining Subscription Classes
Defining Notification Services Applications